[정처기/실기] 7단원(SQL 응용) 요약

*  수제비 정보처리기사 실기 책을 보고 직접 정리한 내용입니다.

1. 데이터베이스 기본

[1-1] 트랜잭션의 특성

• 트랜잭션(Transaction)
  • 인가받지 않은 사용자로부터 데이터를 보장하기 위해 DBMS가 가져야하는 특성
  • 데이터베이스 시스템에서 하나의 논리적 기능을 정상적으로 수행하기 위한 작업의 기본 단위

원자성(Atomicy)

• 트랜잭션의 연산 전체가 모두 정상적으로 실행되거나 모두 취소되어야하는 성질
• 트랜잭션의 연산 전체가 성공 또는 실패(All or Nothing)되어야 하는 성질

일관성(Consistency)

• 시스템이 가지고 있는 고정요소는 트랜잭션 수행 전과 수행 완료 후의 상태가 같아야 하는 성질

격리성 = 고립성(Isolation)

• 동시에 실행되는 트랜잭션들이 서로 영향을 미치지 않아야 한다는 성질

영속성(Durability)

• 성공이 완료된 트랜잭션의 결과는 영속적으로 데이터베이스에 저장되어야 하는 성질

[1-2] 트랜잭션의 상태 변화

활동 상태(Active)

• 초기 상태, 트랜잭션이 실행 중일 때 가지는 상태

부분 완료 상태(Partially Committed)

• 마지막 명령문이 실행된 후에 가지는 상태

완료 상태(committed)

• 트랜잭션이 성공적으로 완료된 후 가지는 상태

실패 상태(Failed)

• 정상적인 실행이 더 이상 진행될 수 없을 때 가지는 상태

철회 상태(Aborted)

• 트랜잭션이 취소되고 데이터베이스가 트랜잭션 시작 전 상태로 환원된 상태

[1-3] 트랜잭션 제어(TCL)

• 트랜잭션 제어 언어(TCL; Transaction Control Language) : 트랜잭션 결과를 허용하거나 취소하는 목적으로 사용되는 언어

커밋(COMMIT)

• 트랜잭션 확정
• 트랜잭션을 메모리에 영구적으로 저장하는 명령어

롤백(ROLLBACK)

• 트랜잭션 취소
• 트랜잭션 내역을저장 무효화시키는 명령어

체크포인트(CHECKPOINT)

• 저장 시기 설정
• ROLLBACK을 위한 시점을지정하는 명령어

[1-4] 병행제어(일관성 주요 기법)

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

목적

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

병행 제어 미보장 시 문제점

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

병행 제어 기법의 종류

• 로킹(Locking)
  • 하나의 트랜잭션을 실행하는 동안 특정 데이터 항목에 대해서 다른 트랜잭션이 동시에 접근하지 못하도록 상호배제(Mutual Exclusion) 기능을 제공하는 기법
  • 특징
    • 한꺼번에 로킹할 수 있는 객체의 크기를 로킹 단위라고 함
    • 데이터베이스, 파일, 레코드 등은 로킹 단위가 될 수 있음
    • 로킹 단위가 작아지면 데이터베이스 공유도 증가
    • 로킹 단위가 작아지면 로킹 오버헤드 증가
• 낙관적 검증(Optimistic Validation)
  • 트랜잭션이 어떠한 검증도 수행하지 않고 일단 트랜잭션을 수행하고, 트랜잭션 종료 시 검증을 수행하여 데이터베이스에 반영하는 기법
• 타임 스탬프 순서(Time Stamp Ordering)
  • 트랜잭션 실행 전 타임 스탬프를 부여하여 부여된 시간에 따라 트랜잭션 작업을 수행하는 기법
• 다중 버전 동시성 제어(MVCC; Multi Version Concurrency Control)
  • 트랜잭션의 타임 스탬프와 접근하려는 데이터의 타임 스탬프를 비교하여 직렬 가능성이 보장되는 적절한 버전을 선택하여 접근하도록 하는 기법

[1-5] 데이터베이스 고립화 수준(격리성 주요 기법)

• 고립화 수준(Isolation Level) : 다른 트랜잭션이 현재의 데이터에 대한 무결성을 해치지 않기 위해 잠금을 설정하는 정도

고립화 수준 종류

• Read Uncommited
• Read Commited
• Repeatable Read
• Serializable Read

[1-6] 회복 기법(영속성 주요 기법)

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

REDO

