49. SQLD 2과목 (2)SQL활용

집합연산자
두 개 이상의 테이블에서 조인을 사용하지 않고 연관된 데이터를 조회할 때 사용
SELECT 절의 칼럼 수가 동일하고, SELECT 절의 동일 위치에 존재하는 칼럼의 데이터 타입이 상호 호환할 때 사용 가능


일반 집합 연산자

UNION	합집합	중복을 제거한 결과의 합 정렬
UNION ALL		중복을 포함한 결과의 합을 검색, 정렬X, 빠름
INTERSECT	교집합	양쪽 모두에서 포함된 행을 검색
MINUS (= EXCEPT)	차집합	첫 번째 검색 결과에서 두 번째 검색 결과를 제외한 나머지를 검색
CROSS JOIN	곱집합

* ALIAS는 처음 테이블, 정렬은 마지막 테이블 기준


순수 관계연산자 : 관계형 DB를 새롭게 구현
1. SELECT -> WHERE절로 구현
2. PROJECT -> SELECT절로 구현
3. NATRUAL JOIN -> 다양한 JOIN으로 구현
4. DIVIDE -> 사용d 안 함 ex) {a,x}{a,y}{a,z} divdie {x,z} = {a}





FROM절 JOIN형태
Equi Join : 조인 조건식에 '='를 사용, 두 테이블에서 공통적으로 존재하는 컬럽의 값이 일치되는 행을 연결해서 결과를 생성
Non-Equi Join : '=' 연산자 이외의 비교 연산자를 사용

INNER JOIN	JOIN 조건에서 동일한 값이 있는 행만 반환 USING이나 ON절을 필수적으로 사용 * on 조건과 where 조건의 결과가 같다
NATURAL JOIN	Oracle 두 테이블 간의 동일한 이름을 갖는 모든 칼럼들에 대해 EQUI JOIN 수행 NATURAL JOIN이 명시되면 추가로 USING, ON, WHERE JOIN절에서 조건을 정의할 수 없다

ON 조건절	USING 조건절
Oracle 같은 이름을 가진 칼럼들 중에서 원하는 칼럼에 대해서만 선택적으로 EQUI JOIN을 할 수 있다 JOIN 칼럼에 대해서 ALIAS나 테이블 이름과 같은 접두사를 붙일 수 없다	JOIN에 사용될 컬럼을 지정 (같아야 하는 속성 명시) ON조건절과 WHERE조건절을 분리하여 이해가 쉬움 칼럼명이 다르더라도 JOIN조건을 사용 가능 ALIAS나 테이블명 반드시 사용 SELECT 칼럼 FROM 테이블a JOIN 테이블b USING (조인 칼럼명)

 

CROSS JOIN	= 카티션 곱 (조인 조건절을 적지 않게 되면 해당 테이블에 대한 모든 데이터를 전부 가져옴) 양쪽 집합의 M*N건의 데이터 조합 발생
OUTER JOIN	JOIN 조건에서 동일한 값이 없는 행도 반환 가능하다 USING이나 ON조건절 반드시 사용해야 함 SQL 식에서 (+) 안붙은 쪽으로 JOIN한다
LEFT OUTER JOIN = LEFT JOIN	왼쪽 테이블 A의 모든 데이터와 A와 B 테이블의 중복데이터들이 검색 우측값에서 같은 값이 없는 경우 NULL값으로 채운다 ex) SELECT * FROM 테이블a LEFT OUTER JOIN 테이블b ON (a.번호 = b.번호)
RIGHT OUTER JOIN = RIGHT JOIN	오른쪽 테이블 B의 모든 데이터와 B와 A 테이블의 중복데이터들이 검색
FULL OUTER JOIN	좌우측 테이블의 모든 데이터를 읽어 JOIN하여 결과를 생성한다 중복 데이터는 삭제

 

계층형 질의
테이블에 계층형 데이터가 존재하는 경우 데이터를 조회하기 위해 사용
* 계층형 데이터 : 동일 테이블에 계층적으로 상위/하위 데이터가 포함된 데이터 ex) 부서table에서 상위부서, 하위부서

 

 

