[정보처리기사 실기] 7. SQL 응용 (1)

📌 트랜잭션

✅ 개념

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

✅ 특성 (ACID)

• 원자성 (Atomicity)
  • 분해가 불가능한 작업의 최소 단위
  • 연산 전체가 성공 또는 실패(All or Nothing)
  • 하나라도 실패할 경우 전체가 취소 되어야 함
  • 회복성 보장
• 일관성 (Consistency)
  • 트랜잭션이 실행 성공 후 항상 일관된 DB 상태 보존
  • 무결성 제약조건, 동시성 제어
• 격리성 (Isolation)
  • 트랜잭션 실행 중 생성하는 연산의 중간 결과를 다른 트랜잭션은 접근 불가
  • Read Uncommited, Read Commited, Repeatable Read, Serializable
• 영속성 (Durability)
  • 성공이 완료된 트랜잭션의 결과는 영속적으로 DB에 저장
  • 회복기법

✅ 상태변화

• 활동 상태 : 초기상태, 트랜잭션이 실행 중인 상태
• 부분 완료 상태 : 마지막 명령문이 실행된 후 가지는 상태
• 완료 상태 : 트랜잭션 성공적 완료 후 가지는 상태
• 실패 상태 : 정상적인 실행 더 이상 진행X 상태
• 철회 상태 : 트랜잭션 취소되고 DB가 트랜잭션 시작 전 상태로 환원

✅ 제어

• TCL : 트랜잭션 제어 언어, 트랜잭션 결과를 허용하거나 취소 목적
  • 커밋 : 확정, 영구적으로 메모리에 저장
  • 롤백 : 취소, 저장 무효화
  • 체크포인트 : 저장 시기 설정, ROLLBACK을 위한 시점 지정

✅ 병행 제어

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

• 목적

  • DB 공유 최대화
  • 시스템 활용도 최대화
  • DB 일관성 유지
  • 응답시간 최소화

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

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

• 기법 종류

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

✅ DB 고립화 수준 (격리성 주요기법)

• 개념 : 다른 트랜잭션이 현재의 데이터에 대한 무결성을 해치지 않기 위해 잠금을 설정하는 정도
• 종류
  • Read Uncommited
    - 한 트랜잭션에서 연산중인 데이터를 다른 트랜잭션이 읽는 것을 허용하는 수준
  • Read Commited
    - 한 트랜잭션에서 연산을 수행할 때 연산이 완료될 때까지 연산 대상 데이터에 대한 읽기를 제한하는 수준
    - 연산이 완료되어 커밋된 데이터는 다른 트랜잭션이 읽는 것을 허용
  • Repeatable Read
    - 선행 트랜잭션이 특정 데이터를 읽을 때, 종료 시까지 해당 데이터에 대한 갱신, 삭제를 제한하는 수준
  • Serializable Read
    - 선행 트랜잭션이 특정 데이터 영역을 순차적으로 읽을 때, 해당 데이터 영역 전체에 대한 접근 제한하는 수준

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

• 개념 : 트랜잭션을 수행하는 도중 장애로 인해 손상된 DB를 손상되기 이전의 정상적인 상태로 복구시키는 작업
• 종류
  • 로그 기반 회복 기법
    - 지연 갱신 회복 기법 : 트랜잭션 완료 전까지 DB에 기록X
    - 즉각 갱신 회복 기법 : 트랜잭션 수행 중 갱신 결과를 바로 DB에 반영
  • 체크 포인트 회복 기법
    - 장애 발생 시 검사점 이후에 처리된 트랜잭션에 대해서만 장애 발생 이전 상태로 복원
  • 그림자 페이징 회복 기법
    - DB 트랜잭션 수행 시 복제본 생성하여 복구
    

📌 DDL

✅ 개념

: 데이터의 구조를 정의하는데 사용, 구조의 생성, 변경, 삭제, 이름 변경

📌 DDL 대상 (도스테뷰인)

✅ 도메인

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

✅ 스키마

