[정보처리기사] 7. SQL 응용

SQL 응용

트랜잭션 - 하나의 논리적 기능을 수행하기 위한 작업의 기본 단위

 

트랜잭션 특성 ACID - 원자성, 일관성, 격리성, 지속성(영속성)

- 원자성(Atomicity) : 연산 전체가 성공 또는 실패 All or Nothing

- 일관성(Consistency) : 트랜잭션 실행 후 항상 일관된 DB 상태를 보존

- 격리성(Isolation) : 트랜잭션 실행 중 다른 트랜잭션이 접근 불가

- 지속성(Durability) : 성공한 트랜잭션은 영구적으로 DB에 저장

 

트랜잭션 상태 - 활부완실철

- 활동 : 실행 중

- 부분완료 : 마지막 명령문 실행 후 상태

- 완료 : 성공적으로 완료된 후 가지는 상태

- 실패 : 정상적 실행 불가 상태

- 철회 : DB가 트랜잭션 시작 전 상태로 되돌아간 상태

 

트랜잭션 제어어(TCL) - 커밋, 롤백, 체크포인트

- 커밋 : 트랜잭션을 메모리에 영구적으로 저장하는 제어어

- 롤백 : 트랜잭션을 저장 무효화시키는 제어어

- 체크포인트 : 롤백을 위한 시점을 지정하는 제어어

- 병행 제어 : 다수 사용자 환경에서 여러 트랜잭션을 실행할 때 일관성 유지 기법

 

병행 제어 미보장시 문제점 : 갱신 손실, 현황파악오류, 모순성, 연쇄복귀

 

병행 제어 기법 (로낙타다) - 로킹, 낙관적 검증, 타임스탬프, 다중 버전 동시성 제어

- 로킹 : 트랜잭션의 순차적 진행을 보장하는 직렬화 기법

- 낙관적 검증 : 트랜잭션 종료 시 검증 수행하여 DB에 반영

- 타임스탬프 : 트랜잭션 실행 전 타임스탬프를 부여 부여된 시간에 따라 트랜잭션 작업을 수행하는 기법

- 다중버전동시성제어 MVCC : 트랜잭션의 타임스탬프들을 비교하여 직렬가능성이 보장되는 버전을 선택하여 접근

 

고립화 수준 종류

- Read Uncommitted : 연산 중인 데이터를 다른 트랜잭션이 읽는 것을 허용

- Read Committed : 연산을 완료할 때까지 대상 데이터 읽기를 제한

- Repeatable Read : 선행 트랜잭션이 특정 데이터를 읽을 때 종료 시까지 해당데이터 갱신, 삭제 제한

- Serializable Read : 선행 트랜잭션이 특정 데이터를 순차적으로 읽을 때 해당 데이터에 대한 접근 제한

 

회복 기법

- 로그 기반 회복 기법 : 지연 갱신, 즉각 갱신

- 체크포인트 회복 기법 : 장애 발생 시 장애 발생 이전 상태로 복원

- 그림자 페이징 회복 기법 : DB 복제본을 생성하여 장애 시 복제본으로 복원

 

DDL - 데이터 정의어 : 데이터 구조를 정의하는 언어

 

DDL의 대상  (도스테뷰인)

- 도메인 : 하나의 속성이 가질 수 있는 원자값들의 집합

- 스키마 : 테이블의 구조 및 제약조건 등의 정보를 담고 있는 기본 구조

- 테이블 : 데이터 저장 공간

- 뷰 : 하나 이상의 물리 테이블에서 유도되는 가상의 테이블

- 인덱스 : 검색을 빠르게 하기 위한 자료구조

 

스키마 종류 (외부, 개념, 내부)

- 외부 스키마 : 사용자나 개발자의 관점의 데이터베이스의 논리적 구조, 사용자 뷰, 서브 스키마

- 개념 스키마 : 데이터베이스의 전체적인 논리적 구조, 전체적인 뷰를 나타냄

- 내부 스키마 : 물리적 저장장치의 관점에서 보는 데이터베이스 구조

 

뷰(View) : 논리 테이블, 사용자에게 테이블과 동일(사용 관점)

 

뷰의 특징

- 데이터 독립성 제공

- 조작 연산 간소화

- 보안 기능 제공

- 뷰 변경 불가

 

뷰의 목적 : 단순 질의어를 사용할 수 있다

 

인덱스 : 테이블에 대한 조회속도를 높여주는 자료구조

 

DDL 명령어

