[ VII ] SQL 응용 - 데이터베이스 기본

[ 1 ] 트랜잭션

( 1 ) 트랜잭션

• 트랜잭션의 개념
  • 인가받지 않은 사용자로부터 데이터를 보장하기 위해 DBMS가 가져야하는 특성이자, 데이터베이스 시스템에서 하나의 논리적 기능을 정상적으로 수행하기 위한 작업의 기본 단위.
• 트랜잭션의 특징 ( ACID )
  • 원자성(Atomicity) 
    • 트랜잭션을 구성하는 연산 전체가 모두 정상적으로 실행되거나 모두 취소되어야 하는 성질
    • 주요기법
      • Commit/Rollback
      • 회복성 보장
  • 일관성(Consistency)
    • 시스템이 가지고 있는 고정요소는 트랜잭션 수행 전과 트랜잭션 수행 완료 후의 상태가 같아야 하는 성질
    • 주요 기법
      • 무결성 제약조건
      • 동시성 제어
  • 격리성=고립성(Isolation)
    • 동시에 실행되는 트랜잭션들이 서로 영향을 미치지 않아야 한다는 성질
    • 주요 기법 ( 고립화 수준 )
      • Read Uncommitted
        • 한 트랜잭션에서 연산(갱신) 중인( 아직 커밋되지 않은 ) 데이터를 다른 트랜잭션이 읽는 것을 허용, 연산(갱신) 중인 데이터에 대한 연산은 불허
        • 연산중인 데이터를 읽을 순 있으나, 연산은 불가
      • Read Committed
        • 한 트랜잭션에서 연산(갱신)을 수행할 때, 연산이 완료될 때까지 연산 대상 데이터에 대한 읽기를 제한, 연산이 완료되어 커밋된 데이터는 다른 트랜잭션이 읽는 것을 허용
        • 연산중인 데이터를 읽은 순 없으나, 완료된 데이터는 읽기 허용
      • Repeatable Read
        • 선행 트랜잭션이 특정 데이터를 읽을 때, 트랜잭션 종료 시까지 해당 데이터에 대한 갱신/삭제를 제한
        • 선행 트랜잭션이 데이터를 읽고 있을 때, 다른 트랜잭션은 해당 데이터에 대해 갱신/삭제는 불가, 읽기는 가능
      • Serializable Read
        • 선행 트랜잭션이 특정 데이터 영역을 순차적으로 읽을 때, 해당 데이터 영역 전체에 대한 접근을 제한.
  • 영속성(Durability)
    • 성공이 완료된 트랜잭션의 결과는 영속적으로 데이터 베이스에 저장되어야 하는 성질
      • 주요기법
        • 회복기법
• 트랜잭션의 상태전이도

트랜잭션의 상태전이도

• 트랜잭션의 상태 변화
  • 활동 상태(Active)
    • 초기 상태, 트랜잭션이 실행 중일 때 가지는 상태
  • 부분 완료 상태(Partially Committed)
    • 마지막 명령문이 실행된 후에 가지는 상태
  • 완료 상태(Committed)
    • 트랜잭션이 성공적으로 완료된 후 가지는 상태
  • 실패 상태(Failed)
    • 정상적인 실행이 더 이상 진행될 수 없을 때 가지는 상태
  • 철회 상태(Aborted)
    • 트랜잭션이 취소되고 데이터베이스가 트랜잭션 시작 전 상태로 환원된 상태
• 트랜잭션 제어
  • 트랜잭션 제어언어 TCL(Transaction Control Language)이라고 함
  • 트랜잭션의 결과를 허용/취소 하는 목적으로 사용
  • 명령어 ( 커밋, 롤백, 체크 포인트 )
    • 커밋(Commit)
      • 트랜잭션 확정 : 트랜잭션을 메모리에 영구적으로 저장하는 명령
    • 롤백(Rollback)
      • 트랜잭션 취소 : 트랜잭션 내역을 저장 무효화 시키는 명령
    • 체크포인트(CHECKPOINT)
      • 저장 시기 설정 : ROLLBACK을 위한 시점을 지정하는 명령어
