[7과목] SQL응용

트랜잭션(Transaction)
–. 데이터 베이스 시스템에서 하나의 논리적 정상적으로 수행하기 위한 작업의 기본단위

트랜잭션의 특성
원자성(Atomicity) : 트랜잭션을 구성하는 연산 전체가 모두 정상적으로 실행되거나 모두 취소되어야 하는 성질
일관성(Consistency) :고정요소는 트랜잭션이 수행 전과 트랜잭션 수행 완료 후의 상태가 같아야 하는 성질
격리성(Isolation) : 동시에 실행되는 트랜잭션들이 서로 영향을 미치지 않아야 한다는 성질
지속성(Durability) :성공한 트랜잭션의 결과는 영구적으로 데이터베이스에 저장되어야 하는 성질

TCL 명령어
Commit – 데이터베이스에 영구적으로 저장
Rollback – 트랜잭션 저장내용을 무효화시키는 명령어
CheckPoint – Rollback을 위한 시점을 지정하는 명령어
 
병행제어(Concurrency Control) 
-. 다수 사용자 환경에서 여러 트랜잭션을 수행할 때, 데이터 베이스 일관성 유지를 위해 상호작용은 제어하는 기법
 
병행 제어 미보장 시 문제점 (갱현 모연)
갱신 손실 (Lost Update) - 먼저 실행된 트랜잭션의 결과를 나중에 실행된 트랜잭션이 덮어쓸 때 발생하는 오류
현황 파악오류(Dirty Read) - 트랜잭션의 중간 수행 결과를 다른 트랜잭션이 참조하여 발생하는 오류
모순성(Inconsistency)  - 두 트랜잭션이 동시에 실행되어 데이터베이스의 일관성이 결여되는 오류
연쇄복귀(Cascading Rollback) - 복수의 트랜잭션이 데이터 공유 시 특정 트랜잭션이 처리를 취소할 경우 트랜잭션이 처리한 곳의 부분을 취소하지 못하는 오류 

로킹(Locking)
-. 다중 트랜잭션 환경에서 DB의 일관성과 무결성을 유지하기 위해 트랜잭션의
순차적 진행을 보장하는 직렬화 기법

회복 기법 종류
 
REDO - 시작과 완료에 대한 기록이 있는 트랜잭션들의 작업을 재작업하는 기법
UNDO - 시작은 있지만 완료 기록이 없는 트랜잭션들이 작업한 변경 내용들을 모두 취소하는 기법

로그 기반

지연 갱신 회복 기법( Deferred Update) : 트랜잭션이 완료되기 전까지 데이터베이스에 기록하지 않는 기법
즉각 갱신 회복 기법 (Immediate Update) :트랜잭션 수행 중 갱신 결과를 바로 DB에 반영하는 기법

체크 포인트 회복 기법 (Check Point Recovery) :장애 발생시 검사시점 이후에 처리된 트랜잭션에 대해서만 장애 발생 이전의 상태로 복원시키는 회복기법
그림자 페이징 회복 기법(Shadow Paging Recovery) :데이터베이스 트랜잭션 수행 시 복제본을 생성하여 데이터베이스 장애시 이를 이용해 복구하는 기법

데이터 정의어 (DDL) - 데이터를 정의하는 언어 ‘데이터를 담는 그릇을 정의하는 언어’

도메인(Domain) : 하나의 속성이 가질 수 있는 원자들의 집합
스키마(Schema) : 데이터베이스의 구조, 제약조건 등의 정보를 담고 있는 기본적인 구조
외부 스키마(External Schema) : 사용자나 개발자의 관점에서 필요로 하는 데이터베이스의 논리적 구조, 사용자 뷰, 서브 스키마
개념 스키마(Conceptual Schema) : 데이터베이스의 전체적이 논리구조, 전체적 뷰, 개체 간의 관계, 제약조건, 접근 권한, 무결성 보안에 대한 정의
내부 스키마 (Internal Schema) : 물리적 저장장치의 관점에서 보는 데이터베이스의 구조
테이블 (Table) :데이터 저장 공간
뷰 (View) : 하나 이상의 물리 테이블에서 유도되는 가상의 테이블
인덱스 (Index) : 검색을 빠르게 하기 위한 데이터 구조

DDL 명령어
생성 – CREATE , 수정 – ALTER (ADD, MODIFY, DROP) , 삭제 – DROP , 데이터 삭제 – TRUNCATE
CASECADE – 참조하는 테이블까지 연쇄적으로 제거하는 옵션
RESTRICT – 다른 테이블이 삭제할 테이블을 참조 중이면 제거하지 않는 옵션

데이터 조작어 (DML) – 데이터베이스에 저장된 자료들을 입력,수정,삭제, 조회하는 언어
집합 연산자 (Set Operator) : 두 테이블 연산에 집합 연산자를 사용하는 방식

