MySQL学习笔记（第五部分）

6.4、子查询

• 子查询指一个查询语句嵌套在另一个查询语句内部的查询

6.4.1、根据实际需求分析与问题解决

• 从案例出发：Abel的工资是多少？谁的工资比Abel高？

#现有解决方式：
SELECT salary
FROM employees
WHERE last_name ='Abel'; #Abel的工资是多少

SELECT salary,last_name
FROM employees
WHERE salary>11000; #谁的工资比Abel高,这块思考一个问题，这个需求需要用到两条语句才能完成，显然还可以继续优化，所以高效率诞生

#将两条SELECT语句优化成一条的写法：
SELECT E2.last_name,E2.salary
FROM employees E1,employees E2
WHERE E2.salary > E1.salary
AND E1.last_name = 'Abel'; #但很快我们发现一个问题，就是并不是所有的多表查询都能解决所有的问题，那么，抛出问题并解决

#解决方式三：
SELECT salary,last_name
FROM employees
WHERE salary>(
            SELECT salary
            FROM employees
            WHERE last_name ='Abel'
    );                              #：这就是子查询，外面的查询语句叫外查询，里面的查询语句叫内查询，该缩进缩进增加可读性

6.4.2、子查询的基本语法结构

• 子查询（内查询）在主查询之前一次执行完成。
• 子查询的结果被主查询（外查询）使用 。
  • 注意事项：
  • 子查询要包含在括号内
  • 将子查询放在比较条件的右侧
  • 单行操作符对应单行子查询，多行操作符对应多行子查询

6.4.3、子查询的分类：

• 子查询的分类又可以通过不同角度去分：
  • 角度一：从内查询返回的结果的条目数，从这个结果分为单行子查询 AND 多行子查询
  • 角度二：从内查询是否被执行多次，从这个角度分为相关子查询AND不相关子查询
    • 以上的需求是不相关子查询，这块举个例子直观感受一下相关子查询，需求：查询工资大于本部门的平均工资的员工信息
    • 即每个部门的平均工资都跟公司的平均工资不同，子查询语句每次查询的语句都跟本部门平均数相关，这就是相关子查询；
    • 对应的不相关子查询的需求：查询工资大于本公司平均工资的员工信息
    • 即公司的平均工资是固定的，每次查询的语句都与公司的平均数不相关，这块就是不相关子查询；

6.4.4、单行子查询

• 单行子查询操作符： = ! = > >= < <=

• 子查询的技巧：1、从外往里写；2、从里往外写

• 话不多说，通过练习案例来学习单行子查询

  • 题目：查询工资大于149号员工工资的员工的信息

#题解思路：以上需求是两个查询需求，数据量大我们把它变成外查询，需要和数据一一对比的我们把它变成内查询
SELECT employee_id,salary
FROM employees
WHERE salary>(
            SELECT salary
            FROM employees
            WHERE employee_id = 149
            );                        #即这块的内查询是一个不确定的数，所有外查询过滤条件中写的是需要查询的具体条件

• 题目二：查询job_id 与141号员工相同， salary比143号员工多的员工姓名， job_id 和工资

SELECT salary,last_name,job_id
FROM employees
WHERE job_id = (
                SELECT job_id
                FROM employees
                WHERE employee_id=141
                )
AND salary>(
                SELECT salary
                FROM employees
                WHERE employee_id = 143
            );

• 题目三：返回工资最少的员工的 last_name, job_id ，和salary

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

• 题目四：查询与141号员工的 manger_id 和 department_id 相同的其他员工的 employee_id ,manager_id, department_id

SELECT employee_id,manager_id,department_id
FROM employees
WHERE manager_id =(
                    SELECT manager_id
                    FROM employees
                    WHERE employee_id =141
                    )
AND department_id = (
                    SELECT department_id
                    FROM employees
                    WHERE employee_id =141
                    )
AND employee_id <> 141;

• 题目五：查询最低工资大于50号部门最低工资的部门id 和其最低工资

SELECT department_id,MIN(salary)
FROM employees
GROUP BY department_id
WHERE department_id IS NOT NULL
HAVING MIN(salary) > (
                    SELECT MIN(salary)
                    FROM employees
                    WHERE department_id = 50
                    );

6.5、多行子查询

• 也称为集合比较子查询
• 内查询返回多行
• 使用多行比较操作符

6.5.1、多行子查询的操作符

• IN ANY ALL SOME(同ANY)

6.5.2、通过案例需求来体现操作符

• 题目一 ：公司里哪些员工的工资恰好等于各个部门的最低工资（IN操作符的使用）

SELECT employee_id,salary
FROM employees
WHERE salary IN(
                SELECT MIN(salary)
                FROM employees
                GROUP BY department_id
                );                     #即这块用了多行子查询的操作符IN来和内查询的数据进行一一比对

