MySQL学习记录 – 07- 子查询

根据尚硅谷的视频教程学习MySQL，学习记录-07-子查询
所有操作开始之前，建议先打开对应的库：

USE 库名;USE employees;

=> 含义：
出现在其他语句中的SELECT语句，成为子查询或者内查询
外部的查询语句，称为主查询或者外查询

按照子查询出现的位置分类
select后面 – 只支持标量子查询
from后面 – 支持表子查询
❤ where或having后面 – 主要支持标量子查询（单行）、列子查询（多行）
exists后面（相关子查询） – 支持表子查询
按结果集的行列数不同：
标量子查询（结果集只有一行一列）
列子查询（结果集只有一列多行）
行子查询（结果集只有一行多列）
表子查询（结果集一般为多行多列）

一、where 或 having后面

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

案例1：谁的工资比abel的工资高？

#1: 查询abel的工资SELECT salary FROM employees WHERE last_name = \'Abel\'#2: 查询员工信息，其中salary大于结果1SELECT * FROM employeesWHERE salary >(SELECT salary FROM employees WHERE last_name = \'Abel\');

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

#1 查询141号员工的job_idSELECT job_id FROM employees WHERE employee_id = 141; --一行一列#2: 查询143号员工的薪资SELECT salary FROM employees WHERE employee_id=143; --一行一列#3:查询满足job_id等于结果1，salary大于结果2的员工信息SELECT last_name, job_id, salaryFROM employeesWHEREjob_id = (SELECT job_id FROM employees WHERE employee_id = 141)AND salary > (SELECT salary FROM employees WHERE employee_id=143);

案例3：返回公司工资最少的员工last_name, job_id,salary

#1: 查询公司的最低工资SELECT MIN(salary) FROM employees;#2:查询员工last_name, job_id,salary，要求工资等于结果1SELECT last_name, job_id, salaryFROM employeesWHERE  salary = (SELECT MIN(salary) FROM employees);

案例4：查询最低工资大于50号部门最低工资的部门ID和其最低工资

#1 查询50号部门的最低工资SELECT MIN(salary) FROM employees WHERE department_id = 50;#2 查询每个部门的最低工资SELECT MIN(salary), department_id FROM employees GROUP BY department_id;#3 筛选满足 min（salary）大于结果1的信息SELECT MIN(salary), department_idFROM employees GROUP BY department_idHAVING MIN(salary) >(SELECT MIN(salary) FROM employees WHERE department_id = 50)

案例5：【列子查询-多行子查询】返回location_id是1400或1700的部门中的所有员工姓名

#1 查询location_id是1400或1700的部门编号（建议去重）SELECT DISTINCT department_idFROM departments WHERE location_id IN (1400,1700);#2 查询部门号在结果1中的员工姓名SELECT last_name FROM employeesWHERE department_id IN(SELECT DISTINCT department_idFROM departments WHERE location_id IN (1400,1700));

案例6：返回其他工种中比job_id为‘IT PROG’部门任一工资低的员工的员工号，姓名、job_id, salary

#1 查询job_id为 IT PROG的所有工资数据SELECT salary FROM employeesWHERE job_id = \'IT_PROG\'#2 查询员工薪资比结果1任一数据低的员工信息(使用ANY）SELECT employee_id, last_name, job_id, salaryFROM employeesWHERE salary <ALL(SELECT salary FROM employeesWHERE job_id = \'IT_PROG\')AND job_id <> \'IT_PROG\';#或者小于最大值即可满足小于任一值SELECT employee_id, last_name, job_id, salaryFROM employeesWHERE salary <ALL(SELECT MAX(salary) FROM employeesWHERE job_id = \'IT_PROG\')AND job_id <> \'IT_PROG\';

案例7：返回其他工种中比job_id为‘IT PROG’部门所有工资低的员工的员工号，姓名、job_id, salary

#1 查询job_id为 IT PROG的最低工资数据SELECT MIN(salary) FROM employeesWHERE job_id = \'IT_PROG\'#2 查询员工薪资比结果1任一数据低的员工信息(使用ALL）SELECT employee_id, last_name, job_id, salaryFROM employeesWHERE salary <ALL(SELECT salary FROM employeesWHERE job_id = \'IT_PROG\')AND job_id <> \'IT_PROG\';#或者小于最小值SELECT employee_id, last_name, job_id, salaryFROM employeesWHERE salary <ALL(SELECT MIN(salary) FROM employeesWHERE job_id = \'IT_PROG\')AND job_id <> \'IT_PROG\';

案例8：【行子查询-结果集一行多列或多行多列】查询员工编号最小并且工资最高的员工信息

