SQL 基礎介紹：DDL、DML、TCL、DCL 與交易管理

^{** 提醒自己要練習 SQL，本篇為作者自學筆記，圖與資源來自各大神和ChatGPT}

SQL（結構化查詢語言）可以按照功能分成四種主要的語言類別，每一類別負責不同的資料庫操作。

1. DDL（Data Definition Language，資料定義語言）

常見操作：

• CREATE：建立新的資料表或資料庫。
• ALTER：修改現有的資料表結構。
• DROP：刪除資料表或資料庫。
• TRUNCATE：清空資料表內容，但保留結構。

例子：（參考 ChatGPT）

建立名為 users 的表格
CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    email VARCHAR(100)
);

-- 新增欄位 age 到 users 表格
ALTER TABLE users ADD age INT;

-- 刪除 users 表格
DROP TABLE users;

2. DML（Data Manipulation Language，資料操作語言）

功能： 用來處理資料庫中的資料，主要是查詢、插入、更新和刪除資料。

常見操作：

• SELECT：查詢資料。
• INSERT：插入新資料。
• UPDATE：更新已存在的資料。
• DELETE：刪除資料。

例子：

-- 插入一筆新資料到 users 表格
INSERT INTO users (id, name, email, age)
VALUES (1, 'Alice', 'alice@example.com', 25);

-- 更新 users 表格中 id 為 1 的資料
UPDATE users
SET age = 26
WHERE id = 1;

-- 刪除 users 表格中 age 小於 18 的資料
DELETE FROM users
WHERE age < 18;

3. TCL（Transaction Control Language，交易控制語言）

功能： 用來管理交易（transaction），確保資料的一致性與完整性，常用於多步驟的操作中。

常見操作：

• COMMIT：提交交易，使修改永久生效。
• ROLLBACK：回復上一個交易，取消之前的修改。
• SAVEPOINT：設定交易的保存點，允許回復到某一特定階段。

例子：

-- 開始交易
BEGIN;

-- 插入資料
INSERT INTO users (id, name, email, age)
VALUES (2, 'Bob', 'bob@example.com', 30);

-- 由於發現錯誤，回復交易
ROLLBACK;

-- 重新插入正確的資料
INSERT INTO users (id, name, email, age)
VALUES (2, 'Bob', 'bob.correct@example.com', 30);

-- 提交交易
COMMIT;

4. DCL（Data Control Language，資料控制語言）

功能： 用來控制資料庫的存取權限，保障資料的安全性。

常見操作：

• GRANT：授權給使用者特定的操作權限。
• REVOKE：收回使用者的操作權限。

例子：

-- 給某使用者讀取和插入資料的權限
GRANT SELECT, INSERT ON users TO 'username';

-- 收回該使用者的插入權限
REVOKE INSERT ON users FROM 'username';

Transaction & ACID (dirty read, non-repeatable read, phantom read)

什麼是交易（Transaction）？

在 SQL 中，交易是一組邏輯操作單位，通常包含多個步驟（如插入、更新、刪除）。交易的目標是確保這些操作要麼全部成功，要麼全部失敗，保證資料的一致性和正確性。

範例：銀行轉帳

假設 A 要轉帳 200 元給 B，這筆交易包含以下兩個步驟：

1. 從 A 的帳戶扣除 200 元。
2. 增加 B 的帳戶 200 元。

為了保證這筆交易的完整性，這兩個步驟必須：

• 要麼一起成功（完成轉帳）。
• 要麼一起失敗（如果出錯，A 和 B 的餘額保持原樣）。

ACID 特性

為了設計可靠的交易系統，關聯交易必須具備以下四個特性，稱為 ACID：

1. Atomicity（原子性）

定義：

交易中的所有操作必須視為一個整體（原子單位）。交易是一個大動作，要嘛成功要嘛失敗，沒有成功一半。

• 若交易成功，所有操作都會執行。
• 若交易失敗，所有操作都會回復（Rollback），不留下任何痕跡。

例子：

• 若扣除 A 的 200 元成功，但新增 B 的 200 元失敗，則交易將完全回復，A 的餘額也會回復到初始狀態。

2. Consistency（一致性）

定義：

交易開始前和結束後，資料必須保持一致。換句話說，交易完成後，資料庫的狀態必須符合業務規則和約束條件。

例子：

• A 和 B 的總金額在交易前後不能為負。例如：
• • 交易前：A = 100 元，B = 100 元。
  • 交易後：A = -100 元，B = 200 元。 這個狀況就不能成立。

3. Isolation（隔離性）

定義：

多個交易同時進行時，彼此之間的操作不應互相影響。交易在提交前，它的中間狀態對其他交易是不可見的。這會衍伸出

例子：

• A 正在轉帳 200 元給 B，這筆交易尚未提交時：
• • 其他交易無法看到 A 和 B 的餘額已經改變。
  • 只有當這筆交易成功提交後，其他交易才能看到更新後的結果。
• Dirty read, non-repeatable read, phantom read 的討論。

假設現在有兩個交易正在進行，如果沒有設計好關聯式資料庫的隔離性就會出現以下問題：

Dirty read：其中一個 Transcation 沒有 commit (完成) 另外一個 Transcation 就讀到了。

Non-repeatable read :　

