📘 7단원 : SQL 응용 - 데이터베이스 기본, 응용 SQL 작성하기, 절차형 SQL 활용하기, 데이터 조작 프로시저 최적화

🎓 1. 데이터베이스 기본

✏️ 트랜잭션

🏷️ 트랜잭션

📘 트랜잭션의 개념

인가받지 않은 사용자로부터 데이터를 보호하기 위해 DBMS가 가져야 하는 특성이자, 데이터베이스 시스템에서 하나의 논리적 기능을 정상적으로 수행하기 위한 작업의 기본 단위

🏷️ 트랜잭션의 특성

1. 원자성(Atomicity) : 분해가 불가능한 작업의 최소 단위
2. 일관성(Consistency) : 트랜잭션이 성공 후 항상 일관된 데이터베이스 상태를 보존해야하는 특성
3. 격리성(Isolation) : 트랜잭션 실행 중 생성하는 연산의 중간 결과를 다른 트랜잭션이 접근 불가능한 특성
4. 영속성(Durability) : 성공이 완료된 트랜잭션의 결과는 영속적으로 데이터베이스에 저장하는 특성

🐥 ACID

🏷️ 트랜잭션의 상태

1. 활동 상태 : 초기 상태, 트랜잭션이 실행 중일 때 가지는 상태
2. 부분 완료 상태 : 마지막 명령문이 실행된 후에 가지는 상태
3. 완료 상태 : 트랜잭션이 성공적으로 완료된 후에 가지는 상태
4. 실패 상태 : 정상적인 실행이 더 이상 진행될 수 없을 때 가지는 상태
5. 철회 상태 : 트랜잭션이 취소되고 데이터베이스가 트랜잭션 시작 전 상태로 환원된 상태

🐥 활부완실철
== 이번달 할부(활부)금 완전 싫죠~(완실철)

🏷️ 트랜잭션 제어

1. 커밋 : 트랜잭션 확정
2. 롤백 : 트랜잭션 취소
3. 체크포인트 : 저장 시기 설정

🐥 커롤체

🏷️ 병행 제어 기법의 종류

1. 로킹 : DB의 무결성과 일관성 보장을 위해 트랜잭션의 순차적 진행을 보장하는 직렬화 기법
2. 낙관정 검증 : 트랜잭션이 어떠한 검증도 수행하지 않고 일단 트랜잭션을 수행하고 종료 시 검증을 수행하여 데이터베이스에 반영하는 기법
3. 타임 스탬프 순서 : 트랜잭션이 갱신한 데이터 사이에 실행하기 전에 타임 스탬프를 부여하여 부여된 시간에 따라 트랜잭션을 수행하는 기법
4. 다중버전 동시성 제어 : 타임스탬프와 접근하려는 타임스탬프를 비교하여 직렬가능성이 보장되는 적절한 버전을 선택하여 접근하도록 하는 기법

🐥 로 낙타다

🏷️ 회복 기법의 종류

📘 로그 기반 회복 기법

1. 지연 갱신 회복 기법 : 트랜잭션이 완료되기 전까지 데이터베이스에 기록하지 않는 기법
2. 즉각 갱신 회복 기법 : 트랜잭션 수행 중 갱신 결과를 바로 DB에 반영하는 기법

🐥 회로체크

📘 체크 포인트 회복 기법 : 장애 방생 시 검사점 이후에 처리된 트랜잭션만 장애 발생 이전의 상태로 복원

📘 그림자 페이징 회복 기법 : 데이터베이스 트랜잭션 수행 시 복제본을 생성하여 복구

✏️ DDL

🏷️ DDL의 개념

데이터를 정의하는 언어

🏷️ DDL의 대상

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

📘 스키마 : 데이터베이스의 구조, 제약조건 등을 담고 있는 기본적인 구조

1. 외부 스키마 : 사용자나 개발자의 관점에서 필요로 하는 DB의 논리적 구조 / 사용자 뷰
2. 개념 스키마 : DB의 전체적인 논리 구조 / 전체적인 뷰
3. 내부 스키마 : 물리적 저장장치의 관점에서 보는 DB의 구조 / 레코드 형식 정의

📘 테이블 : 데이터의 저장 공간

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

📘 인덱스 : 검색을 빠르게 하기 위한 데이터 구조

🐥 도스테뷰인

🏷️ 테이블의 용어

1. 튜플 / 행 / 레코드 / 릴레이션 / 카디널리티 : 가로
2. 애트리뷰트 / 열 / 디그리 / 차수 : 세로
   (도메인 : 애트리뷰트가 취할 수 있는 같은 타입의 원자값 들의 집합)

