SQL 기초 - 12. 뷰 (임시 테이블, CTE)

이 글은 '김상형의 SQL 정복'의 예제 코드를 설명하는 방식으로 SQL 쿼리 예제를 소개하고 있습니다.

 

SQL 기초 - 12. 뷰 (임시 테이블, CTE)

SELECT member, age, addr FROM tMember;

-- 간단한 VIEW를 생성하는 예제
-- VIEW는 쿼리문으로 생성하는 가상적인 테이블이다.
-- 뷰의 데이터를 수정하면 실제 테이블에도 영향을 끼친다
-- 뷰는 보기 전용으로 이용하는 것이 바람직하다.
CREATE VIEW vMember AS
SELECT member, age, addr FROM tMember;

SELECT * FROM vMember;

SELECT member, age FROM vMember;
SELECT * FROM vMember WHERE addr LIKE '%서울%';
SELECT * FROM vMember ORDER BY member;

SELECT name, email FROM vMember;

DROP VIEW vMember;

CREATE VIEW vMember AS SELECT member, age, addr, email FROM tMember;

오라클, 마리아 : CREATE OR REPLACE VIEW vMember AS SELECT member, age, addr, email FROM tMember;
MSSQL : ALTER VIEW vMember AS SELECT member, age, addr, email FROM tMember;

CREATE VIEW vMemberMirror AS SELECT * FROM tMember;

-- 원하는 열을 선택하면 VERTICAL하게 VIEW를 만들 수 있다
CREATE VIEW vStaffVirt AS SELECT depart, salary, name FROM tStaff;

-- WHERE을 통해 원하는 행을 뽑아낼 수 있다.
CREATE VIEW vStaffHorz AS SELECT * FROM tStaff WHERE depart = '총무부';

CREATE VIEW vStaffPart AS SELECT name, salary FROM tStaff WHERE depart = '총무부';

-- 원하는 별명으로 열을 지정해 줄 수 있다.
CREATE VIEW vStaffAlias(n, d, s) AS SELECT name, depart, salary FROM tStaff;

CREATE OR REPLACE VIEW vStaffAlias AS SELECT name n, depart d , salary s FROM tStaff;

-- 원하는 별명으로 열을 지정했으면, 기존 이름이 아닌 별명을 이용해야 한다.
SELECT * FROM vStaffAlias ORDER BY s;           -- 맞음
SELECT * FROM vStaffAlias ORDER BY salary;      -- 에러

CREATE VIEW vStaffBonus AS SELECT name, salary * score / 100 AS bonus FROM tStaff;

SELECT * FROM vStaffBonus WHERE bonus > 300;

CREATE VIEW vShopping AS
SELECT M.member, M.addr, O.item, O.num, O.orderDate FROM tMember M 
INNER JOIN tOrder O ON M.member = O.member;

CREATE VIEW vUnion AS
SELECT name, salary FROM tStaff WHERE depart = '인사과'
UNION
SELECT name, salary FROM tEmployee;

CREATE VIEW vvStaffHorz AS
SELECT name, joindate, salary FROM vStaffHorz;

CREATE VIEW vOriginal AS SELECT a, b, c FROM tOriginal;

CREATE VIEW vOriginal(a, b, c) AS SELECT x, y, c FROM tOriginal;

UPDATE vMember SET addr = '서울 신사동' WHERE member = '춘향';

UPDATE vStaffBonus SET bonus = 500 WHERE name = '유관순';

INSERT INTO vStaffHorz VALUES ('김한슬', '총무부', '여', '2022/08/14', '사원', 520, 55);

-- vStaffHorz는 '총무부'에 해당하는 사원만 저장하는 뷰이다.
-- 여기에 '영업부' 사원을 넣으면 삽입은 되지만 뷰에서 확인할 수가 없다!
INSERT INTO vStaffHorz VALUES ('김한결', '영업부', '남', '2023/05/13', '사원', 490, 35);

-- 이를 해결하기 위해서 WITH CHECK OPTION을 넣어주면, 자동으로 넣을 수 없는 행의 삽입을 막는다.
CREATE VIEW vStaffHorzCheck AS 
SELECT * FROM tStaff WHERE depart = '총무부' WITH CHECK OPTION;

DELETE FROM tStaff WHERE name IN ('김한슬','김한결');
INSERT INTO vStaffHorzCheck VALUES ('김한결', '영업부', '남', '2023/05/13', '사원', 490, 35);

UPDATE vStaffHorzCheck SET depart = '기획팀' WHERE name = '김유신';

INSERT INTO vStaffVirt (name, depart, salary) VALUES ('이완용', '영업부', 99);

