SQL语句之DELETE

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