概述

本章，您将学习 MySQL 中有关 DML 的知识。

DML（Data Manipulation Language，数据操作语言），主要指的是 insert 、update、delete 这些相关的语句。

插入数据（insert）

在已有表的基础上插入新的数据，插入时需要注意：

• 插入的数据需要与字段个数对应
• 插入时，数据应该与字段的数据类型一致

插入数据的基本语法为：

# 这种语法支持在一条 SQL 语句中插入一行或多行数据
insert into 表名(字段名1,字段名2...) values(值1,值2...),(...),(...)...; 

# 这种语法只支持在一条 SQL 语句中插入一行数据
insert into 表名 set 字段名1=值1,字段名2=值2...;

前面提到，可以使用 desc 关键字查看表的结构：

use world;
show tables from world;
desc city;

输出如下：

+-------------+----------+------+-----+---------+----------------+
| Field       | Type     | Null | Key | Default | Extra          |
+-------------+----------+------+-----+---------+----------------+
| ID          | int      | NO   | PRI | NULL    | auto_increment |
| Name        | char(35) | NO   |     |         |                |
| CountryCode | char(3)  | NO   | MUL |         |                |
| District    | char(20) | NO   |     |         |                |
| Population  | int      | NO   |     | 0       |                |
+-------------+----------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

除了可以使用 desc 查看表结构外，我们还可以使用下面的语法查看表的创建语法：

show create table city;

输出如下：

CREATE TABLE city (
  ID int NOT NULL AUTO_INCREMENT,
  Name char(35) NOT NULL DEFAULT '',
  CountryCode char(3) NOT NULL DEFAULT '',
  District char(20) NOT NULL DEFAULT '',
  Population int NOT NULL DEFAULT '0',
  PRIMARY KEY (ID),
  KEY CountryCode (CountryCode),
  CONSTRAINT city_ibfk_1 FOREIGN KEY (CountryCode) REFERENCES country (Code)
)

关于约束和数据类型我们还没有介绍，先略过。

可以知道：

• ID 字段是自增的主键列；
• name、countrycode、district、population 都是字符数据类型
• name、countrycode、district 没有默认值，population 有默认值
• 通过 show create table city; 可知，由于有约束名 city_ibfk_1，因此可以判断这是表级约束（列级约束不能自定义约束名称），也可知 country 是主表，city 是从表，从表的 countrycode 列数据需要参照主表的 code 列数据

不指定字段，插入一行数据：

insert into city values(null,'tmp-city','AFG','tmp-city',100000);

select * from city where name like 'tmp%';

ID       Name      CountryCode  District Population
4080    tmp-city    AFG         tmp-city   100000

有时，我们需要 select 的结果集作为插入的数据，其语法为：

insert into 表名 select 字段1,字段2,字段3,字段4,字段5 from 表名;

更新数据（update）

单表的数据更新

语法为：

update 表名 set 字段1=值1,字段2=值2...  where  筛选条件;

比如更新上面插入的数据：

update city set name='zxcv',district='zxcv' where id=4080;

select * from city where id=4080;

ID       Name      CountryCode  District Population
4080     zxcv          AFG         zxcv    100000

多表的数据更新

不建议在生产环境下这样一次性操作多表，这很容易出错。若一定要操作多表，建议使用 事务（一种保证数据完整性与一致性的机制）。

涉及到多表时，需要使用到前面学习的 SQL 99 版本的内连接（等值连接、非等值连接、自连接）和外连接（左外连接、右外连接）。

相关语法为：

update  表名1  别名1   连接类型  join  表2  表名2  on  连接条件  set  字段1=值1,字段2=值2...  where  筛选条件;

在涉及多表的数据更新时，通常我们需要先查询有多少行的相关数据：

# 输出 5 行数据
select a.name,b.name from city as a inner join country as b on a.countrycode=b.code where b.code='AFG';

name       name
Kabul   Afghanistan
Qandahar    Afghanistan
Herat   Afghanistan
Mazar-e-Sharif  Afghanistan
zxcv    Afghanistan

update city as a inner join country as b on a.countrycode=b.code set a.name='zxcv',b.name='Afghan-new' where b.code='AFG';

name     name
zxcv    Afghan-new
zxcv    Afghan-new
zxcv    Afghan-new
zxcv    Afghan-new
zxcv    Afghan-new

删除数据（delete）

单表的数据删除

基本语法为：

delete from 表名 where 筛选条件;

需要注意！若不添加筛选条件，则会将表中的所有行数据删除。

若您需要清空表中的数据，我们建议您使用 truncate 语法：

truncate table 表名;

同样都是清空行数据，两者的区别在于：

• 效率不同 – delete 语法清空数据是以行为单位进行数据的删除，在效率上不如 truncate
• 风险不同 – delete 语法清空数据后可通过 回滚 的方式恢复数据（回滚通过 binlog）；但 truncate 则是彻底的清空数据，且在相应的日志中没有记录
• 当有自增长的列时，使用 delete删除后再使用 insert into，自增长列的值从断点处开始记；而使用 truncate 清空表的行数据后，插入自增长列的值从 1 开始记
• delete 有返回值，提升有多少行受影响，但是 truncate 没有返回值

删除 ID 为 4080 的行数据：

# 在删除之前先查询相应的数据，养成好习惯
select * from city where id=4080;

# 确定删除
delete from city where id=4080;

多表的数据删除

不建议在生产环境下这样一次性操作多表，这很容易出错。若一定要操作多表，建议使用 事务（一种保证数据完整性与一致性的机制）。

相关的语法为：

delete 表1别名,表2别名 from 表名1 as 表1别名 连接类型 join 表名2 as 表2别名 on 连接条件 where 筛选条件;

比如：

# 先查询出具体的数据
## 这里输出 4 条数据（4 座城市对应一个国家，即删除 city 的 4 行数据同时也删除 country 的 1 行数据）
select  a.*,b.*  from city a inner join  country b on a.countrycode=b.code where a.countrycode="AFG";

# 假设需要将这些数据删除
## ## 这会报错，因为 countrylanguage 表的 countrycode 列参考了 country 表的 code 列。当我们把 country 表的 code='AFG' 这行数据删除时，countrylanguage 表中与之对应的数据没有了参考的数据对象
delete a,b from city as a inner join country as b on a.countrycode=b.code where a.countrycode='AFG';

最后

在执行更新与操作之前要养成良好的习惯，即先查询相应的数据，后执行操作。

赞赏