SQL 활용

계층형 질의 : 테이블에 계층형 데이터가 존재하는 경우 데이터를 조회하기 위해 사용

 

SATRT WITH : 계층 구조 전개의 시작 위치 지정

CONNECT BY : 다음에 전개될 자식 데이터 지정

PRIOR : CONNECT BY 절에 사용되며, 현재 읽은 칼럼을 지정한다. PRIOR 자식 = 부모 형태를 사용하면 계층구조에서 부모 데이터에서 자식 데이터(부모->자식) 방향으로 전개하는 순방향 전개를 한다. 반대는 역방향 전개

NOCYCLE : 동일한 데이터가 전개되지 않음

ORDER SIBLINGS BY : 형제 노드간의 정렬 수행

WHERE : 모든 전개를 수행한 후에 지정된 조건을 만족하는 데이터만 추출한다.(필터링)

 

LEVEL : 루트 데이터이면 1, 그 하위 데이터면 2, 리프 데이터까지 1씩 증가

CONNECT_BY_ISLEAF : 해당 데이터가 리프 데이터면1, 그렇지 않으면 0

CONNECT_BY_ISCYCLE : 해당 데이터가 조상이면 1, 아니면 0 (CYCLE 옵션 사용했을 시만 사용 가능)

 

SYS_CONNECT_BY_PATH : 루트 데이터부터 현재 전개할 데이터까지의 경로를 표시한다.

CONNECT_BY_ROOT : 현재 전개할 데이터의 루트 데이터를 표시한다. 단항 연산자이다.

 

셀프 조인 : 동일 테이블 사이의 조인, FROM 절에 동일 테이블이 2번 이상 나타난다. 반드시 테이블 별칭을 사용해야 함

 

서브 쿼리 : 하나의 SQL문안에 포함되어 있는 또 다른 SQL문, 알려지지 않은 기준을 이용한 검색에 사용

 

서브 쿼리 사용시 주의 사항

1. 서브쿼리를 괄호로 감싸서 사용한다.

2. 서브쿼리는 단일 행 또는 복수 행 비교 연산자와 함께 사용 가능하다. 단일 행 비교 연산자는 서브쿼리의 결과가 반드시 1건 이하여야 하고 복수 행 비교 연산자는 결과 건수와 상관없다.

3. 서브쿼리에서는 ORDER BY를 사용하지 못한다.

4. SELECT, FROM, WHERE, HAVING, ORDER BY, INSERT-VALUES, UPDATE-SET 절에 사용 가능

 

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

다중 행 비교 연산자 : IN, ALL, ANY, SOME 등

 

스칼라 서브쿼리 : 한 행, 한 칼럼만을 반환하는 서브쿼리

 

인라인 뷰 : 테이블 명이 올 수 있는 곳에 사용, ORDER BY 사용 가능

 

뷰 : 테이블은 실제로 데이터를 가지고 있는 반면, 뷰는 실제 데이터를 가지고 있지 않다. 가상 테이블이라고도 함

 

뷰 사용 장점

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

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

3. 보안성 : 직원의 급여정보와 같이 숨기고 싶은 정보가 존재할 때 사용

CREATE VIEW V_PLAYER_TEAM AS

DROP VIEW V_PLAYER_TEAM;

--------------------------------------------

 

ROLLUP : Subtotal을 생성하기 위해 사용, Grouping Columns의 수를 N이라고 했을 때 N+1 Level의 Subtotal이 생성된다. 인수 순서에 주의

 

GROUPING : Subtotal의 total을 생성

 

CUBE : 결합 가능한 모든 값에 대하여 다차원 집계를 생성, ROLLUP에 비해 시스템에 부하 심함

 

GROUPING SETS : 인수들에 대한 개별 집계를 구할 수 있다, 다양한 소계 집합 생성 가능

--------------------------------------------

 

윈도우 함수 : 행과 행간의 관계를 정의하거나 행과 행간을 비교, 연산하는 함수

 

RANK : 특정 항목에 대한 순위를 구하는 함수, 동일한 값에 대해서는 동일한 순위를 부여(1,2,2,4)

 

DENSE_RANK : 동일한 순위를 하나의 등수로 간주(1,2,2,3)

 

ROW_NUMBER : 동일한 값이라도 고유한 순위 부여

 

SUM : 파티션별 윈도우의 합 구할 수 있다.

ex)같은 매니저를 두고 있는 사원들의 월급 합

 

MAX,MIN : 파티션별 윈도우의 최대,최소 값을 구할 수 있다.

ex)같은 매니저를 두고 있는 사원들 중 최대 값

 

AVG : 원하는 조건에 맞는 데이터에 대한 통계 값

ex)같은 매니저 내에서 앞의 사번과 뒤의 사번의 평균

ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING

(현재 행을 기준으로 파티션 내에서 앞의 1건, 현재행, 뒤의 1건을 범위로 지정)

 

COUNT : 조건에 맞는 데이터에 대한 통계 값