-- WITH READ ONLY로 읽기 전용 뷰를 만들 수 있다.
CREATE VIEW vStaffReadOnly AS SELECT * FROM tStaff 
WHERE depart = '인사과' WITH READ ONLY;

-- WITH ENCRYPTION으로 암호화도 가능하다.
CREATE VIEW vMemberEnc WITH ENCRYPTION AS
SELECT member, age, addr FROM tMember;




-- 임시 테이블은 뷰가 아닌 실제 테이블처럼 사용할 수 있다.
-- 임시 테이블을 활용하면 원본 데이터를 건드리지 않고 복잡한 작업을 할 수 있다.
-- 데이터 저장 수명에 대한 두 가지 옵션이 있다.
-- ON COMMIT DELETE ROWS : 커밋하면 데이터가 사라진다.
-- ON COMMIT PRESERVE ROWS : 세션 중에는 데이터를 유지하며 세션이 종료하면 사라진다.
CREATE GLOBAL TEMPORARY TABLE tGtt (
	name VARCHAR(20) PRIMARY KEY,
	score INT
);

INSERT INTO tGtt VALUES ('김한슬', 95);
INSERT INTO tGtt VALUES ('김한결', 80);
SELECT * FROM tGtt;

-- 세션 내부에서만 데이터 확인이 가능하다. (다른 세션에서는 확인 불가)
DROP TABLE tGtt;
CREATE GLOBAL TEMPORARY TABLE tGtt (
	name VARCHAR(20) PRIMARY KEY,
	score INT
) ON COMMIT PRESERVE ROWS;

INSERT INTO tGtt VALUES ('김규민', 70);
SELECT * FROM tGtt;

-- #... 은 SQL 문법이다. (CREATE와 INSERT를 한방에!)
CREATE TABLE #tTemp (
	name VARCHAR(20) PRIMARY KEY,
	score INT
);

INSERT INTO #tTemp VALUES ('김한슬', 95);
INSERT INTO #tTemp VALUES ('김한결', 80);
INSERT INTO #tTemp VALUES ('김규민', 70);
SELECT * FROM #tTemp;

CREATE TEMPORARY TABLE tTemp (
	name VARCHAR(20) PRIMARY KEY,
	score INT
);

INSERT INTO tTemp VALUES ('김한슬', 95);
INSERT INTO tTemp VALUES ('김한결', 80);
INSERT INTO tTemp VALUES ('김규민', 70);
SELECT * FROM tTemp;

CREATE GLOBAL TEMPORARY TABLE tTaskForce AS SELECT * FROM tStaff;

INSERT INTO tTaskForce SELECT * FROM (SELECT * FROM tStaff 
WHERE joindate <= '20160101' ORDER BY salary DESC) WHERE rownum <= 10;

MSSQL : SELECT TOP 10 * INTO #tTaskForce FROM tStaff 
WHERE joindate <= '20160101' ORDER BY salary DESC;
마리아 : CREATE TEMPORARY TABLE tTaskForce AS SELECT * FROM tStaff 
WHERE joindate <= '20160101' ORDER BY salary DESC LIMIT 10;

DELETE FROM tTaskForce WHERE score < (SELECT AVG(score) FROM tTaskForce 
WHERE gender = '남') AND gender = '남';

DELETE FROM tTaskForce WHERE salary < 
(SELECT AVG(salary) FROM tStaff) AND gender = '여';

DELETE FROM tTaskForce WHERE salary > 300 AND grade = '대리';
INSERT INTO tTaskForce SELECT * FROM tStaff WHERE salary > 380 AND grade = '과장';

SELECT name, salary, score FROM tStaff WHERE depart = '영업부' AND gender = '남';

-- 중첩 서브쿼리를 사용할 수도 있지만...
SELECT * FROM 
(
	SELECT name, salary, score FROM tStaff WHERE depart = '영업부' AND gender = '남'
) A
WHERE salary >= (
	SELECT avg(salary) FROM 
	(
		SELECT name, salary, score FROM tStaff WHERE depart = '영업부' AND gender = '남'
	) B
);

-- 임시 테이블을 이용하면 더 간단하게 표현할 수 있다.
CREATE GLOBAL TEMPORARY TABLE tBusiMan AS SELECT name, salary, score FROM tStaff;
INSERT INTO tBusiMan SELECT name, salary, score FROM tStaff 
WHERE depart = '영업부' AND gender = '남';

MSSQL: SELECT name, salary, score INTO #tBusiMan FROM tStaff 
WHERE depart = '영업부' AND gender = '남';
마리아 : CREATE TEMPORARY TABLE tBusiMan AS 
SELECT name, salary, score FROM tStaff WHERE depart = '영업부' AND gender = '남';