• 데이터베이스가 비정상적으로 종료되었을 때, 디스크에 저장된 로그를 분석하여 트랜잭션 시작과 완료에 대한 기록이 있는 트랜잭션들의 작업을 재작업하는 기법
• 데이터베이스 내용 자체가 손상된 경우, 가장 최근의 복제본을 적재한 후, 이후 일어난 변경만을 로그를 이용하여 재실행함으로써 데이터베이스를 복원하는 기법

UNDO

• 데이터베이스가 비정상적으로 종료되었을 때 디스크에 저장된 로그를 분석하여 트랜잭션의 시작은 있지만, 완료 기록이 없는 트랜잭션들이 작업한 변경 내용들을 모두 취소하는 기법
• 데이터베이스 내용 자체는 손상되지 않았지만, 변경 중이거나 변경된 내용에 대한 신뢰성을 잃어버린 경우, 모든 변경 내용을 취소하여 복원하는 기법

회복 기법 종류

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

[2-1] 데이터 정의어(DDL; Data Definition Language)

• 테이블과 같은 데이터 구조를 정의하는데 사용되는 명령어
• 특정 구조를 생성, 변경, 삭제, 이름 바꾸는 데이터 구조와 관련된 명령어

[2-2] DDL의 대상

도메인

• 하나의 속성이 가질 수 있는 원자값들의 집합
• 속성의 데이터 타입과 크기, 제약조건 등의 정보

스키마

• 데이터베이스의 구조, 제약조건 등의 정보를 담고 있는 기본적인 구조
• 외부/개념/내부 3계층으로 구성
  • 외부 스키마(=서브 스키마)
    • 사용자나 개발자의 관점에서 필요로 하는 데이터베이스의 논리적 구조
    • 사용자 뷰를 나타냄
  • 개념 스키마
    • 데이터베이스의 전체적인 논리적 구조
    • 전체적인 뷰를 나타냄
    • 개체 간의 관계, 제약 조건, 접근 권한, 무결성, 보안에 대해 정의
  • 내부 스키마
    • 물리적 저장장치의 관점에서 보는 데이터베이스 구조

테이블

• 데이터를 저장하는 항목인 필드들로 구성된 데이터의 집합체(=릴레이션, 엔터티)
• 테이블 관련 용어
  • 튜플/행 /레코드 : 릴레이션에서 같은 값을 가질 수 없음
  • 애트리뷰트/열 : 열의 개수를 디그리(Degree)라고 함
  • 식별자 : 여러 개의 집합체를 담고 있는 관계형 데이터베이스에서 각각의 구분할 수 있는 논리적인 개념
  • 카디널리티 : 튜플의 개수
  • 차수 : 애트리뷰트의 개수
  • 도메인 : 하나의 애트리뷰트가 취할 수 있는 같은 타입의 원자값들의 집합

뷰

• 하나 이상의 물리적 테이블에서 유도되는 가상의 테이블/논리 테이블
• 뷰의 특징
  • 논리적 데이터 독립성 제공 : 데이터베이스에 영향을 주지 않고 애플리케이션이 원하는 형태로 데이터에 접근 가능
  • 데이터 조작 연산 간소화 : 애플리케이션이 원하는 형태의 논리적 구조를 형성하여 데이터 조작 연산을 간소화
  • 보안 기능 제공(접근 제어) : 특정 필드만을 선택해 뷰를 생성할 경우 애플리케이션은 선택되지 않은 필드의 조회 및 접근 불가
  • 뷰 변경 불가 : 뷰 정의는 ALTER문을 이용하여 변경할 수 없음(뷰는 CREATE문을 사용하여 정의, 뷰를 제거할 때는 DROP문 사용)
• 뷰의 장점
  • 논리적 독립성 제공 : 테이블의 구조가 바뀌어도 뷰를 사용하는 응용 프로그램은 변경하지 않아도 됨
  • 사용자 데이터 관리 용이 : 단순한 질의어 사용 가능
  • 데이터 보안의 용이 : 보안 데이터에 대한 접근 제어 가능
• 뷰의 단점
  • 뷰 자체 인덱스 불가
  • 뷰 정의 변경 불가 : 뷰의 정의를 변경하려면 뷰를 삭제하고 재생성
  • 데이터 변경 제약 존재 : 뷰의 내용에 대한 삽입, 삭제, 변경 제약이 있음

인덱스