#1 查询最小的员工编号SELECT MIN(employee_id) FROM employees#2 查询最高员工工资SELECT MAX(salary) FROM employees#3 查询员工编号为结果1，并且薪资为结果2的员工信息SELECT * FROM employeesWHERE employee_id = (SELECT MIN(employee_id) FROM employees)AND salary = (SELECT MAX(salary) FROM employees)# 接下来使用行子查询实现，要求多个字段的查询符号一致(此处都是等号)SELECT * FROM employeesWHERE  (employee_id,salary) =( SELECT MIN(employee_id), MAX(salary) FROM employees)

二、select后的子查询

只支持标量子查询

案例1：查询每个部门的员工个数

SELECT d.*,(SELECT COUNT(*) FROM employees eWHERE e.department_id=d.department_id) 个数FROM departments d

案例2：查询员工号 = 102的部门名 (一般可以用其他方式完成，不建议select后子查询)

SELECT(SELECT department_name FROM departments dWHERE e.department_id = d.department_id) as 部门FROM employees eWHERE employee_id = 102

三、from后面

要求将子查询结果充当一张表，要求必须起别名
案例1：查询每个部门的平均工资的工资等级

#1 查询每个部门的平均工资SELECT department_id, AVG(salary) FROM employees GROUP BY department_id;#2 连接结果1和等级表，筛选条件平均工资在MIN(salary)和MAX(salary)之间SELECT ag_dep.*, gradeFROM(SELECT department_id, AVG(salary)  agFROM employeesGROUP BY department_id) ag_depJOIN sal_grade sON ag_dep. ag BETWEEN min_salary AND max_salary

四、exists后面(相关子查询)

exists（完整的查询语句） – 结果是1或0，判断查询语句是否有结果，先执行外部查询，再执行相关子查询

案例1：查询有员工的部门名

SELECT department_name FROM departments dWHERE EXISTS(SELECT *FROM employees eWHERE e.department_id = d.department_id)

五、习题练习

练习1：查询和Zlotkey相同部门的员工姓名和工资

#1 查询Zlotkey所在的部门SELECT department_idFROM employeesWHERE last_name = \'Zlotkey\';#2 查询部门编号为结果1的员工姓名和工资SELECT last_name, salaryFROM employeesWHERE department_id =(SELECT department_idFROM employeesWHERE last_name = \'Zlotkey\');

练习2：查询工资比公司平均工资高的员工的员工号、姓名、工资

#1 查询公司平均工资SELECT AVG(salary) FROM employees#2 查询工资高于结果1的员工信息SELECT employee_id, last_name, salaryFROM employeesWHERE salary >(SELECT AVG(salary) FROM employees);

练习3：查询各部门中工资比本部门平均工资高的员工号、姓名、部门编号、工资

#1 查询各部门的平均工资SELECT department_id, AVG(salary) as agFROM employeesGROUP BY department_id;#2 连接结果1和employees表，进行筛选SELECT employee_id, last_name,e.department_id, salaryFROM employees eJOIN(SELECT department_id, AVG(salary) as agFROM employeesGROUP BY department_id) aeON e.department_id = ae.department_idWHERE e.salary > ae.ag

练习4：查询和姓名中包含字母u的员工在相同部门的员工号和姓名

#1 查询姓名中包含u的员工所在的部门SELECT department_idFROM employeesWHERE last_name LIKE \'%u%\'#2 查询结果1包含的部门中的员工号和姓名，把名字中包含u的员工去除SELECT employee_id, last_nameFROM employeesWHERE department_id IN (SELECT department_idFROM employeesWHERE last_name LIKE \'%u%\')AND last_name NOT LIKE \'%u%\';

练习5：查询在部门的location_id为1700的部门工作的员工的员工号

#1 查询location_id为1700的部门编号SELECT department_id FROM departmentsWHERE location_id = 1700#2 查询结果1的部门的员工号SELECT employee_idFROM employeesWHERE department_id IN (SELECT department_idFROM departmentsWHERE location_id = 1700)

练习6：查询管理者是King的员工姓名和工资

#1 查询King对应的员工号，作为manager_id使用SELECT employee_id FROM employeesWHERE last_name = \'K_ing\'#2 查询manager_id是结果1的员工姓名、薪资SELECT last_name, salaryFROM employeesWHERE manager_id IN (SELECT employee_id FROM employeesWHERE last_name = \'K_ing\');

练习7：查询工资最高的员工的姓名，要求first_name，last_name显示为一列，列名为姓名

#1 查询最高工资SELECT MAX(salary) FROM employees#2 查询工资 = 结果1的员工姓名SELECT CONCAT(first_name, \" \", last_name) as \"姓.名\"FROM employeesWHERE salary = (SELECT MAX(salary) FROM employees);