계층형질의 특징

SQL Server에서의 계층형 질의문은 STE를 재귀 호출함으로써 계층 구조를 전개

SQL Server에서의 계층형 질의문은 앵커 멤버를 실행하여 기본 결과 집합을 만들고, 이후 재귀 멤버를 지속적으로 실행

Oracle의 계층형 질의문에서 WHERE절은 모든 전개를 진행하고, 필터 조건으로서 조건을 만족하는 데이터만을 추출하는데 사용

Oracle의 계층형 질의문에서 PRIOR 키워드는 SELECT, WHERE, CONNECT BY절에 사용할 수 있다

 

 

계층형 질의 SQL문

START WITH	계층 구조 전개의 시작 위치 지정
CONNECT BY	다음에 전개될 자식 데이터 지정
PRIOR	CONNECT BY절에 사용 현재 읽은 칼럼을 지정 PRIOR = 부모 형태를 사용하면 계층구조에서 부모 데이터에서 자식 데이터 방향으로 (부모 -> 자식) 전개하는 순방향 전개를 한다. 반대는 역방향 전개
NOCYCLE	동일한 데이터가 전개되지 않음
ORDER SIBLINGS BY	형제 노드(동일 level) 간의 정렬 수행
WHERE	모든 전개를 수행한 후에 지정된 조건을 만족하는 데이터만 추출한다 (=필터링)

가상 칼럼
Oracle이 계층형 질의를 사용할 때 제공한다

LEVEL	루트 데이터면 1, 그 하위 데이터면 2. 리프 데이터까지 1씩 증가
CONNECT_BY_ISLEAF	해당 데이터가 리프 데이터면 1, 그렇지 않으면 0
CONNECT_BY_ISCYCLE	해당 데이터가 조상이면 1, 아니면 0 (CYCLE옵션 사용했을 시만 사용 가능)

계층형 질의 함수

SYS_CONNECT_BY_PATH	루트 데이터부터 현재 전개할 데이터까지의 경로를 표시한다. SYS_CONNECT_BY_PATH (칼럼, 경로분리자)
CONNECT_BY_ROOT	현재 전개할 데이터의 루트 데이터를 표시한다. 단항 연산자. CONNECT_BY_ROOT 칼럼

셀프 조인
한 테이블 내 두 칼럼이 연관 관계가 있을 때 동일 테이블 사이의 조인
FROM절에 동일 테이블이 2번 이상 나타난다
반드시 테이블 별칭을 사용


서브쿼리
하나의 문 안에 포함되어 있는 또 다른 SQL문
알려지지 않은 기준을 이용한 검색에 사용
서브쿼리를 괄호로 감싸서 사용
단일 행 또는 복수 행 비교 연산자와 함께 사용 가능
단일행 비교 연산자는 서브쿼리의 결과가 반드시 1건 이하여야 하고, 복수 행 비교 연산자는 결과 건수와 상관없다.
서브쿼리에서는 ORDER BY를 사용하지 못한다
SELECT, FROM, WHERE, HAVING, ORDER BY, INSERT-VALUES, UPDATE-SET 절에 사용 가능

 

SELECT절	scala	하나의 칼럼처럼 사용 SELECT 절의 결과를 FROM 절에서 하나의 테이블처럼 사용하고 싶을 때 사용
FROM절	inline view	하나의 테이블처럼 사용. 메인쿼리의 칼럼에 사용 가능 ORDER BY 사용 가능
WHERE절	일반 서브쿼리	거의 모든 서브쿼리 (중첩 서브쿼리) SELECT 절의 결과를 WHERE 절에서 하나의 변수(상수)처럼 사용하고 싶을 때 사용 단일행, 다중행, 다중칼럼 서브쿼리
GROUP BY절	사용불가
HAVING절	일반 서브쿼리	거의 모든 서브쿼리 (중첩 서브쿼리)
ORDER BY절	scala

 

 

