DDL、DML、DCL 深度解析：SQL 三大語言分類的定義、差異與實務運用

SQL 指令依功能分為三大類：DDL (資料定義)、DML (資料操縱)、DCL (資料控制)。理解其分類邊界、核心指令與權限模型，是資料庫設計、開發與管理的基石。

一、總體對照表

二、DDL - 資料定義語言：管理「容器」與「骨架」

核心指令詳解

1. CREATE：建立物件

   CREATE DATABASE SalesDB;
   CREATE TABLE Customers (CustID INT PRIMARY KEY, Name NVARCHAR(50) NOT NULL);
   CREATE INDEX IX_Customers_Name ON Customers(Name);
   CREATE VIEW V_ActiveCustomers AS SELECT * FROM Customers WHERE Status=1;
   CREATE PROCEDURE usp_GetCustomer @id INT AS SELECT * FROM Customers WHERE CustID=@id;
   

2. ALTER：修改物件結構

   ALTER TABLE Customers ADD Email NVARCHAR(100);
   ALTER TABLE Customers ALTER COLUMN Name NVARCHAR(100) NOT NULL;
   ALTER INDEX IX_Customers_Name ON Customers REBUILD;
   

3. DROP：永久刪除物件

   DROP TABLE OldTable;
   DROP INDEX IX_Old ON OldTable;
   

4. TRUNCATE TABLE：高速清空表資料
   • 比 DELETE 快：不記錄逐列刪除日誌，只記錄頁釋放
   • 重置識別欄位種子
   • 不觸發 DELETE 觸發器
   • 限制：被外鍵參考的表不可用；參與索引檢視的表不可用
5. COMMENT / sp_addextendedproperty：添加中繼資料說明
6. GRANT/REVOKE (DDL 語境下)：物件級權限授與/收回

DDL 操作對象層級：表結構與約束

• 主鍵：唯一識別列，非空、唯一，自動建立唯一索引
• 唯一約束：唯一但允許單一 NULL，自動建立唯一索引
• 外鍵：參考完整性，主表 PK ↔ 從表 FK
• CHECK / NOT NULL / DEFAULT：資料域完整性約束
• 為什麼 PK/Unique 自動建索引？ 索引加速「唯一性檢查」與「連接查詢」，是約束執行的物理基礎

三、DML - 資料操縱語言：操作「血肉」

核心指令

1. SELECT：查詢 (技術上屬 DQL，常歸類 DML)
2. INSERT：新增列
3. UPDATE：修改既有列
4. DELETE：刪除列 (保留空間，可回滾)
5. MERGE：Upsert 合併操作 (2008+)
6. BULK INSERT：大量載入

進階操作

• 外部連接：LEFT/RIGHT/FULL JOIN 保留單邊資料
• 集合運算：UNION (去重) / UNION ALL (保留重複) / INTERSECT / EXCEPT
• 子查詢與 CTE：模組化複雜查詢邏輯

關鍵提醒

• DML 操作單位是「列」，即使 DELETE FROM Table 清空全表，操作對象仍是逐列刪除 (有日誌、有觸發器、可回滾)
• 大量刪除優先考慮 TRUNCATE (DDL) 或分批 DELETE TOP (1000) 避免日誌暴漲

四、DCL - 資料控制語言：守護「大門」

權限管理三部曲

1. GRANT：授予權限

   GRANT SELECT, INSERT ON Customers TO AppUser;
   GRANT EXECUTE ON usp_GetCustomer TO AppRole;
   

2. DENY：明確拒絕 (優先級高於 GRANT)

   DENY DELETE ON Customers TO AppUser;
   

3. REVOKE：移除先前 GRANT/DENY

   REVOKE INSERT ON Customers FROM AppUser;
   

交易控制語句 (TCL，常併入 DCL 討論)

• COMMIT：提交交易
• ROLLBACK：回滾交易
• SAVEPOINT：設定儲存點 (部分回滾)
• SET TRANSACTION：設定隔離級別、唯讀等特性

五、權限層級與繼承模型

伺服器層級 (Server Level)
├─ 登入
├─ 伺服器角色
└─ 伺服器權限 (CONTROL SERVER, VIEW SERVER STATE...)

資料庫層級 (Database Level)
├─ 使用者 (對應登入)
├─ 資料庫角色 (db_owner, db_datareader, db_datawriter, db_ddladmin, db_securityadmin...)
├─ 應用程式角色
├─ 結構描述
│   └─ 物件 (表、檢視、SP、函數...)
│       └─ 物件權限 (SELECT, INSERT, UPDATE, DELETE, EXECUTE, REFERENCES...)
└─ 資料庫權限 (CREATE TABLE, ALTER ANY SCHEMA...)

六、實務操作建議

1. 最小權限原則：應用程式帳號只授 EXECUTE SP 權限，不授直接表存取
2. 角色基礎授權：建立應用角色 → 授權給角色 → 將使用者加入角色
3. DDL 變更管控：生產環境 DDL 需變更管理流程，配合 DDL Trigger 稽核 (見文章 72345)
4. 定期審計：sys.database_permissions, sys.server_permissions 掃描異常授權
5. 區分環境：開發/測試/生產 使用不同登入與權限集合