[정보처리기사-실기] VII. SQL 응용
VII. SQL 응용
01. 데이터베이스 기본
02. 응용 SQL
03. SQL 활용 및 최적화
01. 데이터베이스 기본
- 트랜잭션: 데이터베이스 시스템에서 하나의 논리적 기능을 정상적으로 수행하기 위한 작업의 기본 단위
- 트랜잭션 특성: ACID
1) 원자성(Atomicity)
2) 일관성(Consistency) : 병행제어*
*병행 제어(Concurrency Control): 일관성 주요 기법 - 개념: 다수 사용자 환경에서 여러 트랜잭션을 수행할 때, 데이터베이스 일관성 유지를 위해 상호 작용을 제어하는 기법 - 목적: 데이터베이스 공유 최대화, 시스템 활용도 최대화, 데이터베이스 일관성 유지, 응답시간 최소화 - 종류: 로 낙타다2 (로킹, 낙관적 검증, 타임 스탬프 순서, 다중버전 동시성 제어, 2PC) |
3) 격리성, 고립성(Isolation) : 데이터베이스고립화수준*
*데이터베이스 고립화 수준(Isolation Level): 격리성 주요 기법 - 개념: 다른 트랜잭션이 현재의 데이터에 대한 무결성을 해치지 않기 위해 잠금을 설정하는 정도 - 종류: Read Uncommitted, Read Committed, Repeatable Read,Serializable Read |
4) 영속성(Durability) : 회복기법*
*회복 기법(Recovery): 영속성 주요 기법 - 개념: 트랜잭션을 수행하는 도중 장애로 인해 손상된 데이터베이스를 손상되기 이전의 정상적인 상태로 복구시키는 작업 - 주요 요소: REDO, UNDO - 종류: 회로체그 (로그 기반 회복 기법, 체크 포인트 회복 기법, 그림자 페이징 회복 기법) |
- 트랜잭션의 상태 변화: 활부완실철
1) 활동 상태, 활성(Active)
2) 부분 완료 상태(Partially Committed)
3) 완료 상태(Committed)
4) 실패 상태(Failed)
5) 철회 상태(Aborted)
- TCL(Transaction Control Language): 트랜잭션 제어언어
▷ 트랜잭션의 결과를 허용하거나 취소하는 목적으로 사용되는 언어
▷ TCL 명령어: 커롤체 (커밋, 롤백, 체크포인트)
- DDL(Data Definition Language): 데이터 정의어
▷ DDL 대상: 스키마, 테이블, 뷰, 인덱스
*스키마(Schema): 데이터베이스의 구조, 제약조건 등의 정보를 담고 있는 기본적인 구조 - 구성: 외부 스키마, 개념 스키마, 내부 스키마 |
*테이블(Table): 데이터를 저장하는 항목인 필드(Field)들로 구성된 데이터의 집합체. (=릴레이션(Relation)=엔터티(Entity)) - 용어: 튜플(행, 레코드), 속성(Attribute, 열), 카디널리티(튜플의 개수), 차수(Degree, 속성의 개수), 도메인 |
*뷰(View): 논리 테이블. 사용 관점에서 테이블과 동일 - 장점: 논리적 독립성 제공, 데이터 조작 연산 간소화, 보안 기능(접근제어) 제공 - 단점: 뷰 자체 인덱스 불가, 뷰 변경 불가, 데이터 변경 제약 존재 |
*인덱스(Index): 검색 연산의 최적화를 위해 데이터베이스 내 값에 대한 주소 정보로 구성된 데이터 구조. 특정 레코드 위치를 알려주는 용도. - 테이블 전체 스캔(Table Full Scan), 테이블 범위 스캔(Index Range Scan) |
▷ DDL 명령어: CREATE, ALTER, DROP, TRUNCATE
*테이블 관련 DDL 1) CREATE TABLE 2) ALTER TABLE 3) DROP TABLE 4) TRUNCATE TABLE |
*뷰 관련 DDL 1) CREATE VIEW 2) CREATE OR REPLACE VIEW 3) DROP VIEW |
*인덱스 관련 DDL 1) CREATE INDEX 2) ALTER INDEX 3) DROP INDEX |
- DML(Data Manipulation Language): 데이터 조작어
▷ 데이터베이스에 저장된 자료들을 입력, 수정, 삭제, 조회하는 언어
▷ DML 유형: SELECT, INSERT, UPDATE, DELETE
1) SELECT(데이터 조회) 명령어
SELECT [ALL | DISTINCT] 속성명1, 속성명2 ...
FROM 테이블명1, ...
[WHERE 조건]
[GROUP BY 속성명1, ...]
[HAVING 그룹조건]
[ORDER BY 속성 [ASC | DESC] ];
*SELECT 명령어: 셀프 웨 구해오 1) SELECT 절: ALL(기본값) | DISTINCT 2) FROM 절: 테이블명 3) WHERE 절: 조건(비교(=, <>, !=, <, <=, >, >=), 범위(BETWEEN), 집합(IN, NOT IN), 패턴(LIKE), NULL(IS NULL, IS NOT NULL), 복합조건(AND, OR, NOT)) 4) GROUP BY 절: 그룹으로 분류 5) HAVING 절: GROUP BY에 의해 분류한 후 그룹에 대한 조건 지정 6) ORDER BY 절: ASC(오름차순, 기본값), DESC(내림차순) |
*JOIN: 두 개 이상의 테이블을 연결하여 데이터를 검색하는 방법 - 유형: 내부 조인, 외부 조인(왼쪽 외부 조인, 오른쪽 외부 조인, 완전 외부 조인), 교차 조인, 셀프 조인 |
*서브쿼리(Sub-Query): SQL 문 안에 포함된 또 다른 SQL 문 - 유형: FROM 절 서브쿼리(=인라인 뷰), WHERE 절 서브쿼리(=중첩 서브쿼리) |
*집합 연산자(Set Operator) - 유형: UNION, UNION ALL, INTERSECT, MINUS |
2) INSERT(데이터 삽입) 명령어: 인인벨(INSERT INTO / VALUES)
INSERT INTO 테이블명(속성명1, ...)
VALUES (데이터1, ...);
3) UPDATE(데이터 변경) 명령어: 업셋웨(UPDATE / SET / WHERE)
UPDATE 테이블명
SET 속성명 = 데이터, ...
WHERE 조건;
4) DELETE(데이터 삭제) 명령어: 델프웨(DELETE FROM / WHERE)
DELETE FROM 테이블명
WHERE 조건;
- DCL(Data Control Language): 데이터 제어어
▷ 유형: GRANT, REVOKE
1) GRANT(권한 부여) 명령어: 그온투(GRANT 권한 ON 테이블 TO 사용자)
GRANT 권한 ON 테이블 TO 사용자;
2) REVOKE(권한 회수) 명령어: 리온프(REVOKE 권한 ON 테이블 FROM 사용자)
REVOKE 권한 ON 테이블 FROM 사용자
02. 응용 SQL: 집계성 SQL
- 다중 행 연산자: 서브 쿼리의 결과가 여러 개의 튜플을 반환하는 다중 행 서브쿼리에서 사용되는 연산자
▷ 종류: IN, ANY(=SOME), ALL, EXISTS
- 집계 함수: 여러 행 또는 테이블 전체 행으로부터 하나의 결괏값을 반환하는 함수
▷ 종류: COUNT, SUM, AVG, MAX, MIN, STDDEV, VARIANCE
SELECT 컬럼1, 컬럼2, ..., 집계함수
FROM 테이블명
[WHERE 조건]
GROUP BY 컬럼1, 컬럼2, ...
[HAVING 조건식(집계함수 포함)]
*GROUP BY 구문: 복수 ROW 대상의 데이터 분석 시 그룹핑 대상이 되는 부분을 선별하기 위해 사용 - NULL 값을 가지는 행은 제외 후 산출 |
*HAVING 구문: GROUP BY 구문의 기준 항목이나 소그룹 집계 함수를 활용한 조건을 적용하는 데 사용 - GROUP BY 및 집계 함수에 대한 WHERE 구문 |
03. SQL 활용 및 최적화
- 절차형 SQL: 절차 지향적인 프로그램이 가능하도록 하는 트랜잭션 언어
▷ 종류: 프로시저(Procedure), 사용자 정의 함수(User-Defined Function), 트리거(Trigger)
- SQL 최적화: 튜닝, 옵티마이저
*튜닝(쿼리 성능 개선) - 데이터베이스에서 프로시저에 있는 SQL 실행 계획을 분석, 수정을 통해 최소의 시간으로 원하는 결과를 얻도록 프로시저를 수정하는 작업 - SQL 성능 개선을 통해 데이터 조작 프로시저의 성능 개선 가능 |
*옵티마이저(Optimizer) - SQL을 가장 빠르고 효율적으로 수행할 최적의 처리경로를 생성해주는 DBMS 내부의 핵심엔진 - 실행계획(Execution Plan): 옵티마이저가 생성한 SQL 처리경로 - 유형: RBO(Rule Based Optimizer, 규칙(우선순위)기반 옵티마이저), CBO(Cost Based Optimizer, 비용(수행 시간)기반 옵티마이저) |
FIN.