基本語法
DELETE FROM table_name
[WHERE condition];
條件刪除範例
-- 簡單條件
DELETE FROM COURSES WHERE COURSE_DESIGNATER = 'Java110';
-- 複合條件
DELETE FROM CLASSCONVENINGS
WHERE CLASSES_NUM_FK > 4
AND CLASS_CONVENE_DATE = TO_DATE('2006-02-15','YYYY-MM-DD')
AND CLASS_LOCATION_FK = 'Seattle-Training Room 1';
完整實戰演示
1. 建立 Employee 表並填入 8 筆資料
CREATE TABLE Employee (
ID VARCHAR2(4) NOT NULL,
First_Name VARCHAR2(10),
Last_Name VARCHAR2(10),
Start_Date DATE,
End_Date DATE,
Salary NUMBER(8,2),
City VARCHAR2(10),
Description VARCHAR2(15)
);
-- INSERT 8 筆 (略)
2. 刪除薪資 > 3000 的員工
SQL> DELETE FROM Employee WHERE Salary > 3000;
4 rows deleted.
SQL> SELECT * FROM Employee;
ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION
01 Jason Martin 25-JUL-96 25-JUL-06 1234.56 Toronto Programmer
04 Celia Rice 24-OCT-82 21-APR-99 2344.78 Vancouver Manager
05 Robert Black 15-JAN-84 08-AUG-98 2334.78 Vancouver Tester
08 James Cat 17-SEP-96 15-APR-02 1232.78 Vancouver Tester
DELETE vs TRUNCATE vs DROP
| 指令 | 類別 | 可回滾 | 觸發 Trigger | 高水位線 | 速度 |
| DELETE | DML | 是 | 是 | 不重置 | 慢 |
| TRUNCATE | DDL | 否 | 否 | 重置 | 極快 |
| DROP | DDL | 否 | 否 | 表結構連同刪除 | 極快 |
刪除不存在的記錄 (無報錯)
DELETE FROM project WHERE pro_id = 1006;
-- 0 rows deleted,不報錯
最佳實務
- 生產環境
DELETE 必加 WHERE,防誤刪全表
- 大量刪除考慮
PARTITION 裁剪或 TRUNCATE PARTITION
- 需保留表結構清空資料用
TRUNCATE;不再需要表用 DROP