写在前面

前面学习使用DDL来管理数据库、数据表和列，接下来学习如何使用数据操作语言（Data Manipulation Language）来对数据库中存储的数据进行一些管理操作。数据操作语言以INSERT(增加)、UPDATE(修改)、DELETE(删除)三者为核心，是非常重要的操作指令。通常我们习惯上将这三者加上后面即将学习的SELECT称为CRUD，也就是增删改查。

本篇主要学习的内容如下所示：（1）插入操作–插入单行；（2）插入操作–批量插入；（3）修改操作–单表修改；（4）修改操作–多表修改；（5）删除操作–使用delete删除–delete单表删除；（6）删除操作–使用delete删除–delete多表删除；（7）使用truncate删除；（8）drop、truncate和delete区别等。

同时需要说明的是，本文所有命令中包含的[]内的信息属于可选项，开发者可按照实际情况进行选用。

插入操作

插入单行

插入单行有两种方式，下面分别进行介绍。

方式1：使用values命令，完整命令格式如下所示：

1
insert into 表名[(字段1,字段2,...)] values(值1,值2,...);

关于上述命令需要注意几点内容：
（1）字段名称和值的数量、类型、位置需要一一对应；
（2）字段如果不能为空，那么必须插入值；
（3）可以为空的字段可以不插入值，或者用null代替；
（4）如果是字符型或日期类型，值需要使用单引号括起来；如果是数值类型，则不需要单引号；
（5）表名后面的字段可以省略不写，但此时就表示所有字段，开发者需要保证插入字段顺序和表中字段顺序一致，否则插入失败。

方式2：使用set命令，完整命令格式如下所示：

1
insert into 表名 set 字段1=值1,字段2=值2,...;

需要说明的是方式2不太常用，在实际工作中还是使用方式1。

例子1：插入单行数据的操作流程：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
mysql> drop table if exists envy1;
Query OK, 0 rows affected, 1 warning (0.11 sec)

mysql> create table envy1(a int,b int);
Query OK, 0 rows affected (0.33 sec)

mysql> insert into envy1(a,b)values(1,2);
Query OK, 1 row affected (0.09 sec)

mysql> select * from envy1;
+------+------+
| a    | b    |
+------+------+
|    1 |    2 |
+------+------+
1 row in set (0.00 sec)

mysql> insert into envy1 set a=1000,b=2000;
Query OK, 1 row affected (0.07 sec)

mysql> select * from envy1;
+------+------+
| a    | b    |
+------+------+
|    1 |    2 |
|  1000|  2000|
+------+------+
2 rows in set (0.00 sec)

批量插入

前面学习的都是如何插入单行数据，接下来学习如何批量插入数据，依然有两种方式。

方式1：使用values命令，完整命令格式如下所示：

1
insert into 表名[(字段1,字段2,...)] values(值1,值2,...),(值1,值2,...),(值1,值2,...)...;

方式2：使用select命令，完整命令格式如下所示：

1
2
insert into 表名[(字段1,字段2,...)]
数据来源select语句;

需要注意的是数据来源select语句可以有很多种写法，select返回的结果和插入数据的字段数量、顺序、类型需要一致。

例子2：下面例子展示了如何使用批量插入方式1来将数据插入到envy2表中：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
mysql> drop table if exists envy2;
Query OK, 0 rows affected, 1 warning (0.10 sec)

mysql> create table envy2(c1 int,c2 int,c3 int);
Query OK, 0 rows affected (0.41 sec)

