본문 바로가기
공부/정보처리기사

정처기 데일리공부 04 - SQL응용, 트랜잭션, DDL, DML, DCL

by 매니션 2021. 7. 1.

7단원 SQL 응용

1. 트랜잭션

  • 데이터베이스 시스템에서 하나의 논리적 기능을 정상적으로 수행하기 위한 작업의 기본 단위
  • 트랜잭션의 특성(ACID)
    • Atomicity (원자성)
      • 분해가 불가능한 작업의 최소 단위
      • 연산 전체가 성공 또는 실패(All or Nothing) / Commit and Rollback / 회복성보장
    • Consistency (일관성)
      • 트랜잭션이 실행 성공 후 항상 일관된 데이터베이스 상태를 보존해야 함
    • Isolation (격리성,고립성)
      • 트랜잭션 실행 중 생성하는 연산의 중간 결과를 다른 트랜잭션이 접은 불가
    • Durability (영속성)
      • 성공이 완료된 트랜잭션의 결과는 영속적으로 데이터베이스에 저장
  • 고립화 수준
    • 현재의 데이터에 대해 무결성을 해치지 않기 위해 잠금을 설정하는 정도
    • 고립화 수준 종류 (RRRS)
      • Read Uncommitted : 모두 읽기 허용
      • Read Committed : 연산 완료될때까지 읽기 제한
      • Repeatable Read : 종료시까지 갱신.삭제 제한
      • Serializable Read : 모두 제한
  • 트랜잭션의 상태변화 (활부완실철)
    • 활동,활성 / 부분완료 / 완료 / 실패 / 철회
  • 병행제어 (갱현모연)
    • 갱신손실 / 현황파악 오류 / 모순성 / 연쇄복귀
  • 병행제어 기법 (로 낙타다)
    • 로킹 : 같은 자원을 액세스하는 다중 트랜잭션 환경에서 DB의 일관성과 무결성을 유지하기 위해 순차적 진행을 보장하는 직렬화 기법
    • 낙관적검증 : 일단 수행 후, 트랜잭션 종료시 검증을 수행하여 데이터베이스에 반영
      • 읽기 > 검증 > 쓰기
    • 타임스탬프 순서 : 특정 시점에 타임스탬프를 부여해 시간에 따라 작업을 수행
    • 다중버전동시성제어(MVCC) : 트랜잭션의 타임스탬프와, 데이터의 타임스탬프를 비교하여 직렬 가능성이 보장되는 적절한 버전을 선택하여 접근
  • 회복기법
    • 트랜잭션을 수행하는 도중 장애로 손상된 데이터베이스를 이전 상태로 복구
    • 회복기법종류 (회로체크)
      • 로그기반 (즉시갱신기법 / 지연갱신)
      • 체크포인트
      • 그림자 페이지

2. SQL문법

