MySQL DELETE语句

# DELETE语句
# DELETE语句用于删除记录
# DELETE [IGNORE] FROM 表名 [WHERE 条件1，条件2] [ORDER BY ...] [LIMIT ...];

# 删除10部门中，工龄超过20年的员工记录
DELETE FROM t_emp 
WHERE deptno=10 AND FLOOR(DATEDIFF(NOW(),hiredate)/365)>20 ;

# 删除20部门中工资最高的员工记录
DELETE FROM t_emp
WHERE deptno=20 
ORDER BY sal+IFNULL(comm,0) DESC;
LIMIT 1;

# DELETE 内连接
# DELETE 表1,... FROM 表1 JOIN 表2 ON 条件 [WHERE 条件1,条件2] [ORDER BY ...] [LIMIT]
# 删除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) as avg FROM t_emp GROUP BY deptno) t
ON e.deptno=t.deptno AND e.sal<t.avg;

# 删除KING和他的直接下属的记录，用表连接实现
# 方法1
DELETE e
FROM t_emp e JOIN (SELECT ename FROM t_emp WHERE mgr=7839) t
ON e.ename=t.ename OR e.empno=7839;

# 方法2
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;

# DELETE外连接
# DELETE 表1,... FROM 表1 [LEFT|RIGHT]JOIN 表2 ON 条件 ...;
# 删除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;

# DELETE语句是在事务机制下删除记录，删除记录之前，先把将要删除的记录保存到日志文件里，然后再删除记录
# TRUNCATE 语句在事务机制之外删除记录，速度远超过DELETE语句
# TRUNCATE TABLE 表名；
TRUNCATE TABLE t_emp;