[SQL/DB기초] 5주차 정리

 

📌 서브쿼리

: SQL문 (주로 SELECT문)안에 포함되는 SELECT문

  - 메인쿼리 / 서브쿼리

 

✔️ 서브 쿼리가 가능한 위치

▪️ 검색문(SELECT문) 

  - WHERE절 (WHERE절 서브쿼리)

  - SELECT절 (Scalar 서브쿼리)

  - FROM절 (Inline View)

  - HAVING절

  - ORDER BY절

▪️ 갱신문

  - INSERT문의 VALUES절

  - DELETE문의 WHERE절

  - UPDATE문의 SET절

 

❗ 서브쿼리 사용시 주의사항

1️⃣ 컬럼 참조의 제한

  : 일반적으로 서브쿼리는 메인쿼리의 컬럼을 참조할 수 있지만, 메인 쿼리는 서브쿼리의 컬럼을 참조할 수 없음 (메인쿼리에서 볼 때, 서브쿼리는 블랙박스) 

  : but FROM절 서브쿼리의 경우, 메인쿼리는 Inline view의 컬럼을 자유롭게 참조 

2️⃣ ORDER BY절 사용의 제한

  : WHERE절 서브쿼리에서는 ORDER BY절을 사용하지 못함 (ORDER BY은 최종적으로 출력할 튜플을 정렬하는 것이므로!)

  : but Inline view에서는 ORDER BY절 사용

 

🔎 WHERE절 서브쿼리

✔️ WHERE절 서브쿼리 분류 방식

1️⃣ 반환되는 결과의 형태에 따른 분류(연산자에 따른 분류)

- 단일값(scalar subquery)

  : 서브쿼리 결과가 1개 이하의 단일값

  : 단일값 비교 연산자 (=, <>, <, <=, >, >=)

SELECT name, MSRP
FROM products
WHERE MSRP >= (
                SELECT AVG(MSRP) * 2
                FROM products
            );

- 다중값(colum subquery)

  : 서브쿼리 결과가 단일값(1-tuple)의 집합

  : 단일값 비교 연산자(=, <>, <, <=, >, >=) + 한정자(ANY, SOME, ALL)

SELECT *
FROM offices
WHERE officeCode = ANY(
                           SELEC officeCode
                           FROM employees
                           WHERE lastName = 'Patterson'
                      )
ORDER BY officeCode;

 

- 다중행(table subquery)

  : 서브쿼리 결과가 n-tuple의 집합

  : 멤버쉽 연산자(IN), 존재 정량자(EXISTS)

❕ IN 연산자는 '=ANY', '=SOME'으로 대체할 수 있음

❕EXISTS는 항상 연관 서브쿼리로 사용

SELECT *
FROM products
WHERE (productLine, MSRP) IN (
                                 SELECT productLine, MIN(MSRP)
                                 FROM products
                                 GROUP BY productLine
                             )
ORDER BY productLine, name;

 

2️⃣ 동작하는 방식에 따른 분류

- 비연관 서브쿼리

  : 서브쿼리에 메인쿼리 컬럼이 없음

  : 서브쿼리는 한 번 실행됨 (서브쿼리는 메인쿼리에 독립적)

  : 메인쿼리에 서브쿼리 결과를 제공함

- 연관 서브쿼리

  : 서브쿼리에 메인쿼리 컬럼이 있음 (서브쿼리에서 메인쿼리 테이블의 값을 참조)

  : 메인쿼리의 튜플마다 서브쿼리가 실행 (메인쿼리 테이블의 튜플 수만큼 실행)

  : 서브쿼리의 WHERE절에 '연관 조건'을 서술

  : 메인쿼리의 WHERE절에는 서브쿼리를 이용한 튜플 조건식을 서술

  : 메인쿼리 테이블을 필터링 (메인쿼리 실행 결과는 메인쿼리 테이블의 부분 집합)

❗ 조인과 서브쿼리가 모두 가능한 경우라면, 서브쿼리가 성능면에서 우월!

 

SELECT *
FROM products P
WHERE MSRT = (
                  SELECT MIN(MSRP)
                  FROM products p
                  WHERE p.producLine = P.producLine
             );

🌊 실행 흐름

