目录

介绍

需求分析与问题解决

实际问题

现有解决方式：

方式一：

方式二：自连接

方式三：子查询 

子查询的基本使用

子查询的基本语法结构：

注意事项

子查询的分类

分类方式1：

分类方式2：

单行子查询

单行比较操作符

WHERE中的子查询 

案例1： 结果

案例2    结果

案例3    结果

案例4    结果

HAVING 中的子查询

案例：  结果

CASE中的子查询

        在CASE表达式中使用单列子查询

                实例：结果

子查询中的空值问题

子查询不返回任何行 

非法使用子查询  语句

多行子查询

        多行子查询使用单行比较符

多行子查询的操作符： IN  ANY ALL SOME(同ANY)

In

案例1：返回每组员工中工资最少的的 员工id 和 姓名  结果

ANY / ALL:

体会 ANY 和 ALL 的区别  

ANY案例  结果

ALL案例1  结果 

ALL案例2  结果

注意：MySQL中聚合函数是不能嵌套使用的。 Order中支持聚合函数嵌套

空值问题

注意：结果中含有空值  内查询还有null值 在含有null的情况下 再去判断 结果 就是一个空的情况

结果：返回的结果为空

子查询有两种分类方式：

非相关子查询

相关子查询

相关子查询执行流程

        说明：子查询中使用主查询中的列 

在 FROM 中使用子查询（相关子查询）：

回顾案例：

结果

案例：

结果

在ORDER BY 中使用子查询（相关子查询）：

案例1

结果

案例2

结果

EXISTS 与 NOT EXISTS关键字

EXISTS 案例：

NOT EXISTS案例：

相关更新

相关删除

子查询可以应用的位置

结论：除了GROUP BY 和 LIMIT 之外，其他位置都可以声明子查询！

抛一个思考题？

问题：谁的工资比Abel的高？

        解答：

问题：自连接和子查询有好坏之分吗？

        解答：自连接方式好

介绍

        子查询指一个查询语句嵌套在另一个查询语句内部的查询，这个特性从MySQL 4.1开始引入。

        SQL 中子查询的使用大大增强了 SELECT 查询的能力，因为很多时候查询需要从结果集中获取数据，或者需要从同一个表中先计算得出一个数据结果，然后与这个数据结果（可能是某个标量，也可能是某个集合）进行比较。

需求分析与问题解决

实际问题

现有解决方式：

方式一：

SELECT salaryFROM employeesWHERE last_name = 'Abel';SELECT last_name,salaryFROM employeesWHERE salary > 11000 ; 

方式二：自连接

SELECT e2.last_name,e2.salaryFROM employees e1,employees e2WHERE e1.last_name = 'Abel' #多表的连接条件AND e2.`salary` > e1.`salary`;

方式三：子查询 

SELECT last_name,salaryFROM employeesWHERE salary > (SELECT salaryFROM employeesWHERE last_name = 'Abel'); 

子查询的基本使用

• 子查询的基本语法结构：

• 子查询（内查询）在主查询之前一次执行完成。

• 子查询的结果被主查询（外查询）使用 。

• 注意事项

  • 子查询要包含在括号内

  • 将子查询放在比较条件的右侧

  • 单行操作符对应单行子查询，多行操作符对应多行子查询

子查询的分类

角度1：从内查询返回的结果的条目数
              单行子查询  vs  多行子查询

分类方式1：

        按内查询的结果返回一条还是多条记录，将子查询分为 单行子查询 、 多行子查询 。

• 单行子查询

         

• 多行子查询

           

角度2：内查询是否被执行多次
    相关子查询  vs  不相关子查询

分类方式2：

        按内查询是否被执行多次，分为 相关(或关联)子查询 和 不相关(或非关联)子查询

        子查询从数据表中查询了数据结果，如果这个数据结果只执行一次，然后这个数据结果作为主查询的条件进行执行，那么这样的子查询叫做不相关子查询。

        如果子查询需要执行多次，即采用循环的方式，先从外部查询开始，每次都传入子查询进行查询，然后再将结果反馈给外部，这种嵌套的执行方式就称为相关子查询

 比如：相关子查询的需求：    查询工资大于本部门平均工资的员工信息。（变化）
            不相关子查询的需求：查询工资大于本公司平均工资的员工信息。（固定）

单行子查询

单行比较操作符

操作符	含义
=	equal to
>	greater than
>=	greater than or equal to
<	less than
<=	less than or equal to
	not equal to