在同一個 transaction 裡面連續使用相同的 Query 讀取了多次資料，但是相同的 Query 卻回傳了不同的結果，這就是 Non-repeatable reads。Dirty read 也是 non-repeatable read 的一種。

Phantom read:

連續搜尋兩次範圍內的筆數，結果不一樣。如 SELECT ... WHERE時，因為其他交易插入或刪除了符合範圍條件的數據，導致相同範圍查詢在同一交易中執行兩次時，結果不同。

• 交易 A：正在查詢一個條件範圍內的數據，例如：
  SELECT * FROM orders WHERE amount > 100;
  查詢結果返回 5 筆資料。
• 交易 B：在此期間插入了一筆新的資料，其 amount > 100，例如：
  INSERT INTO orders (id, amount) VALUES (6, 150);
• 交易 A：再次執行相同的查詢：
  SELECT * FROM orders WHERE amount > 100;
  此時查詢結果返回 6 筆資料，比第一次多出了一筆。

小結：

• Dirty Read： 一個交易讀取到其他交易未提交的數據，可能會讀到錯誤或不穩定的數據。
• Non-Repeatable Read： 在同一交易中，兩次讀取相同條件的資料時，結果不同（通常因其他交易更新或刪除現有數據導致）。
• Phantom Read： 兩次範圍查詢時，結果不同（因其他交易新增或刪除符合條件的數據）。

4. Durability（持久性）

定義：

交易提交後，其結果必須永久保存在資料庫中，即使系統發生故障（如斷電、崩潰），提交的數據也不會不見。

例子：

• A 成功轉帳 200 元給 B，交易提交後，即使伺服器突然重啟，資料庫中 A 和 B 的餘額仍然正確反映交易結果。

交易的操作指令

在 SQL 中，交易可以使用以下指令進行控制：

• BEGIN 或 START TRANSACTION：開始交易。
• COMMIT：提交交易，將變更永久保存。
• ROLLBACK：回復交易，取消所有操作。
• SAVEPOINT：設定保存點，允許回復到特定階段。

為了避免關聯式資料庫出現資料不對稱等問題，在資料庫設計結構時出現「正規化」的要求，拆分資料、消除冗餘、避免異常（例如插入、刪除、更新異常），來確保數據的一致性和完整性。

正規化過程遵循一系列規則（1NF、2NF、3NF 等），每個階段都對表結構有不同的要求。

正規化 Normalization & 反正規化 Denormalization

正規化是分階段進行的，必須先滿足前一階段的要求才能進入下一階段。以下介紹最常用的 1NF、2NF、3NF：

1NF

目標：確保「每個欄位都只能存放一個值」，即消除「重複組」或「多值欄位」。

要求：

1. 表格中的數據是原子性的，每個欄位值不能再進一步分割。
2. 沒有重複的欄位組（row, tuples）。

我們先來看以下的舉例，這份表格哪裡不符合第一正規化（1NF）？

圖片來源：影片 1st, 2nd and 3rd Normal Form (Database Normalisation)

1. 重複的值，解決方式是加上唯一值的 ID 當主鍵。
2. Order 裡面有多個值，解決方是另外建一個表再連結

2NF

目標：消除「部分依賴」，即所有 key 要完全依賴整個 Primary Key。

要求：

1. 必須先符合 1NF。
2. 所有非Primary Key 欄位必須依賴於「整個 Primary Key」，而不是只有一部份的 Primary Key。

下圖是違反 2NF 的案例Student ID 跟 Course ID 是複合鍵，兩個一組是 Primary Key。但學費（Course Fee）跟 Student ID 沒有關係，所以只有依賴 Course ID。

解決方是一樣是分成兩個表格：

3NF

目標：消除「Transitvie Dependency」，即非主鍵欄位不應依賴於其他非主鍵欄位，每個欄位都要跟主鍵有「直接」關係。

要求：

1. 必須先符合 2NF。
2. 非主鍵欄位只依賴於主鍵，而不能通過其他非主鍵欄位間接依賴。

下圖中，班級名稱和學生ID完全沒有關係，班級名稱是依賴在班級ID上，這就出現Transitvie Dependency 傳遞依賴，我的理解是「間接相關」。 ​

解法：

正規化的壞處是找資料需要連結多個表格，查找效率低弱。於是誕生

反正規化 （Denormalization）

例如：訂單表和商品價格表以正規化分開如下

此表由 ChatGPT 生成

如果我們需要查詢訂單的總金額（數量 × 商品價格）需要用JOIN 把兩個表合起來，如果是上千萬筆的電商資料可能就會使查詢效率變慢。

SELECT 
    o.訂單ID, 
    o.客戶ID, 
    o.購買數量 * p.商品價格 AS 總金額
FROM 
    Orders o
JOIN 
    Products p 
ON 
    o.商品ID = p.商品ID;

所以這時候使用反正規化的作法，可以將程式碼節省成

SELECT 訂單ID, 客戶ID, 總金額 FROM Orders WHERE 訂單日期 = '2024-11-25';

小結

正規化的優點：

• 數據容易維護。
• 更新只需要更新一個表。

反正規化適用於：

• 查詢頻繁且要求回復速度的系統。
• 需要頻繁計算的數據（如總金額、總銷量），而數據更新相對較少的情況。