7장 SQL 응용

Chapter 01 데이터베이스 기본

(1) 트랜잭션

트랜잭션이란, 어떤 논리적 기능을 정상적으로 수행하기 위한 작업의 기본단위라고 하는데…

결국에는 하나의 로직 실행이라고 보면 된다.

Update, Delete, Insert 등의 변화를 가져오는 로직 수행

트랜잭션의 특성

  • 원자성
    • 트랜잭션을 구성하는 연산 전체가 모두 정상적으로 실행되거나 취소되어야 하는 성질 (우리 디비 실행하다가 에러 나면 다 취소시키는 것과 같음)
  • 일관성
    • 시스템이 가지고 있는 고정요소는 트랜잭션 수행 전과 수행 완료 후의 상태가 같아야 하는 성질
  • 격리성=고립성
    • 동시에 실행되는 트랜잭션들이 서로 영향을 미치지 않아야 한다.
  • 영속성
    • 성공이 완료된 트랜잭션의 결과는 영속적으로 데이터 베이스에 저장되어야 한다.

트랜잭션의 상태변화

트랜잭션의 상태 변화

  • 활동상태 : 초기상태, 트랜잭션이 실행 중일 때 가지는 상태
  • 부분 완료 상태 : 마지막 명령문이 실행된 후에 가지는 상태
  • 완료 상태 : 트랜잭션이 성공적으로 완료된 후 가지는 상태
  • 실패 상태 : 정상적인 실행이 더 이상 진행 될 수 없을 때 가지는 상태
  • 철회 상태 : 트랜잭션이 취소되고 데이터베이스가 트랜잭션 시작 전 상태로 환원된 상태

트랜잭션 제어

TCL (Transaction Control Language)

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

Begin Tran 쓰고 Commit Rollback

위와 같이 사용하면 됨.

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

  1. 병행제어(Concurrency Control) 개념

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

  2. 병행 제어의 목적
    • 데이터 베이스의 공유를 최대화한다.
    • 시스템의 활용도를 최대화한다.
    • 데이터베이스의 일관성을 유지한다.
    • 사용자에 대한 응답시간을 최소화한다.
  3. 병행 제어 미보장 시 문제점

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

    • 갱신 손실 : 먼저 실행된 트랜잭션의 결과를 나중에 실행된 트랜잭션이 덮어쓸 때 발생하는 오류
    • 현황 파악오류 : 트랜잭션의 중간 수행 결과를 다른 트랜잭션이 참조하여 발생하는 오류
    • 모순성 : 두 트랜잭션이 동시에 실행되어 데이터베이스의 일관성이 결여되는 오류
    • 연쇄복귀 : 복수의 트랜잭션이 데이터 공유 시 특정 트랜잭션이 처리를 취소할 경우 트랜잭션이 처리한 곳의 부분을 취소하지 못하는 오류
  4. 병행제어 기법의 종류
    • 로킹 : 다중 트랜잭션의 환경에서 트랜잭션의 순차적 진행을 위해 잠금을 거는 것임
      • 데이터 베이스, 파일,레코드 등이 로킹 단위가 될 수 있음
      • 로킹 단위가 작아지면 데이터베이스 공유도가 증가
      • 로킹 단위가 작아지면 로킹 오베헤드가 증가
      • 한꺼번에 로킹할 수 있는 객체의 크기를 로킹 단위라고 함.
    • 낙관적 검증 : 트랜잭션이 어떠한 검증도 수행하지 않고 일단 수행하고, 종료 시 검증을 수행하여 데이터베이스에 반영함. (마지막에 검증한다가 핵심)
    • 타임 스탬프 순서 : 트랜잭션이 일거나 갱신한 데이터에 대해 트랜잭션이 실행을 시작하기 전에 타임 스탬프를 부여하여 부여된 시간에 따라 트랜잭션 작업을 수행하는 기법
    • 다중버전 동시성 제어 : 트랜잭션의 타임스탬프와 접근하려는 데이터의 타임스탬프를 비교하여 직렬 가능성이 보장되는 적절한 버전을 선택하여 접근하도록 하는 기법

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

  1. 고립화 수준 (Isolation Level) 개념

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

  2. 고립화 수준 종류
    • Read Uncommitted
      • 트랜잭션에서 연산(갱신) 중인(아직 커밋되지 않은) 데이터를 다른 트랜잭션이 읽는 것을 허용하는 수준
      • 연산(갱신) 중인 데이터에 대한 연산은 불허
    • Read Committed
      • 한 트랜잭션에서 연산(갱신)을 수행할 때, 연산이 완료될 때까지 연산 대상 데이터에 대한 읽기를 제한하는 수준
      • 연산이 완료되어 커밋된 데이터는 다른 프랜잭션이 읽는 것을 허용
    • Repeatable Read
      • 선행 트랜잭션이 특정 데이터를 읽을 때, 트랜잭션 종료 시까지 해당 데이터에 대한 갱신 삭제를 제한하는 수준
    • Serializable Read
      • 선행 트랜잭션이 특정 데이터 영역을 순차적으로 읽을 때, 해당 데이터 영역 전체에 대한 접근 제한하는 수준

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

  1. 회복 기법(Recovery) 개념

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

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

