[SQL SERVER] Transact SQL 문 기본

동영상 : https://www.youtube.com/watch?v=2PDieWqQtFY&list=PLVsNizTWUw7Gw_ubikxbzecfveoxemsAs&index=16

USE Database_이름;

: 데이터 베이스 사용

USE tempdb;

GO

CREATE DATABASE sqlDB;

GO

: 데이터베이스 생성 전 tempdb로 일단 옮겨놔야한다

EXECUTE sp_helpdb;

: 현재 instance에 있는 데이터베이스와 목록을 보여줌

EXECUTE sp_tables @table_type = " 'TABLE' ";

: 현재 Database에 있는 테이블 확인

EXECUTE sp_columns @table_name='table name',

  @table_owner = 'owner';

: table에 있는 열 찾기

SELECT REVERSE ((SELECT * FROM table));

: subquery의 값을 반대로 출력

SELECT SUBSTRING((SELECT col2 FROM table), 99991, 10);

: 

IDENTITY = auto increment

: IDENTITY 입력을 강제로 하고 싶을 때

SET IDENTITY_INSERT table ON;

GO

INSERT into table VALUES (10);

GO

SET IDENTITY_INSERT table OFF;

: IDENTITY 강제로 변경하고 싶을 때 

EXECUTE sp_help table_이름: -> 테이블 사용

GO

SELECT IDENT_CURRENT (table);

GO

SELECT @@IDENTITY;

: 현재 IDENTITY를 출력

SEQUENCE

: ORACLE과의 호환성을 위해 생성

CREATE SEQUENCE idSEQ

START WITH 1

INCREMENT BY 1;

GO

INSERT INTO table VALUES(NEXT VALUE FOR idSEQ);

만약 이걸 사용하고 싶지 않으면 

CREATE TABLE시

id int DEFAULT (NEXT VALUE FOR idSEQ)라고 설정해준다

(원래는 그냥 비워둠)

ALTER SEQUENCE idSEQ RESTART WITH 12;

: SEQUENCE의 idSEQ의 값이 12로 restart하게 바꿈 

CREATE SEQUENCE idSEQ

START WITH 1

INCREMENT BY 1

MINVALUE 100

MAXVALUE 300

CYCLE;

FOREIGN KEY REFERENCES table(column name) : 외래키 주기

nation nchar(4) DEFAULT '대한민국'

BACKUP DATABASE sqlDB TO DISCK= '폴더 주소' WITH INIT;

: backup 명령어

SELECT * FROM table WHERE height >= (SELECT height FROM table); => 만약 하위 쿼리의 return값이 2이상이라면

SELECT * FROM table WHERE height >= ANY (SELECT height FROM table);

: 둘중 하나만 만족해도 상관없다

SELECT * FROM table WHERE height >= ALL (SELECT height FROM table);

: 둘다 만족해라

SELECT TOP(10) height FROM table

WHERE addr = '강남'

ORDER BY year;

: 10명 출력

SELECT TOP(0.1) PERCENT height FROM table

WHERE addr = '강남'

ORDER BY year;

: 상위 0.1 percent 출력

SELECT TOP(0.1) PERCENT WITH TIES height FROM table

WHERE addr = '강남'

ORDER BY year;

: 같은 값인 것 처리

SELECT * FROM table TABLESAMPLE(5 PERCENT);

: 대략 5%를 뽑아냄

SELECT TOP(5000) * FROM table TABLESAMPLE(5 PERCENT);

: 대략 5%를 뽑아냄 & 상위 5000개

SELECT * FROM table ORDER BY year OFFSET 4 ROWS;

: 5등부터 보고싶다

SELECT * FROM table ORDER BY year OFFSET 4 ROWS FETCH NEXT 3 ROWS ONLY;

: 5등부터 3명만 보고싶다

SELECT * into table1 FROM table2;

: table1에 table2의 값을 넣는다

+) table 이름 앞에 #을 붙이면 임시 테이블이라는 이름으로 사용된다 (꼭은 아니고 약속)

+) mssql 문법

DECLARE @total bigint

DECLARE @priceXamount int

DECLARE @num int

DECLARE @count int

SET @total=0

SET @num=1

SELECT @count=COUNT(*) FROM #table

SET @count = @count+1

WHILE @count<>@num

BEGIN

SELECT @priceXamount = price*amount FROM #tmpTbl

WHERE num = @num

SET @num = @num+1

SET @total = @total + @priceXamount

END

PRINT @total

(또는 SELECT @total 로 출력)

SELECT * FROM table GROUP BY ROLLUP(name, num);

: GROUP 이름에 따라 num을 출력

SELECT GROUPING_ID(groupName) FROM table GROUP BY ROLLUP(groupName);

: GROUP 명령어 때문에 추가된 열인지 아닌지 여부

SELECT GROUPING_ID(groupName) FROM table GROUP BY CUBE(groupName, color);

: 다방면을 GROUP BY 가능 현재 이 쿼리에서는 groupName과 color를 두개를 기준으로 group by

- WITH (CTE : Common Table Expression)

비재귀적 CTE / 재귀적 CTE

WITH abc(id, total) AS (

SELECT id, sum(price*amount)

FROM table group by id)

SELECT * FROM abc ORDER BY total DESC;

: abc라는 임시 테이블에 데이터를 잠시 저장해놓고 사용

INSERT INTO table

SELECT * FROM table1;

: 대량의 데이터 삽입시

DELETE FROM table;

TRUNCATE TABLE table;

의 차이

DELETE : where절을 둘 수 있음

TRUNCATE : where절을 둘 수 없음

MERGE : 중요한 데이터가 있는 테이블일때 바로 쿼리를 진행하지 않고 일정 시간이 지난 이후에 실행 -> MERGE를 이용!

MERGE table AS M --변경될 테이블 (target Table)

MERGE new_table AS C --변경할 기준이 되는 테이블 (source Table)

ON M.userID = C.userID --userID를 기준으로 구분

-- 만약 target Table에 없고 신규가입이라면

WHEN NOT MATCHED AND changeType = '신규가입' THEN

INSERT (id) VALUES(C.id)

-- 만약 target Table에 있고 주소변경이라면

WHEN MATCHED AND changeType = '주소변경' THEN

UPDATE SET M.addr = C.addr

-- 만약 target Table에 있고 회원탈퇴라면

WHEN MATCHED AND changeType = '회원탈퇴' THEN

DELETE;