[4일차] 정보처리기사 실기 7장-SQL 응용

교재 참고

오늘은 4일 차 SQL 응용 파트입니다. 오늘도 열심히 정리해 보겠습니다.

Chap01. 데이터베이스 기본

트랜잭션

트랜잭션의 개념

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

트랜잭션의 특성

특성	설명	주요기법
원자성(Atomtcity)	트랜잭션의 연산 전체가 성공 또는 실패 되어야 하는 성질	Commit/Rollback
일관성(Consistency)	시스템이 가지고 있는 고정요소는 트랜잭션 수행 전과 수행 완료 후의 상태가 같아야 하는 성질	무결성 제약 조건
고립성(지속성)(Isolation)	동시에 실행되는 트랜잭션들이 서로 영향을 미치지 않아야 한다는 성질	고립화 수준
영속성(Durability)	성공이 완료된 트랜잭션의 결과는 영속적으로 데이터베이스에 저장되어야 하는 성질	회복 기법

트랜잭션 제어

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

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

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

종류

기법	설명
로킹(Locking)	하나의 트랜잭션을 실행하는 동안 특정 데이터 항목에 대해서 다른 트랜잭션이 동시에 접근하지 못하도록 상호배제 기능을 제공하는 기법
낙관적 검증	트랜잭션이 어떠한 검증도 수행하지 않고 일단 트랜잭션을 수행하고, 트랜잭션 종료 시 검증을 수행하여 데이터베이스에 반영하는 기법
타임 스탬프 순서	트랜잭션이 실행을 시작하기 전에 타임 스탬프를 부여하여 부여된 시간에 따라 트랜잭션 작업을 수행하는 기법
다중버전 동시성 제어	트랜잭션의 타임스탬프와 접근하려는 데이터의 타임스탬프를 비교하여 직렬가능성이 보장되는 적절한 버전을 선택하여 접근하도록 하는 기법
2PC	데이터베이스 동시성 제어 기술 중 하나로, 여러 개의 분산 데이터베이스 시스템에서 트랜잭션의 일관성을 유지하기 위한 기법 - 트랜잭션 두 단계로 분리하여 제어 -  1단계 (준비 단계): 트랜잭션 수행 결과를 다른 분산 시스템에 알리는 과정 2단계 (커밋 단계):    - 모든 분산 시스템에서 트랜잭션 수행 결과가 일치하는지 확인하는 과정   - 모든 분산 시스템이 트랜잭션을 성공적으로 수행했다면, 커밋을 수행 그렇지 않는다면 롤백을 수정

데이터베이스 고립화 수준

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

회복 기법

Redo
- 데이터베이스 내용 자체가 손상된 경우, 가장 최근의 복제본을 적재한 후 이후 일어난 변경만을 로그 이용하여 재실생함으로써
  데이터베이스를 복원하는 기법

Undo
- 데이터베이스 내용 자체를 손상되지 않았지만, 변경 중이거나 변경된 내용에 대한 신뢰성을 읽어버린 경우, 모든 변경 내용을
  취소하여 복원하는 기법

DDL

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

구성	설명
외부 스키마(External Schema)	사용자나 개발자의 관점에서 필요로 하는 데이터베이스의 논리적인 구조
개념 스키마(Conceptual Schema)	개체 간의 관계, 제약조건, 접근 권한, 무결성, 보안에 대해 정의
내부 스키마(Internal Schema)	물리적 저장 장치의 관점에서 보는 데이터베이스 구조

DDL 명령어
구분	DDL 명령어	설명
생성	CREATE	데이터베이스 오브젝트 생성하는 명령어
수정	ALTER	데이터베이스 오브젝트 변경하는 명령어
삭제	DROP	데이터베이스 오브젝트 삭제하는 명령어
	TRUNCATE	데이터베이스 오브젝트 내용 삭제하는 명령어

DML

DML의 유형
유형	조작	설명
SELECT	조회	테이블 내 컬럼에 저장된 데이터를 조회
INSERT	삽입	테이블 내 칼럼에 데이터를 추가
UPDATE	갱신	테이블 내 칼럼에 저장된 데이터를 수정
DELETE	삭제	테이블 내 컬럼에 저장된 데이터를 삭제

DCL

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

Chap02. 응용 SQL

집계성 SQL

다중 행 연산자

연산자	설명
IN	리턴되는 값 중에서 조건에 해당하는 값이 있으면 참
ANY	서브쿼리에 의해 리턴되는 각각의 값과 조건을 비교하여 하나 이상을 만족하면 참
ALL	값을 서브쿼리에 의해 리턴괴는 모든 값과 조건 값을 비교하여 모든 값을 만족해야만 참
EXISTS	메인 쿼리의 비교 조건이 서브쿼리의 결과 중에서 만족하는 값이 하나라도 존재하면 참

집계 함수

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

Chap03. SQL 활용 및 최적화

절차형 SQL

절차형 SQL 개념

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

절차형 SQL 종류

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

SQL 최적화

옵티마이저

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