SQL 응용

1. 데이터베이스 기본

 

(1) 트랜잭션(Transaction)
- 데이터베이스 시스템에서 하나의 논리적 기능을 정상적으로 수행하기 위한 작업의 기본 단위
- 특성: Atomicity, Consistency, Isolation, Durability (ACID)
- 원자성: 트랜잭션의 연산 전체가 성공 또는 실패(All or Nothing)되어야 하는 성질
- 일관성: 시스템이 가지고 있는 고정요소는 트랜잭션 수행 전과 트랜잭션 수행 완료 후의 상태가 같아야 하는 성질
- 격리성=고립성: 동시에 실행되는 트랜잭션들이 서로 영향을 미치지 않아야 하는 성질
- 영속성: 성공이 완료된 트랜잭션의 결과는 영속적으로 데이터베이스에 저장되어야 하는 성질
 
 
- 트랜잭션 상태 변화: 활동(Active), 부분 완료(Partially Committed), 완료(Committed), 실패(Failed), 철회(Aborted) 상태 (활부완실철)
- 트랜잭션 제어언어(TCL) 명령어: COMMIT, ROLLBACK, CHECKPOINT (커롤체)
- 커밋(트랜잭션 확정): 트랜잭션을 메모리에 영구적으로 저장하는 명령어
- 롤백(트랜잭션 취소): 트랜잭션 내역을 저장 무효화시키는 명령어
- 체크포인트(저장 시기 결정): ROLLBACK을 위한 시점을 지정하는 명령어
- 병행 제어(일관성 주요 기법): 다수 사용자 환경에서 여러 트랜잭션을 수행할 때, 데이터베이스 일관성 유지를 위해 상호작용을 제어하는 기법
- 병행 제어 미보장시 문제점: 갱신 손실, 현황 파악오류, 모순성, 연쇄복귀 (갱현모연)
- 병행 제어 기법: 로킹, 낙관적 검증, 타임 스탬프 순서, 다중버전 동시성 제어(MVCC) (로 낙타다)
- 고립화 수준(격리성 주요 기법): 다른 트랜잭션이 현재의 데이터에 대한 무결성을 해치지 않기 위해 잠금을 설정하는 정도
- 고립화 수준 종류: Read Uncommitted, Read Committed, Repeatable Read, Serializable Read
- 회복 기법(영속성 주요 기법): 트랜잭션을 수행하는 도중 장애로 인해 손상된 데이터베이스를 손상되기 이전의 정상적인 상태로 복구시키는 작업
- 회복 기법 주요 요소: REDO, UNDO
- 회복 기법 종류: 로그 기반 회복 기법(지연 갱신 회복 기법, 즉각 갱신 회복 기법), 체크포인트 회복 기법, 그림자 페이징 회복 기법
 
 
(2) 데이터 정의어(DDL; Data Definition Language)
- 데이터를 담는 그릇을 정의하는 언어
- 테이블과 같은 데이터 구조를 정의하는 데 사용되는 명령어들로 특정 구조를 생성, 변경, 삭제, 이름을 바꾸는 데이터 구조와 관련된 명령어
- DDL 대상: 도메인, 스키마, 테이블, 뷰, 인덱스 (도스테뷰인)
- 스키마 3계층: 외부 스키마, 개념 스키마, 내부 스키마
 

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

- 테이블(Table): 데이터를 저장하는 항목인 필드(Field)들로 구성된 데이터의 집합체로 릴레이션(Relation) 혹은 엔터티(Entitty)라고도 불림
- 테이블 관련 용어: 튜플/행(Row), 애트리뷰트/열(Column), 식별자, 카디널리티(Cardinality), 차수(Degree), 도메인
- 뷰(View): 논리 테이블
- 뷰 특징: 논리적 데이터 독립성 제공, 데이터 조작 연산 간소화, 보안 기능(접근 제어) 제공, 뷰 변경 불가
- 인덱스(Index): 데이터를 빠르게 찾을 수 있는 수단으로서, 테이블에 대한 조회 속도를 높여 주는 자료 구조
- 데이터베이스 파일 구조: 순차 방법, 인덱스 방법, 해싱 방법
- 인덱스 종류: 순서 인덱스, 해시 인덱스, 비트맵 인덱스, 함수기반 인덱스, 단일 인덱스, 결합 인덱스, 클러스터드 인덱스 (순해비함 단결클)