• 병행 제어(일관성 주요 기법)
  • 병행 제어(Concurrency Control) 개념
    • 병행 제어는 다수 사용자 환경에서 여러 트랜잭션을 수행할 때, 데이터 베이스 일관성을 유지를 윟 상호 작용을 제어하는 기법이다.
  • 병행 제어의 목적
    • 데이터베이스 공유 최대화
    • 시스템 활용도 최대화
    • 데이터베이스 일관성 유지
    • 사용자 응답시간 최소화
  • 병행 제어 미보장 시 문제점
    • 갱신 손실(Lost Updated)
      • 먼저 실행된 트랜잭션의 결과를 나중에 실행된 트랜잭션이 덮어쓸 때 발생하는 오류
    • 현황 파악오류(Dirty Read)
      • 트랜잭션의 중간 수행 결과를 다른 트랜잭션이 참조하여 발생하는 오류
    • 모순성(Inconsistency)
      • 두 트랜잭션이 동시에 실행되어 데이터베이스의 일관성이 결여되는 오류
    • 연쇄 복귀(Cascading Rollback)
      • 복수의 트랜잭션이 데이터 공유 시 특정 트랜잭션이 처리를 취소할 경우 트랜잭션이 처리한 곳의 부분을 취소하지 못하는 오류
  • 병행 제어 기법의 종류
    • 로킹(Locking)
      • 하나의 트랜잭션을 실행하는 동안 특정 데이터 항목에 대해서 다른 트랜잭션이 동시에 접근하지 못하도록 상호 배제(Mutual Exclusion) 기능을 제공하는 기법
      • 로킹의 특징은 다음과 같음
    • 낙관적 검증(Optimistic Validation)
      • 트랜잭션이 어떠한 검증도 수행하지 않고 일단 트랜잭션을  수행하고, 트랜잭션 종료 시 검증을 수행하여 데이터베이스에 반영하는 기법
    • 타임 스탬프 순서(Time Stamp Ordering)
      • 트랜잭션과 트랜잭션이 읽거나 갱신한 데이터에 대해 트랜잭션이 실행을 시작하기 전에 타임 스탬프를 부여하여 부여된 시간에 따라 트랜잭션 작업을 수행하는 기법
    • 다중버전 동시성 제어(MVCC; Multi Version Concurrency Control)
      • 트랜잭션의 타임스탬프와 접근하려는 데이터의 타임스탬프를 비교하여 직렬가능성이 보장되는 적절한 버전을 선택하여 접근하도록 하는 기법
• 데이터베이스 고립화 수준(격리성 주요 기법)
  • Redo Uncommitted
  • Read Committed
  • Repetable Read
  • Serializable Read
• 회복 기법(영속성 주요 기법)
  • REDO
    • 데이터베이스가 비정상적으로 종료되었을 때 디스크에 저장된 로그를 분석하여 트랜잭션의 시작과 완료에 대한 기록이 있는 트랜잭션들의 작업을 재시작하는 기법
  • UNDO
    • 데이터베이스가 비정상적으로 종료 되었을 때 디스크에 저장된 로그를 분석하여 트랜잭션의 시작은 있지만, 완료 기록이 없는 트랜잯녀들이 작업한 변경 내용을 모두  취소하는 기법
  • 회복 기법 종류
    • 로그 기반 회복기법
      • 지연 갱신 회복기법 : 트랜잭션이 완료되기 전까지 데이터베이스에 기록하지 않는 기법
      • 즉각 갱신 회복 기법 : 트랜잭션 수행 중 갱신 결과를 바로 DB에 반영하는 기법
    • 체크 포인트 회복기법
      • 장애 발생 시 검사점 이후에 처리된 트랜잭션에 대해서만 장애 발생 이전의 상태로 복원시키는 기법
    • 그림자 페이징 회복기법
      • 데이터베이스 트랜잭션 수행 시 복제본을 생성하여 데이터베이스 장애 시 이를 이용해 복구하는 기법

( 2 ) DDL

• 데이터 정의어(DDL; Data Definition Language)
• DDL의 대상
  • 도메인
    • 하나의 속성이 가질 수 있는 원자값들의 집합
  • 스키마
    • 데이터의 구조, 제약조건 등의 정보를 담고있는 기볹거인 구조
    • 외부/개념/내부 3계층으로 구성됨
    • 외부 스키마
      • 사용자 뷰를 나타냄
      • 서브 스키마로 불림
    • 개념 스키마
      • 전체적인 논리적 구조
      • 전체적인 뷰를 나타냄
    • 내부 스키마
      • 물리적 저장 장치 관점에서 보는 데이터베이스 구조
  • 테이블
    • 데이터 저장 공간
  • 뷰
    • 하나 이상의 물리 테이블에서 유도되는 가상의 테이블
  • 인덱스
    • 검색을 빠르게 하기 위한 구조
