[정보처리기사] SQL 응용

1. 데이터베이스 기본

트랜잭션

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

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

 

트랜잭션의 특성(ACID)

- 원자성: 연산 전체가 성공 또는 실패

- 일관성: 트랜잭션 실행  성공 후, 항상 일관된 데이터베이스 상태를 보존

- 격리성: 트랜잭션 실행 중, 생성하는 연산의 중간 결과를 다른 트랜잭션이 접근 불가

- 영속성: 성공이 완료된 트랜잭션의 결과는 영송적으로 데이터베이스에 저장된다

 

트랜잭션 상태 변화

- 활동 상태(Active): 초기 상태, 트랜잭션이 실행 중

- 부분 완료 상태(Pratially Committed): 마지막 명령문이 실행된 후

- 완료 상태(Committed): 트랜잭션이 성공적으로 완료된 후

- 실패 상태(Failed): 정상적인 실행이 더 이상 진행될 수 없을 때

- 철회 상태(Aborted): 트랜잭션이 취소되고 데이터베이스가 트랜잭션 시작 전 상태로 환원된 상태

 

트랜잭션 제어

- 커밋(COMMIT): 트랜잭션을 메모리에 영구적으로 저장하는 명령어

- 롤백(ROLLBACK): 트랜잭션 내역을 저장 무효화시키는 명령어

- 체크포인트(CHECKPOINT): 롤백을 위한 시점을 지정하는 명령어

 

병행 제어

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

- 데이터 베이스 공유, 시스템 활용도 최대화, 데이터 일관성 유지, 응답시간 최소화

- 갱신 손실, Dirty Read, 모순성, 연쇄복귀

- 로킹: 다중 트랜잭션 환경에서 트랜잭션의 순차적 진행을 보장하는 직렬화 기법

- 낙관적 검증: 검증없이 트랜잭션 수행 후, 트랜잭션 종료 시 검증을 수행해 데이터베이스에 반영

- 타임 스탬프 순서: 트랜잭션과 데이터에 대해 트랜잭션 실행 전 타임 스탬프를 부여해 부여된 시간에 따라 트랜잭션 작업 수행

- 다중버전 동시성 제어: 트랜잭션의 타임스탬프와 접근하려는 데이터의 타임스탬프를 비교해 직렬가능성이 보장하도록 버전을 선택해 접근하는 기법

 

고립화 수준

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

- Read Uncommiitted: 한 트랜잭션에서 연산 중인 데이터를 다른 트랜잭션에서 읽기를 허용

- Read Committed: 한 트랜잭션에서 연산이 될 때까지 연상 대상 데이터에 대한 읽기를 제한

- Repeatable Read: 선행 트랜잭션이 데이터를 읽을 때, 트랜잭션 종료 시까지 해당 데이터에 대한 갱신-삭제를 제한

- Serializable Read: 선행 트랜잭션이 데이터를 읽을 때, 트랜잭션 종료 시가지 해당 데이터 영역 전체에 대한 접근 제한

 

회복 기법

- 로그 기반 회복 기법: 지연 갱신 회복 기법, 즉각 갱신 회복 기법

- 체크 포인트 회복 기법

- 그림자 페이징 회복 기법

 

DDL

- 테이블과 같은 데이터 구조를 정의하는 데 사용되는 명령어

- 테이블, 뷰, 인덱스

- CREATE, ALTER, DROP, TRUNCATE

 

인덱스

- 순서, 해시, 비트맵, 함수기반, 단일, 결합, 클러스터드

- 인덱스 범위 스캔, 인덱스 전체 스캔, 인덱스 단일 스캔, 인덱스 생략 스캔

 

DML

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

- SELECT, INSERT, UPDATE, DELETE

 

DCL

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

- GRANT, REVOKE

 

2. 응용 SQL 작성하기

집계 함수

- COUNT, SUM, AVG, MAX, MIN, STDDEV, VARIAN

 

그룹 함수

- ROLLUP, CUBE, GROUPING SETS

 

윈도 함수

- 순위 함수: RANK, DENSE_RANK, ROW_NUMBER

- 행 순서 함수: FIRST_VALUE, LAST_VALUE, LEAD

- 그룹 내 비율 함수: RATIO_TO_REPOT, PERCENT_RANK

 

3. 절차형 SQL 활용하기

절차형 SQL

- 프로시저, 사용자 정의 함수, 트리거

 

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

옵티마이저

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

- RBO(Rule Based Optimizer): 통계 정보가 없는 상태에서 사전 등록된 규칙에 따라 질의실행계획을 선택

- CBO(Cost Based Optimizer): 통계 정보로부터 모든 접근 경로를 고려한 질의실행계획을 선택

- 쿼리 변환, 비용 산정, 계획 생성

- 힌트

 

-- 컬럼 삭제
ALTER TABLE 테이블명 DROP 속성이름;

-- 컬럼 추가
ALTER TABLE 테이블이름 ADD 속성이름;

-- 테이블 생성
CREATE TABLE 테이블명 (
	애트리뷰트 이름 VARCHAR(10)
);

-- 뷰 생성
CREATE VIEW 뷰이름 AS
    SELECT 속성
    FROM 테이블
    WHERE 조건;

-- 인덱스 생성
CREATE INDEX 인덱스이름 ON 테이블이름(속성, 속성 ... );

-- 데이터 삽입
INSERT INTO 테이블(속성, 속성 , ..) VALUES (값, 값, ..)

-- 데이터 삭제
DELETE FROM 테이블 WHERE 조건;

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

-- 권한 부여
GRANT 권한 ON 테이블 TO 사용자;

-- 권한 회수
REVOKE 권한 ON 테이블 FROM 사용자;