기초 T-SQL

1. 기초 T-SQL

2. SQL과 T-SQL을 비교하여 이해한다. 데이터 정의·제어·조작 언어로 구분된 명령문들을 이해한다. T-SQL의 구문 요소를 이해한다. 프로그램을 작성하기 위한 흐름 제어 요소를 이해한다. 데이터베이스 구조를 만들고 프로그램을 작성하는 데 필요한 데이터 형식을 이해한다. 데이터 형식, NULL/NOT NULL, IDENTITY 속성 등을 이해한다. T-SQL의 각종 시스템 함수를 이해한다. 기본값, 유효성 검사, 기타 제약 조건들과 설정 방법을 이해한다. 공통 테이블 식, TOP 절, INSERT EXEC 문, 조인 및 하위 질의에 기초한 행 갱신 및 삭제 등을 숙지한다.

3. 1. T-SQL 개요 2. T-SQL의 구문 요소 3. T-SQL의 흐름 제어 4. T-SQL의 데이터 형식 5. T-SQL의 시스템 함수 6. 무결성과 제약 조건 7. T-SQL 명령문

4. 1. T-SQL 개요 • SQL과 T-SQL의 차이점 • T-SQL은 SQL의 도입 수준을 주로 구현하고, 그 밖에 SQL의 중간 수준 및 완전 수준의 일부 그리고 마이크로소프트의 독자적인 확장을 구현한 언어다. • ANSI SQL-92는 도입 수준(entry level), 중간 수준(intermediate level) 그리고 완전 수준(full level)으로 구분된다. • SQL과 T-SQL의 비교

5. 1. T-SQL 개요 • SQL과 T-SQL의 공통점 • SQL Server에서는 SQL과 T-SQL을 명확히 구분하지 않는다. • 오라클에서는 SQL과 PL/SQL이 명확히 구분되어 명령어 형식과 이들을 처리하는 시스템도 서로 다르지만, SQL Server에서는 SQL과 T-SQL을 거의 동일하게 취급한다. • SQL Server에서만 작업하는 경우에는 SQL과 T-SQL을 굳이 구분 할 필요가 없다. 그러나 다른 DBMS로 이전할 가능성이 있다면 되도록 SQL 위주로 스크립트를 작성하는 것이 좋으며, 이 경우에는T-SQL에 포함된 SQL을 구분해 낼 수 있어야 한다. • SQL과 T-SQL의 비교

6. 1. T-SQL 개요 • T-SQL(또는 SQL)을 기능에 따라 구분하면 데이터 정의 언어, 데이터 제어 언어, 데이터 조작 언어로 나눌 수 있다. • 데이터 정의 언어(DDL, Data Definition Language) 문 : 데이터베이스의 구조를 생성, 변경 또는 제거하는 명령문 • DDL 문의 구문 • 데이터 정의 언어(DDL) 문

7. 1. T-SQL 개요 • object_kind : DATABASE, DEFAULT, FUNCTION, INDEX, CEDURE, RULE, SCHEMA, STATISTICS, TABLE, TRIGGER, VIEW가 올 수있다. • object_name : 실제 개체의 이름이 온다. • options : 각 명령문에 고유한 각종 옵션들이 뒤이어 온다. • DROP 문의 [ , ...n ]은 object_name이 여러 개 올 수 있음을 의미한다. • 데이터 정의 언어(DDL) 문

8. 1. T-SQL 개요 • 데이터 제어 언어 문 (DCL, Data Control Language) : 데이터베이스의 특정 개체에 대한 액세스나 특정 명령문의 실행을 제어하는 명령문 • 구문 • GRANT 문: 권한을 부여하는 명령문이다. • DENY 문: 권한을 구체적으로 빼앗는 명령문이다. • REVOKE 문: 부여하거나 빼앗았던 권한을 중립 상태로 되돌려 놓는 명령문이다. • 데이터 제어 언어(DCL), 데이터 조작 언어(DML) 문

9. 1. T-SQL 개요 • 데이터 조작 언어(DML, Data Manipulation Language) 문 : 데이터를 추가, 갱신 또는 삭제하는 명령문 • 대표적인 DML 문에는 SELECT, INSERT, UPDATE, DELETE가 있다. • 데이터 조작 언어(DML) 문

