Chapter 1. SQL 응용

[1] 절차형 SQL 작성

#. 트리거(Trigger)

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

 

#. 트리거 구성

- 선언부 (DECLARE)

- 이벤트부 (EVENT)

- 시작/종료부 (BEGIN/END)

- 제어부 (CONTROL)

- SQL

- 예외부 (EXCEPTION)

 

#. 사용자 정의 함수 구성

- 선언부 (DECLARE)

- 시작/종료부 (BEGIN/END)

- 제어부 (CONTROL)

- SQL

- 예외부 (EXCEPTION)

- 반환부 (RETURN)

 

#. SQL 문법의 분류 ★

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

 

#. WHERE 조건 ★

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

 

#. LIKE와 같이 사용하는 와일드 문자

와일드 문자	설명	사례
+	문자열을 연결	· '축구' + '감독' : '축구 감독'
%	0개 이상의 문자열과 일치	· LIKE '키워드%'
[ ]	1개의 문자와 일치	· '[0-8]%' : 0-8 사이 숫자로 시작하는 문자열
[ ^ ]	1개의 문자와 불일치	· '[^0-8]%' : 0-8 사이 숫자로 시작하지 않는 문자열
-	특정 위치의 1개의 문자와 일치	· '_동%' : 두 번째 위치에 '동'이 들어가는 문자열

 

 

[2] 응용 SQL 작성

 

#. 데이터 조작어(DML; Data Manipulation Language)의 개념

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

 

#. 데이터 조작어(DML)의 유형

- 데이터 조작어의 유형에는 SELECT, INSERT, UPDATE, DELETE가 있다.

 

① SELECT(데이터 조회) 명령어

구분	설명
SELECT 절	· 검색하고자 하는 속성명, 계산식 · 2개 이상의 테이블을 대상으로 검색할 때는 '테이블명, 속성명'으로 표현 · 술어 부분은 ALL이 기본값 * ALL : 모든 튜플을 검색할 때 사용, 명시하지 않을 때 ALL로 인식 * DISTINCT : 중복된 속성이 조회될 경우 그중 한 개만 검색
FROM 절	· 질의에 의해 검색될 데이터들을 포함하는 테이블명을 기술
WHERE 절	· 검색할 조건을 기술
GROUP BY 절	· 속성값을 그룹으로 분류하고자 할 때 사용
HAVING 절	· GROUP BY에 의해 분류한 후 그룹에 대한 조건을 기술
ORDER BY 절	· 속성값을 정렬하고자 할 때 사용 · ASC : 오름차순 / DESC : 내림차순

 

② INSERT(데이터 삽입) 명령어

구문	설명
INSERT INTO  테이블명 (속성명1, ...) VALUES  (데이터1, ...)	속성과 데이터 개수, 데이터 타입이 일치해야 함 속성명 생략가능

 

③ UPDATE(데이터 변경) 명렁어

구문	설명
UPDATE  테이블명 SET  속성명 = 데이터, ... WHERE 조건;	UPDATE 명령문은 WHERE 절을 통해 어떤 조건이 만족할 경우에만 특정 컬럼의 값을 수정하는 용도로 자주 사용됨

④ DELETE(데이터 삭제) 명령어

구문	설명
DELETE FROM 테이블명 WHERE  조건;	모든 레코드를 삭제할 때는 WHERE절 없이 DELETE만 사용 레코드를 삭제해도 테이블 구조는 남아 있어서 디스크에서 테이블을 완전히 삭제하는 DROP 명령과는 다름

 

#. 데이터 제어어 (DCL; Data Control Language)

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

 

#. 데이터 제어어 기능

1) 데이터 보안

- 불법적인 사용자로부터 데이터를 보호하는 기능

2) 무결성 유지

- 데이터의 정확성과 일관성을 유지하는 기능

3) 병행수행 제어

- 여러 트랜잭션을 수행할 때 트랜잭션들이 데이터베이스의 일관성을 파괴하지 않도록 트랜잭션 간의 상호작용을 제어하는 기능