SELECT * FROM tBusiMan WHERE salary >= (SELECT avg(salary) FROM tBusiMan);

CREATE VIEW vBusiMan AS SELECT name, salary, score FROM tStaff 
WHERE depart = '영업부' AND gender = '남';
SELECT * FROM vBusiMan WHERE salary >= (SELECT avg(salary) FROM vBusiMan);




-- CTE : 뷰와 임시 테이블은 결국 DB에 저장되지만, CTE는 쿼리를 호출할 때
-- 메모리에만 잠시 저장되어 더 빠른 속도를 보인다.
-- 정의와 조회 등의 쿼리를 한번에 실행해야 한다.
WITH tBusiMan AS 
(SELECT name, salary, score FROM tStaff WHERE depart = '영업부' AND gender = '남')
SELECT * FROM tBusiMan WHERE salary >= (SELECT avg(salary) FROM tBusiMan);

WITH tBusiMan(이름, 월급, 성취도) AS 
(SELECT name, salary, score FROM tStaff WHERE depart = '영업부' AND gender = '남')
SELECT * FROM tBusiMan WHERE 월급 >= (SELECT avg(월급) FROM tBusiMan);

WITH tBusiMan AS 
(SELECT name, salary, score FROM tStaff WHERE depart = '영업부' AND gender = '남'),
tBusiGirl AS 
(SELECT name, salary, score FROM tStaff WHERE depart = '영업부' AND gender = '여')
SELECT * FROM tBusiGirl WHERE salary >= (SELECT avg(salary) FROM tBusiMan);

WITH tBusiMan AS 
(SELECT name, salary, score FROM tStaff WHERE depart = '영업부' AND gender = '남'),
tBusiManGod AS 
(SELECT name, salary, score FROM tBusiMan WHERE score > 70)
SELECT * FROM tBusiManGod;

WITH Shopping AS
(SELECT M.member, M.addr, O.item, O.num, O.orderDate FROM tMember M 
INNER JOIN tOrder O ON M.member = O.member)
SELECT * FROM Shopping WHERE num >= (SELECT AVG(num) FROM Shopping);

-- CTE는 내부 동작을 파악하기 어렵기 때문에 VIEW를 이용해서 생김새를 파악하는 방법도 있다.
CREATE VIEW vTemp AS
(SELECT M.member, M.addr, O.item, O.num, O.orderDate FROM tMember M 
INNER JOIN tOrder O ON M.member = O.member);

SELECT * FROM vTemp WHERE num >= (SELECT AVG(num) FROM vTemp);

-- CTE는 재귀적으로 활용 가능하다. (중첩 CTE의 특수한 예)
WITH tFact(num, sum) AS (
	SELECT 1 AS num, 1 AS sum FROM dual
	UNION ALL
	SELECT num + 1, sum * (num + 1) FROM tFact T WHERE T.num < 10
)
SELECT * FROM tFact;

WITH tTree(id, name, parent, depth) AS
(
	SELECT id, name, parent, 0 FROM tDirectory WHERE parent = 0
	UNION ALL
	SELECT D.id, D.name, D.parent, T.depth + 1 FROM tDirectory D 
	INNER JOIN tTree T ON D.parent = T.id
)
SELECT * FROM tTree;

-- 다음은 디렉터리를 표현하는 SQL CTE 쿼리이다. (VER.1)
WITH tTree(id, name, parent, depth, fullpath) AS
(
	SELECT id, name, parent, 0, CAST(name AS VARCHAR(256)) 
	FROM tDirectory WHERE parent = 0
	UNION ALL
	SELECT D.id, D.name, D.parent, T.depth + 1, 
	CAST(CONCAT(CONCAT(T.fullpath, '/'), D.name) AS VARCHAR(256)) 
	FROM tDirectory D 
	INNER JOIN tTree T ON D.parent = T.id
)
SELECT * FROM tTree;

-- 다음은 디렉터리를 표현하는 SQL CTE 쿼리이다. (VER.2)
WITH tTree(id, name, parent, depth, fullpath) AS
(
	SELECT id, name, parent, 0, CAST(name AS VARCHAR(256)) 
	FROM tDirectory WHERE parent = 0
	UNION ALL
	SELECT D.id, D.name, D.parent, T.depth + 1, 
	LPAD('L ', (T.depth + 1) * 4) || D.name 
	FROM tDirectory D 
	INNER JOIN tTree T ON D.parent = T.id
)
SELECT fullpath FROM tTree;

CAST(SPACE((T.depth + 1) * 4) + 'L ' + D.name AS VARCHAR(256))