• DDL 명령어
  • 생성 :
    • CREATE ( 데이터베이스 오브젝트를 생성 )
  • 수정 :
    • ALTER ( 데이터베이스 오브젝트를 변경 )
  • 삭제 :
    • DROP ( 데이터베이스 오브젝트를 삭제 )
    • TRUNCATE ( 데이터베이스 오브젝트의 내용을 삭제 )
• TABLE 관련 DDL
  • CREATE TABLE
    • 테이블을 생성하는 명령어 
    • CREATE TABLE 테이블명
      ( 
       컬럼명 데이터타입 [제약조건],
      ....
      );
  • ALTER TABLE
    • 테이블을 수정하는 명령어 
    • ALTER TABLE 테이블명 ADD 컬럼명 데이터타입 [제약조건];
    • 예 ) ALTER TABLE 사원 ADD 전화번호 VARCHAR(11) UNIQUE;
      • 사원 테이블의 전화번호 컬럼의 타입을 VARCHAR(11)로 바꾸고 UNIQUE라는 제약조건을 설정
  • DROP TABLE
    • 테이블을 삭제하는 명령어 
    • DROP TABLE 테이블명 [ CASCADE | RESTRICT ];
    • 예 ) DROP TABLE 사원;
      • 사원 테이블을 삭제
    • 예2 ) DROP TABLE 사원 CASCADE
      • 사원 테이블을 참조하는 테이블도 모두 삭제
    • 예3 ) DROP TABLE 사원 RESTRICT
      • 다른 테이블이 참조중일 경우 삭제하지 않음
• VIEW 관련 DDL
  • CREATE VIEW
    • 뷰를 생성하는 명령어
    • CREATE VIEW 뷰 이름 AS 조회쿼리;
    • 예 ) CREATE VIEW 사원뷰 AS
      SELECT 사번, 이름
      FROM 사원
      WHERE 성별 = 'M';
      • 사원 테이블에서 성별 값이 'M'을 가지는 사원의 사번, 이름으로 생성한 사원뷰 라는 이름의 뷰를 생성
  • CREATE OR REPLACE VIEW 뷰이름 AS 조회쿼리
    • 뷰를 교체하는 명령어
  • DROP VIEW
    • 뷰를 삭제하는 명령어
    • DROP VIEW 뷰이름;
• INDEX 관련 DDL
  • CREATE INDEX
    • 인덱스를 생성하는 명령어
    • CREATE [UNIQUE] INDEX 인덱스명 ON 테이블명(컬럼명1, 컬럼명2, ...);
    • 예 ) CREATE INDEX 사번인덱스 ON 사원(사번);
      • 사원 테이블의 사번 컬럼에 대한 사번인덱스라는 인덱스 명으로 인덱스 생성
  • ALTER INDEX
    • 인덱스를 수정하는 명령어
    • ALTER [UNIQUE] INDEX 인덱스명 ON 테이블명(컬럼명1, 컬럼명2, ...);
    • 예) ALTER INDEX 사번인덱스 ON 사원(사번); 
      • 사원 테이블의 사번 컬럼에 대해 사번인덱스라는 인덱스 명으로 인덱스 수정
  • DROP INDEX
    • 인덱스를 삭제하는 명령어
    • DROP INDEX 인덱스명

( 3 ) DML

• 데이터 조작어(DML; Data Manipulation Language)의 개념
  • 데이터베이스에 저장된 자료들을 입력, 수정, 삭제, 조회하는 언어
• DML 명령어
  • SELECT ( 조회 )
  • INSERT ( 삽입 )
  • UPDATE ( 갱신 )
  • DELETE ( 삭제 )
