[정보처리기사 실기] 7장 SQL 응용
정보처리기사 수제비 2022 실기 문제집을 요약하며 공부했습니다! 😁
7-1. 데이터베이스 기본
7-1-1. 트랜잭션
📌 트랜잭션 특성 🌟
- 인가받지 않은 사용자로부터 데이터를 보장하기 위해 DBMS가 가져야 하는 특성이자, 데이터베이스 시스템에서 하나의 논리적 기능을 정상적으로 수행하기 위한 작업의 기본 단위
- 원자성(Atomicity): 트랜잭션을 구성하는 연산 전체가 모두 정상적으로 실행되거나 모두 취소되어야 하는 성질
- 일관성(Consistency): 트랜잭션 수행 전과 트랜잭션 수행 완료 후의 상태가 같아야 하는 성질
- 격리성=고립성(Isolation): 동시에 실행되는 트랜잭션들이 서로 영향을 미치지 않어야 한다는 성질
- 영속성(Durability): 성공이 완료된 트랜잭션의 결과는 영속적으로 데이터베이스에 저장되어야 하는 성질
📌 트랜잭션 상태 변화
- 활동 상태(Active): 초기 상태. 트랜잭션이 실행 중일 때 가지는 상태
- 부분 완료 상태(Partially Commited): 마지막 명령문이 실행된 후에 가지는 상태
- 완료 상태(Commited): 트랜잭션이 성공적으로 완료된 후 가지는 상태
- 실패 상태(Failed): 정상적인 실행이 더 이상 진행될 수 없을 때 가지는 상태
- 철회 상태(Aborted): 트랜잭션이 취소되고, 데이터베이스가 트랜잭션 시작 전 상태로 환원된 상태
*TCL 명령어: 커밋(확정), 롤백(취소), 체크포인트(저장 시기 설정)
📌 병행 제어(Concurrency Control)
- 다수 사용자 환경에서 여러 트랜잭션을 수행할 때, 데이터베이스 일관성 유지를 위해 상호 작용을 제어하는 기법
- ✏️ 미 보장시 문제점
- 갱신 손실(Lost Update): 먼저 실행된 트랜잭션의 결과를 나중에 실행된 트랜잭션이 덮어쓸 때 발생하는 오류
- 현황 파악 오류(Dirty Read): 트랜잭션의 중간 수행 결과를 다른 트랜잭션이 참조하여 발생하는 오류
- 모순성(Inconsistency): 두 트랜잭션이 동시에 실행되어 데이터베이스의 일관성이 결여되는 오류
- 연쇄복귀(Cascading Rollback): 복수의 트랜잭션이 데이터 공유 시 특정 트랜잭션이 처리를 취소할 경우 트랜잭션이 처리한 곳의 부분을 취소하지 못하는 오류
- ✏️ 병행 제어 기법의 종류
- 로킹(Locking): 접근하려는 데이터를 다른 트랜잭션이 접근하지 못하도록 잠그는(lock) 직렬화 기법(순차적 진행 보장)
- 낙관적 검증(Optimistic Validation): 트랜잭션 수행 동안은 어떠한 검사를 하지 않고, 트랜잭션이 종료된 이후에 일괄적으로 검사하는 방식
- 타임 스탬프 순서(Time Stamp Ordering): 데이터에 접근하는 시간(Timestamp)을 미리 정해두어 부여된 시간 순서대로 데이터에 접근
- 다중 버전 동시성 제어(MVCC; Multi Version Concurrency Control): 트랜잭션의 타임스탬프와 접근하려는 데이터의 타임스탬프를 비교하여 직렬 가능성이 보장되는 적절한 버전을 선택하여 접근하는 기법
📌 데이터 베이스 고립화 수준
- 고립화 수준: 다른 트랜잭션이 현재 데이터에 대한 무결성을 해치지 않기 위해 잠금을 설정하는 정도
- ✏️고립화 수준 종류
- Read Uncommited(연산중인 데이터 읽기 허용)
- Read Commited(완료 될 때까지 읽기 제한)
- Repeatable Read(종료시까지 갱신,삭제 제한)
- Serializable Read(영역 전체에 대한 접근 제한)
📌 회복 기법
- 회복기법: 트랜잭션을 수행하는 도중 장애로 인해 손상된 데이터베이스를 손상되기 이전의 정상적인 상태로 복구시키는 작업
- ✏️회복기법 종류
- 로그 기반 회복 기법
- 지연 갱신 회복 기법(Deferred Update): 트랜잭션이 완료되기 전까지 데이터베이스에 기록하지 않는 기법
- 즉각 갱신 회복 기법(immediate Update): 트랜잭션 수행 중 갱신 결과를 바로 DB에 반영
- 체크 포인트 회복 기법: 장애 발생시 검사점 이후에 처리된 트랜잭션에 대해서만 장애 발생 이전 상태로 복원
- 그림자 페이징 회복 기법(Shadow Paging Recovery): 트랜잭션 수행 시 복제본을 생성하여 데이터베이스 장애 시 이를 이용해 복구하는 기법
📌 DDL(Data Definition Language)
- 데이터 정의어: 데이터를 정의하는 언어, 테이블과 같은 데이터 구조를 정의하는데 사용되는 명령어
1. 테이블: 카디널리티(튜플의 개수), 차수(애트리뷰트의 개수)
2. 뷰: 뷰는 ALTER문으로 변경할 수 없음
3. 인덱스: 검색 연산의 최적화를 위해 데이터베이스 내 값에 대한 주소 정보로 구성된 데이터 구조
✏️인덱스 종류: 순서 인덱스, 해시 인덱스, 비트맵 인덱스, 함수 기반 인덱스, 단일 인덱스, 결합 인덱스, 클러스터드 인덱스
4. TABLE 관련 DDL
// 테이블 생성
CREATE TABLE 테이블명
(
컬럼명 데이터 타입 [제약조건]
)
// 컬럼 추가
ALTER TABLE 테이블명 ADD 컬럼명 데이터타입 [제약조건]
// 컬럼 수정
ALTER TABLE 테이블명 MODIFY 컬럼명 데이터타입 [제약조건]
// 컬럼 삭제
ALTER TABLE 테이블명 DROP 컬럼명;
//테이블 삭제
DROP TABLE 테이블명 [CASCADE | RESTRICT];
//테이블 내 데이터 삭제
TRUNCATE TABLE 테이블명;- CASCADE: 참조하는 테이블까지 연쇄 제거
- RESTRICT: 다른 테이블이 삭제할 테이블을 참조 중이면 제거하지 않는 옵션
5. VIEW 관련 DDL
// 뷰 생성
CREATE VIEW 뷰이름 AS 조회쿼리
// 뷰 교체
CREATE OR REPLACE VIEW 뷰이름 AS 조회쿼리;
//뷰 삭제
DROP VIEW 뷰이름;
6. INDEX 관련 DDL
//인덱스 생성
CREATE [UNIQUE] INDEX 인덱스명 ON 테이블명(컬럼명1, 컬럼명2, ...);
//인덱스 수정
ALTER [UNIQUE] INDEX 인덱스명 ON 테이블명(컬럼명1, 컬럼명2, ...);
//인덱스 삭제
DROP INDEX 인덱스명;
📌 DML(Data Manipulation Language)
- 데이터 조작어: 데이터베이스에 저장된 자료들을 입력,수정,삭제, 조회하는 언어
//SELECT
SELEC [ALL | DISTICT] 속성명1, 속성명2, ...
FROM 테이블명,
[WHERE 조건]
[GROUP BY 속성명1, ...]
[HAVING 그룹조건]
[ORDER BY 속성 [ASC | DESC]];
- WHERE 절 문법
- LIKE
- % : 0개 이상의 문자열과 일치
- [] : 1개의 문자와 일지
- [^]: 1개의 문자와 불일치
- _ : 특정 위치의 1개의 문자와 일치
✏️ 조인
- 내부 조인(Inner Join): 공통 존재 컬럼의 값이 같은 경우 추출
SELECT A.컬럼1, A.컬럼2, ..., B.컬럼1, B.컬럼2, ...
FROM 테이블1 A [INNER] JOIN 테이블2 B
[WHERE 검색조건];
- 외부 조인(Outer Join)
- 왼쪽 외부 조인(Left Outer Join): 왼쪽 테이블의 모든 데이터와 오른쪽 테이블의 동일 데이터 추출
- 오른쪽 외부 조인(Right Outer Join): 오른쪽 테이블의 모든 데이터와 왼쪽 테이블의 동일 데이터 추출
- 완전 외부 조인(Full Outer Join): 양쪽의 모든 데이터를 추출하는 기법
//왼쪽 외부조인
SELECT A.컬럼1, A.컬럼2, ... B.컬럼1, B.컬럼2, ...
FROM 테이블1 A LEFT [OUTER] JOIN 테이블2 B
ON 조인조건 [WHERE 검색조건];
//오른쪽 외부조인
SELECT A.컬럼1, A.컬럼2, ... B.컬럼1, B.컬럼2, ...
FROM 테이블1 A RIGHT [OUTER] JOIN 테이블2 B
ON 조인조건 [WHERE 검색조건];
//완전 외부조인
SELECT A.컬럼1, A.컬럼2, ... B.컬럼1, B.컬럼2, ...
FROM 테이블1 A FULL [OUTER] JOIN 테이블2 B
ON 조인조건 [WHERE 검색조건];
- 교차 조인(Cross Join): 조인 조건이 없는 모든 데이터 조합을 추출하는 기법
- 셀프 조인(Self Join): 자기 자신에게 별칭을 지정한 후 다시 조인하는 기법
//교차 조인
SELECT 컬럼1, 컬럼2, ...
FROM 테이블1 CROSS JOIN 테이블2
//셀프 조인
SELECT A.컬럼1, A.컬럼2, ... , B.컬럼1, B.컬럼2, ...
FROM 테이블1 A [INNTER] JOIN 테이블1 B ON 조인조건
[WHERE 검색조건]
✏️ 집합 연산자
- UNION(중복 제거된 합집합), UNION ALL(A+B), INTERSECT(교집합), MINUS(A-B)
✏️ INSERT,UPDATE, DELETE
//데이터 삽입
INSERT INTO 테이블명(속성명1, ..)
VALUES(데이터1, ..);
//데이터 변경
UPDATE 테이블명 SET 속성명 = 데이터, ...
WHERE 조건;
//데이터 삭제
DELETE FROM 테이블명 WHERE 조건;
📌 DCL(Data Control Language)
- 데이터베이스 관리자가 데이터 보안, 무결성 유지, 병행 제어, 회복을 위해 관리자가 사용하는 제어용 언어
// 사용 권한 부여
GRANT 권한 ON 테이블 TO 사용자;
// 사용 권한 회수
REVOKE 권한 ON 테이블 FROM 사용자;
7-2. 응용 SQL 작성하기
7-2-1. 집계성 SQL 작성
📌 집계함수
- 여러 행 또는 테이블 전체 행으로부터 하나의 결과값을 반환하는 함수
SELECT 컬럼1, 컬럼2, ..., 집계함수
FROM 테이블명
[WHERE 조건]
GROUP BY 컬럼1, 컬럼2, ...
[HAVING 조건식(집계함수 포함)]✏️집계 함수 종류
- COUNT, SUM, AVG, MAX, MIN, STDDEV(표준편차), VARIAN(분산)
📌 그룹 함수
- 테이블의 전체 행을 하나 이상의 컬럼을 기준으로 컬럼값에 따라 그룹화하여 그룹별로 결과를 출력하는 함수
1. ROLLUP 함수
- 소그룹의 중간 집계 값을 산출. 지정 컬럼 수 보다 1단계 더 큰 레벨만큼의 중간 집계 값이 생성됨
SELECT 컬럼1, 컬럼2, ..., 집계함수
FROM 테이블명
[WHERE...]
GROUP BY [컬럼...] ROLLUP 컬럼
[HAVING...]
[ORDER BY...]
2. CUBE 함수
- 결합 가능한 모든 값에 대해 다차원 집계를 생성하는 그룹함수. 연산이 많아 시스템에 부하를 줌
SELECT 컬럼1, 컬럼2, ..., 집계함수
FROM 테이블명
[WHERE...]
GROUP BY [컬럼...] CUBE(컬럼명a, ...)
[HAVING...]
[ORDER BY...]
3. GROUPING SETS 함수
- 집계 대상 컬럼들에 대한 개별 집계를 구할 수 있으며, 순서와 무관한 결과를 얻을 수 있음
SELECT 컬럼1, 컬럼2, ..., 집계함수
FROM 테이블명
[WHERE...]
GROUP BY [컬럼...] GROUPING SETS(컬럼명1, ...)
[HAVING...]
[ORDER BY...]
📌 윈도 함수
- 데이터베이스를 사용한 온라인 분석 처리 용도로 사용하기 위해 표준 SQL에 추가된 함수 = OLAP 함수
SELECT 함수명(파라미터)
OVER
([PARTITION BY 컬럼1, ...])
[ORDER BY 컬럼A, ...])
FROM 테이블명
- 순위 함수
- RANK: 동일 순위 레코드 존재 시 후순위는 넘어감(2위, 2위, 2위, 5위, 6위)
- DENSE_RANK: 동일 순위 레코드 존재 시 후순위를 넘어가지 않음(2위, 2위, 2위, 3위, 4위)
- ROW_NUMBER: 동일 순위 레코드 존재 시 이와 무관하게 연속 번호 부여(2위, 3위, 4위, 5위)
SELECT NAME, SALARY
RANK() OVER (ORDER BY SALARY DESC) AS A
FROM EMPLOYEE;
7-3. 절차형 SQL 작성하기
7-3-1. 절차형 SQL
📌 절차형 SQL 종류
- 프로시저, 사용자 정의함수, 트리거
📌 프로시저
- 일련의 쿼리들을 마치 하나의 함수처럼 실행하기 위한 쿼리의 집합
- DECLARE(선언부), BEGIN/END(선언부/종료부), CONTROL(제어부), SQL, EXCEPTION(예외), TRANSACTION(실행부)
- 모드(IN - 운영체제에서 프로시저로 값 전달 모드, OUT- 프로시저에서 처리한 결과를 운영체제로 전달하는 모드, INOUT - 두가지 기능 모두 수행하는 모드)
📌 사용자 정의 함수
- 일련의 SQL 처리를 수행하고, 수행 결과를 단일 값으로 반환(RETURN)할 수 있는 절차형 SQL
- 조회용도로 SELECT 문을 사용(INSERT, UPDATE, DELETE 사용 불가)
- 모드는 IN 만 가능 (운영체제에서 사용자 정의 함수로 값 전달 모드)
- 호출문에 대한 함수값을 반환
📌 트리거
- 데이터베이스 시스템에서 삽입, 갱신, 삭제 등의 이벤트가 발생할 때마다 관련 작업이 자동으로 수행되는 절차형 SQL
- 반환값이 없음. EVENT가 존재
CREATE [OR REPLACE] TRIGGER 트리거명
[BEFORE | AFTER] 유형 ON 테이블명
[FOR EACH ROW]
BEGIN
END;
- BEFORE: 테이블에 대한 INSERT/UPDATE/DELETE를 수행하기 전에 트리거 실행 (AFTER는 수행 후 트리거 실행)
- FOR EACH ROW: 매번 변경되는 데이터 행의 수만큼 실행을 위한 명령어
- COMMIT, ROLLBACK 등의 트랜잭션 제어어(TCL) 사용 불가
7-4. 데이터 조작 프로시저 최적화
7-4-1. 데이터 조작 프로시저 성능 개선
📌 쿼리 성능 개선
- 최소의 시간으로 원하는 결과를 얻도록 프로시저를 수정하는 작업
📌 옵티마이저
- SQL을 가장 빠르고 효율적으로 수행할 최적의 처리경로를 생성해주는 DBMS 내부의 핵심엔진.
- 옵티마이저가 생성한 SQL 처리 경로를 실행계획이라고 부름
✏️ 유형
- RBO(Rule Based Optimizer): 통계 정보가 없는 상태에서 사전 등록된 규칙에 따라 질의 실행 계획을 선택하는 옵티마이저로 규칙 우선 기반
- CBO(Cost Based Optimizer): 통계 정보로부터 모든 접근 경로를 고려한 질의실행 계획을 선택하는 옵티마이저로 비용 기반
📌 힌트
- SQL 성능 개선의 핵심 부분으로 옵티마이저의 실행 계획을 원하는대로 변경할 수 있게 한다. 명시적인 힌트를 통해 실행계획을 변경한다.