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

이번 시간은 정보처리기사 실기에서 다루는 7장 SQL 응용에 대해서 정리를 해보았습니다.

 

* 이 글은 정보처리기사 공부 목적으로 쓴 글입니다. 그러다 보니 혹시 제가 잘못 작성한 부분이 있거나 수정이 필요하다면 댓글로 알려주시면 감사하겠습니다. 

---

 

트랜잭션	DB 시스템에서 하나의 논리적 기능을 정상적으로 수행하기 위한 작업 단위 (특성: ACID)
원자성 (Atomicity)	All or Nothing. 연산 전체는 성공 또는 실패여야 하며, 하나라도 실패할 경우 전체가 취소되어야 하는 특성
일관성 (Consistency)	트랜잭션이 성공적으로 실행되면, DB는 일관된 상태를 유지해야 하는 특성 (기법: 병행(동시성) 제어)
격리성 (Isolation)	트랜잭션 실행 중 중간 연산 결과에 다른 트랜잭션이 접근할 수 없다 (기법: 고립화 수준)
영속성 (Durability)	성공적으로 완료된 트랜잭션 결과는 DB에 영속적으로 저장되어야 한다 (기법: 회복기법)
TCL	트랜잭션 제어 언어 - 트랜잭션 결과를 허용하거나 취소하기 위한 제어 언어 커롤체 COMMIT, ROLLBACK, CHECKPOINT
트랜잭션의 상태변환	활부완실철 1. 활동상태 (Active) : 트랜잭션 실행 중 2. 부분 완료 상태 (Partially Committed) : 마지막 명령문 실행 상태 3. 완료 상태 (Committed) : 성공적으로 완료 4. 실패 상태 (Failed) : 정상적 실행 불가 5. 철회 상태 (Aborted) : 트랜잭션 취소
병행제어 미보장 문제점	갱현모연: 갱신손실(Lost Update), 현황파악오류(Dirty Read), 모순성(Inconsistency), 연쇄복귀(Cascading Rollback)
병행제어 기법	로낙타다 1. 로킹(Locking) 2. 낙관적 검증 기법(Validation) 3. 타임 스탬프 순서(Time Stamp Ordering) 4. 다중버전 동시성 제어(MVCC; Multi Version Concurrency Control)
병행 제어	다수 사용자 환경에서 일관성 유지를 위해 제어하는 기법
로킹	트랜잭션의 순차적 진행을 보장하는 기법 - 로킹 단위 : 한번에 로킹할 수 있는 객체 크기 - 로킹 단위가 작을수록 DB공유도는 증가하지만 로킹 오버헤드 증가
낙관적 검증	일단 검증 없이 진행 후, 종료한 다음 검증을 수행해 반영
타임스탬프 순서 (Time Stamp Ordering)	트랜잭션이나 데이터에 타임 스탬프를 부여하여, 그 시간에 따라 작업을 수행
다중 버전 동시성 제어 (MVCC)	트랜잭션의 타임스탬프와 접근하려는 데이터의 타임스탬프를 비교해, 적절한 버전을 선택하여 접근하도록 하는 기법
고립화	무결성을 해치지 않기 위해 잠금을 설정하는 정도
고립화 수준 종류	언커리시 1. Read Uncommitted : 연산 중인 데이터를 다른 트랜잭션이 읽는 것을 허용 2. Read Committed : 연산이 완료되기 전까지 다른 트랜잭션이 읽는 것을 제한 3. Repetable Read : 선행 트랜잭션이 특정 데이터를 읽을 때, 해당 데이터의 갱신/삭제를 제한 4. Serializable Read : 선행 트랜잭션이 특정 데이터 영역을 읽을 때, 해당 영역 전체의 접근을 제한
회복 기법	회로체크 1. 로그 기반 회복 기법 1-1) 지연 갱신 회복 기법 : 트랜잭션 완료 전에는 로그에만 기록. 장애 발생 시 로그를 폐기 1-2) 즉각 갱신 회복 기법 : 트랜잭션 갱신 결과를 바로 DB에 반영. 장애 발생 시 로그를 참고하여 되돌림 2. 체크포인트 회복 기법 : 체크포인트 이전으로 복원 3. 그림자 페이징 회복 기법 : 트랜잭션 수행 시 복제본을 생성해 이를 이용해 복구
SQL 문법	1. DDL (정의어) 크알드트 2. DML (조작어) 세인업데 3. DCL (제어어) 그리
DDL의 대상	도스테뷰인 1. 도메인 2. 스키마 3. 테이블 4. 뷰 5. 인덱스
도메인	하나의 속성이 가질 수 있는 원자값의 집합
스키마	DB의 구조, 제약조건 등의 정보를 담고 있는 구조
스키마 3계층	외개내 1. 외부 스키마 2. 개념 스키마 3. 내부 스키마
외부 스키마	사용자(개발자) 관점에서의 구조. 사용자 뷰를 나타냄
개념 스키마	제약조건, 권한, 보안 등 전체적인 논리 구조
내부 스키마	물리적 저장장치 관점에서의 구조. 레코드 형식, 물리적 순서 등
테이블	데이터를 저장하는 공간. (=릴레이션, =엔터티)
애트리뷰트	=Column.
디그리	애트리뷰트의 개수
튜플	=Row. =레코드. 한 릴레이션에서 중복되는 튜플은 존재 불가
카디널리티	튜플의 개수
뷰	데이터의 독립성을 보장하고 조작 연산을 간소화할 수 있는 논리 테이블 - ALTER로 변경 불가 - 자체 인덱스 불가
뷰 속성	- REPLACE : 기존 존재 시 재생성 - FORCE : 기본 테이블 관계 없이 뷰 생성 - NO FORCE : 기본 테이블이 있을 때만 뷰 생성 - WITH CHECK OPTION : 서브 쿼리 내 조건을 만족하는 행만 변경 - WITH READ ONLY : DML(조작어) 불가
인덱스	DB 시스템에서 빠른 검색을 위한 데이터 구조 - 인덱스가 없으면 Table Full Scan을 하지만, 인덱스가 있으면 Index Range Scan을 하므로 검색 속도가 빠름
인덱스 컬럼 선정	- 적정 분포도: 10~15% (분포도: 특정 컬럼 값이 테이블에 평균적으로 분포된 정도) - 수정이 빈번하지 않은 컬럼 - 분포도가 좋은 컬럼 ⇒ 단일 인덱스 - 자주 결합되는 컬럼 ⇒ 결합 인덱스
인덱스 종류	비단순함 해결 클 1. 비트맵 인덱스 : 컬럼 개수가 적고, 수정이 적을 수록 좋은 인덱스 (생년월일, 상품번호 등) 2. 단일 인덱스 : 하나의 컬럼으로만 구성된 인덱스 3. 순서 인덱스 : 데이터가 정렬된 순서로 생성되는 인덱스 4. 함수기반 인덱스 : 함수를 적용해 만든 인덱스 5. 해시 인덱스 : 해시 함수를 통해 키 값으로 데이터에 접근하는 인덱스 (튜플 양에 무관하게 접근 비용 동일함) 6. 결합 인덱스 : 두 개 이상의 컬럼으로 구성된 인덱스 7. 클러스터드 인덱스 : PK 기준으로 레코드를 묶어 데이터의 물리적 순서에 따라 생성된 인덱스
인덱스 스캔 방식	범전단생 1. 인덱스 범위 스캔 2. 인덱스 전체 스캔 3. 인덱스 단일 스캔 4. 인덱스 생략 스캔
인덱스 범위 스캔 (Index Range Scan)	⬇️ ➡️ 루트 블록에서 리프 블록까지 수직 탐색 후, 리프 블록을 수평 탐색
인덱스 전체 스캔 (Index Full Scan)	➡️ ➡️ 리프 블록을 처음부터 끝까지 수평 탐색
인덱스 단일 스캔 (Index Unique Scan)	⬇️ ⬇️ 수직 탐색으로만 스캔
인덱스 생략 스캔 (Index Skip Scan)	선두 컬럼이 조건 절에 없더라도 인덱스를 활용하는 스캔 (필요없는 부분은 과감히 스킵스킵!)
클러스터링	검색 속도 향상을 위해 물리적으로 저장하는 것 - 분포도가 넓을 수록(=좋지 않을 수록) 클러스터링이 적합
파티셔닝	테이블을 논리적 단위로 쪼개는 것
파티셔닝 유형	레해리컴 1. 레인지 파티셔닝 2. 해시 파티셔닝 3. 리스트 파티셔닝 4. 컴포지트 파티셔닝
레인지 파티셔닝	숫자나 날짜와 같은 범위 기준으로 파티셔닝
해시 파티셔닝	해시 함수의 값을 기준으로 파티셔닝 (균등 분할 가능)
리스트 파티셔닝	값 목록을 기준으로 파티셔닝
컴포지트 파티셔닝	2개 이상의 파티셔닝을 결합
파티셔닝 장점	성가백합 1. 성능 향상 (액세스 범위가 줄어드니까) 2. 가용성 향상 (데이터 훼손 가능성이 적으니까) 3. 백업 가능 4. 경합 감소
DDL 명령어 요약
테이블 생성	크테테 컬타제 CREATE TABLE 테이블명 (   컬럼명 데이터타입 [제약조건]; )
테이블 생성 제약조건	- PRIMARY KEY - FOREIGN KEY REFERENCES 테이블(컬럼) - NOT NULL - UNIQUE - CHECK (조건 OR 조건) - DEFAULT
테이블 변경	알 에모드 ALTER TABLE 테이블명 ADD 컬럼명 데이터타입 [제약조건]; ALTER TABLE 테이블명 MODIFY 컬럼명 데이터타입 [제약조건]; ALTER TABLE 테이블명 DROP 컬럼명;
테이블 삭제	드롭[캐스|리스] 트케 DROP TABLE 테이블명 [CASCADE | RESTRICT]; TRUNCATE TABLE 테이블명;
뷰 생성	크뷰뷰 에즈 CREATE [OR REPLACE] VIEW 뷰이름 AS SELECT 이름 FROM 학생 WHERE 성별 = 'F';
뷰 삭제	DROP VIEW 뷰이름;
인덱스 생성	크인인 온테커 CREATE INDEX 인덱스명 ON 테이블(컬럼);
인덱스 변경	ALTER INDEX 인덱스명 ON 테이블(컬럼);
인덱스 삭제	DROP INDEX 인덱스명;
참조 무결성	외래키 값은 항상 참조되는 테이블의 기본키여야 함
참조 무결성을 위한 옵션	1. RESTRICT : 참조 무결성 위배 시 연산 거절 2. CASCADE : 해당 튜플을 참조하는 튜플도 함께 삭제 3. SET NULL : 해당 튜플을 참조하는 튜플의 외래키에 NULL 값 삽입 (단, NOT NULL 제약조건 시 연산 거절)
DML 명령어 요약
SELECT 명령어	셀프웨구해오 1. SELECT [ALL | DISTINCT] 컬럼명 [AS 별칭] 2. FROM 테이블 3. WHERE 조건 4. GROUP BY 그룹명 5. HAVING 그룹조건 6. ORDER BY 컬럼명 [ASC | DESC];
WHERE 조건절	- 컬럼 BETWEEN 값1 AND 값2 (= 컬럼 ≥ 값1 && 컬럼 ≤ 값2) - 컬럼 IN (값1, 값2...) - 컬럼 NOT IN (값1, 값2...) - 컬럼 LIKE '패턴' - IS NULL - IS NOT NULL - AND / OR / NOT ex) 나이가 20살~29살이면서 성별이 여자 >> WHERE 나이 BETWEEN 20 AND 29 AND 성별 = '여자';
LIKE 패턴	• % (0개 이상의 문자열과 일치) • [ ] (1개의 문자와 일치) • [^] (1개의 문자와 불일치) • _ (특정 위치의 1개의 문자와 일치) ex) 이름 LIKE '_나%' : 바나나, 가나, 소나기, 김나나나나
조인	두 개 이상의 테이블 연결해 데이터를 검색하는 방법
조인 명령어	프조온웨 ex. SELECT A.번호, B.가격 FROM 상품목록 A JOIN 가격목록 B ON A.번호 = B.번호 WHERE A.번호 IS NOT NULL; // 조인 테이블의 별칭을 FROM에서 지정
논리적 조인	내외교셀 1. 내부 조인 2. 외부 조인 (왼쪽 외부, 오른쪽 외부, 완전 외부) 3. 교차 조인 4. 셀프 조인
내부 조인	양 테이블의 공통 컬럼의 값이 같을 때 사용 INNER JOIN
외부 조인	- 왼쪽 외부 조인 : 왼쪽 테이블 전체 + 오른쪽 테이블 동일 데이터 LEFT OUTER JOIN - 오른쪽 외부 조인 : 오른쪽 테이블 전체 + 왼쪽 테이블 동일 데이터 RIGHT OUTER JOIN - 완전 외부 조인 : 양쪽 모든 데이터 추출 FULL OUTER JOIN
교차 조인	조인 조건이 없는 모든 데이터 조합 추출 CROSS JOIN
셀프 조인	자기 자신에게 별칭을 지정한 후 조인
물리적 조인	네소해 1. Nested-Loop Join (중첩 반복 조인) 2. Sort-Merge Join (정렬 합병 조인) 3. Hash Join (해시 조인)
서브 쿼리 유형	1. SELECT - 반드시 단일 행을 리턴, 집계 함수 주로 사용 ex. SELECT (SELECT MAX(가격)                 FROM 도서 A                 WHERE A.책번호 = B.책번호                 AND 책명 = '마법약') FROM 도서가격 B; 2. FROM - 동적으로 생성된 테이블처럼 사용 ex. SELECT MAX(가격) FROM 도서가격 A, (SELECT 책번호 FROM 도서 WHERE 책명 = 마법약') B WHERE A.책번호 = B.책번호; 3. WHERE ex.WHERE 책번호 IN (SELECT 책번호 FROM 도서 WHERE 책명 = '마법약');
집합 연산자	2개 이상의 테이블의 질의의 결과를 합치는 연산자 (검색을 위해 합치는 조인과 다름) 유형: 유유인마
UNION 연산자	합집합. 중복값을 제거하고 모두 포함
UNION ALL 연산자	완전 합집합. 중복값까지 포함
INSERT 연산자	교집합. 겹치는 데이터만 추출
MINUS 연산자	차집합. 첫 번째 쿼리에만 있고, 두 번째 쿼리에만 없는 결과만 추출
INSERT 명령어	인인투밸 INSERT INTO 테이블명(컬럼1, 컬럼2...) VALUES (데이터1, 데이터2...) ex. INSERT INTO 학생(기숙사, 학년, 성명) VALUES ('그리핀도르', 3, '해리 포터');
UPDATE 명령어	업셋웨 UPDATE 테이블명 SET 컬럼명 = 데이터 WHERE 조건절; ex. UPDATE 학생        SET 기숙사 = '슬리데린' WHERE 이름 = '드레이코 말포이';
DELETE 명령어	딜프웨 DELETE FROM 테이블명 WHERE 조건절; ex. DLETE FROM 학생 WHERE 이름 = '론 위즐리';
DCL 명령어
GRANT 명령어	그온투 GRANT 권한 ON 테이블 TO 사용자 [WITH GRANT OPTION];
REVOKE 명령어	리온프 REVOKE 권한 ON 테이블 FROM 사용자 [CASCADE CONSTRAINT];
데이터 분석 함수 요약
집계 함수	단일값 리턴, "SELECT" 절에 사용 NULL은 없는 데이터로 취급함 1. COUNT : 해당하는 튜플의 수 ex. COUNT(DISTINCT 컬럼) = 중복 제거한 컬럼 수 ex. COUNT(학과) = 해당 학과에 해당하는 튜플의 수 (학생 등) 2. SUM : 합계 3. AVG : 평균 4. MAX : 최댓 5. MIN : 최솟값 6. STDDEV : 표준편차 7. VARIAN : 분산
그룹 함수	그룹화 후 그룹별로 결과를 출력, "GROUP BY" 절에 사용 롤큐그셋 1. ROLLUP : 소계 산출 2. CUBE : 모든 값에 대한 다차원 집계 산출 3. GROUPING SETS : 컬럼별 개별 집계 (순서 무관)
ROLLUP 함수	소계 산출 SELECT 기숙사, 학년, SUM(점수) FROM 점수 GORUP BY ROLLUP(기숙사, 학년);
CUBE 함수	다차원 집계 산출 SELECT 기숙사, 학년, SUM(점수) FROM 점수 GORUP BY CUBE(기숙사, 학년);
GROUPING SETS 함수	컬럼별 개별 집계 산출 SELECT 기숙사, 학년, SUM(점수) FROM 점수 GORUP BY GROUPING SETS(기숙사, 학년, ());
윈도 함수	=OLAP 함수. 종류: 순행비 "SELECT" 절에 사용하며, 함수(로우) OVER(ORDER BY 로우 DESC) 별칭 형태로 사용한다 (✋ OVER 빼먹지 않게 주의!) (✋✋ 별칭 지정할 때 AS 없어도 됨!)
순위 함수	랭덴로 1. RANK (2245) - 동일순위 스킵 2. DENSE_RANK (2234) - 동일순위 그대로 3. ROW_NUMBER (2345) - 그냥 차례대로 ex. // 순위를 보여주는 함수이므로 🙅‍♀️ OVER내 매개변수 없음 🙅‍♀️ SELECT 직업, 레벨, RANK() OVER (ORDER BY 레벨 DESC) A FROM 캐릭터;
행 순서 함수	퍼라락리 1. FIRST_VALUE - 윈도에서 가장 먼저 나오는 값 2. LAST_VALUE - 윈도에서 가장 늦게 나오는 값 3. LAG - 윈도에서 이전 로우의 값 4. LEAD - 윈도에서 다음 로우의 값 ex. // 기준이 될 로우를 OVER내 매개변수로 넣어줘야 함 SELECT 직업, 레벨, FIRST_VALUE(직업) OVER (ORDER BY 레벨 DESC) A FROM 캐릭터;
그룹 내 비율 함수	라투리퍼랭 1. RATIO_TO_REPORT - 그룹의 합을 기준으로 각 로우의 상대적 비율 (로우값/총계) 2. PERCENT_RANK - 그룹에서 먼저 나오는 게 0, 늦게 나오는 게 1로 하여 순서별 백분율 (순위-1)/(총 로우-1) ex. // RATIO_TO_REPORT()는 합계를 계산할 로우를 매개변수로 지정해주되, 🙅‍♀️ 순서를 지정하지 않아도 됨 🙅‍♀️ // PERCENT_RANK()는 0~1 값이 나오므로 🙅‍♀️ 매개변수 불필요 🙅‍♀️, 순서 지정 SELECT 이름, 나이, RATIO_TO_REPORT(레벨) OVER () A, PERCENT_RANK() OVER (ORDER BY 레벨 DESC) B FROM 학생;
절차형 SQL	SQL 안에서도 절차 지향적인 프로그래밍이 가능하도록 하는 트랜잭션 언어
절차형 SQL의 종류	프함트 1. 프로시저 (일련의 쿼리들을 하나의 함수처럼 실행하기 위한 쿼리의 집합) 2. 사용자 정의 함수 (일련의 SQL 처리 후, 그 결과를 단일값으로 반환) 3. 트리거 (DB 시스템에서 삽입, 삭제, 갱신 등의 이벤트 발생 시 관련 작업이 자동으로 수행)
DBMS_OUTPUT	버퍼로부터 메시지를 읽어오는 인터패이스 패키지 DBMS_OUTPUT.PUT(문자열); // 문자열 출력하는 프로시저 DBMS_OUTPUT.PUT_LINE(문자열); // 문자열 출력 후 개행하는 프로시저
절차형 SQL 제어부	1. IF문 IF ~ THEN ELSIF ~ THEN ELSE ~ END IF; 2. Case문 CASE ~ WHEN ~ THEN ELSE ~ END CASE; 3. Loop문 LOOP ~ EXIT WHEN ~ END LOOP; 4. While문 WHILE ~ LOOP EXIT WHEN ~ END LOOP; 5. For Loop문 FOR ~ IN ~ LOOP ~ END LOOP ~
프로시저	일련의 쿼리들을 하나의 함수처럼 실행하기 위한 쿼리의 집합
프로시저 구성	디비컨SET 1. 선언부 (DECLARE) 2. 시작/종료부 (BEGIN/END) 3. 제어부 (CONTROL) 4. SQL 5. 예외부 (EXCEPTION) 6. 실행부 (TRANSACTION)
프로시저 문법	변수의 입출력 구분 [IN | OUT | INOUT] • IN : 운영체제 → 프로시저로 값 전달 • OUT : 프로시저의 결과 → 운영체제로 전달 • IN과 OUT 둘 다 수행
사용자 정의함수	일련의 SQL 처리 후, 결과를 단일값으로 반환하는 절차형 SQL
사용자 정의함수 구성	디비컨SER 1. 선언부 (DECLARE) 2. 시작/종료부 (BEGIN/END) 3. 제어부 (CONTROL) 4. SQL 5. 예외부 (EXCEPTION) 6. 반환부 (RETURN)
트리거	DB 시스템에서 삽입, 삭제, 갱신 등의 이벤트 발생 시 관련 작업이 자동으로 수행되는 절차형 SQL (EVENT 명령어로 실행시점을 인지하고, 외부 변수 IN/OUT 이 없고, TCL 사용 불가)
트리거 구성	디이비컨SE 1. 선언부 (DECLARE) 2. 이벤트부 (EVENT) 3. 시작/종료부 (BEGIN/END) 4. 제어부 (CONTROL) 5. SQL 6. 예외부 (EXCEPTION)
트리거 문법	트리거 실행 순서 [BEFORE | AFTER] • BEFORE : 이벤트 실행 전 • AFTER : 이벤트 실행 후 FOR EACH ROW - 매번 변경되는 데이터의 행의 수만큼 명령어 실행 [OLD | NEW] - 변경 전/후 데이터 중 어떤 걸 쓸지 선택
튜닝 (쿼리 성능 개선)	SQL 실행 계획을 수정해 프로시저 성능을 개선하는 것
튜닝 개선 절차	1. 문제 있는 SQL 식별 (APM 모니터링 도구 사용) 2. 옵티마이저 통계 확인 3. SQL문 재구성 4. 인덱스 재구성 5. 실행계획 유지관리
옵티마이저	SQL을 수행할 최적의 처리경로를 찾는 DBMS의 핵심엔진
실행 계획 (Execution Plan)	옵티마이저가 생성한 처리경로
옵티마이저 유형	1. RBO - 규칙 기반 옵티마이저 2. CBO - 비용 기반 옵티마이저
RBO	- 사전에 등록된 규칙에 따라 실행 계획을 선택함 (규칙=우선순위 기반) - 사용자가 원하는 처리 경로로 유도하기 쉬움
CBO	- 모든 접근 경로를 고려해 실행 계획을 선택함 (비용=수행시간 기반) - 이해도가 낮아도 성능보장 가능
옵티마이저의 역할	1. 쿼리 변환 : SQL을 표준화된 형태로 변환 2. 비용 산정 : 카디널리티, 비용 등을 계산 3. 계획 생성
힌트	힌트를 통해 옵티마이저의 실행 계획을 변경 가능함
주요 옵티마이저 힌트	◦ /*+ RULE */ : 규칙 기반 접근방식 사용 ◦ /*+ CHOOSE */ : 오라클 옵티마이저 디폴트값을 따름 ◦ /*+ INDEX(테이블명 인덱스명) */ : 해당 인덱스 강제 사용 ◦ /*+ USE_NL(테이블명) */ : 조인 방식을 Nested Loop Join으로 설정 ◦ /*+ USE_MERGE(테이블명) */ : 조인 방식을 Sort Merge Join으로 설정 ◦ /*+ USE_HASH(테이블명) */ : 조인 방식을 Hash Join으로 설정