단일 행 비교연산자
-, <, >, <> 등

다중 행 비교연산자
IN 같은 값
ANY / SOME 최소값
ALL 최대값
EXISTS(서브 쿼리) 서브 쿼리의 결과가 한 건이라도 존재하면 TRUE 없으면 FALSE를 리턴

동작 방식에 따른 서브쿼리 분류
비연관 서브쿼리 : 서브쿼리가 메인쿼리 칼럼 안가짐. 메인쿼리에 값 제공 목적
연관 서브쿼리 : 서브쿼리가 메인쿼리 칼럼 가짐

반환 데이터에 따른 서브쿼리 종류
단일행 서브쿼리 : 실행결과 1건 이하
다중행 서브쿼리 : 실행결과 여러 건
다중컬럼 서브쿼리 : 실행결과 컬럼 여러 개


VIEW (가상테이블)
테이블은 실제로 데이터를 가지고 있는 반면, 뷰는 실제 데이터를 가지고 있지 않다
실행 시점에 SQL 재작성하여 수행

독립성 : 테이블 구조가 변경되어도 뷰를 사용하는 응용 프로그램은 변경하지 않아도 된다
편리성 : 복잡한 질의를 뷰로 생성함으로써 관련 질의를 단순하게 작성할 수 있다
보안성 : 직원의 급여정보와 같이 숨기고 싶은 정보가 존재할 때 사용
ex) CREATE VIEW v_player_team AS SELECT ... ;
DROP VIEW v_player_team;





★그룹함수

ROLL UP	Subtotal을 생성하기 위해 사용. 계층구조이므로 인수의 순서 중요
CUBE	그룹핑 컬럼이 가질 수 있는 모든 경우의 수에 대하여 소계와 총계를 생성 ROLLUP에 비해 시스템 부하가 심하다 인수의 순서 상관 없음
GROUPING SETS	인수들에 대한 개별 집계를 구할 수 있다 다양한 소계 집합 생성 가능 ROLLUP과 CUBE와 달리 계층 구조가 나타나지 않으므로 인자의 순서 상관 없음
GROUPING	집계 표시면 1, 아니면 0

* Grouping Columns의 수를 N이라고 했을 때 N+1 Level의 Subtotal이 생성
[문제풀기] NULL 찾기 -> 총합 찾기 -> 총합 있으면 ROLL UP, CUBE / 없으면 GROUPING SETS


윈도우함수
RANK : 동일 값에 대해 동일 순위 부여 ex) 1등, 1등, 3등
DENSE RANK : 동일 순위를 하나의 등수로 간주 ex) 1등, 1등, 2등, 3등
ROW_NUMBER : 동일 값이라도 고유한 순위 부여 ex) 1등, 2등, 3등, 4등

ROWS : 행의 수 선택
RANGE : 값의 범위 선택
* 결과값의 차이점 알기. 같은 값이 있으면 RANGE

PATRITION BY : 전체 집합을 기준에 의해 소그룹으로 나눌 수 있다. =group by와 유사한 기능
ORDER BY


행 순서 관련 함수 (ONLY Oracle)
FIRST_VALUE : 파티션별 윈도우의 처음 값
LAST_VALUE : 파티션별 윈도우의 마지막 값

LAG : 파티션별 윈도우에서 이전 몇 번째 행의 값
LEAD : 파티션별 윈도우에서 이후 몇 번째 행의 값


비율 관련 함수
RATIO_TO_REPORT : SUM 파티션 내 전체 에 대한 행별 칼럼 값의 백분율을 소수점으로 구할 수 있다. >0, <=1
PERCENT_RANK : 파티션별 윈도우에서 처음 값을 0, 마지막 값을 1로 하여 행의 순서별 백분율을 구한다. 0>=,<=1
CUME_DIST : 현재 행보다 작거나 같은 건수에 대한 누적백분율을 구한다. >0, <=1
NTILE : 파티션 별 전체 건수를 인수 값으로 N등분한 결과를 구할 수 있다.





