[MSSQL] STUDY 3

1. JOIN 

- 두개 이상의 테이블을 서로 묶어서 하나의 결과 집합으로 만들어 내는 것

1) INNER JOIN

    - 양쪽 테이블에 모두 내용이 있는 것만 조인되는 방식

INNER JOIN

- [구문형식]

SELECT < 열목록 >

FROM < 첫 번째 테이블 >

INNER JOIN  < 두 번째 테이블 >

ON  < 조인될 조건 >

[WHERE 검색조건]

- 예 

SELECT *

FROM buyTbl

INNER JOIN userTbl

ON buyTbl.userid = userTbl.userid

WHERE buyTbl.userid = 'HYS'

# 두 개 테이블을 결합하는 경우에 동일한 컬럼이름이 있다면 꼭 '테이블 이름.컬럼 이름' 형식으로 표기해줘야 함.

- 별칭(Alias) 적용 예

SELECT B.userid, U.name, B.prodName, U.addr, U.mobile1 + U.mobile2 AS [ 연락처 ]

FROM buyTbl B

INNER JOIN userTbl U

ON B.userid = U.userid

WHERE B.userid = 'HYS'

# 테이블에 별칭을 주기 위해서는 간단하게 FROM 절에 나오는 테이블 이름 뒤에 별칭을 붙여주기만 하면 됨.

- 실습 19 : 3개의 테이블 정의하고 데이터를 입력하고 INNER JOIN을 이용하여 정렬

USE sqlDB

CREATE TABLE stdTbl

(

stdName  nvarchar(10) NOT NULL PRIMARY KEY,

addr nchar(4)

)

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 stdTbl VALUES ('박주영', '경기')

INSERT INTO stdTbl VALUES ('조재진', '충북')

INSERT INTO stdTbl VALUES ('이천수', '인천')

INSERT INTO stdTbl VALUES ('안정환', '강원')

GO

INSERT INTO clubTbl VALUES ('수영', '101호')

INSERT INTO clubTbl VALUES ('바둑', '102호')

INSERT INTO clubTbl VALUES ('축구', '103호')

INSERT INTO clubTbl VALUES ('봉사', '104호')

GO

INSERT INTO stdclubTbl VALUES ('박지성', '바둑')

INSERT INTO stdclubTbl VALUES ('박지성', '축구')

INSERT INTO stdclubTbl VALUES ('조재진', '축구')

INSERT INTO stdclubTbl VALUES ('이천수', '축구')

INSERT INTO stdclubTbl VALUES ('이천수', '봉사')

INSERT INTO stdclubTbl VALUES ('안정환', '봉사')

GO

--학생 기준으로 정렬

SELECT S.stdName, S.addr, C.clubName, C.roomNo

FROM stdTbl S

INNER JOIN stdclubTbl SC

ON S.stdName = SC.stdName

INNER JOIN clubTbl C

ON SC.clubName = C.clubName

ORDER BY S.stdName

--동아리 기준으로 정렬

SELECT C.clubName, C.roomNo, S.stdName, S.addr

FROM stdTbl S

INNER JOIN stdclubTbl SC

ON S.stdName = SC.stdName

INNER JOIN clubTbl C

ON SC.clubName = C.clubName

ORDER BY C.clubName

QUIZ - 실습 9에서 INNER JOIN을 사용하지 말고, FROM.. WHERE 만을 사용해서 쿼리 작성하기

SELECT S.stdName, S.addr, C.clubName, C.roomNo

FROM stdTbl S, clubTbl C, stdclubTbl SC

WHERE S.stdName = SC.stdName AND SC.clubName = C.clubName

2) OUTER JOIN

- 한쪽에만 내용이 있어도 그 결과가 표시되는 조인 방식

OUTER JOIN

- [구문형식]

SELECT < 열 목록 >

FROM < 첫 번째 테이블 ( LEFT 테이블 ) >

< LEFT | RIGHT | FULL > OUTER JOIN < 두 번째 테이블 ( RIGHT 테이블 ) >

ON

[WHERE 검색조건]

예 : 왼쪽 테이블의 것은 모두 출력되어야 한다.

SELECT U.userid, U.name, B.prodName, U.addr, U.mobile1 + U.mobile2 AS [ 연락처 ]

FROM userTbl U

LEFT OUTER JOIN buyTbl B

ON B.userid = U.userid

ORDER BY U.userid

# FULL OUTER JOIN : 양쪽 모두에 조건이 일치하지 않는 것을 모두 출력하는 개념

