MySQL讲义第 31 讲—— DML 语句与 select 查询

DML 语句实现对表中数据的更新操作，包括 INSERT（插入记录）、DELETE（删除记录）、UPDATE（更新数据）。DML 语句结合 select 查询，可以实现一些复杂的操作。

一、 INSERT … SELECT 语句

该命令可以把一个查询的结果插入到一张表中。语法格式如下：

INSERT INTO 表名 SELECT 语句;

例如：有下面两张表，把 salary 表的 e_id，e_name，salary 三个字段对应的数据插入到 emp_salary 表中。

mysql> select * from emp; +------+-----------+--------+---------+--------+-----------+ | e_id | e_name | gender | salary | leader | dept_name | +------+-----------+--------+---------+--------+-----------+ | 1101 | 张美华 | 女 | 5000.00 | 1104 | 销售部 | | 1102 | 王涛 | 男 | 5200.00 | 1104 | 销售部 | | 1103 | 张学有 | 男 | 4700.00 | 1104 | 销售部 | | 1104 | 刘得华 | 男 | 5200.00 | 1104 | 销售部 | | 1105 | 董雯花 | 女 | 5900.00 | 1104 | 销售部 | | 1106 | 宋族营 | 男 | 6500.00 | 1104 | 销售部 | | 2201 | 李霜江 | 男 | 7200.00 | 2202 | 财务部 | | 2202 | 梁美丽 | 女 | 6400.00 | 2202 | 财务部 | | 2203 | 王大强 | 男 | 6100.00 | 2202 | 财务部 | | 3301 | 张美华 | 女 | 7800.00 | 3302 | 技术部 | | 3302 | 赵紫龙 | 男 | 6900.00 | 3302 | 技术部 | | 3303 | 诸葛量 | 男 | 9200.00 | 3302 | 技术部 | | 3304 | 曹梦德 | 男 | 8400.00 | 3302 | 技术部 | +------+-----------+--------+---------+--------+-----------+ 13 rows in set (0.05 sec) mysql> select * from emp_salary; Empty set (0.00 sec) mysql> desc emp_salary; +--------+---------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------+---------------+------+-----+---------+-------+ | e_id | int(11) | NO | PRI | NULL | | | e_name | char(20) | YES | | NULL | | | salary | decimal(10,2) | YES | | NULL | | +--------+---------------+------+-----+---------+-------+ 3 rows in set (0.01 sec) mysql> INSERT INTO -> emp_salary -> SELECT -> e_id, -> e_name, -> salary -> from -> emp; Query OK, 13 rows affected (0.03 sec) Records: 13 Duplicates: 0 Warnings: 0 mysql> SELECT -> * -> FROM -> emp_salary; +------+-----------+---------+ | e_id | e_name | salary | +------+-----------+---------+ | 1101 | 张美华 | 5000.00 | | 1102 | 王涛 | 5200.00 | | 1103 | 张学有 | 4700.00 | | 1104 | 刘得华 | 5200.00 | | 1105 | 董雯花 | 5900.00 | | 1106 | 宋族营 | 6500.00 | | 2201 | 李霜江 | 7200.00 | | 2202 | 梁美丽 | 6400.00 | | 2203 | 王大强 | 6100.00 | | 3301 | 张美华 | 7800.00 | | 3302 | 赵紫龙 | 6900.00 | | 3303 | 诸葛量 | 9200.00 | | 3304 | 曹梦德 | 8400.00 | +------+-----------+---------+ 13 rows in set (0.00 sec)

二、DELETE 命令嵌套子查询

在 DELETE 命令的 WHERE 子句中可以嵌套子查询。语法格式如下：

DELETE FROM 表名 WHERE 表达式 运算符 子查询;

举例：有两张表。

mysql> select * from dept; +---------+--------------+ | dept_id | dept_name | +---------+--------------+ | D01 | 管理系 | | D02 | 计算机系 | | D03 | 数学系 | +---------+--------------+ 3 rows in set (0.05 sec) mysql> select * from stu; +-----------+-----------+--------+--------+-------------+---------+ | stu_id | stu_name | gender | height | phone | dept_id | +-----------+-----------+--------+--------+-------------+---------+ | 201801101 | 王占峰 | 男 | 177.0 | 15937320987 | D02 | | 201901002 | 王宏伟 | 男 | 180.0 | 15937320255 | D01 | | 201901004 | 李刚 | 男 | 178.0 | 15937320321 | D01 | | 201901005 | 刘鹏 | 男 | 176.0 | 15937320654 | D01 | +-----------+-----------+--------+--------+-------------+---------+ 4 rows in set (0.00 sec)

删除计算机系的学生。

mysql> DELETE FROM -> stu -> WHERE -> dept_id = -> (SELECT dept_id FROM dept WHERE dept_name = '计算机系'); Query OK, 1 row affected (0.07 sec) mysql> select * from stu; +-----------+-----------+--------+--------+-------------+---------+ | stu_id | stu_name | gender | height | phone | dept_id | +-----------+-----------+--------+--------+-------------+---------+ | 201901002 | 王宏伟 | 男 | 180.0 | 15937320255 | D01 | | 201901004 | 李刚 | 男 | 178.0 | 15937320321 | D01 | | 201901005 | 刘鹏 | 男 | 176.0 | 15937320654 | D01 | +-----------+-----------+--------+--------+-------------+---------+ 3 rows in set (0.00 sec)

