[정처기 실기_남은 2주 동안 복습] SQL응용
트랜잭션
: 데이터베이스 시스템에서 하나의 논리적 기능을 정상적으로 수행하기 위한 작업의 기본 단위
트랜잭선의 특성
- 원자성 (Atomicity) : 하나라도 실패할 경우 전체가 취소되어야하는 특성
- 일관성 (Consistency) : 트랜잭션이 실행 성공 후 항상 일관된 데이터베이스 상태를 보존해야하는 특성
- 격리성 (Isolation) : 트랜잭션 실행 중 생성하는 연산의 중간 결과를 다른 트랜잭션이 접근 불가한 특성
- 영속성 (Durability) : 성공이 완료된 트랜잭션의 결과는 영속적으로 데이터베이스에 저장하는 특성
트랜잭션의 상태 변화
- 활성 (Active) : 초기 상태, 트랜잭션이 실행 중일 때 가지는 상태
- 부분 완료 상태 (Partially Committed) : 마지막 명령문이 실행된 후에 가지는 상태
- 완료 상태 (Committed) : 트랜잭션이 성공적으로 완료된 후 가지는 상태
- 실패 상태 (Failed) : 정상적인 실행이 더 이상 진행될 수 없을 때 가지는 상태
- 철회 상태 (Aborted) : 트랜잭션이 취소되고 데이터베이스가 트랜잭션 시작 전 상태로 환원된 상태
TCL(트랜잭션 제어언어) 명령어
- COMMIT. : 트랜잭션을 메모리에 영구적으로 저장하는 명령어
- ROLLBACK : 트랜잭션 내역을 저장 무효화시키는 명령어
- CHECKPOINT : 롤백을 위한 시점을 지정하는 명령어
병행제어
: 다수 사용자 환경에서 여러 트랜잭션을 수행할 때 데이터베이스 일관성 유지를 위해 상호작용을 제어하는 기법
병행제어 미보장 시 문제점
- 갱신 손실 (Lost Update) : 먼저 실행된 트랜잭션의 결과를 나중에 실행된 트랜잭션이 덮어쓸 때 발생하는 오류
- 현황 파악오류 (Dirty Read) : 트랜잭션의 중간 수행 결과를 다른 트랜잭션이 참조하여 발생하는 오류
- 모순성 (Inconsistency) : 두 트랜잭션이 동시에 실행되어 데이터베이스의 일관성이 결여되는 오류
- 연쇄복귀 (Cascading Rollback) : 복수의 트랜잭션이 데이터 공유 시 특정 트랜잭션이 처리를 취소할 경우 트랜잭션이 처리한 곳의 부분을 취소하지 못하는 오류
병행제어 기법 종류
- 로킹 (Locking) : DB의 일관성과 무결성을 유지하기 위해 트랜잭션의 순차적 진행을 보장하는 직렬화 기법
- 낙관적 검증 : 트랜잭션이 어떠한 검증도 수행하지 않고 일단 트랜잭션을 수행하고, 트랜잭션 종료 시 검증을 수행하여 데이터베이스에 반영하는 기법
- 타임 스탬프 순서 (Time Stamp Ordering) : 트랜잭션과 트랜잭션이 읽거나 갱신한 데이터에 대해 트랜잭션이 실행을 시작하기 전 타임 스탬프를 부여하여 부여된 시간에 따라 트랜잭션 작업을 수행하는 기법
- 다중버전 동시성 제어 (MVCC) : 트랜잭션의 타임스탬프와 접근하려는 데이터의 타임스탬프를 비교하여 직렬가능성이 보장되는 적절한 버전을 선택하여 접근하도록 하는 기법
회복기법(Recovery)
: 트랜잭션을 수행하는 도중 장애로 인해 손상된 데이터베이스를 손상되기 이전의 정상적인 상태로 복구시키는 작업
회복기법 종류
- 로그 기반 회복 기법
- 지연 갱신 회복 기법 (Deferred Update) : 트랜잭션이 완료되기 전까지 데이터베이스에 기록하지 않는 기법
- 즉각 갱신 회복 기법 (Immediate Update) : 트랜잭션 수행 중 갱신 결과를 바로 DB에 반영하는 기법
- 체크 포인트 회복 기법 (Checkpoint Recovery) : 장애 발생 시 검사점 이후에 처리된 트랜잭션에 대해서만 장애 발생 이전의 상태로 복원시키는 회복 기법
- 그림자 페이징 회복 기법 (Shadow Paging Recovery) : 데이터베이스 트랜잭션 수행 시 복제본을 생성하여 데이터베이스 장애 시 이를 이용해 복구하는 기법
DDL (데이터 정의어)
: 테이블과 같은 데이터 구조를 정의하는 데 사용되는 명령어들로 특정 구조를 생성, 변경, 삭제, 이름을 바꾸는 데이터 구조와 관련된 명령어들을 데이터 정의어라고 부름
DDL 대상
- 도메인
- 스키마 : 데이터베이스의 구조, 제약조건 등의 정보를 담고 있는 기본적인 구조
- 외부 스키마 : 사용자나 개발자의 관점에서 필요로 하는 데이터베이스의 논리적 구조
- 개념 스키마 : 데이터베이스의 전체적인 논리적 구조
- 내부 스키마 : 물리적 저장장치의 관점에서 보는 데이터베이스 구조
- 테이블
- 뷰
- 인덱스
DDL 명령어
- CREATE : 생성
- ALTER : 변경
- DROP : 삭제
- TRUNCATE : 내용 삭제
TABLE 관련 DDL
CREATE TABLE 사원(
사번 VARCHAR(10) PRIMARY KEY, — 기본키 설정
업무 VARCHAR(20) FOREIGN KEY REFERENCES 참조테이블(기본키), — 외래키 설정
이름 VARCHAR(10) UNIQUE, — 테이블 내에서 얻은 유일한 값을 갖도록 하는 제약조건
생년월일 CHAR(8) NOT NULL, — 널 값을 포함하지 않도록 하는 제약조건
성별 CHAR(1) CHECK(성별 = ‘M’ OR 성별 = ‘F’), — 개발자가 정의하는 제약조건
입사일 DATE DEFAULT SYSDATE — 값을 넣지 않는 경우 기본값으로 설정
);
ALTER TABLE
ALTER TABLE 사원 ADD 전화번호 VARCHAR(11) UNIQUE;
ALTER TABLE 사원 MODIFY 이름 VARCHAR(10) NOT NULL;
ALTER TABLE 사원 DROP 생년월일;
DROP DABLE
DROP TABLE 사원 [CASCADE : RESTRICT];
TRUNCATE TABLE
TRUNCATE TABLE 사원;
VIEW
CREATE VIEW 사원뷰 AS
SELECT 사번, 이름
FROM 사원
WHERE 성별 = 'M';
INDEX
CREATE INDEX 사번인덱스 ON 사원(사번);
ALTER INDEX 사번인덱스 ON 사원(사번);
DROP INDEX 사번인덱스;
DML (데이터 조작어)
: 데이터베이스에 저장된 자료들을 입력, 수정, 삭제, 조회하는 언어
DML 명령어
- SELECT
- INSERT
- UPDATE
- DELETE
SELECT
- SELECT 절
- ALL : 모든 튜플 검색할 때 사용
- DISTINCT : 중복된 속성이 조회될 경우 그중 한개만 검색
- FROM 절 : 질의에 의해 검색 될 데이터들을 포함하는 테이블명을 기술
- WHERE 절 : 검색할 조건을 기술
- GROUP BY 절 : 속성값을 그룹으로 분류하고자 할 때 사용
- ORDER BY 절 : 속성값을 정렬하고자 할때 사용
- ASC : 오름차순
- DESC : 내림차순
JOIN
SELECT A.책번호, A.책명, B.가격
FROM 도서 A JOIN 도서가격 B
ON A.책번호 = B.책번호;