昨天將 SQL 語法中的更新資料 (UPDATE) & 刪除資料 (DELETE)，介紹完畢了，所以這些基本的 INSERT INTO、SELECT 、UPDATE 、 DELETE 語法都算是告一段落了，但是你知道嗎？這些語法都是屬於 SQL 中的 DML(資料調處語言 Data Manipulation Language)

所以今天我們就來介紹 SQL 指令中的四大類別 DDL, DML, TCL, DCL

備註：這四大類只是一個高層次的分類，每個分類下還有更多的指令和變化。此外，不同的資料庫管理系統（DBMS）可能會有一些特定的、非標準的語言擴展，用於處理特定的需求和功能 (PostgreSQL 有 VACUUM 指令，MySQL 使用 SHOW DATABASES; )

DDL (Data Definition Language)

資料定義語言，如同他的名稱一樣，主要用來定義或修改資料庫結構的語言，像是我們常見的 CREATE 就是一種，我們時常會使用 CREATE TABLE，因為這個指令會定義資料庫結構，所以隸屬於 DDL 的分類中

DDL 主要是由 CREATE、ALTER 與 DROP 三個語法所組成

• CREATE: 用於建立新的資料庫(database)、資料表(table)、索引(INDEX)、序列(SEQUENCE)、觸發程序(TRIGGER)或其他結構

-- Example: 建立一個 users 資料表

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    age INT
);

• ALTER: 用於修改現有的資料庫結構，像是新增、刪除、修改資料表中的欄位，同樣也可以用在資料庫、索引...等

-- Example: 修改 users 資料表，新增欄位 email

ALTER TABLE users
ADD COLUMN email VARCHAR(100);

• DROP: 刪除現有的資料庫結構，你可以把資料庫刪掉，資料表刪掉(但是不推薦)，同樣可以用在刪除索引，刪除觸發程序

-- 刪除 users 資料表

DROP TABLE users;

• TRUNCATE: 主要的對象是資料表 (table)，用於快速的將 table 內的資料全部清空，但是基本上不會改變結構，如果有加上 SERIAL 的話(通常用在 id)，連 id 的序列都會被重置

-- 我這邊做了一個 users table 並寫入 100 筆資料

SELECT MAX(id) FROM users;

 max
-----
 100
(1 row)

-- 接著 TRUNCATE table
TRUNCATE users RESTART IDENTITY;  

-- 備註： 加上 `RESTART IDENTITY`， 連同有關聯的序列(SEQUENCE)，一起重置

-- 寫入一筆資料，並驗證序列是否重置

INSERT INTO users (name, email) values ('demo_user', 'demo@email.com');

SELECT MAX(id) FROM users;
 max
-----
   1
(1 row)

DML (Data Manipulation Language)

資料調處語言: 也就是我們前幾天提到的 INSERT SELECT UPDATE DELETE 指令，用於對於資料的操縱 (Manipulation 有操作的意思)，包括插入(新增)、讀取、更新、刪除資料

這邊應該不用在特別介紹了，有興趣的人可以觀看前幾天的文章

• 備註: 有些文件會特別把 SELECT 分出來成 DQL (Data query language)

TCL (Transaction Control Language)

交易控制語言: 主要用於控制和管理交易，交易 Transaction 可以是一個或多個 SQL 語句所組成的，交易(Transaction)也有很重要的特性就是 (ACID, 原子性 隔離性 一致性 持久性)，所以事務控制語言使我們能夠控制事務的行為和處理，保證資料的一致性和完整性

• COMMIT: 命令用來提交(commit) 當前交易中的所有更改，一旦所做的更改被提交，就會成功的寫進資料庫中了

-- 就來舉個銀行帳戶扣款的流程

BEGIN; -- 開啟一個交易

UPDATE accounts SET balance = balance - 100 WHERE name = 'John'; -- 執行資料更新

COMMIT; -- 提交所做的更改

