MySQL資料庫資料刪除操作詳解

2022-08-10 14:04:31

DELETE 語句

DELETE 語句用於刪除記錄，語法如下：(與 “UPDATE” 語法較為相似)

DELETE [IGNORE] FROM 表名
WHERE 條件1, 條件2, .....
ORDER BY ......
LIMIT ......;

-- 使用 IGNORE 關鍵字時，當存在外來鍵約束組織我們刪除記錄，那麼則會忽略刪除該條資料
-- 使用 WHERE 子句刪除條件範圍內的記錄；如果不使用 WHERE 子句，則是刪除全表範圍
-- 使用 ORDER BY 關鍵字，將被刪除的記錄進行排序以後，產出符合條件的一些資料
-- 使用 LIMIT 關鍵字依然是分頁的意思，

“DELETE” 語句執行的順序：FROM ---> WHERE ---> ORDER BY ---> LIMIT ---> DELETE

DELETE 語句練習①

刪除 10 部門中，工齡超過 20 年的員工記錄（這個比較簡單，只涉及到一張表）

DELETE FROM  t_emp 	
WHERE deptno = 10 
AND DATEDIFF(NOW(), hiredate)/365 >= 20;

DELETE 語句練習②

刪除 20 部門中工資最高的員工記錄（除了使用 WHERE 子句過濾出 20 部門中員工之外，還需要根據工資進行降序排列，並刪除最高的記錄）

DELETE FROM  t_emp 	
WHERE deptno = 20 
ORDER BY sal+IFNULL(comm,0) DESC
LIMIT 1;

"DELETE" 語句的語法還是很簡單的，在有了 "UPDATE" 語句的基礎之後再來看待 "DELETE" 語句就比較好理解了，因為這些子句都差不太多。

DELETE 語句的表連線(內連線)

因為相關子查詢效率非常的低下，所以我們可以利用表連線的方式來提高 DELETE 語句的刪除效率。

“DELETE” SQL語句的內連線語法如下：

DELETE 表1, ...... FROM 表1 JOIN 表2 ON 條件
WHERE 條件1, 條件2, ......
ORDER BY ......
LIMIT ......

-- 需要注意的是，這裡的 "WHERE"、"ORDER BY"、"LIMIT" 子句都是可選條件

DELETE 語句的表連線練習①

刪除 SALES 部門和該部門的全部員工記錄

分析：刪除兩張表的記錄

分析：使用一個 DELETE 語句刪除兩張表記錄，可以使用表連線的方式（參考上文語法）

分析：屬於 SALES 部門的員工記錄未知，需要先試用條件查詢過濾出 SALES 部門的員工記錄

DELETE e, d
FROM t_emp e JOIN t_dept d ON e.deptno=d.deptno
WHERE d.dname="SALES";

DELETE 語句的表連線練習②

刪除每個低於部門平均底薪的員工記錄

分析：依然使用表連線，因為首先需要去查詢部門進行分組，查詢部門的編號與平均底薪。

分析：將查詢到的結果集作為一張表和 "員工表" 做連線，連線條件就是員工的 "部門編號"，還有一個條件就是員工的月薪要低於部門的平均底薪

DELETE e
FROM t_emp e JOIN (SELECT deptno, AVG(sal) AS avg FROM t_emp GROUP BY deptno) t
ON e.deptno=t.deptno 
AND e.sal < t.avg;

DELETE 語句的表連線練習③

刪除員工 “KING” 和他的下屬的員工記錄，用表連線來實現

分析:用 "WHERE" 子句查詢 "ename" 為 "KING" 的員工記錄

分析:將查到的 "KING" 的員工記錄與員工表的 "mgr" 欄位為 "KING" 的 "員工編號" 做連線

DELETE e
FROM t_emp e JOIN (SELECT empno FROM t_emp WHERE ename="KING") t
ON e.mgr=t.empno OR e.empno=t.empno;

DELETE 語句的表連線(外連線)

“DELETE” 語句的表連線既可以是內連線，也可以是外連線。

“DELETE” SQL語句的外連線語法如下：

DELETE 表1, ...... FROM 表1 [LEFT | RIGHT] JOIN 表2 ON 條件
WHERE 條件1, 條件2, ......
ORDER BY ......
LIMIT ......

-- 需要注意的是，這裡的 "WHERE"、"ORDER BY"、"LIMIT" 子句都是可選條件
-- 除了 在連線表 的時候選擇 "LEFT JOIN" 與 "RIGHT JOIN" 的區別之外，其他的與內連線的語法一樣

DELETE 語句的外連線練習

刪除 “SALES” 部門的員工，以及沒有部門的員工。

分析：使用 "左外連線" 與部門表進行連線，因為 "員工表" 中的 "張三" 是沒有部門的；所以要保留下來，與部門表進行連線。

DELETE e
FROM t_emp e LEFT JOIN t_dept d ON e.deptno=d.deptno
WHERE d.dname="SALES" OR e.deptno IS NULL

速刪除表中的全部資料

“DELETE” 語句是在事務機制下刪除記錄（什麼是 “事務機制” 會在後續文章詳細介紹），刪除記錄之前，先要將刪除的記錄儲存到紀錄檔檔案裡，然後再刪除記錄。當使用 “DELETE” 去刪除海量資料的時候，“事務機制” 會讓刪除的速度變得很慢。

“TRUNCATE” 語句在 “事務機制” 之外刪除記錄，執行速度要遠遠地超過 “DELETE” 語句。

“TRUNCATE” 語法如下：

TRUNCATE TABLE 表名;

-- 需要注意的是，"TRUNCATE"  語句一次只能清空一張資料表，不能夠一次性的清空多張資料表。

TRUNCATE TABLE t_emp ;

MySQL 刪除語句小節

語句	功能
drop 語句	刪除資料庫和表 drop 資料庫名; drop 表名;
delete 語句	刪除表中的記錄 delete from weibo_user where username=“xiaomu”; 注意：delete語句中如果沒有加入where條件，將會把表中的所有記錄全部刪除
update set 語句	修改和更新語句，更新時也會覆蓋（刪除）原來的值 update set與where搭配使用，變更某些記錄注意：：update set語句中如果沒有where子句指定其變更的條件，將就會把資料庫中這個欄位的所有值都更新
alert 語句	刪除欄位：alter table 表名 drop 欄位名；刪除主鍵：alter table 表名 drop primary key ；更新表名：alter table 表名 rename to 新表名；