(2) DDL

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

  • 생성, 변경, 삭제, 이름을 바꾸는 데이터 구조와 관련된 명령어들..

DDL 의 대상

  • 도메인
    • 하나의 속성이 가질 수 있는 원자값들의 집합
    • 속성의 데이터 타입과 크기, 제약조건 등의 정보
  • 스키마
    • 데이터베이스의 구조, 제약조건 등의 정보를 담고 있는 기본적인 구조
    • 외부/개념/내부 3계층으로 구성되어 있음.
      • 외부 : 사용자, 개발자의 관점에서 필요로 하는 논리적 구조
      • 개념 : 전체적인 논리적 구조
      • 내부 : 물리적 저장장치 관점에서 보는 데이터베이스 구조
    • 테이블
    • 뷰
    • 인덱스
  1. 테이블
    • 릴레이션, 엔티티라고 부른다.
    • 튜플/ 행 : 테이블 내의 행을 의미 레코드라고도 부름. (같은 값을 가질 수 없음)
    • 애트리뷰트/ 열 : 열의 개수를 디그리(Degree)라고 함.
    • 식별자 : 관계형 데이터베이스에서 각각의 구분 할 수 있는 논리적 개념
    • 카디널리티 : 튜플의 개수를
    • 차수 : 애틜뷰트의 개수
    • 도메인 : 하나의 애트리뷰트가 취할 수 있는 같은 타입의 원자 값들의 집합
  2. 뷰
    • 논리 테이블 (물리적으로 존재하지 않음)
    • Select의 결과 값이라 생각하면 됨.
    • 우리회사에서 출력물에 사용되고 있음.
  • 뷰의 특징
    • 논리적 데이터 독립성 제공 : 데이터베이스에 영향을 주지 않고 애플리케이션이 원하는 형태로 데이터에 접근 가능함.
    • 데이터 조작 연산 간소화 : 애플리케이션이 원하는 형태의 논리적 구조를 형성하여 데이터 조작 연산을 간소화
    • 보안기능 : 특정 필드만을 선택해 뷰를 생성하므로 선택되지 않는 필드 볼 수 없음.
    • 뷰 변경 불가 : 뷰는 Alter문을 사용하여 변경할 수 없음 Create 문을 사용하여 정의 제거할 때는 Drop을 사용함. 변경은 Drop and Create 가 원칙임
  • 단점
    • 뷰 자체 인덱스 불가 : 뷰 자체는 인덱스를 가지지 못함
    • 뷰 정의 변경 불가 : 삭제후 재생성 해야 함. (이게 딱히 불편함 나는 못느꼈는데)
    • 데이터 변경 제약 존재 : 삽입, 삭제, 변경에 제약이 있음.
  1. 인덱스
    • 책갈피
    • 검색 연산의 최적화르 루이해 주소 정보로 구성된 데이터 구조
    • 조회 속도가 높아짐
  • 인덱스의 특징
    • 기본키 컬럼은 자동으로 인덱스가 생성됨
    • 연월일, 이름을 기준으로 하는 인덱스는 자동으로 생성되지 않음.
    • 인덱스가 없으면 전체 내용을 검색함. (Table Full Scan)
    • 인덱스가 생서오디어 있을 때 빠르게 찾을 수 있음 (Index Range Scan)
    • 조건절에 ‘=’ 로 비교되는 컬럼을 대상으로 인덱스를 생성하면 검색 속도를 높일 수 있음.
  • 인덱스의 종류 : 순해비함단결클
    • 순서 : 데이터가 정렬된 순서로 생성되는 인덱스
    • 해시 : 해시 함수에 의해 직접 데이터에 키 갑승로 접근하는 인덱스
    • 비트맵 : 각 컬럼에 적은 개수 값이 저장된 경우 선택하는 인덱스
    • 함수기반 : 수식이나 함수를 적용하여 만든 인덱스
    • 단일 : 하나의 컬럼으로만 구성된 인덱스
    • 결합 : 두개 이상의 컬럼으로 구성됨
    • 클러스터드 인덱스 : 기본 키를 기준으로 레코드를 묶어서 저장하는 인덱스
  • 인덱스의 스캔방식
    • 인덱스 범위 스캔
    • 인덱스 전체 스캔
    • 인덱스 단일 스캔
    • 인덱스 생략 스캔

