정보처리기사 7단원(SQL 활용) 요약

기출문제가 출제된 부분은 배경색으로 표시해 두었습니다

목차

1. 데이터베이스 기본
2. 응용 SQL 작성하기
3. 절차형 SQL 활용하기
4. 데이터 조작 프로시저 최적화

 

데이터베이스 기본

---

트랜잭션

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

 

트랜잭션의 특성

💡 암기 TIP
Atomicity, Consistency, Isolation, Durability → ACID

특성	설명
원자성(Atomicity)	분해가 불가능한 작업의 최소단위, 연산 전체가 성공 또는 실패
일관성(Consistency)	트랜잭션이 실행 성공 후 항상 일관된 DB 상태를 보존
격리성(Isolation)	트랜잭션 실행 중 생성하는 연산의 중간 결과를 다른 트랜잭션이 접근 불가
영속성(Durability)	성공이 완료된 트랜잭션의 결과는 영속적으로 DB에 저장

 

트랜잭션의 상태 변화

트랜잭션 상태 전이도

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

 

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

트랜잭션의 결과를 허용하거나 취소하는 목적으로 사용

💡 암기 TIP
커밋, 롤백, 체크포인트 → 커롤체

명령어	설명
커밋(Commit)	트랜잭션을 메모리에 영구적으로 저장
롤백(Rollback)	트랜잭션 내역을 저장 무효화
체크포인트(Checkpoint)	Rollback을 위한 시점을 지정

 

병행 제어(Concurrency Control, 일관성 주요 기법)

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

💡 암기 TIP
로킹, 낙관적, 타임 스탬프, 다중버전 → 로 낙타다!

병행 제어 기법	설명
로킹(Locking)	일관성과 무결성을 유지하기 위해 트랜잭션의 순차적 진행을 보장하는 직렬화 기법
낙관적 검증	일단 트랜잭션을 수행하고, 종료 후 검증
타임 스탬프 순서 (Time Stamp ordering)	트랜잭션이 실행을 시작하기 전에 타임 스탬프를 부여해 부여된 시간에 따라 수행
다중버전 동시성 제어 (MVCC: Multi Version Concurrency Control)	타임 스탬프를 비교하여 직렬가능성이 보장되는 적절한 버전을 선택하여 접근

 

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

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

고립화 수준	설명
Read Uncommitted	한 트랜잭션에서 연산 중인 데이터를 다른 트랜잭션이 읽는 것을 허용
Read Committed	한 트랜잭션의 연산이 완료(Commit)된 후 데이터 읽기 가능
Repeatable Read	트랜잭션 종료 시까지 해당 데이터에 대한 갱신/삭제 제한
Serializable Read	특정 데이터 영역을 순차적으로 읽을 때, 해당 데이터 영역 전체에 대한 접근 제어

 

회복 기법(영속성 주요 기법, Recovery)

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

기법	설명
로그 기반 회복 기법 [지연 갱신 회복 기법]	트랜잭션이 완료된 후 DB에 기록
로그 기반 회복 기법 [즉각 갱신 회복 기법]	트랜잭션 수행 중 갱신결과를 바로 DB에 반영
체크 포인트 회복 기법	장애 발생 시 검사점(체크포인트) 이후에 처리된 트랜잭션만 복원
그림자 페이징 회복 기법	트랜잭션 수행 시 복제본을 생성하여 장애 시 이를 이용해 복구

 

DDL(데이터 정의어, Data Definition Language)

데이터를 정의하는 언어로, 특정 구조를 생성, 변경, 삭제, 이름 변경하는 데이터 구조와 관련된 명령어

 

DDL의 대상

💡 암기 TIP
도메인, 스키마, 테이블, 뷰, 인덱스 → 도스테 뷰인 (도스토옙스키 부인)

대상	설명
도메인(Domain)	하나의 속성이 가질 수 있는 원자값들의 집합
스키마(Schema)	데이터베이스의 구조, 제약조건, 정보를 담고 있는 기본적인 구조
테이블(Table)	데이터 저장 공간
뷰(View)	하나 이상의 물리 테이블에서 유도되는 가상의 테이블
인덱스(Index)	검색을 빠르게 하기 위한 데이터 구조

