2과목(2장: SQL활용)

JOIN

- Left join이라고 말하면 Left outer join을 말하는 것. 

- ORACLE에서 OUTER JOIN 구문을 + 기호로 사용하여 처리할 수 있음

- JOIN에서 ON을 통해 조건을 걸 수 있음

 

M*N cartesian product(카티시안곱) => CROSS JOIN

 

SELF JOIN은 하나의 테이블에서 두개의 칼럼이 연관관계를 가질 때 사용

동일 테이블 조인을 수행하면 테이블과 칼럼이름이 모두 동일하기 때문에 식별을 위해서 반드시 별칭ALIAS를 사용

 

EXISTS

EXISTS(~~~) : 괄호 안 조건에 충족하는 결과를 반환

NOT EXISTS(~~~) : 괄호 안 조건에 충족하는 결과를 제외한 모든 것을 반환

SELECT A, B FROM TAB1 EXCEPT 
SELECT A, B FROM TAB2
=> 묶여있던 SELECT A, B를 NOT IN으로 바꿔서 표현할때 A와 B를 동시에 진행해야 함. 
만약, A에서 NOT IN~~ , B에서 NOT IN~~~ 이런식으로 따로따로 NOT IN을 진행하면 값이 달라짐.

IN과 EXISTS의 차이

IN : 모든 값을 확인한 뒤, 수행 중단

EXISTS : 해당 ROW에 조건만족하는 결과의 존재여부를 확인 후, 수행 중단  

 

EXCEPT

- 차집합(LEFT/RIGHT OUTER ONLY와 같은)에서 연산으로 NOT IN 또는 NOT EXISTS로 대체하여 처리 가능

 

개념정리

UNION : 여러개 SQL문의 결과에 대한 합집합으로 결과에서 모든 중복된 행은 하나의 행으로 만듦(=중복제거)

UNION ALL : 중복된 행이 포함된 합집합(같은내용 여러개 일 수도)

INTERSECT : 여러 SQL문의 교집합. 중복된 행은 하나의 행(=중복값없음)

EXCEPT : 중복행은 하나로(일부 DB에서는 MINUS를 사용) => SQL가 - SQL나 = SQL가에서 나를뺀 차집합

UNION은 UNION
INTERSECTION은 INTERSECTION
DIFFERENCE는 EXCEPT (ORACLE은 MINUS)
PRODUCT는 CROSS JOIN 으로 기능 구현

UNION ALL

- UNION ALL을 사용하는 경우, ALIAS(표 속성명이나 이름을 나타내는 별칭)은 첫번쨰 SQL모듈 기준으로 표시되고 정렬 기준은 마지막 SQL모듈에 표시

- UNION ALL 이후 UNION을 수행하게되면 UNION이 중복값을 제거하고 정렬이 발생함.

- 중복레코드를 유지하는 UNION ALL은 ORDER BY가 없으면 정렬발생하지 않음.

(UNION은 중복값이 없어서 자동으로 정렬을 하게 됨)

 

ORACLE 계층형 질의

- START WITH절은 계층구조의 시작점

- 순방향 : 부모기준 자식 정렬

- ORDER SIBLINGS BY : 형제 노드 사이 정렬

- 루트 노드 LEVEL 값은 1

* START WITH에서 부모데이터의 조건을 지정

* ORDER SIBLINGS BY는 형제노드(동일레벨에서)로 수행

 

PRIOR

- CONNECT BY에서 사용

- PRIOR 부모 = 자식 => 자식 ⊃ 부모 (역방향)

- PRIOR 자식 = 부모 => 자식 ⊂ 부모 (순방향)

 

 계층형질의(Hierachival Query)

계층형 데이터가 존재할 때, 데이터 조회를 위해 계층형질의(Hierachival Query)이용
계층형 데이터? 동일 테이블에 계층적으로 상위/하위 데이터가 포함됨을 말함

- SQL SERVER에서 계층형 질의문은 CTE(Common Table Escpression)을 재귀호출 함으로서 계층 구조를 전개

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

- ORACLE 계층형 질의문에서 where절은 모든 전개를 진행한 이후 필터 조건으로서 조건에 부합하는 데이터만을 추출하는 데 활용

- priror 키워드는 select, where, connect by절에서 사용가능

 

 

서브쿼리

SINGLE ROW 단일행 서브쿼리	- 서브쿼리 실행결과가 항상 1건 이하 - = < <= > >= < >
MULTI ROW 다중행 서브쿼리	- 실행결과가 여러건 - 다중행 비교연산자(IN, ALL, ANY, EXISTS, SOME)와 함께 사용
MULTI COLUMN 다중 칼럼 서브쿼리	- 실행결과가 여러칼럼 - 메인 쿼리 조건절에 여러 칼럼을 동시에 비교 - 서브쿼리와 메인쿼리의 비교하고자하는 칼럼 개수와 칼럼 위치가 동일해야 함

*다중행 비교연산자는 단일행 서브쿼리에도 사용 가능

 

