延續上冊，本筆記涵蓋第 7~12 章核心知識點：外鍵與關聯、正規化三大範式、各類 JOIN、子查詢效能抉擇、UNION 集合運算、約束/視圖/交易、權限模型。
第 7 章：多表設計與外鍵
- 外鍵 (FK)：引用父表主鍵（或唯一鍵），確保參考完整性。可為 NULL、可重複。
- 定義：
ALTER TABLE child ADD CONSTRAINT fk_name FOREIGN KEY (col) REFERENCES parent(pk_col); 型別必須完全一致，否則報錯 150。
- 三種關聯：
- 一對一：罕用，通常合併表
- 一對多：FK 在「多」方
- 多對多：需連接表，存雙方 PK
- 組合鍵：多欄位組成 PK
第 7 章（續）：正規化三大範式
| 範式 | 核心規則 | 違反徵兆 | 拆解方向 |
|---|
| 1NF | 欄位原子性（不可再分）；無重複群組（如 color1, color2） | 單欄存多值、多欄存同類值 | 拆分欄位或抽新表 |
| 2NF | 滿足 1NF + 無部分函數相依（非鍵欄位不可僅相依於組合 PK 的一部分） | 訂單明細表：UnitPrice、ProductName 僅相依 ProductID | 抽產品表 |
| 3NF | 滿足 2NF + 無傳遞函數相依（非鍵欄位不可相依於另一非鍵欄位） | 訂單表：CustomerName/Addr 相依 CustomerID（非 PK） | 抽客戶表 |
記憶鑰： 2NF 看「是否完全相依 PK」，3NF 看「是否直接相依 PK（而非透過其他非鍵欄位）」。
第 8 章：連接與多表操作
CREATE TABLE new AS SELECT ...：查詢結果直接建表CROSS JOIN：笛卡爾積INNER JOIN ... ON：等值/非等值/自然連接
第 9 章：子查詢與 IN/EXISTS 抉擇
| 場景 | 推薦 | 理由 |
|---|
| 外表小、內表大 | EXISTS | 外表驅動，用內表索引 |
| 外表大、內表小 | IN | 內表建雜湊，掃外表 |
| 兩表相當 | 差異不大 | |
NOT IN vs NOT EXISTS | 永遠用 NOT EXISTS | NOT IN 遇 NULL 全表掃描；NOT EXISTS 仍用索引 |
IN ('a','b') 等價於多個 OR，但可讀性優、優化器易轉雜湊連接。
第 10 章：外連接、自連接與集合運算
- 左外連接：保留左表所有列，右表無配對則 NULL
- 自連接：表含自參考 FK（如員工-主管），視為兩張同構表
INNER JOIN 自己
UNION：去重合併；UNION ALL：保留重複（效能高）INTERSECT 交集、EXCEPT 差集（MySQL 用 NOT IN/EXISTS 模擬）
第 11 章：約束、視圖、交易
- 約束：
CHECK(col IN ('A','B')), NOT NULL, PK, FK, UNIQUE
- 視圖：虛擬表，
CREATE VIEW v AS SELECT ...，可簡化複雜查詢、做權限隔離
- 交易：
START TRANSACTION → SQL → COMMIT/ROLLBACK，需支援交易引擎
第 12 章：安全性模型
-- 用戶與密碼
SET PASSWORD FOR 'root'@'localhost' = PASSWORD('aaa');
CREATE USER 'conan' IDENTIFIED BY 'pwd';
-- 權限
GRANT SELECT ON table1 TO conan;
REVOKE SELECT ON table1 FROM conan;
-- 角色
CREATE ROLE data_entry;
GRANT SELECT, INSERT, UPDATE ON table1 TO data_entry;
GRANT data_entry TO conan;
DROP ROLE data_entry;
核心觀念： 最小權限原則 → 建角色 → 授角色給用戶，避免直接授予物件權限造成管理爆炸。
- 上一篇文章： SQL 查重與刪重完全手冊：單欄/多欄、保留最大/最小 ID、ROW_NUMBER 現代寫法
- 下一篇文章： PostgreSQL 位圖索引掃描實戰解析：從 EXPLAIN 看 Bitmap Heap/Index Scan 運作細節
Copyright ©
程式碼之家 All Rights Reserved
