MySQL全面瓦解5：数据操作-DML

爱新觉羅丶高

发布于 2022-8-1 19:13

浏览

0收藏

说明
DML(Data Manipulation Language)数据操作语言，是指对数据库进行增删改的操作指令，主要有INSERT、UPDATE、DELETE三种，代表插入、更新与删除，这是学习MySQL必要掌握的基本知识。

与之前的章节一致，下方语法中 [] 中内容可以省略。

INSERT操作
逐行插入
语法格式如下：

insert into t_name[(column_name1,columnname_2,...)] values (val1,val2);
或者
insert into t_name set column_name1 = val1,column_name2 = val2;1.
2.
3.

1、字段名称和值需要保证数量一直，类型一直，位置一一对应，否则可能导致异常。

2、not null的字段需要保证有插入的值，否则会报非空的异常信息。允许null的字段如果不想输入数据，字段和值都不出现，或者value用null代替。

3、数值类型，值不需要用单引号括起来，其他的如字符型或日期类型，值需要用单引号括起来；

4、如果表名后面的column_name 省略不写，则代表覆盖该表的所有字段。值的顺序和表中字段顺序须保持一致。

5、上述第二种语法的写法更繁琐，现在比较少使用。

测试一下：

mysql> desc `user1`;
+---------+--------------+------+-----+---------+----------------+
| Field   | Type         | Null | Key | Default | Extra          |
+---------+--------------+------+-----+---------+----------------+
| id      | bigint(20)   | NO   | PRI | NULL    | auto_increment |
| name    | varchar(20)  | NO   |     | NULL    |                |
| age     | int(11)      | NO   |     | 0       |                |
| address | varchar(255) | YES  |     | NULL    |                |
+---------+--------------+------+-----+---------+----------------+
4 rows in set

mysql> insert into `user1`(name,age,address) values('brand',20,'fuzhou');
Query OK, 1 row affected

mysql> insert into `user1`(age,address) values(20,'fuzhou');
1364 - Field 'name' doesn't have a default value

mysql> insert into `user1` values('sol',21,'xiamen');
1136 - Column count doesn't match value count at row 1

mysql> insert into `user1` values(null,'sol',21,'xiamen');
Query OK, 1 row affected

mysql> select * from `user1`;
+----+-------+-----+---------+
| id | name  | age | address |
+----+-------+-----+---------+
|  3 | brand |  20 | fuzhou  |
|  4 | sol   |  21 | xiamen  |
+----+-------+-----+---------+
2 rows in set1.
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.

批量插入
语法格式如下：

insert into t_name [(column_name1,column_name2)] values (val1_1,val1_2),(val2_1,val2_2)...);
或者
insert into t_name [(column_name1,column_name2)] select  o_name1,o_name2  from o_t_name [where  condition];1.
2.
3.

1、上述第一个语法，values 后面的值个数需要同等配对 column的数量，可以设置多个，逗号隔开，提高数据插入效率。

2、第二个语法，select查询的字段和插入数据的字段数量、顺序、类型需要一致。insert的字段可以省略，代表插入t_name表所有字段。条件可选。

测试一下：

mysql> insert into `user1`(name,age,address) values('brand',20,'fuzhou'),('sol',21,'xiamen');
Query OK, 2 rows affected
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from `user1`;
+----+-------+-----+---------+
| id | name  | age | address |
+----+-------+-----+---------+
|  5 | brand |  20 | fuzhou  |
|  6 | sol   |  21 | xiamen  |
+----+-------+-----+---------+
2 rows in set1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.

mysql> desc `user2`;
+---------+--------------+------+-----+---------+----------------+
| Field   | Type         | Null | Key | Default | Extra          |
+---------+--------------+------+-----+---------+----------------+
| id      | bigint(20)   | NO   | PRI | NULL    | auto_increment |
| name    | varchar(20)  | NO   |     | NULL    |                |
| age     | int(11)      | NO   |     | 0       |                |
| address | varchar(255) | YES  |     | NULL    |                |
| sex     | int(11)      | NO   |     | 1       |                |
+---------+--------------+------+-----+---------+----------------+
5 rows in set

mysql> insert into `user2` (name,age,address,sex) select name,age,address,null from `user1`;
Query OK, 2 rows affected
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from `user2`;
+----+-------+-----+---------+------+
| id | name  | age | address | sex  |
+----+-------+-----+---------+------+
|  7 | brand |  20 | fuzhou  | 1    |
|  8 | sol   |  21 | xiamen  | 1    |
+----+-------+-----+---------+------+
2 rows in set1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.