- 서브쿼리는 단일행 또는 복수행 비교 연산자와 함께 쓸 수 있음.

- 서브쿼리는 select, from, having, order by 절 등에서 사용 가능

- 서브쿼리에서는 order by를 사용하지 못함. ?????????????????????????????????????????

  (order by절은 select 절에서 오직 1개만 올 수 있기 때문에 order by절은 메인쿼리의 마지막 문장에 위치)

- Group by 사용 안하고도 Having절에서 사용 가능

- 서브쿼리의 결과가 복수행 결과를 반환하는 경우, In, All, Any 등의 복수행 비교 연산자와 사용

- 연관(correlated)서브쿼리는 서브쿼리가 메인쿼리 칼럼을 포함하고 있는 형태의 서브쿼리

- 다중 칼럽 서브쿼리는 서브쿼리의 결과로 여러개의 컬럼이 반환되어 메인쿼리의 조건과 비교되는 데,

  SQL Server에서는 현재 지원하지 않는 기능/ Oracle가능

- 서브쿼리 사용시, 서브쿼리를 괄호에 감싸서 사용

- 단일행 비교 연산자는 서브쿼리 결과가 반드시 1건 이하, 복수행 비교 연산자는 서브쿼리 결과가 건수와 상관 없음

- 단일형 서브쿼리 비교연산자는 다중행 서브쿼리 비교 연산자로 사용할 수 없지만, 

  다중행 서브쿼리 연산자가 단일행 서브쿼리 비교 연산자로 사용 가능

- 비연관 서브쿼리가 주로 메인쿼리 값을 제공하기 위한 목적으로 사용됨

- 메인쿼리 결과가 서브쿼리로 제공될 수 있고, 서브쿼리 결과가 메인쿼리로 제공될 수 있으므로

  실행순서는 정해지지 않고, 상황에 따라 달라진다.

- From절에서 사용되는 서브쿼리를 인라인뷰

  (서브쿼리 결과가 동적으로 생성된 테이블인 것 처럼 사용가능.

  SQL실행시에만 임시적으로 생성되는 동적뷰라서 DB에는 정보가 저장되는 것은 아님)

 

 

View 뷰

1. 독립성 : 테이블 구조가 변경되어도 뷰를 사용하는 응용프로그램은 변경하지 않아도 됨.

2. 편리성 : 복잡한 질의를 뷰로 생성함으로써 관련질의를 단순하게 작성.

               해당 SQL을 자주 사용할 때, 뷰를 이용하면 편리하게 사용 가능

3. 보안성 : 숨기고픈 정보가 존재하면 해당 칼럼을 빼고 생성해서 사용자에게 정보를 감출 수 있음

- 단지 정의민 가지고 있고, 실행시점에 질의를 재작성하여 수행

- 보안 강화 목적도 있음

- 실제 데이터를 저장하고 있는 뷰를 생성하는 기능을 지원하는 DBMS도 있음

 

 

 

 

- Grouping columns이 가질 수 있는 모든 경우에 대해 subtotal을 생성해야 하는 경우, cube를 사용하는 것이 바람직.

- Rollup에 비해 시스템에 많은 부담을 주므로 사용에 유의

*CUBE : 결합한 모든 값에 대해 다차원 집계를 생성. CUBE도 결과에 대한 정렬이 필요한 경우는 order by절에 명시적으로 정렬 칼럼이 표시되어야 함.

- CUBE, GRUPING SETS, ROLLUP 세가지 그룹함수 모두 일반 그룹함수로 동일 결과 추출 가능

- ROLLUP 그룹 함수는 함수인자로 주어진 컬럼의 순서에 따라 다른 결과를 추출하게 되고, 나열된 컬럼에 대해 계층구조로 집계를 출력

- CUBE, ROLLUP, GROUPING SETS함수들에 의해 집계된 레코드에서 집계대상 컬럼 이외의 GROUP 대상 컬럼 값은 NULL을 반환

- GROUP SETS는 다양한 소계집합을 만들 수 있음

- GROUP SETS에 표시된 인수들에 대한 개별 집계를 구할 수 있으며, 이때 표시된 인수들 간에는 계층 구조인 ROLLUP과 달리 평등한 관계이므로 인수의 순서가 바뀌어도 결과는 같음

- GROUPING SETS 함수는 결과에 대한 정렬이 필요한 경우에는 ORDER BY절에 명시적으로 정렬칼럼이 표시되어야 함

 

 

 

윈도우 함수 (Window Function, Analytic function)

- Group by 절과 함께 사용한다고해서 오류가 나는 것은 아님

- PARTITION과 GROUP BY구문은 의미적으로 유사

- PARTITION 구문이 없으면 전체 집합을 하나의 PARTITION으로 정의한 것과 동일

- 윈도우 함수는 결과에 대한 함수처리로서 결과 건수는 줄지 않음

- 윈도우 함수의 적용범위는 PARTITION을 넘을 수 없음

 

 

