정보처리기사 실기 - SQL

SQL

데이터베이스 기본

트랜잭션(Transaction)

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

트랜잭션 특성

• 원자성(Atomicity) : 트랜잭션의 연산 전체가 성공 또는 실패되어야 하는 성질 (All or Nothing)
• 일관성(Consistency) : 트랜잭션 수행 전과 트랜잭션 수행 완료 후의 상태가 같아야 하는 성질
• 격리성(Isolation) : 동시에 실행되는 트랜잭션들이 서로 영향을 미치지 않어야 한다는 성질
• 영속성(Durability) : 성공이 완료된 트랜잭션의 결과는 영속적으로 데이터베이스에 저장되어야 하는 성질

트랜잭션 상태 변화

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

트랜잭션 제어어(TCL, Transaction Control Language)

• 트랜잭션의 결과를 허용하거나 취소하는 목적으로 사용되는 언어
• TCL 명령어
  • COMMIT: 트랜잭션을 메모리에 영구적으로 저장하는명령어
  • ROLLBACK: 트랜잭션 내역의 저장을 무효화시키는 명령어
  • CHECKPOINT(SAVEPOINT): ROLLBACK을 위한 시점을 지정하는 명령어

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

• DB를 구축하거나 수정할 목적으로 사용하는 언어

DDL 대상

• 도메인(Domain): 하나의 속성이 가질 수 있는 원자값들의 집합
• 스키마(Schema): 데이터베이스의 구조, 제약조건 등의 정보를 담고 있는 기본적인 구조
• 외부 스키마, 개념 스키마, 내부 스키마
• 테이블(Table): 데이터 저장 공간
• 뷰(View): 하나 이상의 물리 테이블에서 유도되는 가상의 테이블
• 인덱스(Index): 검색을 빠르게 하기 위한 데이터 구조
  • 순서 인덱스(Ordered Index): 데이터가 정렬된 순서로 생성되는 인덱스
  • 해시 인덱스(Hash Index): 해시 함수에 의해 직접 데이터에 키 값으로 접근하는 인덱스
  • 비트맵 인덱스(Bitmap Index): bit 값인 0 또는 1로 변호나하여 인덱스 키로 사용하는 인덱스
  • 함수기반 인덱스(Functional Index): 수식이나 함수를 적용하여 만든 인덱스
  • 단일 인덱스(Single Index): 하나의 컬럼으로만 구성한 인덱스
  • 결합 인덱스(Concatenated Index): 두 개 이상의 컬럼으로 구성한 인덱스
  • 클러스터드 인덱스(Clustered Index): 인덱스 키의 순서에 따라 데이터가 정렬되어 저장되는 방식 (검색 빠름)
  • 넌클러스터드 인덱스(Non-Clustered Index): 인덱스의 키 값만 정렬되어 있고 실제 데이터는 정렬되지 않는 방식 (데이터 삽입, 삭제 시 데이터 재정렬해야함)

DDL 명령어: CREATE(생성), ALTER(수정), DROP(삭제)

• CASCADE: 제거할 요소를 참조하는 다른 모든 개체를 함께 제거
• RESTRICT: 다른 개체가 제거할 요소를 참조중일 떄는 제거를 취소

데이터 조작어(DML: Data Manipulation Language)

• 저장된 데이터를 실질적으로 관리하는데 사용되는 언어
• DML 유형
  • SELECT(조회), INSERT(삽입), UPDATE(수정), DELETE(삭제)

데이터 제어어(DCL: Data Control Language)

• 데이터의 보안, 무결성, 회복, 병행 제어등을 정의하는데 사용하는 언어
• DCL 유형
  • GRANT: 사용 권한 부여
  • REVOKE: 사용 권한 취소

병행제어(Concurrency Control)

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

목적

• 데이터베이스의 공유를 최대화
• 시스템의 활용도를 최대화
• 데이터베이스의 일관성을 유지
• 사용자에 대한 응답시간을 최소화

기법

• 로킹(Locking)
  • 같은 자원을 액세스하는 다중 트랜잭션 환경에서 DB의 일관성과 무결성을 유지하기 위해 트랜잭션의 순차적 진행을 보장하는 직렬화 기법
• 낙관적 검증
  • 트랜잭션이 어떠한 검증도 수행하지 않고 일단 트랜잭션을 수행하고, 트랜잭션 종료 시 검증을 수행하여 데이터베이스에 반영하는 기법
• 타임 스탬프 순서(Time Stamp Ordering)
  • 트랜잭션과 트랜잭션이 읽거나 갱신한 데이터에 대해 트랜잭션이 실행을 시작하기 전에 타임 스탬프(Time Stamp)를 부여하여 부여된 시간에 따라 트랜잭션 작업을 수행하는 기법
• 다중버전 동시성 제어(MVCC; Multi Version Concurrency Control)
  • 트랜잭션의 타임 스탬프와 접근하려는 데이터의 타임 스탬프를 비교하여 직렬가능성이 보장되는 적절한 버전을 선택하여 접근하도록 하는 기법

미보장 시 문제점(갱현모연)

