DB 기말 준비

DB09cdef - Advanced SQL

뷰의 특징

• 실제로 기존 테이블을 복사하거나 새로운 테이블을 만드는 게 아닌, 원래 테이블에 쿼리를 걸어서 필요한 데이터만 보여주는 방식

뷰의 생성

# 형식
CREATE VIEW <뷰이름> AS 
	쿼리
;

# 예시
CREATE VIEW dept_names AS 
    SELECT dept_name
    FROM instructor
;

뷰의 속성 이름 명시적 지정 (can be specified explicitly)

SUM(salary) 같은 계산식(expression)은 자동으로 컬럼 이름이 생기지 않거나 이름이 애매할 수 있다.

그래서 뷰를 만들 때 직접 컬럼 이름을 정해줘야 더 명확하게 사용할 수 있다.

CREATE VIEW departments_total_salary(dept_name, total_salary) AS
    SELECT dept_name, SUM(salary)
    FROM instructor
    GROUP BY dept_name;

• dept_name은 원래 테이블에 있는 컬럼 이름이니까 그대로 사용해도 되지만,

• SUM(salary)는 컬럼 이름이 없어서 → total_salary라는 이름을 직접 정해준 것이다.

뷰의 확장 (View expansion)

• 뷰의 확장 : 다른 뷰를 기반으로 정의된 뷰의 의미를 정의하는 방법

# 예시 상황
-- 뷰 v2 정의
CREATE VIEW v2 AS
SELECT id, name FROM instructor;

-- 뷰 v1 정의 (v2 사용)
CREATE VIEW v1 AS
SELECT name FROM v2 WHERE id < 100;

# v1을 실제로 실행하려면 내부적으로 이렇게 바뀐다.
SELECT name FROM (SELECT id, name FROM instructor) AS v2 WHERE id < 100

• 이 과정을 View Expansion, 즉 “뷰 펼치기” 라고 한다.

• 멈추는 조건 : 뷰 안에 또 다른 뷰가 있고… 계속되면 무한 루프가 될 수 있지만, 재귀적으로 자기 자신을 포함하지 않는 한, 이 과정은 언젠가는 반드시 끝난다.

뷰의 종류

1. 가상 뷰 (Virtual View): 데이터베이스에 저장되지 않고, 쿼리만 존재하는 뷰

   • 사용하는 경우

     • 데이터가 자주 바뀌는 경우

     • 결과가 작고 간단한 쿼리인 경우

2. 실체화 뷰 (Materialized View): 실제로 생성되어 저장되는 뷰

   • 사용하는 경우

     • 결과 계산이 오래 걸리는 복잡한 쿼리인 경우

     • 대시보드용 실시간 응답이 필요한 경우

     • 분석 시스템에서 많이 사용하는 경우

   • 특징

     • 테이블처럼 저장되지만, 직접 수정이 불가능하다

     • 'REFRESH MATERIALIZED VIEW sales_summary;'와 같이 수동갱신해줘야 한다.

뷰에 Insert하는 방법

1. 단순한 뷰일 경우 (한 테이블을 기준으로 하고, 조건도 없고, 모든 컬럼을 포함한다면)
   → 데이터를 삽입하거나 수정할 수 있어요.
   : 예를 들어 instructor 테이블 전체를 보여주는 뷰를 만들고 여기에 튜플을 추가하면, 이는 원본 테이블에 그대로 반영돼요.

2. 한 테이블 기반이긴 하지만 일부 컬럼이 누락된 경우
   → 업데이트는 가능하지만, 누락된 컬럼(예: salary)은 null로 채워지거나, 제약조건(NOT NULL)에 의해 오류가 날 수 있다.: 예를 들어 salary 컬럼이 빠진 뷰에 INSERT하려고 하면, DBMS는 그 값을 어디서 가져와야 할지 모르기 때문에 문제를 일으켜요.

3. WHERE 조건이 있는 뷰에 데이터를 넣는 경우
   → 삽입은 가능하지만, 조건에 맞지 않으면 삽입된 데이터가 뷰에 보이지 않게 돼요.: 예를 들어 dept_name = 'History' 조건이 있는 뷰에 dept_name = 'Biology'인 데이터를 넣으면, 실제 테이블에는 추가되지만 뷰에서는 안 보여요. 이건 무결성 논란을 만들 수 있어요.