🏷️ 인덱스의 종류

1. 순서 인덱스 : 데이터가 정렬된 순서로 생성되는 인덱스
2. 해시 인덱스 : 해시 함수에 의해 직접 데이터에 키 값으로 접근하는 인덱스
3. 비트맵 인덱스 : 컬럼에 적은 개수 값이 저장될 경우 선택하는 인덱스
4. 함수기반 인덱스 : 수식이나 함수를 적용하여 만든 인덱스
5. 단일 인덱스 : 하나의 컬럼으로만 구성한 인덱스
6. 결합 인덱스 : 두 개 이상의 컬럼으로 구성한 인덱스
7. 클러스터드 인덱스 : 기본 키 기준으로 레코드를 묶어서 저장하는 인덱스

🐥 순해비함 단결클

🏷️ DDL 명령어

CRATE + ALTER + DROP + TRUNCATE

🐥 크알드트
== 크리스마스를 위한 계란 알 두 트럭

❤️ 컬럼 추가
ALTER TABLE 사원 ADD 전화번호 VARCHAR(11) UNIQUE; 
❤️ 컬럼 수정
ALTER TABLE 사원 MODIFY 전화번호 VARCHAR(11) UNIQUE; 
❤️ 컬럼 삭제
ALTER TABLE 사원 DROP 생년월일;
❤️ 테이블 삭제
DROP TABLE 사원;
❤️ 테이블 내 데이터 삭제
TRUNCATE TABLE 사원;

🧡 뷰 생성
CREATE VIEW 사원뷰 AS
SELECT 사번, 이름
FROM 사원
WHERE 성별 = 'M';
🧡 뷰 교체
CREATE OR REPLACE VIEW 사원 AS
조회쿼리;
🧡 뷰 삭제
DROP VIEW 사원;

💛 인덱스 생성
CREATE INDEX 사번인덱스 ON 사원(사번);
💛 인덱스 수정
ALTER INDEX 사번인덱스 ON 사원(사번);
💛 인덱스 삭제
DROP INDEX 사번인덱스;

✏️ DML

🏷️ DML의 개념

데이터 조작어

🏷️ DML 명령어

SELECT + INSERT + UPDATE + DELETE

🐥 세인업데
== 내 친구 세인이 집에 업데

🏷️ WHERE 절

📘 패턴 : LIKE

1. % : 0개 이상의 문자열과 일치
2. [] : 1개의 문자와 일치
3. [^] : 1개의 문자와 불일치
4. _ : 특정 위치의 1개의 문자와 일치

🏷️ 조인

📘 내부 조인

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

📘 왼쪽 외부 조인

SELECT A.컬럼, A.컬럼2, B.컬럼1, B.컬럼2
FROM 테이블1 A LEFT JOIN 테이블2 B
ON 조인조건
WHERE 검색조건

🏷️ 집합 연산자

1. UNION : 중복 행이 제거된 쿼리 결과를 갖는 집합 연산자
2. UNION ALL : 중복 행이 제거되지 않은 결과를 갖는 집합 연산자
3. INTERSECT : 두 쿼리 결과에 공통적으로 존재하는 결과를 갖는 집합 연산자
4. MINUS : 첫 쿼리에만 있고 두 번째 쿼리에는없는 결과를 반환하는 집합 연산자

💚 데이터 삽입
INSERT INTO 학생(학번, 성명, 학년, 수강과목)
VALUES (6677,'장길산',3,'수학');
💚 데이터 변경
UPDATE 학생
SET 주소 = '인천'
WHERE 이름 = '장길산';
💚 데이터 삭제
DELETE FROM 학생
WHERE 이름 = '장길산';

✏️ DCL

🏷️ DCL의 개념

데이터 제어어

🏷️ DCL 명령어

GRANT + REVOKE

💙 권한 부여
GRANT UPDATE ON 학생 TO 장길산;
💙 권한 회수
REVOKE UPDATE ON 학생 FROM 장길산;

🎓 2. 응용 SQL 작성하기

✏️ 집계성 SQL 작성

🏷️ 데이터 분석 함수의 개념

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

🏷️ 그룹 함수

📘 ROLLUP 함수

SELECT DEPT, JOB, SUM(SALARY)
FROM DEPT_SALARY
[WHERE~]
GROUP BY ROLLUP(DEPT, JOB);
[HAVING~][ORDER BY~]

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

