[정보처리기사 실기] 7과목 - SQL 응용

01. 데이터베이스 기본

 

1. 트랜잭션

인가받지 않은 사용자로부터 데이터를 보장하기 위해 DBMS가 가져야 하는 특성

데이터베이스 시스템에서 하나의 논리적 기능을 정상적으로 수행하기 위한 작업의 기본단위

 

1) 트랜잭션특성 - ACID

특성	설명	주요기법
원자성 (Atomicity)	분해가 불가능한 작업의 최소단위 연산 전체가 성공 또는 실패 하나라도 실패할 경우 전체가 취소되어야 함	Commit / Rollback 회복성보장
일관성 (Consistency)	트랜잭션이 실행 성공후 항상 일관된 데이터베이스 상태 보존	무결성 제약조건 동시성 제어 병행제어
격리성 (Isolation)	트랜잭션 실행중 생성하는 연산의 중간 결과를 다른 트랜잭션이 접근 불가	Read Uncommited Read Commited Repeatable Read Serializable Read
영속성 (Durability)	성공이 완료된 트랜잭션의 결과는 영속적으로 데이터베이스에 저장	회복기법

 

2) 데이터베이스 고립화 수준 (격리성 주요기법)

다른 트랜잭션이 현재의 데이터에 대한 무결성을 해치지 않기 위해 잠금 설정 하는 정도 

수준	설명
Read Uncommited	한 트랜잭션에서 연산중인 데이터를 다른 트랜잭션이 읽는것을 허용하는 수준 연산(갱신)중인 데이터에 대한 연산은 불허
Read Commited	한 트랜잭션에서 연산을 수행할 때 연산이 완료될 때 까지는 연산 대상 데이터에 대한 읽기를 제한 연산이 완료되어 커밋된 데이터는 다른 트랜잭션이 읽는것을 허용
Repeatable Read	선행 트랜잭션이 특정 데이터를 읽을 때 트랜잭션 종료시 까지 해당 데이터에 대한 갱신, 삭제를 제한
Serializable Read	선행 트랜잭션이 특정 데이터 영역을 순차적으로 읽을 때 해당 데이터 영역 전체에 대한 접근 제한

 

3) 트랜잭션 상태변화 -환부완실철

상태	설명
활동(Active)	초기상태, 트랜잭션이 실행 중일때 가지는 상태
부분완료상태 (Partially Committed)	마지막 명령문이 실행된 후 가지는 상태
완료상태 (Committed)	트랜잭션이 성공적으로 완료된 후 가지는 상태
실패상태 (Failed)	정상적인 실행이 더이상 진행될 수 없을때 가지는 상태
철회상태 (Aborted)	트랜잭션이 취소되고 데이터베이스가 트랜잭션 시작전 상태로 환원

 

4) 트랜잭션 제어 - 커롤체

트랜잭션 제어어 TCL (Transaction Control Language)

트랜잭션 결과를 허용하거나 취소하는 목적으로 사용되는 언어 지칭

명령어	핵심	설명
커밋 (COMMIT)	트랜잭션 확정	트랜잭션 메모리에 영구적 저장
롤백 (ROLLBACK)	트랜잭션 취소	트랜잭션 내역 무효화
체크포인트 (CHECKPOINT)	저장시기 설정	ROLLBACK을 위한 시점 지정

 

5) 병행제어 (일관성주요기법)

다수 사용자 환경에서 여러 트랜잭션을 수행할 때 데이터베이스 일관성 유지를 위해 상호작용 제어하는 기법

 

(1) 목적

• 데이터 베이스의 공유 최대화
• 시스템 활용도 최대화
• 데이터 베이스 일관성 유지
• 사용자에 대한 응답시간 최소화

(2) 병행제어 미보장시 문제점 - 갱현모연

문제점	설명
갱신손실 (Lost update)	먼저 실행된 트랜잭션 결과를 나중에 실행된 트랜잭션이 덮어 쓸 때 발생하는 오류
현황 파악 오류 (Dirty Read)	트랜잭션의 중간 수행 결과를 다른 트랜잭션이 참조하여 발생하는 오류
모순성 (Incosistency)	두 트랜잭션이 동시에 실행되어 데이터베이스의 일관성이 결여되는 오류
연쇄복귀 (Cascading Rollback)	복수의 트랜잭션이 데이터 공유 시 특정 트랜잭션이 처리를 취소할 경우 트랜잭션이 처리한 곳의 부분을 취소하지 못하는 오류

 