• 갱신 손실(Lost Update)
  • 먼저 실행된 트랜잭션의 결과를 나중에 실행된 트랜잭션이 덮어쓸 때 발생하는 오류
• 현황 파악오류(Dirty Read)
  • 트랜잭션의 중간 수행 결과를 다른 트랜잭션이 참조하여 발생하는 오류
• 모순성(Inconsistency)
  • 두 트랜잭션이 동시에 실행되어 데이터베이스의 관성이 결여되는 오류
• 연쇄복귀(Cascading Rollback)
  • 복수의 트랜잭션이 데이터 공유 시 특정 트랜잭션이 처리를 취소할 경우 트랜잭션이 처리한 곳의 부분을 취소하지 못하는 오류

데이터 분석 함수 종류

• 집계 함수: 여러 행 또는 테이블 전체 행으로부터 하나의 결괏값을 반환하는 함수
• 그룹 함수: 소그룸 간의 소계 및 중계 등의 중간 합계 분석 데이터를 산출하는 함수
• 윈도 함수(Window Function): 데이터베이스를 사용한 온라인 분석 처리 용도로 사용하기 위해서 표준 SQL에 추가된 기능

절차형 SQL(Procedural SQL)

• SQL언어에서도 절차 지향적인 프로그램이 가능하도록 하는 트랜잭션 언어

절차형 SQL 종류

• 프로시저(Procedure): 일련의 쿼리들을 마치 하나의 함수처럼 실행하기 위한 커리의 집합
• 사용자 정의 함수(User-Defined FUnction): SQL 처리를 수행하고, 수행 결과를 단일 값으로 반환할 수 있는 절차형 SQL
• 트리거(Trriger): 데이터베이스 시스템에서 삽입, 갱신, 삭제 등의 이벤트가 발생할 때마다 관련 작업이 자동으로 수행되는 절차형 SQL

성능 개선, 옵티마이저, 힌트

• 쿼리 성능 개선
  • 최소의 시간으로 원하는 결과를 얻도록 프로시저를 수정하는 작업
• SQL 성능 개선 절차
  • 문제있는 SQL식별 → 옵티마이저 통계확인 → SQL문 재구성 → 인덱스 재구성 → 실행계획 유지관리
• 옵티마이저(Optimizer)
  • SQL이 가장 효율적으로 수행되도록 최적의 경로를 찾아 주는 모듈
• 옵티마이저 유형
  • 규칙기반 옵티마이저(RBO): 사전에 정의해둔 규칙에 의거하여 경로를 찾는 규칙 기반 옵티마이저
  • 비용기반 옵티마이저(CBO): 각 DBMS마다 고유의 알고리즘에 따라 산출되는 비용으로 최적의 경로를 찾는 비용기반 옵티마이저
• 힌트(Hint): 실행하려는 SQL문에 사전에 정보를 주어서 SQL문 실행에 빠른 결과를 가져오는 효과를 만드는 문법
  • 옵티마이저의 실행 계획을 원하는 대로 변경할 수 있게 한다.
  • 옵티마이저는 명시적인 힌트를 통해 실행 계획을 변경한다.

기타

데이터베이스 회복 기법(회로체그)

• 로그 기반 회복 기법
• 체크 포인트 회복 기법
• 그림자 페이징 회복 기법

---

-- sql 예제

-- DML
SELECT RANK() OVER (PARTITION BY [], ORDER BY [])

UPDATE 테이블 SET 열 = 변경할 값 WHERE [조건]

DELETE FROM 테이블 WHERE [조건]

INSERT INTO 테이블 (열1,열2, …) VALUE(값1, 값2, ...);


-- DDL
CREATE INDEX 인덱스 ON 열1(복합인덱스1, 복합인덱스2, ..);

ALTER TABLE 테이블 MODIFY 컬럼명 데이터타입 [제약조건];
ALTER TABLE 테이블 ADD 컬럼명 데이터타입 CHECK(컬럼명 IN('a','b',...)); -- a,b,... 만
ALTER TABLE 테이블 DROP 컬럼명;

-- DCL
GRANT 권한 ON 테이블 TO 사용자 [WITH GRANT ONPION]
	-- WITH GRANT ONPION : 다른 사용자에게 권한을 부여할 수 있는 권한까지 부여
REVOKE 권한 ON 테이블 FROM 사용자 [CASCADE CONSTRAINTS];
	-- CASCADE CONSTRAINTS : 연쇄적인 권한 해제(WITH GRANT OPTION 부여까지 회수)

-- 기타
TRUNCATE TABLE 테이블; -- 데이터 삭제[DDL] (== DELETE FROM []; [DML])
				   -- DROP TABLE []은 테이블 전체가 사라지지만
				   -- TRUNCATE TABLE []은 내용만 지우고 구조는 남음

GROUP BY ROLLUP(학년, 반)		--: 부분 집계, 총 집계
		CUBE(학년, 반)          --: 다차원 집계 생성 / 모든 경우의 수에 대한 소계, 총계
		GROUPING SETS(학년, 반)	--: 부분, 총 집계 선택해서

ROLLUP, CUVE, GROUPING SETS 자세히