📘 GROUPING SETS 함수 : 집계 대상 컬럼들에 대한 개별 집계를 구하고 순서와 무관한 결과 도출 가능

🏷️ 윈도 함수

📘 윈도 함수 개념

데이터베이스를 사용한 온라인 분석 처리 용도로 사용하기 위해 표준 SQL에 추가된 함수
OLAP 함수라고도 부른다

📘 윈도 함수 분류

1. 순위 함수 : RANK, DENSE_RANK, ROW_NUMBER 함수
2. 행 순서 함수 : FIRST_VALUE, LAST_VALUE, LEG, LEAD 함수
3. 그룹내 비율 함수 : RATIO_TO_REPORT, PERCENT_RANK 함수

🐥 순행비

🏷️ 순위 함수

1. RANK : 동일 순위의 레코드 존재 시 후순위는 넘어감(1위, 2위, 2위, 4위)
2. DENSE_RANK : 동일 순위 시 후순위를 넘어가지 않음(1위, 2위, 2위, 3위)
3. ROW_NUMBER : 동일 순위가 존재해도 무관하게 연속 번호 부여(1위, 2위, 3위, 4위)

🎓 3. 절차형 SQL 활용하기

✏️ 절차형 SQL

🏷️ 절차형 SQL 종류

1. 프로시저 : 일련의 쿼리들을 마치 하나의 함수처럼 실행하기 위한 쿼리의 집합
2. 사용자 정의함수 : 일련의 SQL을 수행하고 수행결과를 단일 값으로 반환가능한 SQL
3. 트리거 : DB에서 이벤트가 발생할 때마다 관련 작업이 자동으로 수행되는 SQL

✏️ 프로시저

🏷️ 프로시저 구성

선언부 + 시작/종료부 + 제어부 + SQL + 예외부 + 반환부

🐥 디비컨 SET
== DB 컨닝 세트

✏️ 사용자 정의함수

🏷️ 사용자 정의함수 구성

선언부 + 시작/종료부 + 제어부 + SQL + 예외부 + 실행부

🐥 디비컨 SER
== DB 컨설팅

✏️ 트리거

🏷️ 트리거 구성

선언부 + 이벤트부 + 시작/종료부 + 제어부 + SQL + 예외부

🐥 디이비컨 SE
== D~B 컨닝 세트

🎓 4. 데이터 조작 프로시저 최적화

✏️ 데이터 조작 프로시저 성능개선

🏷️ 옵티마이저 통계 확인

📘 옵티마이저 개념

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

📘 옵티마이저 유형

규칙기반 옵티마이저(RBO) + 비용기반 옵티마이저(CBO)

📘 힌트 사용

옵티마이저가 항상 최선의 실행 계획을 수립할 수 없어 명시적인 힌트를 제공한다.

🎓 SQL문 정리

< DDL 함수 >
❤️ 컬럼 추가
ALTER TABLE 사원 ADD 전화번호 VARCHAR(11) UNIQUE; 
❤️ 컬럼 수정
ALTER TABLE 사원 MODIFY 전화번호 VARCHAR(11) UNIQUE; 
❤️ 컬럼 삭제
ALTER TABLE 사원 DROP 생년월일;
❤️ 테이블 삭제
DROP TABLE 사원;
❤️ 테이블 내 데이터 삭제
TRUNCATE TABLE 사원;

🧡 뷰 생성
CREATE VIEW 사원뷰 AS
SELECT 사번, 이름
FROM 사원
WHERE 성별 = 'M';
🧡 뷰 교체
CREATE OR REPLACE VIEW 사원 AS
조회쿼리;
🧡 뷰 삭제
DROP VIEW 사원;

💛 인덱스 생성
CREATE INDEX 사번인덱스 ON 사원(사번);
💛 인덱스 수정
ALTER INDEX 사번인덱스 ON 사원(사번);
💛 인덱스 삭제
DROP INDEX 사번인덱스;

< DML 함수 >
💚 데이터 삽입
INSERT INTO 학생(학번, 성명, 학년, 수강과목)
VALUES (6677,'장길산',3,'수학');
💚 데이터 변경
UPDATE 학생
SET 주소 = '인천'
WHERE 이름 = '장길산';
💚 데이터 삭제
DELETE FROM 학생
WHERE 이름 = '장길산';

< DCL 함수 >
💙 권한 부여
GRANT UPDATE ON 학생 TO 장길산;
💙 권한 회수
REVOKE UPDATE ON 학생 FROM 장길산;