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

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)