4. 두 개 이상의 테이블을 조인한 뷰인 경우
   → 삽입이나 수정이 불가능해요.: 왜냐하면 DB가 “이 데이터는 instructor에 들어가야 해?”, “아니면 department에 들어가야 해?”라는 식으로 명확하게 판단할 수 없기 때문이에요. 따라서 MySQL 같은 시스템은 이때 “조인 뷰에는 삽입할 수 없습니다”라는 오류를 반환해요.

5. 집계 함수(SUM, COUNT 등)를 사용하는 뷰
   → 이런 뷰는 삽입이나 수정이 원천적으로 불가능해요.: 예를 들어 부서별 교수 수를 보여주는 뷰에 “CS 부서 교수 수는 10명이야!“라고 INSERT를 하려고 해도, 이건 실질적으로 어떤 교수를 추가해야 하는지 DB가 판단할 수 없기 때문에 막히는 거예요.

6. DISTINCT를 포함하거나 GROUP BY, HAVING 같은 절이 있는 뷰
   → 마찬가지로, 이들도 결과가 요약된 형태이기 때문에 어떤 데이터를 원본 테이블에 추가해야 할지 판단할 수 없어요.: 따라서 업데이트는 허용되지 않아요.

7. 실체화 뷰(Materialized View)의 경우
   → 사용자는 직접 데이터를 INSERT하거나 UPDATE할 수 없어요.: 실체화 뷰는 “쿼리 결과를 미리 저장한 테이블 같은 뷰”이기 때문에, 데이터가 변경되었을 경우에는 REFRESH MATERIALIZED VIEW 같은 명령으로 갱신해야만 최신 상태를 유지할 수 있어요. 즉, 직접 수정하는 게 아니라, 쿼리를 다시 실행해서 내용 전체를 바꾸는 방식이에요.

Summary: 단순한 한 테이블 기반 뷰는 대부분 수정이 가능하지만, 조건이 있거나 조인/집계/그룹화가 포함된 복잡한 뷰는 데이터 변경이 어렵거나 불가능합니다.(실체화 뷰는 쿼리 결과를 저장해둔 것이므로 직접 수정하지 않고 주기적으로 갱신해야 합니다.)

윈도우 함수

• 의미 :현재 행을 기준으로 주변 행들과의 관계를 계산하는 함수

• 특징

  • 일반적인 집계 함수(SUM, AVG, COUNT)는 여러 행을 하나의 값으로 요약하지만,
    윈도우 함수는 각 행마다 계산된 결과를 따로 보여준다.

  • GROUP BY 절과는 함께 사용할 수 없다.

  • 원래 있던 행(레코드) 개수는 그대로 유지되고,
    그 옆에 계산된 값이 “추가 컬럼”으로 붙는다.

• 종류

  • Aggregate window functions (집계 윈도우 함수):SUM(), MAX(), MIN(), AVG(), COUNT(), …

  • Ranking window functions (순위 윈도우 함수):RANK(), DENSE_RANK(), PERCENT_RANK(), ROW_NUMBER(), NTILE(), CUME_DIST(), NTH_VALUE()

  • Value window functions (값 기반 윈도우 함수):LAG(), LEAD(), FIRST_VALUE(), LAST_VALUE()

# 문법
SELECT WINDOW_FUNCTION ( [ ALL ] expression )
OVER ( [ PARTITION BY partition_list ] [ ORDER BY order_list] )
FROM table;

# 예시
SELECT name, salary, sum(salary) OVER (PARTITION BY dept_name) SUM_SALARY
FROM instructor;

• 사용 문법

  • WINDOW_FUNCTION : SUM(), AVG(), RANK(), ROW_NUMBER() 같은 윈도우 함수 이름

  • ALL : 사용 시, 중복 포함하여 전부 계산된다. (DISTINCT를 미지원)

  • OVER : 윈도우 함수가 “어떤 기준으로 계산할지” 정의하는 공간

    • PARTITION BY : 행을 그룹으로 나누는 기준

    • ORDER BY : 각 그룹(파티션) 안에서 정렬 기준을 지정