(3) 병행 제어 기법 - 로 낙타다

기법	설명
로킹 (Locking)	트랜잭션들이 동일한 데이터에 동시에 접근하지 못하도록 lock과 ulock연산으로 제어한다. 로킹 단위가 커질수록 병행성이 낮아지지만 제어가 쉽고, 로킹단위가 작아질수록 제어가 어렵지만 병행성이 높아진다. 1) lock 연산 트랜잭션이 데이터에대 한 독점권을 요청하는 현상 2) unlock연산 트랜잭션이 데이터에 대한 독점권을 반환하는 현상
낙관적검증	트랜잭션이 어떠한 검증도 수행하지 않고 일단 트랜잭션 수행하고 트랜잭션 종료시 검증을 수행하여 데이터베이스에 반영하는 기법
타임스탬프순서	트랜잭션과 트랜잭션이 읽거나 갱신한 데이터에대해 트랜잭션이 실행을 시작하기 전에 타임스탬프를 부여하여 부여시간에 따라 트랜잭션 작업 수행
다중버전동시성 (MVCC; Murti Version Concurrency Control)	트랜잭션의 타임스탬프와 접근하려는 데이터의 타임스탬프를 비교하여 직렬가능성이 보장되는 적절한 버전을 선택하여 접근하도록 하는기법

 

6) 회복 기법 (영속성 주요기법) 

트랜잭션을 수행하는 도중 장애로 인해 손상된 데이터베이스를 손상되기 이전의 정상적 상태로 복구시키는 작업

 

(1) 종류 - 회로체크

기법	설명
로그 기반 회복 기법	지연 갱신 회복 기법 - 트랜잭션이 완료되기 전까지 데이터 베이스에 기록하지 않는 방법 (Deferred Update) 즉각 갱신 회복 기법 - 트랜잭션 수행중 갱신 결과를 바로 DB에 반영 (Immediate Update)
체크 포인트 회복 기법 (Checkpoint Recovery)	장애 발생시 검사점 이후에 처리된 트랜잭션에 대해서만 장애 발생 이전 상태로 복원 시키는 회복기법
그림자 페이징 회복 기법	데이터베이스 트랜잭션 수행시 복제본을 생성하여 데이터베이스 장애시 이를 이용해 복구하는 기법

 

2. DDL

1) 데이터정의어

데이터 구조를 정의하는데 사용되는 명령어 특정 구조를 생성, 변경, 삭제 이름을 바꾸는 데이터 구조와 관련된 명령어 들을 데이터 정의어라고 부름

 

2) DDL 대상 - 도스테뷰인

DDL	설명
도메인 (Domain)	하나의 속성이 가질 수 있는 원자값들의 집합 속성의 데이터 타입과 크기, 제약조건 등의 정보
스키마 (Schema)	데이터베이스 구조, 제약조건 등 정보를 담고있는 기본적인 구조 외부 : 사용자, 개발자의 관점에서 필요로하는 데이터베이스의 논리적 구조, 사용자 뷰, 서브 스키마 개념 : 데이터 베이스의 전체적인 논리구조, 전체적인 뷰, 개체 간의 관계/제약조건/접근권한/무결성/ 보안에 대해 정의 내부 : 물리적 저장장치의 관점에서 보는 데이터베이스구조, 실제로 데이터베이스에 저장될 레코드 형식 정의/ 저장데이터 항목의 표현방법/ 내부 레코드의 물리적 순서등 표현
테이블 (Table)	데이터 저장공간
뷰 (View)	하나이상의 물리 데이틀에서 유도되는 가상의 테이블
인덱스 (Index)	검색을 빠르게 하기 위한 데이터구조

 

3) 뷰 - 장점과 단점

구분	용어	설명
장점	논리적 독립성 제공	논리 테이블 (테이블 구조가 변경되어도 뷰를 사용하는 응용프로그램은 변경하지 않아도됨)
	사용자 데이터 관리 용이	복수 테이블에 존재하는 여러 종류의 데이터에 대해 단순한 질의어 사용 가능
	데이터 보안 용이	중요 보안 데이터를 저장중인 테이블에는 접근 불허 해당 테이블의 일부 정보만 볼 수 있는 뷰에는 접근 허용 보안 데이터에 대한 접근 제어 가능