스키마의 종류

1. 외부(External) 스키마: 사용자나 개발자 관점에서 필요로 하는 DB의 논리적 구조
2. 개념(Conceptual) 스키마: DB의 전체적인 논리적 구조
3. 내부(Internal) 스키마: 물리적 저장장치의 관점에서 보는 DB 구조

 

DDL 명령어

💡 암기 TIP
CREATE, ALTER, DROP, TRUNCATE → 크리 알 드 트러(크리스마스 계란 두 트럭)

명령어	구분	설명	예시
CREATE	생성	DB 오브젝트 생성	CREATE TABLE 테이블명
ALTER	수정	DB 오브젝트 수정	ALTER TABLE 테이블명
DROP	삭제	DB 오브젝트 삭제	DROP TABLE 테이블명
TRUNCATE		DB 오브젝트 내용 삭제	TRUNCATE TABLE 테이블명

CREATE TABLE 제약조건

1. PRIMARY KEY: 기본키 지정
2. FOREIGN KEY: 외래키 지정
3. UNIQUE: 유일한 값을 갖도록 지정
4. NOT NULL: NULL 값을 갖지 않도록 지정
5. CHECK: 개발자가 정의하는 제약 조건
6. DEFAULT: 데이터를 INSERT할 때 해당 컬럼의 값을 갖지 않는 경우 기본값으로 설정

ALTER TABLE 제약조건

1. ADD 컬럼명: 컬럼 추가
2. MODIFY 컬럼명: 컬럼 수정
3. DROP 컬럼명: 컬럼 삭제

DROP TABLE 제약조건 - 외래 키가 걸려있는 경우

1. CASCADE: 참조하는 테이블까지 연쇄적으로 제거
2. RESTRICT: 다른 테이블이 삭제할 테이블을 참조 중이면 제거하지 않음

 

DML(데이터 조작어, Data Manipulation Language)

DB에 저장된 자료들을 입력, 수정, 삭제, 조회하는 언어

💡 암기 TIP
SELECT, INSERT, UPDATE, DELETE → 세인 업데 (세인이 집에 없대)

명령어	구분	설명	예시
SELECT	조회	테이블 내 컬럼에 저장된 데이터 조회	SELECT 컬럼명 FROM 테이블명
INSERT	삽입	테이블 내 컬럼에 데이터 삽입	INSERT INTO 테이블명 VALUES 데이터
UPDATE	갱신	테이블 내 컬럼에 저장된 데이터 갱신	UPDATE 테이블명 SET 속성 WHERE 조건
DELETE	삭제	테이블 내 컬럼에 저장된 데이터 삭제	DELETE FROM 테이블명 WHERE 조건

SELECT 명령어

명령어	설명
ALL	모든 튜플 검색(기본값)
DISTINCT	중복된 속성이 조회될 경우 그 중 한 개만 검색
FROM	검색될 데이터를 포함하는 테이블 명시
WHERE	검색할 조건 기술
GROUP BY	속성값들을 그룹으로 분류하고자 할 때 사용
HAVING	GROUP BY로 분류한 후 그룹 조건 지정
ORDER BY	속성값을 정렬하고자 할 때 (ASC: 오름차순, DESC: 내림차순)

WHERE절 문법

명령어	설명	예시
BETWEEN	값1보다 크거나 같고, 값2보다 작거나 같은 데이터	컬럼 BETWEEN 값1 AND 값2
IN	IN 안에 포함된 경우 조회	컬럼 IN (값1, 값2, ..., )
NOT IN	IN 안에 포함되어 있지 않은 경우	컬럼 NOT IN (값1, 값2, ..., )
LIKE	컬럼이 패턴에 포함된 경우 데이터 조회 %: 0개 이상의 문자열과 일치 [ ]: 1개의 문자와 일치 [^]: 1개의 문자와 불일치 _: 특정 위치의 1개의 문자와 일치	컬럼 LIKE 패턴 NAME LIKE '정보%'(정보로 시작하는 이름) NAME LIKE '[ABCD]%'(A,B,C,D로 시작하는 문자열)