• 검색을 빠르게 하기 위한 데이터 구조
• 인덱스 특징
  • 기본 키(PK; Primary Key) 컬럼은 자동으로 인덱스가 생성됨
  • 연월일이나 이름을 기준으로 하는 인덱스는 자동으로 생성되지 않음
  • 테이블의 컬럼에 인덱스가 없는 경우, 테이블의 전체 내용을 검색(테이블 전체 스캔; Table Full Scan)
  • 인덱스가 생성되어 있을 때 데이터를 빠르게 찾을 수 있음(인덱스 범위 스캔; Index Range Scan)
  • 조건절에 ‘=’으로 비교되는 컬럼을 대상으로 인덱스를 생성하면 검색 속도를 높일 수 있음
• 인덱스의 종류
  • 순서 인덱스
    • 데이터가 정렬된 순서로 생성되는 인덱스
    • B-Tree 알고리즘 활용
  • 해시 인덱스
    • 해시 함수에 의해 직접 데이터에 키 값으로 접근하는 인덱스
    • 데이터 접근 비용이 균일, 튜플 양에 무관
  • 비트맵 인덱스
    • 각 컬럼에 적은 개수 값이 저장된 경우 선택하는 인덱스
    • 수정 변경이 적을 경우 유용
  • 함수기반 인덱스
    • 수식이나 함수를 적용하여 만든 인덱스
  • 단일 인덱스
    • 하나의 컬럼으로만 구성한 인덱스
    • 주 사용 컬럼이 하나일 경우 사용
  • 결합 인덱스
    • 두 개 이상의 컬럼으로 구성한 인덱스
    • WHERE 조건으로 사용하는 빈도가 높은 경우 사용
  • 클러스터드 인덱스
    • 기본 키 기준으로 레코드를 묶어서 저장하는 인덱스
    • 저장 데이터의 물리적 순서에 따라 인덱스가 생성
    • 특정 범위 검색 시 유리함

[2-3] DDL 명령어

CREATE

• 데이터베이스 오브젝트 생성

ALTER

• 데이터베이스 오브젝트 변경

DROP

• 데이터베이스 오브젝트 삭제

TRUNCATE

• 데이터베이스 오브젝트 내용 삭제

[2-4] TABLE 관련 DDL

CREATE TABLE

CREATE TABLE 테이블명
(
	컬럼명 데이터타입 [제약조건],
	...
);

ALTER TABLE

• ALTER TABLE 컬럼 추가

ALTER TABLE 테이블명 ADD 컬럼명 데이터타입 [제약조건];

• ALTER TABLE 컬럼 수정

ALTER TABLE 테이블명 MODIFY 컬럼명 데이터타입 [제약조건];

• ALTER TABLE 컬럼 삭제

ALTER TABLE 테이블명 DROP COLUMN 컬럼명;

DROP TABLE

• 테이블을 삭제하는 명령어

DROP TABLE 테이블명 [CASCADE | RESTRICT];

• DROP TABLE 명령어 옵션
  • CASCADE : 참조하는 테이블까지 연쇄적으로 제거하는 옵션
  • RESTRICT : 다른 테이블이 삭제할 테이블을 참조 중이면 제거하지 않는 옵션

TRUNCATE TABLE

• 테이블 내의 데이터들을 삭제하는 명령어

TRUNCATE TABLE 테이블명;

[2-5] VIEW 관련 DDL

CREATE VIEW

• 뷰를 생성하는 명령어
• VIEW 테이블의 SELECT 문에는 UNION 이나 ORDER BY 절을 사용할 수 없음

CREATE VIEW 뷰이름 AS
조회쿼리;

CREATE OR REPLACE VIEW

• 뷰를 교체하는 명령어

CREATE OR REPLACE VIEW 뷰이름 AS
조회쿼리;

DROP VIEW

• 뷰를 삭제하는 명령어

DROP VIEW 뷰이름;

[2-6] INDEX 관련 DDL

CREATE INDEX

• 인덱스를 생성하는 명령어

CREATE [UNIQUE] INDEX 인덱스명 ON 테이블명(컬럼명1, 컬럼명2, ...);

ALTER INDEX

• 인덱스를 수정하는 명령어
• BUT, 기존 인덱스를 삭제하고 신규 인덱스를 생성하는 방식으로 사용 권고

ALTER [UNIQUE] INDEX 인덱스명 ON 테이블명(컬럼명1, 컬럼명2, ...);

DROP INDEX

• 인덱스를 삭제하는 명령어

DROP INDEX 인덱스명;

