일하면서 정리해둔 MSSQL 문법

USE

-- USE를 사용해 데이터베이스 선택
USE DATEBASE_NAME;

변수 선언

-- 지역변수 선언
DECLARE @변수명 VARCHAR(6);
-- 변수에 값 할당
SET @변수명 = '000000';

UPDATE, DELETE

-- UPDATE 문
UPDATE 테이블명
SET 컬럼명 = 
-- UPDATE 문 - JOIN 사용
UPDATE 테이블명 AS A
SET 컬럼명 = 값
  , A.컬럼명 = B.컬럼명
FROM 테이블명 AS A
LEFT JOIN 테이블명 AS B
       ON A.컬럼명 = B.컬럼명
-- DELETE 문
DELETE 테이블명
WHERE 

임시테이블 (가테이블) 생성

-- 임시테이블 (local, global)
-- PK, Null, Check 세 가지 조약조건만 허락
-- local(#)은 프로시저가 종료되면 자동 삭제
-- global(##)은 사용하고 있는 모든 작업이 종료되어야만 삭제
SELECT *
INTO #임시테이블명
FROM 테이블

TOP

-- TOP 함수 - MySQL의 LIMIT 같은 기능
SELECT TOP 100
       *
FROM DATABASE..TABLE

데이터 형변환

-- 데이터 형변환 (CONVERT, CAST)

-- CONVERT는 해당 칼럼의 데이터 형식을 변환한다. 형식 디폴트 : NULL
SELECT CONVERT(data_type(length), expression, style)

-- CAST
SELECT CAST(expression AS data_type(length))

https://learn.microsoft.com/ko-kr/sql/t-sql/functions/cast-and-convert-transact-sql?view=sql-server-ver16

CAST 및 CONVERT(Transact-SQL) - SQL Server

날짜

-- 현재기준 년월일 시분초 출력 
GETDATE()
-- 날짜를 더하거나 뺌
DATEADD(단위, 더하거나 뺄 수, 기준 날짜)
-- 현재 날짜에서 1개월을 뺌
DATEADD(mm, -1, GETDATE())

https://learn.microsoft.com/ko-kr/sql/t-sql/functions/dateadd-transact-sql?view=sql-server-ver16

DATEADD(Transact-SQL) - SQL Server

테이블 내용 추가, 변경

-- 테이블에 새로운 컬럼 추가
ALTER TABLE [TABLE_NAME] ADD [COLUMN_NAME] [DATA_TYPE(LENGTH)]


-- 테이블 새로운 컬럼에 값 할당
UPDATE A -- 테이블 A를 수정할 것
       SET NEW_COLUMN = CASE WHEN B.COLUMN_A = '1' THEN 'X'
                         WHEN B.COLUMN_A = '2' THEN 'Y'
                         ELSE B.COLUMN_A END
       FROM TABLE_A A
       LEFT JOIN TABLE_B B
       ON A.KEY = B.KEY
-- 테이블에 새로운 행 추가
INSERT INTO [TABLE_NAME] VALUES ('','','')

WINDOW FUNCTION

-- 컬럼 기준 행번호를 매기는 기능
ROW_NUMBER() OVER(PARTITION BY 컬럼 ORDER BY 컬럼)
-- 컬럼 기준 순위를 매기는 기능, ex) 1, 2, 2, 4, 5
RANK() OVER(PARTITION BY 컬럼 ORDER BY 컬럼)
-- 컬럼 기준 순위를 매기는 기능, but ex) 1, 2, 2, 3, 4, 5
DENSE_RANK() OVER(PARTITION BY 컬럼 ORDER BY 컬럼)
-- 다음 값을 불러오는 기능
LEAD(컬럼, offset, default) OVER(PARTITION BY 컬럼)
-- 이전 값을 불러오는 기능
LAG(컬럼, offset, default) OVER(PARTITION BY 컬럼)

OPENQUERY

-- OPENQUERY SELECT
SELECT 컬럼명
FROM OPENQUERY(서버별칭, 'SELECT 컬럼명
                         FROM 테이블명')
-- OPENQUERY INSERT
INSERT INTO OPENQUERY(서버별칭, 'SELECT 컬럼명
                                FROM 테이블명')
VALUES('값','값','값',...)
-- OPENQUERY UPDATE
UPDATE OPENQUERY(서버별칭, 'SELECT 컬럼명
                           FROM 테이블명
                           WHERE 조건절')  -- 조건절에 문자열을 사용할 때 ''문자''로 입력
SET 컬럼명 = '값'
-- OPENQUERY DELETE
DELETE OPENQUERY(서버별칭, 'SELECT 컬럼명
                           FROM 테이블명
                           WHERE 조건절')
-- OPENQUERY 테이블 복사
SELECT 컬럼명
INTO 새로운 테이블명
FROM OPENQUERY(서버별칭, 'SELECT 컬럼명
                         FROM 테이블명')
-- OPENQUERY 레코드 복사해오기
INSERT INTO 새로운 테이블명
SELECT 컬럼명 FROM OPENQUERY(서버별칭, 'SELECT 컬럼명
                                       FROM 테이블명')
-- OPENQUERY 레코드 복사하기
INSERT INTO (서버별칭, 'SELECT 컬럼명
                       FROM 테이블명')
SELECT 컬럼명
FROM 새로운 테이블명

DELETE, TRUNCATE, DROP 비교

테이블 원본

이름	주소	연락처
홍길동	서울	010-1234-5678
오대쌀	철원	010-5678-1234
김삿갓	양주	010-8765-4321

 

DELETE 사용

이름	주소	연락처

-> ROW만 삭제되고 INDEX, COLUMN 유지

TRUNCATE 사용

이름

주소

연락처

-> ROW, INDEX 삭제, COLUMN 유지

DROP 사용

-> 완전 삭제

 

팁

• GROUP BY를 통해 간단하게 중복을 제거할 수 있다.
• JOIN의 ON에서 조건을 설정하면, JOIN되기 전 조건문을 설정할 수 있다.
  (JOIN한 후 조건이 실행되면 데이터가 달라질 때 사용하면 좋다.)
• WHERE 절에 1=1 을 통해 쿼리를 작성하면 주석 처리를 통한 조건 ON/OFF가 용이하다.