三、UPDATE 命令嵌套子查询

在 UPDATE 命令的 WHERE 子句中可以嵌套子查询。语法格式如下：

UPDATE 表名 SET 字段名 = 值 WHERE 表达式 运算符 子查询;

格式：

UPDATE emp SET salary = salary * 1.1 WHERE dept_name = (SELECT dept_name FROM emp WHERE e_name = '王涛');

举例：有以下两张表。

mysql> select * from dept; +---------+-----------+ | dept_id | dept_name | +---------+-----------+ | 11 | 财务部 | | 12 | 技术部 | | 13 | 销售部 | | 14 | 公关部 | +---------+-----------+ 4 rows in set (0.00 sec) mysql> select * from emp; +------+-----------+--------+---------+---------+ | e_id | e_name | gender | salary | dept_id | +------+-----------+--------+---------+---------+ | 1101 | 张美华 | 女 | 5000.00 | 11 | | 1102 | 王涛 | 男 | 5200.00 | 11 | | 1103 | 张学有 | 男 | 4700.00 | 11 | | 1201 | 刘得华 | 男 | 5200.00 | 12 | | 1202 | 董雯花 | 女 | 5900.00 | 12 | | 1203 | 宋族营 | 男 | 6500.00 | 12 | | 1204 | 李霜江 | 男 | 7200.00 | 12 | | 1301 | 梁美丽 | 女 | 6400.00 | 13 | | 1302 | 王大强 | 男 | 6100.00 | 13 | | 1303 | 张美华 | 女 | 7800.00 | 13 | | 1304 | 赵紫龙 | 男 | 6900.00 | 13 | | 1305 | 诸葛量 | 男 | 9200.00 | 13 | | 1306 | 曹梦德 | 男 | 8400.00 | 13 | +------+-----------+--------+---------+---------+ 13 rows in set (0.00 sec)

把销售部所有员工的工资增加 10%。

mysql> UPDATE -> emp -> SET -> salary = salary * 1.1 -> WHERE -> dept_id = -> (SELECT dept_id FROM dept WHERE dept_name = '销售部'); Query OK, 6 rows affected (0.01 sec) Rows matched: 6 Changed: 6 Warnings: 0 mysql> select * from emp; +------+-----------+--------+----------+---------+ | e_id | e_name | gender | salary | dept_id | +------+-----------+--------+----------+---------+ | 1101 | 张美华 | 女 | 5000.00 | 11 | | 1102 | 王涛 | 男 | 5200.00 | 11 | | 1103 | 张学有 | 男 | 4700.00 | 11 | | 1201 | 刘得华 | 男 | 5200.00 | 12 | | 1202 | 董雯花 | 女 | 5900.00 | 12 | | 1203 | 宋族营 | 男 | 6500.00 | 12 | | 1204 | 李霜江 | 男 | 7200.00 | 12 | | 1301 | 梁美丽 | 女 | 7040.00 | 13 | | 1302 | 王大强 | 男 | 6710.00 | 13 | | 1303 | 张美华 | 女 | 8580.00 | 13 | | 1304 | 赵紫龙 | 男 | 7590.00 | 13 | | 1305 | 诸葛量 | 男 | 10120.00 | 13 | | 1306 | 曹梦德 | 男 | 9240.00 | 13 | +------+-----------+--------+----------+---------+ 13 rows in set (0.00 sec)

四、使用 UPDATE … JOIN ON 关联更新

有一张 comm 表，表结构及数据如下：

mysql> select * from comm; +------+--------+--------+----------+ | e_id | e_name | gender | comm | +------+--------+--------+----------+ | 1102 | NULL | NULL | 15000.00 | | 1103 | NULL | NULL | 25000.00 | | 1202 | NULL | NULL | 27000.00 | | 1203 | NULL | NULL | 52000.00 | | 1302 | NULL | NULL | 43000.00 | | 1304 | NULL | NULL | 65000.00 | | 1306 | NULL | NULL | 38000.00 | +------+--------+--------+----------+ 7 rows in set (0.00 sec)

根据 emp 表中的数据把 comm 表中的 e_name 列和 gender 列补充完整，命令如下：

mysql> UPDATE -> comm -> JOIN emp ON comm.e_id = emp.e_id -> SET -> comm.e_name = emp.e_name, -> comm.gender = emp.gender; Query OK, 7 rows affected (0.00 sec) Rows matched: 7 Changed: 7 Warnings: 0 mysql> select * from comm; +------+-----------+--------+----------+ | e_id | e_name | gender | comm | +------+-----------+--------+----------+ | 1102 | 王涛 | 男 | 15000.00 | | 1103 | 张学有 | 男 | 25000.00 | | 1202 | 董雯花 | 女 | 27000.00 | | 1203 | 宋族营 | 男 | 52000.00 | | 1302 | 王大强 | 男 | 43000.00 | | 1304 | 赵紫龙 | 男 | 65000.00 | | 1306 | 曹梦德 | 男 | 38000.00 | +------+-----------+--------+----------+ 7 rows in set (0.00 sec)