[3-1] 데이터 조작어(DML; Data Manipulation Language)

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

[3-2] DML 명령어

SELECT

• 테이블 내 컬럼에 저장된 데이터 조회

INSERT

• 테이블 내 컬럼에 데이터 추가

UPDATE

• 테이블 내 컬럼에 저장된 데이터 수정

DELETE

• 테이블 내 컬럼에 저장된 데이터 삭제

[3-3] SELECT 명령어

SELECT

• 테이블에서 출력할 컬럼을 명시하기 위해 사용

ALL	모든 튜플을 검색 할 때 사용
DISTINCT	중복된 속성이 조회될 경우 그중 한 개만 검색

FROM

• 질의에 의해 검색될 데이터를 포함하는 테이블명 기술

WHERE

• 검색할 조건을 기술
• WHERE절 문법
  • 비교
    • =
    • <>, !=
    • <, <=, >, >=
  • 범위
    • BETWEEN
  • 집합
    • IN
    • NOT IN
  • 패턴
    

    LIKE%	0개 이상 문자열과 일치
    [ ]	1개 문자와 일치
    [^]	1개의 문자와 불일치
    _	특정 위치의 1개의 문자와 일치

  • NULL
    • IS NULL
    • IS NOT NULL
  • 복합조건
    • AND
    • OR
    • NOT

GROUP BY

• 속성값을 그룹으로 분류하고자 할 때 사용

HAVING

• GROUP BY에 의해 분류한 후그룹에 대한 조건 지정

ORDER BY

• 속성값을 정렬하고자 할 때 사용(ASC : 오름차순, DESC : 내림차순)

조인(Join)

• 두 개 이상의 테이블을 연결하여 데이터를 검색하는 방법
• 논리적 조인 유형
  • 내부 조인
  • 외부 조인
    

    왼쪽 외부 조인	왼쪽 테이블의 모든 데이터와 오른쪽 테이블의 동일 데이터 추출
    오른쪽 외부 조인	오른쪽 테이블의 모든 데이터와 왼쪽 테이블의 동일 데이터 추출
    완전 외부 조인	양쪽의 모든 데이터 추출

  • 교차 조인
  • 셀프 조인

서브쿼리(Sub-Query)

• SQL문 안에 포함된 또 다른 SQL문
• 알려지지 않은 기준을 위한 검색을 위해 사용
• 서브쿼리 유형
  • FROM절 서브쿼리
  • WHERE절 서브쿼리

집합 연산자(Set Operator)

• 테이블을 집합 개념으로 보고, 두 테이블 연산에 집합 연산자를 사용하는 방식
• 집합 연산자 유형
  • UNION : 중복 레코드를 제외
  • UNION ALL : 중복 레코드도 허용
  • INTERSECT : 중복 레코드만 포함
  • MINUS : 비교 레코드 제외(첫 쿼리에 있고 두 번째 쿼리에는 없는 결과 반환)

[3-4] INSERT 명령어

• 데이터의 내용을 삽입할 때 사용하는 명령어

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

[3-5] UPDATE 명령어

• 데이터의 내용을 변경할 때 사용하는 명령어

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

[3-6] DELETE 명령어

• 데이터의 내용을 삭제할 때 사용하는 명령어

DELETE FROM 테이블명
 WHERE 조건;

[3-7] 데이터 제어어(DCL; Data Control Language)

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

GRANT

• 사용 권한 부여

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

REVOKE

• 사용 권한 취소

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

2. 응용 SQL 작성하기

[1-1]데이터 분석 함수의 개념

데이터 분석 함수

다중 행 연산자

• IN : 리턴되는 값 중에서 조건에 해당하는 값이 있으면 참
• ANY : 서브쿼리에 의해 리턴되는 각각의 값과 조건을 비교하여 하나 이상을 만족하면 참
• ALL : 값을 서브쿼리에 의해 리턴되는 모든 값과 조건 값을 비교하여 모든 값을 만족해야만 참
• EXIST : 메인 뭐링의 비교 조건이 서브쿼리의 결과 중에서 만족하는 값이 하나라도 존재하면 참

[2-1] 집계함수

• COUNT
• SUM
• AVG
• MAX
• MIN
• STDDEV
• VARIANCE

[2-2] 그룹함수

• ROLLUP
• CUBE
• GROUPING SETS

[2-3] 윈도함수

• 순위 함수
  • RANK
  • DENSE_RANK
  • ROW_NUMBER