UNION – 중복 행이 제거된 쿼리 결과 반환
UNION ALL – 중복 행이 제거되지 않은 쿼리 결과 반환
INTERSECT – 두 쿼리 결과에 공통적으로 존재하는 결과를 반환하는 집합 연산자
MINUS – 첫 쿼리에 있고 두번째 쿼리에 없는 결과하는 반환하는 집합 연산자

데이터 제어어 (DCL)
– 데이터베이스 관리자가 데이터 보안, 무결성 유지, 병행 제어, 회복을 위해 관리자가 사용하는 제어용 언어

GRANT – 관리자가 사용자에게 권한을 부여
Ex) GRANT SELECT ON 사원 TO 사용자 (WITH 사용시 권한을 부여받은 조건을 다른사람에게 부여 할 수 있음)
REVOKE – 관리자가 사용자에게 갖고 있는 권한을 회수
Ex) REVOKE SELECT ON 사원 FROM 사용자

다중 행 연산자 - 여러 개의 튜블을 반환하는 다중 행 서브쿼리에서 사용
IN - 리턴되는 값 중에서 조건에 해당하는 값이 있으면 참
ANY - 서브쿼리에 의해 리턴되는 각각의 값과 조건을 비교하여 하나 이상을 만족하면 참 (=SOME)
ALL - 값을 서브쿼리에 의해 리턴되는 모든 값과 조건 값을 비교하여 모든 값을 만족해야지만 참
EXISTS - 메인 쿼리의 비교 조건이 서브쿼리의 결과 중에서 만족하는 값이 하나라도 존재하면 참

집계 함수 - 여러 행과 테이블 전체로부터 하나의 결과값을 반환
GROUP BY 문 사용
COUNT, SUM, AVG, MAX, MIN STDDEV(표준편차 계산), VARIAN (분산 계산)

그룹 함수 – 소 그룹간의 중간 합계, 소계를 계산하여 산출하는 함수
ROLLUP : 소계, 중간 집계 값 산출
CUBE : 다차원 집계 생성
GROUPING SET : 컬럼에 대한 개별 집계

윈도 함수 – 데이터베이스를 사용한 온라인 분석 처리 용도로 사용하기 위해 SQL에 추가한 함수  SELECT 함수명(파라미터) OVER PARTITION BY ORDER BY

순위함수
RANK -  1 2 2 4 5
DENSE_RANK – 1 2 2 3 4
ROW_NUMBER – 1 2 3 4 5
 
프로시저 (Procedure) : 일련의 쿼리들을 마치 하나의 함수처럼 실행하기 위한 쿼리의 집합

DECLARE – 프로시저 선언부 (변수와 인수 데이터 타입 등)
BEGIN/END – 프로시저 시작과 종료
CONTROL – 제어부(조건/반복), 순차적 처리
SQL – DML, DQL
EXCEPTION – 예외사항 처리
TRANSCATION – DML 수행 취소 여부 (COMMIT, ROLLBACK)


사용자 정의 함수 (User-Defined Function) :사용자가 정의하여 SQL문 처리 수행 후 단일 값을 반환하는 절차형 SQL

DECLARE – 프로시저 선언부 (변수와 인수 데이터 타입 등)
BEGIN/END – 프로시저 시작과 종료
CONTROL – 제어부(조건/반복), 순차적 처리
SQL – DML, DQL
EXCEPTION – 예외사항 처리
RETURN – 단일 값 반환


트리거 (Trigger) : 특정 테이블 수정, 삭제 삽입 등의 데이터 이벤트 발생시 자동으로 DBMS에서 실행 되도록 구현된 프로그램

트리거 종류
행 트리거 : 데이터 변화시 실행
문장 트리거 : 트리거에 의해 한번 실행

DECLARE – 프로시저 선언부 (변수와 인수 데이터 타입 등)
EVENT – 이벤트 부 (FOR EACH ROW)
BEGIN/END – 프로시저 시작과 종료
CONTROL – 제어부(조건/반복), 순차적 처리
SQL – DML, DQL
EXCEPTION – 예외사항 처리

옵티마이저 (Optimizer)
-. 옵티마이저는 SQL을 가장 빠르고 효율적으로 수행할 최적의 처리경로를 생성해주는 DBMS 내부의 핵심엔진
-. 옵티마이저가 생성한 SQL 처리경로를 실행계획(Execution Plan)이라고 부른다.

유형
-.규칙기반 옵티마이저 (RBO) - Rule
–> 통계 정보가 없는 상태에서 사전 등록된 규칙에 따라 질의 실행 계획을 선택하는 옵티마이저
-. 비용기반 옵티마이저 (CBO) - Cost
–> 통계 정보로부터 모든 접근 경로로 고려한 질의실행 계획을 선택하는 옵티마이저

힌트(Hint)
– 실행하려 하는 SQL 문에 사전에 정보를 주어 SQL 문 실행에 빠른 결과를 가져오는 효과를 만드는 문법