윈도우 함수는 OVER 절을 통해 계산 범위(PARTITION)와 정렬 기준(ORDER)을 지정해서, 각 행마다 통계나 순위를 계산해주는 기능이다.(행은 줄어들지 않고, 계산 결과가 컬럼처럼 덧붙는다.)

윈도우함수 사용 예시

1) Aggregate Window Functions (집계 윈도우 함수)

? 누적 합계, 평균, 최대/최소값 등을 구하면서도 행은 줄이지 않고 결과를 붙이는 함수

• 누적 통계

• 그룹 내부의 평균이나 전체 평균 표시

• 테이블 줄이지 않고 분석용 값 덧붙임

2) Ranking Window Functions (순위 윈도우 함수)

? 정렬 기준에 따라 순위나 위치를 계산하는 함수

• 순위 표시, 상위/하위 N명 뽑기

• 분위 수, 백분위 통계

• 그룹 내부에서 순서 기반 판단

3) Value Window Functions (값 기반 윈도우 함수)

? 다른 행의 값을 현재 행에서 참조하는 함수

? 사용 목적:

• 이전/다음 행과 비교

• 변화 감지, 트렌드 분석

• 그룹의 시작/끝 값 추출

✅ 실생활 예시로 요약

함수 종류역할예시 상황

원하시면 각 함수별 샘플 쿼리와 결과 표도 이어서 만들어드릴 수 있어요!

ROWS BETWEEN 절

: 윈도우 함수가 현재 행을 기준으로, 어디서부터 어디까지의 행을 계산 범위(=윈도우)로 삼을지 정하는 구문

ROWS BETWEEN ... AND ...은 윈도우 함수가 어떤 범위의 행을 보고 계산할지 설정하는 방식으로,누적합, 이동평균, 슬라이딩 통계 등 유연한 분석이 가능하게 해준다.

• INTERVAL : 날짜나 시간 계산 시 쓰는 “기간 표현”

  • ex) RANGE BETWEEN INTERVAL 2 DAY PRECEDING AND CURRENT ROW
    현재 행의 날짜 기준으로 “2일 전부터 오늘까지”를 범위로 지정하는 구문
    

RANK

• ROW_NUMBER() 모든 행에 고유한 순번. 중복 무시. -> 1, 2, 3, 4,

• RANK() 중복 순위 인정, 다음 순위는 건너뜀 (희소 순위). -> 1, 2, 2, 4

• DENSE_RANK() 중복 순위 인정, 다음 순위는 연속 (조밀 순위). -> 1, 2, 2, 3

WINDOW 절 사용

# 문법
SELECT ...
       함수() OVER 윈도우_이름
       ...
FROM 테이블
WINDOW 윈도우_이름 AS (
    [PARTITION BY ...]
    [ORDER BY ...]
    [ROWS BETWEEN ... AND ...]
);

# 예시
SELECT ENAME, SAL, JOB, HIREDATE,
       RANK() OVER w AS RANK_BY_HIREDATE,
       ROW_NUMBER() OVER w AS ROW_NUM
FROM EMP
WINDOW w AS (
  PARTITION BY JOB
  ORDER BY HIREDATE DESC
);

Key

• 의미 : 릴레이션에서 하나의 튜플을 고유하게 식별할 수 있도록 돕는 속성 또는 속성들의 집합

• 필요성

  • 데이터의 고유성 강제 (force identity of data)

  • 데이터의 무결성 유지 (ensure integrity od data is maintained)

  • 릴레이션 간 관계 설정 (establish releationship between relations)