DCL
유저 생성하고 권한을 제어할 수 있는 명령어
* 시스템 권한 : SQL 사용자가 문을 실행하기 위해 필요한 적절한 권한
GRANT : 사용자(User)에게 접속권한, 오브젝트 생성권한, DBA 권한 등을 부여할 수 있는 명령어 ex) GRANT CREATE TABLE TO pjs;
REVOKE : 사용자(User)에게 부여한 권한을 다시 회수하는 명령어 ex) REVOKE CREATE TABLE FROM pjs;

ROLE : 유저에게 알맞은 권한들을 한 번에 부여하기 위해 사용. 명령어가 아닌 객체이다.
CREATE ROLE LOGIN_TABLE;
DROP USER PJS CASCADE; * CASCADE : 하위 오브젝트까지 삭제


Oracle SQL과 Server의 사용자 아키텍처 차이

Oracle	SQL Server
유저를 통해 DB 접속 ID/PW 방식으로 인스턴스에 접속하고, 그에 해당하는 스키마에 오브젝트 생성 등의 권한을 부여받게 됨	인스턴스에 접속하기 위해 로그인이라는 것을 생성 인스턴스 내에 존재하는 다수의 DB에 연결하여 작업하기 위해, 유저를 생성한 후 로그인과 유저를 매핑 Windows 방식과 혼합 모드 방식이 존재

절차형 SQL
SQL문의 연속적인 실행이나 조건에 따른 분기, 반복 등의 제어를 활용해 다양한 기능을 수행하는 DB 저장 모듈 생성
Procedure, User Defined Function, Trigger 등의 프로그래밍 언어가 해당된다.
- Procedure : 특정 기능을 수행하는 일종의 트랜잭션 언어. 호출을 통해 실행되며 미리 저장해 놓은 SQL 작업을 수행
- Function : 하나 이상의 프로시저, 함수, 변수, 예외 등의 묶음
- Trigger : 특정한 테이블에 INSERT, UPDATE, DELETE와 같은 DML문이 수행되었을 때 DB에서 자동으로 동작하도록 작성된 프로그램. 사용자 호출이 아닌 DB 자동 수행

* 저장 모듈 : PL/SQL DB 문장을 서버에 저장하여 사용자와 애플리케이션 사이에서 공유할 수 있도록 만든 일종의 SQL컴포넌트 프로그램. 독립적으로 실행되거나 다른 프로그램으로부터 실행될 수 있는 완전한 실행 프로그램


절차형 SQL의 특징
1. Block 구조로 되어있어 각 기능별로 모듈화 가능
2. 변수, 상수 등을 선언하여 SQL 문장 간 값을 교환
3. IF, LOOP 등의 절차형 언어를 사용하여 절차적인 프로그램이 가능하도록 한다.
4. DBMS 정의 에러나 사용자 정의 에러를 정의하여 사용할 수 있다.
5. Oracle에 내장되어 있어 호환성이 좋다.
6. 응용 프로그램의 성능을 향상시킨다.
7. Block -> 단위로 처리 통신량을 줄일 수 있다.


절차형 SQL의 구성
DECLARE : BEGIN~END 절에서 사용될 변수와 인수에 대한 정의 및 데이터 타입 선언부
BEGIN~END :처리하고자 하는 SQL문과 여러 가지 비교문, 제어문을 이용해서 필요한 로직 처리
- BEGIN : 프로시저, 사용자 정의함수가 실행되는 시작점
- END : 프로시저, 사용자 정의함수가 실행되는 종료점
EXCEPTION : BEGIN~END 절에서 실행되는 SQL문이 실행될 때 에러가 발생하면 그 에러를 어떻게 처리할지 정의하는 예외 처리부

프로시저와 트리거의 차이점
프로시저 : BEGIN~END COMMIT절 내에 ROLLBACK과 같은 트랜잭션 종료 명령어 사용가능. EXECUTE 명령어로 실행
트리거 : BEGIN~END절 내에 사용 불가. 생성 후 자동 실행

T-SQL : 근본적으로 SQL Server를 제어하는 언어