• SELECT(데이터 조회) 명령어
  • 데이터를 조회할 때 사용하는 명령어
  • SELECT [ALL | DISTINCT] 속성명1, 속성명2... FROM 테이블명1, ...
    [ WHERE 조건]
    [ GROUP BY 속성명1, ...]
    [ HAVING 그룹조건]
    [ ORDER BY 속성 [ ASC | DESC ] ] ;
  • SELECT 절
    • 검색하고자 하는 속성명, 계산식을 기술
    • 속성명 별칭은 AS를 사용하며 생략 가능
    • 술어 부분은 ALL이 기본값
      • ALL : 모든 튜플을 검색할 때 사용
      • DISTINCT : 중복된 속성이 조회될 경우 그 중 한 개만 검색
  • WHERE 절
    • 비교
      • =  : 값이 같은 경우 조회
      • <>, != : 값이 다른 경우 조회
      • <, <=, >, >= : 비교 연산에서 해당하는 데이터 조회
    • 범위
      • BETWEEN
        • 컬럼 BETWEEN 값1 AND 값2;
        • 값1보다 크거나 같고, 값2보다 작거나 같은 데이터 조회
        • 예 ) SELECT * FROM PRODUCT WHERE PRICE BETWEEN 50000 AND 80000;
        • PRODUCT 테이블에서 PRICE의 가격이 50000보다 보다 크거나 같고, 80000만 보다 작거나 같은 튜플을 조회
    • 집합
      • IN
        • 컬럼 IN (값1, 값2, ...)
        • 컬럼 IN 안에 포함된 경우 데이터 조회
        • 예 ) SELECT * FROM PRODUCT WHERE PRICE IN (40000, 50000, 60000);
        • PROUCT 테이블에 PRICE라는 값이 40000, 50000, 60000 인 튜플을 조회
      • NOT IN
        • 컬럼 NOT IN (값1, 값2, ...)
        • 컬럼 IN 안에 포함되지 않은 경우 데이터 조회
    • 패턴
      • LIKE
        • 컬럼 LIKE 패턴
        • 컬럼이 패턴에 포함된 경우의 데이터 조회
        • 패턴
          • % : 0개 이상의 문자열과 일치
          • [] : 1개의 문자와 일치
          • [^] : 1개의 문자와 불일치
          • _ : 특정위치의 1개의 문자와 일치
        • 예1 ) SELECT * FROM PRODUCT WHERE NAME LIKE '정보%';
          • PRODUCT 테이블에서 NAME이 '정보' 로 시작하는 문자열 검색
        • 예2 ) SELECT * FROM PRODUCT WHERE NAME LIKE '[ABCD]%';
          • 첫 번째 문자가 A 또는 B 또는 C 또는 D 인 문자열과 일치하는 문자열 검색
    • NULL
      • IS NULL
        • 컬럼 IS NULL
        • 컬럼이 NULL인 데이터 조회
      • IS NOT NULL
        • 컬럼 IS NOT NULL
        • 컬럼이 NULL이 아닌 데이터 조회
    • 복합조건
      • AND
        • 조건1 AND 조건2
        • 조건1과 조건2가 모두 만족하는 데이터 조회
      • OR
        • 조건1 OR 조건2
        • 조건1 또는 조건2 둘 주우 하나를 만족 하는 데이터 조회
      • NOT
        • NOT 조건
        • 조건에 해당하지 않는 데이터 조회
  • GROUP BY 절
  •  
  • 속성값을 그룹으로 분류하고자 할 때 사용
  • 예 ) SELECT 직책, COUNT(직책), SUM(급여), FROM 급여 GROUP BY 직책;
    • 급여 테이블에서 직책과, 직책의개수, 급여의 합을 직책을 기준으로 그룹으로 묶어서 보여줌
  • HAVING 절
    • GROUP BY에 의해 분류한 후 그룹에 대한 조건을 지정할 때 사용
    • 예 ) SELECT 직책, 부서, SUM(급여) AS 급여합계 FROM 급여 GROUP BY 직책, 부서 HAVING 급여합계 >= 5000; 
      • 급여 테이블에서 직책과 부서를 기준으로 그룹화 했을 때, 급여 합계가 5000이상이 직책, 부서, 급여 합계를 조회
  • ORDER BY 절
    • 속성값을 정렬하고자 할 때 사용
    • 예1 ) SELECT * FROM 성적 ORDER BY 이름;
      • 성적 테이블을 이름을 기준으로 정렬( 오름차순 )
      • 정렬 방식을 명시하지 않을 경우 기본적으로 오름차순(ASC) 이다.
    • 예2 ) SELECT * FROM 성적 ORDER BY 학점 DESC, 이름 ASC;
      • 성적 테이블에서 학점을 기준으로 내림차순 정렬 한 이후, 이름을 기준으로 오름차순 정렬을 한다.
