3.3 强大的多层嵌套子查询——子查询
25 Oct 2019
子查询指的是一条查询语句嵌套在另一条查询语句内部，即在一条select语句的from或where子句中包含另一条select语句。在查询语句中，外层select语句称为主查询，from或where子句中包含的select语句称为子查询，也称嵌套查询。理论上子查询可以出现在select语句的任何位置，但实际开发中，子查询经常出现在from和where子句中。本节将介绍SQL子查询方法，其中包括：from型子查询和where型子查询。
3.3.1 from型子查询
from型子查询也称为表子查询，子查询语句位置在from后，用来返回多行多列的数据记录，返回的数据记录可以当作一张临时表。MySQL中from型子查询基本语法形式为：
select column_name from (select column_name from table_name) as table_name_new;
小贴士:使用from型子查询必须给内层select语句所生成的临时表提供一个新表名！
本节将使用样例表player和penalty，数据表结构、创建及插入数据语句如下：
| 字段名称 | 数据类型 | 约束 | 备注 |
|---|---|---|---|
| id | int(10) | 主键约束 | 球员编号 |
| name | varchar(20) | / | 球员姓名 |
| birthday | datetime(6) | / | 出生日期 |
| sex | varchar(20) | / | 球员性别 |
| town | varchar(20) | / | 居住城市 |
player表数据如下：
| id | name | birthday | sex | town |
|---|---|---|---|---|
| 1 | Dempsey | 1982-9-1 | M | Stratford |
| 2 | Aldrich | 1981-6-25 | M | Stratford |
| 7 | Alex | 1983-5-11 | M | Stratford |
| 8 | Nic | 1986-11-1 | F | Inglewood |
| 27 | Devin | 1982-12-28 | F | Eltham |
| 28 | Dick | 1981-6-22 | F | Midhurst |
| 39 | Fitch | 1986-10-29 | M | Stratford |
| 44 | Mason | 1983-1-9 | M | Inglewood |
| 57 | Noah | 1978-8-17 | M | Stratford |
| 83 | Hope | 1986-11-11 | M | Stratford |
| 95 | Cumberland | 1983-5-14 | M | Douglas |
| 100 | Parmenter | 1983-2-28 | M | Stratford |
| 104 | Esther | 1975-5-10 | F | Eltham |
| 112 | Conrad | 1983-10-1 | F | Plymouth |
| 字段名称 | 数据类型 | 约束 | 备注 |
|---|---|---|---|
| p_id | int(10) | 外键约束 | 球员编号 |
| pay_no | int(10) | 主键约束 | 罚款编号 |
| pay_date | datetime(6) | / | 罚款时间 |
| amount | float(10) | / | 罚款金额 |
penalty表数据如下：
| p_id | pay_no | pay_date | amount |
|---|---|---|---|
| 44 | 2 | 1991-5-5 | 75000 |
| 27 | 3 | 1983-9-10 | 10000 |
| 104 | 4 | 1984-12-8 | 50000 |
| 44 | 5 | 1991-12-8 | 25000 |
| 8 | 6 | 1991-11-12 | 25000 |
| 44 | 7 | 1992-12-30 | 30000 |
| 27 | 8 | 1984-11-12 | 75000 |
player球员信息表的代码输入如下：
结果输出如下：
penalty罚款信息表的代码输入如下：
结果输出如下：
【例】查询球员编号小于10的男性球员姓名，上述需求可拆解成以下几个部分：
· 编号小于10的球员
· 性别为男性的球员
可以看出这是一个单表查询的问题，实际上我们可以用多条件查询解决，MySQL输入及输出如下：
但我们也可以将上述多条件查询改写成子查询的方法，MySQL输入及输出如下：
3.3.2 where型子查询
where型子查询包括行子查询和列子查询两种，其子查询语句位置在where后，行子查询用来返回单行的数据记录，而列子查询用来返回多行的数据记录。
（1）行子查询
行子查询主要包括单行单列和单行多列的情况，MySQL基本语法形式为：
select column_name from table_name where column_name 逻辑操作符 (
select column_name from table_name where 查询条件);
① 单行单列
【例】查询与100号球员性别相同的球员编号和姓名。
② 单行多列
【例】查询与100号球员性别相同且居住城市相同的球员编号和姓名。
小贴士：当用where进行多条件判断时，也可以用in关键字来简化代码量，即上述语句可写为：
（2）列子查询
行子查询主要面对多行单列的情况，通常会包含in、any、all、exists等关键字。
① 带有in关键字的列子查询
当主查询的条件存在于子查询的结果中时，可以通过关键字in进行判断。相反如果想实现主查询的条件不存在于子查询的结果中时，可以通过关键字not in进行判断，MySQL基本语法形式为：
select column_name from table_name where column_name in (select column_name from table_name where 查询条件);
【例】查询所有有过罚款记录的球员编号和球员姓名。
② 带有any关键字的列子查询
当主查询的条件满足子查询返回结果中任意一条数据记录时，可以通过关键字any进行判断，MySQL基本语法形式为：
select column_name from table_name where column_name 逻辑操作符
any(select column_name from table_name where 查询条件);
【例】查询至少比另一个球员年轻且居住在同一城市的球员编号和球员姓名。
上述需求可拆解成以下2个部分：
·居住城市相同的球员；
·至少比另一个球员年轻的球员。
MySQL输入及输出如下：
③ 带有all关键字的列子查询
当主查询的条件满足子查询返回结果中所有数据记录，可以通过关键字all进行判断，MySQL基本语法形式为：
select column_name from table_name where column_name 逻辑操作符 all(
select column_name from table_name where 查询条件);
【例】查询最年轻的球员编号和球员姓名，MySQL输入及输出如下：
④ 带有exists关键字的列子查询
关键字exists是一个布尔类型，当有返回结果时为True，不能返回结果时为False，MySQL基本语法形式为：
select column_name from table_name where exists (
select column_name from table_name where 查询条件);
【例】查询至少支付了一次罚款的球员编号和球员姓名，MySQL输入及输出如下：
小贴士：
exists表示返回结果为True，如果想要查询返回结果为False时，可以使用not exists关键字
【例】查询没有支付过罚款的球员编号和球员姓名。
练习
1.用子查询方法，从emp表中查询姓名首字母为c，薪资高于6000，津贴不为空的员工；
2.从player表中查询与27号球员性别相同且居住城市相同的球员编号和姓名；
3.从player表中查询最年长的球员编号和球员姓名。
练习答案
1.第一题的语句如下：
2.第二题的语句如下：
方法1
方法2
3.第三题的语句如下：
分类: 数据库技术