SQL Server Programming요약

1. SELECT쿼리

  - 조회 쿼리

SELECT [DISTINCT] [TOP] <컬럼별칭과 반환될 컬럼, 표현식, 상수값들>  [INTO] <새로운 테이블명>  FROM <기초 테이블명과 별칭들>  [WHERE] <검색 조건들>  [GROUP BY] <집계 기준 컬럼들>  [HAVING] <집계함수결과에 대한 조건들>  [ORDER BY] <정렬될 기준 컬럼들>

  - 문자열 조회 : 문자열 비교시, =를 이용한 비교는 문자열이 일치해야 한다. (대소문자는 구분 안함)

   만약 특정 문자열을 포함하는 경우를 검색하고 싶다면 LIKE문과 패턴을 사용하자

패턴	설명	예	예제 설명
%	어떤 경우라도	LIKE 'Co%'  LIKE '%co%'	Co로 시작하는 문자열  co가 포함된 문자열
_	어떤 하나의 문자	LIKE '_ean'	ean으로 끝나는 문자열. '_'수만큼 길이가 일치해야 한다.
[]	[]안의 각각의 문자	LIKE '[CK]on%'  LINE '[C-K]on'	C또는 K로 시작하고, 다음이 co인 문자열  C에서 K사이 문자와 on으로 끝나는 문자열
[^]	[^]안의 문자를 제외	LIKE 'de[^l]%'  LIKE '[A^l]%'	de로 시작하며, 다음이 l이 아닌 모든 문자열  A로 시작하고, 다음이 I가 아닌 모든 문자열

  * _의 경우 '_'의 개수가 자리만큼이다. 만약 값 중에, sa, sala,라는 데이터가 있는데, '_a'일 경우 sa만 반환되고, '___a'일 경우 sala가 반환된다. 만약, 자릿수가 정확하지 않으면 '%a'를 사용.

 

  - 특정 범위 검색을 위해서는  : BETWEEN A AND B를 사용.

  - IN : 특정 값이 포함되어 있는지 여부. IN ( 목록 )인데, 해당 목록에는 직접 값을 나열하여도 되고, SELECT를 이용한 쿼리도 가능하다.

  - NULL비교 : ANSI_NULLS를 OFF하지 않으면 IS NULL, IS NOT NULL을 이용하여 null값을 비교해야 한다.

  - ORDER BY : 출력되는 순서를 정렬하여 출력하는데 사용한다.

  - SELECT <컬럼명> INTO a FROM <이후쿼리> : SELECT한 결과를 a라는 테이블을 만들고 삽입할 수 있다.

  - GROUP BY : 요약 정보를 위해 사용할 수 있다. 특정 id들이 가지는 총합, count등을 얻어오는데 유용하다.

 

2. DELETE쿼리 

DELETE [FROM] <데이터삭제할 테이블> [ FROM <검색조건을위한 참조테이블>] [ WHERE <삭제조건>]

  지정된 테이블에서 데이터를 지운다. 삭제 조건을 지정하지 않으면 해당 테이블에 모든 데이터를 지운다.

 

 * TRUNCATE TABLE <테이블명> 으로도 테이블을 초기화(모든 데이터 삭제)할 수 있다. TRUNCATE의 경우 로그를 남기지 않는다. 각 행 별로 삭제를 수행하는 DELETE보다는 빠르다. 그러나 복구는 불가능하다 (DELETE는 가능하다)

 

 *만약 테이블 전체를 없애고 싶다면 (삭제하기 위해서) DROP문을 사용하면 된다. DROP문은 다음과 같이 사용한다

DROP <타입> <이름>

  타입은 삭제하고자 하는 것을 표기한다.

   1) COLUMN : ALTER TABLE에서 컬럼 삭제시

   2) DATABASE : DB를 삭제할 때

   3) TABLE : 테이블 삭제할 때

   4) PROC : 저장된프로시저 삭제시

   5) TRIGGER : 트리거 삭제시

   6) VIEW : 뷰 삭제시

   7) FUNCTION : 사용자 정의함수 삭제시

 