메인 쿼리 테이블 각각의 튜플에 대해

 - 메인 쿼리 값을 읽은 후, 서브쿼리의 연관 조건(WHERE p.producLine = P.producLine)에 그 값(P.producLine)을 대입 (연관 조건의 상수화)

 - 서브쿼리 테이블의 모든 튜플을 엑세스 하면서, 연관 조건을 만족하는 튜플을 선택 

 - 서브쿼리 테이블에서 선택한 모든 튜플에 대해, 서브쿼리 실행

 - 메인쿼리 튜플 조건식에 서브쿼리 실행 결과를 대입

 - 튜플 조건식이 true면, 메인쿼리의 현재 튜플을 선택 → 메인쿼리 실행

SELECT *
FROM customers c
WHERE customerId = ANY (
                            SELECT customerId
                            FROM orders o
                            WHERE o.customerId = c.customerId AND
                                  o.status IN ('Cancelled', 'On Hold')
                       );

 

🙋🏻‍♀️ 서브쿼리와 조인의 차이?

1️⃣ 결과 테이블의 튜플 개수

  - 조인은 두 테이블의 카티션 프로덕트의 부분 집합을 리턴

    : 결과 테이블에는 조인할 두 테이블의 컬럼이 모두 포함

  - 서브쿼리는 메인쿼리 테이블의 부분 집합을 리턴

❗ 질의의 중간 결과가 커지는 것을 막으려면, 조인보다 서브쿼리를 사용

 

2️⃣ 출력 가능한 컬럼

  - 조인은 조인할 두 테이블이 대등한 관계이므로, 두 테이블의 컬럼을 자유롭게 사용

  - 서브쿼리는 메인쿼리 테이블의 컬럼만 사용

❗ 결과의 두 테이블의 컬럼을 모두 포함해야 한다면, 조인을 사용

 

🔎 WHERE절 이외의 서브쿼리

1️⃣ SELECT절 서브쿼리

 : 새로운 컬럼의 생성

: 단일값 서브쿼리(scalar 서브쿼리)만 사용 가능! (주로 집계 함수를 이용해 통계치를 구함)

SELECT city 지점,
       (
           SELECT COUNT(*)
           FROM employees e
           WHERE e.officeCode = o.officeCode
       ) 직원수
FROM offices;

 

2️⃣ FROM절 서브쿼리

 : 임시 테이블 생성

 : Inline view (다중행 서브쿼리) 사용 가능

❕ 일반적인 뷰를 static view라고 하고, 인라인 뷰를 dynamic view라 함

 : Inline view와 WITH절은 기능이 동일

❗ MySQL에서는 inline view에 반드시 table alias를 정의

❗ 가독성을 고려할 때, Inline view보다는 WITH절 사용을 권장

 

🙋🏻‍♀️ 일반적인 서브쿼리와 Inline view의 차이

👩🏻‍🏫 Inline view는 메인쿼리에서 inline view의 컬럼을 자유롭게 참조할 수 있고, ORDER BY절을 자유롭게 사용

 

3️⃣ HAVING절 서브쿼리

 : HAVING절에서 사용하는 서브쿼리

 

✔️ 검색문(SELECT문) 서브쿼리 용도

1. 테이블 필터로 사용(WHERE절 서브쿼리)

2. 새로운 컬럼을 생성(SELECT절 서브쿼리)

3. 임시 테이블을 생성(FROM절 서브쿼리)

 

 

🔎 갱신문 서브쿼리

1️⃣ INSET문 서브쿼리

INSERT INTO employees (employeeId, lastName, firstName, extension, email, jobTitle, officeCode)
VALUES (
            (
                SELECT MAX(employeeId) + 1
                FROM employees
            ),
            'Julia', 'Roberts', 'x1111', 'julia@gmail.com', 'Sales Rep', 1
       );

❗ MySQL에서는 동일 테이블에서 SELECT하여 INSERT/UPDATE할 수 없음 (대신 AUTO_INCREMENT 옵션 사용)

 

2️⃣ DELETE문 서브쿼리

DELETE
FROM employees
WHERE officeCode IN (
                         SELECT officeCode
                         FROM offices
                         WHERE coutry = 'USA'
                    );

 

