SQL 응용

(1) 트리거

1. 트리거란

- 트리거는 데이터베이스 시스템에서 삽입, 갱신, 삭제 등의 이벤트가 발생할 때마다 관련 작업이 자동으로 수행되는 절차형 SQL이다. 

- 이벤트와 관련된 테이블의 데이터 삽입, 추가, 삭제 작업을 DBMS가 자동적으로 실행시키는 데 활용

 

▼ 트리거 구성

구성요소	설명
선언부 (DECLARE)	트리거의 명칭을 정의하는 부분
이벤트부 (EVENT)	트리거가 실행되는 타이밍, 이벤트를 명시하는 부분
시작/종료부 (BEGIN/END)	트리거의 시작과 종료를 표현하는 데 필수적이며, BEGIN/END가 쌍을 이루어 추가되므로 블록으로 구성 다수 실행을 제어하는 기본적 단위가 되며 논리적 프로세스를 구성
제어부 (CONTROL)	기본적으로는 순차적으로 처리 비교 조건에 따라 블록 또는 문장을 실행 조건에 따라 반복 실행
SQL	DML을 주로 사용하고, 자주 사용되지 않지만 DDL을 사용
예외부 (EXCEPTION)	BEGIN~END 절에서 실행되는 SQL문이 실행될 때 예외 발생 시 예외 처리 방법을 정의하는 처리부

 

2. 트리거 작성 시 주의사항

- TCL 사용 불가 : 트리거 내에는 COMMIT, ROLLBACK 등의 트랙잭션 제어어(TCL) 사용 시 컴파일 에러 발생

- 오류에 주의 : 트리거 실행 중 오류가 발생하게 되면 트리거 실행의 원인을 제공한 데이터 작업에도 영향

 

!!여기서 잠깐!!

TCL이란?

- 트랜잭션을 제어하는 언어로, 트랜잭션은 데이터베이스의 논리적 연산단위를 의미

 

(2) 이벤트

1. 이벤트란

- 이벤트는 특정 시간에 특정한 쿼리, 프로시저, 함수 등을 실행시키는 기능이다. 

 

 

(3) 사용자 정의 함수

1. 사용자 정의 함수란

- 사용자 정의 함수는 절차형 SQL을 활용하여 일련의 연산 처리 결과를 단일 값으로 반환할 수 있는 함수다.

- DBMS에서 제공되는 공통적 함수 이외에 사용자가 직접 정의하고 작성한다.

 

2. 사용자 정의 함수 특징

- 사용자 정의 함수는 호출을 통해 실행되며, 반환되는 단일 값을 조회 또는 삽입, 수정 작업에 이용한다.

- 프로시저와의 가장 큰 차이점은 기본적인 개념 및 사용법, 문법 등은 프로시저와 동일하며, 종료 시 단일 값을 반환한다는 점이다.

 

!!여기서 잠깐!!

프로시저란?

- 프로시저는 특정한 로직 값을 처리하기만 하고 결과 값을 반환하지 않는 서브 프로그램이다. 

 

▼ 사용자 정의 함수 구성

구성요소	설명
선언부 (DECLARE)	프로시저의 명칭, 변수와 인수 그리고 그에 대한 데이터 타입을 정의하는 부분
시작/종료부 (BEGIN/END)	프로시저의 시작과 종료를 표현하는 데 필수적이며, BEGIN/END가 쌍을 이루어 추가되므로 블록으로 구성 다수 실행을 제어하는 기본적 단위
제어부 (CONTROL)	기본적으로 순차적으로 처리 비교 조건에 따라 블록 또는 문장을 실행 조건에 따라 반복 실행
SQL	조회 용도로 SELECT 문을 사용 데이터를 조작하는 INSERT, DELETE, UPDATE는 사용할 수 없음
예외부 (EXCEPTION)	BEGIN~END 절에서 실행되는 SQL 문이 실행될 때 예외 발생 시 예외 처리 방법을 정의하는 처리부
반환부 (RETURN)	호출문에 대한 함수 값을 반환

 

3. 사용자 정의 함수 호출쿼리 작성 문법

- 사용자 정의 함수는 프로시저와 동일하게 외부에서의 호출을 통해 실행한다.

- 단순한 결과값이나 다른 시스템에 정보를 제공할 때 또는 캡슐화를 제공할 때에도 쓰인다. 

 

▼ 사용자 정의 함수 호출쿼리 작성 문법

//문법

//직접 사용자 정의 함수 결과값을 데이터 질의어에 활용
SQL>
SELECT 함수명(파라미터1, .....)
	FROM 테이블명;