단점	뷰 자체 인덱스 불가	인덱스는 물리적으로 저장된 데이터를 대상으로 하기에 논리적 구성인 뷰 자체는 인덱스를 가지지 못함
	뷰 정의 변경 불가	뷰의 정의를 변경하려면 뷰를 삭제하고 재생성
	데이터 변경 제약 존재	뷰의 내용에 대한 삽입, 삭제, 변경 제약이 있음

 

4) 테이블 관련 용어 - 튜행카, 애열디

용어	설명
튜플/행 (Tuple/Row)	테이블 내의 행을 의미, 레코드라고도함 튜플은 릴레이션에서 같은 값을 가질 수 없음
카디널리티 (Cardinality)	튜플의 갯수
애트리뷰트/열	테이블 내의 열을 의미
차수/디그리 (Degree)	애트리뷰트의 개수
도메인 (Domain)	하나의 애트리뷰트가 취할 수 있는 같은 타입의 원자값 들의 집합
식별자 (Identifier)	여러개의 집합체를 담고있는 관계형 데이터베이스에서 각각의 구분할 수 있는 논리적인 개념

 

5) 인덱스의 종류 - 순해비함단결클

종류	설명
순서 인덱스 (Ordered Index)	데이터가 정렬된 순서로 생성되는 인덱스 B-Tree 알고리즘 활용 (오르내림 지정가능)
해시 인덱스 (Hash Index)	해시 함수에 의해 직접 데이터에 키 값으로 접근하는 인덱스 데이터 접근 비용이 균일, 튜플 양에 무관
비트맵 인덱스 (Bitmap Index)	각 컬럼에 적은 개수 값이 저장된 경우 선택하는 인덱스 수정 변경이 적을 경우 유용 (생년월일, 상품번호)
함수 기반 인덱스 (Functional index)	수식이나 함수를 적용하여 만든 인덱스
단일 인덱스 (Single Index)	하나의 컬럼으로만 구성한 인덱스 주 사용 컬럼이 하나일 경우 사용
결합 인덱스 (Concatenated Index)	두개 이상의 컬럼으로 구성한 인덱스 WHERE 조건으로 사용하는 빈도가 높은 경우 사용
클러스터드 인덱스 (Clutered Index)	기본 키를 기준으로 레코드를 묶어서 저장하는 인덱스 저장 데이터의 물리적 순서에 따라 인덱스가 생성 특정 범위 검색시 유리함

 

6) 인덱스의 스캔방식 - 범전단생

종류	설명
인덱스 범위 스캔 (Index Range Scan)	인덱스 루트블록에서 리프블록까지 수직적으로 탐색한 후 리프블록을 필요한 범위만 스캔
인덱스 전체 스캔 (Index Full Scan)	수직적 탐색없이 인덱스 리프블록을 처음부터 끝까지 수평적으로 탐색
인덱스 단일 스캔 (Index Unique Scan)	수직적 탐색만으로 데이터를 찾는 스캔
인덱스 생략 스캔 (Index Skip Scan)	선두 컬럼이 조건절에 빠졌어도 인덱스를 활용하는 스캔

 

7) DDL명령어 - 크알드트 (CRDT)

구분	DDL 명령어
생성	CREATE TABLE orders ( id CHAR(20) PRIMARY KEY,   name VARCHAR(20) FOREIGN KEY REFERENCES members (name),   birth DATE NOTNULL,   gender CHAR(1) CHECK (gender = 'M' or gender = 'F'),   joindate DATE DEFAULT SYSDATE ); CREATE VIEW v1 AS SELECT id, name FROM orders WHERE gender = 'M'; CREATE OR REPLACE VIEW v1 AS SELECT id, name FROM orders WHERE gender = 'M'; CREATE INDEX idx1 ON orders(name, tel);
수정	ALTER TABLE orders ADD telno VARCHAR(11) UNIQUE; - 컬럼추가 ALTER TABLE orders MODIFY telno number(20) UNIQUE; - 컬럼수정 ALTER TABLE orders DROP telno; ALTER INDEX idx1 ON orders(name);
삭제	DROP TABLE orders; - 테이블 삭제 DROP TABLE orders CASCADE | RESTRICT; - 참조테이블 함께삭제 | 삭제 제한 DROP VIEW v1; DROP INDEX idx1;
	TRUNCATE TABLE orders; - 테이블내 데이터 삭제

 

3. DML 

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

SELECT , INSERT, UPDATE, DELETE 

 

* SELECT 명령문 - 셀프웨구해오

SELECT id,
  FROM orders
 WHERE addr = '서울'
 GROUP BY gender = 'M'
HAVING [그룹조건]
ORDER BY DESC;

 

