SQL语句之DELETE
时间：2021-07-01 10:21:17
帮助过：25人阅读
# 删除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;
SQL语句之DELETE
标签：name nbsp 部门 dep font dna ble order HERE
