1 # 删除10部门里面，工龄超过20年的员工记录 2 DELETE IGNORE FROM t_emp WHERE deptno=10 AND DATEDIFF(NOW(),hiredate)/365>=20; 3 # 删除20部门里面工资最高的纪录 4 DELETE IGNORE FROM t_emp WHERE deptno=20 ORDER BY sal DESC LIMIT 1; 5 # 删除SALES部门和该部门的所有员工记录 6 DELETE e,d FROM t_emp e JOIN t_dept d ON e.deptno=d.deptno WHERE d.dname="SALES"; 7 # 删除每个低于部门平均底薪的员工记录 8 DELETE e FROM t_emp e JOIN (SELECT deptno,AVG(sal) avg FROM t_emp GROUP BY deptno) t ON e.deptno=t.deptno WHERE sal<avg; 9 # 删除员工KING和他直接下属的记录10 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;11 # 删除SALES部门的员工及没有部门的员工12 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;13 # 绕过事务机制直接删除全表数据14 TRUNCATE TABLE t_emp;