• 종류

  • Super key (슈퍼 키) : 튜플(행)을 유일하게 식별할 수 있는 속성 또는 속성들의 집합
    -> 유일성만 보장되면 OK, 컬럼 많이 써도 됨

  • Candidate key (후보 키) : 슈퍼 키 중에서 중복 속성이 없는 최소 키
    -> 최소한의 컬럼만 사용, super key 중에서 추림
    

  • Primary key (기본 키) : 후보 키 중에서 기본 식별자로 선택된 키, 중복과 NULL 불가
    -> candidate key 중 하나를 선택해서 사용하는 것

  • Alternate key (대체 키) : 후보 키 중 기본 키로 선택되지 않은 나머지 키
    -> 선택되되 않은 나머지 후보 키

  • Foreign key (외래 키) : 다른 테이블의 기본 키를 참조하는 속성, 테이블 간 관계를 나타냄
    -> 다른 테이블의 primary key를 참조

  • Composite key (복합 키) : 둘 이상의 속성을 조합해 하나의 후보 키 또는 기본 키로 사용하는 경우
    -> 여러 속성 합쳐쳐 key (예 : (student_id, course_id)

  • Compound key (결합 키) : 복합 키와 유사하나, 각 속성이 개별적으로는 키가 아님
    -> 마찬가지로 여러 개 조합, 단 개별 속성은 식별 불가능
    -> 복합 키 중 하나 이상의 속성이 외래 키이면 결합키!

  • Surrogate key (대리 키) : 의미 없는 인공적인 키(예: 자동 증가 숫자)로, 데이터 식별만을 위해 사용됨
    -> 의미 없는 ID, 그냥 유일하게 식별만 가능하게 함

DB10 - Transaction

트랜잭션

• 의미 : 하나의 논리적인 작업 단위로, 여러 SQL 쿼리(조회/수정)를 묶어서 전부 실행되거나, 전혀 실행되지 않아야 하는 것

  • Indivisible: Either execute entirely or not at all불가분성: 전부 실행하거나 전혀 실행되지 않음

• 필요성 : 여러 사용자가 동시에 DB를 사용해도, 데이터 충돌 없이 안전하게 처리되게 하기 위해서

• 구성 : 트랜잭션은 여러 쿼리들을 묶어서 하나의 일 처리처럼 다루고, 원자성과 순서를 보장

  • 트랜잭션은 단순히 여러 SQL 문장이 아니라, “논리적으로 하나인 묶음”

  • 트랜잭션은 원자성(atomicity)과 직렬성(serializability)을 보장해요

• 실행 방식

  • SQL 문이 실행되면 자동으로 트랜잭션이 시작

  • 반드시 COMMIT 또는 ROLLBACK으로 종료

  • COMMIT: 변경 내용을 DB에 영구 반영

  • ROLLBACK: 변경 내용 모두 취소, 원상복구

    • 제약 조건 위반, 0으로 나누기 등의 오류가 발생하면 자동으로 ROLLBACK 됨

• 명시적인 트랜잭션 선언 : 개발자는 START TRANSACTION;을 사용해서 트랜잭션의 시작을 선언할 수 있다.

  • 이 구문은 뒤따르는 여러 쿼리들이 하나로 묶여서 반드시 함께 실행되어야 한다는 걸 명확히 한다.

  • 반대로, 트랜잭션 없이 쓰인 SQL 문은 그 자체로 하나의 단일 트랜잭션으로 취급된다.

START TRANSACTION;
UPDATE ...
DELETE ...
COMMIT; -- 또는 ROLLBACK;

트랜잭션 예시 (1)

START TRANSACTION;

UPDATE accounts
SET balance = balance + 100
WHERE accNo = 456;

UPDATE accounts
SET balance = balance - 100
WHERE accNo = 123;

COMMIT;

이체 처리 두 개의 쿼리를 하나의 트랜잭션으로 묶어서,
중간에 오류가 생기면 ROLLBACK으로 둘 다 취소,
성공하면 COMMIT으로 둘 다 저장됩니다.

트랜잭션 예시 (2)

START TRANSACTION;
SELECT @A := SUM(salary) FROM instructor WHERE dept_name = 'Comp. Sci.';
UPDATE budget_summary SET summary = @A WHERE dept_name = 'Comp. Sci.';
COMMIT;

• 상황instructor 테이블에서 ‘Comp. Sci.’ 학과 교수들의 급여 총합을 구한 후, 그 값을 budget_summary 테이블에 업데이트하려는 과정입니다.

트랜잭션 예시 (3)

SELECT * FROM sales_history;           -- 현재 상태 확인
START TRANSACTION;
DELETE FROM sales_history;            -- 전체 삭제
SELECT * FROM sales_history;          -- 확인하면 비어 있음
ROLLBACK;                             -- 취소
SELECT * FROM sales_history;          -- 복원됨

• 상황sales_history의 전체 데이터를 삭제했다가,
  다시 ROLLBACK을 통해 원래대로 되돌리는 시나리오입니다.

세션 변수

• 의미 : SQL 실행 중 임시로 값을 저장해두고 다음 쿼리에서 그 값을 재사용할 수 있게 해주는 변수

• 세션 단위로 유지되며, 세션이 종료되면 자동으로 사라진다.

• 대표적인 형식은 @var_name

• 특징

  • Declaration is not required선언이 필요하지 않음

  • Data type: Defined at the assignment데이터 타입: 할당 시점에 정의됨

  • Scope: Until the end of the current session범위: 현재 세션이 끝날 때까지

트랜잭션 예시 (4) - 경쟁 시나리오

: 이 예시는 동시성(concurrency) 문제와 트랜잭션의 필요성을 설명하는 아주 대표적인 시나리오

• 개념 요약

  • 여러 사용자가 동시에 같은 테이블에 접근하고 수정할 때, 예상치 못한 결과나 불일치가 생길 수 있습니다.

  • 이를 막기 위해, 관련 작업들을 트랜잭션 단위로 묶어야 합니다.

• 기본 상황

  • 테이블: Sells(store, chocobar, price)

  • 초기 데이터: Joe’s Store에서는 Snickers($1.00), Twix($1.50) 판매 중

  • Sally: Joe’s Store의 최고가, 최저가를 조회하려 함 → MAX, MIN 사용

  • Joe: Snickers와 Twix를 삭제하고 M&M’s($2.00)를 새로 추가하려 함

• 1) 문제가 생긴 경우 (이상한 실행 순서)

  • 실행 순서 : (max) → (del) → (ins) → (min) 라고 가정해보자.

  • 중간 상태 변화

    • (max): Snickers, Twix 기준 → 결과: 1.50

    • (del): Snickers, Twix 삭제

    • (ins): M&M’s 추가

    • (min): M&M’s 기준 → 결과: 2.00

  • 결과: MAX = 1.50, MIN = 2.00 → 말이 안 됨! → MAX < MIN이 되어 버린다.

  • 해결 방법: 트랜잭션 사용

    • Sally의 쿼리 (max)(min)을 트랜잭션으로 묶으면, 일관된 시점의 데이터만 볼 수 있음
      → MAX와 MIN이 동일한 데이터 집합에서 계산되므로 이상한 결과가 없음

    • START TRANSACTION;SELECT MAX(...);SELECT MIN(...);COMMIT;