3️⃣ UPDATE문 서브쿼리

 : UPDATE문에서 사용하는 서브쿼리

 

 

📌 무결성 유지 메커니즘

✔️ 무결성 제약 

- 개체 무결성

  : 기본키(PK) 값은 유일하며(UNIQUE), 널 값을 가질 수 없음(NOT NULL)

- 참조 무결성

  : 외래키(FK) 값은 부모 테이블의 기본키(PK)값과 같거나, NULL값을 가짐

 

✔️ DDL에서 무결성 제약 기술

- PRIMARY KEY 제약조건

  : 개체 무결성 (UNIQUE + NOT NULL)

  : 테이블에서 PRIMARY KEY 제약조건은 한번만 선언

  : PK는 한 개 이상의 컬럼으로 구성(컬럼의 집합)

  : 이 제약조건이 선언되지 않으면, 컬럼들 중에 UNIQUE와 NOT NULL이 같이 설정된 첫번째 컬럼을 PK로 함

  : INSERT/UPDATE문에서 테이블의 PK값으로 NULL 혹은 기존 PK값으로 시도될 경우, DBMS가 이 명령의 실행을 거부

CONSTRAINT pk_order
    PRIMART KEY (orderNo)

- FOREIGN KEY 제약조건

  : 참조 무결성 (자식 테이블의 FK값은 부모 테이블의 PK값 중 하나이거나, NULL)

  : 테이블에서 FOREIGN KEY 제약조건은 여러 번 선언될 수 있음

  : 자식 테이블의 FOREIGN KEY 제약조건에 참조 무결성 옵션이 설정됨

    - ON DELETE <참조_무결성_옵션>

    - ON UPDATE <참조_무결성_옵션>

CONSTRAINT fk_order_customer
        FOREIGN KEY (customerId) REFERENCES customers (customerId)
            ON DELETE RESTRICT
            ON UPDATE CASCADE

❕ 자식 테이블에 대한 INSERT / UPDATE 문에서 부모 테이블의 PK에 존재하지 않는 값이 자식 테이블의 FK값으로 시도될 경우 실행 거부

❕ 부모 테이블에 대한 DELETE / UPDATE 문에서 부모 테이블의 PK값을 삭제 / 수정할 경우 참조 무결성 옵션에 따라 3가지 동작 중 하나를 행함

  ▪️ NO ACTION, RESTRICT : 자식 테이블의 FK에 해당 PK값을 갖는 튜플이 한 개 이상 존재하면, DBMS가 이 명령의 실행 거부

  ▪️ SET NULL, SET DEFAULT : 해당 PK값을 갖는 자식 테이블의 모든 FK값을 NULL값 혹은 디폴트 값으로 수정

  ▪️ CASCADE : 자식 테이블의 FK에 해당 PK값을 갖는 모든 튜플을 연속적으로 삭제/수정

❕ NO ACTION은 표준 SQL의 용어이고, RESTRICT는 MySQL의 용어

❕ MySQL에서는 SET DEFAULT옵션을 지원하지 않음

 

✔️ 참조 무결성 옵션 설정

1️⃣ 무대응 : 디폴트 (삭제 / 수정 거부)

  - ON DELETE NO ACTION

  - ON UPDATE NO ACTION

2️⃣ 소극적 대응 : 안전한 모드, 가장 많이 사용 (삭제 거부 / 수정 허용)

  - ON DELETE NO ACTION

  - ON UPDATE CASCADE

3️⃣ 적극적 대응1 :부모-자식 간에 aggregation 관계 (삭제 / 수정 허용)

  - ON DELETE SET NULL

  - ON UPDATE CASCADE

4️⃣ 적극적 대응2 :부모-자식 간에 composition 관계 (삭제 / 수정 허용)

  - ON DELETE CASCADE : 부모 튜플 삭제 시 자식 튜플도 삭제

  - ON UPDATE CASCADE

❕ 자식, 손자 등 자손 테이블의 FK가 모두 ON DELETE CASCADE이면 부모 튜플이 삭제되면 해당 자손 테이블로 삭제 전파

❗ 두 테이블이 상호 참조하는 경우, 한 쪽 테이블의 FK에 ON DELETE CASCADE 혹은 ON UPDATE CASCADE가 선언되었으면, 다른 쪽 테이블의 FK도 동시에 이 제약이 선언되어야 함

 

