- CTE는 기존의 뷰, 파생 테이블, 임시 테이블 등으로 사용되던 것을 대신할 수 있으며, 더 간결한 식으로 보이는 장점이 있음.
- CTE는 비재귀적(Non- Recursive) CTE와 재귀적(Recursive) CTE 두 가지 가 있다.
* 비재귀적 CTE : 단순한 형태로 복잡한 쿼리 문장을 단순화 시키는 데 사용하기에 적합.
--1) 비재귀적(Non-Recursive) CTE
--A. 사용자 별 총 구매액
select userid '사용자', sum(price*amount) '총구매액' from buyTbl group by userid;
--B. 사용자 별 총 구매액이 많은 사용자 순서로 정렬 출력
select userid '사용자', sum(price*amount) '총구매액' from buyTbl group by userid order by sum(price*amount) desc;
-- A의 실행 결과가 abc라는 이름의 테이블이라면
select * from abc order by 총구매액 desc;
WITH abc(userid, total)
as
(select userid, sum(price*amount) from buyTbl group by userid)
select * from abc order by total desc;
↑이와 같이 구문을 단순화해주는 목적이 비재 귀적CTE의 장점이자 목적이다.
--Q1) 각 지역별로 가장 키가 큰 사람 1명씩을 뽑은 후에, 그 사람들의 키 평균을 구하시오.
-- 1. 각 지역별로 가장 키가 큰 사람을 뽑는 쿼리
select addr as '지역', MAX(height) from userTbl group by addr;
-- 2. 위 쿼리를 WITH 구문으로 (가상)테이블 생성
WITH CTE_userTbl(addr, maxheight)
AS
(select addr, MAX(height) from userTbl group by addr)
-- 3. 키 평균을 구하는 쿼리 작성
select AVG(maxheight) from CTE_userTbl
↓위에서 가상 테이블을 만들었으므로, 테이블명 입력 도중에 가상 테이블 명이 뜬다.
-- 4. 2, 3 쿼리를 조합 with cte 완성, 키의 평균을 실수로 만들기 위해서 키에 1.0을 곱함.
WITH CTE_userTbl(addr, maxheight)
AS
(select addr, MAX(height) from userTbl group by addr)
select AVG(maxheight*1.0) '평균키' from CTE_userTbl;
--Q2) 물품 분류(groupName)에서 가격이 가장 비싼 제품들의 평균 금액을 구하시오.
select groupname as '물품분류', MAX(price) '가장비싼가격' from buyTbl group by groupname;
WITH CTE_buyTbl(groupname, maxprice)
AS
(select groupname as '물품분류', MAX(price) from buyTbl group by groupname)
select AVG(maxprice*1.0) from CTE_buyTbl;
|
중복 CTE WITH aaa(컬럼들) as (aaa의 쿼리문) bbb(컬럼들) as (bbb의 쿼리문) ccc(컬럼들) as (ccc의 쿼리문)
select * from [aaa 또는 bbb 또는 ccc]
-- CCC의 쿼리문에서는 AAA나 BBB를 참조할 수 있지만, AAA의 쿼리문이 나 BBB의 쿼리문에서는 CCC를 참조할 수 없다. -- 즉 아직 정의되지 않은 CTE를 미리 참조할 수 없다. |
--Q3) (WITH 절을 이용) 구매테이블에서 각 사용자 별 총구매액(단가*수량)을 구한 후에, 그것들의 평균을 구하시오.
-- A. 사용자별 구매한 총합, B. 그 총 합의 합계, C. 총합을 전체 개수로 나누어 평균
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;
* 재귀적 CTE : 재귀적이라는 말은 자기 자신을 반복적으로 호출한다는 의미.
|
WITH CTE_테이블이름(열이름) AS ( <쿼리문1 : SELECT * FROM 테이블A> UNION ALL <쿼리문2 : SELECT * FROM 테이블A JOIN CTE_테이블이름> )
SELECT * FROM CTE_테이블이름
-쿼리문1 : 앵커멤버(Anchor Member : AM) ㄴ기준이 되는 쿼리이며, 한번만 실행된다. -쿼리문2 : 재귀멤버(Recursive Member : RM) ㄴ계속 호출하여 반복적으로 실행된다. |
- 재귀적 CTE 작동 원리
1. 쿼리문1을 실행한다. 이것이 루틴의 최초 호출에 해당한다.. 그리고, 기 본 값은 0으로 초기화된다..
2. 쿼리문2를 실행한다. 기본 값을 기본 값+1로 증가시킨다.. 그런데 select의 결과가 빈 것이 아니라면, ‘CTE_테이블이름’을 다시 재귀적으로 호출한다..
3. 계속 2번을 반복한다. 단, Select의 결과가 아무것도 없다면 재귀적인 호출이 중단된다..
4. 외부의 SELECT 문을 실행해서 앞 단계에서의 누적된 결과(UNION ALL)를 가져온다..
T-SQL의 분류
|
1. DML(Data Manipulation Language : 데이터 조작 언어)
2. DDL(Data Definition Language : 데이터 정의 언어) - DDL은 데이터베이스, 테이블, 뷰, 인덱스 등의 데이터베이스 개체를 생성 /삭제/변경하는 역할을 한다. - CREATE, DROP, ALTER 등이 있다. - DDL은 트랜잭션을 발생시키지 않는다. 따라서 되돌림(ROLLBACK)이나 완전 적용(COMMIT)를 시킬 수 없다. (DDL문은 실행 즉시 SQL Server에 적용 됨.)
3. DCL(Data Control Language : 데이터 제어 언어) - DCL은 사용자에게 어떤 권한을 부여하거나 빼앗을 때 사용하는 구문. - GRANT/REVOKE/DENY 등이 있다. |
|
-- 데이터 삽입 : INSERT INSERT INTO 테이블명(필드명1, 필드명2.........) ㄴ (필드명)은 VALUE로 넣어주는 값이 테이블명에 정의 되어있는 필드의 순서와 데이터형이 같을 때 생략이 가능하다. VALUES (값1(필드명1에 대한 값), 값2(필드명2에 대한 값)........)
-- 자동으로 증가하는 : IDENTITY -- 테이블 속성이 IDENTITY로 지정되어 있다면, INSERT에서는 해당 필드가 없다. 라고 생각하고 입력하면 된다. -- IDENTITY는 자동으로 1부터 증가하는 값을 입력 해 줌. -- DEFAULT 속성을 지정하면, INSERT할 때 별도의 값을 입력하지 않고 지정해 놓은 Default값을 입력이 되도록 할 수 있다. |
USE tempdb;
CREATE TABLE testTbl1(id int, userName nchar(3), age int);
select * from testTbl1;
INSERT INTO testTbl1(id, username, age) VALUES(1, N'홍길동', 25);
INSERT INTO testTbl1 VALUES(1, N'홍길동', 25);
INSERT INTO testTbl1(id, username) VALUES(2, N'한가인'); --NULL값을 넣어줌
INSERT INTO testTbl1 VALUES(2, N'한가인'); --에러남
-- 열의 순서를 바꿔서 입력하고 싶은 경우
INSERT INTO testTbl1(userName, age, id) VALUES(N'조인성', 29, 3);
--DEFAULT : 데이터 입력 시 DEFAULT만 입력하면 DEFAULT로 지정되어 있는 데이터를 자동으로 입력하여 넣어준다.
-- 강제로 IDENTITY 값을 변경하고자 할 때
-- SET IDENTITY_INSERT 테이블명 ON : 강제로 IDENTITY값을 바꾸어줌
-- 다시 이전의 IDENTITY값부터 시작하게 하고 싶다면 속성 값을OFF로 바꾸어 준다.
-- 열의 이름을 모를 때 확인하는 방법
-- exec sp_help 테이블이름
-- 특정 테이블에 설정 되어있는 현재 IDENTITY 값을 확인하는 방법
select IDENT_CURRENT ('테이블명'); -- 특정 테이블을 지정 후 해당 테이블에 설정된 identity값을 확인
select @@IDENTITY; --현재의 세션(쿼리 창)에서 가장 최근에 생성된 id값 확인
--↑세션에 따라서 값이 다르게 나올 수 있으므로 사용에 유의한다.
-- SEQUENCE : oracle dbms에서 사용하던 아이, sql server 2012부터 추가 된 기능
GUI방법
데이터베이스 – 해당DB 더보기 클릭 – 프로그래밍 기능 – 시퀀스 우클릭 – 새 시퀀스
쿼리문 방법
-- 시퀀스 생성, 시작값 1, 증가값 1 <-시작값과 증가값을 지정이 가능하다.
CREATE SEQUENCE idSEQ
START WITH 1 --시작값 지정
INCREMENT BY 1; --증가값 지정
-- 시퀀스를 입력하려면 'NEXT VALUE FOR 시퀀스 이름'
-- 강제로 id열에 다른 값을 입력하고 싶을 때 VALUES에 id값 입력
-- ALTER : 시퀀스의 속성을 바꿈
ALTER SEQUENCE idSEQ
RESTART WITH 20; --시작값을 다시 설정
-- 특정 범위의 값이 계속 반복 되어서 입력 되게 하는 경우
-- 100, 200, 300이 반복되어 입력되게끔 하려면
CREATE TABLE testTbl4(id INT);
CREATE SEQUENCE cycleSEQ
START WITH 100 --시작값
INCREMENT BY 100 --증가값
MINVALUE 100 --최소값
MAXVALUE 300 --최대값
CYCLE; --반복
GUI로 확인해보면 쿼리문에서 쓴 내용이 설정되어 있는 것을 볼 수 있다.
INSERT INTO testTbl4 VALUES(NEXT VALUE FOR cycleSEQ);
SELECT * FROM testTbl4;
↑테이블을 확인해보면 100, 200, 300이 반복되는 결과를 확인할 수 있다.
-- DEFAULT와 함께 사용하면 IDENTITY와 마찬가지로 값 표기를 생략해도 자동으로 입력 되게 설정이 가능
CREATE SEQUENCE autoSEQ
START WITH 1
INCREMENT BY 1;
CREATE TABLE testTbl5
(id int DEFAULT(next value for autoSEQ), username nchar(3));
INSERT INTO testTbl5(username) VALUES(N'강동원');
INSERT INTO testTbl5(username) VALUES(N'키아누');
INSERT INTO testTbl5(username) VALUES(N'다꾸앙');
select * from testTbl5;
-- SQL SERVER 2008부터 데이터를 넣어주는 방법
-- 대량의 DB로부터 샘플 데이터 생성
INSERT INTO .... SELECT
--다른 테이블의 데이터를 가져와서 대량으로 입력하는 경우(샘플 데이터 생성 등)
INSERT INTO 테이블면(열이름1, 열이름2....)
SELECT 문....
ㄴ테이블 생성하지 않아도 데이터 입력이 가능하다.
CREATE TABLE testTbl6(id int, Fname nvarchar(50), Lname nvarchar(50));
INSERT INTO testTbl6
SELECT BusinessEntityID, FirstName, LastName
FROM AdventureWorks.Person.Person;
ㄴ테이블을 생성해야 한다.
--기존에 입력되어 있는 값을 변경 : UPDATE
UPDATE 테이블명
SET 열=값, 열=값 .....
WHERE 조건
★★★주의★★★
WHERE 절은 생략 가능하지만, 생략 시 지정한 테이블의 전체 행이 변경되므로 주의한다.
SELECT * FROM testTbl6 WHERE Fname='Kim';
UPDATE testTbl6
SET Lname=N'없음'
WHERE Fname=N'Kim';
SELECT * FROM testTbl6 WHERE Fname='Kim';
-- sqlDB의 buyTbl에서 모든 물건의 단가가 1.5배 인상하였을 경우 단가를 모두 1.5배 증가시켜 적용하라
UPDATE sqlDB.dbo.buyTbl
SET price=price*1.5;
SELECT * FROM sqlDB.dbo.buyTbl;
-- 행 단위로 데이터를 삭제 : DELETE
DELETE 테이블이름 WHERE 조건;
★★★조건절을 사용하지 않으면 지정한 테이블의 모든 값이 삭제되므로 더더더욱 주의한다.★★★
SELECT * FROM testTbl6 WHERE Fname='Kim';
DELETE testTbl6 WHERE Fname='Kim';
SELECT * FROM testTbl6 WHERE Fname='Kim';
