DELETE [IGNORE] FROM 表名
[WHERE 条件1,条件2,...]
[ORDER BY...]
[LIMIT...];
删除10部门中,工龄超过20年的员工记录
delete from t_emp where deptno=10 and datediff(now(),hiredate)/365>20;
删除20部门中工资最高的员工记录
delete from t_emp where deptno=20 order by sal+ifnull(comm,0) desc limit 1;
DELETE 表1, FROM 表1 JOIN 表2 ON 条件
[WHERE 条件1,条件2...]
[ORDER BY...]
[LIMIT...];
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) s
on e.deptno=s.deptno and e.sal<s.avg;
delete e
from t_emp e join
(select empno from t_emp where ename="KING") t
on e.mgr=t.empno or e.empno=d.empno;
临时表的数据不能删除
DELETE 表1,.. FROM 表1 [LEFT | RIGHT] JOIN 表2
ON 条件...;
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 表名;