📌 뷰

: 하나 이상의 기본 테이블로부터 유도된 가상 테이블

: SELECT문을 사용하여 정의 (뷰는 기본 테이블의 일부만을 보는 window임)

: DBMS는 뷰의 정의(SELECT문)만 저장, 뷰에 대한 검색 요청 시 결과가 동적으로 생성

  - 생성된 데이터는 저장X

  - 뷰는 실행시간에만 임시로 구체화되는 특수한 테이블

❕스키마는 기본 테이블과 뷰로 구성

 

✔️ 뷰의 조작

 - 검색 : SELECT 문으로 뷰를 검색. 검색 제약X (기본 테이블과 동일하게 취급)

 - 갱신 : 뷰의 갱신에는 많은 제약이 따름

❕질의 재작성

 : 질의에 뷰가 사용되면, 뷰의 정의를 참조해서 DBMS가 내부적으로 해당 질의를 재작성하여 실행

CREATE VIEW abnormalOrders AS
SELECT *
FROM orders
WHERE status IN ('Cancelled', 'Disputed', 'On Hold')
WITH CHECK OPTION; # WHERE 절 조건을 위반하는 삽입/수정 거부

❕ 컬럼명을 따로 abnormalOrders (orderNo, ...) 이런식으로 적지 않으면 컬럼명은 상속됨

 

✔️ 뷰의 제거

DROP VIEW abnormalOrders RESTRICT;

- RESTRICT 옵션 : 제거하려는 뷰에서 유도한 다른 뷰가 있으면, 실행 거부 (디폴트)

- CASCADE 옵션 : 제거하려는 뷰에서 유도한 다른 뷰가 있으면 함께 제거 (propagated destroy)

 

✔️ 뷰의 수정

▪️ 수정 가능한 뷰

  : 뷰의 튜플이 기본 테이블의 튜플과 1:1대응 + 뷰의 정의에 기본 테이블의 PK가 포함

▪️ 수정 불가능한 뷰

  : 집계함수, DISTINCT. GROUP BY, HAVING, UNION, SELECT절 서브쿼리, 일부 조인 연산을 사용한 뷰

 

🙋🏻‍♀️ 그래서 뷰는 왜 쓰나요? (장점)

1️⃣ 독립성 : 기본 테이블 구조를 변경해도, 뷰를 통해 그 테이블을 사용하는 응용 프로그램은 영향받지 않을 수 있음

2️⃣ 편리성 : 복잡한 질의의 일부를 뷰로 미리 정의함으로써, 최종 질의를 단순하게 작성할 수 있음

3️⃣ 보안성 : 숨기고 싶은 정보는 제외하고 뷰를 생성하여, 사용자에게 특정 정보를 감춤 (접근 제어)

 

🙋🏻‍♀️ 그러면 뷰의 단점은 뭔가요? (단점)

1️⃣ 독자적인 인덱스가 없음

2️⃣ 데이터 갱신(삽입, 삭제, 수정)에 제약이 많음

3️⃣ 정의를 변경할 수 없음

 

 

📌 데이터 사전

: 데이터에 대한 데이터, 즉 메타데이터(데이터의 의미, 출처, 사용법, 형식을 제공하는 데이터)의 집합

: 데이터 베이스 스키마(테이블 + 뷰) 정의의 집합 ← DBMS에서의 정의

 

데이터베이스 시스템

✔️ MySQL의 INFORMATION_SCHEMA

: 설계자가 DDL로 정의한 스키마 정보를 담고 있는 스키마 (MySQL시스템이 내부적으로 유지, 데이터 사전 역할)

: 스키마 정보가 49개 테이블에 분산 저장됨

  - SCHEMATA 테이블

  - TABLES 테이블

  - COLUMNS 테이블

  - KEY_COLUMN_USAGE 테이블 (PK와 FK에 대한 정보 제공)

  - REFERENTIAL_CONSTRAINTS 테이블 (FK 참조 무결성 옵션에 대한 정보 제공)

  - CHECK_CONSTRAINTS 테이블

: 일반 테이블과 마찬가지로, 검색문으로 내용 검색 가능!

USER INFORMATION_SCHEMA;