子查询:出现在其它语句内部的select语句,称为子查询或内查询,就是嵌套的查询结果集
子查询分类
按子查询出现的位置:
- select后面:仅仅支持标量子查询
- from后面:支持表子查询
- where或having后面:标量子查询、列子查询、行子查询
- exists后面(相关子查询)
按结果集的行列数不同:
- 标量子查询:结果集只有一行一列
- 列子查询:结果集只有一列多行
- 行子查询:结果集有一行多列,也可多行多列
- 表子查询:结果集一般为多行多列
特点:
- 子查询放在小括号内
- 子查询一般放在条件的右侧
- 标量子查询一般搭配单行操作符的使用(> < <= >= = <>)
- 列子查询一般搭配多行操作符的使用(in、any、all)
一、where后面子查询
1.标量子查询
案例1:谁的工资比Abel高?
首先需要查询出Abel的工资,查询结果为一个标量集,所以在where后面当一个常量值使用
SELECT
last_name,
salary
FROM
employees
WHERE
salary > (
SELECT
salary
FROM
employees
WHERE
last_name = 'Abel')
案例2:返回job_id与141号员工相同,salary比143号员工多的员工姓名、job_id和工资
首先需要查询出141号的job_id,然后再查询出143号的salary,然后再主查询中进行条件筛选
SELECT last_name, job_id, salary
FROM employees
WHERE job_id = (
SELECT
job_id
FROM
employees
WHERE
employee_id = 141)
AND salary >(
SELECT
salary
FROM
employees
WHERE
employee_id = 143)
案例3:返回公司工资最少的员工的last_name,job_id和salary
SELECT
last_name,
job_id,
salary
FROM
employees
WHERE
salary =(
SELECT
min( salary )
FROM
employees)
案例4:查询最低工资大于50号部门最低工资的部门id和其最低工资
SELECT
department_id,
min( salary )
FROM
employees
GROUP BY
department_id
HAVING
min( salary ) > (
SELECT
MIN( salary )
FROM
employees
WHERE
department_id = 50)
2.列子查询
案例1:返回location_id是1400或1700的部门中的所有员工姓名
首先查询出location_id为1400或1700的部门编号
SELECT
last_name
FROM
employees
WHERE
department_id IN (
SELECT
department_id
FROM
departments
WHERE
location_id IN ( 1400, 1700 )
)
案例2:返回其他工种中比job_id为‘IT_PROG’工种任一工资低的员工的员工号、姓名、job_id以及salary
首先查询出工种为IT_PROG的工资列表,由于是小于任一,也就是小于其中任意一个就行,所以使用any关键字进行筛选
SELECT
employee_id,
last_name,
job_id,
salary
FROM
employees
WHERE
salary < ANY ( SELECT salary FROM employees WHERE job_id = 'IT_PROG' )
AND job_id <> 'IT_PROG';
3.行子查询
案例:查询员工编号最小并且工资最高的员工信息
先把最小编号和最高工资查询出来,是一个行信息,然后用于之后的筛选
SELECT
*
FROM
employees
WHERE
( employee_id, salary ) = ( SELECT min( employee_id ), max( salary ) FROM employees );
二、select后面子查询
案例1:查询每个部门的员工个数及其部门信息
SELECT
d.*,
( SELECT count(*) FROM employees WHERE department_id = d.department_id )
FROM
departments d;
案例2:查询员工号为102的部门名
SELECT
( SELECT department_name FROM departments d JOIN employees e ON d.department_id = e.department_id WHERE e.employee_id = 102 );
三、from后面子查询
案例:查询每个部门的平均工资的工资等级
首先查出每个部门的平均工资,相当于一张新的表,由于是中间数据新表,所以需要起别名,然后再使用sql99的非等值连接即可
SELECT
avg,
grade_level
FROM
( SELECT avg( salary ) avg FROM employees GROUP BY department_id ) a
JOIN job_grades j ON a.avg BETWEEN j.lowest_sal
AND j.highest_sal;
四、exists后面(相关子查询)
exist可以看成一个函数,该函数判断给定结果集中是否有数据,如果有则返回1,否则返回0,就是一个判断结果集是否为空的函数
案例1:查询有员工的部门名
可以看成是双重for循环,首先遍历部门表,拿着部门id去筛选,判断员工表中是否有员工的部门id等于该id,一旦有返回的集合就不为空,该部门名选中,如果没有则返回空。
第二种方式使用in,首先查询出员工表中是否有部门表中的部门id,然后使用in判断部门表的部门id是否在该子查询中
# 方式一
SELECT
department_name
FROM
departments d
WHERE
EXISTS (
SELECT
*
FROM
employees
WHERE
department_id = d.department_id
)
# 方式二
SELECT
department_name
FROM
departments d
WHERE
department_id IN (
SELECT
department_id
FROM
employees
WHERE
department_id = d.department_id)
案例2:查询没有女朋友的男神信息
方式一使用exist,拿着男表中的id去查询女表中,一旦查询出结果,那么则返回1,所以使用notexist,不存在即结果集为空则筛选对该男信息文章来源:https://www.toymoban.com/news/detail-641803.html
方式二使用not in,查询出女表中的所有boyid,然后判断男表的id是否在其中文章来源地址https://www.toymoban.com/news/detail-641803.html
# 方式一
SELECT
*
FROM
boys
WHERE
NOT EXISTS (
SELECT
*
FROM
beauty
WHERE
boyfriend_id = boys.id
)
# 方式二
SELECT
*
FROM
boys
WHERE
id NOT IN ( SELECT boyfriend_id FROM beauty );
到了这里,关于【大数据系列之MySQL】(二十五):MySQL中的子查询的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!