WHERE中的子查询 

案例1：

        查询工资大于149号员工工资的员工的信息

#题目：查询工资大于149号员工工资的员工的信息SELECT employee_id,last_name,salaryFROM employeesWHERE salary > (SELECT salaryFROM employeesWHERE employee_id = 149);

结果

案例2 

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

#题目：返回job_id与141号员工相同，salary比143号员工多的员工姓名，job_id和工资SELECT last_name,`job_id`,salaryFROM employeesWHERE job_id = (SELECT job_idFROM employeesWHERE `employee_id` = 141)AND salary > (SELECT salaryFROM employeesWHERE `employee_id` = 143);;

结果

案例3 

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

#题目：返回公司工资最少的员工的last_name,job_id和salarySELECT last_name,job_id,salaryFROM employeesWHERE salary = (SELECT MIN(salary)FROM employees);

结果

案例4

        查询与141号员工的manager_id和department_id相同的其他员工的employee_id，manager_id，department_id。

#题目：查询与141号员工的manager_id和department_id相同的其他员工#的employee_id，manager_id，department_id。#方式1：SELECT employee_id,manager_id,department_idFROM employeesWHERE 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;-----------------------------------------#方式2：了解SELECT employee_id,manager_id,department_idFROM employeesWHERE (manager_id,department_id) = (    SELECT manager_id,department_id     FROM employees    WHERE employee_id = 141   )AND employee_id  141;

结果

HAVING 中的子查询

• 首先执行子查询。
• 向主查询中的HAVING 子句返回结果。

案例：

        查询最低工资大于110号部门最低工资的部门id和其最低工资

#题目：查询最低工资大于110号部门最低工资的部门id和其最低工资SELECT department_id,MIN(salary)FROM employeesWHERE department_id IS NOT NULLGROUP BY department_idHAVING MIN(salary) > (SELECT MIN(salary)FROM employeesWHERE department_id = 110     );

结果

CASE中的子查询

        在CASE表达式中使用单列子查询

实例：

        显式员工的employee_id,last_name和location。其中，若员工department_id与location_id为1800的department_id相同，则location为’Canada’，其余则为’USA’。

#题目：显式员工的employee_id,last_name和location。#其中，若员工department_id与location_id为1800的department_id相同，#则location为’Canada’，其余则为’USA’。SELECT employee_id,last_name,CASE department_id WHEN (SELECT department_idFROM departmentsWHERE location_id =1800      )THEN 'Canada'ELSE 'USA'END"location"FROM employees;

结果

子查询中的空值问题

 子查询中的空值问题SELECT last_name, job_idFROM   employeesWHERE  job_id =  (SELECT job_id   FROM   employees   WHERE  last_name = 'Haas');  # 没有此人,无job_id,无法查询出结果,返回空值

子查询不返回任何行 

非法使用子查询

语句

#非法使用子查询#错误：Subquery returns more than 1 row  多行子查询，应该应用多行子查询的操作符SELECT employee_id, last_nameFROM   employeesWHERE  salary =  (SELECT   MIN(salary)   FROM     employees   GROUP BY department_id);   

多行子查询

        多行子查询使用单行比较符

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

多行子查询的操作符： IN  ANY ALL SOME(同ANY)

操作符	含义
IN	等于列表中的任意一个
ANY	需要和单行比较操作符一起使用，和子查询返回的某一个值比较
ALL	需要和单行比较操作符一起使用，和子查询返回的所有值比较
SOME	实际上是ANY的别名，作用相同，一般常使用ANY

In

案例1：返回每组员工中工资最少的的 员工id 和 姓名

SELECT employee_id, last_nameFROM   employeesWHERE  salary IN  (SELECT   MIN(salary)   FROM     employees   GROUP BY department_id); 

结果

ANY / ALL:

体会 ANY 和 ALL 的区别

        非诚勿扰综艺，五个女生给你亮灯，
        ANY 随便牵手哪一个都可以  只选一个
        ALL 全部都带走

ANY案例

        返回其它 job_id中比 job_id 为 ‘IT_PROG’ 部门任一工资低的员工的员工号、姓名、job_id 以及salary

#题目：返回其它job_id中比job_id为‘IT_PROG’部门任一工资低的员工的员工号、#姓名、job_id 以及salarySELECT employee_id,last_name,job_id,salaryFROM employeesWHERE job_id  'IT_PROG'AND salary < ANY (SELECT salaryFROM employeesWHERE job_id = 'IT_PROG'  ); #  小于 9000 就可以