- CREATE TABLE : 생성

- ALTER TABLE : 수정 (ADD, DROP, MODIFY)

- DROP TABLE : 전체 삭제 CASCADE (참조 테이블까지 삭제), RESTRICT (참조 중이면 미삭제)

- TRANCATE TABLE - 테이블 내의 데이터만 삭제

 

TABLE 제약조건

- PRIMARY KEY : 기본키

- FOREIGN KEY REFERENCES : 외래키

- UNIQUE : 유일 값

- NOT NULL : 널 값 미포함

- CHECK('M' or 'F')

- DEFAULT

 

VIEW 관련 DDL VIEW AS

CREATE VIEW 뷰이름 AS 조회쿼리

CREATE OR REPLACE VIEW 뷰이름 AS 조회쿼리 - OR REPLACE 뷰를 변경

DROP VIEW 뷰이름

 

INDEX 관련 DDL INDEX ON

- CREATE INDEX 인덱스명 ON 테이블(컬럼)

- ALTER INDEX 변경인덱스명 ON 테이블(컬럼)

- DROP INDEX

 

DML 데이터 조작어

- Select : 조회

- Insert : 입력

- Update : 수정

- Delete : 삭제

 

SELECT 명령어 (셀프웨그해오)

 

SELECT 절

-DISTINCT : 중복제거

 

WHERE 절 조건

- 비교 : 동일 = 값이 다른 <>,!=

- 범위 : 컬럼 BETWEEN 값1 AND 값2, 왼쪽 값보다 크거나 작고 오른쪽 값보다 작거나 같은

- 집합 : IN, NOT IN 컬럼 IN (값1)

- 패턴 : LIKE , [], [^], _ 

- NULL : IS NULL, IS NOT NULL

- 복합조건 : AND, OR, NOT

 

GROUP BY 절 : 속성 값을 그룹으로 분류하고자 할 때

 

HAVING 절 : GROUP BY에 의해 분류한 후 그룹에 대한 조건을 지정

 

ORDER BY 절 : 속성값을 정렬하고자 할 때 사용, ASC : 오름차순, DESC : 내림차순

 

조인 개념 : 두 개 이상의 테이블을 연결하여 데이터를 검색하는 방법

 

조인 종류 JOIN ON

- 내부 조인 : 컬럼의 값이 같은 경우를 추출하는 기법 왼쪽

- 외부 조인 : 왼쪽 테이블의 모든 데이터와 오른쪽 테이블의 동일 데이터를 추출

- 오른쪽 외부 조인 : 오른쪽 테이블의 모든 데이터와 왼쪽 테이블의 동일 데이터를 추출

- 완전 외부 조인 : 양쪽의 모든 데이터를 추출하는 기법

- 교차 조인 : 모든 데이터 조합을 추출하는 기법

- 셀프 조인 : 자기 자신에게 별칭을 지정한 후 다시 조인하는 기법

 

서브 쿼리 : SQL 문 안에 포함된 또 다른 SQL문이다

- 메인쿼리와 서브쿼리 관계는 주종 관계로서, 서브쿼리에 사용되는 컬럼 정보는 메인 쿼리의 컬럼 정보를 사용할 수 있다.

- 셀렉 절 서브쿼리 : 스칼라 서브쿼리, 단일 행을 리턴해야 함, 집계함수가 많이 쓰임

- 프롬 절 서브쿼리 : 인라인 뷰, 뷰처럼 결과가 동적으로 생성된 테이블 형태로 사용할 수 있다

- 웨얼 절 서브쿼리 : 중첩 서브쿼리라고 불림

 

집합연산자 : 테이블을 집합 개념으로 보고, 두 테이블 연산에 집합 연산자를 사용하는 방식

*여러 질의 결과를 연결하여 하나로 결합하는 방식을 사용한다.

- UNION : 중복 행이 제거된 쿼리 결과를 반환

- UNION ALL : 중복 행이 제거되지 않은 쿼리 결과를 반환하는 연산자

- NTERSECT : 두 쿼리 결과에 공통적으로 존재하는 결과를 반환하는 연산자

- MINUS : 첫 쿼리에는 있고 두 번째 쿼리에는 없는 결과를 반환하는 연산자

 

데이터 제어어(DCL) : 데이터 보안, 무결성 유지, 병행 제어, 회복을 위해 DBA가 사용하는 제어용 언어

- GRANT 권한 ON 테이블 TO 사용자: 사용 권한 부여

