02-Mysql DQL语言-07子查询

理论

一、含义

嵌套在其他语句内部的select语句可称为子查询或内查询
外面的语句可以是insert、update、delete、select等，一般select作为外面语句较多
外面如果select语句，则语句称为外查询或主查询

二、分类

1、按出现的位置
Select 后面
仅仅支持标量子查询
from后面
表子查询
where或having后面型✨
标量子查询✨
列子查询✨
行子查询

2、按结果集的行列
标量子查询（单行子查询）：结果为一行一列✨
列子查询（多行子查询）：结果为多行一列✨
行子查询：结果集为多行多列
表子查询：结果集为多行多列

三、举例

where和having后面

• 1、标量子查询

案例：查询最低的工资的员工姓名和工资
①最低工资

select min(salary) from employees

②查询员工的姓名和工资，要求工资=①

select last_name ,salary
from employees
where salary = (
Select min(salary) from employees
);

• 2、列子查询

案例：查询所有是领导的员工姓名
#①查询所有员工的manager_id

select manager_id from employees

#查询姓名，employee_id属于①列表的一个

select last_name
from employees
where employee_id in(
select manager_id from employees
);

案例

含义：出现在其他语句中的select语句，成为子查询或内查询 外部的查询语句，成为著查询或外查询 分类： 按子查询出现的位置：
select后面：
仅仅支持标量子查询
from后面
支持表子查询

where或having后面：⭐
    标量子查询 √
    列子查询   √
    
    行子查询
    
exists后面（相关子查询）
    表子查询 按结果集的行列数不同：
变量子查询（结果只有一行一列）
列子查询（结果集只有一列多行）
行子查询（结果集有多行多列）

一、where或having后面

① 子查询放在小括号内
② 子查询一般放在条件的右侧
③ 标量子查询，一般搭配着单行操作符使用 > < >= <= = <> 列子查询，一般搭配着多行操作符使用 in、any/some、all
④ 子查询的执行优先于著查询执行，主查询的条件用到了子查询的结果

#1、标量子查询
#案例1：谁的工资比Abel高？

#①查询Abel的工资
SELECT salary ,last_name
FROM employees
WHERE last_name = 'Abel'
#②查询员工的信息，满足salary >①结果
SELECT *
FROM employees
WHERE salary > (
    SELECT salary 
    FROM employees
    WHERE last_name = 'Abel'
);

#案例2：返回job_id与141号员工相同，salary比143号员工多的员工 姓名， jpb_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 = ①并且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_anme ,jon_id和salary

#①工资最少
SELECT MIN(salary) 
FROM employees
#② 员工信息，要求salary = ①
SELECT last_name , job_id ,salary
FROM employees
WHERE salary = (
    
    SELECT MIN(salary) 
    FROM employees
);

#案例4：查询最低工资大于50号部门最低工资的部门id和其最低工资

#① 50号部门的最低工资
SELECT MIN(salary)
FROM employees
WHERE department_id = 50 
#② 查询每个部门的最低工资
SELECT MIN(salary),department_id
FROM employees
GROUP BY department_id
#③ 筛选②，满足min(salary) > ①
#分组（group by）前用where，分组后用having
SELECT MIN(salary),department_id
FROM employees
GROUP BY department_id
HAVING MIN(salary) >(
    SELECT MIN(salary)
    FROM employees
    WHERE department_id = 50    
);

#非法使用标量子查询

SELECT MIN(salary),department_id
FROM employees
GROUP BY department_id
HAVING MIN(salary) >(
    SELECT salary
    FROM employees
    WHERE department_id = 250   
);

#2、列子查询（多行子查询）

多行操作符 操作符 含义
in/not in • 等于列表中的任意一个
any|some 和子查询返回的某一个值比较
all 和子查询返回的所有值比较

#案例1：返回location_id是1400或1700的部门中的所有员工姓名

#① 查询location_id是1400或1700的部门编号
SELECT location_id,department_id
FROM departments
WHERE location_id IN (1400,1700)
#②查询员工姓名，要求部门号是①列表中的某一个
#----------下面这个有错误------------------
SELECT last_name
FROM employees
WHERE department_id IN(
    SELECT location_id,department_id
    FROM departments
    WHERE location_id IN (1400,1700)
);

#案例2：返回其他工种中比job_id为‘IT_PROG’工种任一工资低 的员工 的员工号、姓名、job_id 以及salary

#①job_id为‘IT_PROG’部门任一工资
SELECT DISTINCT salary
FROM employees 
WHERE job_id = 'IT_PROG'
#② 查询员工号，姓名，job_id 以及salary，salary < (①)的任意一个
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 < ANY (
    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’部门任一工资
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';
SELECT NOW();

#3、行子查询
（结果集一行多列或多行多列,用得少）
#案例：查询员工编号最小并且工资最高的员工信息

SELECT *
FROM employees
WHERE (employee_id,salary) =(
    SELECT MIN(employee_id),MAX(salary)
    FROM employees
);
#①员工编号最小的员工信息
SELECT MIN(employee_id),last_name
FROM employees
#②工资最高的员工
SELECT MAX(salary) ,last_name
FROM employees
#③ 员工信息的基础上找①②
SELECT * 
FROM employees
WHERE employee_id =(
    SELECT MIN(employee_id)
    FROM employees
) AND salary =(
    SELECT MAX(salary)
    FROM employees
)

二、select后面

仅仅支持标量子查询

#案例：查询每个部门的员工个数

SELECT d.*, (
    SELECT COUNT(*)
    FROM employees e
    WHERE e.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 employee_id =102
) 部门名;

三、from后面

将查询结果充当一张表，要求必须起别名

#案例：查询每个部门的平均工资的工资等级

#① 查询每个部门的平均工资
SELECT AVG(salary), department_id
FROM employees
GROUP BY department_id
SELECT * FROM job_grades
#② 连接①的结果集和job_grades表,筛选条件平均工资 betweem lowest_sal and highest_sal
SELECT ag_dep.*, g.grade_level
FROM (
    SELECT AVG(salary) ag, department_id
    FROM employees
    GROUP BY department_id
)ag_dep
INNER JOIN job_grades g
ON ag_dep.ag BETWEEN lower_sal AND highest_sal;

四、exists后面

语法： exists（完整的查询语句）
结果： 1或0

SELECT EXISTS 
(SELECT employee_id FROM employees WHERE salary = 30000);

#案例1：查询有员工的部门名

#in
SELECT department_name
FROM departments d
WHERE d.department_id IN (
    SELECT department_id
    FROM employees
    
)
#或 exists
SELECT department_name
FROM departments d
WHERE EXISTS(
    SELECT *
    FROM employees e
    WHERE d.`department_id` = e.`department_id`
)

#案例2：查询没有女朋友的男神信息

#in
SELECT bo.*
FROM boys bo
WHERE bo.id NOT IN(
    SELECT boyfriend_id
    FROM beauty
)
#exists
SELECT boyName
FROM boys bo
WHERE NOT EXISTS(
    SELECT *
    FROM beauty b
    WHERE bo.id = b.`boyfriend_id`
)