SQL Server 2005

1. SQL Server 2005 필수로 알아야 할 Transact-SQL 문

2. 기본 T-SQL 구문 : 기본적인 SELECT…FROM (1) • SELECT문의 기본 형식 [ WITH <common_table_expression>] SELECT select_list [ INTO new_table ] [ FROM table_source ] [ WHERE search_condition ] [ GROUP BY group_by_expression ] [ HAVING search_condition ] [ ORDER BY order_expression [ ASC | DESC ] ] • 가장 간단한 SELECT 형식 SELECT 열목록 FROM 테이블 WHERE 조건

3. USE 구문 • USE 데이터베이스_이름 • SELECT와 FROM • 사용 예) • USE Adventureworks • SELECT * FROM HumanResources.Employee • SELECT * FROM BRAIN.AdventureWorks.HumanResources.Employee • SELECT Name, GroupName FROM HumanResources.Department • 주석 • ‘--’ 이후로는 주석으로 처리됨 기본 T-SQL 구문 : 기본적인 SELECT…FROM (2)

4. <실습1> DB이름, 스키마이름 등의 조회 • 실습 목표 • DB이름, 스키마 이름, 테이블 이름 등이 정확히 기억나지 않을 때 조회하는 방법을 익힌다. • 실습 요약 • DB 조회 • EXEC sp_helpdb ; • 테이블 정보 조회 • EXEC sp_tables @table_type = "'TABLE'" ; • 열이름 조회 • EXEC sp_columns @table_name = ‘테이블이름', @table_owner = ‘스키마이름‘;

5. <실습2> 샘플 데이터베이스의 생성 • 실습 목표 • 앞으로 실습에서 주로 사용될 데이터베이스와 테이블을 생성한다. • 샘플 데이터베이스 구조