집합 연산자(Set Operator)

테이블을 집합 개념으로 보고, 두 테이블 연산에 집합 연산자를 사용

집합 연산자	설명
UNION	중복 행이 제거된 쿼리 결과를 반환하는 합집합
UNION ALL	중복 행도 포함한 쿼리 결과를 반환하는 합집합
INTERSECT	공통적으로 존재하는 결과를 반환하는 교집합
MINUS	첫 쿼리에 있고 두 번째 쿼리에는 없는 결과를 반환하는 차집합

 

DCL(데이터 제어어, Data Control Language)

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

💡 암기 TIP
GRANT ON TO → 그온투
REVOKE ON FROM → 리온프

명령어	설명
GRANT 권한 ON 테이블 TO 사용자	사용자에게 DB에 대한 권한을 부여
REVOKE 권한 ON 테이블 FROM 사용자	사용자에게 부여했던 DB에 대한 권한을 회수

 

 

응용 SQL 작성하기

---

데이터 분석 함수

데이터 분석을 위해 복수 행 기준의 데이터를 모아 처리하는 함수

함수 종류	설명
집계 함수	여러 행으로부터 하나의 결과 값을 반환
그룹 함수	소그룹 간의 소계 및 중계 등의 중간 합계 분석 데이터 산출
윈도 함수	DB를 사용한 온라인 분석 처리 용도로 사용하기 위해 표준 SQL에 추가 (OLAP, OnLine Analytical Processing 의사결정 지원 함수라고도 불림)

 

그룹함수

그룹화하여 그룹별로 결과를 출력하는 함수

그룹 함수 유형	설명
ROLLUP	지정된 컬럼은 소계 등 중간 집계 값을 산출하기 위한 함수
CUBE	결합 가능한 모든 값에 대해 다차원 집계를 생성 (연산이 많아 부담)
GROUPING SETS	컬럼 간 순서와 무관한 결과를 얻을 수 있음

 

 

절차형 SQL 활용하기

---

절차형 SQL

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

 

프로시저(Procedure)

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

💡 암기 TIP
DECLARE, BEGIN/END, CONTROL, SQL, EXCEPTION, TRANSACTION → DB 컨닝 SET

구성요소	설명
선언부(DECLARE)	명칭, 변수, 데이터 타입 정의
시작/종료부(BEGIN/END)	프로시저의 시작과 종료를 표현
제어부(CONTROL)	조건문과 반복문 이용하여 문장 처리
SQL	DML을 주로 사용
예외부(EXCEPTION)	SQL문이 실행될 때 예외 발생 시 예외 처리 방법 정의
실행부(TRANSACTION)	프로시저에서 수행된 DML 수행 내역의 DBMS 적용 또는 취소 여부 결정

 

사용자 정의 함수

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

💡 암기 TIP
DB컨 SE + RETURN(반환부 추가) → DB 컨설팅

 

트리거(Trigger)

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

💡 암기 TIP
D, EVENT, B, CON, SE → DEB CON SE (디이비 컨닝 세트)

 

 

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

---

옵티마이저(Optimizer)

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

용어	설명
규칙기반 옵티마이저(RBO) (Rule Based Optimizer)	통계 정보가 없는 상태에서 사전 등록된 규칙에 따라 쿼리 실행계획을 선택
비용기반 옵티마이저(CBO) (Cost Based Optimizer)	통계 정보로부터 모든 접근 경로를 고려한 쿼리 실행 계획을 선택

 

SQL 힌트

실행하려는 SQL 문에 사전에 정보를 주어 SQL 문 실행에 빠른 결과를 가져오는 효과를 만드는 문법

1. SQL 성능 개선의 핵심 부분으로 옵티마이저의 실행 계획을 원하는대로 변경 가능
2. 옵티마이저가 항상 최선의 실행 계획을 수립할 수 없어 명시적인 힌트를 통해 실행계획 변경