3. UPDATE쿼리

UPDATE <갱신할테이블> SET <갱신컬럼>=<신규값> [FROM <검색조건용참조테이블>][WHERE <검색조건>]

  지정한 테이블의 특정 컬럼의 값을 갱신한다. 모든 행이 같은 값을 가지게 만들지 않으려면 조건을 기입해 주어야 한다.

 

4. INSERT쿼리

INSERT INTO <테이블명> [삽입할컬럼이름] VALUES ( <컬럼의 순서에 맞는 값들> )

 

 - 테이블에 값을 삽입. NULL속성인 경우 삽입 시 명시하지 않아도 된다. 만약 기본값이 있을 경우에 명시하지 않거나, 명시한 경우 DEFAULT를 이용하여 값을 삽입할 수 있다. 자동증가형(IDENTITY)의 경우 IDENTITY_INSERT옵션을 끄지 않으면 대입할 수 없다. 명시하지 않아야 한다.

 - 만약 삽입할 컬럼 이름을 제거하면 모든 컬럼에 대해 순서에 맞추어 값을 넣어주어야 한다. 이 경우에도 IDENTITY는 제외하고 값을 넣어줄 것, 또한, 기본값을 넣어주기 위해서는 DEFAULT키워드를 사용

 예제)

생성쿼리>  CREATE TABLE T2  ( Seq int PRIMARY KEY IDENTITY,    Date smalldatetime NOT NULL DEFAULT(GETDATE()),    ProductID varchar(30) NOT NUL DEFAULT('zz'),    Qty int NULL DEFAULT(1),    ,MyRowVersion RowVersion)  삽입쿼리>  INSERT T2 VALUES(3, '20141203', DEFAULT, DEFAULT, DEFAULT) -> 8101에러  INSERT T2 VALUES('20141203', DEFAULT, DEFAULT, DEFAULT) -> 성공함  INSERT T2 VALUES(DEFAULT, DEFAULT, DEFAULT, 100) -> 273.에러  INSERT T2 VALUES(DEFAULT, DEFAULT, DEFAULT)  -> 213에러. 컬럼수가 맞지 않음

 

 

5. JOIN : 여러 테이블을 쿼리하여 하나의 결과를 만들을 때 사용. 횡적결합(컬럼끼리 합침)

 1) INNER JOIN : 연결 조건을 모두 만족하는 행들을 조회. 가장 일반적

 2) CROSS JOIN : 상호조인. 좌우의 모든 행은 모두 조인한다. 만약 5행, 6행이 조인하면 결과는 30행

 3) OUTER JOIN : 조건을 만족하는 행과 만족하지 못하는 행을 모두 확인하기 위함.

   a) LEFT OUTER : 조인에 있어 좌측 테이블의 항목을 기준으로 모두 확인

   b) RIGHT OUTER : 조인에 있어 우측 테이블의 항목을 기준으로 모두 확인

   c) FULL OUTER : 양측 테이블의 항목을 가지고 모두 확인한다.

 * CROSS와 FULL OUTER의 차이.

   좌측에 행이 m, 우측에 n, 양측에 공통으로 있는 데이터가 a개이면, CROSS의 결과 행의 수는 m * n개이다. 그러나 FULL OUTER의 경우 결과 행의 수는 a + (m-a) + (n-a)이다.  (공통으로 있는 행 + 좌측에만 있는 행 + 우측에만 있는 행)

 

6. UNION : JOIN이 횡적 결합이라면 UNION은 종적 결합. 두 테이블의 데이터를 합친다. 데이터 형식은 동일하거나 변환 가능해야 한다.

 

7. DB에서 사용하는 데이터 형식 :  http://gomnezip.tistory.com/348 참조

  1) 유니코드 문자열 사용시 문자열 앞에 N을 붙여서 처리

  2) sql_variant : 다양한 데이터를 받아오기 위한 데이터형식

 

