数据的增加、修改、删除（四）

一、数据添加

1.1 INSERT语法

INSERT语句可以向数据表写入记录，可以是一条记录，也可以是多条记录。

语法：

# 添加一条记录
INSERT INTO 表名 （字段1，字段2，...）VALUES(值1， 值2，...);
# 添加多条记录
INSERT INTO 表名 （字段1，字段2，...）VALUES(值1， 值2，...), (值1， 值2，...), (值1， 值2，...);

1.2 INSERT方言语法

Mysql数据库的语法

语法：

INSERT INTO 表名 SET 字段1=值1, 字段2=值2, ...;
# INTO还可以省略不写

例子： 插入一条员工记录

INSERT INTO t_emp
SET empno=8002, ename="JACK", job="SALESMAN", mgr=8000, hiredate="1999-11-09", sal=2300, comm=NULL, deptno=50;

1.3 INSERT子查询

values中可以写子查询，只能写单行子查询且SELECT子句中的参数只能有一个，单行单列的子查询才可以

例子： 向技术部添加一条员工记录

INSERT INTO t_emp
(empno, ename, job, mgr, hiredate, sal, comm, deptno)
VALUES(8001, "刘娜", "SALESMAN", 8000, "1999-01-01", 2000, NULL, 
(SELECT deptno FROM t_dept WHERE dename="技术部"));

1.4 IGNORE关键字

当插入多条记录的时候，如果其中一条记录在表中已存在（主键冲突或唯一性冲突）就会导致所有的记录都插入失败。IGNORE关键字会让INSERT只插入数据库不存在的记录。

语法：

INSERT IGNORE INTO 表名....; 

二、数据修改

2.1 UPDATE语法

UPDATE语句用于修改表的记录

语法：

UPDATE [IGNORE] 表名
SET 字段1=值1, 字段2=值2,...
[WHERE 条件1....]
[ORDER BY...]
[LIMIT...];
# 这里的LIMIT只能写一个参数
# []表示里面的内容可写可不写

执行顺序： UPDATE->WHERE->ORDER BY->LIMIT->SET

例子： 把每名员工的编号和上司的编号+1，用ORDER BY子句完成

UPDATE t_emp SET empno=empno+1, mgr=mgr+1
ORDER BY empno DESC;

例子： 把月收入前三名的员工底薪减100元，用LIMIT子句完成

UPDATE t_emp SET sal=sal-100
ORDER BY sal+IFNULL(comm,0) DESC
LIMIT 3;

2.2 UPDATE表连接

相关子查询效率低，采用表连接改造UPDATE语句

语法：

# SET后的字段既可以是来自表1的字段也可以是表2的字段，即表连接的UPDATE语句可以修改多张表的记录

写法一：
UPDATE 表1 JOIN 表2 ON 条件
SET 字段1=值1，字段2=值2,...;

写法二：
UPDATE 表1, 表2  
SET 字段1=值1，字段2=值2,...
WHERE 连接条件;

例子： 把ALLEN调往RESEARCH部门，职务调整为ANALYST

# 该题不能写连接条件
UPDATE t_emp e JOIN t_dept d 
SET e.deptno=d.deptno, e.job="ANALYST"
WHERE e.ename="ALLEN" AND d.dename="RESEARCH";

例子： 把底薪低于公司平均底薪的员工，底薪增加150元

UPDATE t_emp e JOIN
(SELECT AVG(sal) AS avg FROM t_emp) t
ON e.sal<t.avg 
SET e.sal = e.sal+150;

UPDATE语句的表连接即可以是内连接，又可以是外连接

UPDATE 表1[LEFT|RIGHT] JOIN 表2 ON 条件
SET 字段1=值1，字段2=值2

例子： 把没有部门的员工，或者SALES部门低于2000元底薪的员工，都掉往20部门

UPDATE t_emp e LEFT JOIN t_dept d ON e.deptno=d.deptno
SET e.deptno=20
WHERE e.deptno IS NULL OR (d.dename="SALES" AND e.sal<2000);

三、数据删除

3.1 DELETE语法

删除数据记录

语法：

DELETE [IGNORE] FROM 表名
[WHERE 条件1, 条件2，...]
[ORDER BY...]
[LIMIT...]

注意：

1. IGNORE删掉没有外键约束的 和 UPDATE的差不多
2. 不写WHERE条件就是删除全表记录

执行顺序： FROM->WHERE->ORDER BY->LIMIT->DELETE

例子： 删除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;

3.2 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.dename="SALES"; 

例子： 删除每个低于部门平均底薪的员工的记录

DELETE e
FROM t_emp e JOIN (SELECT deptno, AVG(sal) AS avg FROM t_emp GROUP BY deptno) t
ON e.deptno=d.deptno AND e.sal<t.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;

也可以支持外连接

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.dename="SALSE" OR e.deptno IS NULL;

快速删除数据表全部记录：

• DELETE语句是在事务机制下删除记录，删除记录之前，先把将要删除的记录保持到日志文件里，然后在删除记录。

• TRUNCATE语句在事务机制之外删除记录，速度远超过DELETE语句

  一次只能清空一张表

  TRUNCATE TABLE 表名;