//사용자 정의 함수 결과값을 데이터 조작어에 직접 적용, 활용
SQL>
UPDATE 테이블명
	SET 컬럼명 = 함수명(파라미터1, ....)
WHERE 조건

 

(4) SQL 문법

1. SQL 문법이란

- 데이터베이스를 접근하고 조작하는 데 필요한 표준 언어를 활용할 수 있게 해주는 규칙

 

▼SQL 문법의 분류

분류	설명
데이터 정의어(DDL)	데이터 정의어는 데이터를 정의하는 언어 테이블이나 관계의 구조를 생성하는 데 사용 CREATE, ALTER, DROP, TRUNCATE 문이 있음
데이터 조작어(DML)	데이터베이스에 저장된 자료들을 입력, 수정, 삭제, 조회하는 언어 SELECT, INSERT, UPDATE, DELETE문이 있음 SELECT 문은 쿼리(Query)라고 부름
데이터 제어어(DCL)	데이터베이스 관리자가 데이터 보안, 무결성 유지, 병행 제어, 회복을 위해 DBA가 사용하는 제어용 언어 GRANT, REVOKE 문이 있음

!!여기서 잠깐!!

DBA란?

- DBA는 데이터베이스 관리자를 뜻함. 

 

2. WHERE 조건

구분	연산자	사례
비교	=,<>,<,<=,>,>=	PRICE < 50000 - 가격이 50000 미만 - <>은 다름을 의미
범위	BETWEEN	PRICE BETWEEN 50000 AND 80000 - 가격이 50000보다 크거나 같고 80000보다 작거나 같음
집합	IN, NOT IN	PRICE IN(40000, 50000, 60000) - 가격이 40000 또는 50000 또는 60000
패턴	LIKE	NAME LIKE '정보%' - 이름이 '정보'로 시작되는 문자열
NULL	IS NULL, IS NOT NULL	PRICE IS NULL - 가격의 값이 NULL인 경우
복합조건	AND, OR, NOT	(PRICE < 50000) AND (NAME LIKE '정보%') - 가격이 50000미만이고 이름이 '정보'로 시작되는 문자열

 

- LIKE와 같이 사용하는 와일드 문자는 "+, %, [], [^], _" 가 있다.

+ -> 문자열을 연결-> '축구'+'감독':'축구 감독'

% -> 0개 이상의 문자열과 일치 -> LIKE '키워드%'

[ ] -> 1개의 문자와 일치 -> '[0-8]%':0-8사이 숫자로 시작하는 문자열

[^] -> 1개의 문자와 불일치 -> '[^0-8]%':0-8 사이 숫자로 시작하지 않는 문자열

_ -> 특정 위치의 1개의 문자와 일치 -> '_동%' :  두 번째 위치에 '동'이 들어가는 문자열

 

 

- SQL HINT란 실행하려 하는 SQL 문에 사전에 정보를 주어서 SQL 문 실행에 빠른 결과를 가져오는 효과를 만드는 문법

- 주석에 '+' 기호를 붙이면 힌트로 인식한다.

 

(5) 데이터 조작어(DML)

1. 데이터 조작어란

- 데이터 조작어는 데이터베이스에 저장된 자료들을 입력, 수정, 삭제, 조회하는 언어다. 

 

▼DML의 유형

유형	동작	설명
SELECT	데이터 조회	만족하는 튜플을 검색하여 주기억장치 상에 임시 테이블로 구성
INSERT	데이터 생성	해당 테이블에 새로운 튜플을 삽입할 때 사용하는 명령문
UPDATE	데이터 변경	특정 튜플의 내용을 변경할 때 사용
DELETE	데이터 삭제	특정 튜플을 삭제할 때 사용

 

▼SELECT 명령어 예시

//SELECT 명령어
SELECT[ALL |] 속성명1, 속성명2......
	FROM 테이블명1, ....
[WHERE 조건]
[GROUP BY 속성명1, .....]
[HAVING 그룹조건]
[ORDER BY 속성 [ASC | DESC]];

 

▼ INSERT 명령어 예시

INSERT INTO 테이블명(속성명1, ...)
VALUES(데이터1, ....)

 

▼ UPDATE 명령어 예시

UPDATE 테이블명
	SET 속성명 = 데이터, .....
WHERE 조건;

 

▼DELETE 명령어 예시

DELETE FROM 테이블명
WHERE 조건;

 

 

(6) 데이터 제어어(DCL)

1. 데이터 제어어란

- 데이터 제어어는 DB 관리자가 데이터 보안, 무결성 유지, 병행 제어, 회복을 위해 사용하는 언어

 

