Transact-SQL 기본
Transact-SQL 정의
국제 표준화 위원회에서는 ANSI/ISO SQL이라는 명칭의 SQL의 표준을 관리하고 있으며,
이 중에서도 1992년에 제정된 ANSI-92 SQL과 1999년에 제정된 ANSI-99 SQL 이라는 명칭의 표준이 대부분의 DBMS 회사에서 적용하는 기준이 되고 있다.
그러나 ANSI-92/99 SQL이 모든 DBMS 제품의 특성을 반영할 수가 없기 때문에, 각 회사들은 자신들의 제품을 특성하는 sql에 별도의 이름을 붙였다. 일례로, SQL Server에서는 Transact-SQL(줄여서 T-SQL)이라고 명명한 SQL문을 사용하고, Oracle에서는 PL/SQL 이라는 이름의 SQL문을 사용한다.
SELECT 문
SELECT 문은 다양한 옵션으로 인해서 전체 구문 형식은 복잡해 보이지만, 실제적으로 요약한 구조는 다음과 같다.
SELECT 열이름
FROM 테이블이름
WHERE 조건
SQL
USE 구문
SELECT 문을 학습하려면 먼저 사용할 데이터베이스를 지정해야 한다. 주로 사용할 데이터베이스는 잠시 후에 만들게 될 ‘sqlDB’와 SQL Server의 샘플 DB인 ‘AdventureWorks’다. 현재 사용하는 데이터베이스를 지정 또는 변경하는 구문 형식은 다음과 같다.
USE 데이터베이스_이름;
-- AdventureWorks를 사용하려면 쿼리창에 다음과 같이 입력한다.
USE AdventureWorks;
SQL
SELECT FROM
SSMS의 쿼리창에서 간단한 SQL문을 수행.
SELECT * FROM HumanResources.Employee;
SQL
*쿼리 수행 결과
결과를 확인할 수 있다. 위 그림의 표시해 놓은 아래쪽 상태 바의 의미를 간단히 살펴보자
•
쿼리가 실행되었습니다 : 정상적으로 쿼리가 실행된 상태를 표시하며, 초록색 아이콘으로 표시된다.
•
DESKTOP-Q2VH2QS(15.0 RTM) : 현재 쿼리를 수행한 필자의 인스턴스는 DESKTOP-Q2VH2QS이며, 버전은 15.0 RTM(2019)이다.
•
DESKTOP-Q2VH2QS￦USER : 쿼리를 수행한 사용자를 의미한다.
•
AdventureWorks : 쿼리가 수행된 데이터베이스다.
•
00:00:00 : 쿼리를 수행하는데 걸린 시간:분:초 다. 1초 미만이 걸리면 00:00:00으로 표시된다.
•
290개의 행 : 쿼리의 결과 행의 수를 나타낸다. 즉, 조건에 맞는 데이터의 건수로 생각하면 된다.
*메세지 탭을 클릭 시 ‘290개 행이 영향을 받음’이라는 정상적인 메세지가 보인다. 해당 쿼리에 오류가 있다면 메세지 행에서 어떤 오류가 발생했는지 확인된다.
테이블의 전체 이름
원래 테이블의 전체 이름은 ‘인스턴스이름.데이터베이스이름.스키마이름.테이블이름’ 형식으로 표현된다.
-- 원칙적인 테이블 입력 방법
USE AdventureWorks;
SELECT * FROM [DESKTOP-Q2VH2QS].AdventrueWorks.HumanResources.Employee;
-- 보편적인 테이블 입력 방법
USE AdventureWorks;
SELECT * FROM HumanResources.Employee;
SQL
여기서 HumanResources 는 스키마이고, Employee는 테이블이다.
간혹 스키마 없이 테이블만 입력하는 경우도 있는데 테이블 앞에 “dbo. “ 가 붙는다. (SQL Server만)
주석 입력 방법
SQL Server에서 ‘--’ 이후 부터는 주석으로 처리된다. 주로 코드에 설명을 달거나, 잠시 해당 부분의 실행을 막고 싶을 때 사용하고, 여러 줄(블록) 주석 처리는 ‘/* */’ 로 묶는다.
-- 한줄 주석 연습
SELECT Name, GroupName -- 이름과 그룹 이름 열을 가져옴
FROM HumanResources.Department;
/* 블록 주석 연습
SELECT Name, GroupName
FROM HumanResources.Department;
*/
SQL
주석으로 묶이게 되면 해당 글자들은 모두 초록색으로 보이게 된다.
DB, 스키마, 테이블 검색 방법
•
SQL Server에서 자체적으로 만든 프로시저를 통해 DB, 스키마, 테이블이름을 검색한다.
EXECUTE sp_helpdb; -- 검색 시 DB 이름, 사이즈, Status 등의 정보가 표시
EXECUTE sp_tables; -- 테이블 이름 출력되고 해당 테이블에 종속되는 스키마, DB이름 표시
EXECUTE sp_columns; -- 컬럼명 출력되고 해당 컬럼에 종속된 정보와 해당 컬럼의 자료 타입 표시
SQL
Alias
열 이름을 별도의 별칭(Alias)으로 지정할 수도 있다. 열 이름 뒤에 ‘AS 별칭’ 또는 그냥 ‘별칭’의 형식으로 붙이면 된다. 하지만, 별칭의 중간에 공백이 있다면 꼭[ ]로 별칭을 감싸줘야 한다.
SELECT DepartmentID AS 부서번호, Name 부서이름, [그룹 이름] = GroupName
FROM HumanResources.Department;
SQL
Database, Table 생성
•
데이터베이스 생성
◦
데이터베이스 생성할 때 다른 DB 한번 사용 후 생성
USE tempdb;
GO
CREATE DATABASE sqlDB;
GO
USE sqlDB;
SQL
•
userTbl 테이블 생성
◦
각 필드 별 속성을 지정한다.
◦
userTbl 테이블에 userID를 PRMARY KEY 지정한다.
◦
속성 앞에 N이 붙는건 한글이다.
◦
CHAR(8)로 선언시 글자를 몇 개를 넣든 8바이트의 공간을 차지한다.
◦
VARCHAR는 반대로 가변형 문자열이기 때문에 데이터의 길이에 따라서 길이가 정해진다.
CREATE TABLE userTbl -- 회원 테이블
( userID CHAR(8) NOT NULL PRIMARY KEY, -- 사용자 아이디
name NVARCHAR(10) NOT NULL, -- 이름
birthYear INT NOT NULL, -- 출생년도
addr NCHAR(2), -- 지역(경기, 서울, 경남 식으로 2글자만 입력)
mobile1 CHAR(3), -- 휴대폰의 국번(011, 016, 017, 018, 019, 010 등)
mobile2 CHAR(8), -- 휴대폰의 나머지 전화번호(하이픈제외)
height SMALLINT, -- 키
mDate DATE -- 회원 가입일
)
SQL
•
buyTbl 테이블 생성
◦
num 필드에 INT IDENTITY 속성 지정하여 자료가 늘어날 때 마다 순번을 지정함 (NOT NULL, PK설정)
◦
userID를 FK로 지정하고 userTbl의 userID를 참조함
CREATE TABLE buyTbl -- 회원 구매 테이블
( num INT IDENTITY NOT NULL PRIMARY KEY, -- 순번(PK)
userID CHAR(8) NOT NULL -- 아이디(FK)
FOREIGN KEY REFERENCES userTbl(userID),
prodName NCHAR(6) NOT NULL, -- 물품명
groupName NCHAR(4), -- 분류
price INT NOT NULL, -- 단가
amount SMALLINT NOT NULL -- 수량
);
SQL
•
데이터 입력
◦
데이터 입력 후 확인
◦
데이터베이스 백업
INSERT INTO userTbl VALUES('LSG', '이승기', 1987, '서울', '010', '1111111', 182, '2008-8-8');
INSERT INTO userTbl VALUES('KBS', '김범수', 1979, '경남', '011', '2222222', 173, '2012-4-4');
INSERT INTO userTbl VALUES('KKG', '김경호', 1971, '전남', '019', '3333333', 177, '2007-7-7');
INSERT INTO userTbl VALUES('JYP', '조용필', 1950, '경기', '011', '4444444', 166, '2009-4-4');
INSERT INTO userTbl VALUES('SSK', '성시경', 1979, '서울', NULL , NULL , 186, '2013-12-12');
INSERT INTO userTbl VALUES('LJB', '임재범', 1963, '서울', '016', '6666666', 182, '2009-9-9');
INSERT INTO userTbl VALUES('YJS', '윤종신', 1969, '경남', NULL , NULL , 170, '2005-5-5');
INSERT INTO userTbl VALUES('EJW', '은지원', 1978, '경북', '011', '8888888', 174, '2014-3-3');
INSERT INTO userTbl VALUES('JKW', '조관우', 1965, '경기', '018', '9999999', 172, '2010-10-10');
INSERT INTO userTbl VALUES('BBK', '바비킴', 1973, '서울', '010', '0000000', 176, '2013-5-5');
GO
INSERT INTO buyTbl VALUES('KBS', '운동화', NULL, 30, 2);
INSERT INTO buyTbl VALUES('KBS', '노트북', '전자', 1000, 1);
INSERT INTO buyTbl VALUES('JYP', '모니터', '전자', 200, 1);
INSERT INTO buyTbl VALUES('BBK', '모니터', '전자', 200, 5);
INSERT INTO buyTbl VALUES('KBS', '청바지', '의류', 50, 3);
INSERT INTO buyTbl VALUES('BBK', '메모리', '전자', 80, 10);
INSERT INTO buyTbl VALUES('SSK', '책', '서적', 15, 5);
INSERT INTO buyTbl VALUES('EJW', '책', '서적', 15, 2);
INSERT INTO buyTbl VALUES('EJW', '청바지', '의류', 50, 1);
INSERT INTO buyTbl VALUES('BBK', '운동화', NULL, 30, 2);
INSERT INTO buyTbl VALUES('EJW', '책', '서적', 15, 1);
INSERT INTO buyTbl VALUES('BBK', '운동화', NULL, 30, 2);
GO
SELECT * FROM buyTbl;
SELECT * FROM userTbl;
USE tempdb;
BACKUP DATABASE sqlDB TO DISK = 'C:\SQL\sqlDB2016.bak' WITH INIT ;
SQL
WHERE 절
•
Where 절은 조회하는 결과에 특정한 조건을 줘서, 원하는 데이터만 보고 싶을 때 사용한다.
USE sqlDB;
-- 이름이 김경호인 사람 조회
SELECT * FROM userTbl WHERE name = '김경호';
-- 1970년 이후에 출생했고 신장이 182 이상인 사람의 아이디와 이름 조회
SELECT userID, Name FROM userTbl WHERE birthYear >= 1970 AND height >= 182;
-- 1970년 이후에 출생했거나 신장이 182 이상인 사람의 아이디와 이름 조회
SELECT userID, Name FROM userTbl WHERE birthYear >= 1970 OR height >= 182;
-- 키가 180~183인 사람 이름 조회 (AND 사용)
SELECT Name, height FROM userTbl WHERE height >= 180 AND height <= 183;
-- 키가 180~183인 사람 이름 조회 (BETWEEN, AND 사용)
SELECT Name, height FROM userTbl WHERE height BETWEEN 180 AND 183;
-- 지역이 '경남', '전남', '경북'인 사람의 정보 확인 (OR 사용)
SELECT Name, addr FROM userTbl WHERE addr = '경남' OR addr = '전남' OR addr = '경북';
-- 지역이 '경남', '전남', '경북'인 사람의 정보 확인 (IN 사용)
SELECT Name, addr FROM userTbl WHERE addr IN('경남', '전남', '경북');
-- 성이 '김'씨인 사람 이름, 키 추출
SELECT Name, height FROM userTbl WHERE name LIKE '김%'; -- '%'는 무엇이든 허용한다는 의미
-- 이름이 '종신'인 사람 이름, 키 추출
SELECT Name, height FROM userTbl WHERE name LIKE '_종신'; -- 한 글자와 매치하기 위해서는 '_' 사용
SQL
ANY/ALL/SOME와 서브쿼리
•
하위 쿼리(서브 쿼리)의 결과값이 2개 이상일 경우 사용한다.
•
서브 쿼리란 SELECT 안에 또 다른 SELECT가 들어간 형태를 의미한다.
SELECT 컬럼명 FROM [테이블A]
WHERE 컬럼명 조건연산자 (SELECT 컬럼명 FROM [테이블B] WHERE 조건)
SQL
•
예제
◦
이름이 ‘꽁쥐’ 인 사람의 값보다, 값이 크거나 같은 사람을 추출
SELECT 번호, 값 FROM A
WHERE 값 >= (SELECT 컬럼명 FROM B WHERE 이름 = '꽁쥐')
SQL
위의 쿼리는 테이블B에서 꽁쥐의 데이터가 2개 존재하기 때문에 오류가 발생한다.
테이블 A에서 값을 출력하려는데, 테이블 B에서의 결과가 2개(10,30)가 나와버려 오류가 발생하게 된 것이다. 이럴 때 ANY, SOME, ALL를 사용하여 해결할 수 있다.
ANY, SOME은 하위 쿼리에서 나온 결괏값에 하나 이상 충족하는 값이 기준이 되고, ALL은 하위 쿼리에서 나온 결괏값을 모두 충족하는 값이 기준이 된다.
ANY, SOME (OR)
•
ANY, SOME은 하위 쿼리에서 나온 결괏값에 하나 이상 충족하는 값이 검색
SELECT 번호, 값 FROM A
WHERE 값 >= ANY (SELECT 컬럼명 FROM B WHERE 이름 = '꽁쥐')
-- ANY : 값 중에 하나라도 만족하면 됨
-- = ANY : 값이랑 정확히 일치해야 함
SQL
•
쿼리 결과
ALL (AND)
•
ALL은 하위 쿼리에서 나온 결괏값에 모두 충족하는 값이 검색
SELECT 번호, 값 FROM A
WHERE 값 >= ALL (SELECT 값 FROM B WHERE 이름 = '꽁쥐')
SQL
•
쿼리 결과
꽁쥐의 값이 10, 30 이기 때문에 테이블 A의 값 중 30보다 큰 데이터를 출력한다.
ORDER BY : 원하는 순서대로 정렬하여 출력
•
ORDER BY 절은 결과물에 대해 영향을 미치지는 않지만, 결과가 출력 되는 순서를 조절하는 구문이다.
•
가입한 순서로 회원들을 출력
SELECT Name, mDate FROM userTbl ORDER BY mDate; -- 기본적으로 오름차순으로 정렬됨
SQL
•
가입 일자 내림차순으로 정렬
SELECT Name, mDate FROM userTbl ORDER BY mDate DESC;
SQL
•
다중 정렬(키가 큰 순서로 정렬하되, 키가 같을 경우 이름 순으로 정렬)
SELECT Name, height FROM userTbl ORDER BY height DESC, Name ASC;
SQL
*ORDER BY 주의사항
•
ORDER BY에 나온 열이 SELECT 에 꼭 있을 필요는 없다.
즉, ‘SELECT userID FROM userTbl ORDER BY height’와 같은 구문을 사용해도 된다.
•
ORDER BY에 사용되는 열의 크기는 전체 8060Byte 이하여야 한다.
•
ntext, text, image, geography, geometry, xml 등의 형식에는 ORDER BY를 사용할 수 없다.
•
ORDER BY 절은 쿼리문의 제일 뒤에 와야 한다
•
ORDER BY 절은 SQL Server의 성능을 떨어뜨릴 소지가 있다 꼭 필요한 경우가 아니라면 되도록 사용하지 않는 것이 좋다.
DISTINCT와 TOP(N)과 TABLESAMPLE 절
•
회원 테이블에서 회원들의 거주 지역이 몇 군데인지 출력
SELECT addr FROM userTbl;
SQL
•
DISTINCT 사용하여 중복된 거주 지역은 한 건만 출력
SELECT DISTINCT addr FROM userTbl;
SQL
•
AdventrueWorks DB에 Sales.CreditCard 테이블에는 ExpYear(신용카드 유효연도)와 ExpMonth(신용카드 유효월)라는 열이 있는데, 그중에서 유효 기간이 얼마 남지 않은 ‘Vista’ 카드의 카드번호 10개를 ORDER BY 문을 사용하여 추출
USE AdventureWorks;
SELECT TOP(10) CreditCardID -- TOP()문에 출력할 개수 지정
FROM Sales.CreditCard -- Sales.CreditCard 테이블 불러오기
WHERE CardType = 'Vista' -- CardType 에 'Vista' 선택
ORDER BY ExpYear, ExpMonth; -- ExpYear, ExpMonth 다중 정렬
SQL
•
전체 신용카드의 1%만 출력 (서브 쿼리 활용)
◦
191개 행 출력 됨
SELECT TOP(SELECT COUNT(*)/100 FROM Sales.CreditCard) CreditCardID
FROM Sales.CreditCard
WHERE CardType = 'Vista'
ORDER BY ExpYear, ExpMonth;
SQL
•
PERCENT
•
전체 신용카드의 0.1%, 5개의 행 출력
SELECT TOP(0.1) PERCENT CreditCardID, ExpYear, ExpMonth -- PERCENT 텍스트 기입
FROM Sales.CreditCard
WHERE CardType = 'Vista'
ORDER BY ExpYear, ExpMonth;
SQL
•
WITH TIES
•
0.1%만 출력하되 마지막 Score와 같은 것들은 전부 출력
SELECT TOP(0.1) PERCENT WITH TIES CreditCardID, ExpYear, ExpMonth -- WITH TIES 옵션
FROM Sales.CreditCard
WHERE CardType = 'Vista'
ORDER BY ExpYear, ExpMonth;
SQL
•
TABLESAMPLE
◦
대략 5%의 샘플 데이터 출력
◦
소량의 행이 있는 테이블에서는 실행되지 않는다.
USE AdventureWorks;
SELECT * FROM Sales.SalesOrderDetail TABLESAMPLE(5 PERCENT);
SQL
•
5%의 샘플 데이터 출력하되 데이터 개수 5000개 이하로 출력
USE AdventureWorks;
SELECT TOP(5000) * FROM Sales.SalesOrderDetail TABLESAMPLE(5 PERCENT);
SQL
•
NEWID
◦
개별 행에 대해 무작위 샘플링을 하려면, 아래와 같이
SELECT TOP() FROM ORDER BY NEWID()를 사용
USE sqlDB;
SELECT TOP(5) * FROM userTbl ORDER BY NEWID();
SQL
OFFSET과 PATCH
•
OFFSET은 지정한 행의 수만큼 건너 뛴 후에 출력하는 기능을 한다.
•
태어난 연도를 기준으로 정렬(내림차순)하되 5등부터 출력
USE sqlDB
SELECT userID, name FROM userTbl
ORDER BY birthYear
OFFSET 4 ROWS; -- 5등부터 출력
SQL
•
5등부터 출력하되, 3건만 출력
USE sqlDB
SELECT userID, name FROM userTbl
ORDER BY birthYear
OFFSET 4 ROWS
FETCH NEXT 3 ROWS ONLY; -- 5, 6, 7 등만 출력
SQL
SELECT INTO
•
SELECT INTO 구문은 테이블을 복사해서 사용할 경우에 주로 사용된다.
•
형식
SELECT 복사할열 INTO 새로운테이블 FROM 기존테이블
SQL
•
buyTbl을 buyTbl2로 복사
USE sqlDB;
SELECT * INTO buyTbl2 FROM buyTbl1
SELECT * FROM buyTbl2;
SQL
•
지정한 일부 열만 복사
SELECT userID, prodName INTO buyTbl3 FROM buyTbl;
SELECT * FROM buyTbl3;
SQL
*참고 : SSMS 개체 탐색기에서 확인해 보면 buyTbl의 PK, FK 등의 제약 조건은 복사되지 않는다.
GROUP BY와 HAVING과 집계 함수
GROUP BY
SELECT 형식 중에서 GROUP BY, HAVING절에 대한 순서 파악.
*순서가 바뀌면 안됨
WITH <common_table_expression> -- ㅇㅇ
SELECT select_list INTO NEW_TABLE -- ㅇㅇ
FROM table_source -- ㅇㅇ
WHERE search_condition -- ㅇㅇ
GROUP BY group_by_expression -- ㅇㅇ
HAVING search_condition -- ㅇㅇ
ORDER BY order_expression ASC | DESC -- ㅇㅇ
SQL
•
구매 테이블에서 사용자가 구매한 물품의 개수 파악 (ORDER BY 활용)
USE sqlDB;
SELECT userID, amount FROM buyTbl ORDER BY userID;
SQL
위 결과를 보면 사용자 별로 여러 번의 물건 구매가 이루어져서, 각각의 행이 별도로 출력된다. BBK의 사용자 경우에는 5+10+2+2+=19개의 구매를 했다. 합계를 낼 때 이렇게 손이나 전자계산기를 두드려서 계산한다면, SQL Server를 사용할 이유가 없을 것이다.
이럴 때는 집계 함수를 사용하면 된다. 집계 함수는 주로 GROUP BY 절과 함께 쓰이며 데이터를 그룹화 해주는 기능을 한다. 사용자 별로 GROUP BY로 묶어준 후에 SUM() 함수로 구매 개수를 합치면 된다.
SELECT userID, SUM(amount) FROM buyTbl GROUP BY userID;
SQL
•
AS(Alias)
◦
SUM(amount)의 결과 열에는 제목이 없다. 별칭(Alias)를 사용해서 결과를 보기 편하게 한다.
SELECT userID AS [사용자 아이디] , SUM(amount) AS [총 구매 개수]
FROM buyTbl GROUP BY userID;
SQL
•
구매액의 총합 출력
◦
구매액은 가격 * 수량 이므로, 총합은 SUM()을 사용하면 된다.
SELECT userID AS [사용자 아이디] , SUM(price*amount) AS [총 구매액]
FROM buyTbl GROUP BY userID;
SQL
•
집계 함수
◦
SUM() 외에 자주 사용되는 집계 함수는 아래 표와 같다.
•
전체 구매자가 구매한 물품 개수의 평균 조회
SELECT AVG(amount) AS [평균 구매 개수] FROM buyTbl;
SQL
평균 구매 개수의 결과가 2가 나왔다. 사실은 약 2.9166개이다. 이렇게 결과가 잘못 나온 이유는, 구매 개수의 데이터 형식이 정수형이기 때문에 그 연산 결과도 정수형으로 나오게 된것이다.
•
amount에 1.0을 곱해서 실수로 변환
SELECT AVG(amount*1.0) AS [평균 구매 개수] FROM buyTbl;
SQL
•
CAST() 함수 사용하여 실수로 변환
SELECT AVG(CAST(amount AS DECIAMAL(10,6))) AS [평균 구매 개수] FROM buyTbl;
-- amount 정수를 DECIMAL(10,6) 10자리 정수, 6자리 소수점으로 변경
SQL
•
각 사용자 별 평균 구매 수
SELECT userID, AVG(amount*1.0) AS [평균 구매 개수] FROM buyTbl GROUP BY userID;
SQL
•
가장 큰 키와 가장 작은 키의 회원 이름과 키를 출력
SELECT name, height FROM userTbl
WHERE height = (SELECT max(height) FROM userTbl)
OR height = (SELECT min(height) FROM userTbl); -- 서브쿼리 사용하여 출력
SQL
•
휴대폰 있는 사용자 수 출력
SELECT COUNT(mobile1) AS [휴대폰이 있는 사용자] FROM userTbl;
SQL
*메세지 탭 클릭 시, Null 값 제거되었다는 문구 출력
•
SQL Server Profiler
•
SQL Server Profiler 활용하여 그냥 SELECT의 성능 비교
◦
SSMS → 도구 탭 → SQL Server Profiler
SQL Server Profiler
•
SQL Server Profiler 인스턴스에서 서버 연동
◦
추적 속성에서 추적 이름, 템플릿 사용에 TSQL_Duration 지정 후 실행 버튼 터치
Count 함수 성능
•
SELECT * 으로 조회했을때 듀레이션이 253, COUNT로 조회했을 때는 듀레이션이 1로 출력
•
개수를 셀 때는 SELECT보다 COUNT가 훨씬 효율적이다.
Having 절
•
앞에서 했던 SUM()을 다시 사용해서 사용자별 총 구매액 조회
USE sqlDB;
GO
SELECT userID AS[사용자], SUM(price*amount) AS[총구매액]
FROM buyTbl
GROUP BY userID ;
SQL
•
Having 절을 사용해 총 구매액이 1000이상인 사용자만 추출
SELECT userID [사용자], SUM(price*amount) AS[총구매액]
FROM buyTbl
GROUP BY userID
HAVING SUM(price*amount) > 1000 -- WHERE 개념은 비슷하지만 GROUP BY 관련 조건
ORDER BY SUM(price*amount) ; -- 총 구매액이 적은 사용자부터 정렬
SQL
*HAVING 절은 꼭 GROUP BY절 다음에 나와야 한다.
ROLLUP(), GROUPING_ID(), CUBE() 함수
ROLLUP()
•
총합 또는 중간합계가 필요하다면 GROUP BY절과 함께 ROLLUP() 또는 CUBE()를 사용하면 된다. 만약 분류(groupName)별로 합계 그 총합을 구하고 싶다면 아래 구문을 사용하면 된다.
SELECT num, groupName, SUM(price*amount) AS [비용]
FROM buyTbl
GROUP BY ROLLUP (groupName, num);
SQL
*groupName 별로 소합계, 총합계 출력
•
소합계는 출력하지 않고, 총합계만 출력
◦
num 필드 빼주면 됨
SELECT groupName, SUM(price*amount) AS [비용]
FROM buyTbl
GROUP BY ROLLUP (groupName);
SQL
GROUPING_ID()
•
한눈에 데이터인지 합계인지를 알기 위해서는 GROUPING_ID() 함수를 사용할 수 있다.
•
GROUPING_ID() 함수의 결과가 0이면 데이터, 1이면 합계를 위해서 추가된 열이라고 보면 된다.
SELECT groupName, SUM(price * amount) AS [비용]
, GROUPING_ID(groupName) AS [추가행 여부]
FROM buyTbl
GROUP BY ROLLUP(groupName);
SQL
CUBE
•
CUBE() 함수도 ROLLUP()과 비슷한 개념이지만, CUBE()는 다차원 정보의 데이터를 요약하는데 더 적당하다.
•
우선, 아래 테이블과 같은 간단한 데이터를 가정해보자.
•
이를 물품 별 소합계 및 색상 별 소합계를 모두 보고 싶다면 CUBE를 사용할 수 있다.
USE sqlDB;
CREATE TABLE cubeTbl(prodName NCHAR(3), color NCHAR(2), amount INT); -- 테이블 생성
GO
INSERT INTO cubeTbl VALUES('컴퓨터', '검정', 11); -- 데이터 넣기
INSERT INTO cubeTbl VALUES('컴퓨터', '파랑', 22);
INSERT INTO cubeTbl VALUES('모니터', '검정', 33);
INSERT INTO cubeTbl VALUES('모니터', '파랑', 44);
GO
SELECT prodName, color, SUM(amount) AS[수량 합계]
FROM cubeTbl
GROUP BY CUBE (color, prodName);
SQL
WITH 절과 CTE
WITH 절은 CTE(Common Table Expression)을 표현하기 위한 구문이다. CTE는 기존의 뷰, 파생 테이블, 임시 테이블 등으로 사용되던 것을 대신 할 수 있으며, 더 간결한 식으로 보여지는 장점이 있다. CTE는 ANSI-SQL99 표준에서 나온 것으로 기존의 SQL은 대부분 ANSI-SQL92가 기준으로 하지만, 최근의 DBMS는 대개 ANSI-SQL99와 호환이 되므로 다른 DBMS에서도 SQL Server와 같거나 비슷한 방식으로 응용된다.
CTE는 재귀적(Recursive) CTE와 비재귀적(Non-Recursive) CTE 이렇게 두 가지 종류가 있다.
재귀적이라는 의미는 자기 자신을 반복적으로 호출한다는 의미를 내포한다.
•
비재귀적 CTE
◦
비재귀적 CTE는 말 그대로 재귀적이지 않은 CTE이다. 단순한 형태이며, 복잡한 쿼리문장을 단순화 시키는 데에 적합하게 사용될 수 있다.
WITH CTE_테이블이름(열이름)
AS
(
<쿼리문>
)
SELECT 열이름 FROM CTE_테이블이름 ;
SQL
*SELECT 열이름 FROM CTE_테이블이름 외에 UPDATE 등도 가능하지만, 주로 사용되는 것은 SELECT다.
◦
위의 형식이 좀 생소해 보일 수도 있지만, 위쪽을 떼버리고 그냥 제일 아래의 SELECT 열이름 FROM CTE_테이블이름만 생각해도 된다. 그런데 기존에는 실제 DB에 있는 테이블을 사용했지만, CTE는 바로 위의 WITH 절에서 정의한 CTE_테이블이름을 사용하는 것만 다르다. 즉, ‘WITH CTE_테이블이름(열이름) AS···’ 형식의 테이블이 하나 더 있다고 생각하면 된다.
◦
쉽게 이해하기 위해 앞에서 했던 buyTbl에서 사용자 별 총 구매액 조회
USE sqlDB;
SELECT userID AS 사용자, SUM(price*amount) AS 총구매액
FROM buyTbl GROUP BY userID;
SQL
위의 결과를 총 구매액이 많은 사용자 순서로 정렬하고 싶다면 앞의 쿼리에서 ORDER BY 문을 첨가해도 된다. 하지만, 그럴 경우에는 SQL 문이 더욱 복잡해 보일 수 있으므로 이렇게 생각해보자, 위의 쿼리의 결과가 바로 abc라는 이름의 테이블이라고 생각하면 정렬하는 쿼리는 아래와 같이 간단해진다.
SELECT * FROM abc ORDER BY 총구매액 DESC
SQL
이것이 CTE의 장점중 하나다. 구문을 단순화시켜 준다. 지금까지 얘기한 실질적인 쿼리문은 아래와 같이 작성하면 된다.
WITH abc(userID, total)
AS
( SELECT userID, SUM(price*amount)
FROM buyTbl GROUP BY userID )
SELECT * FROM abc ORDER BY total DESC ;
SQL
•
회원 테이블(userTbl)에서 각 지역별로 큰 키를 1명씩 뽑은 후에, 그 사람들 키의 평균을 내보자. 만약, 전체의 평균이라면 집계 함수 AGV(height)만 사용하면 되지만, 각 지역별로 가장 큰 키의 1명을 우선 뽑아야 하므로 얘기가 좀 복잡해진다. 이럴 때, cte를 유용하게 사용할 수 있다. 한꺼번에 생각하지 말고 하나씩 분할해서 생각해보자.
◦
1단계 → ‘각 지역별로 가장 큰 키’를 뽑는 쿼리를 아래와 같다.
SELECT addr, MAX(height) FROM userTbl GROUP BY addr;
SQL
◦
2단계 → ‘위 쿼리를 WITH 구문으로 묶는다.
WITH abc(addr, height) -- WITH 절에 cte_테이블 이름 작성. 예 : abc
AS
(SELECT addr, MAX(height)
FROM userTbl GROUP BY addr)
SQL
◦
3단계 → ‘키의 평균’을 구하는 쿼리를 작성한다.
SELECT AVG(height) FROM userTbl; -- FROM 절에 cte_테이블이름 작성
SQL
◦
4단계 → 2단계와 3단계의 쿼리를 합친다. 키의 평균을 실수로 만들기 위해서 키에다 1.0을 곱해서 실수로 변환.
WITH abc(addr, height)
AS
(SELECT addr, MAX(height)
FROM userTbl GROUP BY addr)
SELECT AVG(height*1.0) FROM userTbl;
SQL
•
중복 CTE
◦
CTE는 다음 형식과 같은 중복 CTE가 허용된다.
WITH
AAA (컬럼들)
AS ( AAA의 쿼리문 ),
BBB (컬럼들)
AS ( BBB의 쿼리문 ),
CCC (컬럼들)
AS ( CCC의 쿼리문 )
SELECT * FROM [AAA 또는 BBB 또는 CCC]
SQL
*주의할 점 : CCC의 쿼리문에서는 AAA나 BBB를 참조할 수 있지만, AAA의 쿼리문이나 BBB의 쿼리문에서는 CCC를 참조할 수 없다. 즉, 아직 정의되지 않은 CTE를 미리 참조할 수 없다.
-- 참조 예제
WITH
AAA(userID, total)
AS
(SELECT userID, SUM(price*amount) FROM buyTbl GROUP BY userID ),
BBB(sumtotal)
AS
(SELECT SUM(total) FROM AAA ),
CCC(sumavg)
AS
(SELECT sumtotal / (SELECT count(*) FROM buyTbl) FROM BBB)
SELECT * FROM CCC;
SQL
T-SQL의 분류
SQL 문은 크게 DML, DDL, DCL로 분류한다
•
DML
데이터 조작 언어 DML, Data Manipulation Language은 데이터 조작(선택, 삽입, 수정, 삭제)하는데 사용되는 언어다. DML 구문이 사용되는 대상은 테이블의 행이다. 그러므로 DML을 사용하기 위해서는 꼭 그 이전에 테이블이 정의되어 있어야 한다.
SQL 문 중에 SELECT, INSERT, UPDATE, DELETE가 이 구문에 해당된다. 또, 트랜잭션이 발생하는 SQL도 DML이다.
트랜잭션이란 쉽게 표현하면, 테이블의 데이터를 변경(입력/수정/삭제)할 때 실제 테이블에 완전히 적용하지 않고, 임시로 적용시키는 것을 말한다. 그래서 실수가 있었을 경우에 임시로 적용시킨 것을 취소시킬 수 있게 해준다.
*SELECT도 트랜잭션을 발생시키기는 하지만, INSERT/UPDATE/DELETE와는 조금 성격을 달리하므로 별도로 생각하는 것이 좋다. 트랜잭션은 10장에서 상세히 다룬다.
•
DDL
데이터 정의 언어 DDL, Data Definition Language는 데이터베이스, 테이블, 뷰, 인덱스 등의 데이터베이스 개체를 생성/삭제/변경하는 역할을 한다. 자주 사용되는 DDL은 CREATE, DROP, ALTER 등이다. DDL은 이번 장 이후부터 종종 나오게 될 것이므로 그때마다 다시 살펴보자. 한 가지 기억할 것은 DDL은 트랜잭션을 발생시키지 않는다는 것이다. 따라서 되돌림(ROLLBACK)이나 완전 적용(COMMIT)을 시킬 수가 없다. 즉, DDL 문은 실행 즉시 SQL Server에 적용된다.
*예외로 CREATE TABLE 문 등은 트랜잭션을 발생시키기는 하지만, 특별히 신경 쓰지 않아도 된다.
•
DCL
데이터 제어 언어 DCL, Data Control Language는 사용자에게 어떤 권한을 부여하거나 빼앗을 때 주로 사용하는 구문으로, GRANT/REVOKE/DENY 등이 이에 해당된다.
데이터의 변경을 위한 SQL문
데이터의 삽입 : INSERT
INSERT 문 기본
INSERT는 테이블에 데이터를 삽입하는 명령어다. 어렵지 않고 간단하다. 기본적인 형식은 다음과 같다
INSERT [INTO] <테이블> [(열1, 열2, ...)] VALUES (값1, 값2, ...)
SQL
INSERT/UPDATE/DELETE 문도 CTE의 사용이 가능하다. 즉 아래와 같은 형식이 지원된다.
[WITH CTE_테이블명() ...]
INSERT [INTO] <CTE_테이블명> ...
SQL
INSERT 문은 별로 어려울 것이 없으며, 몇 가지만 주의하면 된다.
우선 테이블 이름 다음에 나오는 열은 생략이 가능하다. 하지만, 생략할 경우에 VALUE 다음에 나오는 값들의 순서 및 개수가 테이블이 정의된 열 순서 및 개수와 동일해야 한다.
USE tempDB;
CREATE TABLE testTbl1 (id int, userName nchar(3), age int);
GO
INSERT INTO testTbl1 VALUES (1, '홍길동', 25);
SQL
*tempDB는 SQL Server 서비스가 재가동되면(컴퓨터를 재부팅해도 동일) 그 내부의 내용은 모두 삭제된다. 그러므로 지금처럼 임시로 사용하는 것은 괜찮지만, 잠시라도 저장되어야 할 테이블을 tempDB에 생성하는 것은 위험한 일이다.
만약, 위에 예에서 id와 이름만 입력하고 나이를 입력하고 싶지 않다면, 아래와 같이 테이블 이름 뒤에 입력할 열의 목록을 나열해줘야 한다.
INSERT INTO testTbl1(id, userName) VALUES (2, '설현');
SQL
이 경우 생략한 age에는 NULL 값이 들어간다.
열의 순서를 바꿔서 입력하고 싶을 때는 꼭 열 이름을 입력할 순서에 맞춰 나열해줘야 한다.
INSERT INTO testTbl1(userName, age, id) VALUES ('초아', 26, 3);
SQL
자동으로 증가하는 IDENTITY
테이블의 속성이 IDENTITY로 지정되어 있다면, INSERT에서는 해당 열이 없다고 생각하고 입력하면 된다. IDENTITY는 자동으로 1부터 증가하는 값을 입력해준다. 또, CREATE TABLE에서 DEFAULT 문장으로 기본 값을 설정해 놓았을 때, INSERT를 사용 시 별도의 값을 입력하지 않고 지정해 놓은 디폴트 값을 그대로 사용하려면 값이 입력될 자리에 ‘DEFAULT’라고 써주면 된다.
USE tempdb;
CREATE TABLE testTbl2
(id int IDENTITY,
userName, nchar(3),
age int,
nation nchar(4) DEFAULT '대한민국');
GO
INSERT INTO testTbl2 VALUES ('지민', 25, DEFAULT);
SQL
그런데 강제로 identity 값을 입력하고 싶다면 아래와 같이 수행하면 된다.
SET IDENTITY_INSERT testTbl2 ON;
GO
INSERT INTO testTbl2(id, userName, age, nation) VALUES (11, '쯔위', 18, '대만');
SQL
주의 사항은 IDENTITY_INSERT를 ON으로 변경한 테이블은 꼭 입력할 열을 명시적으로 지정해줘야 한다. 이 문장을 생략하면 오류가 발생한다.
SET IDENTITY_INSERT testTbl2 ON;
GO
INSERT INTO testTbl2(id, userName, age, nation) VALUES (11, '쯔위', 18, '대만');
SQL
다시 IDENTITY__INSERT를 OFF로 변경하고 입력하면, id값은 최대값+1부터 자동 입력된다.
SET IDENTITY_INSERT testTbl2 ON;
GO
INSERT INTO testTbl2(id, userName, age, nation) VALUES (11, '쯔위', 18, '대만');
SQL
열의 이름을 잊어버렸을 때에는 다음과 같이 시스템 저장 프로시저를 사용하면 열의 목록 및 기타 정보를 출력해준다.
EXECUTE sp_help 테이블이름;
SQL
특정 테이블에 설정된 현재의 IDENTITY 값을 확인하려면 아래 형식을 사용하면 된다.
EXECUTE IDEN_CURRENT ('테이블이름');
SQL
또는 다음과 같다
SELECT @@IDENTITY;
SQL
IDENT_CURRENT는 특정 테이블을 지정해서 그 테이블에 설정된 IDENTITY 값을 확인할 수 있으며, @@IDENTITY는 현재의 세션(쿼리창)에서 가장 최근에 생성된 ID값을 확인할 수 있다.
SEQUENCE
•
IDENTITY와 같은 효과를 내기 위해 시퀀스 사용
•
오라클에서는 전부터 제공하던 개체인데, SQL Server에서 오라클과 동일한 방식으로 제공하기 위해 SQL Server 2012 제공된다.
USE tempdb;
CREATE TABLE testTbl3
( id int,
userName nchar(3),
age int,
nation nchar(4) DEFAULT '대한민국');
GO
SQL
시퀀스 생성, 시작값은 1로 증가값도 1로 설정한다.
CREATE SEQUENCE idSEQ
START WITH 1 -- 시작 값
INCREMENT BY 1 ; -- 증가 값
GO
SQL
데이터를 입력한다. 시퀀스를 입력하려면 “NEXT VALUE FOR 시퀀스이름”을 사용하면 된다.
INSERT INTO testTbl3 VALUES (NEXT VALUE FOR idSEQ, '지민', 25, DEFAULT);
SQL
강제로 id열에 다른 값을 입력하고 싶다면, id열에는 아무것도 지정하지 않았으므로 그냥 원하는 값을 입력하면 된다. 그리고 시퀀스의 시작값을 다시 설정해준 후에 계속 시퀀스를 입력하면 된다. 결과는 앞에서 했던 IDENTITY와 동일하다.
INSERT INTO testTbl3 VALUES (11, '쯔위', 18, '대만');
GO
ALTER SEQUENCE idSEQ
RESTART WITH 12; -- 시작값을 다시 설정
GO
INSERT INTO testTbl3 VALUES (NEXT VALUE FOR idSEQ, '미나', 21, '일본');
SELECT * FROM testTbl3;
SQL
특정 범위의 값이 계속 반복해서 입력되게 할 수도 있다. 예를 들면 100, 200, 300이 반복해서 입력되게 다음과 같이 설정할 수도 있다.
CREATE TABLE testTbl4 (id INT);
GO
CREATE SEQUENCE cycleSEQ
START WITH 100
INCREMENT BY 100
MINVALUE 100 -- 최소값
MAXVALUE 300 -- 최대값
CYCLE ; -- 반복 설정
GO
INSERT INTO testTbl4 VALUES (NEXT VALUE FOR cycleSEQ);
INSERT INTO testTbl4 VALUES (NEXT VALUE FOR cycleSEQ);
INSERT INTO testTbl4 VALUES (NEXT VALUE FOR cycleSEQ);
INSERT INTO testTbl4 VALUES (NEXT VALUE FOR cycleSEQ);
GO
SELECT * FROM testTbl4;
SQL
시퀀스를 DEFAULT와 함께 사용하면 IDENTITY와 마찬가지로 값이 표기를 생략해도 자동으로 입력되도록 설정할 수 있다.
USE tempdb;
CREATE SEQUENCE autoSEQ
START WITH 1
INCREMENT BY 1 ;
GO
CREATE TABLE testTbl5
( id int DEFAULT (NEXT VALUE FOR autoSEQ) ,
userName nchar(3)
) ;
GO
INSERT INTO testTbl5(userName) VALUES ('지민');
INSERT INTO testTbl5(userName) VALUES ('쯔위');
INSERT INTO testTbl5(userName) VALUES ('미나');
GO
SELECT * FROM testTbl5;
SQL
대량의 샘플데이터 생성
지금까지 했던 방식으로 직접 키보드로 샘플 데이터를 입력하려면 많은 시간이 걸릴 것이다. 이럴 때 INSERT INTO … SELECT 구문을 사용할 수 있다. 이는 다른 테이블의 데이터를 가져와서 대량으로 입력하는 효과를 낸다.
•
형식
INSERT INTO 테이블이름 (열이름1, 열이름2, ···)
SELECT 문 ;
SQL
물론, SELECT 문의 결과 열의 개수는 INSERT를 할 테이블의 열 개수와 일치해야 한다.
AdventureWorks의 데이터를 가져와서 입력.
USE tempdb;
CREATE TABLE testTbl6 (id int, Fname nvarchar(50), Lname nvarchar(50);
GO
INSERT INTO testTbl6
SELECT BusinessEntityID, FirstName, LastName
FROM AdventureWorks.Person.Person;
SQL
대량의 데이터를 샘플 데이터로 사용할 때에 INSERT INTO … SELECT 문은 아주 유용하다. 아예, 테이블 정의까지 생략하고 싶다면 앞에서 배웠던 SELECT … INTO 구문을 아래와 같이 사용할 수도 있다.
USE tempDB;
SELECT BusinessEntityID as id, FirstName as Fname, LastName as Lname
INTO testTbl7
FROM AdventureWorks.Person.Person ;
SQL
데이터의 수정 : UPDATE
기존에 입력되어 있는 값을 변경하기 위해서는 UPDATE 문을 아래와 같은 형식으로 사용한다.
UPDATE 테이블이름
SET 열1=값1, 열2=값2 ···
WHERE 조건 ;
SQL
UPDATE도 사용법은 간단하지만, 주의할 사항이 있다. WHERE 절은 생략이 가능하지만 WHERE 절을 생략하면 테이블의 전체의 행이 변경되므로, 웬만해서는 WHERE 절을 꼭 써야 한다.
다음 예는 ‘Kim’의 전화번호를 ‘없음’으로 변경하는 예다. 10건이 변경될 것이다.
UPDATE testTbl6
SET Lname = '없음'
WHERE Fname = 'Kim';
SQL
실수로 WHERE 절을 빼먹고 UPDATE testTbl6 SET Lname = ‘없음’을 실행했다면, 전체 행의 Lname이 모두’없음’으로 변경된다. 실무에서도 이러한 실수가 종종 일어날 수 있으므로 주의가 필요하다. 원상태로 복구를 위해서는 많은 복잡한 절차가 필요할 뿐만 아니라, 다시 되돌릴 수 없는 경우도 있다.
가끔은 전체 테이블의 내용을 변경하고 싶을 때 WHERE를 생략할 수도 있는데, 예를 들어 구매 테이블에서 현재의 단가가 모두 1.5배 인상되었다면, 아래와 같이 사용할 수 있다.
USE sqlDB;
UPDATE buyTbl SET price = price * 1.5 ;
SQL
데이터의 삭제 : DELETE
DELETE도 UPDATE와 거의 비슷한 개념이다. DELETE는 행 단위로 삭제하는데, 형식은 다음과 같다.
DELETE 테이블이름 WHERE 조건;
SQL
WHERE 문이 생략되면 전체 데이터를 삭제한다.
testTbl3에서 ‘Kim’사용자가 필요 없다면 다음과 같은 구문을 사용하면 된다.
USE tempDB;
DELETE testTbl6 WHERE Fname = 'Kim';
SQL
이 예에서는 10건의 행이 삭제될 것이다.
10건의 ‘Kim’을 모두 지우는 것이 아니라 ‘Kim’ 중에서 중 상위 몇 건만 삭제하려면 TOP(N) 구문과 함께 사용하면 된다. 다음은 ‘Kim’중에서 상위 5건만 삭제된다.
USE tempDB;
DELETE TOP(5) testTbl6 WHERE Fname = 'Kim';
SQL
대용량 테이블 삭제 성능 비교
•
step 0 : 대용량 테이블 3개 생성
USE tempdb;
SELECT * INTO bigTbl1 FROM AdventureWorks.Sales.SalesOrderDetail;
SELECT * INTO bigTbl2 FROM AdventureWorks.Sales.SalesOrderDetail;
SELECT * INTO bigTbl3 FROM AdventureWorks.Sales.SalesOrderDetail;
SQL
•
step 1 : SQL Server Profiler 실행
SSMS 메뉴의 [도구] → [SQL Server Profiler] 실행해서 추적 이름 설정 후 템플릿 사용을 ‘TSQL_Duration’으로 선택한 후 <실행>을 클릭한다.
•
step 2 : SSMS 쿼리창에서 DELETE, DROP, TRUNCATE 문으로 새 테이블부터 모두 삭제한다. 세 구문 테이블의 행을 삭제한다(단, DROP 문은 테이블 자체를 삭제한다).
DELETE FROM bigTbl1;
GO
DROP FROM bigTbl2;
GO
TRUNCATE FROM bigTbl3;
SQL
•
step 3 : Profiler에서 Duration 성능 비교
DELETE FROM 문은 행 하나하나씩 삭제하기 떄문에 성능이 매우 좋지 않고,
전체 행을 지울 때는 TRUNCATE 문을 써야 한다. (MS권장 사항)
조건부 데이터 변경 : MERGE
MERGE 문은 하나의 문장에서 경우에 따라서 INSERT, UPDATE, DELETE를 수행할 수 있는 구문이다.
MERGE 구문의 활용 연습
멤버테이블(memberTBL)에는 기존 휘원들이 존재하는데, 이 멤버테이블은 중요한 테이블이라서 실수를 하면 안 되므로 INSERT, DELETE, UPDATE를 직접 사용하면 안된다. 그래서 회원의 가입, 변경, 탈퇴가 생기면 변경테이블(changeTBL)에 INSERT 문으로 회원의 변경사항을 입력한다. 변경테이블의 변경 사항은 신규가입/주소변경/회원탈퇴 3가지 경우가 있다.
변경테이블의 쌓인 내용은 1주일마다, MERGE 구문을 사용해서 변경테이블의 내용이 ‘신규가입’이면 멤버테이블에 새로 회원을 등록해주고, ‘주소변경’이면 멤버테이블의 주소를 변경하고, ‘회원탈퇴’면 멤버테이블에서 해당 회원을 삭제한다.
이와 같은 시나리오가 작동되도록 SQL 문을 작성
•
테이블 생성
USE sqlDB;
SELECT userID, name, addr INTO memeberTBL FROM userTbl;
SELECT * FROM memberTBL;
CREATE TABLE changeTBL
( changeType NCHAR(4), -- 변경 사유
userID CHAR(8),
name nvarchar(10),
addr nchar(2) );
SQL
•
changeTBL에 데이터 넣고 MERGE 문 작성
INSERT INTO changeTBL VALUES
('신규가입', 'CHO', '초아', '미국')
('주소변경', 'LSG', null, '제주')
('주소변경', 'LJB', null, '영국')
('회원탈퇴', 'BBK', null, null)
('회원탈퇴', 'SSK', null, null)
MERGE memberTBL AS M -- 변경될 테이블 (target 테이블)
USING changeTBL AS C -- 변경할 기준이 되는 테이블 (source 테이블)
ON m.userID = C.userID -- userID를 기준으로 두 테이블을 비교한다.
-- target 테이블에 source 테이블의 행이 없고, 사유가 '신규가입' 이라면 새로운 행을 추가
WHEN NOT MATCHED AND changeType = '신규가입' THEN
INSERT (userID, name, addr) VALUES(C.userID, C.name, C.addr)
-- target 테이블에 source 테이블의 행이 있고, 사유가 '주소변경'이라면 주소를 변경한다.
WHEN MATCHED AND changeType = '주소변경' THEN
UPDATE SET M.addr = C.addr
-- target 테이블에 source 테이블의 행이 있고, 사유가 '회원탈퇴'라면 해당 행을 삭제한다.
WHEN MATCHED AND changeType = '회원탈퇴' THEN
DELETE ;
SELECT * FROM memberTBL;
SQL
'Database > SQL Server' 카테고리의 다른 글
| <SQL Server> 06. Transact SQL 고급 - 3 (2) | 2023.05.28 |
|---|---|
| <SQL Server> 05. Transact SQL 고급 - 2 (0) | 2023.05.28 |
| <SQL Server> 04. Transact SQL 고급 - 1 (0) | 2023.05.28 |
| <SQL Server> 02. SQL Server 툴, 유틸리티 (0) | 2023.05.28 |
| <SQL Server> 01. 데이터베이스 모델링 (0) | 2023.05.28 |