RANK함수

- ORDER BY를 포함한 QUERY문에서 특정항목(컬럼)에 대한 순위를 구하는 함수

- 동일한 값에 대해서는 동일한 순서를 부여

DENSE_RANK : RANK함수와 흡사하나 동일한 순위를 하나의 건수로 취급하는 것이 다른 점.
ROW- NUMBER : 동일한 값이라도 고유한 순위를 부여

RANK BETWEEN 10000 PRESENDING AND 1000 FOLLOWING 

=> -10000~+10000

 

 

- LAG함수를 이용해서 파티션별 윈도우에서 이전 몇번째 행의 값을 가져올 수 있음

- 이후 몇 번 째 행의 값을 가져오는 것은 LEAD함수이며 SQL SERVER에서는 지원하지 않음

 

 

- DBMS관리자가 사용자 별로 권한을 관리하는 부담을 줄이기 위해 다양한 권한을 그룹으로 묶어 관리할 수 있도록

  사용자-권한 사이의 중개역할을 수행하는 ROLE을 제공

 

 

GRANT SELECT, INSERT, DELETE ON R TO KIM WITH GRANT OPTION.
=> KIN에게 R테이블의 조회, 삽입, 삭제에 대한 권한을 주면서 해당 권한을 다른사람에게 부여할 수 있는 권한도 줌

REVOKE INSERT ON R FROM KIM CASCADE;
=> KIM에게서 R테이블에 삽입에 대한 권한을 회수하면서 WITH GRANT OPTION을 통해 받은 KIM에게서 다른사람이 받은 권한 까지도 회수를 명령함 

 

 

PL/SQL

- BLOCK 구조로 되어있어 각 기능별 모듈화 가능

- 변수, 상수 등을 선언하여 SQL문장 간 값을 교환 (예: 일반SQL실행시, WHERE절 조건 등으로 대입 가능)

- IF, LOOP 등의 절차형 언어를 사용하여 절차적인 프로그램이 가능하도록 함

- DBMS 정의 에러나 사용자 정의 에러를 정의하여 사용가능 

문제: ROLLBACK 불가능하도록 삭제하기
=> 동적 SQL 또는 DDL문장을 실행할 때, EXECUTE IMMEDITATE를 사용할 것.

- ORACLE에 내장되어 있어 ORACLE과 PL/SQL을 지원하는 어떤 서버로도 프로그램은 옮길 수 있음

- 응용프로그램 성능을 향상

- 프로시저 내부에 작성된 절차적 코드는 PL/SQL 엔진이 처리하고, 일반적 SQL문장은 SQL실행기가 처리

- SQL문장을 블록으로 묶고 한번에 BLOCK 전부를 서버로 보내기 때문에 통신량을 줄일 수 있음

- PROCEDURE, USER DEFINED FUNCTION, TRIGGER 객체를 PL/SQL로 작성 가능

=> 작성자 기준으로 트랜잭션 분할 가

=> 프로시저 내에서 다른 프로시저를 호출할 경우,

     호출 프로시저의 트랜잭션과 별도 PRAGMA AUTO NOMOUS TRANSATION을 선언하여 자율 트랜잭션 처리 가능

 

 

저장모듈(STORED MODULE)

- SQL문장을 데이터베이스 서버에 저장하여 사용자-어플리케이션 사이에서 공유할 수 있도록 만든 일종의 SQL 컴포넌트 프로그램

- 독립적으로 실행되거나 다른 프로그램으로부터 실행될 수 있는 완전한 실행 프로그램

- ORACLE 저장 모듈에는 PROCEDURE, USER DEFINED FUNCTION, TRIGGER가 있음.

- STORED MODULE에는 PL/SQL, LP/SQL, T-SQL이 있음

- 구현 가능한 기능

* 로직과 함께 DB내에 저장해 놓은 명령문의 집합

* 저장형 함수(사용자 정의 함수)는 단독으로 실행되기 보다는 다른 SQL문을 통해 호출되고 그 결과를 리턴하는 SQL보조 역할

* 트리거는 특정 테이블에 INSERT, UPDATE, DELETE와 같은 DML문이 수행되었을 때, DB에서 자동 동작하도록 작성된 프로그램

트리거?
데이터 무결성과 일관성을 위해 사용자 정의 함수를 사용하는 것은 트리거의 용도
프로시저와 달리 COMMIT 및 ROLLBACK과 같은 TCL 사용 불가
DN에 의해서 자동호출 가능
INSERT, UPDATE, DELETE 수행 시, 호출 되도록 정의 가능
데이터베이스에 로그인하는 작업에도 정의 가능

프로시저 PROCEDURE	트리거 TRIGGER
CREATE PROCEDURE 문법 사용	CREATE TRIGGER 문법 사용
EXECUTE 명령어로 실행	생성 후, 자동 실행
COMMIT, ROLLBACK 실행 가능O	COMMIT, ROLLBACK 실행 불가X