# 内查询 SELECT salaryFROM employeesWHERE job_id = 'IT_PROG'

结果

ALL案例1

        返回其它 job_id中比 job_id 为 ‘IT_PROG’ 部门所有工资低的员工的员工号、姓名、job_id 以及salary

#题目：返回其它job_id中比job_id为‘IT_PROG’部门所有工资低的员工的员工号、#姓名、job_id 以及salarySELECT employee_id,last_name,job_id,salaryFROM employeesWHERE job_id  'IT_PROG'AND salary < ALL (SELECT salaryFROM employeesWHERE job_id = 'IT_PROG');# 小于 4200  最小工资才可以

结果

ALL案例2

        查询平均工资最低的部门id

        让查询的结果充当一张表，此时列叫 avg_sal 而不是一个函数 MIN(avg_sal)

#题目：查询平均工资最低的部门id# 让查询的结果充当一张表，此时列叫 avg_sal 而不是一个函数 MIN(avg_sal)#MySQL中聚合函数是不能嵌套使用的。 Order中支持聚合函数嵌套#方式1：SELECT department_idFROM employeesGROUP BY department_idHAVING AVG(salary) = (SELECT MIN(avg_sal)FROM(SELECT AVG(salary) avg_salFROM employeesGROUP BY department_id)  t_dept_avg_sal# Every derived table must have its own alias 每一个保留下来的表，起个别名);#方式2：SELECT department_idFROM employeesGROUP BY department_idHAVING AVG(salary) <= ALL( # <= ALL 小于等于所有的 相当于小于最小的 SELECT AVG(salary) avg_salFROM employeesGROUP BY department_id) 

结果

注意：MySQL中聚合函数是不能嵌套使用的。 Order中支持聚合函数嵌套

空值问题

# 空值问题# 结果中含有空值  内查询还有null值 在含有null的情况下 再去判断 结果 就是一个空的情况SELECT last_nameFROM employeesWHERE employee_id NOT IN (SELECT manager_id FROM employees# where manager_id IS NOT NULL); 

注意：结果中含有空值  内查询还有null值 在含有null的情况下 再去判断 结果 就是一个空的情况

结果：返回的结果为空

子查询有两种分类方式：

        方式一:单行子查询和多行子查询

        方式二:相关子查询和非相关子查询

上面的方式（单行子查询和多行子查询）案例都是非相关子查询

非相关子查询

相关子查询

相关子查询执行流程

        如果子查询的执行依赖于外部查询，通常情况下都是因为子查询中的表用到了外部的表，并进行了条件关联，因此每执行一次外部查询，子查询都要重新计算一次，这样的子查询就称之为 关联子查询 。
        相关子查询按照一行接一行的顺序执行，主查询的每一行都执行一次子查询。

        说明：子查询中使用主查询中的列 

在 FROM 中使用子查询（相关子查询）：

回顾案例：

        查询员工中工资大于公司平均工资的员工的last_name,salary和其department_id

#回顾：查询员工中工资大于公司平均工资的员工的last_name,salary和其department_idSELECT last_name,salary,department_idFROM employees    WHERE salary > (    SELECT AVG(salary)    FROM employees)

结果

案例：

        查询员工中工资大于本部门平均工资的员工的last_name,salary和其department_id

#方式1：使用相关子查询SELECT last_name,salary,department_idFROM employees e1WHERE salary > (  SELECT AVG(salary)FROM employees e2 WHERE department_id = e1.department_id # 比较的是那个部门);

#方式2：在FROM中声明子查询SELECT e1.last_name,e1.salary,e1.department_idFROM employees e1,(SELECT department_id,AVG(salary) avg_sal # 需要传入department_id 用于做链接条件FROM employeesGROUP BY department_id) t_dept_avg_salWHERE e1.department_id = t_dept_avg_sal.department_idAND e1.salary > t_dept_avg_sal.avg_sal; # avg_sal 此时是字段 不是函数 必须有别名 

        from型的子查询：子查询是作为from的一部分，子查询要用()引起来，并且要给这个子查询取别名， 把它当成一张“临时的虚拟的表”来使用。

结果

在ORDER BY 中使用子查询（相关子查询）：

案例1

        查询员工的id,salary,按照department_name 排序

