2-2. SQL 활용

1. 표준 조인

SQL의 연산

 

집합 연산	UNION	합집합
	INTERSECTION	교집합
	DIFFERENCE	차집합
	PRODUCT	곱집합(모든 데이터 조합)
관계 연산	SELECT	조건에 맞는 행 조회
	PROJECT	조건에 맞는 칼럼 조회
	JOIN
	DIVIDE	공통 요소를 추출하고 분모 릴레이션의 속성을 삭제한 후 중복된 행 제거 (But, 현재 사용하지 않음)

 

 

• NATURAL JOIN: 같은 이름을 가진 칼럼 전체에 대한 등가 조인, USING 조건절이나 ON 조건절 사용 불가, 같은 데이터 유형 칼럼만 조인 가능, 앨리어스나 테이블명 사용 불가

 

• INNER JOIN: 행에 동일한 값이 있는 칼럼 조인, JOIN의 디폴트 옵션, ON 조건절 필수, CROSS JOIN이나 OUTER JOIN과 동시 사용 불가, 두 테이블에 동일 이름 칼럼이 있을 경우 SELECT절에 앨리어스 필수

 

• USING 조건절 : 같은 이름을 가진 칼럼들 중에서 원하는 칼럼에 대해서만 선택적으로 JOIN 가능. SQL Server에서는 지원 X

 

• ON 조건절 : 칼럼 명이 다르더라도 JOIN 조건을 사용할 수 있음, WHERE 검색 조건은 충돌 없이 사용 가능, 괄호는 옵션, ★ 반드시 ALIAS나 테이블명과 같은 접두사를 사용

 

• CROSS JOIN : 가능한 모든 조합으로 조인

 

• OUTER JOIN : 조인 조건에서 행에 동일한 값이 없는 칼럼 조인, USING 조건절이나 ON 조건절 필수

 

 

-- INNER JOIN
SELECT 칼럼s FROM 테이블1 A JOIN 테이블2 B USING (칼럼명);
SELECT 칼럼s FROM 테이블1 A, 테이블2 B WHERE A.칼럼=B.칼럼;
SELECT 칼럼s FROM 테이블1 A INNER JOIN 테이블2 B ON A.칼럼=B.칼럼;

-- NATURAL JOIN
SELECT 칼럼s FROM 테이블1 NATURAL JOIN 테이블2;
-- ON 조건절:다른 이름을 가진 칼럼 간 조인 가능
SELECT 칼럼s FROM 테이블1 A JOIN 테이블2 B ON (A.칼럼=B.칼럼); 

-- CROSS JOIN
SELECT 칼럼 FROM 테이블1, 테이블2; (조인 조건이 없을 때 발생 ↔ NATURAL JOIN은 명시해야 됨)

-- LEFT OUTER JOIN : 
SELECT 칼럼s FROM 테이블1 A
	LEFT OUTER JOIN 테이블2 B
	ON (A.칼럼=B.칼럼);
-- FULL OUTER JOIN: LEFT와 RIGHT OUTER JOIN 포함
SELECT 칼럼s FROM 테이블1 A 결과:
	FULL OUTER JOIN 테이블2 B
	ON (A.칼럼=B.칼럼);

 

 

 

---

2. 집합 연산자

집합 연산자

조인 없이 여러 테이블의 관련 데이터를 조회하는 연산자

 

집합 연산자를 사용하기 위한 제약조건

• Select 절의 컬럼 수가 동일
• Select 절의 동일 위치 데이터 타입이 상호 호환 가능해야함 (동일할 필요는 x)

 

UNION : 합집합, 칼럼 수와 데이터 타입이 모두 동일한 테이블 간 연산만 가능

SELECT 칼럼명 FROM 테이블명 A WHERE 조건절 UNION SELECT 테이블명 WHERE 조건절;

 

UNION ALL : 중복된 행도 전부 출력하는 합집합, 정렬 안함 (↔ UNION은 정렬을 유발함), 집합 연산자에 속함

SELECT 칼럼명 FROM 테이블명 A WHERE 조건절 UNION ALL SELECT 테이블명 WHERE 조건절;

 

INTERSECT : 교집합

SELECT 칼럼명 FROM 테이블명 A WHERE 조건절MS INTERSECT SELECT 테이블명 WHERE 조건절;

 

MINUS, EXCEPT : 차집합

SELECT 칼럼명 FROM 테이블명 A WHERE 조건절 MINUS SELECT 테이블명 WHERE 조건절;

 

 

 

 

---

3. 계층형 질의와 셀프 조인

계층형 질의(Hierarchical Query)

테이블의 계층형 데이터를 조회하기 위해 사용

계층형 데이터: 동일 테이블에 계층적으로 상/하위 데이터가 포함된 데이터

 

 

Oracle 계층형 질의

 

