爱站程序员基地含义：出现在其他语句中的select语句，称为子查询或内查询（外部称为主查询）
分类：
按子查询出现的位置：
select后面：##仅仅支持标量子查询from后面：##支持表子查询where或having后面：##标量子查询、列子查询、行子查询（较少）●●exists后面（相关子查询）：##表子查询
按功能/结果集的行列数不同：
标量子查询（结果集只有一行一列）（单行子查询）列子查询（结果集只有一列多行）（多行子查询）行子查询（结果集有一行多列）表子查询（结果集一般为多行多列）
一、where 或者 having后面
1、标量子查询（单行子查询）
2、列子查询（多行子查询）
3、行子查询（多列单行）
特点：
1）子查询放在小括号内
2）子查询一般放在条件的右侧
3）标量子查询，一般搭配着单行操作符使用：> < >= <= <> =
列子查询，一般搭配着多行操作符使用： in 、ANY/SOME、ALL
4）子查询优先执行
1、标量子查询
where、having后面的条件是跟一个值进行对比
#案例1：谁的工资比Abel高？
SELECT last_nameFROM employeesWHERE salary > (SELECT salaryFROM employeesWHERE last_name = \'Abel\')
#案例2：返回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
SELECT last_name,job_id,salaryFROM employeesWHERE salary = (SELECT MIN(salary)FROM employees)
#案例4：查询最低工资大于50号部门最低工资的部门id和其最低工资
SELECT department_id,MIN(salary)FROM employeesGROUP BY department_idHAVING MIN(salary) > (SELECT MIN(salary)FROM employeesWHERE department_id = 50)
非法使用标量子查询（如果子查询的结果不是一行一列，则会出错）
2、列子查询（多行子查询）
多行操作符使用：
IN/NOT IN 、ANY/SOME、ALL
a > any(10,20,30) = a > min(10,20,30)
a > all(10,20,30) = a > max(10,20,30)
#案例1：返回location_id 是1400或1700的部门中的所有员工姓名
#1）查询哪些部门的location_id是1400，1700
SELECT DISTINCT department_idFROM departments dWHERE location_id IN (1400,1700)
SELECT last_nameFROM employeesWHERE department_id IN (SELECT DISTINCT department_idFROM departments dWHERE location_id IN (1400,1700)) #in 也可以用 = any（。。。）#not in 也可以用 <>all（。。。）
可以用内连接的等值连接
SELECT last_nameFROM employees eINNER JOIN departments dON e.department_id = d.department_idWHERE location_id IN (1400,1700)
#案例2:返回其他工种中，比job_id为“IT_PROG\”工种任意一个员工的工资要低的员工的：工号，姓名，job_id，salary
#1)方法一：先找IT_PROG部门的所有工资列，然后查找比这列所有工资都高的员工
SELECT salaryFROM employeesWHERE job_id = \'it_prog\'
SELECT employee_id,last_name,job_id,salaryFROM employeesWHERE salary < ANY(SELECT salaryFROM employeesWHERE job_id = \'it_prog\')AND job_id != \'it_prog\'
#2）方法二：先找IT_PROG的最高工资，然后通过标量子查询查找
SELECT MAX(salary)FROM employeesWHERE job_id = \'it_prog\'
SELECT employee_id,last_name,job_id,salaryFROM employeesWHERE salary < (SELECT MAX(salary)FROM employeesWHERE job_id = \'it_prog\')AND job_id != \'it_prog\'
#案例三：返回其他工种中，比job_id为“IT_PROG\”工种所有员工的工资要低的员工的：工号，姓名，job_id，salary
SELECT employee_id,last_name,job_id,salaryFROM employeesWHERE salary < ALL(SELECT salaryFROM employeesWHERE job_id = \'it_prog\')AND job_id != \'it_prog\'
3、行子查询（一行多列/多行多列）
#案例1：查询员工编号最小，且工资最高的员工信息(有可能不能同时满足两个条件，则不存在）
#两个条件都是用等号
#1）方法一：
SELECT *FROM employeesWHERE employee_id = (SELECT MIN(employee_id)FROM employees)AND salary = (SELECT MAX(salary)FROM employees)
#2)方法二：行子查询
SELECT *FROM employeesWHERE (employee_id,salary)=(SELECT MIN(employee_id),MAX(salary)FROM employees)
二、select后面
#案例1：查询每个部门的员工个数(返回的是departments表格的全部内容+员工个数）
#方法一：子查询
SELECT d.*,(SELECT COUNT(*)FROM employees eWHERE e.department_id = d.department_id#如果此处加GROUP BY d.department_id 或者e.department_id,#则没有员工的部门返回的是null,# FROM 的是employees表，但是如果 group by ,则在employees表中#没有出现的department_id则为null值#此处没有group by也有分组的结果，因为在表连接where语句中，默认了分组结果) AS 员工个数FROM departments d
#错误语句
SELECT d.*,(SELECT COUNT(*)FROM employees e,departments dWHERE e.department_id = d.department_id) AS 员工个数FROM departments d /*子查询中连接了departments表后，查询出的员工个数结果全部为106，因为此子查询的结果本来就是一个数106(employees表中，一共107行，其中一行无department_id）*/
#方法二：外连接
SELECT d.*,COUNT(e.employee_id)FROM departments dLEFT JOIN employees eON e.department_id = d.department_idGROUP BY d.department_id
#案例2：查询员工号=102的部门名
#方法一：where后面的子查询（标量子查询）
SELECT department_nameFROM departmentsWHERE department_id = (SELECT department_idFROM employeesWHERE employee_id = 102)
#方法二：内连接
SELECT d.department_nameFROM employees eINNER JOIN departments dON e.department_id = d.department_idWHERE e.employee_id = 178 #178这个员工是没有department_id的，此语句什么都没有返回，因为inner join后，不会有其中一个表中没有的数据
# 方法三：外连接
SELECT d.department_nameFROM employees eLEFT JOIN departments dON e.department_id = d.department_idWHERE e.employee_id = 178 #返回的是一个null，证明该员工是没有department_id
三、from后面
#案例：查询每个部门的平均工资的工资等级
（1）查询每个部门的平均工资
SELECT AVG(salary) 平均工资,department_idFROM employeesGROUP BY department_id
（2）连接（1）结果集和job_grades表
方法一：普通非等值连接
SELECT ag.*,g.grade_levelFROM (SELECT AVG(salary) 平均工资,department_idFROM employeesGROUP BY department_id ) ag,job_grades gWHERE 平均工资 BETWEEN g.lowest_sal AND g.highest_sal #此处直接用AVG（salary），不用别名，会报错：Invalid use of group function
方法二：内连接中的非等值连接
SELECT ag.*,g.grade_levelFROM (SELECT AVG(salary) 平均工资,department_idFROM employeesGROUP BY department_id) agINNER JOIN job_grades gON 平均工资 BETWEEN g.lowest_sal AND g.highest_sal
#扩展：查看所有部门的平均工资和工资等级（按照departments表，没有的也显示出来）
#（1）连接departments表和平均工资
SELECT d.*,(SELECT AVG(salary)FROM employees eWHERE d.department_id = e.department_id) agFROM departments d #不用group by
#（2）将（1）的结果集连接grade_level
SELECT ag_dep.*,g.grade_levelFROM (SELECT d.*,(SELECT AVG(salary)FROM employees eWHERE d.department_id = e.department_id) agFROM departments d) ag_depLEFT JOIN job_grades gON ag_dep.ag BETWEEN g.lowest_sal AND g.highest_sal #如果不是用left join，而是用where进行非等值连接，则不会显示没有平均工资的部门信息
四、exists后面（相关子查询）
#1、exists、not exists
#查询的结构是否有值，如果有，则返回1，无则返回0
#案例1：查看
employees
中department_id 是否有空值
SELECT EXISTS(SELECT *FROM employeesWHERE department_id IS NULL)
#扩展：查看
employees
中department_id 有多少空值
SELECT SUM(ISNULL(department_id))FROM employees #isnull(),空值返回1，非空值返回0
SELECT COUNT(employee_id)FROM employeesWHERE department_id IS NULL
#案例2：查询有员工的部门名
#方法一：用in、any
SELECT department_nameFROM departmentsWHERE department_id IN (SELECT DISTINCT department_idFROM employees)
#方法二：用exists
SELECT department_nameFROM departments dWHERE EXISTS(SELECT *FROM employees eWHERE e.department_id = d.department_id)
**exists 的效率比in的高**子查询中，连接了两个表，employees表中有的department_id，才会exists = True**查询过程：查看d表中第一行department_name，然后查看条件：e表中是否有该department_name的department_id，有则返回department_name，无则不返回。然后查看第二行、第三行...
SELECT *FROM departments d,employees eWHERE d.department_id = e.department_id #此语句会出现106行数据（其中一行department_id为null的不显示）
#方法三：等值连接
SELECT d.*,COUNT(*)FROM departments d,employees eWHERE d.department_id = e.department_idGROUP BY d.department_id
五、例题
#1、查询和Zlotkey相同部门的员工姓名和工资
SELECT last_name,salaryFROM employeesWHERE department_id = (SELECT department_idFROM employeesWHERE last_name = \'Zlotkey\')
#2、查询工资比公司平均工资高的员工的员工号，姓名和工资
SELECT employee_id,last_name,salaryFROM employeesWHERE salary >(SELECT AVG(salary)FROM employees)
#3、查询各部门中工资比本部门平均工资高的员工的员工号，姓名和工资
#方法一：等值连接
SELECT employee_id,last_name,e.department_id,salary,agFROM (SELECT AVG(salary) ag,department_idFROM employeesGROUP BY department_id) ag_dep,employees eWHERE e.department_id = ag_dep.department_idAND salary > ag_dep.ag
#方法二：内连接
SELECT employee_id,last_name,e.department_id,salary,agFROM employees eINNER JOIN (SELECT AVG(salary) ag,department_idFROM employeesGROUP BY department_id) ag_depON e.department_id = ag_dep.department_idWHERE salary > ag_dep.ag
#4、查询 和 姓名中包含字母u的员工 在相同部门的员工的员工号和姓名
#(1)哪些部门中有员工的姓名含有字母u
SELECT DISTINCT department_idFROM employeesWHERE last_name LIKE \'%u%\'
#（2）连接两个查询
SELECT employee_id,last_name,department_idFROM employeesWHERE department_id IN(SELECT DISTINCT department_idFROM employeesWHERE last_name LIKE \'%u%\')
#5、查询在部门的location_id为1700的部门工作的员工的员工号
SELECT employee_idFROM employeesWHERE department_id IN (SELECT department_idFROM departmentsWHERE location_id = 1700)
#6、查询管理者是K_ing的员工姓名和工资
SELECT employee_idFROM employeesWHERE last_name = \'K_ing\' #有两个叫king的,100和156
SELECT last_name,salary,manager_idFROM employeesWHERE manager_id = ANY(SELECT employee_idFROM employeesWHERE last_name = \'K_ing\') #不清楚子查询结果是一个值，还是单列多个值的话，直接用in或者any就行
#7、查询工资最高的员工的姓名，要求first_name和last_name显示为一列，列名为姓名
SELECT CONCAT(first_name,\' \',last_name) 姓名FROM employeesWHERE salary = (SELECT MAX(salary)FROM employees)