#题目：查询员工的id,salary,按照department_name 排序SELECT employee_id,salaryFROM employees eORDER BY ( SELECT department_name FROM departments d WHERE e.`department_id` = d.`department_id`) ASC;

结果

案例2

        若employees表中employee_id与job_history表中employee_id相同的数目不小于2，输出这些相同id的员工的employee_id,last_name和其job_id

SELECT *FROM job_history;SELECT e.employee_id,last_name,e.job_idFROM employees eWHERE 2 <= (      SELECT COUNT(*)      FROM `job_history` j      WHERE j.employee_id = e.employee_id);

结果

EXISTS 与 NOT EXISTS关键字

• 关联子查询通常也会和 EXISTS操作符一起来使用，用来检查在子查询中是否存在满足条件的行。
• 如果在子查询中不存在满足条件的行：
  • 条件返回 FALSE
  • 继续在子查询中查找
• 如果在子查询中存在满足条件的行：
  • 不在子查询中继续查找
  • 条件返回 TRUE
• NOT EXISTS关键字表示如果不存在某种条件，则返回TRUE，否则返回FALSE。

EXISTS 案例：

        查询公司管理者的employee_id，last_name，job_id，department_id信息

#题目：查询公司管理者的employee_id，last_name，job_id，department_id信息#方式1：自连接SELECT DISTINCT mgr.employee_id,mgr.last_name,mgr.job_id,mgr.department_idFROM employees emp JOIN employees mgrON emp.manager_id = mgr.employee_id;#方式2：子查询SELECT employee_id,last_name,job_id,department_idFROM employeesWHERE employee_id IN (SELECT DISTINCT manager_idFROM employees);#方式3：使用EXISTSSELECT employee_id,last_name,job_id,department_idFROM employees e1WHERE EXISTS (SELECT *  # 此时 与 * 还是什么字段 已经不重要了，找到记录就要，没有找到记录就不要了FROM employees e2WHERE e1.`employee_id` = e2.`manager_id` # 相当于一条条数据往下找，找到满足的数据 为 true 就不像下寻找     );

NOT EXISTS案例：

        查询departments表中，不存在于employees表中的部门的department_id和department_name

#方式1： 外连接 相当于 只需要个 一个表 去除 两个表的交集部门SELECT d.department_id,d.department_nameFROM employees e RIGHT JOIN departments dON e.`department_id` = d.`department_id`WHERE e.`department_id` IS NULL;#方式2：SELECT department_id,department_nameFROM departments dWHERE NOT EXISTS (SELECT *FROM employees eWHERE d.`department_id` = e.`department_id`);

相关更新

        使用相关子查询依据一个表中的数据更新另一个表的数据。

# 1）ALTER TABLE employeesADD(department_name VARCHAR2(14));# 2）UPDATE employees eSET department_name = (SELECT department_name           FROM  departments d           WHERE e.department_id = d.department_id);

相关删除

        使用相关子查询依据一个表中的数据删除另一个表的数据。

DELETE FROM employees eWHERE employee_id in      (SELECT employee_id      FROM  emp_history      WHERE employee_id = e.employee_id);

子查询可以应用的位置

SELECT ....,....,....(存在聚合函数)FROM ... (LEFT / RIGHT)JOIN ....ON 多表的连接条件 (LEFT / RIGHT)JOIN ... ON ....WHERE 不包含聚合函数的过滤条件GROUP BY ...,....HAVING 包含聚合函数的过滤条件ORDER BY ....,...(ASC / DESC )LIMIT ...,....

结论：除了GROUP BY 和 LIMIT 之外，其他位置都可以声明子查询！

抛一个思考题？

问题：谁的工资比Abel的高？

解答：

#方式1：自连接SELECT e2.last_name,e2.salaryFROM employees e1,employees e2WHERE e2.`salary` > e1.`salary` #多表的连接条件AND e1.last_name = 'Abel';#方式2：子查询SELECT last_name,salaryFROM employeesWHERE salary > (SELECT salaryFROM employeesWHERE last_name = 'Abel');

问题：自连接和子查询有好坏之分吗？

解答：自连接方式好

        题目中可以使用子查询，也可以使用自连接。一般情况建议你使用自连接，因为在许多 DBMS 的处理过程中，对于自连接的处理速度要比子查询快得多。
        可以这样理解：子查询实际上是通过未知表进行查询后的条件判断，而自连接是通过已知的自身数据表进行条件判断，因此在大部分 DBMS 中都对自连接处理进行了优化。