mysql> insert into envy2 values(1,2,3),(11,12,13),(21,22,23),(31,32,33);
Query OK, 4 rows affected (0.15 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select * from envy2;
+------+------+------+
| c1   | c2   | c3   |
+------+------+------+
|    1 |    2 |    3 |
|   11 |   12 |   13 |
|   21 |   22 |   23 |
|   31 |   32 |   33 |
+------+------+------+
4 rows in set (0.01 sec)

例子3：下面例子展示了如何使用批量插入方式2来将envy2表中c1和c2数据插入到envy1表中：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
mysql> select * from envy1;
+------+------+
| a    | b    |
+------+------+
|    1 |    2 |
| 1000 | 2000 |
+------+------+
2 rows in set (0.00 sec)

mysql> select * from envy2;
+------+------+------+
| c1   | c2   | c3   |
+------+------+------+
|    1 |    2 |    3 |
|   11 |   12 |   13 |
|   21 |   22 |   23 |
|   31 |   32 |   33 |
+------+------+------+
4 rows in set (0.00 sec)

mysql> insert into envy1 select c1,c2 from envy2 where c1>30;
Query OK, 1 row affected (0.12 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> select * from envy1;
+------+------+
| a    | b    |
+------+------+
|    1 |    2 |
| 1000 | 2000 |
|   31 |   32 |
+------+------+
3 rows in set (0.00 sec)

修改操作

单表修改

单表修改的语法格式如下所示：

1
update 表名 [[as] 别名] set [别名].字段 = 值, [别名].字段 = 值 [where条件];

当遇到一些较长表名的时候，出于操作方便的目的，可以给这个表起一个别名；如果不起别名，那么表名就是别名。

例子4：下面例子展示了如何修改envy1表中的数据：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
mysql> select * from envy1;
+------+------+
| a    | b    |
+------+------+
|    1 |    2 |
| 1000 | 2000 |
|   31 |   32 |
+------+------+
3 rows in set (0.00 sec)

mysql> update envy1 e set e.a=666;
Query OK, 3 rows affected (0.14 sec)
Rows matched: 3  Changed: 3  Warnings: 0

mysql> select * from envy1;
+------+------+
| a    | b    |
+------+------+
|  666 |    2 |
|  666 | 2000 |
|  666 |   32 |
+------+------+
3 rows in set (0.00 sec)

mysql> update envy1 as e set e.a=888;
Query OK, 3 rows affected (0.07 sec)
Rows matched: 3  Changed: 3  Warnings: 0

mysql> select * from envy1;
+------+------+
| a    | b    |
+------+------+
|  888 |    2 |
|  888 | 2000 |
|  888 |   32 |
+------+------+
3 rows in set (0.00 sec)

mysql> update envy1 as e set e.a=888,e.b=666;
Query OK, 3 rows affected (0.12 sec)
Rows matched: 3  Changed: 3  Warnings: 0

mysql> select * from envy1;
+------+------+
| a    | b    |
+------+------+
|  888 |  666 |
|  888 |  666 |
|  888 |  666 |
+------+------+
3 rows in set (0.00 sec)

多表修改

顾名思义，多表修改就是同时修改多个表中的数据，其对应的语法格式如下所示：

1
2
3
update 表1 [[as] 别名1],表2 [[as] 别名2]
set [ 别名.]字段=值,[ 别名.]字段=值
[where条件];

例子5：下面例子展示了如何进行多个表的数据修改操作：

1
2
3
4
5
-- 没有别名
mysql> update envy1,envy2 set envy1.a=100,envy1.b=100,envy2.c1=12,envy2.c2=13;

-- 采用别名来更新多个表的多个字段
mysql> update envy1 as e1,envy2 e2 set e1.a=66,e1.b=88,e2.c1=100 where e1.a=e2.c1;

尽管上面介绍了单表和多表的修改操作，但是建议优先采用单表修改的方式，这样维护起来更加方便。

删除操作

使用delete删除

delete单表删除

开发者可以使用如下命令来进行单表的删除操作：

1
delete [别名] from 表名 [[as] 别名] [where条件];

请注意以下几点内容：
（1）如果待删除的表不存在别名，那么表名就是别名；（2）如果存在别名，那么delete后面就必须写别名；（3）如果没有别名，那么delete后面的别名就可以省略不写。

例子6：下面例子展示了如何对数据表进行删除操作：

1
2
3
4
5
6
7
8
9
10
11
-- 删除envy1表的所有记录
delete from envy1;

-- 删除envy1表的所有记录
delete envy1 from envy1;

-- 存在别名，删除envy1表的所有记录
delete e1 from envy1 e1;

-- 存在别名，删除envy1表中满足指定条件记录
delete e1 from envy1 e1 where e1.a>100;
delete多表删除

delete除了可以进行单表的删除，多表删除也是可以的，开发者可以使用如下命令来进行多表的删除操作：

1
delete [别名1],[别名2] from 表1 [[as] 别名1]， 表2 [[as] 别名2] [where条件];

上面命令中的别名可以省略不写，但是需要在delete后面跟上表名，多个表之间使用逗号进行隔开。

例子7：下面例子展示了如何删除envy3表中的记录，条件是这些记录的字段a在envy4.c1中存在记录。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
mysql> drop table if exists envy3;

mysql> create table envy3(a int,b int);

mysql> drop table if exists envy4;

mysql> create table envy4(c1 int,c2 int);

mysql> insert into envy4 values(100,101),(200,201),(300,301);

mysql> insert into envy3 values(1,1),(2,2),(3,3);

mysql> insert into envy3(a,b) select c1,c2 from envy4 where c1>120;

mysql> select * from envy3;
+------+------+
| a    | b    |
+------+------+
|    1 |    1 |
|    2 |    2 |
|    3 |    3 |
|  200 |  201 |
|  300 |  301 |
+------+------+
5 rows in set (0.00 sec)

mysql> select * from envy4;
+------+------+
| c1   | c2   |
+------+------+
|  100 |  101 |
|  200 |  201 |
|  300 |  301 |
+------+------+
3 rows in set (0.00 sec)

mysql> delete e3 from envy3 e3, envy4 e4 where e3.a=e4.c1;

mysql> select * from envy3;
+------+------+
| a    | b    |
+------+------+
|    1 |    1 |
|    2 |    2 |
|    3 |    3 |
+------+------+
3 rows in set (0.00 sec)

从上面例子的执行结果可以看出，envy3表中的2条记录都被删除了。

例子8：下面例子展示了如何同时删除envy3和envy4表中的记录，条件是envy3.a=envy4.c1：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
mysql> drop table if exists envy3;

mysql> create table envy3(a int,b int);

mysql> drop table if exists envy4;

mysql> create table envy4(c1 int,c2 int);

mysql> insert into envy4 values(100,101),(200,201),(300,301);

mysql> insert into envy3 values(1,1),(2,2),(3,3);

mysql> insert into envy3(a,b) select c1,c2 from envy4 where c1>120;

mysql> select * from envy3;
+------+------+
| a    | b    |
+------+------+
|    1 |    1 |
|    2 |    2 |
|    3 |    3 |
|  200 |  201 |
|  300 |  301 |
+------+------+
5 rows in set (0.00 sec)

mysql> select * from envy4;
+------+------+
| c1   | c2   |
+------+------+
|  100 |  101 |
|  200 |  201 |
|  300 |  301 |
+------+------+
3 rows in set (0.00 sec)

mysql> delete e3,e4 from envy3 e3,envy4 e4 where e3.a = e4.c1;
Query OK, 4 rows affected (0.05 sec)

mysql> select * from envy3;
+------+------+
| a    | b    |
+------+------+
|    1 |    1 |
|    2 |    2 |
|    3 |    3 |
+------+------+
3 rows in set (0.00 sec)

mysql> select * from envy4;
+------+------+
| c1   | c2   |
+------+------+
|  100 |  101 |
+------+------+
1 row in set (0.00 sec)

从执行结果可以看出envy3和envy4两个表共4条记录都被删除了。

当然在平时工作中使用最多的则是delete from 表名这种语法，但是如果该表存在别名，那么使用别名将会是不错的选择。

使用truncate删除

开发者可以使用如下命令来删除表：

1
truncate 表名;

例子9：下面例子展示了如何使用truncate来删除envy3表：

1
2
3
4
5
6
7
8
9
10
11
mysql> truncate envy3;
Query OK, 0 rows affected (0.39 sec)

mysql> select * from envy3;
Empty set (0.00 sec)

mysql> drop table if exists envy3;
Query OK, 0 rows affected (0.23 sec)

mysql> select * from envy3;
ERROR 1146 (42S02): Table 'envybook.envy3' doesn't exist

drop、truncate和delete区别

（1）drop（删除表）：它用于删除表的内容和定义，释放空间。说白了就是将整个表都删除，删除以后无法新增数据，除非再次创建新表。

drop语句将删除表结构所依赖的约束（constraint），触发器(trigger)，索引(index)，但是依赖于该表的存储过程/函数将会被保留，只是状态会变成invalid。

建议：当需要删除表定义及其数据，可以使用drop table语句。

（2）truncate（只清空表中的数据）：它用于清空表中的数据，释放空间，但是不删除表的定义。说白了就是会保留表的数据结构，与之前介绍的drop不同在于，truncate只是清空表中数据。

truncate table子句用于删除表中的所有行，保留表结构及其列、索引、约束等信息。

请注意，对于有foreign key约束引用的表，不能使用truncate table子句，而应当使用不带where条件的delete子句。由于truncate table记录在日志中，所以它无法激活触发器。

（3）delete（删除表中的行数据）：它用于删除表中的行。delete语句执行删除的过程是每次从表中删除一行，并且同时将该行的删除操作作为事务记录在日志中进行保存，以便于后续进行回滚操作。

如果开发者想要只删除表中数据，而不删除表的定义（数据结构），那么可以使用“truncate”或者“不带where子句的delete”语句。

此外请注意以下内容：
（a）delete语句是数据库管理语言(dml)，这个操作会放到rollback segement中，事务提交之后才会生效；如果存在相应的trigger触发器，那么就会在执行的时候触发。
（b）truncate、drop语句是数据库定义语言(ddl)，操作会立即生效。原数据是不放到rollback segement中，不能回滚，操作不会触发trigger。
（c）如果存在自增列，那么使用truncate方式删除之后，自增列的值就会被初始化；而对于delete方式需要分两种情况：数据库被重启，那么自增列也会被初始化；数据库未重启，那么保持不变。
（d）如果想要删除表定义及其数据，推荐使用drop table子句；
（e）在使用drop和truncate子句的时候，请注意小心使用，尤其是数据没有备份的情况下，执行上述两个命令后删除的数据无法恢复。
（f）一般来说，删除数据的速度是drop>truncate>delete。

最后通过一张表来展示drop、truncate和delete这三者在是否支持条件删除、删除表结构、事务删除方式和触发触发器等方面的不同：

方面 drop truncate delete
条件删除 不支持 不支持 支持
删除表结构 支持 不支持 不支持
事务的方式删除 不支持 不支持 支持
触发触发器 不支持 不支持 支持

那么这样，关于数据表的增删改操作就先学习到这，后期学习其他知识。