- DDL 명령어 유형: CREATE, ALTER, DROP, TRUNCATE (크알드트)
- CREATE TABLE(테이블 생성): PRIMARY KEY, FOREIGN KEY, UNIQUE, NOT NULL, CHECK, DEFAULT
- ALTER TABLE(테이블 수정): CREATE TABLE의 컬럼에 사용되는 제약조건 사용 가능
- DROP TABLE(테이블 삭제): CASCADE(참조하는 테이블까지 연쇄 제거), RESTRICT(참조 중이면 제거 제한)
- TRUNCATE TABLE(테이블 내 데이터 삭제)

CREATE TABLE 테이블명 (컬럼명 데이터타입 [제약조건], …);

ALTER TABLE 테이블명 ADD 컬럼명 데이터타입 [제약조건]; #컬럼 추가
ALTER TABLE 테이블명 MODIFY 컬럼명 데이터타입 [제약조건]; #컬럼 수정
ALTER TABLE 테이블명 DROP COLUMN 컬럼명; #컬럼 삭제

DROP TABLE 테이블명 [CADCADE:RESTRICT];

TRUNCATE TABLE 테이블명;

- CREATE VIEW(뷰 생성): UNION이나 ORDER BY(ASC/DESC) 절 사용 불가
- CREATE OR REPLACE VIEW(뷰 교체): 뷰는 수정(ALTER) 불가
- DROP VIEW(뷰 삭제)

CREATE VIEW 뷰이름 AS
조회쿼리;

CREATE OR REPLACE VIEW 뷰이름 AS
조회쿼리;

DROP VIEW 뷰이름;

- CREATE INDEX(인덱스 생성): UNIQUE 생략 가능
- ALTER INDEX(인덱스 수정): 삭제 후 생성 권고
- DROP INDEX(인덱스 삭제)

CREATE [UNIQUE] INDEX 인덱스명 ON 테이블명(컬럼명1, 컬럼명2, …);

ALTER [UNIQUE] INDEX 인덱스명 ON 테이블명(컬럼명1, 컬럼명2, …);

DROP INDEX 인덱스명;

(3) 데이터 조작어(DML; Data Manipulation Language)
- 데이터베이스에 저장된 자료들을 입력, 수정, 삭제, 조회하는 언어
- DML 명령어 유형: SELECT, INSERT, UPDATE, DELETE (세인업데)
- SELECT(데이터 조회): SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY (셀프 웨 구해오)
- WHERE: 비교, 범위(BETWEEN AND), 집합(IN/NOT IN), 패턴(LIKE), NULL(IS NULL/IS NOT), 복합조건
- ORDER BY: ASC(오름차순), DESC(내림차순)

SELECT [ALL : DISTINCT] 속성명1, 속성명2, … #속성명 별칭은 AS 사용하며 생략 가능
    FROM 테이블명1, …
[WHERE 조건]
[GROUP BY 속성명1, …]
[HAVING 그룹조건]
[ORDER BY 속성 [ASC : DESC]];

- 조인(Join)

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

- 논리적 조인 유형: 내부 조인, 외부 조인, 교차 조인, 셀프 조인

 

 

내부 조인 (INNER JOIN)	공통 존재 컬럼의 값이 같은 경우를 추출
외부 조인 (OUTER JOIN)	왼쪽 외부 조인 (LEFT OUTER JOIN)	왼쪽 테이블의 모든 데이터와 오른쪽 테이블의 동일 데이터 추출
	오른쪽 외부 조인 (RIGHT OUTER JOIN)	오른쪽 테이블의 모든 데이터와 왼쪽 테이블의 동일 데이터 추출
	완전 외부 조인 (FULL OUTER JOIN)	양쪽의 모든 데이터를 추출
교차 조인 (CROSS JOIN)	조인 조건이 없는 모든 데이터 조합을 추출, ON 절 없음
셀프 조인 (SELF JOIN)	자기 자신에게 별칭을 지정한 후 다시 조인

 

 

SELECT A.컬럼1, A.컬럼2, …,
       B.컬럼1, B.컬럼2, …
  FROM 테이블1 A [INNER] JOIN 테이블2 B
    ON 조인조건
