[SQL SERVER] Transact SQL 고급

RESTORE DATABASE db FROM DISK = '폴더 주소' WITH REPLACE:

: data복원

CAST(amount AS FLOAT)

CONVERT (FLOAT, amount)

: 데이터 타입 변환

PARSE('2017년 09월 09일' AS DATE);

: 만약 정상적으로 실행이 되지 않는다면 오류 발생

TRY_PARSE('123.45' AS INT);

: 정상적으로 실행되지 않는다면 NULL값 RETURN

UPDATE table SET

col1 = STUFF((SELECT col1 FROM table),999991,10,REPLICATE('c',10)),

col2 = STUFF((SELECT col2 FROM table),999991,10, REPLICATE('다',10));

UPDATE table SET col1.WRITE('DDDDD',999996,5), col2.WRITE('라라라라라',999996,5);

: 둘중에서 col1.WRITE가 훨씬 속도가 빠름

SELECT ROW_NUMBER() OVER(ORDER BY height DESC) [키큰순위], name, addr, height, FROM  table;

: height에 따라서 순위가 매겨짐

SELECT ROW_NUMBER() OVER(PARTITION BY addr ORDER BY height DESC, name ASC) [지역별키큰순위], name, height, FROM  table;

: 지역별로 순위를 나눔

SELECT DENSE_RANK() OVER(ORDER BY height DESC)[키큰순위], name, addr, height FROM table;

: 같은 값이면 같은 순위가 나오게

SELECT NTILE(2) OVER(ORDER BY height DESC) [반번호], name, addr, height FROM table;

: 전체 인원을 키순으로 세운 후 그룹으로 분할

SELECT height - (LEAD(height,1,0) OVER (ORDER BY height DESC)) FROM table;

: 다음 사람과의 키차이를 나타내줌

+) LAG (이전 사람과의 차이)

SELET height - (FIRST_VALUE(height) OVER (PARTITION BY addr ORDER BY height DESC)) 

FROM table;

: 지역별로 키 큰사람과의 차이

SELECT (CUME_DIST() OVER (PARTITION BY addr ORDER BY height DESC)*100 FROM table;

: 지역별로 키를 정렬하여 지역별 자신보다 키가 같거나 큰 사람의 백분율을 구함

SELECT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY height) OVER (PARTITION BY addr) from table;

: 지역별 중앙값을 구함 (0<0.5<1 : 중앙)

PIVOT

SELECT * FROM table

PIVOT (sum(amount) FOR season IN ([봄],[여름], [가을], [겨울]))

JSON

ex)

DECLARE @json VARCHAR(MAX);

SET @json = N'{'table':{{'name':'name1'},{'name':'name2'}}}

SELECT ISJSON(@json)

SELECT JSON_QUERY(@json, '$.table[0]'); -- {'name':'name1'}

SELECT JSON_VALUE(@json, '$.table[0].name'); -- name1

SELECT * FROM OPENJSON(@json, '$.table')

WITH(

name NCHAR(8) '$.name',

height INT '$.height');

JOIN

: join에 대한 개념은 알아서..

OUTER JOIN 

: 조건에 맞는 것이 없어도 데이터는 출력

SELECT <left table>

FROM table

<LEFT | RIGHT | FULL> OUTER JOIN <right table> ON <조건>

WHERE 검색조건;

: LEFT -> 왼쪽 테이블은 모두 나옴

 RIGHT -> 오른쪽 테이블은 모두 나옴

 FULL -> 양쪽 다 없어도 모두 나와라

+) CROSS JOIN(카탈린 곱 -> 10개 데이터가 있는 table * 12개 데이터가 있는 table) = 120

 / SELF JOIN -> 자기 자신과 자기 자신이 join

 / UNION -> 두 쿼리의 결과를 하나로 합침

             원래 UNION은 중복된 데이터는 나오지 않음 but UNION ALL은 중복된 데이터가 나옴

EXCEPT

: 뒤에 나오는 하위 Query 조건을 제외한 결과값 출력

IF 조건식

BEGIN

SQL

END

ELSE

BEGIN

SQL

END

SET @credit = 

CASE

WHEN (@point >=90) THEN 'A',

WHEN (@point >=80) THEN 'B',

ELSE 'F'

END

WHILE (@i<=100)

BEGIN

처리

IF (@i%7=0)

BEGIN

CONTINUE

END

SET @hap += @i

IF (@hap >1000) BREAK

SET @i+= 1

END

GOTO

: 넘어감~

GOTO endprint

로 하면

endprint:

PRINT N'안녕'

WAITFOR

: 일정 시간동안 멈춤

BEGIN

WAITFOR DELAY '00:00:05';

END

TRY/CATCH

: error handling

BEGIN TRY

원래 Query

END TRY

BEGIN CATCH

오류 처리

ex) PRINT ERROR_NUMBER() / PRINT ERROR_MESSAGE()

END CATCH

RAISERROR/THROW

: 강제로 ERROR 발생

RAISERROR(N'오류',16,1);

THROW 5555, N'오류',1;

EXEC(문자열)

:문자열을 실행

SET @sql = 'SELECT * FROM table WHERE name = ''name1'' '

EXEC(@sql)