DDL (데이터 정의어)

  • 데이터를 정의하는 언어
  • 테이블이나 관계의 구조를 생성
  • DDL 명령어 : CREATE / ALTER / DROP / TRUNCATE (크알드트)
    • CREATE
    • CREATE TABLE 사람 ( 이름 VARCHAR(10) 성별 CHAR(1) CHECK (성별 = 'M' OR 성별 = 'F') );
    • ALTER
      • 추가 : ADD
      • 수정 : MODIFY
      • 삭제 : DROP
      • 컬럼명 수정 : RENAME COLAUM
      ALTER TABLE 테이블명 ADD 컬럼명 데이터타입;
      ALTER TABEL 테이블명 MODIFY 컬럼명 데이터타이베[디폴트값][NOT_NULL];
      ALTER TABLE 테이블명 DROP 컬럼명;
      ALTER TABLE 테이블명 RENAME COLUMN 변경전컬럼명 TO 변경후컬럼명
    • DROP : 테이블을 삭제
    • DROP TABLE 테이블명 [CASCADE] CASCADE : 참조하는 테이블까지 연쇄적으로 제거(전체제거) RESTRICT : 참조중이면 제거하지 않음
  • TRUNCATE : 테이블내 데이터를 삭제
  • TRUNCATE TABLE 사원;
  • DDL의 대상 (도스테뷰인)
    • 도메인 : 하나의 속성이 가질 수 있는 원자값들의 집합
    • 스키마 : 데이터베이스의 구조, 제약조건 등의 정보를 담고있는 기본적인 구조(외부,개념,내부 스키마)
    • 테이블 : 데이터 저장 공간
    • 뷰 : 하나 이상의 물리 테이블에서 유도되는 가상의 테이블
    • 인덱스 : 검색을 빠르게 하기 위한 데이터 구조
  • 스키마의 유형 (외개내)
    • 외부스키마 : 사용자나 개발자의 관점에서 필요로하는 논리적 구조(사용자 뷰)
    • 개념스키마 : 전체적인 논리적 구조. 개체 간의 관계, 권한 등을 정의(전체적인 뷰)
    • 내부스키마 : 물리적 저장장치(하드웨어) 관점에서 보는 데이터베이스 구조
  • 테이블의 용어 (논리/물리)
    • 튜플/행(가로) : 레코드(논리적구조) 라도고하며 같은 값은 가질 수 없음.
    • 애트리뷰터트 / 열 : 테이블의 열
    • 식별자 : 구분할 수 있는 논리적 개념
    • 카디널리티 : 튜플의 개수(세로) - 5명의 정보(5개)
    • 차수 : 애트리뷰트의 개수(가로) - 학번/이름/학과(3개) = 스키마
    • 도메인 : 하나의 애트리뷰트가 취할 수 있는 같은 타입의 원자값들의 집합 (학번에는 숫자만)
    • 논리테이블로서 사용자에게 테이블과 동일
    • 논리적 데이터 독립성 제공 / 조작연산 간소화 / 보안기능(접근제어) 제공 / 뷰 변경 불가
    • 뷰 명령어
      • 컬럼 전체 : SELECT * FROM A
      • 컬럼 일부 : SELECT 컬럼1, 컬럼2 FROM A
      • 조인 : FROM A,B
    • 뷰 생성 (VIEW 뷰이름 AS)
    • CREATE VIEW 뷰이름 AS CREATE OR REPLACE VIEW 뷰이름 AS //뷰를 교체
  • 인덱스
    • 종류 ( 순해비함 단결클)
      • 순서인덱스 / 해시인덱스 / 비트맵인덱스 / 함수기반 인덱스 / 단일 인덱스 / 결합인덱스 / 클러스터드 인덱스
    • 생성 : CREATE [UNIQUE] INDEX 인덱스명 ON 테이블명(컬럼명);
      • [UNIQUE] : 중복 비허용
    • 삭제 : DROP INDEX 인덱스명;
    • 변경 : ALTER[UNIQUE] INDEX 인덱스명 ON 테이블명(컬럼명);
    • 스캔 방식(범전단생)
    • 인덱스 범위 스캔 / 인덱스 전체 스캔 / 인덱스 단일 스캔 / 인덱스 생략 스캔
    • 인덱스 생성(INDEX 인덱스명 ON)
    • CREATE [UNIQUE] INDEX 인덱스명 ON 테이블명(컬럼명)