3) CROSS JOIN ( 상호 조인, 카티션 곱 )

- 한쪽 테이블의 모든 행들과 다른 쪽 테이블의 모든 행을 조인시키는 기능

- 결과 개수는 두 테이블 개수를 곱한 개수가 된다. 

예 : 

USE  sqlDB

SELECT *

FROM  buyTbl

CROSS JOIN userTbl 

4) SELF JOIN ( 자체 조인 )

-자기 자신과 자기 자신이 조인한다는 의미

- 하나의 테이블에 같은 데이터가 존재하되 의미는 다르게 존재하는 경우, 두 테이블을 서로 SELF JOIN 시켜서 정보를 확인

예 :

USE sqlDB

SELECT A.emp AS [부하직원] , B.emp AS [직속상관], B.department AS [직속상관부서]

FROM empTbl A

INNER JOIN empTbl B

ON A.manager = B.emp

WHERE A.emp = '우대리'

2. UNION, UNION ALL

- 두 쿼리의 결과를 행으로 합치는 것

[구문형식]

SELECT  문장1

UNION  [ALL]

SELECT  문장2

# SELECT 문장1과 SELECT 문장2의 결과 열의 개수가 같아야 하고, 데이터 형식도 각 열 단위로 같거나 서로 호환되는 데이터 형식이어야 함. 

- UNION : 중복된 열 제거

- UNION ALL : 중복된 열까지 모두 출력

예 : 

SELECT stdName, addr FROM stdTbl

UNION ALL 

SELECT clubName, roomNo FROM clubTbl

Section 04 : SQL 프로그래밍

1. IF ~ ELSE

    - 조건에 따른 분기

    - 이중 분기 

    - 한 문장 이상이 처리되어야 할 때는 BEGIN.. END와 함께 묶어줘야만 함.

    [구문형식]

IF < 부울 표현식 >

BEGIN

SQL문장들1...

END

ELSE

BEGIN

SQL문장들2...

END

2. CASE

- 다중분기

예1 : 

DECLARE  @point  INT,  @credit  NCHAR(1)

SET  @point  =  100

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

PRINT  N'취득점수==> ' + CAST(@point AS NCHAR(3) )

PRINT  N'학점==> ' + @credit

예2 : 

SELECT * FROM buyTbl

GO

SELECT U.userid, U.name, sum(price*amount) AS [총구매액],

CASE

WHEN ( sum(price*amount) >= 2000 ) THEN N'최우수고객'

WHEN ( sum(price*amount) >= 1000 ) THEN N'우수고객'

WHEN ( sum(price*amount) >= 1 ) THEN N'일반고객'

ELSE '유령고객'

END AS [고객등급]

FROM buyTbl B

RIGHT OUTER JOIN userTbl U

ON B.userid = U.userid

GROUP BY U.userid, U.name

ORDER BY sum(price*amount) DESC

3. WHILE과 BREAK, CONTINUE, RETURN

1) WHILE

[구문형식]

WHILE < 부울 식 >

BEGIN

SQL 명령문들

END

예 1 : 

DECLARE @i INT

DECLARE @hap BIGINT

SET @i = 1

SET @hap = 0

WHILE ( @i <= 100 )

BEGIN

IF ( @i % 7 = 0 )

BEGIN

PRINT '7의 배수: ' + CAST(@i AS NCHAR(3))

SET @i = @i + 1

CONTINUE

END

SET @hap = @hap + @i

IF (@hap > 1000) BREAK

SET @i = @i + 1

END

PRINT '합계 = ' + CAST(@hap AS NCHAR(10) )

3. GOTO

- 지정된 위치로 무조건 이동

예 :

...

...

SET @hap = @hap + @i

IF ( @hap > 1000 ) GOTO endprint

SET @i = @i + 1

END

endprint :

PRINT ...

4. TRY, CATCH

[구문형식]

BEGIN TRY

원래 사용하던 SQL 문장들

END TRY

BEGIN CATCH

만약 BEGIN .. TRY에서 오류가 발생하면 처리할 일들

END CATCH

# SQL에서 제공하는 오류 파악 함수

- ERROR_NUMBER() : 오류번호

- ERROR_MESSAGE() : 오류메시지

- ERROR_SEVERITY() : 오류 심각도

- ERROR_STATE() : 오류 상태 번호

- ERROR_LINE() : 오류를 발생시킨 행 번호

- ERROR_PROCEDURE() : 오류가 발생한 저장프로시저나 트리거 이름