[WHERE 검색조건];

SELECT 컬럼1, 컬럼2, …
  FROM 테이블1 CROSS JOIN 테이블2

 

 

- 서브쿼리(Sub-Query)

- SQL 문 안에 포함된 또 다른 SQL 문

- 메인쿼리와 서브쿼리는 주종 관계

- 서브쿼리 유형: FROM 절 서브쿼리, WHERE 절 서브쿼리

- FROM 절 서브쿼리: 뷰(View)처럼 결과가 동적으로 생성된 테이블 형태로 사용 가능, 인라인 뷰(Inline Views)

- WHERE 절 서브쿼리: 중첩 서브쿼리(Nested Sub-Query)

 

 

- 집합 연산자(Set Operator)

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

- 2개 이상의 질의 결과를 연결하여 하나로 결합

 

 

UNION	중복 레코드 제외	중복 행이 제거된 쿼리 결과 반환
UNION ALL	중복 레코드 허용	중복 행이 제거되지 않은 쿼리 결과 반환
INTERSECT	중복 레코드만 포함	두 쿼리 결과에 공통적으로 존재하는 결과 반환
MINUS	비교 레코드 제외	첫 번째 쿼리에 있고 두 번째 쿼리에 없는 결과 반환

 

 

- INSERT(데이터 삽입)

- UPDATE(데이터 변경)

- DELETE(데이터 삭제)

 

 

INSERT INTO 테이블명(속성명1, …) #속성, 데이터 개수, 데이터 타입 일치
VALUES (데이터1, …); #속성의 타입이 문자열인 경우 따옴표 붙임

UPDATE 테이블명
   SET 속성명 = 데이터, …
 WHERE 조건;
 
 DELETE FROM 테이블명 #레코드 삭제해도 테이블 구조는 남음
       WHERE 조건;

 

 

(4) 데이터 제어어(DCL; Data Control Language)

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

- DCL 명령어 유형: GRANT, REVOKE

- GRANT(사용 권한 부여): 데이터베이스 관리자(DBA)가 사용자에게 데이터베이스에 대한 권한 부여 (그온투)

- REVOKE(사용 권한 취소): 데이터베이스 관리자(DBA)가 사용자에게 부여했던 권한 회수 (리온프)

 

 

GRANT 권한 ON 테이블 TO 사용자; #권한 부여

REVOKE 권한 ON 테이블 FROM 사용자; #권한 회수

 

 

2. 응용 SQL 작성

 

 

(1) 집계성 SQL 작성

- 다중 행 연산자: 서브 쿼리의 결과가 여러 개의 튜플을 반환하는 다중 행 서브쿼리에서 사용되는 연산자

- 다중 행 연산자 종류: IN, ANY, SOME, ALL, EXISTS 사용하며 단일 행 비교 연산자(<, >, =, <>)와 결합하여 사용 가능

 

 

IN	리턴되는 값중 조건에 해당하는 값이 있으면 참
ANY (=SOME)	서브쿼리에 의해 리턴되는 각각의 값과 조건을 비교하여 하나 이상 만족하면 참
ALL	값을 서브쿼리에 리턴되는 모든 값과 조건 값을 비교하여 모든 값 만족하면 참
EXISTS	메인쿼리의 비교 조건이 서브쿼리의 리턴되는 값중에서 만족하는 값이 하나라도 존재하면 참

 

 

- 데이터 분석 함수: 총합, 평균 등의 데이터 분석을 위해, 복수 행 기준의 데이터를 모아서 처리하는 다중 행 함수

- 데이터 분석 함수 종류: 집계 함수, 그룹 함수, 윈도 함수

 

 

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

 

 

- 집계 함수(Aggregate Function)

- GROUP BY 구문: NULL값을 가지는 ROW는 제외하고 산출

-  HAVING 구문: GROUP BY 및 집계 함수에 대한 WHERE 구문

 

 

SELECT 컬럼1, 컬럼2, …, 집계함수
  FROM 테이블명
[WHERE 조건]
GROUP BY 컬럼1, 컬럼2, …
[HAVING 조건식(집계함수 포함)] #그룹화된 집합에 대한 조건 지정시 사용

 

 