• CONNECT BY : 다음에 전개될 자식 데이터 지정
• PRIOR : CONNECT BY 절에 사용되며, 현재 읽은 칼럼을 지정한다.
• LEVEL : 루트 데이터이면 1, 그 하위 데이터면 2, 리프 데이터까지 1씩 증가
• CONNECT_BY_ISLEAF : 해당 데이터가 리프 데이터면1, 그렇지 않으면 0
• CONNECT_BY_ISCYCLE : 해당 데이터가 조상이면 1, 아니면 0
• SYS_CONNECT_BY_PATH : 루트 데이터부터 현재 전개할 데이터까지의 경로를 표시
• CONNECT_BY_ROOT : 현재 전개할 데이터의 루트 데이터를 표시. 단항 연산자

 

SQL Server 계층형 질의

CTE(Common Table Expression)로 재귀 호출

 

셀프 조인

동일 테이블 사이의 조인, 반드시 테이블 별칭(Alias)을 사용

 

 

 

---

4. 서브쿼리

서브쿼리

하나의 SQL문안에 포함되어있는 또다른 SQL문

 

서브쿼리가 SQL문에서 사용 가능한 곳

• Select절, From절, Where절, Having절, Order By절
• Insert문의 Values절, Update 문의 SET절 (delete만 안됨)

 

서브쿼리 분류

동작 방식에 따른 분류

‒ 비연관 서브쿼리 : 메인쿼리 칼럼을 가지고 있지 않는 서브쿼리, 메인쿼리에 값을 제공하기 위한 목적으로 주로 사용함

‒ 연관 서브쿼리 : 메인쿼리의 결과를 조건이 맞는지 확인하기 위한 목적으로 주로 사용

반환 데이터 형태에 따른 분류 ‒ 단일 행 서브쿼리: 실행 결과가 1건 이하인 서브쿼리, 단일 행 비교 연산자와 함께 사용 ‒ 다중 행 서브쿼리: 실행 결과가 여러 건인 서브쿼리, 다중 행 비교 연산자와 함께 사용

 

※ 다중 행 비교 연산자

 

다중 행 연산자	설명
IN	서브쿼리의 결과 중 하나의 값이라도 동일하다는 조건
ANY	서브쿼리의 결과 중 하나의 값이라도 만족한다는 조건
ALL	서브쿼리의 모든 결과값을 만족한다는 조건
EXISTS	서브쿼리의 결과를 만족하는 값이 존재하는지 여부를 확인하는 조건

‒ 다중 칼럼 서브쿼리: 실행 결과로 여러 칼럼 반환, 주로 메인쿼리의 조건과 비교하기 위해 사용 (비교하고자 하는 칼럼의 개수와 위치가 동일해야 함)

 

 

스칼라 서브쿼리

값 하나를 반환하는 서브쿼리, SELECT절에 사용하는 서브쿼리

 

 

뷰

가상의 테이블. 실제 데이터를 가지고 있지 않음. FROM절에 사용하는 뷰는 인라인 뷰(Inline View)라고 함

 

뷰의 장점

 

독립성	테이블 구조 변경 자동 반영
편리성	쿼리를 단순하게 작성할 수 있음, 자주 사용하는 SQL문의 형태를 뷰로 생성하여 사용할 수 있음
보안성	뷰를 생성할 때 칼럼을 제외할 수 있음

 

 

 

---

5. 그룹 함수

ANSI/ISO 표준 데이터 분석 함수: 집계 함수, 그룹 함수, 윈도우 함수

 

그룹 함수(Group Function)

합계 계산 함수, NULL을 빼고 집계함 (~ 집계 함수), 결과값 없는 행은 출력 안함

 

• ROLLUP : GROUP BY로 묶인 칼럼의 소계 계산, 계층 구조로 GROUP BY의 칼럼 순서가 바뀌면 결과 값 바뀜
  Rollup(A, B) != Rollup(B, A)

 

• CUBE : 조합 가능한 모든 값에 대해 다차원 집계

 

• GROUPING SETS : 특정 항목에 대한 소계 계산, GROUP BY의 칼럼 순서와 무관하게 개별적으로 처리함
  Grouping Sets(A, B) == Grouping Sets(B, A)

 

표현식	출력값
GROUP BY ROLLUP (E1,E2)	E1과 E2별 소계 / E1별 소계 / 총합계
GROUP BY CUBE (E1,E2)	E1과 E2별 소계 / E1별 소계 / E2별 소계 / 총합계
GROUP BY GROUPING SETS (E1,E2)	E1별 소계 / E2별 소계
CUBE (E1,E2)	E1과 E2별 소계 / E1별 소계 / E2별 소계 / 총합계
ROLLUP (E1,E2)	E1과 E2별 소계 / E1별 소계 / 총합계

※ ‘GROUP BY CUBE (E1,E2)’와 ‘GROUP BY GROUPING SETS (E1,E2,(E1,E2),())’는 동일한 결과 출력

GROUPING : 그룹 함수에서 생성되는 합계를 구분해주는 함수, 소계나 합계가 계산되면 1 아니면 0 반환

 

 

 

---

6. 윈도우 함수

윈도우 함수(Window Function)

여러 행 간의 관계 정의 함수, 중첩 불가, OVER문구가 키워드로 필수 포함.

SELECT WINDOW_FUNCTION (ARGUMENTS) OVER ( [PARTITION BY 칼럼] [ORDER BY 절] [WINDOWING 절] ) FROM 테이블 명;
SELECT 윈도우함수(A) OVER (PARTITION BY 칼럼 ORDER BY 칼럼 윈도잉절) FROM 테이블명;

 