4) 회복

- 데이터베이스 장애가 발생할 경우, 데이터베이스를 장애 발생 이전의 상태로 복원하는 기능

 

#. DCL의 명령어

유형	명령어	동작	설명
DCL	GRANT	사용권한 부여	관리자(DBA)가 사용자에게 데이터베이스에 대한 권한을 부여하는 명령어
	REVOKE	사용권한 취소	관리자(DBA)가 사용자에게 부여했던 권한을 회수하기 위한 명령어
DCL/TCL	COMMIT	트랜잭션 확정	데이터베이스 트랜잭션의 내용 업데이트를 영구적으로 확정하는 명령어
	ROLLBACK	트랜잭션 취소	데이터베이스에서 업데이트 오류가 발생할 때, 이전 상태로 되돌리는 명령어
	SAVEPOINT (= CHECKPOINT)	저장 시기 설정	트랜잭션의 특정 지점에 이름을 지정하고, 그 지점 이전에 수행한 작업에 영향을 주지 않고 그 지점 이후에 수행한 작업을 롤백할 수 있는 명령어

 

① GRANT(권한 부여) 명령어

권한	구문	설명
시스템 권한	GRANT 권한 TO 사용자;	관리자가 사용자에게 테이블/뷰/프로시저 등을 생성하고 삭제할 수 있는 권한 부여
객체 권한	GRANT 권한 ON 테이블 TO 사용자;	관리자가 사용자에게 테이블을 수정, 삽입, 삭제, 조회와 프로시저 실행을 할 수 있는 권한을 부여

※ GRANT 구문 마지막에 WITH GRANT OPTION 키워드를 붙이면 권한이 필요할 경우 다른 사용자에게 부여할 수 있는 권한을 부여할 수 있다.

 

② REVOKE (권한 취소) 명령어

권한	구문	설명
시스템 권한	REVOKE 권한 FROM 사용자;	관리자가 사용자에게 테이블/뷰/프로시저 등을 생성하고 삭제할 수 있는 권한 회수
객체 권한	REVOKE 권한 ON 테이블 FROM 사용자;	관리자가 사용자에게 테이블을 수정, 삽입, 삭제, 조회와 프로시저 실행을 할 수 있는 권한 회수

※ REVOKE 구문에 CASCADE CONSTRAINTS 키워드를 붙이면 WITH GRANT OPTION으로 부여된 사용자들의 권한까지 회수할 수 있다.

 

③ TCL 명령어

명령어	구분	설명
트랜잭션 확정	COMMIT;	데이터베이스 트랜잭션 내용 업데이트를 영구적으로 확정
트랜잭션 취소	ROLLBACK;	데이터베이스 업데이트 오류 발생 시 이저 상태로 되돌림
세이브 포인트 지정	SAVEPOINT 이름;	특정 지점을 지정
세이브 포인트 롤백	ROLLBACK TO SAVEPOINT 이름;	SAVEPOINT로 지정한 부분 이후에 발생한 트랜잭션 취소

 

#. 윈도 함수 (Window Function)

구문	설명
SELECT 함수명(파라미터)     OVER ([PARTITION BY 컬럼1, ...] [ORDER BY 컬럼A, ...]}     FROM 테이블명	· PARTITION BY는 선택 항목이며, 순위를 정할 대상 범위의 컬럼을 설정 PARTITION BY구에는 GROUP BY구가 가진 집약 기능이 없으며, 이로 인해 레코드가 줄어들지 않음 · PARTITION BY를 통해 구분된 레코드 집합을 윈도라고 함 · 윈도 함수에는 OVER 문구가 필수적으로 포함 · ORDER BY 뒤에는 SORT 컬럼을 입력 (어떤 열을 어떤 순서로 순위를 정할지를 지정)

 

#. 윈도 함수의 분류

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

 

#. 집계 함수

-  COUNT

-  SUM

-  AVG

-  MAX

- MIN

- STDDEV

- VARIAN

 

#. 순위 함수

- RANK

- DENSE_RANK

- ROW_NUMBER