조인
조인의 개념
두 개 이상의 테이블을 서로 묶어서 하나의 결과집합으로 만들어 내는 것
INNER JOIN
구매 테이블에서 JYP라는 아이디를 가진 사람이 구매한 물건을 발송하려면 다음과 같이 이름/주소/연락처 등을 조인해서 검색하면 된다. 가장 많이 사용하는 조인으로써 일반 적인 조인은 이 INNER JOIN을 말하는 것이다.
형식)
SELECT <열 목록>
FROM <첫번째 테이블>
INNER JOIN <두번째 테이블>
ON<조인될 조건>
[WHERE 검색 조건]
위의 결과를 생성하기 위해 아래 그림과 같은 과정을 거친다.
구매 테이블의 userID(buyTbl.userid)인 'JYP'를 추출하게 된다. 그런 다음 'JYP'와 동일한 값을 판매 테이블의userID(userTbl.userid) 열에서 검색한 후 'JYP'라는 아이디를 찾으면 구매 테이블과 판매 테이블의 두 행을 결합(JOIN)한다.
위의 구문에서 "WHERE buyTbl.userid = 'JYP' "을 생략하면 buyTbl의 모든 행에 대해서 위와 동일한 방식으로 반복하게 된다. WHERE을 뺀 결과는 아래와 같다.
열의 항목이 너무 많아서 복잡해 보이므로, 필요한 열만 추출해보자. 아이디/이름/구매물품/주소/연락처만 추출해보자.
구매 테이블을 보면, 물건을 구매한 사용자의 아이디와 물건 등의 정보만 나타난다. 그런데 물건을 배송하려면 구매한 회원의 주소를 알아야한다. 이때 회원의 주소 정보를 알기 위해 주소 정보가 있는 회원 테이블과 결합하는 방법이 INNER JOIN이다.
열의 항목이 너무 많아서 복잡해 보이므로 필요한 열만 추출해보자.
SELECT buyTbl.userID AS '아이디',
userTBL.name AS '회원명',
buyTbl.prodName AS '물건',
buyTbl.price AS '가격',
buyTbl.amount AS '수량',
userTBL.addr AS '주소'
FROM buyTBL
INNER JOIN userTBL
on buyTbl.userID = userTBL.userID;
세 개의 테이블 내부 조인
USE sqlDB
CREATE TABLE stdtbl(
stdName NVARCHAR(10) NOT NULL PRIMARY KEY,
addr NCHAR(4) NOT NULL
);
GO
CREATE TABLE clubTbl(
clubName NVARCHAR(10) NOT NULL PRIMARY KEY,
roomNo NCHAR(4) NOT NULL
);
GO
CREATE TABLE stdclubTbl(
num INT IDENTITY NOT NULL PRIMARY KEY,
stdName NVARCHAR(10) NOT NULL
FOREIGN KEY REFERENCES stdTbl(stdName),
clubName NVARCHAR(10) NOT NULL
FOREIGN KEY REFERENCES clubTbl(clubName)
);
GO
INSERT INTO stdTbl VALUES
('김범수','경남'),
('성시경','서울'),
('조용필','경기'),
('은지원','경북'),
('바비킴','서울');
INSERT INTO clubTbl VALUES
('수영','101호'),
('바둑','102호'),
('축구','103호'),
('봉사','104호');
INSERT INTO stdclubTbl VALUES
('김범수','바둑'),
('김범수','축구'),
('조용필','축구'),
('은지원','축구'),
('은지원','봉사'),
('바비킴','봉사');
세 테이블을 이용해서 학생을 기준으로 학생이름/지역/가입한 동아리/동아리 이름을 출력해보자.
SELECT s.stdName, s.addr, t.clubName, c.roomNo
FROM stdTbl AS s
INNER JOIN stdclubTbl AS t
ON s.stdName = t.stdName
INNER JOIN clubTbl AS c
ON t.clubName = c.clubName;
OUTER JOIN(외부 조인)
내부 조인은 조인의 조건인 만족되지 않는 행은 출력되지 않지만, 외부조인은 조건이 만족되지 않는 행까지 출력된다.
형식)
SELECT <열목록>
FROM <첫 번재 테이블(LEFT 테이블)>
<LEFT | RIGHT | FULL> OUTER JOIN <두번째 테이블(RIGHT 테이블)>
ON <조인될 조건>
[WHERE 검색조건]
실습예제1)
SELECT u.userID, u.name, b.prodName,
u.addr, u.mobile1+u.mobile2 AS mobile
FROM userTBL AS u
LEFT OUTER JOIN buyTbl AS b
ON u.userID = b.userID
WHERE b.prodName IS NULL -- 회원중에 구매를 한번도 안한사람 추출
ORDER BY u.userID ASC
-- LEFT와 RIGHT를 바꾸려면 왼쪽테이블과 오른쪽 테이블의 위치만 바꿔주면 된다.
SELECT u.userID, u.name, b.prodName,
u.addr, u.mobile1+u.mobile2 AS mobile
FROM buyTblAS b
LEFT OUTER JOIN userTBL AS u
ON u.userID = b.userID
WHERE b.prodName IS NULL
ORDER BY u.userID ASC
실습예제2)
위에 3개의 테이블을 선언할 때, 동아리에 가입하지 않은 학생 성시경은 출력이 안됬다. OUTER JOIN으로 동아리에 가입하지 않은 학생도 출력되게 수정해보자.
이번에는 동아리를 기준으로 가입된 학생을 출력하되, 가입 학생이 하나도 없는 동아리도 출력되게 해보자.
LEFT 조인과 RIGHT 조인의 차이를 보려면 ON절을 기준으로 생각하면된다.
REFT JOIN 이면 ON의 왼쪽꺼기준 - 성시경의 이름은 있는데 클럽이 가입안되있으니 NULL로 출력한다
RIGHT JOIN 이면 ON의 오른쪽꺼기준 - 클럽에 가입한 성시경은 없으니 NULL로 출력된다.
INNER JOIN과의 비교)
두 테이블의 교집합에 딱 맞게 나오는건 이너조인(NULL이 출력되지않고 해당되는 값만 출력한다,)
NULL값(가입안되있거나 구매를 안한 사람)까지 출력하는게 아우터 조인
크로스 조인, 셀프 조인
안씀, 필요없음
UNION, UNION ALL
겁나 많이씀.
UNION은 두 쿼리의 결과를 행으로 합치는 것을 말한다.(단, 중복은 제거한다)
UNION ALL은 중복까지 허용한다.
UNION은 두 쿼리의 결과를 행으로 합치는 것을 말한다. SELECT 문장 1과 SELECT 문장 2의 결과열의 개수가 같아야 하고, 데이터 형식도 각 열 단위로 같거나 서로 호환되는 데이터 형식이어야 한다. UNION만 사용하면 중복된 열은 제거되고 데이터가 정렬되어 나오며, UNION ALL을 사용하면 중복된 열까지 모두 출력된다.
SELECT s.stdName, s.addr FROM stdTBL AS s
UNION
SELECT c.clubName, c.roomNo FROM clubTBL AS c;
----밑은 유니온 ALL------
SELECT s.stdName, s.addr FROM stdTBL AS s
UNION ALL
SELECT s1.stdName, s1.addr FROM stdTBL AS s1;
EXCEPT : 첫번째 쿼리의 결과 중에서, 두 번째 쿼리에 해당하는 것을 제외한다.
INTERSECT : 첫 번째 쿼리의 결과 중에서 두 번째 쿼리에 해당되는 것만 조회한다.
SQL 프로그래밍
IF ... ELSE 문
참과 거짓에 의해서 실행해야 할 구문이 다를 때 사용한다.
유형)
IF<부울 표현식>
BEGIN
SQL 문장들 1..
END
ELSE
BEGIN
SQL 문장들 2..
END
if문 예제
DECLARE @VAR1 INT -- @var1 변수선언
SET @var1 = 100 -- 변수에 초기값 대입
IF @var1 = 100 -- 만약 @var1이 100이라면,
BEGIN
PRINT '@var1이 100이다.'
END
ELSE
BEGIN
PRINT '@var1이 100이 아니다.'
END
참고로 DECLARE로 선언된 변수는 실행후에 사라진다.
CASE
if문은 2중 분기로 참 거짓만 존재하는데 점수나 학점은 경우에따라 여러가지로 분리되는 다중 분기인 CASE문을 사용하는게 편하다.
DECLARE @point INT = 77, @credit CHAR(1)
SET @credit =
CASE
WHEN(@point >= 90) THEN 'A'
WHEN(@point >= 80) THEN 'B'
WHEN(@point >= 70) THEN 'C'
WHEN(@point >= 60) THEN 'D'
ELSE 'F'
END
SELECT '취득점수' + CAST(@point AS char(2))
SELECT '학점' + @credit
위 예제에서 CASE의 구문의 결과는 하나의 값만을 돌려주고, 그 값은 @credit 변수에 대입하였다.
참고로 CASE 문의 활용은 SELECT문에서 더 많이 사용된다.
case문 실습)
구매한 액수에 따라서 최우수고객/우수고객/일반고객 등으로 분류하기 위해서 CASE문을 활용해보자
구매테이블(buyTbl)에 구매액(price*amount)이 1500원 이상인 고객은 '최우수 고객', 1000원 이상인 고객은 '우수고객', 1원 이상인 고객은 '일반고객'으로 출력하자. 또 전혀 구매 실적이 없는 고객은 '유령고객'이라고 출력하자.
SELECT u.userID, u.name,
SUM(price * amount) AS '총구매액',
CASE
WHEN SUM(price * amount) >= 1500 THEN 'VVIP'
WHEN SUM(price * amount) >= 1000 THEN 'VIP'
WHEN SUM(price * amount) >= 1 THEN 'NORMAL'
ELSE '유령고객'
END AS '고객등급' --필드에 없으니까 이름을 붙여준다
FROM userTBL AS u
LEFT OUTER JOIN buyTbl AS b
ON u.userID = b.userID
GROUP BY u.userID, u.name
ORDER BY SUM(price * amount) DESC;
스텝에 따라서 쿼리를 짤줄 알아야한다
반복문
WHILE, BREAK, CONTINUE, RETURN
WHILE
참인 동안에 계속 반복되는 반복문
CONTINUE, BREAK
CONTINUE는 WHILE문의 비교문으로 이동하고, BREAK는 WHILE문을 빠져나감
형식)
WHILE <부울식>
BEGIN
SQL 명령문들
END1에서 100까지의 값을 모두 더하는 간단한 기능을 구현해보자.
DECLARE @i INt = 1 -- 1에서 100까지 증가할 변수
DECLARE @hap BIGINT = 0 -- 더한 값을 누적할 변수
WHILE (@i <= 100)
BEGIN
SET @hap += @i -- @hap의 원래의 값에 @i를 더해서 다시 @hap에 넣어라
SET @i += 1 -- @i의 원래의 값에 1을 더해서 다시 @i에 넣어라
END
PRINT @hap
1에서 100까지 합계에서 7의 배수는 총 합계에서 제외시키는 코드를 짜보자. 또, 합계가 1000이 넘으면 더하는 것을 그만두고, 출력을 하고싶다고 가정할때, CONTINUE 문과 BREAK 문을 사용할 수 있다.
DECLARE @i INt = 1;
DECLARE @hap BIGINT = 0;
WHILE (@i <= 100)
BEGIN
if(@i % 7 = 0)
BEGIN
PRINT '7의 배수' + CAST (@i AS NCHAR(3))
SET @i += 1
CONTINUE
END
SET @hap += @i
IF(@hap > 1000) BREAK
SET @i += 1
END
PRINT '합계=' + CAST(@hap AS NCHAR(10))
EXEC (동적 SQL)
SQL 문을 실행시켜주는 역할을 한다.
언급되지 않은 부분은 생략...
8장. 테이블과 뷰
테이블은 반복했고, 확인 차원에서 간단히 테이블을 생성해보고, 제약조건과 테이블의 수정에 대해 자세히 알아보자.
SSMS에서 테이블 생성
제약조건
데이터의 무결성을 지키기 위한 제한된 조건
특정 데이터를 입력할 때 무조건 입력되는 것이 아니라, 어떠한 조건을 만족했을 때에만 입력되도록 제한한다.
기본 키 제약 조건
무진장 중요한 개념이다.
1. 기본 키(primary Key) 제약 조건은 테이블의 각 행들을 구분할 수 있는 식별자이다.
2. 중복될 수 없고, NULL 값이 입력 될 수 없다.
3. 기본키로 설정하면 자동으로 클러스터형 인덱스가 생성된다.
4. 기본키는 하나의 열 또는 여러 개의 열을 합쳐서 설정할 수는 있으나, 한 개만 설정할 수 있다.
5. 생성하는 방법은 CREATE TABLE 문에 PRIMARY KEY라는 예약어를 넣어주면 된다.
CREATE TABLE userTbl
(userID char(8) NOT NULL PRIMARY KEY,
name nvarchar(10) NOT NULL,
-- 중간 생략--
테이블의 정보를 보기 위해서는 sp_help 프로시저를 사용하면 된다.
제일 마지막에서 제약 조건의 타입과 제약 조건 이름, 그리고 제약 조건이 설정된 열도 확인 할 수 있다. 나중에 더 살펴보므로 참고만해보자.
외래 키(Foreign Key) 제약 조건
1. 두 테이블 간의 관계를 선언함으로써 데이터의 무결성을 보장해준다.
2. 외래키 관계를 설정하게 되면하나의 테이블이 다른 테이블에 의존하게 된다.
3. '외래키 테이블'에 데이터를 입력할 때는 꼭 '기준 테이블'을 참조해서 입력하므로, '기준 테이블'에 이미 데이터가 존재해야만 한다.
외래 키 테이블이 참조하는 기준 테이블 열은 기본키이거나 유니크 제약조건이 설정되어있어야 한다.
외래 키를 생성하는 방법은 CREATE TABLE 내부에 FOREIGN KEY 키워드로 설정하는 방법이 있다.
Unique 제약 조건
1. 중복되지 않는 유일한 값을 입력해야함
2. Primary Key 제약조건과 거의 비슷하며 차이점은 NULL값을 허용한다.(단 1개만)
3. 회원 테이블의 예를 든다면 주로 email 주소를 Unique로 설정하는 경우가 많다.
NULL이 두개라면 이미 유일하지않다는 것이다.
Unique 지정하는방법)
위의 예제에서 userID랑 email 부분을 똑같이 입력하면 오류가 뜨는 것을 확인할 수 있다. 이는 userID를 기본 키로 설정하고, email을 UNIQUE 제약 조건을 지정해줬으므로 중복되면 SSMS에서 제약을 어겼다고 오류가 뜨기 때문이다.
또, 첫번째 실행하고 또 실행하면 오류가 뜨는데, 기본키가 이미 테이블에 입력되어있는데 또 넣으려고 실행하면 똑같은 데이터가 중복되서 들어가기때문에 기본키의 제약 조건을 위반했다는 오류가 뜬다.
('이미 가입된 이메일 주소입니다.' 가 뜨는거랑 비슷한 개념이라고 생각해보자)
check 제약 조건
1. 입력되는 데이터를 점검하는 기능.
2. check 제약조건이 설정되면 제약조건에 위배되는 데이터는 입력이 되지않는다.
3. 예로 출생년도가 1900년 이후이고 현재 시간 이전이어야 하는 조건을 지정한다.
DEFAULT 정의
NULL값이 들어와도 기본값으로 정해준 값이 테이블에 삽입된다.
NULL값 허용
테이블 디자인에서 체크하면 NULL허용, 체크안하면 NULL값을 허용하지 않는다
스파스열
생략
임시테이블
임시로 만들었다가 끝나면 사라지는 테이블
테이블삭제
DROP 테이블하면 삭제되지만, 마우스 우클릭으로 삭제누르는게 더 편하다.
차일드랑 더미는 자식관계인데 이미 사용하고 있으므로 삭제가 되지 않는다
그래서 자식 테이블부터 지워야한다.
테이블 수정
ALTER TABLE 문을 사용한다.이미 생성된 테이블에 무엇인가를 추가/변경/수정/삭제하는 것은 모두 ALTER TABLE을 사용한다.
이 사이에 있는 개념들은 스킵..
스키마(Schema)
데이터베이스_이름.스키마._이름.개체_이름1. 스키마는 데이터베이스 내에 있는 개체들을 관리하는 묶음
2.. 스키마 이름을 생략하면 자동으로 디폴트 스키마인 'dbo'를 자동으로 붙여준다
3. 사용자가 직접 스키마를 생성하고 지정할 수 있음
4. 강력하게 묶는 개념이라기보다는 관리의 편리를 위해서 가볍게 테이블을 묶어준다고 생각하는 것이 좋다.
뷰
사용자 입장에서는 테이블과 동일하게 사용하는 개체이다. 뷰는 한 번 생성하면 테이블이라고 생각하고 사용해도 될 정도로 사용자의 입장에서는 테이블과 거의 동일한 개체로 여겨진다. 뷰에 데이터를 입력 가능하나 되도록 입력하지 않는 것이 좋다.
뷰 생성과 사용 예제)
CREATE VIEW v_userTbl
AS
SELECT userid, name, addr FROM userTbl
GO
뷰의 장점
1. 보안에 도움이 된다.
위의 v_userTbl테이블 예제에서 사용자의 이름과 주소만 있을뿐, 사용자의 개인정보인 출생년도, 연락처, 키, 가입일 등의 정보는 들어가있지않다. 테이블을 그대로 출력하면 사용자의 중요 개인정보를 누구나 볼 수 있게되지만, 외부 사람들에게 userTbl에 접근하지 못하도록 권한을 제한하고 뷰에만 접근할 수 있게 권한을 준다면 중요한 개인정보를 보호할 수 있다.
2. 복잡한 쿼리를 단순화시킬 수 있다.
어떤 쿼리를 자주 사용해야 한다면 이것을 뷰로 생성하고 사용자가 해당 뷰에만 접근할 수 있게 비교적 간단히 사용할 수 있다.
뷰의 종류
1. 표준 뷰
한개 또는 그 이상의 테이블을 이용해서 만든 뷰를 표준 뷰
2. 분할 뷰
한대 또는 여러 대의 서버에있는 테이블을 조인해서 하나의 테이블처럼 보이도록 하는 뷰
3. 인덱싱된 뷰
예외적으로 테이블처럼 실제 데이터가 뷰에 들어있는 뷰이다. 인덱싱된 뷰에는 인덱스를 구성하는 실제 데이터가 들어있다. 뷰의 단점 중 하나는 대량의 데이터를 조인하는 내용을 포함할 경우에, 실시간으로 쿼리가 수행되는 것으로 SQL Server에 큰 부하를 줄 수밖에 없다. 대신 부하를 줄이는 방법으로 뷰에 실제 데이터를 미리 가져다 놓는것이 있다.
4. 시스템 뷰
SQL Server는 상태 및 내부의 정보를 관리하기 위한 시스템 테이블을 가지고 있다. 이는 일반 사용자가 시스템 테이블에 직접 테이블에 직접 접근하지 못하도록 되어 있다. 시스템 뷰는 SQL Server의 상태 등에 관한 정보를 보여주는 뷰를 말한다.
'개발자과정준비 > 데이터베이스(DB)' 카테고리의 다른 글
| [DB] 6. 커서(Cursor), 트리거(trigger), 전체 텍스트 검색(Full text Search Service) (0) | 2020.06.13 |
|---|---|
| [DB] 5. 인덱스, 트랜잭션, 데이터베이스 모델링, 저장 프로시저, 사용자 정의 함수 (0) | 2020.06.12 |
| [DB] 3. ROLLUP(), WITH절, INSERT문, 데이터형식의 종류, JSON 데이터, PIVOT (0) | 2020.06.09 |
| [DB] 2. 운영 실습, SELECT 문 (0) | 2020.06.08 |
| [DB] 1. DBMS 개요, DB 생성 실습 (0) | 2020.06.08 |