구분	DML 명령어
읽기	SELECT * FROM orders WHERE addr = '서울';
삽입	INSERT INTO orders(addr) VALUES('부산');
수정	UPDATE orders SET addr = '경주' WHERE addr = '부산';
삭제	DELETE FROM 학생 WHERE 이름 = '홍길동';

 

4. 서브쿼리

SQL문 안에 포함된 또다른 SQL문

서브쿼리종류	설명
SELECT 서브쿼리	서브쿼리가 SELECT절 안에 들어있는 형태 스칼라 서브쿼리 (Scalar Sub-Query)라고도 불림 SELECT 절에 오는 서브쿼리는 반드시 단일 행을 리턴해야함 SUM, COUNT, MIN, MAX 등 집계 함수가 많이 쓰임
FROM 서브쿼리	서브쿼리가 FROM절안에 들어있는 형태 인라인 뷰 (Inline Views)라고 불림 뷰처럼 결과가 동적으로 생성된 테이블 형태로 사용할 수 있음
WHERE 서브쿼리	서브쿼리가 WHERE절안에 들어있는 형태 중첩 서브쿼리 (Nested Sub Query) 라고도 불림

 

5. DCL

데이터 제어어 - 그온투, 리온마

DCL유형	동작	설명
GRANT	권한부여 GRANT select ON orders TO admin1	사용자에게 데이터베이스 대한 권한 부여
REVOKE	권한취소 REVOKE select ON orders FROM admin1	사용자에게 부여했던 권한 회수

 

---

02. 응용 SQL 작성

1. 집계성 SQL

1) 데이터 분석 함수

총합, 평균 등 데이터 분석을 위해 복수 행 기준의 데이터 모아서 처리하는 것 = 다중 행 함수

 

2) 데이터 분석 함수 종류

함수	설명	구문	함수
집계 함수	여러 행 또는 전체 행으로 부터 하나의 결괏값 반환하는 함수	GROUP BY HAVING	COUNT, SUM, AVG, MAX, MIN, STDDEV,
그룹 함수	소그룹 간의 소계 및 중계 등의 중간 합계 분석 데이터 산출		ROLLUP - 중간집계 값 산출 CUBE - 다차원집계 (명시한 모든컬럼)값 산출 GROUPING SETS - 컬럼들에 대한 개별 집계
윈도 함수	데이터 베이스를 사용한 온라인 분석 초리 용도로 사용하기 위해 표준 SQL에 추가된 기능	OVER	RANK, DENSE_RANK, ROW_NUMBER FIRST_VALUE, LAST_VALUE, LAG, LEAD RATIO_TO_REPORT, PERCENT_RANK

 

03. 절차형 SQL 작성

일반적인 개발 언어처럼 SQL 언어에서도 절차 지향적인 프로그램이 가능하도록 하는 트랜잭션 언어

종류	설명
프로시저 (Procedure)	일련의 쿼리들을 마치 하나의 함수처럼 실행하기 위한 쿼리 집합
사용자 정의함수 (User Defined Function)	일련의 SQL처리를 수행하고 수행결과를 단일값으로 반환할 수 있는 절차형 SQL
트리거 (Trigger)	데이터베이스 시스템에서 삽입, 갱신, 삭제등의 이벤트가 발생할 때마다 관련 작업이 자동으로 수행되는 절차형 SQL

 

1) 출력부

메시지를 버퍼에 저장하고 버퍼로부터 메시지를 읽어오기 위한 인터페이스 패키지

명령어	설명
DBMS_OUTPUT.PUT	개행없이 문자열을 출력하는 프로시저
DBMS_OUTPUT.PUT_LINE	문자열을 출력 후 개행하는 프로시저

 

2) 제어부

 

(1) 조건문

• IF문

IF문은 조건이 참인지 거짓인지에 따라 경로를 선택하는 조건문

IF 조건 THEN
	문장;
ELSIF 조건 THEN
	문장;
ELSE
	문장;
END IF;

 

• 간단한 케이스문 

명확한 값을 가지는 조건에 따라 여러개의 선택경로 중 하나를 취하고자 할때 사용하는 조건문

범위같은 더 복잡한 매칭을 수행하려면 검색된 CASE문을 사용해야한다.

CASE 변수
	WHEN 값1 THEN
     SET 명령어;
    WHEN 값2 THEN
     SET 명령어;
 	ELSE
     SET 명령어;
END CASE;

 

•  검색된 케이스문 

