MySQL基础（三）数据的插删改

一、插入insert语句

插入语句使用的是insert，有两种方式：

(1) 方式一：

# 插入一条记录
INSERT INTO 表名(列名1,列名2,...)
VALUES(值1,值2,...);

# 插入多条记录
INSERT INTO 表名(列名1,列名2,...)
VALUES(值1,值2,...),
(值1,值2,...),
(值1,值2,...);

一些tips：
1.插入的值的类型要与列的类型一致或兼容
2.不可以为null的列必须插入值；可以为空的列插入值，可以将列名和值都省略，也可以写好列名，值用null填充
3.列的顺序可以调换
4.列数和值的个数必须一致
5.可省略列名，此时默认为所有列，列的顺序和表中列的顺序一致

(2) 方式二：

INSERT INTO 表名
SET 列名1=值1,列名2=值2,...;

(3) 方式一 vs 方式二：
1.方式一支持插入多行，方式二不支持
2.方式一支持子查询，方式二不支持

举个例子，假设数据库有一张学生表，表中字段依次为stu_id、stu_name、class_id以及score，现在想要往表中插入三条语句:

stu_id	stu_name	class_id	score
202001	小三	2	98
202002	小四	2	86
202003	小五	1	97

用上述两种方式插入，如下：

# 方式一
INSERT INTO students 
VALUES
(202001,'小三',2,98),
(202002,'小四',2,86),
(202003,'小五',1,97);

# 方式二
INSERT INTO students SET stu_id=202001, stu_name='小三', class_id=2, score=98;
INSERT INTO students SET stu_id=202002, stu_name='小四', class_id=2, score=86;
INSERT INTO students SET stu_id=202003, stu_name='小五', class_id=1, score=97;

二、修改update语句

修改语句使用的是update。

2.1 修改单表的记录

具体的语法为：

UPDATE 表名    第①步
SET 列名=新值,列名=新值,...  第③步
[WHERE 筛选条件];  第②步

看个例子，修改姓名中含有‘五’字的学生的班级编号为5。

UPDATE students SET class_id = 5 WHERE stu_name LIKE '%五%';

2.2 修改多表的记录

同前一篇MySQL基础（二）连接查询部分中的语法分类，这里的具体语法也可以划分为两类：92语法和99语法下的修改语句。

# 92语法（同 MySQL基础（二）连接查询部分的语法分类）
UPDATE 表1 别名, 表2 别名
SET 列名=新值,列名=新值,...
WHERE 连接条件
AND 筛选条件;

# 99语法
UPDATE 表1 别名
INNER|LEFT|RIGHT JOIN 表2 别名
ON 连接条件
SET 列名=新值,列名=新值,...
WHERE 筛选条件;

看个例子，修改《干校六记》的作者的性别为‘女’。

表2.1: 人物表authors

id	author_name	sex	born_date	book_id
1	林徽因	女	1904-6	2
2	杨绛	男	1911-7	1

表2.2: 作品表books

id	book_name	publishing_date
1	干校六记	1981
2	你是人间四月天	2005

先连表，再筛选，再修改，但语句顺序应该如下：

UPDATE authors a
INNER JOIN books b ON a.book_id = b.id
SET a.sex = '女'
WHERE b.book_name = '干校六记';

三、删除delete/truncate语句

删除语句可以使用delete，也可以使用truncate。

3.1 delete语句

# 单表
DELETE FROM 表名 
[WHERE 筛选条件] 
[ORDER BY 排序列表] 
[LIMIT 条目数];

# 多表
## 92语法
DELETE 表1的别名, 表2的别名（要删除的表的别名）
FROM 表1 别名, 表2 别名
WHERE 连接条件
AND 筛选条件;


## 99语法
DELETE 表1的别名, 表2的别名（要删除的表的别名）
FROM 表1 别名 
INNER|LEFT|RIGHT JOIN 表2 别名
ON 连接条件 
[WHERE 筛选条件];

看个单表删除的例子，删除姓‘林’的作家的信息。

DELETE FROM authors WHERE author_namee LIKE '林%';

再看个多表删除的例子，删除《干校六记》作者的信息。

DELETE a
FROM authors a
INNER JOIN books b ON a.book_id = b.id
WHERE b.book_name = '干校六记';

3.2 truncate语句

TRUNCATE TABLE 表名;

由于truncate后面不能接where筛选，所以，就相当于将表中的数据全部清空。

3.3 delete语句 vs truncate语句

1.delete可以加where条件，truncate不能加
2.truncate删除，效率高一丢丢
3.假如要删除的表中有自增长列，用delete删除后再插入数据，自增长列的值从断点开始，而用truncate删除后再插入数据，自增长列的值从1开始
4.delete删除有返回值，而truncate删除没有返回值
5.delete删除可以回滚，而truncate删除不能回滚

前两个点比较好理解，后面三点需要额外提一嘴。

第一点：自增长列，顾名思义，在我们插入新的记录时，自动递增的列就是自增长列。

举例来说，假设存在表students如下：

stu_id	stu_name	class_id	score
202001	小三	2	98
202002	小四	2	86

现在我们往表中插入数据(stu_name: ‘小五’,class_id: 1, score: 97)，返回结果如下：

stu_id	stu_name	class_id	score
202001	小三	2	98
202002	小四	2	86
202003	小五	1	97

可以看到，我们在插入数据的时候，并没有设置stu_id的值，但是stu_id的值却自动增长1，这就是自增长列。

了解了自增长列，就好理解上面的第三点了。

假设我们使用delete语句删除students表的数据即DELETE FROM students;后，重新插入值(stu_name: ‘小三’,class_id: 2, score: 98),(stu_name: ‘小四’,class_id: 2, score: 86),(stu_name: ‘小五’,class_id: 1, score: 97)，可得结果：

stu_id	stu_name	class_id	score
202004	小三	2	98
202005	小四	2	86
202006	小五	1	97

可以看到，stu_id是在202003的基础上递增的。而truncate删除后插入的语句，并不会如此，而是一夜回到解放前，从1重新开始。

第二点：返回值，这里主要指的是具体受影响的行数是否能够清楚地返回提示。

假设我们使用delete语句删除上面的students表中数据即DELETE FROM students;，运行后可以清楚地看到提示为共3行受到影响；而如果使用truncate语句进行删除即TRUNCATE TABLE students;，可以看见返回的结果为共0行受到影响。

第三点：回滚，这个概念在后面事务部分还会再次遇见，为了完整性，这里就先简单提一下。从定义上来讲，事务的回滚是指程序或数据处理错误，将程序或数据恢复到上一次正确状态的行为。

很抽象？那举个经典的例子–从ATM机中取钱。

ATM就相当于一个数据库，想要从中取钱须经过以下几个步骤：
Step1: 将卡插入ATM机进行登陆，输入密码
Step2: 连接数据库，验证输入的密码
Step3: 验证成功，获取用户余额信息
Step4: 输入取款金额，按下确认键
Step5: 数据库中，该用户账户金额相应减少
Step6: ATM机吐出钱

此处，取钱这件事就是一个事务。该事务的回滚，就是指上面六个步骤有一步出现错误，则会取消整个取钱的过程。比如说，在最后一步出了错，此时数据库中已经将账户里的钱减了，但是ATM机中并没有吐出钱来，这个时候就会回滚(rollback)到第一步的状态，之前的步骤都不算数。

总之，要么就顺利地完成所有的步骤(取到了钱)，要么就相当于什么都没干，一切重新开始。

以上，便是数据的插删改的全部内容啦～