含义：
出现在其他语句中的select语句，称为子查询或内查询，外部的查询语句，称为主查询或外查询
分类：
按子查询出现的位置：
select后面：仅仅支持标量子查询
from后面：支持表子查询
where或having后面：标量子查询（单行）、列子查询 （多行）
exists后面（相关子查询）表子查询

按结果集的行列数不同：
标量子查询（结果集只有一行一列）
列子查询（结果集只有一列多行）
行子查询（结果集有一行多列）
表子查询（结果集一般为多行多列）

where或having后面

1、标量子查询（单行子查询）
2、列子查询（多行子查询）
3、行子查询（多列多行）

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

  1. #1.标量子查询★
  2. #案例1：谁的工资比 Abel 高?
  3. #①查询Abel的工资
  4. SELECT salary
  5. FROM employees
  6. WHERE last_name = 'Abel'
  7. #②查询员工的信息，满足 salary>①结果
  8. SELECT *
  9. FROM employees
  10. WHERE salary>(
  11.     SELECT salary
  12.     FROM employees
  13.     WHERE last_name = 'Abel'
  14. );
  15. #案例2：返回job_id与141号员工相同，salary比143号员工多的员工 姓名，job_id 和工资
  16. #①查询141号员工的job_id
  17. SELECT job_id
  18. FROM employees
  19. WHERE employee_id = 141
  20. #②查询143号员工的salary
  21. SELECT salary
  22. FROM employees
  23. WHERE employee_id = 143
  24. #③查询员工的姓名，job_id 和工资，要求job_id=①并且salary>②
  25. SELECT last_name,job_id,salary
  26. FROM employees
  27. WHERE job_id = (
  28.     SELECT job_id
  29.     FROM employees
  30.     WHERE employee_id = 141
  31. ) AND salary>(
  32.     SELECT salary
  33.     FROM employees
  34.     WHERE employee_id = 143
  35. );
  36. #案例3：返回公司工资最少的员工的last_name,job_id和salary
  37. #①查询公司的 最低工资
  38. SELECT MIN(salary)
  39. FROM employees
  40. #②查询last_name,job_id和salary，要求salary=①
  41. SELECT last_name,job_id,salary
  42. FROM employees
  43. WHERE salary=(
  44.     SELECT MIN(salary)
  45.     FROM employees
  46. );
  47. #案例4：查询最低工资大于50号部门最低工资的部门id和其最低工资
  48. #①查询50号部门的最低工资
  49. SELECT  MIN(salary)
  50. FROM employees
  51. WHERE department_id = 50
  52. #②查询每个部门的最低工资
  53. SELECT MIN(salary),department_id
  54. FROM employees
  55. GROUP BY department_id
  56. #③ 在②基础上筛选，满足min(salary)>①
  57. SELECT MIN(salary),department_id
  58. FROM employees
  59. GROUP BY department_id
  60. HAVING MIN(salary)>(
  61.     SELECT  MIN(salary)
  62.     FROM employees
  63.     WHERE department_id = 50
  64. );
  65. #非法使用标量子查询
  66. SELECT MIN(salary),department_id
  67. FROM employees
  68. GROUP BY department_id
  69. HAVING MIN(salary)>(
  70.     SELECT  salary
  71.     FROM employees
  72.     WHERE department_id = 250
  73. );
  74. #2.列子查询（多行子查询）★
  75. #案例1：返回location_id是1400或1700的部门中的所有员工姓名
  76. #①查询location_id是1400或1700的部门编号
  77. SELECT DISTINCT department_id
  78. FROM departments
  79. WHERE location_id IN(1400,1700)
  80. #②查询员工姓名，要求部门号是①列表中的某一个
  81. SELECT last_name
  82. FROM employees
  83. WHERE department_id  <>ALL(
  84.     SELECT DISTINCT department_id
  85.     FROM departments
  86.     WHERE location_id IN(1400,1700)
  87. );
  88. #案例2：返回其它工种中比job_id为‘IT_PROG’工种任一工资低的员工的员工号、姓名、job_id 以及salary
  89. #①查询job_id为‘IT_PROG’部门任一工资
  90. SELECT DISTINCT salary
  91. FROM employees
  92. WHERE job_id = 'IT_PROG'
  93. #②查询员工号、姓名、job_id 以及salary，salary<(①)的任意一个
  94. SELECT last_name,employee_id,job_id,salary
  95. FROM employees
  96. WHERE salary<ANY(
  97.     SELECT DISTINCT salary
  98.     FROM employees
  99.     WHERE job_id = 'IT_PROG'
  100. ) AND job_id<>'IT_PROG';
  101. #或
  102. SELECT last_name,employee_id,job_id,salary
  103. FROM employees
  104. WHERE salary<(
  105.     SELECT MAX(salary)
  106.     FROM employees
  107.     WHERE job_id = 'IT_PROG'
  108. ) AND job_id<>'IT_PROG';
  109. #案例3：返回其它部门中比job_id为‘IT_PROG’部门所有工资都低的员工   的员工号、姓名、job_id 以及salary
  110. SELECT last_name,employee_id,job_id,salary
  111. FROM employees
  112. WHERE salary<ALL(
  113.     SELECT DISTINCT salary
  114.     FROM employees
  115.     WHERE job_id = 'IT_PROG'
  116. ) AND job_id<>'IT_PROG';
  117. #或
  118. SELECT last_name,employee_id,job_id,salary
  119. FROM employees
  120. WHERE salary<(
  121.     SELECT MIN( salary)
  122.     FROM employees
  123.     WHERE job_id = 'IT_PROG'
  124. ) AND job_id<>'IT_PROG';
  125. #3、行子查询（结果集一行多列或多行多列）
  126. #案例：查询员工编号最小并且工资最高的员工信息
  127. SELECT *  
  128. FROM employees
  129. WHERE (employee_id,salary)=(
  130.     SELECT MIN(employee_id),MAX(salary)
  131.     FROM employees
  132. );
  133. #①查询最小的员工编号
  134. SELECT MIN(employee_id)
  135. FROM employees
  136. #②查询最高工资
  137. SELECT MAX(salary)
  138. FROM employees
  139. #③查询员工信息
  140. SELECT *
  141. FROM employees
  142. WHERE employee_id=(
  143.     SELECT MIN(employee_id)
  144.     FROM employees
  145. )AND salary=(
  146.     SELECT MAX(salary)
  147.     FROM employees
  148. );

