-전부 정리된 건 아님. 공부하면서 추가/수정중
- 코드에서 [ ] 표기는 선택적으로 들어가는 부분, | 표기는 OR의 의미로 쓰임.
SQL (Structured Query Language)
: 관계형 데이터베이스 관리 시스템(RDBMS)의 데이터를 관리하기 위해 만들어진 프로그래밍 언어.
<SQL 기본>
문법 종류와 주요 명령어
데이터 정의어 (DDL, Data Definition Language)
: DB의 구조를 정의함.
- CREATE : 테이블 생성
- ALTER : 테이블 수정
- DROP : 테이블 삭제 (테이블 구조까지 아예 삭제됨, 롤백 불가)
- TRUNCATE : 테이블의 모든 행 삭제 (테이블 구조는 그대로, 데이터만 삭제)
데이터 조작어 (DML, Data Manipulation Language)
: DB의 레코드를 조작해 조회, 삽입, 수정, 삭제함.
- SELECT : 데이터 조회
|
1
2
3
4
5
6
|
SELECT 속성명
FROM 릴레이션
[WHERE 조건]
[GROUP BY 속성명]
[HAVING 그룹조건]
[ORDER BY 속성 [ASC | DESC] ];
|
cs |
- INSERT : 데이터 삽입
|
1
2
|
INSERT INTO 릴레이션(속성명)
VALUES(데이터)
|
cs |
- DELETE : 데이터 삭제 (롤백 가능)
|
1
2
|
DELETE FROM 릴레이션
[WHERE 조건]; --WHERE이 생략되면 해당 릴레이션 내 모든 튜플 삭제
|
cs |
- UPDATE : 데이터 수정
|
1
2
3
|
UPDATE 릴레이션
SET 속성명 = 데이터
[WHERE 조건]; --WHERE절 생략되면 해당 릴레이션 내 모든 튜플 수정
|
cs |
데이터 제어어 (DCL, Data Control Language)
: DB의 접근권한 및 트랜잭션을 제어함.
- GRANT : 권한을 부여
|
1
2
|
GRANT 권한 ON 테이블 TO 사용자
[WITH GRANT OPTION]; --다른 사람과 권한을 나눠가질 수 있는 옵션
|
cs |
- REVOKE : 권한을 회수
|
1
2
|
REVOKE 권한 ON 테이블 FROM 사용자
[CASCADE CONTRAINTS]; --연쇄적인 권한 해제(WITH GRANT OPTION으로 부여된 것까지 취소)
|
cs |
<SQL 응용>
프로시저, 사용자 정의함수, 트리거
프로시저
: SQL을 활용해 특정한 기능을 수행하는 트랜잭션 언어.
: 프로시저 호출을 통해 실행됨. (EXECUTE 문 사용)
: 선언부, 시작/종료부, 제어부, 예외부, 실행부로 구성
1. 선언부
: 프로시저 이름, 변수 이름과 타입 선언
|
1
2
3
4
|
CRATE PROCEDURE 프로시저_이름
(파라미터_이름 모드 데이터_타입 ...)
IS
변수 선언
|
cs |
- CREATE 대신 CREATE OR REPLACE을 사용하면 해당 이름의 프로시저가 존재할 시 덮어씌움.
- 모드 종류
- IS : OS에서 프로시저로 값 전달하는 모드
- OUT : 프로시저에서 처리된 결과를 OS로 전달하는 모드
- INOUT : IN과 OUT의 두 기능을 동시에 수행하는 모드
- IS는 AS로 쓸 수도 있음.
2. 시작/종료부
: 프로시저 본문의 시작과 종료를 나타내는 블록
: BEGIN ~ END
3. 제어부
: 제어문이나 반복문 등을 통해 실행흐름을 제어함.
4. SQL
: 데이터 조작 (조회, 생성, 변경, 삭제) 가능
5. 예외부
: 실행 중 생길 수 있는 예외 상황을 처리함.
|
1
2
3
|
EXCEPTION
WHEN 조건 THEN
SET 명령어;
|
cs |
6. 실행부
: 프로시저에서 수행한 내용을 DBMS에 반영할지 여부를 결정함
- COMMIT : 트랜잭션 결과를 DBMS에 반영.
- ROLLBACK : 트랜잭션을 처음부터 다시 시작하거나, 부분적으로 연산을 취소함.
<프로시저 전체 구성 요약 >
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
|
CREATE PROCEDURE 프로시저_이름
(입력받을_파라미터_이름 IN 데이터_타입)
IS
변수_이름 데이터_타입
BEGIN
IF 조건1
THEN
실행_내용
ELSEIF 조건2
THEN
실행_내용
END IF;
EXCEPTION
WHEN 예외조건 THEN
SET 예외처리_내용
COMMIT; --종료시 트랜잭션을 DBMS에 반영
END;
|
cs |
사용자 정의함수
: 사용자가 직접 정의하고 작성하는 함수. 기본적인 것은 프로시저와 같으나 리턴값이 있음.
: 선언부, 시작/종료부, 제어부, SQL, 예외부, 반환부로 구성
1. 선언부
2. 시작/종료부
: 함수 본문의 시작과 종료를 나타내는 블록
: BEGIN ~ END
3. 제어부
: 제어문이나 반복문 등을 통해 실행흐름을 제어함.
4. SQL
: SELECT만 가능
5. 예외부
: 실행 중 생길 수 있는 예외 상황을 처리함.
6. 반환부
: RETURN 명령을 통해 반환할 단일 값을 정의
<사용자 정의 함수 전체 구성 요약 >
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
|
CREATE PROCEDURE 프로시저_이름
(입력받을_파라미터_이름 IN 데이터_타입)
IS
변수_이름 데이터_타입
BEGIN
IF 조건1
THEN
실행_내용
ELSEIF 조건2
THEN
실행_내용
END IF;
RETURN 리턴값;
END;
|
cs |
트리거
: 특정한 이벤트(테이블 삽입, 수정 삭제 등) 발생 시 자동적으로 실행되도록 만든 프로그램.
: 트리거의 종류
- 행 트리거 - 데이터 변화가 생길 때마다 실행됨
- 문장 트리거 - 단 한 번만 실행됨
: 트리거 내에 COMMIT, ROLLBACK같은 트랜잭션 제어어(TCL)는 사용불가
: 선언부, 이벤트부, 시작/종료부, 제어부, SQL, 예외부로 구성됨.
1. 선언부
: 트리거의 이름, 변수 및 상수, 타입 선언
|
1
2
|
CREATE TRIGGER 트리거이름 -- 새 트리거 생성
CREATE OR REPLACE TRIGGER 트리거이름 -- 기존 트리거 존재 시 덮어씀
|
cs |
2. 이벤트부
: 트리거가 실행되는 이벤트 조건을 명시.
: BEFORE과 AFTER가 있다.
|
1
2
|
BEFORE ON 테이블이름 -- 이벤트 발생 전 트리거가 실행됨
AFTER ON 테이블이름 -- 이벤트 발생 후 트리거가 실행됨
|
cs |
3. 시작/종료부
: 트리거 본문의 시작과 종료를 나타내는 블록
: BEGIN ~ END
4. 제어부
: 제어문이나 반복문 등을 통해 실행흐름을 제어함.
5. SQL
: 데이터 조작(SELECT, INSERT, DELETE, UPDAT)을 주로 수행
6. 예외부
: 실행 중 생길 수 있는 예외 상황을 처리함.
<트리거 전체 구성 요약 >
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
|
CREATE TRIGGER 트리거이름
AFTER UPDATE OR DELETE -- 테이블에 수정/삭제가 발생한 후 트리거가 실행됨
ON 테이블이름
FOR EACH ROW
DECLARE
--변수 선언
BEGIN
IF UPDATING
THEN
--테이블에 수정 발생시 실행할 내용
ELSEIF DELETING
THEN
--\\테이블에 삭제발생 시 실행할 내용
END IF;
END; --종료
|
cs |
데이터 분석 함수
: 데이터 분석을 목적으로 하는 다중 행 함수. 대개 GROUP BY 구문을 활용해 여러 행을 그룹핑한다.
: SELECT, HAVING, ORDER BY 등의 구문에 활용함.
: 종류로는 집계함수, 그룹 함수, 윈도 함수가 있다.
집계함수
: 여러 행으로부터 하나의 결괏값을 반환하는 함수
- COUNT - 라인 갯수
- SUM - 합계
- AVG - 평균
- MAX - 최댓값
- MIN - 최솟값
- STDDEV - 표준편차
- VARIAN - 분산
그룹 함수
: 그룹별로 결과를 출력하는 함수
- ROLLUP - 지정된 컬럼의 중간 집계 값을 산출
- CUBE - 결합 가능한 모든 값에 대한 다차원 집계를 생성
- GROUPING SETS - 대상 컬럼들의 개별 집계를 구함. (컬럼 간 순서 무관)
윈도 함수 (=OLAP 함수, OnLine Analytical Processing)
: DB를 사용한 온라인 분석 처리 용도로 사용
: 순위 함수, 행순서 함수, 그룹 내 비율 함수가 있다.
1. 순위 함수
: 컬럼에 대한 순위를 구하는 함수
- RANK - 동일 순위 레코드 있을 때, 후순위는 넘어감 (EX. 1, 2, 2, 4, 5)
- DENSE_RANK - 동일 순위 레코드 있을 때, 후순위 넘어가지 않음 (EX. 1, 2, 2, 3, 4)
- ROW_NUMBER - 동일 순위 레코드 상관 없이 연속 번호 부여 (EX. 1, 2, 3, 4, 5)
2. 행순서 함수
: 파티션별 윈도에서 행의 순서를 다루는 함수
- FIRST_VALUE - 가장 먼저 나오는 값을 찾음
- LAST_VALUE - 가장 늦게 나오는 값을 찾음
- LAG - 이전 행의 값을 반환
- LEAD - 다음 행의 값을 반환
3. 그룹 내 비율 함수
: 해당하는 그룹 내에서 각 행의 비율을 구하는 함수
- RATIO_TO_REPORT - 0~1의 범위 내에서 각 행의 상대적 비율을 반환
- PERCENT_RANK - 제일 먼저 나오는 것을 0, 제일 늦게 나오는 것을 1로 하여 각 행의 순서별 백분율을 구함
참고한 자료: 수제비 정보처리기사 2020, 데이터베이스 배움터