10. 2. T-SQL의 구문 요소 • 식별자(identifier): 데이터베이스 내 각종 개체(데이터베이스, 테이블, 열, 저장 프로시저 등)의 이름. 일반 식별자와 구분 식별자가 있다 • 일반 식별자: 표준적인 식별자로서, 다음과 같은 규칙이 있다. • 1∼128자의 문자로 구성된다. 단, 로컬 임시 테이블은 116자다. • 첫 문자는 다음 중 하나여야 한다. - 유니코드 표준 3.2에서 정의된 문자, 밑줄(_), at 기호(@) 또는 숫자 기호(#) 3. 후속 문자는 다음을 포함할 수 있다. - 유니코드 표준 3.2에서 정의된 문자 - 기본 라틴 또는 기타 국가 표준 스크립트의 실수 - at 기호(@), 달러 기호($), 숫자 기호 또는 밑줄 4. 식별자는 T-SQL 예약어가 아니어야 한다(대·소문자 모두). 5. 중간 공백이나 특수 문자, 보충 문자는 사용할 수 없다. • 식별자

11. 2. T-SQL의 구문 요소 • 구분(delimited) 식별자: 일반 식별자의 규칙에 어긋나는 특별한 식별자를 사용하고자 할 때 식별자를 [ ] 또는“ ”로 둘러싸서 만든다 구분 식별자를 활용해 보자. • 식별자 1 CREATE DATABASE [1회용DB]; 2 DROP DATABASE [1회용DB 예제 1

12. 2. T-SQL의 구문 요소 SET QUOTED_IDENTIFIER 옵션을 수정해보자. SET QUOTED_IDENTIFIER 옵션을 OFF로 설정하면“ ”로 둘러싼 식별자에서 오류가 발생한다. 구분 식별자를 사용할 때마다 매번 [ ] 또는“ ”로 둘러싸야 하므로 번거롭고 오류가 발생할 가능성도 커지므로, 특별한 이유가 없다면 일반 식별자를 사용할 것을 권장한다. • 식별자 1 SET QUOTED_IDENTIFIER OFF; 2 CREATE DATABASE "1회용DB"; 예제 2

13. 2. T-SQL의 구문 요소 • 예약어(reserved word):T-SQL에서 특별한 목적으로 사용하기 위해 예약해 놓은 키워드로, 일반 식별자로 사용하면 오류가 발생한다. • 예약어

14. 2. T-SQL의 구문 요소 • 예약어

15. 2. T-SQL의 구문 요소 • 예약어

16. 2. T-SQL의 구문 요소 • 예약어

17. 2. T-SQL의 구문 요소 예약어 ADD를 데이터베이스 이름으로 사용해보자. 오류가 발생한다. 쿼리 편집기에서 모든 키워드는 파란색으로 표시되는데, 이는‘식별자로 사용하지 말라’는 경고 신호등으로 받아들이면 될 것이다. • 예약어 CREATE DATABASE ADD; 예제 3

18. 2. T-SQL의 구문 요소 • 변수(variable) 또는 지역변수 -‘@’로 시작되는 식별자 - 특정데이터 형식의 값을 일시적으로 보관하는 용도로 사용 - 다른일반적인 프로그래밍 언어에서처럼 T-SQL의 변수도 반드시 선언을 해야 하고, 사용하기 전에 값을 할당해야 한다. • 변수 선언과 값 할당에 사용되는 명령문들의 구문 • 변수

19. 2. T-SQL의 구문 요소 • 구문 설명 • DECLARE 문 : 하나 이상의 변수를 선언한다. • data_type : 데이터 형식으로, 4절에서 자세히 다룰 것이다. • = value : 변수에 초기 값을 할당할 수 있다. 상수 또는 식을 할당할 수 있는데, 데이터 형식에 호환성이 있어야 한다. • SET 문 : 변수에 값을 할당한다. • 변수

20. 2. T-SQL의 구문 요소 1 USE AdventureWorksLT; 2 DECLARE @LastNamenvarchar(50); 3 SET @LastName = 'Gates'; 4 SELECT CustomerID, FirstName, LastName, Phone 5 FROM SalesLT.Customer 6 WHERE LastName = @LastName; 변수를 사용해보자. @LastName이라는 변수를 선언하고, 여기에‘Gates’라는 값을 할당한 후에 이를 SELECT 문의 WHERE 절에서 사용해보자. • 변수 예제 4

21. 2. T-SQL의 구문 요소 • 연산자에는 산술 연산자, 대입 연산자, 비트 연산자, 비교 연산자, 논리 연산자, 문자열 연결 연산자, 단항 연산자 등이 있다. • 산술 연산자 • 산술 연산자에는 +, -, *, /, % 등이 있다. • %는 모듈로(modulo) 연산자로, 나머지 계산을 한다. 모듈로 연산자를 사용해보자. PRINT 문은 연산식의 결과를 결과 집합이 아닌 메시지로 돌려준다. • 연산자 예제 5 PRINT 5 % 3;

22. 2. T-SQL의 구문 요소 PRINT 1 & 0; • 대입 연산자 대입 연산자에는 =가 있다. SET 문에서 변수에 값을 할당할 때 또는 SELECT 문에서 열 머리글을 정의할 때 사용된다. • 비트 연산자 비트 연산자에는 &(비트AND), |(비트OR), 그리고 ^(배타적 비트OR) 등이 있다. &(비트 AND) 연산자를 사용해보자. • 연산자 예제 6

23. 2. T-SQL의 구문 요소 PRINT 1 | 0; PRINT 1 ^ 1; |(비트 OR) 연산자를 사용해보자. ^(배타적 비트 OR) 연산자를 사용해보자. • 연산자 예제 7 예제 8

24. 2. T-SQL의 구문 요소 • 비교 연산자 비교 연산자에는 =(같다), ＞(크다), ＜(작다), ＞=(크거나 같다), ＜=(작거나 같다), ＜＞(같지 않다) 등이 있다. • 문자열 연결 연산자 문자열 연결 연산자에는 +가 있다. 문자열을 연결할 때 숫자 데이터 형식은 반드시 문자 데이터 형식으로 변환한 후 사용해야 한다. • 연산자

25. 2. T-SQL의 구문 요소 PRINT ‘ABC’ + 1 + ‘DEF’; 숫자 데이터 형식을 문자열 연결에 그대로 사용해보자. - 오류가 발생한다. • 연산자 예제 9

26. 2. T-SQL의 구문 요소 PRINT ‘ABC’ + CONVERT(char(1), 1) + ‘DEF’; 숫자 데이터 형식을 문자 데이터 형식으로 변환한 후 연결에 사용해보자. CONVERT(char(1), 1) 함수 호출은 정수 1을 char(1) 데이터 형식으로 변환하므로 성공한다. • 연산자 예제 10

27. 2. T-SQL의 구문 요소 PRINT ‘ABC’ + CONVERT(char(1), 1) + ‘DEF’; • 논리 연산자 : NOT, AND, OR 등이 있다. • 단항 연산자 • 단항 연산자에는 +(양수), -(음수), ~(비트NOT; 1의 보수) 등이 있다. • +와 -는 숫자 형식의 식에, ~는 정수 형식의 식에만 적용할 수 있다. ~(비트 NOT; 1의 보수) 연산을 살펴보자. 000000002의 1의 보수인 111111112을 돌려준다. • 연산자 예제 11

28. 2. T-SQL의 구문 요소 • 연산자 우선순위 • 연산자들 사이에는 우선순위(precedence)가 있어 서로 뒤섞여 있는 연산자들의 연산 순서를 결정해준다. • 같은 우선순위를 가진 연산자들이 섞여 있을 때 연산 순서는 왼쪽부터 오른쪽 순으로 진행된다. 그리고 우선순위를 바꾸려면 괄호를 사용하면 된다. 괄호가 중첩될 경우에는 가장 안쪽의 괄호가 먼저 계산된다. • 연산자

29. 2. T-SQL의 구문 요소 • 연산자

30. 2. T-SQL의 구문 요소 PRINT 3 * ( 9 – (4 – 2) ); 중첩된 괄호를 사용해보자. 계산 순서는 (4 - 2) → (9 - 2) → 3 * 7 이 된다. • 연산자 예제 12

31. 2. T-SQL의 구문 요소 • 주석(comment) - 실행되지 않는 문자열로서, 다른 사람이나 본인이 나중에 소스를 봤을 때 쉽게 이해할 수 있도록 소스 중간에 삽입한다. - 주석에는 인라인 주석과 문 주석이 있다. • 인라인 주석 ‘--’로 시작하여 그 줄 끝까지 이어지는 주석으로, 한 줄로 된 주석을 만들거나 명령문 뒤에 이어지는 주석을 만들 때, 그리고 명령문을 비활 성화할 때 사용한다. • 주석

32. 2. T-SQL의 구문 요소 CREATE DATABASE ADD; --> 오류 발생함 명령문 뒤에 이어지는 주석을 만들어보자. 네 개의 명령문들의 실행을 막기 위해 비활성화시켜보자. 1 --SET QUOTED_IDENTIFIER ON; 2 --CREATE DATABASE "1회용DB"; 3 --DROP DATABASE "1회용DB"; 4 --SET QUOTED_IDENTIFIER OFF; • 주석 예제 13 예제 14

33. 2. T-SQL의 구문 요소 • 문 주석 ‘/*’로 시작하고‘*/’로 끝나는 주석으로, 여러 줄로 된 주석을 만들거나 여러 개의 명령문들을 한꺼번에 비활성화할 때 사용한다. 아래의 문 주석에서 각 줄 앞의‘**’는 가독성을 높이기 위한 것인데, 주석 내에서 줄 바꿈이 자주 발생할 경우에는 생략하는 것이 좋다. 1 /* [filename.sql] 2 ** 제목: 3 ** 설명: 4 ** 5 ** 버전: 6 ** 개발 기간: 7 */ • 주석 예제 15

34. 2. T-SQL의 구문 요소 문 주석으로 여러 개의 명령문들을 한꺼번에 비활성화 시켜 보자. - 과거 버전에서는 이런 주석이 적용되지 않고 주석 내의 명령어가실행되는 버그가 종종 있었다. 만약 그럴 경우에는 Ctrl + K, Ctrl ＋ C 를 사용하여 인라인 주석으로 바꿔야 한다. 1 /* 2 SET QUOTED_IDENTIFIER ON; 3 CREATE DATABASE "1회용DB"; 4 DROP DATABASE "1회용DB"; 5 SET QUOTED_IDENTIFIER OFF; 6 */ • 주석 예제 16

35. 2. T-SQL의 구문 요소 • 동적 명령 실행 T-SQL 명령문(들)을 고정적으로 미리 작성해 두는 것이 아니라 실행하기 직전에 동적으로 구성한 후 이를 실행하는 것이다. 사용자의 입력을 받은 후 명령문을 결정해야 하는 경우에 많이 사용된다. • 동적 명령 실행의 구문 • 동적 명령 실행

36. 2. T-SQL의 구문 요소 • 구문 설명 • EXECUTE 또는 생략형 EXEC를 둘 다 쓸 수 있는데, 생략형을 더 많이 쓰는 편이다. • ( )는 생략할 수 없다. • 하나 이상의 문자 형식 변수 또는 문자열 상수를‘+’로 연결하여 명령문을 만든다. • 중간에 숫자 형식이 나올 경우에는 CONVERT 함수 등을 사용하여 반드시 문자 형식으로 변환한 후 연결해야 한다. • 연결할 때 중간에 공백을 삽입하는 것을 잊지 말아야 한다(예: 키워드 두 개가 중간에 공백 없이 붙을 경우에는 오류가 발생한다). • 동적 명령 실행

37. 2. T-SQL의 구문 요소 동적 명령을 구성하고 실행해보자. • 동적 명령 실행 1 DECLARE @column_listvarchar(100), @table_name varchar(20), 2 @where_phrasevarchar(100), @orderby_phrase varchar(100), 3 @sqlvarchar(400); 4 SET @column_list = 'CustomerID, FirstName, LastName, Phone'; 5 SET @table_name = 'SalesLT.Customer'; 6 SET @where_phrase = 'CustomerID < 10'; 7 SET @orderby_phrase = 'LastName'; 8 9 SET @sql = 'USE AdventureWorksLT; SELECT ' + @column_list; 10 SET @sql = @sql + ' FROM ' + @table_name; 11 IF @where_phrase IS NOT NULL 12 SET @sql = @sql + ' WHERE ' + @where_phrase; 13 IF @orderby_phrase IS NOT NULL 14 SET @sql = @sql + ' ORDER BY ' + @orderby_phrase; 15 EXEC( @sql ); 예제 17

38. 2. T-SQL의 구문 요소 • 동적 명령 실행

39. 2. T-SQL의 구문 요소 • 배치(batch) : 한꺼번에 컴파일 및 실행되는 명령어들의 묶음으로‘일괄 처리’ 라고도 한다. 쿼리 편집기 또는 sqlcmd 유틸리티에서는 특수한 명령어인GO를 사용하여 배치의 끝을 알린다. • sqlcmd : 쿼리 편집기의 텍스트 모드 버전으로 명령 프롬프트에서 실행하며, 과거 버전의 osql을 대체한다. • 배치

40. 2. T-SQL의 구문 요소 • 배치를 구성하는 예 • 배치

41. 2. T-SQL의 구문 요소 • 배치-1에서 CREATE DATABASE 문, USE 문 그리고 CREATE TABLE 문은 하나의 배치로 구성될 수 있다. • 만약 배치 내에 구문 오류가 있다면 배치 내의 명령문은 하나도 실행 되지 않고, 다음 배치로 제어가 넘어간다. • 배치-1의 경우, $test1이 구문 오류를 일으키므로 배치-1의 명령문 은 하나도 실행되지 않고 배치-2로 제어가 넘어간다. • 배치-2의 CREATE VIEW 문은 특별히 까다로운 DDL 문으로, 단독 으로 배치를 구성해야 한다. • 배치-3에서는 변수를 선언하고 초기화하고, 사용하는 명령문들을 반드시 하나의 배치 안에 오도록 해야 한다. • 배치

42. 2. T-SQL의 구문 요소 • 스크립트 파일로 저장된 하나 이상의T-SQL 명령문이나 배치다. 스크립트의 확장자는 일반적으로 .sql이다. 저장된 스크립트는 쿼리 편집기나 sqlcmd 등에 로드해서 재실행할 수 있으므로 편리하다. 예) 앞에서 검토했던 동적 명령 실행 예를 스크립트로 저장하고, sqlcmd 에 로드하여 실행할 수 있다. 여기서 -S 옵션은 서버 이름을 지정하는 것인데, [그림 8-3]과 같이 SSMSE SQL Server Management Studio Express의 개체 탐색기에 표시되는 이름(예: WXP1AMD4 \SQLEXPRESS) 을 지정하면 된다. 그리고 -E 옵션은 트러스트된 연결로 설정하는 것이 고, -i 옵션은 입력 파일을 지정하는 것이다. • 스크립트

43. 2. T-SQL의 구문 요소 • 스크립트

44. 2. T-SQL의 구문 요소 C:\> sqlcmd –S WXP1AMD4\SQLEXPRESS –E –I 08-17_ Dynamic.sql • 스크립트

45. 3. T-SQL의 흐름 제어 1 IF (@@ERROR <> 0) 2 BEGIN 3 PRINT '오류 발생!'; 4 RETURN; 5 END • 하나 이상의 명령문들이 한꺼번에 실행되도록 묶어준다. C 계열 언어 (C, C++, 자바, C#)의 { } 블록과 같다고 생각하면 된다. BEGIN...END 문을 살펴보자. • BEGIN…END 문 예제 18

46. 3. T-SQL의 흐름 제어 1 DECLARE @ERR smallint; 2 SET @ERR = 1; 3 IF @ERR <> 0 4 PRINT 'ERROR!'; • 조건에 따라 분기할 때 사용된다. BEGIN...END 문과 같이 사용되는 경우가 많다. ELSE 없는 IF 문을 살펴보자. • IF…ELSE 문 예제 19

47. 3. T-SQL의 흐름 제어 1 DECLARE @ERR smallint; 2 SET @ERR = 1; 3 IF @ERR = 0 4 PRINT 'OK!'; 5 ELSE 6 BEGIN 7 PRINT 'Error code: ' + CONVERT(varchar(10), @ERR); 8 RETURN; 9 END 10 /* ... 다른 명령어들 ... */ IF...ELSE 문을 살펴보자. - BEGIN...END 문이 없다면 무조건 RETURN 문이 실행되므로 주의한다. • IF…ELSE 문 예제 20

48. 3. T-SQL의 흐름 제어 • 단순 CASE 식: 독립적인 값을 비교할 때 많이 사용된다. • 구문 • input_expression : 보통열 이름(SELECT 문 내에서)이나 변수가 온다. • when_expression : input_expression과 비교하는 연산식으로, 일반적으로 문자열, 숫자 등의 상수가 온다. • result_expression : when_expression이 input_expression 과 일치할 때 돌려주는 연산식이 온다. • else_result_expression : when_expression이 모두 실패할 때 돌려주는 연산식이 온다. • CASE 식

49. 3. T-SQL의 흐름 제어 1 USE AdventureWorksLT; 2 SELECT City, Country = 3 CASE City 4 WHEN 'Bothell' THEN 'USA' 5 WHEN 'Dallas' THEN 'USA' 6 WHEN 'Phoenix' THEN 'USA' 7 WHEN 'Montreal' THEN 'Canada' 8 ELSE 'Unknown' 9 END 10 FROM SalesLT.Address 11 WHERE AddressID < 300; Case 식을 사용해보자. -‘Country =’는‘alias = column’형식으로 열 별칭을 지정하는 것이다. City 열의 값이‘Bothell’이면‘USA’를, ...,‘ Montreal’이면‘Canada’를, 그리고 다른 경우는‘Unknown’을 열 값으로 돌려준다. • CASE 식 예제 21

50. 3. T-SQL의 흐름 제어 • CASE 식