MySQL数据库，子查询学习，高手必备(一)

⼦查询

出现在select语句中的select语句，称为⼦查询或内查询。

外部的select查询语句，称为主查询或外查询。

⼦查询分类

按结果集的⾏列数不同分为4种

• 标量⼦查询（结果集只有⼀⾏⼀列）

• 列⼦查询（结果集只有⼀列多⾏）

• ⾏⼦查询（结果集有⼀⾏多列）

• 表⼦查询（结果集⼀般为多⾏多列）

按⼦查询出现在主查询中的不同位置分

• select后⾯：仅仅⽀持标量⼦查询。

• from后⾯：⽀持表⼦查询。

• where或having后⾯：⽀持标量⼦查询（单列单⾏）、列⼦查询（单列多⾏）、⾏⼦

查询（多列多⾏）

• exists后⾯（即相关⼦查询）：表⼦查询（多⾏、多列）

准备测试数据

测试数据⽐较多，放在我的个⼈博客上了。

浏览器中打开链接：http://www.itsoku.com/article/209

mysql中执⾏⾥⾯的javacode2018_employees库部分的脚本。示例2

查询员⼯号=102的部门名称

SELECT (SELECT a.department_name

FROM departments a, employees b

WHERE a.department_id = b.department_id

AND b.employee_id = 102) AS 部门名;

from后⾯的⼦查询

将⼦查询的结果集充当⼀张表，要求必须起别名，否者这个表找不到。

然后将真实的表和⼦查询结果表进⾏连接查询。

示例1

查询每个部门平均⼯资的⼯资等级

-- 查询每个部门平均⼯资

SELECT

department_id,

avg(a.salary)

FROM employees a

GROUP BY a.department_id;

-- 薪资等级表

SELECT *

FROM job_grades;

-- 将上⾯2个结果连接查询，筛选条件:平均⼯资 between lowest_sal and

highest_sal;

SELECT

t1.department_id,

sa AS '平均⼯资',

t2.grade_level

FROM (SELECT

department_id,

avg(a.salary) sa

FROM employees a GROUP BY a.department_id) t1, job_grades t2

WHERE

t1.sa BETWEEN t2.lowest_sal AND t2.highest_sal;

运⾏最后⼀条结果如下：

mysql> SELECT

t1.department_id,

sa AS '平均⼯资',

t2.grade_level

FROM (SELECT

department_id,

avg(a.salary) sa

FROM employees a

GROUP BY a.department_id) t1, job_grades t2

WHERE

t1.sa BETWEEN t2.lowest_sal AND t2.highest_sal;

+---------------+--------------+-------------+

| department_id | 平均⼯资 | grade_level |

+---------------+--------------+-------------+

| NULL | 7000.000000 | C |

| 10 | 4400.000000 | B |

| 20 | 9500.000000 | C |

| 30 | 4150.000000 | B |

| 40 | 6500.000000 | C |

| 50 | 3475.555556 | B |

| 60 | 5760.000000 | B |

| 70 | 10000.000000 | D |

| 80 | 8955.882353 | C |

| 90 | 19333.333333 | E |

| 100 | 8600.000000 | C |

| 110 | 10150.000000 | D |

+---------------+--------------+-------------+

12 rows in set (0.00 sec)where和having后⾯的⼦查询

where或having后⾯，可以使⽤

1. 标量⼦查询（单⾏单列⾏⼦查询）

2. 列⼦查询（单列多⾏⼦查询）

3. ⾏⼦查询（⼀⾏多列）

特点

1. ⼦查询放在⼩括号内。

2. ⼦查询⼀般放在条件的右侧。

3. 标量⼦查询，⼀般搭配着单⾏单列操作符使⽤ >、<、>=、<=、=、<>、!=

4. 列⼦查询，⼀般搭配着多⾏操作符使⽤

in(not in)：列表中的“任意⼀个”

any或者some：和⼦查询返回的“某⼀个值”⽐较，⽐如a>some(10,20,30)，a⼤于

⼦查询中任意⼀个即可，a⼤于⼦查询中最⼩值即可，等同于a>min(10,20,30)。

all：和⼦查询返回的“所有值”⽐较，⽐如a>all(10,20,30)，a⼤于⼦查询中所有

值，换句话说，a⼤于⼦查询中最⼤值即可满⾜查询条件，等同于

a>max(10,20,30);

5. ⼦查询的执⾏优先于主查询执⾏，因为主查询的条件⽤到了⼦查询的结果。

mysql中的in、any、some、all

in，any，some，all分别是⼦查询关键词之⼀。

in：in常⽤于where表达式中，其作⽤是查询某个范围内的数据

any和some⼀样：可以与=、>、>=、<、<=、<>结合起来使⽤，分别表⽰等于、⼤于、

⼤于等于、⼩于、⼩于等于、不等于其中的任何⼀个数据。all：可以与=、>、>=、<、<=、<>结合是来使⽤，分别表⽰等于、⼤于、⼤于等于、⼩

于、⼩于等于、不等于其中的其中的所有数据。

下⽂中会经常⽤到这些关键字。