- 집계 함수 종류: COUNT, SUM, AVG, MAX, MIN, STDDEV(표준편차), VARIANCE(분산)

- 집계 함수 계산시 NULL이 포함된 경우 없는 데이터로 판단

 

 

COUNT(컬럼명)	NULL은 제외하고 COUNT
COUNT(*)	NULL도 포함하여 전부 COUNT

 

 

- 그룹 함수(Group Function)

- 테이블의 전체 행을 하나 이상의 컬럼을 기준으로, 컬럼 값에 따라 그룹화하여 그룹별로 결과를 출력하는 함수

- 그룹 함수 종류: ROLLUP, CUBE, GROUPING SETS

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

 

 

SELECT 컬럼1, 컬럼2, …, 집계 함수
  FROM 테이블명
[WHERE]
GROUP BY [컬럼] ROLLUP(컬럼1, 컬럼2, …);
[HAVING]
[ORDER BY]

 

 

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

 

 

SELECT 컬럼1, 컬럼2, …, 집계 함수
  FROM 테이블명
[WHERE]
GROUP BY [컬럼] CUBE(컬럼1, 컬럼2, …);
[HAVING]
[ORDER BY]

 

 

- GROUPING SETS 함수: 개별 집계를 구할 수 있는 집계 함수

- ROLLUP이나 CUBE와 달리 컬럼 간 순서에 상관없이 동일한 결과

 

 

SELECT 컬럼1, 컬럼2, …, 집계 함수
  FROM 테이블명
[WHERE]
GROUP BY [컬럼] GROUPING SETS(컬럼1, 컬럼2, …);
[HAVING]
[ORDER BY]

 

 

- 윈도 함수

- OLAP(Online Analytical Processing): 의사결정 지원 시스템으로, 사용자가 동일한 데이터를 여러 기준으로  다차원 분석할 수 있도록 도와주는 기술

 

 

SELECT 함수명(파라미터)
  OVER
([PARTITION BY 컬럼1, …]
[ORDER BY 컬럼A, …])
  FROM 테이블명

 

 

- 윈도 순위 함수: RANK, DENSE_RANK, ROW_NUMBER

 

 

RANK	동일 순위 레코드 존재시 후순위 넘어감	2위, 2위, 2위, 5위, 6위
DENSE_RANK	동일 순위 레코드 존재시 후순위 넘어가지 않음	2위, 2위, 2위, 3위, 4위
ROW_NUMBER	동일 순위 레코드 존재시 이와 무관하게 연속 번호 부여	2위, 3위, 4위, 5위, 6위

 

 

SELECT NAME,
       SALARY,
       RANK() OVER (ORDER BY SALARY DESC) AS A,
       DENSE_RANK() OVER (ORDER BY SALARY DESC) AS B,
       ROW_NUMBER() OVER (ORDER BY SALARY DESC) AS C
  FROM EMPLOYEE;

 

 

3. SQL 활용 및 최적화

 

 

(1) 절차형 SQL(Procedural SQL)

- 일반적인 개발 언어처럼 SQL 언어에서도 절차 지향적인 프로그램이 가능하도록 하는 트랜잭션 언어

- 종류: 프로시저, 사용자 정의 함수, 트리거

- 프로시저: 일련의 쿼리들을 마치 하나의 함수처럼 실행하기 위한 쿼리의 집합

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

- 트리거: 데이터베이스 시스템에서 삽입, 갱신, 삭제 등의 이벤트가 발생할 때마다 관련 작업이 자동으로 수행되는 절차형 SQL

 

 

(2) SQL 최적화

- 쿼리 성능 개선: 데이터베이스에서 프로시저에 있는 SQL 실행 계획을 분석을 통해 최소의 시간으로 원하는 결과를 얻도록 프로시저를 수정하는 작업

- APM(Application Performance Monitoring): 애플리케이션 성능 관리 및 모니터링 도구

- 옵티마이저(Optimizer): SQL을 가장 빠르고 효율적으로 수행할 최적의 처리경로(=실행계획)를 생성해주는 데이터베이스 핵심모듈

- 옵티마이저 유형: 규칙기반 옵티마이저(RBO), 비용기반 옵티마이저(CBO)