chapter 07. SQL 응용

트랜잭션

트랜잭션(Transaction)의 개념

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

 

트랜잭션의 특성 [2020년 1회, 2021년 2회]

특성	설명	주요기법
원자성 (Atomicity)	- 트랜잭션을 구성하는 연산 전체가 모두 정상적으로 실행되거나 모두 취소되어야 하는 성질 - 트랜잭션의 연산 전체가 성공 또는 실패(All or Nothing)되어야 하는 성질	- Commit / Rollback - 회복성 보장
일관성 (Consistency)	- 시스템이 가지고 있는 고정요소는 트랜잭션 수행 전과 트랜잭션 수행 완료 후의 상태가 같아야 하는 성질	- 무결성 제약조건 - 동시적 제어
격리성=고립성 (Isolation)	- 동시에 실행되는 트랜잭션들이 서로 영향을 미치지 않아야 한다는 성질	- Read Uncommitted - Read Committed - Repeatable Read - Serializable
영속성 (Durability)	- 성공이 완료된 트랜잭션의 결과는 영속적으로 데이터베이스에 저장되어야 하는 성질	- 회복 기법

 

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

① 병행 제어(Concurrency Control) 개념

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

 

② 병행 제어의 목적

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

 

③ 병행 제어 미보장 시 문제점

병행 제어 미보장 시 문제점은 갱신 손실, 현황 파악오류, 모순성, 연쇄복귀가 있다.

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

 

④ 병행 제어 기법의 종류 [2021년 2회]

병행 제어 기법에는 로킹, 타임 스탬프 순서가 있다.

기법	설명
로킹 (Locking)	- 같은 자원을 액세스하는 다중 트랜잭션 환경에서 DB의 일관성과 무결성을 유지하기 위해 트랜잭션의 순차적 진행을 보장하는 직렬화 기법 - 로킹의 특징은 다음과 같음    =데이터 베이스, 파일, 레코드 등은 로킹 단위가 될 수 없음    =로킹 단위가 작아지면 데이터베이스 공유도가 증가    =로킹 단위가 작아지면 로킹 오버헤드가 증가    =한꺼번에 로킹할 수 있는 객체의 크기를 로킹 단위라고 함
낙관적 검증 (Optimistic Validation)	- 트랜잭션이 어떠한 검증도 수행하지 않고 일단 트랜잭션을 수행하고, 트랜잭션 종료 시 검증을 수행하여 데이터베이스에 반영하는 기법
타임 스탬프 순서 (Time Stamp Ordering)	- 트랜잭션과 트랜잭션이 읽거나 갱신한 데이터에 대해 트랜잭션이 실행을 시작하기 전에 타음 스탬프(Time Stamp)를 부여하여 부여된 시간에 따라 트랜잭션 작업을 수행하는 기법
다중버전 동시성 제어 (MVCC; Mutli Version Concurrency Control)	- 트랜잭션의 타임스탬프와 접근하려는 데이터의 타임 스탬프를 비교하여 직렬 가능성이 보장되는 적절한 버전을 선택하여 접근하도록 하는 기법

 

 

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

회복 기법 종류 [2020년 4회]

회복 기법 종류에는 로그 기반 회복 기법인 지연 갱신 회복 기법, 즉각(즉시) 갱신 회복 기법, 체크 포인트 회복 기법, 그림자 페이징 회복 기법이 있다.

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

 

 

DDL

데이터 정의어(DDL; Data Definition Language)의 개념

• 데이터 정의어는 데이터를 정의하는 언어로서 '데이터를 담는 그릇을 정의하는 언어'이다.
• 테이블과 같은 데이터 구조를 정의하는 데 사용되는 명령어들로 특정 구조를 생성, 변경, 삭제, 이름을 바꾸는 데이터 구조와 관련된 명령어들을 데이터 정의어라고 부른다.

DDL의 대상

DDL의 대상	설명
도메인 (Domain)	- 하나의 속성이 가질 수 있는 원자값들의 집합 - 속성의 데이터 타입과 크기, 제약조건 등의 정보
스키마 (Shema)	- 데이터베이스의 구조, 제약조건 등의 정보를 담고 있는 기본적인 구조 - 스키마는 외부/개념/내부 3계층으로 있음
테이블(Table)	데이터 저장 공간
뷰(View)	하나 이상의 물리 테이블에서 유도되는 가상의 테이블
인덱스(Index)	검색을 빠르게 하기 위한 데이터 구조

인덱스(Index)의 개념 [2021년 3회]

• 인덱스는 검색 연산의 최적화를 위해 데이터베이스 내 값에 대한 주소 정보로 구성된 데이터 구조이다.
• 인덱스는 데이터를 빠르게 찾을 수 있는 수단으로서, 테이블에 대한 조회 속도를 높여 주는 자료 구조이다.
• 인덱스는 테이블의 특정 레코드 위치를 알려주는 용도로 사용

 

 

DML

SELECT(데이터 조회) 명령

① SELECT 명령어 개념

• SELECT 명령어는 데이터의 내용을 조회할 때 사용하는 명령어이다.
• SELECT 명령어는 SELECT 절, FROM 절, WHERE 절, GROUP BY 절, HAVING 절, ORDER BY 절로 구성된다.

구분	설명
SELECT 절	- 검색하고자 하는 속성명, 계산식을 기술 - 속성명 별칭은 AS를 사용하며 생략 가능함 - 2개 이상의 테이블을 대상으로 검색할 때는 '테이블명,속성명'으로 표현 - 술어 부분은 ALL이 기본값
FROM 절	질의에 의해 검색될 데이터들을 포함하는 테이블명을 기술
WHERE 절	검색할 조건을 기술
GROUP BY 절	속성값을 그룹으로 분류하고자 할 때 사용
HAVING 절	GROUP BY에 의해 분류한 후 그룹에 대한 조건 지정
ORDER BY 절	속성값을 정렬하고자 할 때(ASC: 오름차순, DESC: 내림차순, ASC, DESC 키워드 생략 시 오른차순 정렬)사용

 

