[정보처리기사 실기] 7장 SQL 응용 - 데이터베이스 기본

트랜잭션

트랜잭션

(1) 개념 : 인가받지 않은 사용자로부터 데이터 보장하기 위해 가지는 특성, 하나의 논리적 기능을 정상적으로 수행하기 위한 작업 단위

 

(2) 특성

• Atomicity : 모두 정상적으로 실행되거나 모두 취소되는 성질, 전체 성공 또는 실패 : Commit/Rollback, 회복성 보장
• Consistency : 수행 전과 수행 후의 상태가 같아야 하는 성질, 무결성 제약 조건, 동시성 제어
• Isolation : 서로 영향을 미치지 않아야 한다는 성질 : Read Uncommitte, Read Committed, Repeatable Read, Serializable
• Durability : 결과는 영속적으로 데이터 베이스에 저장 : 회복 기법

(3) 상태

• Active : 초기 상태, 실행 중일때 가지는 상태
• Partially Committed : 마지막 명령문이 실행된 후 가지는 상태
• Committed : 성공적으로 완료된 후 가지는 상태
• Failed : 실해이 더 이상 진행될 수 없을때 가지는 상태
• Aborted : 취소되고 디비가 트랜잭션 시작 전 상태로 환원

(4) 제어

- TCL 이라고 불리며, 결과 허용이나 취소 목적으로 사용

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

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

• 개념 : 다수 사용 환경에 여러 트랜잭션 수행 시, 일관성 유지해 상호 작용 제어
• 목적 : 디비 공유 최대화, 시스템 활용도 최대화, 디비 일관성 유지, 응답시간 최소화
• 병행 제어 미보장 시 문제점 
  • 갱신 손실 : 먼저 실행된 결과를 나중에 실행된 결과가 덮어쓸 때 발생
  • 현행 파악오류 : 중간 수행 결과를 다른 트랜잭션이 참조하여 발생
  • 모순성 : 동시에 실행되어 디비의 일관성이 결여되는 오류
  • 연쇄 복귀 : 복수의 데이터 공유 시 특정 트랜잭션이 처리를 취소할 경우 트랜잭션이 처리한 곳의 부분을 취소하지 못하는 오류
• 종류
  • 로킹 : 같은 자원 액세스 하는 다중 환경에서 일관성과 무결성을 유지 위해 순차적 진행을 보장하는 직렬화 기법
    • 디비, 파일, 레코드가 로킹 단위, 로킹 단위가 작아지면 디비 공유도와 로킹 오버레드가 증가, 로킹 단위
  • 낙관적 검증 : 어떤 검증도 수행하지 않고 일단 수행하고, 종료 시 검증을 수행해 디비에 반영
  • 타임 스탬프 순서 : 읽거나 쓰기 전 타임 스탬프를 부여해 부여된 시간에 따라 트랜잭션 작업 수행
  • 다중버전 동시성 제어 : 타임스탬프를 비교하여 직렬가능성이 보장되는 적절한 버전 선택해 접근

(6) 고립화 수준 - 격리성 주요 기법

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

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

• 개념 : 장애로 인해 손상된 디비를 손상 이전의 정상적인 상태로 복구 시키는 작업
• 종류
  • 로그 기반 회복 기법 
    • 지연 갱신 회복 기법 : 완료 되기 전까지 디비에 기록하지 않는 기법
    • 즉각 갱신 회복 기법 : 수행 중 갱신 결과를 바로 디비에 반영
  • 체크 포인트 회복 기법 : 장애 발생 시 검사점 이후 장애 발생 이전의 상태로 복원 시키는 기법
  • 그림자 페이징 회복 기법 : 복제본 생성해 디비 장애 시 이를 이요해 복구하는 기법

DDL

(1) 개념 

- 데이터 정의어, 데이터 구조 정의하는데 사용하는 특정 구조를 생성 변경 삭제 리네임과 관련된 명령어들을 데이터 정의어라 부름

 

(2) 대상

• 도메인 : 원자값들의 집합, 속성의 데이터 타입과 크기, 제약조건의 정보
• 스키마 : 구조나 제약 조건 정보 담은 구조
  • 외부 스키마 : 사용자나 개발자 관전에 필요한 디비 논리 구조, 사용자 뷰 나타냄, 서브 스키마
  • 개념 스키마 : 전체적인 논리적 구조, 전체적인 뷰, 개체 간의 관계 제약조건 접근권한 무결성 보안에 대한 정의
  • 내부 스키마 : 물리적 저장장치, 레코드 형식 정의하고, 표현 방법, 내부 레코드의 물리적 순서 표현