• 데이터 보안 : 불법적인 사용자로부터 데이터를 보호
• 무결성 유지 : 데이터의 정확성과 일관성을 유지
• 병행수행 제어 : 여러 트랜잭션을 수행할 때 트랜잭션들이 데이터베이스의 일관성을 파괴하지 않도록 트랜잭션 간의 상호작용을 제어
• 회복 : 데이터베이스 장애가 발생할 경우, 장애 발생 이전의 상태로 복원

 

2. DCL 명령어

- GRANT : 관리자가 사용자에게 데이터베이스에 대한 권한 부여

- REVOKE : 관리자가 사용자에게 부여했던 권한을 회수

- COMMIT : 데이터베이스 트랜잭션의 내용 업데이트를 영구적으로 확정

- ROLLBACK : 업데이트 오류가 발생하면 이전 상태로 되돌리는 명령어

- SAVEPOINT : 트랜잭션의 특정 지점에 이름을 지정하고 그 지점 이전에 수행한 작업에 영향을 주지 않고 그 지점 이후에 수행한 작업을 롤백

 

(7) 윈도 함수

1. 윈도 함수란

- 윈도 함수는 행과 행 간의 관계를 쉽게 정의하기 위해 만든 함수

 

▼윈도 함수 예시

SELECT 함수명(파라미터)
	OVER
    ([PARTITION BY 컬럼1, ....])
    [ORDER BY 컬럼A, ....]
    FROM 테이블명

 

2. 윈도 함수의 분류

• 집계 함수 : 여러 행 또는 테이블 전체 행으로부터 하나의 결과값을 반환하는 함수
• 순위 함수 : 레코드의 순위를 계산하는 함수(RANK, DENSE_RANK, ROW_NUMBER)
• 행 순서 함수 : 레코드에서 가장 먼저 나오거나 가장 뒤에 나오는 값, 이전이나 이후의 값들을 출력
• 그룹 내 비율 함수 : 백분율을 보여주거나 행의 순서별 백분율 등 비율과 관련된 통계를 보여주는 함수(RATIO_TO_REPORT, PERCENT_RANK)

▣ 집계 함수

- COUNT(줄 수), SUM(합), AVG(평균), MAX(최대값), MIN(최소값), STDDEV(표준편차), VARIAN(분산)이 있다.

 

 

▣ 순위 함수

- RANK(특정 항목 순위), DENSE_RANK(레코드의 순위, 후순위x), ROW_NUMBER(레코드의 순위, 후순위O) 함수가 있다.

 

▣ 행 순서 함수

- FIRST_VALUE(가장 먼저 나오는 값 반환), LAST_VALUE(가장 늦게 나오는 값 반환), LAG(이전 로우의 값 반환), LEAD(이후 로우의 값 반환) 함수가 있다.

 

▣ 그룹 내 비율 함수

- RATIO_TO_REPORT(상대적 비율 반환), PERCENT_RANK(제일 먼저는 0, 제일 늦게 나오는 것을 1로 하여 순서별 백분율) 함수가 있다.

 

3. 윈도 함수 활용

OLAP 연산

- 사용자가 동일한 데이터를 여러 기준을 이용하는 다양한 방식으로 바라보면서 다차원 데이터 분석을 할 수 있도록 도와주는 의사결정 지원 시스템

▷ OLAP 연산 - Roll Up(구체적->요약), Drill-Down(요악->구체적), Slicing(데이터 큐브의 한 조각), Dicing(고정된 다차원 값), Pivoting(차원 변경을 위해 사용)이 있다.

 

(8) 그룹 함수 

1. 그룹 함수란

- 테이블의 전체 행을 하나 이상의 컬럼을 기준으로 컬럼 값에 따라 그룹화하여 그룹별로 결과를 출력하는 함수

 

▣ROLLUP

- 지정된 컬럼의 총계를 구하기 위해 사용하는 그룹 함수

 

▣ CUBE

- 결합 가능한 모든 값에 대해 다차원 집계를 생성하는 그룹 함수

 

▣ GROUPING SETS

- 집계 대상 컬럼들에 대한 소계를 구할 수 있는 그룹함수

 

 

(9) 오류 처리

1. 오류 처리란

- 프로그램 코드상의 오류나 프로시저 실행 시 예외나 에러가 발생했을 때, 문제를 해결하고 의미 있는 에러 메시지를 부여하는 과정

 

2. 핸들러 선언

- 핸들러를 선언하기 위해서는 DECLARE HANDLER를 사용한다.

- HANDLER는 액션, 상태 값, 명령문으로 이루어져 있다.