DDL 명령어

  • 생성 : CREATE
  • 수정 : ALTER
  • 삭제 : DROP(오브젝트 삭제), TRUNCATE (오브젝트 내용삭제)
  1. CREATE Table
    • UNIQUE : 테이블 내에서 얻은 유일한 값을 갖도록 하는 제약조건
    • CHECK : 개발자가 정의하는 제약조건(참이어야 하는 조건을 지정)
    • DEFAULT : 데이터를 INSERT 할 때 해당 컬럼의 값을 넣지 않는 경우 기본값으로 설정해주는 제약 조건
  2. ALTER TABLE
    • ALTER TABLE 테이블명 ADD 컬럼명 데이터타입 제약조건
    • ALTER TABLE 테이블명 MODIFY 컬럼명 데이터타입 제약조건
    • ALTER TABLE 테이블명 DROP 컬럼명
  3. DROP TABLE
    • DROP TABLE 테이블 명 [CASCADE|RESTRICT]
    • CASCADE, RESTRICT 의 경우 외래키가 걸려있을 때 사용한다.
    • CASCADE : 참조하는 테이블까지 연쇄적으로 제거하는 옵션
    • RESTRICT : 다른 테이블을 삭제할 테이블을 참조 중이면 제거하지 않는 옵션
  4. TRUNCATE TABLE
    • TRUNCATE TABLE 테이블 명
  5. VIEW관련 DDL
    • CREATE VIEW 뷰이름 AS
    • CREATE OR REPLACE VIEW 뷰이름 AS
    • DROP VIEW
  6. INDEX 관련 DDL
    • CREATE INDEX
    • CREATE[UNIQUE] INDEX 인덱스명 ON 테이블명 (컬럼명1, 컬럼명2..) - ALTER INDEX
    • ALTER[UNIQUE] INDEX 인덱스 명 ON 테이블명 (컬럼명1, 컬럼명2 ..) - DROP INDEX
    • DROP INDEX 인덱스명

(3) DML

  1. 데이터 조작어 (DML : Data Manipulation Langugage)

    입력, 수정, 삭제, 조회하는 언어

  2. DML 명령어
    • Select
    • INSERT
    • Update
    • DELETE
  3. 조인
    • 내부조인
    • 외부조인
      • 왼쪽 외부조인
      • 오른쪽 외부조인
      • 완전 외부조인
    • 교차조인
    • 셀프조인
  4. 서브쿼리
    • FROM절 서브쿼리

      FROM 다음에 SELECT를 사용하여 그 안에서 조회되게 한다.

  • WHERE절 서브쿼리

    WHERE IN 다음에 SELECT 를 사용하여 대상 안에서 검색되게 한다.

  1. 집합연산자
    • UNION : 합집합(중복제거)
    • UNION ALL : 합집합(중복허용)
    • INTERSECT : 교집합
    • MINUS : 비교레코드 제외 UNION

(4) DCL

  1. 데이터 제어어 (Data Control Language)의 개념
    • GRANT : 관리자가 사용자에게 데이터베이스에 대한 권한을 부여하는 명령어
    • REVOKE : 관리자가 사용자에게 부여했던 권한을 회수하기 위한 명령어
  2. 사용법 
    GRANT 권한 ON 테이블 TO 사용자 
REVOKE 권한 ON 테이블 FROM 사용자

Chapter 02 응용 SQL 작성하기

1. 집계성 SQL 작성

(1) 데이터 분석 함수

총합, 평균 등의 데이터 분석을 위해서 복수행 기준으로 데이터를 모아서 처리하는 애들..

(2) 종류

  • 집계함수
  • 그룹함수
  • 윈도함수

(3) 집계함수

  1. GROUP BY 구문
  • NULL 값을 가지는 ROW는 제외한 후 산출한다.
  • SELECT 에서 사용하는 것과 같은 ALIAS 사용이 불가능하다.
  • WHERE 구문 안에 포함되지 않는다.
  • WHERE 구문은 GROUP BY 보다 먼저 실행되고 선별해준다.
  1. HAVING 구문
    • GROUP BY 에 대한 WHERE 조건이다.
  2. 집계함수의 종류
    • COUNT
    • SUM
    • AVG
    • MAX
    • MIN
    • STDDEV
    • VARIAN

(4) 그룹 함수

  1. 그룹함수의 개념

    테이블의 전체 행을 컬럼 기준으로 그룹화하여 출력해주는 함수

  2. 그룹 함수의 유형
    • ROLLUP 함수

      소계 등 중간 집계 값을 산출하기 위한 그룹함수

    • CUBE 함수

      다차원 집계 연산이 많아 시스템에 부담을 준다.

    • GOUPING SETS 함수

      개별 집계, 컬럼간 순서와 무관한 결과를 얻을 수 있음

나중에 보충 필요할 듯. (좀 생소하네…)

(5) 윈도 함수

중요도가 낮은편이라고 함…

  1. 개념

    온라인 분석처리 용도로 사용, OLAP 함수라고도 함.

넘어가자….

Chapter 03 절차형 SQL 활용하기

절차형 SQL

종류

  • 프로시저
  • 사용자 정의 함수
  • 트리거

제어부

  • IF
  • CASE WHEN

반복문

  • LOOP
  • WHILE
  • FOR LOOP

프로시저와 함수와의 차이점

프로시저는 일련의 트랜잭션의 처리가 목적

함수는 종료 시 단일 값을 반환해줌.

트리거

  1. 개념

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

  1. 목적

    데이터 변경을 시작점으로 그와 관련된 작업을 수행하기 위함.

  2. 종류
    • 행 트리거
    • 문장 트리거

문법등 사용법은 다음에하자…

오늘도 힘들었다 화이팅!