SQL DELETE 語句實戰指南：單行、多行與全表刪除的安全操作規範

DELETE 基本語法

DELETE FROM table_name
WHERE column_name OPERATOR value [AND|OR ...];

WHERE 子句為可選，但生產環境嚴禁省略，否則將清空整張表。

三大典型場景

1. 刪除單行（精確匹配）

DELETE FROM employee
WHERE lastname = 'May';

2. 刪除多行（OR 組合）

DELETE FROM employee
WHERE firstname = 'Mike' OR firstname = 'Eric';

3. 刪除全表（極度危險）

DELETE FROM employee;  -- 無 WHERE，刪除所有行！

若確需清空表，優先考慮 TRUNCATE TABLE（速度更快、不記錄行級日誌、可回滾）。

安全操作清單

• ✅ 先跑 SELECT * FROM table WHERE ... 確認將刪除的行
• ✅ 使用事務包裹：BEGIN TRAN ... DELETE ... COMMIT/ROLLBACK
• ✅ 生產庫設置 DELETE 權限最小化，僅 DBA 可執行
• ✅ 關鍵表啟用 INSTEAD OF DELETE 觸發器做軟刪除或審計

DELETE vs TRUNCATE vs DROP