명확한 값 및 범위를 가지는 조건에 따라 여러개의 선택 경로중 하나를 취하고자 할 때 사용하는 조건문

CASE 
	WHEN 값1 THEN
     SET 명령어;
    WHEN 값2 THEN
     SET 명령어;
 	ELSE
     SET 명령어;
END CASE;

 

(2) 반복문

• LOOP

LOOP
	문장;
    EXIT WHEN 탈출조건;
END LOOP;

• WHILE

WHILE 반복조건 LOOP
	문장;
EXIT WHEN 탈출조건;
END LOOP;

• FOR LOOP문

FOR 인덱스 IN 시작값 .. 종료값
LOOP
	문장
END LOOP;

 

(3) 예외부

 

EXCEPTION
	WHEN 조건 THEN
     SET 명령어;

 

 

3) 프로시저 - 디비컨 SET

일련의 쿼리들을 마치 하나의 함수처럼 실행하기 위한 쿼리 집합

구성요소	설명
선언부 (DECLARE)	프로시저의 명칭, 변수와 인수 그리고 그에대한 데이터타입 정의
시작/종료부 (BEGIN/END)	프로시저 시작과 종료를 표현 , 쌍을 이룸 다수 실행을 제어하는 기본적 단위가 되면 논리적 프로세스를 구성
제어부 (CONTROL)	기본적으로 순차적으로 처리 조건문과 반복문을 이용하여 문장 처리
SQL	DML주로사용, 자주사용되지 않지만 DDL중 TRUNCATE사용
예외부  (EXCEPTION)	BEGIN~END절에서 실행되는 SQL 문이 실행될때 예외 발생시 예외처리방법을 정의하는 처리부
실행부 (TRANSACTION)	프로시저에서 수행된 DML수행 내역의 DBMS적용 또는 취소 여부를 결정하는 처리부

 

4) 사용자정의함수 - 디비컨SER

일련의 SQL처리를 수행하고 수행결과를 단일값으로 반환할 수 있는 절차형 SQL

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

 

5) 트리거 - 디이비컨 SE

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

삽, 삭 , 갱 작업을 DBMS가 자동적으로 실행시키는데 활용

데이터 무결성 유지 및 로그 메시지 출력등 별도 처리를 위해 트리거를 사용한다.

종류	설명
행트리거	데이터변화가 생길때마다실행
문장트리거	트리거에의해 단 한번실행

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

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

 

04. 데이터 조작 프로시저 최적화

1. 쿼리 성능 개선 (튜닝) 개념

데이터베이스에서 프로시저에 있는 SQL 실행 계획을 분석, 수정을 통해 최소의 시간으로 원하는 결과를 얻도록 프로시저를 수정하는 작업

 

01. 옵티마이저 (Optimizer)

SQL을 가장 빠르고 효육적으로 수행할 최적의 처리 경로를 생성해주는 DBMS내부 핵심엔진

옵티마이저가 생성한 SQL 처리경로를 실행계획이라고 부른다.

 

비교	규칙기반 옵티마이저 (Rule Based Op-)	비용기반 옵티마이저 (Cost Based Op-)
개념	통계 정보가 없는 상태에서 사전 등록된 규칙에 따라 질의 실행 계획을 선택	통계 정보로부터 모든 접근 경로를 고려한 질의시행 계획
핵심	규칙(우선 순위)기반	비용 (수행 시간)기반
평가 기준	인덱스 구조, 연산자, 조건절	레코드 개수, 블록 개수, 평균 행 길이, 컬럼 값의 수, 컬럼 값 분포, 인덱스 높이 클러스터링 팩터 등
장점	사용자가 원하는 처리 경로로 유도하기 쉬움	옵티마이저의 이해도가 낮아도 성능 보장 가능

 

02. 힌트(Hint)

SQL 성능 개선의 핵심 부분으로 옵티마이저의 실행 계획을 원하는대로 변경할 수 있게 함

옵티마이저가 항상 최선의 실행계획을 수립할 수 없어 명시적인 힌트를 통해 실행계획을 변경한다.

SELECT /*+ RULE*/ ENAME, SAL
  FROM EMP
 WHERE EMPNO > 90000;
 
 -- 비용기반 옵티마이저에서 규칙기반 옵티마이저모드로 변경수행

 

alter table 성적 add 학점 varchar(4) notnull;

select 학번, 이름 where 학과 = 전산과; 

select 부서, sum(급여합계) from 급여 group by 부서 having >= 6000;