윈도잉절

함수의 대상이 되는 행 범위 지정

• BETWEEN A AND B : 구간 지정
• N PRECEDING, N FOLLOWING : N번째 앞 행, N번째 뒤 행
• UNBOUNDED PRECEDING, UNBOUNDED FOLLOWING : 첫 행, 끝 행
• CURRENT ROW : 현재 행
• ROWS, RANGE : 행 지정, 값의 범위 지정

 

그룹 내 순위 함수

• RANK : 중복 순위 포함 (ex. 1 2 2 4)
• DENSE_RANK : 중복 순위 무시 (중간 순위를 비우지 않음. ex. 1 2 2 3)
• ROW_NUMBER : 단순히 행 번호 표시, 값에 무관하게 고유한 순위 부여

SELECT JOB, ENAME, SAL, RANK( ) 
 OVER (ORDER BY SAL DESC) ALL_RANK, RANK( ) 
 OVER (PARTITION BY JOB ORDER BY SAL DESC) JOB_RANK FROM EMP;

 

일반집계 함수: SUM, MAX, MIN, AVG, COUNT

 

그룹 내 행 순서 함수

• FIRST_VALUE 함수: 파티션별 윈도우에서 가장 먼저 나온 값으로 모두 대체
• LAST_VALUE 함수: 파티션별 윈도우에서 가장 나중에 나온 값으로 모두 대체
• ★ LAG 함수 : 현재 읽혀진 데이터의 이전 값을 알아내는 함수
• ★ LEAD 함수: 현재 읽혀진 데이터의 이후 값을 알아내는 함수

 

그룹 내 비율 관련 함수

• PERCENT_RANK : 제일 먼저 나오는 것을 0으로, 제일 늦게 나오는 것을 1로 하여, 행의 순서별 백분율
• CUME_DIST : 전체건수에서 현재 행보다 작거나 같은 건수에 대한 누적백분율
• NTILE(A) : 전체 데이터를 ARGUMENT 값으로 A등분
• RATIO_TO_REPORT : 전체 SUM(칼럼)값에 대한 행별 칼럼 값의 백분율

 

 

 

---

7. DCL

DCL (DATA CONTROL LANGUAGE)

유저를 생성하거나 권한을 제어하는 명령어, 보안을 위해 필요함

-- GRANT: 권한 부여
SQL>> GRANT 권한 ON 오브젝트 TO 유저명;

-- REVOKE: 권한 제거
SQL>> REVOKE 권한 ON 오브젝트 TO 유저명;

 

권한(Privileges)

• SELECT, INSERT, UPDATE, DELETE, ALTER, ALL : DML 관련 권한
• REFERENCES : 지정된 테이블을 참조하는 제약조건을 생성하는 권한
• INDEX : 지정된 테이블에서 인덱스를 생성하는 권한

 

Oracle에서 제공하는 유저

 

유저	역할
SCOTT	테스트용 샘플 유저
SYS	DBA 권한이 부여된 최상위 유저
SYSTEM	DB의 모든 시스템 권한이 부여된 DBA

 

 

ROLE

권한의 집합, 권한을 일일이 부여하지 않고 ROLE로 편리하게 여러 권한을 부여할 수 있음

 

ROLE	권한
CONNECT	CREATE SESSION
RESOURCE	CREATE CLUSTER          CREATE PROCEDURE CREATE TYPE                 CREATE SEQUENCE CREATE TRIGGER           CREATE OPERATOR CREATE TABLE               CREATE INDEXTYPE

 

 

 

---

8. 절차형 SQL

절차형 SQL

일반적인 개발언어처럼 절차지향적인 프로그램을 작성할 수 있도록 제공하는 기능, SQL문의 연속적인 실행 및 조건에 따른 분기처리를 이용하여 특정 기능을 수행하는 저장 모듈 생성 가능

 

 

PL/SQL (Oracle)

블록 내에 DML, 쿼리, IF나 LOOP 등을 사용할 수 있음

PL/SQL 구조

• Declare(선언부): 블록에서 사용할 변수나 인수에 대한 정의
• Begin(실행부): 처리할 SQL문 정의
• Exception(예외 처리부): 블록에서 발생한 에러 처리 로직 정의, 유일한 선택 항목

 

T-SQL (SQL Server)

T-SQL은 근본적으로 SQL Server를 제어하기 위한 언어

 

프로시저(Procedure)

주로 DML을 사용해 주기적으로 진행해야되는 작업을 저장, 별도의 호출을 통해 실행

 

사용자 정의 함수 (User Defined Function)

절차형 SQL을 로직과 함께 DB 내에 저장해 놓은 명령문 집합, RETURN을 통해 반드시 하나의 값 반환 (↔ 프로시저)

 

트리거(Trigger)

DML문이 수행되었을 때 자동으로 동작하는 프로그램 (↔ 프로시저는 EXECUTE로 실행함), DCL와 TCL 실행 불가 (↔ 프로시저는 사용 가능함)