UPDATE操作

数据更新
语法格式如下：

update t_name [[as] alias] set [ alias.]column_name1 = val1,[alias.]column_name2 = val2 [where condition];1.

1、alias 是别名的意思，别名越简单识别性越强越好，容易辨认，方便操作，没有别名情况下，表名就是别名

2、as alias 中as也是可选的，where 条件也是可选的，所以用户可以选择需要的，符合特定条件的部分数据进行更新。

测试一下：

mysql> select * from `user2`;
+----+-------+-----+---------+------+
| id | name  | age | address | sex  |
+----+-------+-----+---------+------+
|  7 | brand |  20 | fuzhou  | NULL |
|  8 | sol   |  21 | xiamen  | NULL |
+----+-------+-----+---------+------+
2 rows in set

mysql> update `user2` as u2 set u2.name = 'hero',u2.age=23,u2.sex=1 where id=7;
Query OK, 1 row affected
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from `user2`;
+----+------+-----+---------+------+
| id | name | age | address | sex  |
+----+------+-----+---------+------+
|  7 | hero |  23 | fuzhou  |    1 |
|  8 | sol  |  21 | xiamen  | NULL |
+----+------+-----+---------+------+
2 rows in set1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.

还有一种方式是同时更新多个表，使用不同的别名以及一些条件去限制，不过不建议这么做，操作易错，并且不好维护。

DELETE操作
delete方式删除
语法格式如下：

delete [alias] from t_name [[as] alias] [where condition];
1.

1、跟上面一样，alias代表别名，没有别名情况下，表名就是别名

2、如果表设置了别名，则delete后面必须跟上别名，否则数据库会报异常。

测试一下：

mysql> select * from `user2`;
+----+------+-----+---------+------+
| id | name | age | address | sex  |
+----+------+-----+---------+------+
|  7 | hero |  23 | fuzhou  |    1 |
|  8 | sol  |  21 | xiamen  | NULL |
+----+------+-----+---------+------+
2 rows in set

mysql>  delete from `user2` as alias where sex=1;
1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'as alias where sex=1' at line 1

mysql>  delete alias from `user2` as alias where sex=1;
Query OK, 1 row affected

mysql> select * from `user2`;
+----+------+-----+---------+------+
| id | name | age | address | sex  |
+----+------+-----+---------+------+
|  8 | sol  |  21 | xiamen  | NULL |
+----+------+-----+---------+------+
1 row in set1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.

3、如果删除表中所有的数据，则后面不带上where条件即可，不过要谨慎使用哟。

mysql> select * from `user2`;
+----+-------+-----+----------+-----+
| id | name  | age | address  | sex |
+----+-------+-----+----------+-----+
|  8 | sol   |  21 | xiamen   |   0 |
| 10 | brand |  21 | fuzhou   |   1 |
| 11 | helen |  20 | quanzhou |   0 |
+----+-------+-----+----------+-----+
3 rows in set

mysql> delete from `user2`;
Query OK, 3 rows affected

mysql> select * from `user2`;
Empty set1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.

truncate方式删除
语法格式如下：

truncate t_name;
1.

mysql> select * from `user2`;
+----+-------+-----+----------+-----+
| id | name  | age | address  | sex |
+----+-------+-----+----------+-----+
| 12 | brand |  21 | fuzhou   |   1 |
| 13 | helen |  20 | quanzhou |   0 |
| 14 | sol   |  21 | xiamen   |   0 |
+----+-------+-----+----------+-----+
3 rows in set

mysql> truncate `user2`;
Query OK, 0 rows affected

mysql> select * from `user2`;
Empty set1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.

看起来跟delete很像，但是重新插入数据会发现，他的自增主键会重新从1开始，但是delete的是直接在原来的所以自增值之后往上加。看下面id字段。

mysql> insert into `user2` (name,age,address,sex) values('brand',21,'fuzhou',1),('helen',20,'quanzhou',0),('sol',21,'xiamen',0);
Query OK, 3 rows affected
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from `user2`;
+----+-------+-----+----------+-----+
| id | name  | age | address  | sex |
+----+-------+-----+----------+-----+
|  1 | brand |  21 | fuzhou   |   1 |
|  2 | helen |  20 | quanzhou |   0 |
|  3 | sol   |  21 | xiamen   |   0 |
+----+-------+-----+----------+-----+
3 rows in set1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.