打算提升sql技能的,可以加我微信itsoku,带你成为sql高手。
这是Mysql系列第12篇。
环境:mysql5.7.25,cmd命令中进行演示。
本章节非常重要。
子查询
出现在select语句中的select语句,称为子查询或内查询。
外部的select查询语句,称为主查询或外查询。
子查询分类
按结果集的行列数不同分为4种
标量子查询(结果集只有一行一列)
列子查询(结果集只有一列多行)
行子查询(结果集有一行多列)
表子查询(结果集一般为多行多列)
按子查询出现在主查询中的不同位置分
select后面:仅仅支持标量子查询。
from后面:支持表子查询。
where或having后面:支持标量子查询(单列单行)、列子查询(单列多行)、行子查询(多列多行)
exists后面(即相关子查询):表子查询(多行、多列)
准备测试数据
测试数据比较多,放在我的个人博客上了。
浏览器中打开链接:http://www.itsoku.com/article/209
mysql中执行里面的javacode2018_employees库
部分的脚本。
成功创建javacode2018_employees
库及5张表,如下:
表名 | 描述 |
---|---|
departments | 部门表 |
employees | 员工信息表 |
jobs | 职位信息表 |
locations | 位置表(部门表中会用到) |
job_grades | 薪资等级表 |
select后面的子查询
子查询位于select后面的,仅仅支持标量子查询。
示例1
查询每个部门员工个数
-
SELECT
-
a.*,
-
(
SELECT
count(*)
-
FROM employees b
-
WHERE b.
department_id = a.
department_id)
AS 员工个数
-
FROM departments a;
示例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后面,可以使用
标量子查询(单行单列行子查询)
列子查询(单列多行子查询)
行子查询(多行多列)
特点
子查询放在小括号内。
子查询一般放在条件的右侧。
标量子查询,一般搭配着单行操作符使用,多行操作符 >、<、>=、<=、=、<>、!=
列子查询,一般搭配着多行操作符使用
in(not in):列表中的“任意一个”
any或者some:和子查询返回的“某一个值”比较,比如a>som(10,20,30),a大于子查询中任意一个即可,a大于子查询中最小值即可,等同于a>min(10,20,30)。
all:和子查询返回的“所有值”比较,比如a>all(10,20,30),a大于子查询中所有值,换句话说,a大于子查询中最大值即可满足查询条件,等同于a>max(10,20,30);
子查询的执行优先于主查询执行,因为主查询的条件用到了子查询的结果。
mysql中的in、any、some、all
in,any,some,all分别是子查询关键词之一。
in:in常用于where表达式中,其作用是查询某个范围内的数据
any和some一样: 可以与=、>、>=、<、<=、<>结合起来使用,分别表示等于、大于、大于等于、小于、小于等于、不等于其中的任何一个数据。
all:可以与=、>、>=、<、<=、<>结合是来使用,分别表示等于、大于、大于等于、小于、小于等于、不等于其中的其中的所有数据。
下文中会经常用到这些关键字。
标量子查询
一般标量子查询,示例
查询谁的工资比Abel的高?
-
/*①查询abel的工资【改查询是标量子查询】*/
-
SELECT salary
-
FROM employees
-
WHERE last_name =
'Abel';
-
/*②查询员工信息,满足salary>①的结果*/
-
SELECT *
-
FROM employees a
-
WHERE a.
salary > (
SELECT salary
-
FROM employees
-
WHERE last_name =
'Abel');
多个标量子查询,示例
返回job_id与141号员工相同,salary比143号员工多的员工、姓名、job_id和工资
-
/*返回job_id与141号员工相同,salary比143号员工多的员工、姓名、job_id和工资*/
-
/*①查询141号员工的job_id*/
-
SELECT job_id
-
FROM employees
-
WHERE employee_id =
141;
-
/*②查询143好员工的salary*/
-
SELECT salary
-
FROM employees
-
WHERE employee_id =
143;
-
/*③查询员工的姓名、job_id、工资,要求job_id=① and salary>②*/
-
SELECT
-
a.
last_name 姓名,
-
a.
job_id,
-
a.
salary 工资
-
FROM employees a
-
WHERE a.
job_id = (
SELECT job_id
-
FROM employees
-
WHERE employee_id =
141)
-
AND
-
a.
salary > (
SELECT salary
-
FROM employees
-
WHERE employee_id =
143);
子查询+分组函数,示例
查询最低工资大于50号部门最低工资的部门id和其最低工资【having】
-
/*查询最低工资大于50号部门最低工资的部门id和其最低工资【having】*/
-
/*①查询50号部门的最低工资*/
-
SELECT
min(salary)
-
FROM employees
-
WHERE department_id =
50;
-
/*②查询每个部门的最低工资*/
-
SELECT
-
min(salary),
-
department_id
-
FROM employees
-
GROUP
BY department_id;
-
/*③在②的基础上筛选,满足min(salary)>①*/
-
SELECT
-
min(a.
salary) minsalary,
-
department_id
-
FROM employees a
-
GROUP
BY a.
department_id
-
HAVING
min(a.
salary) > (
SELECT
min(salary)
-
FROM employees
-
WHERE department_id =
50);
错误的标量子查询,示例
将上面的示例③中子查询语句中的min(salary)改为salary,执行效果如下:
-
mysql>
SELECT
-
min(a.
salary) minsalary,
-
department_id
-
FROM employees a
-
GROUP
BY a.
department_id
-
HAVING
min(a.
salary) > (
SELECT salary
-
FROM employees
-
WHERE department_id =
500000);
-
ERROR
1242 (
21000):
Subquery returns more than
1 row
错误提示:子查询返回的结果超过了1行记录。
说明:上面的子查询只支持最多一列一行记录。
列子查询
列子查询需要搭配多行操作符使用:in(not in)、any/some、all。
为了提升效率,最好去重一下distinct关键字。
示例1
返回location_id是1400或1700的部门中的所有员工姓名
-
/*返回location_id是1400或1700的部门中的所有员工姓名*/
-
/*方式1*/
-
/*①查询location_id是1400或1700的部门编号*/
-
SELECT
DISTINCT department_id
-
FROM departments
-
WHERE location_id
IN (
1400,
1700);
-
/*②查询员工姓名,要求部门是①列表中的某一个*/
-
SELECT a.
last_name
-
FROM employees a
-
WHERE a.
department_id
IN (
SELECT
DISTINCT department_id
-
FROM departments
-
WHERE location_id
IN (
1400,
1700));
-
/*方式2:使用any实现*/
-
SELECT a.
last_name
-
FROM employees a
-
WHERE a.
department_id =
ANY (
SELECT
DISTINCT department_id
-
FROM departments
-
WHERE location_id
IN (
1400,
1700));
-
/*拓展,下面与not in等价*/
-
SELECT a.
last_name
-
FROM employees a
-
WHERE a.
department_id <>
ALL (
SELECT
DISTINCT department_id
-
FROM departments
-
WHERE location_id
IN (
1400,
1700));
示例2
返回其他工种中比job_id为'IT_PROG'工种任意工资低的员工的员工号、姓名、job_id、salary
-
/*返回其他工种中比job_id为'IT_PROG'工种任一工资低的员工的员工号、姓名、job_id、salary*/
-
/*①查询job_id为'IT_PROG'部门任-工资*/
-
SELECT
DISTINCT salary
-
FROM employees
-
WHERE job_id =
'IT_PROG';
-
/*②查询员工号、姓名、job_id、salary,slary<①的任意一个*/
-
SELECT
-
last_name,
-
employee_id,
-
job_id,
-
salary
-
FROM employees
-
WHERE salary <
ANY (
SELECT
DISTINCT salary
-
FROM employees
-
WHERE job_id =
'IT_PROG')
AND job_id !=
'IT_PROG';
-
/*或者*/
-
SELECT
-
last_name,
-
employee_id,
-
job_id,
-
salary
-
FROM employees
-
WHERE salary < (
SELECT
max(salary)
-
FROM employees
-
WHERE job_id =
'IT_PROG')
AND job_id !=
'IT_PROG';
示例3
返回其他工种中比job_id为'IT_PROG'部门所有工资低的员工的员工号、姓名、job_id、salary
-
/*返回其他工种中比job_id为'IT_PROG'部门所有工资低的员工的员工号、姓名、job_id、salary*/
-
SELECT
-
last_name,
-
employee_id,
-
job_id,
-
salary
-
FROM employees
-
WHERE salary <
ALL (
SELECT
DISTINCT salary
-
FROM employees
-
WHERE job_id =
'IT_PROG')
AND job_id !=
'IT_PROG';
-
/*或者*/
-
SELECT
-
last_name,
-
employee_id,
-
job_id,
-
salary
-
FROM employees
-
WHERE salary < (
SELECT
min(salary)
-
FROM employees
-
WHERE job_id =
'IT_PROG')
AND job_id !=
'IT_PROG';
行子查询(结果集一行多列)
示例
查询员工编号最小并且工资最高的员工信息,3种方式。
-
/*查询员工编号最小并且工资最高的员工信息*/
-
/*①查询最小的员工编号*/
-
SELECT
min(employee_id)
-
FROM employees;
-
/*②查询最高工资*/
-
SELECT
max(salary)
-
FROM employees;
-
/*③方式1:查询员工信息*/
-
SELECT *
-
FROM employees a
-
WHERE a.
employee_id = (
SELECT
min(employee_id)
-
FROM employees)
-
AND salary = (
SELECT
max(salary)
-
FROM employees);
-
/*方式2*/
-
SELECT *
-
FROM employees a
-
WHERE (a.
employee_id, a.
salary) = (
SELECT
-
min(employee_id),
-
max(salary)
-
FROM employees);
-
/*方式3*/
-
SELECT *
-
FROM employees a
-
WHERE (a.
employee_id, a.
salary)
in (
SELECT
-
min(employee_id),
-
max(salary)
-
FROM employees);
方式1比较常见,方式2、3更简洁。
exists后面(也叫做相关子查询)
语法:exists(玩转的查询语句)。
exists查询结果:1或0,exists查询的结果用来判断子查询的结果集中是否有值。
一般来说,能用exists的子查询,绝对都能用in代替,所以exists用的少。
和前面的查询不同,这先执行主查询,然后主查询查询的结果,在根据子查询进行过滤,子查询中涉及到主查询中用到的字段,所以叫相关子查询。
示例1
简单示例
-
mysql>
SELECT
exists(
SELECT employee_id
-
FROM employees
-
WHERE salary =
300000)
AS
'exists返回1或者0';
-
+----------------------+
-
| exists返回
1或者
0 |
-
+----------------------+
-
|
0 |
-
+----------------------+
-
1 row
in set (
0.00 sec)
示例2
查询所有员工的部门名称
-
/*exists入门案例*/
-
SELECT
exists(
SELECT employee_id
-
FROM employees
-
WHERE salary =
300000)
AS
'exists返回1或者0';
-
/*查询所有员工部门名*/
-
SELECT department_name
-
FROM departments a
-
WHERE
exists(
SELECT
1
-
FROM employees b
-
WHERE a.
department_id = b.
department_id);
-
/*使用in实现*/
-
SELECT department_name
-
FROM departments a
-
WHERE a.
department_id
IN (
SELECT department_id
-
FROM employees);
示例3
查询没有员工的部门
-
/*查询没有员工的部门*/
-
/*exists实现*/
-
SELECT *
-
FROM departments a
-
WHERE
NOT
exists(
SELECT
1
-
FROM employees b
-
WHERE a.
department_id = b.
department_id
AND b.
department_id
IS
NOT
NULL);
-
/*in的方式*/
-
SELECT *
-
FROM departments a
-
WHERE a.
department_id
NOT
IN (
SELECT department_id
-
FROM employees b
-
WHERE b.
department_id
IS
NOT
NULL);
上面脚本中有
b.department_id IS NOT NULL
,为什么,有大坑,向下看。
NULL的大坑
示例1
使用in的方式查询没有员工的部门,如下:
-
SELECT *
-
FROM departments a
-
WHERE a.
department_id
NOT
IN (
SELECT department_id
-
FROM employees b);
运行结果:
-
mysql>
SELECT *
-
->
FROM departments a
-
->
WHERE a.
department_id
NOT
IN (
SELECT department_id
-
->
FROM employees b);
-
Empty set (
0.00 sec)
in的情况下,子查询中列的值为NULL的时候,外查询的结果为空。
建议:建表是,列不允许为空。
总结
本文中讲解了常见的子查询,请大家务必多练习
注意in、any、some、any的用法
字段为空的时候,in查询有大坑,这个要注意
建议创建表的时候,列不允许为空
Mysql系列目录
mysql系列大概有20多篇,喜欢的请关注一下,欢迎大家加我微信itsoku或者留言交流mysql相关技术!
来源:https://itsoku.blog.csdn.net/article/details/101444328