MySQL-DQL——子查询

含义：

出现在其他语句内部的select语句，称为子查询或内查询

内部嵌套其他select语句的查询，称为主查询或外查询

分类：

（1）按子查询出现的位置：

                select后面：支持标量子查询

                from后面：支持表子查询

                where或having后面⭐：支持标量子查询√、列子查询√、行子查询

                exists后面（相关子查询）：支持表子查询

（2）按结果集的行列数不同：

                标量子查询（结果集只有一行一列）（单行）

                列子查询（结果集只有一列多行）（多行）

                行子查询（结果集有一行多列）

                表子查询（结果集一般为多行多列）

一、where或having后面的子查询

1、标量子查询（单行子查询）

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

3、行子查询（多行多列）

特点：

（1）子查询都放在小括号（）内

（2）子查询一般放在条件的右侧

（3）标量子查询，一般搭配着单行操作符使用

        单行操作符：>    <    =    <=     >=    <>

        列子查询，一般搭配多行操作符使用

        多行操作符：IN、ANY/SOME、ALL

（4）子查询的执行优先于主查询执行，主查询的条件用到了子查询的结果

1、标量子查询

eg1.谁的工资比Abel高？

SELECT last_name,salary
FROM employees
WHERE salary>(
    SELECT salary
    FROM employees
    WHERE last_name = 'Abel'
    );

eg2.返回job_id与141号员工相同，salary比143号员工多的员工姓名 job_id和工资

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);

#添加分组查询

eg3.返回公司工资最少的员工的last_name,job_id,salary

SELECT last_name,job_id,salary
FROM employees
WHERE salary=(
    SELECT MIN(salary)
    FROM employees
);

eg4.查询最低工资大于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、列子查询（多行子查询）

搭配多行操作符：IN、ANY/SOME、ALL

IN / NOT IN：等于列表中的任意一个，列表中的值为查询语句

ANY / SOME：和子查询返回的某一个值比较，可以替换成MAX（）、MIN（）

ALL：和子查询返回的所有制比较，可以替换MIN（）、MAX（）

eg1.返回location_id是1400或1700的部门中所有员工的姓名 

SELECT last_name
FROM employees
WHERE department_id IN (    
    SELECT DISTINCT  department_id
    FROM departments
    WHERE location_id IN (1400,1700));

IN可替换为=ANY

NOT IN 可替换为<>ANY

eg2.返回其他工种中比job_id为‘IT_PROG’的任一员工工资低的员工的员工号、工资、姓名

SELECT employee_id,last_name,salary
FROM employees
WHERE salary< ANY(
    SELECT DISTINCT salary
    FROM employees
    WHERE job_id='IT_PROG'
)
AND job_id <>'IT_PROG';

eg3.返回其他部门中比job_id为‘IT_PROG’的所有员工工资低的员工的员工号、工资、姓名

SELECT employee_id,last_name,salary
FROM employees
WHERE salary< ALL(
    SELECT DISTINCT salary
    FROM employees
    WHERE job_id='IT_PROG'
)
AND job_id <>'IT_PROG';

3、行子查询（一行多列或多行多列）

eg1.查询员工编号最小且工资最高的员工信息

方法1：

SELECT *
FROM employees
WHERE employee_id=(
    SELECT MIN(employee_id)
    FROM employees
)
AND salary=(
    SELECT MAX(salary)
    FROM employees
)

方法2：行子查询

SELECT *
FROM employees
WHERE (employee_id,salary)=(
    SELECT MIN(employee_id),MAX(salary)
    FROM employees
);

二、select后面的子查询

只支持标量子查询

1、标量子查询

eg1.查询每个部门的员工个数

SELECT d.*,(
    SELECT COUNT(*) 
    FROM employees AS e
    WHERE e.department_id=d.`department_id`
) AS 个数
FROM departments AS d;

eg2.查询员工号为102的部门名

SELECT(
    SELECT department_name
    FROM departments AS d
    INNER JOIN employees AS e
    ON e.`department_id`=d.`department_id`
    WHERE e.`employee_id`=102
) AS 部门名;

三、from后面的子查询

支持表子查询

eg1.查询每个部门的平均工资的工资等级

SELECT ag_dep.*,j.`grade_level`
FROM(
    SELECT AVG(salary) AS ag,department_id
    FROM employees AS e
    GROUP BY department_id
)AS ag_dep
INNER JOIN job_grades AS j
ON ag_dep.ag BETWEEN `lowest_sal` AND `highest_sal`;

#将子查询结果充当一张表，新的查询子表必须起别名

四、exists后面的子查询（相关子查询）

exists（完整的查询语句）：查询有没有值，类似布尔型

结果：1或0

例：

SELECT EXISTS(
SELECT employee_id FROM employees
);

eg1.查询有员工的部门名

select department_name
from departments as d
where exists(
    select *
    from employees as e
    where d.`department_id`=e.`department_id`
);

或：

SELECT department_name
FROM departments AS d
WHERE department_id IN (
    SELECT department_id
    FROM employees 
);

#可以使用exists的都可以使用 IN 代替

eg2.查询没有男朋友的女生信息

SELECT b.*
FROM beauty AS b
WHERE NOT EXISTS(
    SELECT id
    FROM boys AS bo
    WHERE b.`boyfriend_id`=bo.`id`
);

或：

SELECT b.*
FROM beauty AS b
WHERE b.`boyfriend_id` NOT IN(
    SELECT id
    FROM boys AS bo
)