• 테이블 : 저장 공간
  • 개념 : 데이터 저장 필드, 데이터 집합체, 하나의 디비내 여러 테이블 구성, 릴레이션 혹 엔티티라 불림
  • 용어 
    • 가로, 카디널리티, 튜플, 행 : 레코드 의미, 릴레이션에서 같은 값 가질 수 없다.
    • 세로, 디그리, 애트리뷰트, 열 : 열의 개수는 디그리
    • 식별자 : 관계령 디비에서 각각의 구분 할 수 있는 논리적 개념
    • 카디널리티 : 튜플 개수
    • 차수 : 애트리뷰트 개수
    • 도메인 : 원자값 집합 (이름, 직책 등)
  • CREATE 명령어 : 테이블 생성하는 명령어, 하나의 컬럼에 컬럼명 데이터 타입 제약조건 구성
    • PRIMARY KEY : 기본키, 유일하게 테이블의 각 행 식별
    • FOREIGN KEY : 외래키, 참조 대상 명시
    • UNIQUE : 유일한 값을 갖도록 하는 제약조건
    • NOT NULL : NULL 값을 포함하지 않도록 하는 제약조건
    • CHECK : 개발자가 정의, 참이 되어야 하는 조건
    • DEFAULT : 삽입 할 때, 해당 컬럼의 값을 넣지 않는 경우 기본값 설정
  • ALTER 명령어 : 테이블 수정하는 명령어
    • 추가 : ALTER TABLE name ADD column type [something];
    • 수정 : 이걸로 제약 조건 변경 가능 : ALTER TABLE name MODIFY column type [something];
    • 삭제 : ALTER TABLE name DROP coulmn;
  • DROP 명령어 : 삭제하는 명령어 : DROP TABLE name[CASCADE | RESTRICT];
    • cascade : 참조 테이블 연쇄 제거
    • restrict : 참조 중이면 삭제 안함
  • TRUNCATE 명령어 : 테이블 내 데이터 삭제 : TRUNCATE TABLE name;
• 뷰 : 테이블 유도되는 가상의 테이블
  • 논리 테이블로서 사용자에게 테이블과 동일하다, 하나의 물리 테이블로 생성 가능하고, 조인 기능을 이용할 수 있다.
  • 특징
    • 논리적 데이터 독립성 제공 : 디비에 영향을 주지 않고 앱이 원하는 형태로 데이터 접근 가능
    • 데이터 조작 연산 간소화 : 논리적 구조를 형성하여 데이터 조작 연산 간소화
    • 보안 기능 제공 : 특정 필드만 선택해 뷰 생성 시 선택되지 않은 필드의 조회 및 접근 불가
    • 뷰 변경 불가 : ALTER 를 이용해 변경 할 수 없다.
  • 목적 : 단순 질의어 사용, 복수 데이터 대체하는 단순성, 중요 데이터 일부만 제공
  • 장점 
    • 논리적 독립성 제공, 사용자 데이터 관리 용이, 데이터 보안 용이
  • 단점 
    • 뷰 자체 인덱스 불가, 뷰 정의 변경 불가, 데이터 변경 제약 존재
  • 명령어
    • CREATE VIEW : CREATE VIEW name AS select ~~, select 문에는 union이나 order by 절 가능, 컬럼명 기술되지 않으면 자동으로 사용
    • CREATE OR REPLACE VIEW : 뷰를 생성하거나 교체하는 뷰, CREATE OR REPLACE VIEW name AS select ~~
    • DROP VIEW : 삭제, DROP VIEW name