• 2) 또 다른 문제: 롤백으로 인한 유령 데이터

  • Joe가 아래 작업을 했다고 가정
    DELETE ...;
    INSERT ...;
    ROLLBACK;

    • 근데 Sally가 Joe가 INSERT한 후, ROLLBACK 전에 조회하면?
      → Sally는 임시로만 존재했던 M&M’s 2.00을 볼 수 있음
      → 이 데이터는 실제로 DB에는 존재하지 않게 될 데이터

  • 해결책 : Joe의 (DELETE)(INSERT)를 트랜잭션으로 묶으면:

    • COMMIT 전에는 다른 사용자가 볼 수 없음

    • ROLLBACK 시에는 변경사항이 아예 취소됨

    • → Sally는 절대로 임시 데이터를 볼 일이 없음!

    • START TRANSACTION;DELETE ...;INSERT ...;-- COMMIT 또는 ROLLBACK

ACID 속성

트랜잭션의 상태

직렬 가능(Serializable)

• 의미 : 여러 트랜잭션이 동시에 실행되더라도, 결과가 마치 하나씩 차례대로 실행된 것처럼 보여야 한다

Read-Only Transactions (읽기 전용 트랜잭션)

• 의미 : 말 그대로 데이터를 읽기만 하고, 수정(쓰기)은 하지 않는 트랜잭션

• 기본 트랜잭션은 기본적으로 읽기와 쓰기 둘 다 가능하게 열려 있다.

SET TRANSACTION READ ONLY;
START TRANSACTION;
-- SELECT문만 실행 가능
COMMIT;

• 장점 : 병렬 처리 성능이 좋아짐

  • READ WRITE 트랜잭션은 데이터에 영향을 줄 수 있으므로,→ DB는 락(lock) 을 걸거나, 충돌을 조심해야 함

  • 반면 READ ONLY 트랜잭션은 데이터를 건드리지 않으니
    → DB 입장에서 “얘는 걍 보기만 하네? 동시에 여러 명이 봐도 괜찮아~”

  • 따라서 락을 줄이거나 제거하고, 병렬 실행을 더 공격적으로 허용할 수 있어요

