참고
VIEW
NOTE
SQL에서 View는 하나 이상의 테이블에서 유도된 가상 테이블 입니다!
View는 실제로 데이터를 저장하지는 않고, 정의된 SQL에 기반하여 데이터를 제공합니다. View는 복잡한 쿼리를 숨기고 보안을 강화하는 용도로 사용할 수 있습니다.
CREATE VIEW 뷰이름 AS
SELECT 열1, 열2, ...
FROM 테이블명
WHERE 조건;
SQL
복사
사용 예시
•
View는 데이터를 저장하지 않고, 쿼리 기반으로 데이터를 동적으로 생성합니다.
•
View는 가상 테이블인 만큼 insert와 update가 가능합니다.
•
View를 사용하면 사용자가 특정 데이터만 볼 수 있도록 제한할 수 있습니다.
•
단순한 SQL 쿼리를 View로 정의함으로써, 사용자는 복잡한 쿼리를 실행하지 않고도 데이터에 접근할 수 있습니다.
View는 엄연히 SuqQuery와 다른 개념입니다.
•
View는 DB에 영구적으로 존재하며, 다른 쿼리에서 반복적으로 참조가능합니다.
•
SubQuery는 일시적이며, 재사용되지 않습니다.
VIEW 사용 예시
NOTE
# 테이블 생성
CREATE TABLE Department
(
DeptID INT NOT NULL PRIMARY KEY COMMENT '부서 ID',
DeptName VARCHAR(100) NOT NULL COMMENT '부서 이름',
ParentDeptID INT NULL COMMENT '상위 부서 ID'
) COMMENT '부서 정보';
CREATE TABLE Employees (
EmployeeID INT PRIMARY KEY,
Name VARCHAR(100),
Email VARCHAR(100),
Salary DECIMAL(10, 2),
DeptID INT,
FOREIGN KEY (DeptID) REFERENCES Department(DeptID)
);
# View 생성
CREATE VIEW EmployeeDeptInfo AS
SELECT
e.Name,
e.Email,
d.DeptName
FROM
Employees e
JOIN
Department d ON e.DeptID = d.DeptID;
SQL
복사
DDL
SELECT * FROM EmployeeDeptInfo;
SQL
복사
DML
WITH(임시 테이블)
NOTE
SQL에서 WITH절은 일시적인 결과 집합을 생성하여 SQL문 내에서 1번 이상 재사용할 수 있게 해줍니다.
WITH와 같이 임시로 생기는 테이블을 CTE(Common Table Expression)라고 합니다. TABLE을 만들지 않고도 TABLE을 만든 것과 같은 효과를 내는데, 실제로는 temp라는 임시 테이블에 저장됩니다.
WITH CTE_이름 AS (
SELECT ~ # 쿼리를 통한 임시 테이블 생성
)
SELECT * FROM CTE_이름;
SQL
복사
예시 코드
•
SELECT에서 WITH에서 생성한 CTE을 참조하여 데이터에 접근할 수 있습니다.
•
주로 동일한 SQL이 반복되어서 사용될 때 성능을 높이기 위해서 사용됩니다.
•
임시 테이블인 만큼 실제로 메모리를 차지하는 단점이 있습니다.
temp에 저장되는 임시테이블은 쿼리문이 시작될때 생성되며 쿼리가 끝나면 자동으로 제거됩니다.
WITH 사용 예시
NOTE
CREATE TABLE TICKET
(
TCKT_NO VARCHAR(10) NOT NULL PRIMARY KEY COMMENT '티켓번호',
TCKT_NM VARCHAR(50) NOT NULL COMMENT '티켓명',
WRKFLW_CD VARCHAR(10) NOT NULL COMMENT '워크플로코드',
STTS_CD VARCHAR(6) DEFAULT 'WAIT' NOT NULL COMMENT '상태코드',
REG_DT DATETIME DEFAULT CURRENT_TIMESTAMP() NOT NULL COMMENT '등록일시',
MDFCN_DT DATETIME DEFAULT CURRENT_TIMESTAMP() NOT NULL COMMENT '수정일시'
) COMMENT '티켓';
SQL
복사
DDL
With
# 진행중인 티켓 테이블조회
ActiveTickets AS (SELECT TCKT_NO,
TCKT_NM,
WRKFLW_CD,
STTS_CD,
DATEDIFF(IFNULL(CRLTN_CMPTN_DT, CURRENT_TIMESTAMP()), REG_DT) As DurationDays
FROM TB_TPS_TK_001
WHERE STTS_CD NOT IN ('COMPLETE', 'CANCELLED')),
# 진행중인 티켓에서 워크플로코드 별 개수
TicketCounts AS (SELECT WRKFLW_CD,
COUNT(*) AS TotalTickets
FROM TB_TPS_TK_001
WHERE STTS_CD NOT IN ('COMPLETE', 'CANCELLED')
GROUP BY WRKFLW_CD),
# 워크플로우 코드 별 배포 평균시간
AverageDutation AS (SELECT WRKFLW_CD,
AVG(DurationDays) As AvgDuration
FROM ActiveTickets # 상위 CTE 사용 가능
GROUP BY WRKFLW_CD)
# 워크플로우 코드별 티켓 개수, 티켓 평균시간
SELECT tc.WRKFLW_CD,
tc.TotalTickets,
ad.AvgDuration
FROM TicketCounts tc
JOIN
AverageDutation ad ON tc.WRKFLW_CD = ad.WRKFLW_CD
ORDER BY ad.AvgDuration DESC;
SQL
복사
DML
WITH RECURSIVE(재귀문)
NOTE
WITH RECURSIVE는 SQL의 고급 기능 중 하나로, 재귀적인 쿼리를 작성할 수 있게 해주는 기능입니다.
WITH RECURSIVE 재귀_CTE_이름 AS (
-- 초기 쿼리: 재귀의 시작점
(초기_셀렉트_쿼리)
UNION [ALL]
-- 재귀 쿼리: 자기 자신을 참조하여 반복
(재귀_셀렉트_쿼리)
)
SELECT * FROM 재귀_CTE_이름;
SQL
복사
•
초기 쿼리: 재귀의 초기값으로 사용된다.
•
재귀 쿼리: 재귀적으로 실행될 쿼리를 의미하며, 자신을 참조하여 데이터를 확장해나간다.
사용 예시
NOTE
CREATE TABLE TICKET
(
TCKT_NO VARCHAR(10) NOT NULL PRIMARY KEY COMMENT '티켓번호',
TCKT_NM VARCHAR(50) NOT NULL COMMENT '티켓명',
WRKFLW_CD VARCHAR(10) NOT NULL COMMENT '워크플로코드',
STTS_CD VARCHAR(6) DEFAULT 'WAIT' NOT NULL COMMENT '상태코드',
REG_DT DATETIME DEFAULT CURRENT_TIMESTAMP() NOT NULL COMMENT '등록일시',
MDFCN_DT DATETIME DEFAULT CURRENT_TIMESTAMP() NOT NULL COMMENT '수정일시'
) COMMENT '티켓';
SQL
복사
DDL
WITH RECURSIVE DeptCTE AS(
# 상위 부서가 없는 부서(초기상태)
SELECT DeptID, DeptName, ParentDeptID, 1 AS Level
FROM Department
WHERE ParentDeptID IS NULL
UNION ALL
# 현재 부서가 부모인 경우 JOIN
# JOIN이 실패할때 까지 반복
SELECT d.DeptID, d.DeptName, d.ParentDeptID, dc.Level + 1
FROM Department d
JOIN DeptCTE dc ON dc.DeptID = d.ParentDeptID
)
SELECT DeptID, DeptName, ParentDeptID, Level
FROM DeptCTE
ORDER BY Level, DeptID
SQL
복사
DML