MySQL数据库数据删除操作详解

2022-08-10 09:00

短信预约 -IT技能 免费直播动态提醒

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;

MySQL数据库数据删除操作详解

DELETE 语句练习②

删除 20 部门中工资最高的员工记录（除了使用 WHERE 子句过滤出 20 部门中员工之外，还需要根据工资进行降序排列，并删除最高的记录）

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

MySQL数据库数据删除操作详解

"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";

MySQL数据库数据删除操作详解

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;

MySQL数据库数据删除操作详解

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;

MySQL数据库数据删除操作详解

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

MySQL数据库数据删除操作详解

速删除表中的全部数据

“DELETE” 语句是在事务机制下删除记录（什么是 “事务机制” 会在后续文章详细介绍），删除记录之前，先要将删除的记录保存到日志文件里，然后再删除记录。当使用 “DELETE” 去删除海量数据的时候，“事务机制” 会让删除的速度变得很慢。

“TRUNCATE” 语句在 “事务机制” 之外删除记录，执行速度要远远地超过 “DELETE” 语句。

“TRUNCATE” 语法如下：

TRUNCATE TABLE 表名;

-- 需要注意的是，"TRUNCATE"  语句一次只能清空一张数据表，不能够一次性的清空多张数据表。

TRUNCATE TABLE t_emp ;

MySQL数据库数据删除操作详解

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 新表名；