数据库学习（17）MySQL数据库DML——DELETE语句

DELETE语句简介

• DLETE语句用于删除记录，语法如下：

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

注： 删除表中记录用DELETE语句，而删除整表使用DROP语句。

DELETE语句练习1

删除10部门中，工龄超过20年的员工记录

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

DELETE语句练习2

删除20部门中工资最高的员工记录

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

DELETE语句的表连接

• 因为相关子查询效率非常低，所以我们可以利用表连接的方式来改造DELETE语句
  使用方法：

DELETE 表1, ...... FROM 表1 JOIN 表2 ON 条件
[WHERE 条件1, 条件2, ......]
[ORDER BY ......]
[LIMIT ......];

DELETE语句练习3

删除SALES部门和该部门的全部员工记录

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

DELETE语句练习4

删除每个低于部门平均底薪的员工记录

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

DELETE语句练习5

删除员工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;

注： 结果集为临时表时，其里面的数据是不能删除的，即上述语句中的 t 表是不能删除的。

DELETE语句表连接的外连接

• DELETE语句的表连接既可以是内连接，又可以是外连接
  使用方法：

DELETE 表1, ...... FROM 表1 [LEFT|RIGHT] JOIN 表2
ON 条件 ......;

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语句是在事务机制下删除记录，删除记录之前，先把将要删除的记录保存到日志文件里，然后再删除记录。
• TRUNCATE语句在事务机制之外删除记录，速度远超过DELETE语句
  TRUNCATE语句使用方法：

TRUNCATE TABLE 表名;

示例：清空员工表的数据

TRUNCATE TABLE t_emp;