COMMIT 是用於確保資料庫中的資料更改，例如，當銀行轉帳成功後，你會希望使用 COMMIT 來確保資料庫中的資料變更會被保存，這樣即便系統突然崩潰，資料也不會遺失，且兩個賬戶的餘額都是正確的

• ROLLBACK: 用於取消當前交易中的所有更改，從上一次 COMMIT 或交易開始以來的所有更改都會被撤銷

-- 延續上面的例子，如果您發現在更新帳戶餘額時犯了一個錯誤，可以使用 ROLLBACK 取消這個更改

BEGIN; -- 開啟一個交易

UPDATE accounts SET balance = balance - 100 WHERE name = 'John'; -- 執行資料更新

UPDATE accounts SET balance = balance + 100 WHERE name = 'Jenny'; 

ROLLBACK; -- 取消剛剛的更改

是上面的例子，如果轉帳過程中發生了錯誤，我們就可以使用 ROLLBACK 來取消整筆的交易，這樣兩個帳戶的餘額都會保持不變，也不會有錯誤的資料寫入資料庫

• SAVEPOINT: 命令用於在當前交易中創建一個保存點，您可以回滾到這個點而不是整個交易

假設一個訂單處理流程

1. 首先會先檢查庫存，確保產品可用
2. 扣除庫存
3. 處理付款

像這樣多個步驟的流程，如果某一步失敗，你不會想要整筆訂單流程都取消，而是只想取消交易失敗的部分，就很適合使用 SAVEPOINT

BEGIN; -- 開啟一個交易

-- 步驟 1: 檢查庫存
SELECT stock FROM products WHERE id = 1;
-- 假設庫存足夠

SAVEPOINT check_stock; -- 設立一個保存點在檢查庫存後

-- 步驟 2: 扣除庫存
UPDATE products SET stock = stock - 1 WHERE id = 1;

SAVEPOINT deduct_stock; -- 設立一個保存點在扣除庫存後

-- 步驟 3: 處理付款
-- 假設付款處理失敗，我們只想回滾到扣除庫存的部分，而不影響庫存檢查的結果
ROLLBACK TO deduct_stock; -- 回滾到扣除庫存的保存點

-- 重新嘗試付款處理或進行其他錯誤處理

COMMIT; -- 最終提交交易

• SET TRANSACTION: 命令用於設定交易的屬性，例如設定交易的隔離級別。

主要分為：

1. READ UNCOMMITTED -- postgresql 不支援
2. READ COMMITTED
3. REPEATABLE READ
4. SERIALIZABLE

假設線上拍賣平台，用戶們都可以對商品出價。每個出價都會被記錄為一個交易

因為會不希望兩個不同的用戶能夠在同一時間對同一商品出同一價格，這會導致資料會不一致

所以我們可以設定隔離級別為 SERIALIZABLE

-- 用戶 A 的交易
BEGIN;

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

-- 用戶 A 對一個商品出價 $100
UPDATE product SET amount = 100 WHERE item_id= 1 AND user_name = 'A';

COMMIT;

-- 同時，用戶 B 也想對同一個商品出價 $100
BEGIN;

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

-- 由於 SERIALIZABLE 的隔離級別，這個交易會被阻塞，直到用戶 A 的交易完成
UPDATE product SET amount = 100 WHERE item_id = 1 AND user_name = 'B';

COMMIT;

DCL (Data Control Language)

• 資料控制語言: 主要用於控制不同用戶對資料庫對象（如資料表、檢視表(VIEW)）的訪問權限

demo_db=# \du

\du 可以列出現在這個 RDBMS 的所有用戶

DCL 主要包括兩個語句

• GRANT: 賦予用戶訪問資料庫對象的權限。例如，你可以授予用戶查詢某個資料表的權限

-- 給予 john 查詢 employees 資料表的權限

GRANT SELECT ON employees TO john;

• REVOKE: 與 GRANT 相反，REVOKE 則是取消之前授予用戶的權限

-- 取消 john 查詢 employees 資料表的權限

GRANT SELECT ON employees TO john;