• 인덱스 : 빠른 검색 위한 데이터 구조
  • 개념 : 검색의 최적화를 위해 값에 대한 주소 정보 구성된 데이터 구조, 빠르게 찾는 수단으로 속도 높여줌, 특정 레코드 위치 알려주는 용도
  • 특징 : 기본 키 컬럼은 자동으로 인덱스 생성, 날짜나 이름은 자동 생성 안함, 인덱스 없는 경우 전체 내용 검색, 인덱스 생성 시 빠르게 찾는다, =로 비교되는 컬럼을 대상 인덱스 생성해 검색 속도 높임
  • 종류
    • 순서 인덱스 : 정렬된 순서로 생성, B-Tree 알고리즘 활용
    • 해시 인덱스 : 해시 함수에 의해 직접 데이터에 키 값 접근, 비용이 균일하고 튜플 양에 무관
    • 비트맵 인덱스 : 컬럼에 적은 개수 값이 저장된 경우 선택하는 인덱스, 수정 변경이 적을 경우 유용
    • 함수 기반 인덱스 : 수식이나 함수를 적용해 만든 인덱스 
    • 단일 인덱스 : 하나의 컬럼만 구성, 컬럼이 하나일 경우 사용
    • 결합 인덱스 : 두 개 이상의 컬럼으로 구성한 인덱스, WHERE 조건 사용해 빈도가 높은 경우 사용
    • 클러스터드 인덱스 : 기본 키 기준 레코드 묶어서 저장, 물리적 순서에 따라 인덱스 생성, 특정 범위 검색 시 유리 함
  • 스캔 방식
    • 인덱스 범위 스캔 : 루트에서 리프까지 수직적 탐색 후 리프를 필요한 범위만 스캔
    • 인덱스 전체 스캔 : 수직적 탐색 없이 리플 블럭을 처음부터 끝까지 수평적 스캔
    • 인덱스 단일 스캔 : 수직적 탐색만으로 스캔
    • 인덱스 생략 스캔 : 선두 컬럼이 조건 절에 빠졌어도 인덱스 스캔
  • 명령어
    • CREATE INDEX : 인덱스 생성, UNION 생략 가능, 중복 값 허용 안함, 복수 컬럼을 인덱스로 걸 수 있다.
      • CREATE [UNION] INDEX name ON table name ~~
    • ALTER INDEX : 인덱스 수정, 일부 DBMS는 제공안함, 기존 인덱스 삭제 하고 신규 인덱스를 생성하는 방식으로 사용 권고
      • ALTER [UNION] INDEX name ON table name ~~
    • DROP INDEX : 삭제, DROP INDEX name;

(3) 명령어

- CREATE, ALTER, DROP, TRUNCATE

DML 

(1) 개념 : 데이터 조작어, 저장된 자료 입력, 수정, 삭제, 조회 언러

 

(2) 명령어 : SELECT, INSERT, UPDATE, DELETE

• SELECT : 내용 조회
  • select : 검색하고자 하는 속성명, 계산식 기술, 별칭 AS 생략가능, 2개 이상은 테이블.속성명, 술어는 ALL 기본값
    • ALL : 모든 튜플 검색, DISTINCT : 중복 제거
  • FROM : 포함하는 테이블 명 기술
  • WHERE : 검색할 조건
    • =, <>, !=, <, <=, >, >=, between a and b, in, not in,like, is null, is not null, and, or, not, !
  • GROUP BY : 속성값 그룹을 분류하고자 할 때 사용
  • HAVING 절 : 그룹에 대한 조건 지정
  • ORDER BY : 정렬 ASC 오름차순, DESC 내림차순, 지정안하면 ASC
  • 조인 개념 : 두 개 이상 연결해 검색, 하나의 릴레이션으로 데이터 연결
    • 내부 조인 : 공통 존재 컬럼의 값이 같은 경우 추출
    • 외부 조인 
      • 왼쪽 외부 조인 : 왼쪽 테이블 모든 데이터와 동일한 오른쪽 데이터 추출
      • 오른쪽 외부 조인 : 왼쪽과 반대
      • 완전 외부 조인 : 양쪽 모든 데이터 추출
    • 교차 조인 : 조인 조건 없는 모든 데이터 조합 추출
    • 셀츠 조인 : 자기 자신에게 별칭 주고 조인
  • 서브 쿼리 : 쿼리 문 안에 또 다른 쿼리, 알려지지 않은 기준을 위한 검색을 위해 사용, 메인과 서브는 주종 관계
    • 유형 - 위치 기준
      • FROM 절 서브 쿼리 : 서브 쿼리가 FROM 절 안에 존재, 인라인 뷰라 칭함, 뷰처럼 결과가 동적으로 생성된 테이블 형태
      • WHERE 절 서브 쿼리 : 서브 쿼리가 WHERE 절 안에 존재, 중첩 서브 쿼리라 불림
  • 집합 연산자 : 여러 질의 결과를 연결해 하나로 결합
    • union : 중복 레코드 제외 후 반환
    • union all : 중복 레코드 포함 후 반환
    • intersect : 중복 레코드만 포함
    • minus : 비교 레코드 제외 첫 쿼리에 있고 두 번째에 없는 결과 반환
• INSERT : 삽입, insert into name(option ~~) values (data ~~);
• UPDATE : 수정, update name set option=data ~~ where ~;
• DELETE : 삭제, delete from name where ~;

DCL

(1) 개념 : 데이터 제어어, 보안, 무결성 유지, 병행 제어, 회복 위한 관리자 DBA

• GRANT : 사용 권한 부여, grant permission on name to user;
• REVOKE : 권한 해제, revoke permission on name from user;