DML (데이터 조작어)

  • 데이터베이스에 저장된 자료들을 입력 / 수정 / 삭제 / 조회하는 언어 (CRUD)
  • SELECT / INSERT / UPDATE / DELETE (세인업데)
  • SELECT = 질의어(Query)
  • SELECT 명령문 ( 셀프웨 구해오)
    • SELECT절
      • 검색하고자 하는 속성명, 계산식
      • ALL(기본값) / DISTINCT 속성명 / DISTINCTROW(중복제거)-튜플전체
    • FROM절 : 데이터를 포함하는 테이블명
    • WHERE절 : 검색할 조건
      • 범위 : 속성 BETWEEN 100 AND 200 (크거나같고, 작거나 같음)
      • 집합 : 속성 IN (40,50,60)
      • 패턴 : 속성 LIKE '한%' (한 으로 시작하는 문자열)
        • % : 한%(한으로 시작) / %이(이로 끝남)
        • [ ] : 1개 일치 [0-8] 0~8사이 숫자로 시작하는 문자
        • [^] : 1개불일치 [ ^0-8] 0~8사이 숫자로 시작하지 않는 문자
        • _ : 특정위치에 1개 일치 _동%
      • NULL : 속성 IS NULL (NULL인 데이터만 가져옴 )
      • 복합 : (속성<100) AND (속성2 LIKE '한%')
    • GROUP BY절 : 속성값을 그룹으로 분류
    • HAVING절 : GROUP BY에 의해 분류 후 그룹에 대한 조건(GROUP BY절과 세트)
    • ORDER BY절
      • 속성값 정렬
      • ASC : 오름차순(1,2,3,4) / DESC : 내림차순(4,3,2,1)
  • SELECT FROM WHERE GROUP BY HAVING ORDER BY
  • 조인 : 두 개 이상의 테이블을 연결하여 데이터를 검색하는 방법
    • 논리적 조인(내외교셀)
      • 내부조인(Inner) / 외부조인(Outer) / 교차조인(Cross) / 셀프조인(Self)
      • 내부조인(SELECT, FROM, JOIN,ON)
      SELECT A.컬럼1, A.컬럼2
      FROM 테이블1 A [INNER] JOIN 테이블2 B ON 조인조건
      [WHERE 검색조건];
    • 물리적 조인(네소해)
      • 중첩 반복 조인(Nested loop join)
      • 정렬 합병 조인(Sort merge join)
      • 해시 조인(Hash join)
  • 서브쿼리 : SQL문 안에 포함된 또다른 SQL문
    • SELECT절 서브쿼리(=스칼라 서브쿼리), 단일행 리턴
    • FROM절 서브쿼리(=인라인 뷰), 테이블 형태로 사용 가능
    • WHERE절 서브쿼리(=중첩 서브쿼리)
  • 집합연산자 : 두 개 이상의 테이블에서 여러개의 질의의 결과를 연결하여 하나로 결합하는 연산자 (유유인마)
    • UNION : 중복제거
    • UNION ALL : 중복허용
    • INTERSECT : 교집합
    • MINUS : 차집합
  • INSERT : 데이터 내용을 삽입
  • INSERT INTO 테이블명(속성명1, 속성명2) VALUES(데이터1, 데이터2)
  • UPDATE : 데이터 내용을 변경(업셋웨)
  • UPDATE 테이블명 SET 속성명 = 데이터 WHERE 조건;
  • DELETE : 데이터 내용을 삭제(델프웨)
  • DELETE FROM 테이블명 WHERE 조건;

DCL (데이터 제어어)

  • 데이터베이스 관리자(DBA)가 보안, 무결성 유지, 병행 제어, 회복을 위해 DBA가 사용하는 제어용 언어 (보무병회)
  • DCL 유형
    • GRANT : 사용권한 부여
      • 시스템 권한 : GRANT 권한 TO 사용자 (큰 범위)
      • 객체 권한 : GRANT 권한 ON 테이블 TOP 사용자 (테이블 수정)
      • 다른 사용자에게 권한을 부여할 수 있는 권한(매니저권한) : WITH GRANT OPTION
    • REVOKE : 사용권한 회수
      • 시스템 권한 : REVOKE 권한 FROM 사용자;
      • 객체권한 : REVOKE 권한 ON 테이블 FROM 사용자;
      • 매니저가 부여한 권한 모두 회수 : CASCADE CONSTRAINT;
  • DCL / TCL
    • COMMIT / ROLLBACK / SAVEPOINT
반응형

댓글