• 외부 스키마
  • 사용자나 개발자 관점에서 필요로 하는 DB 논리적 구조
  • 사용자 뷰
  • 서브 스키마
• 개념 스키마
  • DB의 전체적인 논리적 구조
  • 전체적인 뷰
  • 개체 간의 관계, 제약조건, 접근 권한, 무결성, 보안에 대해 정의
• 내부 스키마
  • 물리적 저장장치 관점
  • DB에 저장될 레코드 형식 정의, 저장 데이터 항목의 표현 방법, 내부 레코드의 물리 순서 등 표현

✅ 테이블

• 데이터 저장 공간
• 필드 들로 구성된 데이터의 집합체
• 릴레이션 or 엔터티라고 불림

✅ 뷰

• 물리 테이블에서 유도되는 가상 테이블
• 특징
  • 논리적 데이터 독립성 제공 : DB에 영향을 주지 않고 데이터에 접근
  • 데이터 조작 연산 간소화 : 원하는 형태의 논리적 구조 형성
  • 보안 기능 제공 : 선택되지 않은 필드 조회 및 접근 불가
  • 뷰 변경 불가 : ALTE문을 이용하여 변경 불가
• 목적
  • 사용의 주된 이유는 단순 질의어를 사용할 수 있기 때문
  • 하나의 뷰를 통해 복수의 테이블을 대체하는 단순성
  • 중요 데이터 일부만을 제공할 수 있는 장단점
• 장점
  • 논리적 독립성 제공
  • 사용자 데이터 관리 용이
  • 데이터 보안 용이
• 단점
  • 뷰 자체 인덱스 불가
  • 뷰 정의 변경 불가
  • 데이터 변경 제약 존재

✅ 인덱스

• 검색을 빠르게 하기 위한 데이터 구조
• 기본 키 컬럼은 자동으로 인덱스 생성
• 연원일 or 이름을 기준으로 하는 인덱스는 자동 생성 X
• 컬럼에 인덱스 없는 경우 테이블 전체 내용 검색 (테이블 전체 스캔)
• 인덱스 생성 시 데이터 빠르게 찾을 수 있음 (인덱스 범위 스캔)
• =로 비교되는 컬럼 대상으로 인덱스 생성하면 검색 속도 향상

✔ 인덱스 종류 (순해비함 단결클)

• 순서 인덱스
  • 데이터가 정렬된 순서로 생성
  • B-Tree 알고리즘 활용
• 해시 인덱스
  • 해시 함수에 의해 직접 데이터에 키 값으로 접근
  • 데이터 접근 비용 균일, 튜플 양에 무관
• 비트 인덱스
  • 각 컬럼에 적은 개수 값이 저장된 경우 선택하는 인덱스
  • 수정이 적을 경우 유용(e.g. 생년원일, 상품번호 등)
• 함수형 인덱스
  • 수식이나 함수를 적용
• 단일 인덱스
  • 하나의 컬럼으로만 구성
  • 주 사용 컬럼이 하나일 경우 사용
• 결합 인덱스
  • 두개 이상의 컬럼 구성
  • WHERE조건으로 사용하는 빈도가 높은 경우 사용
• 클러스터드 인덱스
  • 기본 키 기준으로 레코드를 묶어서 저장
  • 물리적 순서에 따라 인덱스 생성
  • 특정 범위 검색시 유리

✔ 인덱스 스캔 방식

• 인덱스 범위 스캔 : 루트 블록부터 리프 블록까지 수직적으로 탐색 후 리프 블록을 필요한 범위만 스캔
• 인덱스 전체 스캔 : 인덱스 리프 블록을 처음부터 끝까지 수평적으로 탐색
• 인덱스 단일 스캔 : 수직적 탐색 만으로 데이터 찾는 스캔 방식
• 인덱스 생략 스캔 : 선두 컬럼이 조건절에 빠졌어도 인덱스를 활용

📌 DDL 명령어

✅ CREATE

