⼦查询
出现在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:可以与=、>、>=、<、<=、<>结合是来使⽤,分别表⽰等于、⼤于、⼤于等于、⼩
于、⼩于等于、不等于其中的其中的所有数据。
下⽂中会经常⽤到这些关键字。