6. 기본적인 WHERE 절 SELECT 필드이름 FROM 테이블이름 WHERE 조건식 • 관계연산자의 사용 SELECT userID, Name FROM userTbl WHERE birthYear >= 1981 AND height >= 180 • BETWEEN… AND와 IN() SELECT Name, height FROM userTbl WHERE height BETWEEN 180 AND 183 SELECT Name, height FROM userTbl WHERE height IN (178, 180, 182) • ANY/ALL/SOME 그리고 하위쿼리(SubQuery, 서브쿼리) SELECT Name, height FROM userTbl WHERE height >= ANY (SELECT height FROM userTbl WHERE mobile1 = '019') SELECT Name, height FROM userTbl WHERE height = ANY (SELECT height FROM userTbl WHERE mobile1 = '019‘) 기본 T-SQL 구문 : SELECT…FROM... WHERE (1)

7. ORDER BY  순서대로 정렬 SELECT Name, height FROM userTbl ORDER BY height • DISTINCT  중복제거 SELECT DISTINCT addr FROM userTbl • TOP(N)  상위 N개만 출력 SELECT TOP(10) CreditCardID FROM Sales.CreditCard SELECT TOP(0.1) PERCENT CreditCardID FROM Sales.CreditCard • TABLESAMPLE  일정 샘플 추출 SELECT * FROM Sales.SalesOrderDetail TABLESAMPLE(5 PERCENT) • SELECT INTO  테이블 복사에 주로 사용 SELECT 복사할열 INTO 새로운테이블 FROM 기존테이블 SELECT * INTO buyTbl2 FROM buyTbl 기본 T-SQL 구문 : SELECT…FROM... WHERE (2)

8. GROUP BY  결과를 그룹으로 묶어줌 • 형식 : [ WITH <common_table_expression>] SELECT select_list [ INTO new_table ] [ FROM table_source ] [ WHERE search_condition ] [ GROUP BY group_by_expression ] [ HAVING search_condition ] [ ORDER BY order_expression [ ASC | DESC ] ] • 사용 예: SELECT userid AS [사용자아이디], SUM(amount) AS [총구매개수] FROM buyTbl GROUP BY userid 기본 T-SQL 구문 : GROUP BY

9. SUM() 외의 자주 사용되는 집계함수 • 사용 예 SELECT AVG(amount) AS [평균구매개수] FROM buyTbl SELECT userid, AVG(amount*1.0) AS [평균구매개수] FROM buyTbl GROUP BY userid SELECT Name, height FROM userTbl WHERE height = (SELECT MAX(height)FROM userTbl) OR height = (SELECT MIN(height)FROM userTbl) 기본 T-SQL 구문 : 집계 함수

10. <실습3> 집계함수와 일반 SQL문의 성능 비교 • 실습 목표 • 집계함수를 잘 활용하면 일반 SQL문보다 빠른 성능을 얻을 수 있다는 것을 파악한다. • 실행 결과

11. 집계함수의 조건을 잘못 사용하는 경우 SELECT userid AS [사용자], SUM(price*amount) FROM buyTbl WHERE SUM(price*amount) > 1000 GROUP BY userid • HAVING 절의 사용 SELECT userid AS [사용자], SUM(price*amount) FROM buyTbl GROUP BY userid HAVING SUM(price*amount) > 1000 기본 T-SQL 구문 : HAVING 절

12. COMPUTE  전체를 나열한 후에, 그 집계결과가 출력 SELECT *, price*amount AS [가격] FROM buyTbl COMPUTE SUM(price * amount) COMPUTE AVG(price * amount) • COMPUTE BY 각 소그룹 단위로 집계함수를 사용 SELECT *, price * amount FROM buyTbl ORDER BY groupName COMPUTE SUM(price * amount) BY groupName COMPUTE AVG(price * amount) BY groupName • ROLLUP / GROUPING() / CUBE  총합 또는 중간합계가 필요시에 SELECT num, groupName, SUM(price * amount) AS [비용] FROM buyTbl GROUP BY groupName, num WITH ROLLUP 기본 T-SQL 구문 : 결과의 요약

13. 개념 • WITH 절은 CTE(Common Table Expression)을 표현하기 위한 구문임 • CTE는 기본의 뷰, 파생테이블, 임시테이블을 대신할 수 있으며 더 간결하게 표현됨 • ANSI-SQL99 표준으로 제안됨 • 재귀적 CTE와 비 재귀적 CTE로 구분됨 • 비 재귀적 CTE • 단순한 형태로 복잡한 쿼리문을 단순화 시키는데 적합 • 구문 형식 WITH CTE_테이블이름(열이름) AS ( <쿼리문> ) SELECT 열이름 FROM CTE_테이블이름 ; 기본 T-SQL 구문 : WITH절과 CTE (1)

14. 비 재귀적 CTE • CTE의 예 WITH abc(userid, total) AS (SELECT userid, SUM(price*amount) FROM buyTbl GROUP BY userid ) SELECT * FROM abc ORDER BY total DESC ; • CET의 작동 기본 T-SQL 구문 : WITH절과 CTE (2)

15. 재귀적 CTE • 자기 자신을 반복적으로 호출하는 의미 • 회사의 조직도 등이 대표적인 예 • 구문 형식 WITH CTE_테이블이름(열이름) AS ( <쿼리문1 : SELECT * FROM 테이블A > UNION ALL <쿼리문2 : SELECT * FROM 테이블A JOIN CTE_테이블이름> ) SELECT * FROM CTE_테이블이름; 기본 T-SQL 구문 : WITH절과 CTE (3)

16. <실습4> 회사 조직도 출력을 위한 재귀적 CTE • 실습 목표 • 재귀적 CTE를 사용해서 회사의 조직도를 출력하는 방법을 익힌다. • 실행 결과

17. DML(Data Manipulation Language: 데이터 조작 언어) • 데이터의 선택, 삽입, 수정, 삭제에 사용됨 • 대상은 테이블의 행 • SELECT, INSERT, UPDATE, DELETE 가 해당됨 • 트랜잭션(Transaction)을 발생시킴 (select 제외) • DDL(Data Definition Language: 데이터 정의 언어) • 데이터베이스 개체를 생성/삭제/변경하는데 사용됨 • CREATE, DROP, ALTER 등이 해당됨 • 트랜잭션을 발생시키지 않음 • DCL(Data Control Language: 데이터 제어 언어) • 사용자에게 권한부여시에 주로 사용됨 • GRANT, REVOKE, DENY 등이 해당됨 기본 T-SQL 구문 : T-SQL의 분류

18. 데이터의 삽입 (Insert) • 구문 형식 INSERT [INTO] <테이블> [(열1, 열2, …)] VALUES (값1, 값2 …) • 사용 예 INSERT INTO testTbl1 VALUES (1, '희동구', 25) INSERT INTO testTbl1(id, userName) VALUES (2, '아드복‘) • IDENTITY 지정시에는 입력값을 생략 / DEFAULT 값 CREATE TABLE testTbl2 (id int IDENTITY, userName nchar(3), age int, nation nchar(4) DEFAULT '대한민국'); GO INSERT INTO testTbl2 VALUES ('구엘루', 30, DEFAULT) • 대량의 데이터를 입력 : INSERT INTO … SELECT INSERT INTO 테이블이름 (열이름1, 열이름2, …) SELECT 문 기본 T-SQL 구문 : 데이터 변경(Insert,Update,Delete)(1)

19. 데이터의 수정 (Update) • 구문 형식 UPDATE 테이블이름 SET 열1=값1, 열2=값2 … WHERE 조건 • 사용 예 UPDATE testTbl3 SET tel = '없음' WHERE Fname = 'Kim' • 데이터의 삭제 (Delete) • 구문 형식 DELETE 테이블이름 WHRER 조건 • 사용예 DELETE testTbl3 WHERE Fname = 'Kim' 기본 T-SQL 구문 : 데이터 변경(Insert,Update,Delete)(2)

20. <실습5> 대용량의 테이블 삭제 • 실습 목표 • 테이블을 삭제하기 위한 방법(Delete, Drop, Truncate)들을 알아 보고, 성능에 효율적인 것을 확인해 보자. • 실행 결과

21. SQL Server의 데이터 형식: 데이터 형식의 종류 (1) • 숫자형 데이터 형식

22. SQL Server의 데이터 형식: 데이터 형식의 종류 (2) • 문자형 데이터 형식

23. SQL Server의 데이터 형식: 데이터 형식의 종류 (3) • 시간형 데이터 형식 • 기타 데이터 형식

24. SQL Server의 데이터 형식: 데이터 형식의 종류 (4) • 사용자 정의 데이터 형식 • 개념 : 기존의 데이터 형식에 별칭을 붙이는 것으로, 사용의 편의성을 위해 사용 • 구문 형식 sp_addtype ‘사용자정의 데이터형식 이름’, ‘시스템 데이터 형식’,‘NULL 여부’,‘소유자’ • 사용 예 EXEC sp_addtype 'typeName', 'nchar(10)', 'NULL', 'dbo‘ • VARCHAR(MAX), NVARCHAR(MAX), VARBINARY(MAX) • SQL Server 2005에서 LOB(Large Object: 대량의 데이터)를 저장하기 위해서 지원하는 형식 • 유니코드 데이터 • 문자 데이터를 저장하고 관리할 경우, 각 국가별 코드 페이지가 달라서 서로 호환되지 않는 문제점을 해결 • 데이터형식은 nchar, nvarchar, ntext 이 있음 • 입력시에는 N’문자열’ 형식으로 사용하는 것을 권장함

25. SQL Server의 데이터 형식 : 변수의 사용 • 일시적으로 사용되며, 실행후에는 바로 소멸됨 • 사용 형식 • 변수의 선언: DECLARE @변수이름 데이터형식 • 변수에 값 대입: SET @변수이름 = 변수의값 • 변수의 값 출력: SELECT @변수이름 • 실습 목표 • 변수의 사용법을 익힌다. <실습6> 변수의 사용

26. SQL Server의 데이터 형식 : 데이터형식 관련 시스템 함수 (1) • CAST() , CONVERT() : 데이터형 변환 함수 • 개념 • 데이터의 형식을 변환해 주는 함수 • 구문 형식 • CAST ( expression AS 데이터형식 [ (길이 ) ]) • CONVERT ( 데이터형식[(길이)] , expression [ , 스타일 ] ) • 사용 예  정수를 실수로 변환 • SELECT AVG( CAST(amount AS FLOAT) ) AS [평균구매개수] FROM buyTbl • SELECT AVG( CONVERT(FLOAT, amount) ) AS [평균구매개수] FROM buyTbl • 명시적 형변환 / 암시적 형변환 • 명시적 형변환 • CAST() 또는 CONVERT()를 사용해서 형을 변환하는 것 • 암시적 형변환 • CAST() 또는 CONVERT()를 사용하지 않고, 내부적으로 자동으로 형이 변환되는 것 • 예로 ‘문자 + 정수 = 정수’ , ‘문자 + 실수 = 실수’ 등의 변환 등. • 사용자의 착오로 인해서 엉뚱한 결과가 나올 수 있으므로 주의

27. SQL Server의 데이터 형식 : 데이터형식 관련 시스템 함수 (2) • 스칼라 함수 • 개념 • 단일 값에 적용되어 단일 값의 결과 함수 • 종류로는 구성함수, 커서 함수, 날짜/시간함수, 수치함수, 문자열 함수 등. • 스칼라 함수의 일부 예

28. <실습7> MAX데이터 형식의 활용 • 실습 목표 • SQL Server 2005의 새로운 형식인 VARCHAR(MAX) 및 NVARCHAR(MAX)형태의 특징을 이해하고, 관련 함수의 사용법을 익힌다. • 실습 요약 • MAX 형 데이터의 정의 • 큰 데이터 (1백만개 문자) 입력 및 오류 원인 파악 • CAST(), CONVERT() 함수를 이용한 형변환 • 문자열 함수의 활용과 관련함수의 성능비교

29. SQL Server의 데이터 형식 : 데이터형식 관련 시스템 함수 (3) • 순위 함수 • 개념 • 순번(또는 순위)를 처리하기 위한 함수 • SQL Server 2005에서 새로이 제공 • RANK(), NTILE(), DENSE_RANK(), ROW_NUMBER() 가 있음 • 구문 형식 <순위함수이름>( ) OVER( [PARTITION BY <partition_by_list>] ORDER BY <order_by_list>) • 실습 목표 • 순위함수의 사용법을 익힌다. • 실습 요약 • 키 순서로 순위를 부여 : ROW_NUBER() • 그룹별로 키 순서로 순위 부여 : PARTITION BY 절 사용 • 동일한 점수를 같은 등수로 처리 : DENSE_RANK() • 동일 점수시에 등수를 건너 뜀 : RANK() • 몇 개의 그룹으로 분할 : NTILE() <실습8> 순위 함수

30. 조인 (Join) : INNER JOIN (내부조인) (1) • 조인의 개념 • 두개 이상의 테이블을 서로 묶어서 하나의 결과집합으로 만들어 내는 것 • INNER JOIN 개념 • 조인 중 가장 많이 사용됨. • 일반적인 조인은 이 INNER JOIN을 얘기하는 것임. • 구문 형식 SELECT <열 목록> FROM <첫 번째 테이블> INNER JOIN <두 번째 테이블> ON <조인될 조건> [WHERE 검색조건] • 사용 예 : 구매 테이블에서 중에서‘LCS’라는 아이디를 가진 사람이 구매한 물건을 발송하기 위해서, 이름/주소/연락처 등을 조인 USE sqlDB SELECT * FROM buyTbl INNER JOIN userTbl ON buyTbl.userid = userTbl.userid WHERE buyTbl.userid = 'LCS'

31. 조인 (Join) : INNER JOIN (내부조인) (2) • INNER JOIN 의 결과행 및 작동순서

32. 조인 (Join) : INNER JOIN (내부조인) (3) • 별칭(Alias)의 사용 • 테이블의 별칭을 사용함으로써 코드를 간결화 할 수 있다. • 별칭을 사용하기 전 SELECT buyTbl.userid, userTbl.name, buyTbl.prodName, userTbl.addr, userTbl.mobile1 + userTbl.mobile2 AS [연락처] FROM buyTbl INNER JOIN userTbl ON buyTbl.userid = userTbl.userid • 별칭을 사용한 후 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

33. <실습9> 3개 테이블의 내부 조인 • 실습 목표 • 테이블의 정의 및 데이터입력을 복습한다. • 3개 테이블의 조인 방법을 익힌다. • 실습 테이블 샘플

34. 조인 (Join) : OUTER JOIN (외부 조인) (1) • 개념 • 내부조인은 조인의 조건인 만족되지 않는 행은 출력되지 않지만, 외부조인은 조건이 만족되지 않는 행까지 출력됨. • 구문 형식 SELECT <열 목록> FROM <첫 번째 테이블(LEFT 테이블)> <LEFT | RIGHT | FULL> OUTER JOIN <두 번째 테이블(RIGHT 테이블)> ON <조인될 조건> [WHERE 검색조건]

35. 조인 (Join) : OUTER JOIN (외부 조인) (2) • 내부조인과 외부조인의 비교 • 내부 조인과 결과 SELECT U.userid, U.name, B.prodName, U.addr, U.mobile1 + U.mobile2 AS [연락처] FROM userTbl U INNER JOIN buyTbl B ON U.userid = B.userid ORDER BY U.userid • 외부조인과 결과 SELECT U.userid, U.name, B.prodName, U.addr, U.mobile1 + U.mobile2 AS [연락처] FROM userTbl U LEFT OUTER JOIN buyTbl B ON U.userid = B.userid ORDER BY U.userid

36. <실습10> 3개 테이블의 외부 조인 • 실습 목표 • 3개 테이블의 외부 조인 방법을 익힌다. • LEFT OUTER JOIN과 RIGHT OUTER JOIN 이 합쳐진 FULL OUTER JOIN 의 방법을 익힌다. • 실습 요약 • <실습9>와 비교하여 내부조인과 외부조인의 차이점을 비교 • 동아리에 가입하지 않은 학생도 출력  LEFT JOIN • 가입학생이 한명도 없는 동아리도 출력  OUTER JOIN • 동아리에 가입되지 않은 학생 및 학생이 한명도 없는 동아리를 모두 출력  FULL JOIN

37. 조인 (Join) : CROSS JOIN (상호조인) • 개념 • 한쪽 테이블의 모든 행들과 다른쪽 테이블의 모든 행을 조인시키는 기능. • 주로 테스트 용도로 대용량의 테이블을 생성시에 사용 • 상호 조인의 방식 SELECT * FROM buyTbl CROSS JOIN userTbl

38. 조인 (Join) : SELF JOIN (자체조인) • 개념 • 자기자신과 자기자신이 조인한다는 의미. • 주로 조직도 등에서 많이 활용됨 • 자체 조인의 예 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 = ‘우대리’ • 실행 결과 부하직원 직속상관 직속상관부서 ----------------------------- 우대리 이부장 재무부 (1개 행 적용됨)

39. 조인 (Join) : UNION, UNION ALL • 개념 • UNION은 두 쿼리의 결과를 행으로 합치는 것.(단, 중복 제거) • UNION ALL 은 중복까지 허용함 • UNION의 결합 과정

40. SQL 프로그래밍 : IF…ELSE (조건에 따라 분기) • 사용 예 • DECLARE @var1 INT • -- @var1 변수 선언 • SET @var1=100 • -- 변수에 값 대입 • IF @var1 = 100 • -- 만약 @var1 이 100이라면, • BEGIN • PRINT '@var1 이100 이다' • END • ELSE • BEGIN • PRINT '@var1 이100이아니다.' • END • 개념 참과 거짓에 의해서 실행해야 할 구문이 다를 때 사용 • 구문 형식 IF <부울 표현식> BEGIN SQL문장들1.. END ELSE BEGIN SQL문장들2.. END

41. SQL 프로그래밍 : CASE (다중 분기) • 개념 여러 개의 경우에 따라서 서로 다른 구문을 수행할 경우에 IF문보다 간결하게 사용할 수 있음. • 사용 예 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 '취득점수==> ' + CAST(@point AS NCHAR(3)) PRINT '학점==> ' + @credit

42. <실습11> CASE 문 실습 • 실습 목표 • 구매한 액수에 따라서 최우수고객/우수고객/일반고객 등으로 분류하기 위해서 CASE문을 활용해 본다. • 결과 SQL 구문 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 N'유령고객' 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

43. SQL 프로그래밍 : WHILE,BREAK,CONTINUE,RETURN • WHILE • 개념 : 참인 동안에 계속 반복되는 반복문 • CONTINUE와 BREAK • 개념 : CONTINUE는 WHILE문의 비교문으로 이동하고, BREAK는 WHILE문을 빠져나감 • 사용 예 DECLARE @i INT -- 1에서 100까지 증가할 변수 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))

44. SQL 프로그래밍 : GOTO, TRY…CATCH • GOTO • 개념 • 지정한 위치로 무조건 이동함. • 되도록 사용하지 않는 것이 좋다. • TRY … CATCY • 개념 • SQL Server 2005에서 새로이 소개됨. • 오류를 처리하는 데 편리하고 강력함 • 구문 형식 BEGIN TRY 원래 사용하던 SQL 문장들 END TRY BEGIN CATCH 만약 BEGIN … TRY에서 오류가 발생하면 처리할 일들 END CATCH • 사용 예 • USE sqlDB • BEGIN TRY • INSERT INTO userTbl VALUES('AJH', '우주희', 1988, '서울', NULL, NULL, 170) • PRINT N'정상적으로입력되었다.' • END TRY • BEGIN CATCH • PRINT N'오류가발생했다.' • END CATCH