• 제약조건
  • PRIMARY KEY : 기본키, Unique & not null
  • FOREIGN KEY : 외래키
  • UNIQUE : 유일한 값을 갖도록 하는 제약 조건
  • NOT NULL : Null 값 허용 X
  • CHECK : 개발자가 정의, 참이어야 하는 조건 지정
  • DEFAULT : INSERT시 해당 컬럼의 값을 넣지 않는 경우 기본값

✅ ALTER

• 추가 : ALTER TABLE 테이블명 ADD 컬럼명 데이터타입 [제약조건];
• 수정 : ALTER TABLE 테이블명 MODIFY 컬럼명 데이터타입 [제약조건];
• 삭제 : ALTER TABLE 테이블명 DROP 컬럼명;

✅ DROP

• DROP TABLE 테이블명 [CASCADE | RESTRICT];
• 명령어 옵션
  • CASCADE : 참조한느 테이블까지 연쇄 제거
  • RESTRICT : 참조 중이면 제거 X

✅ TRUNCATE

• TRUNCATE TABLE 테이블명;
• 테이블 내 데이터 제거

✅ VIEW 관련 DDL

• CREATE VIEW
  - VIEW 테이블의 SELECT절에는 UNION 또는 ORDER BY 사용 불가
• CREATE OR REPLACE VIEW
  - 뷰 교체
• DROP VIEW

✅ INDEX 관련 DDL

• CREATE INDEX
  -CREATE [UNIQUE] INDEX 인덱스명 ON 테이블명(컬럼명)
  - UNIQUE 생략 가능, 인덱스 걸린 컬럼에 중복 값 허용 X
  - 복수 컬럼 인덱스로 걸 수 있음
• ALTER INDEX
• DROP INDEX

📌 DML

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

✅ SELECT

• 데이터의 내용 조회시 사용

SELECT [ALL|DISTINCT] 속성명
	FROM 테이블명
[WHEERE 조건]
[GROUP BY 속성명]
[HAVING 그룹 조건]
[ORDER BY 속성 [ASC|DESC]];

• 조인 : 두개 이상의 테이블 연결하여 검색

  • 내부 조인 : 공통 칼럼의 값이 같은 경우 추출
  • 외부 조인 : 왼쪽 내부 조인, 오른쪽 외부 조인, 완전 외부 조인
  • 교차 조인 : 모든 데이터 조합 추출
  • 셀프 조인 : 자신에게 별칭 지정하고 다시 조인

• 서브쿼리

  • 개념
    - SQL문 안에 포함된 또 다른 SQL문
    - 알려지지 않은 기준을 위한 검색을 위해 사용
    - 메인쿼리와 서브쿼리 관계는 주종관계, 서브쿼리에 사용되는 컬럼 정보는 메인쿼리 컬럼 사용 가능, 역으로는 성립X
  • 유형
    • SELECT절 서브쿼리
      - 스칼라 서브쿼리
      - 단일 행을 리턴해야 함
      - 집계 함수가 많이 쓰임
    • FROM절 서브쿼리
      - 인라인 뷰
    • WHERE절 서브쿼리
      - 중첩 서브쿼리

• 집합 연산자
  : UNION, UNION ALL, INTERSECT, MINUS
  

✅ INSERT

• INSERT INTO 테이블명(속성명) VALUES (데이터)
• 속성과 데이터 개수, 타입 일치해야함

✅ UPDATE

• UPDATE 테이블명 SET 속성명 = 데이터 WHERE 조건
• WHERE 절을 통해 어떤 조건이 만족할 경우에만 특정 컬럼의 값을 수정하는 용도로 자주 사용

✅ DELETE

• 모든 레코드 삭제시 WHERE절 X
• 레코드 삭제해도 테이블 구조 남아있어서 DROP과는 다름

📌 DCL

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

✅ GRANT

• GRANT 권한 ON 테이블 TO 사용자
• 권한 부여

✅ REVOKE

• REVOKE 권한 ON 테이블 FROM 사용자
• 권한 회수