㉮ SELECT 절 [2021년 1회]

• SELECT 절은 테이블에서 출력할 컬럼을 명시하기 위해 사용한다.

㉯ WHERE 절 [2020년 2회, 2021년 2회]

• WHERE 절 조건에서는 비교, 범위, 집합, 패턴, NULL, 복합조건이 있다.

㉰ GROUP BY 절

• GROUP BY 절은 속성값을 그룹으로 분류하고자 할 때 사용한다.

㉱ HAVING 절

• HAVING 절은 GROUP BY에 의해 분류한 후 그룹에 대한 조건을 지정할 때 사용한다.

㉲ ORDER BY 절

• ORDER BY 절은 속성값을 정렬하고자 할 때 사용한다.

 

② 조인(Join) [2021년 2회]

㉮ 조인 개념

• 조인은 두 개 이상의 테이블을 연결하여 데이터를 검색하는 방법이다.
• 두 릴레이션으로부터 관련된 튜플들을 결합하여 하나의 튜플로 만드는 가장 대표적인 데이터 연결 방법이다.

㉯ 내부 조인

SELECT A.컬럼1, A.컬럼2, ..., B.컬럼1,B.컬럼2, ...
    FROM 테이블1 A [INNER] JOIN 테이블2 B ON 조인조건
[WHERE 검색조건];

• 같은 이름의 컬럼이 여러 테이블에 있을 경우 '별칭.컬럼명' 형태로 명시
• INNER라는 키워드는 생략해도 내부 조인이 됨
• 검색조건을 추가할 경우 조인된 값에서 해당 조건에 맞는 결과만 출력되도록 설정

㉰ 왼쪽 외부 조인

SELECT A.컬럼1, A.컬럼2, ..., B.컬럼1,B.컬럼2, ...
    FROM 테이블1 A LEFT [OUTER] JOIN 테이블2 B ON 조인조건
[WHERE 검색조건];

• OUTER라는 키워드는 생략해도 왼쪽 외부 조인이 됨
• 검색조건을 추가할 경우 조인된 값에서 해당 조건에 맞는 결과만 출력되도록 설정

㉱ 오른쪽 외부 조인

SELECT A.컬럼1, A.컬럼2, ..., B.컬럼1,B.컬럼2, ...
    FROM 테이블1 A RIGHT [OUTER] JOIN 테이블2 B ON 조인조건
[WHERE 검색조건];

 

㉲ 완전 외부 조인

SELECT A.컬럼1, A.컬럼2, ..., B.컬럼1,B.컬럼2, ...
    FROM 테이블1 A FULL [OUTER] JOIN 테이블2 B ON 조인조건
[WHERE 검색조건];

 

㉳ 교차 조인

SELECT 컬럼1, 컬럼2, ...
    FROM 테이블1 CROSS JOIN 테이블2

• 조인 조건이 없는 모든 데이터 조합을 추출하기 때문에 ON절이 없음

 

㉴ 셀프 조인

SELECT A.컬럼1, A.컬럼2, ..., B.컬럼1,B.컬럼2, ...
    FROM 테이블1 A [INNER] JOIN 테이블1 B ON 조인조건
[WHERE 검색조건];

• 같은 테이블명을 쓰고 별칭만 A, B와 같이 다르게 함
• 검색조건을 추가할 경우 조인된 값에서 해당 조건에 맞는 결과만 출력되도록 설정

 

UPDATE(데이터 변경) 명령어

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

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

• UPDATE 명령문은 WHERE 절을 통해 어떤 조건이 만족할 경우에만 특정 컬럼의 값을 수정하는 용도로 자주 사용됨

 

DELETE(데이터 삭제) 명령어

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

DELETE FROM 테이블명
    WHERE 조건;

• 모든 레코드를 삭제할 때는 WHERE 절 없이 DELETE만 사용
• 레코드를 삭제해도 테이블 구조는 남아 있어서 디스크에서 테이블을 완전히 삭제하는 DROP 명령과는 다름

 

DCL [2021년 3회]

데이터 제어어(DCL; Date Control Language)의 개념

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

유형	동작	설명
GRANT	사용 권한 부여	관리자(DBA)가 사용자에게 데이터베이스에 대한 권한을 부여하는 명령어
REVOKE	사용 권한 취소	관리자(DBA)가 사용자에게 부여했던 권한을 회수하기 위한 명령어

 

집계 함수

집계 함수의 종류 [2021년 1회]

집계 특성상 숫자 유형의 계산에 사용되는 것은 대다수이나, MAX/MIN 또는 COUNT와 같이 문자열 유형의 최대/최소나 건수 계산 등에도 사용한다.

집계 함수	내용
COUNT	복수 행의 줄 수를 반환하는 함수
SUM	복수 행의 해당 컬럼 간의 합계를 계산하는 함수
AVG	복수 행의 해당 컬럼 간의 평균을 계산하는 함수
MAX	복수 행의 해당 컬럼 중 최댓값을 계산하는 함수
MIN	복수 행의 해당 컬럼 중 최솟값을 계산하는 함수
STDDEV	복수 행의 해당 컬럼 간의 표준편차을 계산하는 함수
VARIAN	복수 행의 해당 컬럼 간의 분산을 계산하는 함수

 

 

[참고 : 수제비 2022]