《Head First SQL》核心知識圖譜：從規範化到事務的完整筆記結構

《Head First SQL》核心知識圖譜：從規範化到事務的完整筆記結構

本文重構原零散筆記為結構化知識圖譜，涵蓋規範化、連接查詢、事務、常用語句四大核心模組，適合快速複習與查漏補缺。

知識導圖

《Head First SQL》
├── 1. 規範化
│   ├── 1.1 約束
│   ├── 1.2 原子性
│   ├── 1.3 第一范式 (1NF)
│   ├── 1.4 數據模式 (Schema)
│   │   ├── 一對多
│   │   ├── 多對多
│   │   └── 依賴 (函數依賴 T.x → T.y)
│   └── 1.5 更高范式 (2NF/3NF/BCNF)
├── 2. 連接查詢
│   ├── 2.1 交叉連接 (CROSS JOIN / 笛卡爾積)
│   ├── 2.2 內連接 (INNER JOIN)
│   ├── 2.3 子查詢
│   │   ├── 非相關子查詢 (獨立執行)
│   │   └── 相關子查詢 (引用外層)
│   ├── 2.4 外連接 (LEFT/RIGHT JOIN)
│   ├── 2.5 自連接 (Self JOIN)
│   └── 2.6 集合操作 (UNION/INTERSECT/EXCEPT)
├── 3. 事務
│   ├── 3.1 ACID 特性
│   │   ├── 原子性
│   │   ├── 一致性
│   │   ├── 隔離性
│   │   └── 持久性
│   └── 3.2 事務管理
│       ├── BEGIN/START TRANSACTION
│       ├── COMMIT
│       ├── ROLLBACK
│       └── SAVEPOINT
└── 4. 常用語句與注意事項
    ├── DDL (CREATE/ALTER/DROP)
    ├── DML (INSERT/UPDATE/DELETE)
    ├── DQL (SELECT/JOIN/WHERE/GROUP BY/HAVING/ORDER BY/LIMIT)
    ├── DCL (GRANT/REVOKE)
    └── 索引/鎖/隔離級別/優化提示

核心概念精要

1. 規範化三大支柱

• 原子性：列值不可再分（無多值、無重複列組）。
  反例：ingredients: 'flour,milk,egg' 或 student1, student2 多列同義。
• 1NF：所有列原子化，無重複組。
• 函數依賴：T.x → T.y 表示 y 由 x 唯一決定。完全函數依賴、部分依賴、傳遞依賴是 2NF/3NF 判斷依據。

2. 連接查詢類型對比

3. 子查詢分類與改寫

• 非相關子查詢：可獨立執行，優化器常轉為 JOIN。
  SELECT * FROM user WHERE id NOT IN (SELECT user_id FROM orders)
• 相關子查詢：引用外層列，逐行執行，效能差。
  SELECT * FROM user u WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id)
• 改寫原則：優先用 JOIN/EXISTS/NOT EXISTS 替代 IN/NOT IN 子查詢，尤其大表。

4. 事務 ACID 與隔離級別

四大隔離級別（由低到高，鎖/一致性越強）：

1. READ UNCOMMITTED：可讀未提交（髒讀）
2. READ COMMITTED：可讀已提交（不可重複讀）
3. REPEATABLE READ (InnoDB 預設)：可重複讀（幻讀可能，但 Next-Key Lock 解決）
4. SERIALIZABLE：串行化（完全鎖表/範圍）

5. 常用語句模板

-- 建表
CREATE TABLE t (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL DEFAULT '',
  status TINYINT NOT NULL DEFAULT 1,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 條件查詢分頁
SELECT * FROM t WHERE status=1 ORDER BY created_at DESC LIMIT 0,20;

-- 聚合統計
SELECT status, COUNT(*) AS cnt FROM t GROUP BY status HAVING cnt > 100;

-- 事務範例
START TRANSACTION;
UPDATE account SET balance = balance - 100 WHERE id = 1;
UPDATE account SET balance = balance + 100 WHERE id = 2;
COMMIT; -- 或 ROLLBACK;

避坑提示

• NOT IN 子查詢結果含 NULL 會導致空結果集，改用 NOT EXISTS。
• 外連接條件放 WHERE 會退化為內連接，必須寫在 ON。
• COUNT(*) vs COUNT(col)：前者含 NULL 行，後者不含。
• 隱式類型轉換導致索引失效：WHERE varchar_col = 123 應為 WHERE varchar_col = '123'。
• 大事務長時間持有鎖，拆分為小批次提交。

《Head First SQL》以視覺化、對話式風格降低 SQL 學習門檻，建議配合實操練習，將知識圖譜內化為肌肉記憶。