[MsSQL] MsSQL의 기본 문법
MsSQL
MsSQL이란 Microsoft에서 개발한 RDBMS(관계형 데이터베이스 관리 시스템)이다.
데이터를 테이블(행과 열의 집합) 형태로 구성하고 관리하며,
표준 SQL의 형식을 유지하기 때문에 MySQL과 큰 차이가 있지는 않다.
- 주로 Windows OS에 최적화 (Linux와 docker에서도 동작 잘 됨)
- 대규모 기업 환경에서 높은 성능과 안정성을 제공하며 복잡한 트랜잭션 처리에 강점을 보이기 때문에 특히 보안을 중시하는 기업에서 많이 사용하는 편
- SSMS(SQL Server Management Studio)라는 강력한 그래픽 관리 도구를 제공
- 일반적으로 유료로 제공
기본문법
표준 SQL을 준수하기 때문에,
MySQL과 굉장히 비슷하다
MsSQL만의 문법이라기보다는,
그냥 전반적으로 문법에 대하여 정리해보겠다
WITH 절을 통한 서브쿼리문
공통 테이블 식(Common Table Expression) 정의에서 사용
임시 테이블처럼 활용 가능
WITH [CTE name] AS (
select [column_name]
from [database].[table_name]
)
SELECT [column_name]
FROM [CRE name];with 뒤에 임시 결과 집합(CTE)를 정의
이후, 메인 쿼리에서 "마치 하나의 테이블"처럼 사용이 가능하다
with를 이용한 특징1. 단순 서브쿼리 대체
WITH DeptAvg AS (
SELECT dept_id, AVG(salary) AS avg_salary
FROM employees
GROUP BY dept_id
)
SELECT e.emp_name, e.salary, e.dept_id
FROM employees e
JOIN DeptAvg d ON e.dept_id = d.dept_id
WHERE e.salary > d.avg_salary;DeptAvg라는 공통 테이블을 만들어 부서별 평균 급여를 계산한 뒤에 메인 쿼리에 활용
with를 이용한 특징2. 여러 CTE를 대체
WITH Sales AS (
SELECT emp_id, SUM(amount) AS total_sales
FROM orders
GROUP BY emp_id
),
HighSales AS (
SELECT emp_id
FROM Sales
WHERE total_sales > 100000
)
SELECT e.emp_name, s.total_sales
FROM employees e
JOIN Sales s ON e.emp_id = s.emp_id
JOIN HighSales h ON e.emp_id = h.emp_id;여러 개의 CTE를 정의해서 단계별로 가독성 있는 쿼리문을 작성 가능
- WITH 문법의 장점
- 복잡한 서브쿼리를 분리해 가독성을 높일 수 있음
- 동일한 서브 쿼리 여러번 사용 시에 효율적임
- 재귀 쿼리 작성이 가능 EX. 계층 구조 데이터 탐색
BEGIN ... END
여러 개의 문장을 하나의 블록(Statement)으로 묶고 싶을 때 사용한다.
보통 조건문(IF), 반복문(WHILE), TRY…CATCH 같은 제어문 안에서 자주 사용한다.
단독으로도 쓸 수 있지만, 보통은 "여러 구문을 하나로 묶어서 실행해야 할 때" 쓰임!
IF NOT EXISTS (SELECT * FROM [db].[table_name] WHERE name = 'tabel_name')
BEGIN
CREATE TABLE tabel_name (
Step NVARCHAR(255),
StepDesc NVARCHAR(255)
);
PRINT('tabel_name 테이블 생성 완료');
END정리하자면, BEGIN .. END는 여러 SQL문을 하나로 묶어서 제어 구문 안에서 실행할 때 사용한다.
단순히 CREATE TABLE + PRINT만 실행할 거라면 없어도 무방함!!!
LEFT JOIN, INNER JOIN
단순 select로 어려운 경우 join을 이용하여 sql의 테이블 생성하여 조회하는 건 똑같다.
- JOIN
SQL에서 JOIN은 여러 테이블을 결합해 원하는 데이터 조회 가능하며 기본 문법은 동일
- INNER JOIN : 두 테이블에서 조건에 일치하는 행만 조회 (교집합)
SELECT c.CustomerID, c.Name, o.OrderDate
FROM dbo.Customer AS c
INNER JOIN dbo.[Order] AS o
ON o.CustomerID = c.CustomerID;- LEFT JOIN (OUTER JOIN) : 왼쪽 테이블의 모든 행을 유지하면서, 오른쪽 테이블에서 조건이 맞는 데이터만 매칭하고, 오른쪽에 데이터가 없으면 NULL로 채움
SELECT c.CustomerID, c.Name, o.OrderDate
FROM dbo.Customer AS c
LEFT JOIN dbo.[Order] AS o
ON o.CustomerID = c.CustomerID;파일 헤더를 읽어 동적으로 CREATE TABLE 하기(T-SQL)
동적 테이블 생성하는 방법에 대하여 보자
- CREATE TABLE 구문을 만든 뒤 EXEC 또는 sp_executesql로 실행하는 것이 가능
아래는 단순 동적 테이블 생성의 예시이다.
DECLARE @TableName NVARCHAR(128) = N'DynamicTable1';
DECLARE @SQL NVARCHAR(MAX);
SET @SQL = N'CREATE TABLE ' + QUOTENAME(@TableName) + N'(
ID INT IDENTITY(1,1) PRIMARY KEY,
Name NVARCHAR(100),
CreatedDate DATETIME DEFAULT GETDATE()
);';
EXEC(@SQL);
이러면 실행 후 DynamicTable1 테이블이 생성된다.
QUTOENAME()을 사용하면 테이블명에 공백, 예약어, 특수문자가 있어도 안전하게 처리된다.
- 컬럼 리스트를 동적으로 만들어 생성하는 방법도 존재한다.
파일 헤더나 입력 값에 따라서 컬럼 구성이 달라질 때 유용함!!
DECLARE @TableName NVARCHAR(128) = N'DynamicTable2';
DECLARE @Columns NVARCHAR(MAX) =
N'[Col1] NVARCHAR(100),'
+ N'[Col2] INT,'
+ N'[Col3] DATETIME';
DECLARE @SQL NVARCHAR(MAX);
SET @SQL = N'CREATE TABLE ' + QUOTENAME(@TableName)
+ N'(' + @Columns + N');';
EXEC(@SQL);이러면 결과적으로 다음과 같은 테이블이 그려진다.
CREATE TABLE [DynamicTable2](
[Col1] NVARCHAR(100),
[Col2] INT,
[Col3] DATETIME
);- STRING_SPLIT을 활용해 동적 컬럼 생성
CSV 헤더 같은 문자열에서 컬럼명을 분리해 테이블을 자동으로 생성이 가능
DECLARE @Header NVARCHAR(MAX) = 'Name|Age|Email';
DECLARE @TableName NVARCHAR(128) = N'DynamicTable3';
DECLARE @SQL NVARCHAR(MAX) = N'CREATE TABLE ' + QUOTENAME(@TableName) + N'(';
-- 컬럼 목록 만들기
SELECT @SQL = @SQL + STRING_AGG(QUOTENAME(value) + ' NVARCHAR(255)', ',')
FROM STRING_SPLIT(@Header, '|');
SET @SQL = @SQL + N');';
PRINT @SQL; -- 확인용
EXEC(@SQL);실제로는 아래와 같은 테이블이 만들어짐
CREATE TABLE [DynamicTable3](
[Name] NVARCHAR(255),
[Age] NVARCHAR(255),
[Email] NVARCHAR(255)
);- sp_executesql 활용
EXEC 대신 sp_executesql을 쓰면, 매개변수 전달이 가능하고 SQL 인젝션 방어에 유리합니다.
DECLARE @TableName NVARCHAR(128) = N'DynamicTable4';
DECLARE @SQL NVARCHAR(MAX);
SET @SQL = N'CREATE TABLE ' + QUOTENAME(@TableName)
+ N'(ID INT PRIMARY KEY, Description NVARCHAR(200));';
EXEC sp_executesql @SQL;
IF EXISTS + INFORMATION_SCHEMA.TABLES
테이블 존재 여부 확인 후 조건 실행.
IF EXISTS (
SELECT 1
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'schema_name' AND TABLE_NAME = 'table_name'
)
BEGIN
DROP TABLE [schema_name].[table_name]
END변수 할당 (SET) + 서브쿼리/스칼라
SET을 통해서 변수를 할당
DECLARE @FirstJoinDate DATE;
SET @FirstJoinDate = (
SELECT MIN(HireDate)
FROM Employee
WHERE Department = 'Sales'
);
PRINT @FirstJoinDate;- 스칼라 서브쿼리 결과를 변수에 대입.
문자열 치환: REPLACE
문자열을 치환하는 함수
UPDATE #HEADER SET HeadString = REPLACE(HeadString, '#', '');- 헤더에 포함된
#문자 제거.
TRUNCATE TABLE + 변수 초기화
TRUNCATE TABLE #Header; -- 임시 테이블 내용 비우기(구조 유지)
SET @createTbQUERY = ''; -- 다음 사용 대비 초기화MsSQL (Transact-SQL, T-SQL)과 MySQL (표준 SQL 기반)은 기본적인 SQL 문법을 공유하지만, 데이터 정의, 데이터 조작 함수, 페이지네이션(페이징) 처리 등에서 여러 구문상의 차이점을 보이지만,
표준 SQL 형식을 잇는 것은 동일하다.
대표적으로 식별자 인용 부호 차이 등이 존재한다.
테이블이나 컬럼 이름에 예약어(Reserved Keyword)를 사용하거나 공백이 포함된 경우, 각 데이터베이스는 이를 구분하기 위해 다른 기호를 사용
- MSSQL (T-SQL): 대괄호
[테이블명]또는 큰따옴표"테이블명"(설정 필요) - MySQL: 백틱
`테이블명`
다음은 Gemini를 기반으로 모아둔 대표적인 MySQL과 MsSQL의 차이점 정리 표이다.
MsSQL과 MySQL의 주요 문법 구문 차이
| 기능 (구분) | MSSQL (T-SQL) 구문 예시 | MySQL 구문 예시 | 설명 |
|---|---|---|---|
| 자동 증가 (Auto Increment) | 컬럼명 INT **IDENTITY**(1,1) PRIMARY KEY | 컬럼명 INT **AUTO_INCREMENT** PRIMARY KEY | 테이블 생성 시 기본 키(Primary Key)의 자동 증가 기능을 정의하는 키워드가 다릅니다. |
| 상위 N개 선택 (Top N Query) | SELECT **TOP 10** * FROM 테이블; | SELECT * FROM 테이블 **LIMIT 10**; | 검색 결과 중 상위 N개의 행만 가져올 때 사용하는 구문이 다릅니다. |
| 문자열 연결 (Concatenation) | SELECT 'Hello' **+** 'World'; | SELECT **CONCAT**('Hello', 'World'); | MySQL은 CONCAT() 함수를 사용하지만, MSSQL은 + 연산자를 사용합니다. (MSSQL도 CONCONCAT()을 지원하지만, +가 일반적입니다.) |
| NULL 처리 함수 | SELECT **ISNULL**(컬럼, 대체값) FROM 테이블; | SELECT **IFNULL**(컬럼, 대체값) FROM 테이블; | 컬럼 값이 NULL일 때 대체값을 지정하는 함수가 다릅니다. (MSSQL은 COALESCE도 사용 가능) |
| 현재 날짜/시간 | SELECT **GETDATE()**; | SELECT **NOW()**; 또는 SELECT **SYSDATE()**; | 현재 시각을 가져오는 함수가 다릅니다. |
| 날짜 형식 변환 | SELECT **CONVERT**(VARCHAR, 날짜컬럼, 120); | SELECT **DATE_FORMAT**(날짜컬럼, '%Y-%m-%d'); | 날짜/시간 데이터를 원하는 문자열 형식으로 변환하는 방식이 다릅니다. |
| 페이지네이션 (페이징) | SELECT * FROM 테이블 ORDER BY 컬럼 **OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY**; (SQL Server 2012+) 또는 ROW_NUMBER() OVER 사용 | SELECT * FROM 테이블 ORDER BY 컬럼 **LIMIT 10 OFFSET 10**; | 대용량 데이터에서 특정 범위의 데이터를 가져오는(페이징) 구문 방식이 크게 다릅니다. MySQL의 LIMIT 구문이 상대적으로 간단합니다. |
| 시스템 변수/파라미터 | DECLARE @변수명 데이터타입; SET @변수명 = 값; | SET @변수명 = 값; (세션 변수) | T-SQL은 @로 시작하는 변수를 선언하고 사용하지만, MySQL의 사용자 변수는 선언 없이 SET으로 바로 사용할 수 있습니다. |
일반적으로 예약어를 사용하지 않는 한 인용 부호를 생략하고 사용하지만, 구문을 정확히 구분할 때 차이가 나므로 위의 것을 참고해두면 좋다