• 题目二：返回其他 job_id中比 job_id 为 'IT_PROG' 部门担任一工资低的员工的员工号、姓名 job_id 以及salary

SELECT salary,job_id,department_id,last_name
FROM employees
WHERE job_id <> 'IT_PROG'
AND salary < ANY (
                    SELECT salary
                    FROM employees
                    WHERE job_id = 'IT_PROG'
                ); #即这块查询使用了多行查询操作符ANY来和内查询的数据进行比对，满足任一即可

• 题目三：返回其他 job_id中比 job_id 为 'IT_PROG' 部门担所有一工资低的员工的员工号、姓名 job_id 以及salary

SELECT department_id,last_name,job_id,salary
FROM employees
WHERE job_id <> 'IT_PROG'
AND salary < ALL(
                SELECT salary
                FROM employees
                WHERE job_id ='IT_PROG'
                ); #即这块查询使用了多行操作符ALL来和内查询的所有数据进行比对，满足所有条件的数据返回

• 题目四：查询平均工资最低的部门id

SELECT department_id
FROM employees
GROUP BY department_id
HAVING AVG(salary)=(
                    SELECT MIN(avg_sal)
                    FROM(
                            SELECT AVG(salary) AS avg_sal
                            FROM employees
                            GROUP BY department_id
                        ) d_avg_sal
                    );
  #解题思路：不能直接在聚合函数外嵌套聚合函数，但是可以使用别名把聚合查询的结果看成是一张表就可以再次嵌套
 #简化写法：
 SELECT department_id
FROM employees
GROUP BY department_id
HAVING AVG(salary)<=ALL (
                        SELECT AVG(salary) AS avg_sal
                        FROM employees
                        GROUP BY department_id
                        );

6.6、相关子查询

• 相关子查询执行流程

• 也是一样通过案例来体现：题目：查询员工中工资大于本部门平均工资的员工的 last _name ,salary 和其department_id

SELECT last_name,last_name,salary,department_id
FROM employees E1
WHERE salary> (
                    SELECT AVG(salary)
                    FROM employees E2
                    WHERE E1.department_id = E2.department_id
                ); #即使用外部表的一个变量作为更新数据去连接内表，每一次查询变量都会更新，这就是相关子查询

• 在ORDER BY关键字中去使用子查询：题目：查询员工的 id，salary，按照department_name 排序

SELECT employee_id,salary
FROM employees E
ORDER BY (
            SELECT department_name
            FROM departments D
            WHERE D.department_id = E.department_id
             );

• 得出结论：在SELECT语句当中除了GROUP BY 和 LIMIT之外，其他位置都可以声明子查询
• 再来一题：若 employees表中employee_id 与 job_history 表中 employee_id 相同的数目不小于2，输出这些相同id员工的 employee _id ,last_name 和其 job_id;

SELECT employee_id,last_name,job_id
FROM employees E
WHERE 2 <= (
            SELECT COUNT(*)
            FROM job_history J
            WHERE E.employee_id = J.employee_id
            );

6.6.1、EXISTS与NOT EXISTS关键字

• EXISTS关键字的用法：

• 也是通过题目案例来举例：题目：查询公司管理者的employee_id ，last_name，job_id，department_id信息

#方式一：使用连接完成，由于表中有重复字段所以要进行去重操作
SELECT DISTINCT G.employee_id,G.last_name,G.last_name,G.job_id,G.department_id
FROM employees E JOIN employees G
ON E.manager_id = G.employee_id;

#方式二：使用子查询完成
SELECT employee_id,last_name,job_id,department_id
FROM employees
WHERE employee_id IN(
                    SELECT DISTINCT manager_id
                    FROM employees
                    );

#方式三：使用EXISTS关键字完成
SELECT employee_id,last_name,job_id,department_id
FROM employees E
WHERE EXISTS(
            SELECT *
            FROM employees G
            WHERE E.employee_id = G.manager_id
              );

• NOT EXISTS关键字的用法：
• 同样通过案例来举例：题目：查询departments表中，不存在于employees表中的部门的department_id 和 department_name

#方式一：使用外连接并且过滤掉共同部分
SELECT D.department_name,D.department_id
FROM departments D LEFT JOIN employees E
ON D.department_id = E.department_id
WHERE E.department_id IS NULL;

#方式二：
SELECT department_id,department_name
FROM departments D
WHERE NOT EXISTS(
                SELECT *
                FROM employees E
                WHERE D.department_id = E.department_id
                  ); #使用EXISTS关键字过滤后的数据就是有员工的部门，在关键字前加上NOT即可满足案例的需求