- REVOKE 권한 ON 테이블 FROM 사용자: 사용 권한 취소

 

데이터 분석 함수 : 복수 행 기준의 데이터를 모아서 처리하는 것을 목적으로 하는 다중 행 함수

- 복수 행을 그룹별로 모아놓고 그룹당 단일 계산 결과를 반환한다.

- GROUP BY 구문을 활용하여 복수행을 그룹핑한다

- SELECT, HAVING, ORDER BY 등의 구문에 활용한다

 

집계함수 : 여러 행으로부터 하나의 결괏값을 반환하는 함수 집계 함수 구문 (셀프웨그해)

GROUP BY 구문 -WHERE 구문은 GROUP BY 보다 먼저 실행되고 대상이 되는 단일 행을 사전에 선별하는 역할

HAVING 구문 -집계 함수의 구문을 적용하여 복수행의 계산 결과를 조건별로 적용하는 데 사용된다.

집계 함수 종류 -COUNT : 줄 수를 반환 -SUM : 컬럼 간의 합계 -AVG : 평균을 계산 -MAX/MIN : 최댓값, 최솟값 -STDDEV/VARIAN : 표준편차 / 분산

 

그룹 함수 유형

- ROLLUP 함수 : 소그룹의 합계와 중간 집계 값을 산출하기 위한 그룹 함수

- SELECT 뒤에 포함되는 컬럼이 GROUPBY 또는 ROLLUP 뒤에 기재되어야 한다

 

CUBE 함수 : 결합 가능한 모든 값에 대해 다차원 집계를 생성하는 그룹 함수이다.

GROUPING SETS 함수 : 집계 대상 컬럼들에 대한 개별 집계를 구할 수 있다. 컬럼 순서와 무관한 결과를 얻을 수 있다.

윈도 함수 : 데이터 베이스를 사용한 온라인분석처리(OLAP) 용도로 사용하기 위해서 표준 SQL 추가된 함수

 

순위 함수

- RANK : 순위를 구하는 함수, 1위, 2위, 2위 ,4위, 5위

- DENSE_RANK : 순위 구하는 함수 1위,1위,1위, 2위, 3위, 4위

- ROW_NUMBER: 1위,2위,3위,4위

행 순서 함수

- FIRST_VALUE : 파티션별 윈도에서 가장 먼저 나오는 값을 찾음

- LAST_VALUE : 파티션별 윈도에서 가장 늦게 나오는 값을 찾음

- LAG : 이전 로우의 값을 반환

- LEAD : 이후 로우의 값 반환

그룹 내 비율 함수

- RATIO_TO_REPORT : 전체 1을 나눔

- PERCENT_RANK : 0 - 1까지 순서

 

절차형 SQL : 절차지향적인 프로그램이 가능하도록 하는 트랜잭션 언어이다.

 

프로시저 개념 : 쿼리들을 하나의 함수처럼 실행하기 위한 쿼리의 집합

 

프로시저 구성 (디비컨 SET)

- 선언부 DECLARE

- 시작/종료부(BEGIN/END)

- 제어부(CONTROL)

- SQL 예외부(EXCEPTION)

- 실행부(TRANSACTION)

 

사용자 정의함수 : SQL 처리를 수행하고, 수행 결과를 단일 값으로 반환할 수 있는 절차형 SQL

 

사용자 정의 함수 구성 (디비컨 SER)

- 선언부 DECLARE

- 시작/종료부(BEGIN/END)

- 제어부(CONTROL)

- SQL 예외부(EXCEPTION)

- 반환부(RETURN)

 

트리거 : 이벤트가 발생할 때마다 관련 작업이 자동으로 수행되는 절차형 SQL

- 행 트리거 : 데이터 변화가 생길 때마다 실행

- 문장 트리거 : 트리거에 의해 단 한 번 실행

 

트리거 구성 (디이비컨 SE)

- 선언부 (DECLARE)

- 이벤트부(EVENT)

- 시작/종료부(BEGIN/END)

- 제어부(CONTROL)

- SQL 예외부(EXCEPTION)

 

옵티마이저 : SQL을 가장 효율적으로 수행할 최적의 처리경로를 생성해 주는 DBMS 핵심 엔진

- 실행 계획 : 옵티마이저가 생성한 처리경로, 규칙기반 RBO, 비용기반 CBO

- 힌트 : 실행하려는 SQL 문에 사전에 정보를 주어서 SQL 문 실행에 빠른 결과를 가져오는 효과를 주는 문법,  /*+ RULE /