READ ONLY 트랜잭션은 읽기만 하는 트랜잭션으로 선언함으로써,데이터베이스의 동시성 처리와 성능을 더 향상시킬 수 있는 안전하고 효율적인 방법이다.

Isolation Level (고립 수준)

• 의미 :여러 트랜잭션이 동시에 데이터베이스에 접근할 때, 얼마나 서로 간섭을 허용할지를 설정하는 기준

# 문법
SET TRANSACTION ISOLATION LEVEL <level>;
START TRANSACTION;
...
COMMIT; or ROLLBACK;

• 고립 수준을 나누는 이유

  • 고립 수준이 낮으면: 동시 실행 많아짐 → 처리량 ↑ (but 무결성 ↓)

  • 고립 수준이 높으면: 충돌 방지, 데이터 정확도 ↑ (but 병렬성 ↓)

• 각 고립 수준에서 발생 가능한 문제

• 각 고립 수준의 예시

  1. ▶ Dirty Read (READ UNCOMMITTED)

     • T1: A = A - 50 (아직 commit 안 함)

     • T2: A를 읽으면 이미 50이 빠진 값이 보임 ❌

  2. ▶ Non-repeatable Read (READ COMMITTED)

     • T1: SELECT * FROM account WHERE id = 1 → A = 100

     • T2: UPDATE account SET A = 200 WHERE id = 1 → commit

     • T1: 다시 SELECT하면 A = 200 ❗

  3. ▶ Phantom Read (REPEATABLE READ)

     • T1: SELECT * FROM flights WHERE seat_class = ‘Business’ → 10개 좌석

     • T2: INSERT INTO flights (seat_class=‘Business’) → commit

     • T1: 다시 SELECT 하면 결과가 11개 ❗

Isolation Level은 트랜잭션이 다른 트랜잭션과 얼마나 영향을 주고받을 수 있는지를 설정하는 기준이며,고립 수준이 높을수록 데이터 정확성은 보장되지만, 성능(동시성)은 떨어지게 됟나.

한 번에 정리하는 고립 수준

• 격리 수준이란, 트랜잭션끼리 서로 얼마나 간섭하지 않도록 할지(얼마나 격리할지)를 정하는 기준.

• 격리 수준이 높을수록 데이터는 안전해지고, 낮을수록 성능은 빨라지지만 오류 가능성이 높아져요.

1. SERIALIZABLE (가장 안전, 가장 느림)

• 모든 트랜잭션이 마치 “순서대로 한 명씩” 실행된 것처럼 동작

• 접근하는 데이터는 전부 잠금(lock)

• 다른 트랜잭션은 읽기, 쓰기, 삽입 전부 못함

• 데이터 무결성 최고지만, 성능 저하 큼

• ? “줄 서서 한 명씩만 처리하자”

2. REPEATABLE READ (MySQL 기본값)

• 같은 SELECT 쿼리를 여러 번 해도 같은 결과만 보여야 함

• 트랜잭션이 시작되기 전에 커밋된 데이터만 읽음

• 다른 트랜잭션이 **새로운 행(phantom rows)**을 삽입하는 건 막지 못함

• → “팬텀 리드”는 발생할 수 있음

• ? “내가 본 데이터는 그대로지만, 누가 새로 넣을 수는 있어”

3. READ COMMITTED (많이 사용됨)

• 커밋된 데이터만 읽음

• 다른 트랜잭션이 중간에 값을 바꾸면, 같은 쿼리라도 결과가 달라질 수 있음

• → “Non-repeatable read” 발생 가능

• ? “다른 사람이 커밋한 값은 볼 수 있어”

4. READ UNCOMMITTED (가장 위험, 빠름)

• 아직 커밋되지 않은 데이터도 읽을 수 있음

• → “Dirty read” 발생 가능

• 다른 트랜잭션이 rollback하면 내가 읽은 데이터가 사라질 수도 있음

• 성능은 좋지만 데이터 신뢰성 거의 없음

• ? “남이 쓰는 중인 값도 그냥 막 봄 (지나가던 메모장 훔쳐봄 느낌)”