select后面

  1. /*
  2. 仅仅支持标量子查询
  3. */
  4. #案例：查询每个部门的员工个数
  5. SELECT d.*,(
  6.     SELECT COUNT(*)
  7.     FROM employees e
  8.     WHERE e.department_id = d.`department_id`
  9.  ) 个数
  10.  FROM departments d;
  13.  #案例2：查询员工号=102的部门名
  15. SELECT (
  16.     SELECT department_name,e.department_id
  17.     FROM departments d
  18.     INNER JOIN employees e
  19.     ON d.department_id=e.department_id
  20.     WHERE e.employee_id=102
  22. ) 部门名;

from后面

  1. /*
  2. 将子查询结果充当一张表，要求必须起别名
  3. */
  4. #案例：查询每个部门的平均工资的工资等级
  5. #①查询每个部门的平均工资
  6. SELECT AVG(salary),department_id
  7. FROM employees
  8. GROUP BY department_id
  9. SELECT * FROM job_grades;
  10. #②连接①的结果集和job_grades表，筛选条件平均工资 between lowest_sal and highest_sal
  11. SELECT  ag_dep.*,g.`grade_level`
  12. FROM (
  13.     SELECT AVG(salary) ag,department_id
  14.     FROM employees
  15.     GROUP BY department_id
  16. ) ag_dep
  17. INNER JOIN job_grades g
  18. ON ag_dep.ag BETWEEN lowest_sal AND highest_sal;

exists后面（相关子查询）

  1. /*
  2. 语法：
  3. exists(完整的查询语句)
  4. 结果：
  5. 1或0
  6. */
  7. SELECT EXISTS(SELECT employee_id FROM employees WHERE salary=300000);
  8. #案例1：查询有员工的部门名
  9. #in
  10. SELECT department_name
  11. FROM departments d
  12. WHERE d.`department_id` IN(
  13.     SELECT department_id
  14.     FROM employees
  15. )
  16. #exists
  17. SELECT department_name
  18. FROM departments d
  19. WHERE EXISTS(
  20.     SELECT *
  21.     FROM employees e
  22.     WHERE d.`department_id`=e.`department_id`
  23. );
  24. #案例2：查询没有女朋友的男神信息
  25. #in
  26. SELECT bo.*
  27. FROM boys bo
  28. WHERE bo.id NOT IN(
  29.     SELECT boyfriend_id
  30.     FROM beauty
  31. )
  32. #exists
  33. SELECT bo.*
  34. FROM boys bo
  35. WHERE NOT EXISTS(
  36.     SELECT boyfriend_id
  37.     FROM beauty b
  38.     WHERE bo.`id`=b.`boyfriend_id`
  39. );