• 조인(Join)
  • 조인 개념
    • 조인은 두 개 이상의 테이블을 연결하여 데이터를 검색하는 방법이다.
  • 내부 조인(Inner Join)
    • 공통 존재 칼럼의 값이 같은 경우를 출력하는 기법
    • SELECT A.컬럼1, A.컬럼2, ..., B.컬럼1, B.컬럼2, ...
      FROM 테이블1 A [INNER] JOIN 테이블2 B
      ON 조인조건
      [ WHERE 검색 조건 ]
      • 같은 이름의 컬럼이 여러 테이블에 있을 경우 "별칭.컬럼명" 형태로 명시
      • INNER 라는 키워드는 생략해도 내부 조인이 됨
      • 검색 조건을 추가할 경우 조인된 값에서 해당 조건에 맞는 결과만 출력되도록 설정
  • 외부 조인(Outer Join)
    • 외부 조인의 종류로는 왼쪽 외부 조인, 오른쪽 외부 조인, 완전 외부 조인이 있음
      • 왼쪽 외부 조인(Left Outer Join)
        • 왼쪽 테이블의 모든 데이터와 오른쪽 테이블의 동일 데이터를 추출하는 기법
        • SELECT A.컬럼1, A.컬럼2, ..., B.컬럼1, B.컬럼2, ...
          FROM 테이블1 A LEFT [OUTER] JOIN 테이블2 B
          ON 조인조건
          [WHERE 검색 조건];
      • 오른쪽 외부 조인(Right Outer Join)
        • 오른쪽 테이블의 모든 데이터와 왼쪽 테이블의 동일 데이터를 추출하는 기법
        • SELECT A.컬럼1, A.컬럼2, ..., B.컬럼1, B.컬럼2, ...
          FROM 테이블1 A RIGHT [OUTER] JOIN 테이블2 B
          ON 조인조건
          [WHERE 검색 조건];
      • 완전 외부 조인(Full Outer Join)
        • 양쪽의 모든 데이터를 추출하는 기법
          SELECT A.컬럼1, A.컬럼2, ..., B.컬럼1, B.컬럼2, ...
          FROM 테이블1 A FULL [OUTER] JOIN 테이블2 B
          ON 조인조건
          [WHERE 검색 조건];
  • 교차 조인(Cross Join)
    • 조인 조건이 없는 모든 데이터 조합을 추출하는 기법
    • SELECT A.컬럼1, A.컬럼2, ...
      FROM 테이블1 CROSS JOIN 테이블2
      • 조인 조건이 없는 모든 데이터 조합을 추출하기 때문에 ON절이 없음
  • 셀프 조인(Self Join)
    • 자기 자신에게 별칭을 지정한 후 다시 조인하는 기법
    • SELECT A.컬럼1, A.컬럼2, ..., B.컬럼1, B.컬럼2, ...
      FROM 테이블1 A [INNER] JOIN 테이블1 B
      ON 조인 조건
      [WHERE 검색조건];
      • 같은 테이블명을 쓰고 별밍창 A, B와 같이 다르게 함
• INSERT(데이터 삽입) 명령어
  • 데이터의 내용을 삽입할 때 사용하는 명령어 이다.
  • INSERT INTO 테이블명(속성명1, ...) VALUE (데이터1, ...);
    • 속성과 데이터 개수, 데이터 타입이 일치해야 함
    • 속성명 생략가능
    • 속성의 타입이 숫자인 경우 데이터는 따옴표를 붙이지 않아도 되며, 문자열인 경우 따옴표를 붙여야 함
  • 예 ) INSERT INTO 학생(학번, 성명, 학년, 수강과목) VALUES(6677, "김씨", 3, "수학");
    • 학생 테이블에 학번이 6677이고 이름이 "김씨" 학년은 3학년 수강과목이"수학"인 학생을 삽입
• UPDATE(데이터 변경) 명령어
  • UPDATE 테이블명 SET 속성명 = 데이터, ... WHERE 조건;
  • 예 ) UPDATE 학생 SET 주소 = '인천' WHERE 이름 = '김씨';
    • 학생 테이블에 이름이 '김씨' 인 학생의 주소를 '인천' 으로 변경
• DELETE(데이터 삭제) 명령어
  • DELETE FROM 테이블명 WHERE 조건;
  • 예 ) DELETE FROM 학생 WHERE 이름 = '김씨'
    • 학생 테이블에 이름이 '김씨' 인 튜플을 삭제

( 4 ) DCL

• 데이터 제어어(DCL; Data Control Language)의 개념
  • 데이터베이스 관리자가 데이터 보안, 무결성 유지, 병행 제어, 회복을 위해 관리자가 사용하는 제어용 언어이다.
  • 제어어 유형에는 GRANT, REVOKE가 있다.
  • GRANT(사용 권한 부여)
    • GRANT 권한 ON 테이블 TO 사용자;
    • 예 ) GRANT UPDATE ON 학생 TO 김씨;
      • 학생 테이블에 있는 김씨에게 UPDATE 권한을 부여한다.
  • REVOKE(사용 권한 취소)
    • REVOKE 권한 ON 테이블 FROM 사용자;
    • 예 ) REVOKE UPDATE ON 학생 FROM 김씨;
      • 학생 테이블에 김씨의 UPDATE 권한을 취소