ex)본인의 급여보다 50 이하가 적거나 150 이하로 많은 급여를 받는 인원수

 

FIRST_VALUE : 파티션별 윈도우에서 가장 먼저 나온 값을 구한다.(SQL Server는 지원x)

 

LAST_VALUE : 파티션별 윈도우에서 가장 나중에 나온 값을 구한다.(SQL Server 지원x)

 

LAG : 파티션별 윈도우에서 이전 몇 번째 행의 값을 가져올 수 있다.(SQL Server 지원x)

 

LEAD : 파티션별 윈도우에서 이후 몇 번째 행의 값을 가져올 수 있다.(SQL Server 지원x)

 

RATIO_TO_REPORT : 파티션 내 전체 SUM값에 대한 행별 칼럼 값의 백분율을 소수점으로 구할 수 있다. 결과 값은 0보다 크고 1보다 작거나 같다.

 

PERCENT_RANK : 파티션별 윈도우에서 제일 먼저 나오는 것을 0, 제일 늦게 나오는 것을 1로 하여 행의 순서별 백분율을 구한다. 0>=,<=1

 

CUME_DIST : 현재 행보다 작거나 같은 건수에 대한 누적백분율을 구한다. >0, <=1

 

NTILE : 파티션별 전체 건수를 인수 값으로 N등분한 결과를 구할 수 있다.

--------------------------------------------

 

DCL : 유저 생성하고 권한을 제어할 수 있는 명령어

 

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

Oracle : 유저를 통해 DB에 접속을 하는 형태, ID와 PW 방식으로 인스턴스에 접속을 하고 그에 해당하는 스키마에 오브젝트 생성 등의 권한을 부여받게 됨

SQL Server : 인스턴스에 접속하기 위해 로그인이라는 것을 생성하게 되며, 인스턴스 내에 존재하는 다수의 DB에 연결하여 작업하기 위해 유저를 생성한 후 로그인과 유저를 매핑해 주어야 한다. Windows 인증 방식과 혼합 모드 방식이 존재함

 

시스템 권한 : 사용자가 SQL 문을 실행하기 위해 필요한 적절한 권한

 

GRANT : 권한 부여

REVOKE : 권한 취소

 

GRANT CREATE USER TO SCOTT;

CONN SCOTT/TIGER(ID/PW)

CREATE USER PJS IDENTIFIED BY KOREA7;

GRANT CREATE SESSION TO PJS;

GRANT CREATE TABLE TO PJS;

REVOKE CREATE TABLE FROM PJS;

 

 

모든 유저는 각각 자신이 생성한 테이블 외에 다른 유저의 테이블에 접근하려면 해당 테이블에 대한 오브젝트 권한을 소유자로부터 부여받아야 한다.

 

ROLE : 유저에게 알맞은 권한들을 한 번에 부여하기 위해 사용하는 것

 

CREATE ROLE LOGIN_TABLE;

GRANT CREATE TABLE TO LOGIN_TABLE;

 

DROP USER PJS CASCADE;

CASCADE : 하위 오브젝트까지 삭제

-------------------------------------------

 

절차형 SQL : SQL문의 연속적인 실행이나 조건에 따른 분기처리를 이용하여 특정 기능을 수행하는 저장 모듈을 생성할 수 있다, Procedure, User Defined Function, Trigger 등이 있음

 

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

 

PL/SQL 특징

1. Block 구조로 되어있어 각 기능별로 모듈화 가능

2. 변수, 상수 등을 선언하여 SQL 문장 간 값을 교환

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

4. DBMS 정의 에러나 사용자 정의 에러를 정의하여 사용할 수 있다.

5. PL/SQL은 Oracle에 내장되어 있으므로 호환성 굳

6. 응용 프로그램의 성능을 향상시킨다.

7. Block 단위로 처리 -> 통신량을 줄일 수 있다.

 

DECLARE : BEGIN~END 절에서 사용될 변수와 인수에 대한 정의 및 데이터 타입 선언부

BEGIN~END : 개발자가 처리하고자 하는 SQL문과 여러 가지 비교문, 제어문을 이용 필요한 로직 처리

EXCEPTION : BEGIN~END 절에서 실행되는 SQL문이 실행될 때 에러가 발생하면 그 에러를 어떻게 처리할지 정의하는 예외 처리부

 

CREATE Procedure Procedure_name

REPLACE Procedure Procedure_name

DROP Procedure Procedure_name

/ <- 컴파일 하라는 명령어

 

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

CREATE Procedure schema_NAME.Procedure_name

 

Trigger : 특정한 테이블에 INSERT, UPDATE, DELETE와 같은 DML문이 수행되었을 때, DB에서 자동으로 동작하도록 작성된 프로그램, 사용자 호출이 아닌 DB 자동 수행

 

CREATE Trigger Trigger_name

 

프로시저와 트리거의 차이점

프로시저는 BEGIN~END 절 내에 COMMIT, ROLLBACK과 같은 트랜잭션 종료 명령어 사용가능, DB 트리거는 BEGIN~END 절 내에 사용 불가