8. 임시테이블, 테이블 변수

  1) 임시테이블 : #(로컬)또는 ##(글로벌)으로 시작. 테이블 생성은 동일하다.

CREATE TABLE [DB명.][소유자.] <테이블명> (     <컬럼명> <데이터형식> [[DEFAULT <표현식>] |[IDENTITY[(초기값,증가값) [NOT FOR REPLICATION]]]         [ROWGUIDCOL] [ COLLATE <정렬이름>] [NULL | NOT NULL]         [ [<컬럼제약조건들>]|[컬럼명 AS 표현식]|[테이블제약 조건들]] [,...N])    [ON { <파일그룹> | DEFAULT] [TEXTIMAGE_ON{ <파일그룹> | DEFAULT } ]

    - 임시테이블에서는 DB명, 소유자는 필요없다.

 

9. ALTER구문

 기존에 존재하는 내용을 수정할 때, 사용한다.

  ALTER TABLE : 테이블의 내용을 수정.

  ALTER TRIGGER : 트리거를 수정

  ALTER PROC : 프로시저를 수정

  ALTER VIEW : 뷰를 수정

  ALTER FUNCTION : 사용자정의 함수를 수정

 

10. CASE문 : 조건에 따라 값을 변경할 때 사용

쿼리>  SELECT au_fname, au_lname,    CASE state      WHEN 'CA' THEN 'California'      WHEN 'KS' THEN 'KAnsas'      WHEN 'TN' THEN 'Tennessee'      WHEN 'OR' THEN 'Oregon'      WHEN 'MI' THEN 'Michigan'     END AS statename  FROM authors ORDER BY au_lname

 

11. 동적쿼리 : 쿼리를 문자열을 이용하여 만들고, 그 문자열을 EXECUTE를 이용하여 실행

 

12. 트랜잭션

BEGIN TRAN : 트랜잭션 시작을 알림  ...  SAVE TRAN : 트랜잭션의 중간 저장점을 설정  ...  COMMIT TRAN : 트랜잭션의 종료. 작업한 내용을 적용  ROLLBACK TRAN : 트랜잭션의 종료. 작업한 내용을 원복

 

13. 변수의 선언 

DECLARE @변수명 타입

 해당 변수는 한번의 일괄작업 (GO단위)에서만 유효한 범위를 가진다.

 * 임시테이블은 현재 영역에서 생성한 경우, 하위 영역에서 접근 가능하다. 반대는 불가능

 

14. IF..ELSE 

IF <조건문>    BEGIN       ...    END  ELSE    BEGIN      ...    END

 C언어에서의 IF문과 유사하다. 조건이 참이면 IF아래의 구문을 수행하고, 아니면 ELSE의 구문을 수행한다.

단, IF절 아래에서 한 구문만 유효하다. 그래서 블럭을 지정해 주어야 한다. (C에서도 {}로 묶지 않으면 단 한줄만 실행된다. SQL에서는 BEGIN( { )과 END( } )로 블럭을 지정해 준다.)

 

15. WHILE, BREAK, CONTINUE

WHILE <조건문>  BEGIN    ...    CONTINUE;    BREAK;  END

 이 구문 또한 C언어와 유사하다. 조건이 참인 동안 반복해서 실행한다. WHILE루프의 처음부터 다시 실행하고자 할 때에는 CONTINUE를 이용하여 제어를 옮기고, 루프를 탈출하려면 BREAK로 빠져나간다.

 

16. RETURN

 - 현재 실행중인 작업을 종료한다. RETURN문 뒤에 반환값을 정할 수 있다. 단, 정수형만 반환 가능하다. 없으면 0이 반환된다.

 - RETURN은 저장프로시저에서도 동일하다. 정수형 이외의 반환값은 아래에서 설명할 함수영역에 다시 기술함

 

17. 뷰

 - 테이블과 유사하다. 하지만, 실제 데이터를 가지고 있지 않다. 생성구문은 TABLE생성과 유사하다

CREATE [ <소유자>] VIEW <뷰이름> [ (컬럼목록)] [ WITH <뷰속성목록>] AS    <select구문> [WITH CHECK OPTION]

  TABLE대신, VIEW키워드를 사용한다. 뷰 이름에 컬럼 목록을 정해주어도 되고, 명시하지 않으면 원본테이블의 컬럼명을 그대로 사용한다. 그리고, 뷰의 경우 데이터를 보여주는 것이기에, 뷰에 보여줄 내용을 SELECT구문으로 조회해야 함

 

18. 저장 프로시저

CREATE PROC[EDURE] <프로시저명> [;number]     [{<@파라미터> data_type} [VARYING] [= default] [ OUTPUT]][,... n]     [ WITH {RECOMPILE | ENCRYPTION | RECOMPILE,ENCRYPTION} ]     [FOR REPLICATION]  AS    <SQL구문>

  자주 사용하는 구문을 프로시저로 만들면 캐싱되어 유용하다. (최근 SQL서버에서는 모든 구문에 대해 캐싱하기는 한다.) 아니면 특정 계산을 수행할 때에도 유용하다

  프로시저 실행은 EXEC '프로시저명' 으로 호출한다.

  저장 프로시저에서 반환값을 받고 싶다면 파라미터명 뒤에 OUTPUT을 붙인다. RETURN의 경우 정수형만 반환이 가능하고, 하나의 값만 반환이 가능한데, OUTPUT파라미터는 더 다양한 데이터형을 지원하고, 갯수 제한이 없다.

 

19. 사용자 정의 함수

CREATE FUNCTION [<소유자명>.] <함수명>    ( [ {<@파라미터명> [AS] <파라미터타입> [= <기본값>] } [, ... n] ] )  RETURNS <반환할 데이터타입>    [ WITH <함수옵션> [ , ... n] ] [AS]  BEGIN    <함수 body>    RETURN <반환할 값>  END

 - 테이블도 반환할 수 있다.

CREATE FUNCTION [<소유자명>.] <함수명>     ([{<@파라미터명> [AS] <파라미터타입> [=<기본값>]} [, ...n] ] )  RETURN <@반환변수> TABLE <table_type_definition>     [ WITH <function_option>[ , ... n] ] [AS]  BEGIN       <함수본문>       RETURN  END  <table_type_definition> = { ENCRYPTION | SCHEMABINDING}  <function_option> = ({column_definition | table_constraint}[, ...n])

 

20. 트리거

 - 사용자가 호출하지 않는 프로시저. INSERT, DELETE, UPDATE 동작이 수행되면, 호출된다. 호출시점은 트리거 종류와 sp_settriggerorder 프로시저로 정한 순서에 따라 호출된다.

  INSTEAD_OF트리거가 가장 먼저 실행되고, AFTER트리거 중 sp_settriggerorder에서 First로 지정한 트리거가 다음에 실행된다, 그리고 AFTER트리거들이 실행(이 순서는 임의로 실행)되고, sp_settriggerorder에서 Last로 지정한 트리거가 마지막에 실행된다.

 - INSERT, UPDATE, DELETE가 호출되면 inserted, deleted 테이블이 생성되는데 해당 데이터를 참조하여 삽입/삭제되는 값을 조회할 수 있다.

 - 트리거는 테이블에 데이터가 수정되었을 때, 해당 테이블의 무결성 등을 검사하는데 사용할 수 있다.

 

21. 커서

 - 행단위 작업을 할 때 유용하다.

 - 커서의 사용은 다음 5단계를 거친다.

  1) DECLARE : 커서의 선언. 커서의 선언시, 불러올 데이터를 알기 위해 SELECT구문이 필요하다

  2) OPEN : SQL서버를 열고 커서를 채운다.

  3) FETCH : 행을 불러온다.

  4) CLOSE : 커서가 불러온 결과 집합을 해제, 커서가 위치한 행에 대한 잠금을 해제하고 커서를 닫는다

  5) DEALLOCATE : 커서의 참조를 제거한다.