本文共 8018 字，大约阅读时间需要 26 分钟。

三、MySQL子查询学习笔记

7：子查询

含义：

一条查询语句中又嵌套了另一条完整的select语句，其中被嵌套的select语句，称为子查询或内查询；在外面的查询语句，称为主查询或外查询 分类： 一、按子查询出现的位置：

1）select后面：

                       仅支持标量子查询

2）from后面：

                       支持表子查询

3）where或having后面：

                       标量子查询（1行1列）                        列子查询（n行1列）                        行子查询（1行n列）

4）exists后面（相关子查询）:

                       表子查询

二、按结果集的行列数不同：

1. 标量子查询（单行子查询）：结果集为1行1列
2. 列子查询（多行子查询）：结果集为n行1列
3. 行子查询：结果集为1行n列
4. 表子查询：结果集为n行n列

特点：

1、子查询都放在小括号内

2、子查询可以放在from后面、select后面、where后面、having后面，但一般放在条件的右侧 3、子查询优先于主查询执行，主查询使用了子查询的执行结果 4、子查询根据查询结果的行数不同分为以下两类：

① 单行子查询：结果集只有一行

一般搭配单行操作符使用：> < = <> >= <= 非法使用子查询的情况： a、子查询的结果为一组值 b、子查询的结果为空

② 多行子查询：结果集有多行

一般搭配多行操作符使用：any、all、in、not in in： 属于子查询结果中的任意一个就行 any和all往往可以用其他查询代替

一、where 或 having后面可以加一下三种子查询：

1. 标量子查询（单行子查询）：1行1列
2. 列子查询（多行子查询）：n行1列
3. 行子查询：1行n列

特点

1. 子查询放在小括号内
2. 子查询一遍放在条件的右侧
   3. 标量子查询，一般会搭配着单行操作符来使用：> < >= <= = <>；
   列子查询，一般搭配着多行操作符来使用：in、any/some、all
   
   注意：
   all 是“任意”，是与所有值比较
   例如：小于 any() 等价于 小于 min()
   any 是“任一个”， 是与其中的某个值比较满足即可。
   例如：小于 any() 等价于 小于 max()
3. 子查询的执行优先于主查询执行，因为 主查询的条件用到了子查询的结果

1.标量子查询

# 1.标量子查询（一行一列）# 【案例1】查询谁的工资比Abel高？# 1）查询Abel的工资SELECT salary FROM employeesWHERE last_name = 'Abel';# 2) 查询员工的信息，满足 salary > 1)的结果SELECT * 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 employeesWHERE employee_id = 141;# 2）查询 员工号为143的员工的工资SELECT salaryFROM employeesWHERE employee_id = 143;# 3)查询员工的姓名，job_id，和工资，要求job_id = 1）并且salary > 2）SELECT last_name , job_id ,salaryFROM employeesWHERE job_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，要求salary = 1)SELECT last_name, job_id,salaryFROM employeesWHERE salary = (						SELECT MIN(salary)						FROM employees)# 【案例4】 查询最低工资大于 50号部门最低工资 的部门id 和其最低工资# 1) 查询50号部门的最低工资SELECT MIN(salary) FROM employeesWHERE department_id = 50;# 2) 查询每个部门的最低工资SELECT department_id , min(salary)FROM employeesGROUP BY department_id;# 3) 在2）的基础上筛选，满足min(salary)>1)  查询department_id > 50 的部门号 和 最低工资SELECT department_id , MIN(salary)FROM employeesGROUP BY department_idHAVING MIN(salary) > (						SELECT MIN(salary) 						FROM employees						WHERE department_id = 50)

2.列子查询

# 2.列子查询（n行1列）# 【案例1】 返回location_id是1400或1700 的部门中的所有员工姓名# 1）查询location_id 是1400或1700的部门编号（多行1列）SELECT DISTINCT department_idFROM departmentsWHERE location_id IN(1400,1700);# 2）查询员工姓名，要求部门号是1）列表中的某一个SELECT last_nameFROM employeesWHERE department_id in(							SELECT DISTINCT department_id							FROM departments							WHERE location_id IN(1400,1700))# 【案例2】 返回其他工种中比 job_id为“IT_PROG” 工种的所有员工的工资 中 "任一" 低的员工的员工号、姓名、job_id和salary# 1) 查询job_id为“IT_PROG” 部门的所有员工的工资 SELECT DISTINCT salaryFROM employeesWHERE job_id = "IT_PROG";# 2) 查询员工的员工号、姓名、job_id和salary，要求salary< 1）中的任一（比其中某一个小就行）一个结果SELECT last_name ,employee_id , job_id , salaryFROM employeesWHERE salary < ANY(							SELECT DISTINCT salary							FROM employees							WHERE job_id = "IT_PROG") AND job_id <> 'IT_PROG';# 2) 或者   小于其中任何一个就行 等价于 小于最大值SELECT last_name ,employee_id , job_id , salaryFROM employeesWHERE salary < (							SELECT max(salary)							FROM employees							WHERE job_id = "IT_PROG") AND job_id <> 'IT_PROG';# 【案例3】 返回其他部门中比job_id为‘IT_PROG’部门所有工资 ”都要“ 低的员工 的员工号、姓名、job_id 和salarySELECT last_name ,employee_id , job_id , salaryFROM employeesWHERE salary < ALL(							SELECT DISTINCT salary							FROM employees							WHERE job_id = "IT_PROG") AND job_id <> 'IT_PROG';# 或者  小于所有（任意）的  等价于  大于最小值SELECT last_name ,employee_id , job_id , salaryFROM employeesWHERE salary < (							SELECT MIN(salary)							FROM employees							WHERE job_id = "IT_PROG") AND job_id <> 'IT_PROG';

3.行子查询（结果集为1行多列 或 多行多列）

# 3.行子查询（结果集为1行多列 或 多行多列）# 【案例1】 查询员工编号最小并且工资最高的员工信息# 先用标量子查询来实现# 1) 查询最小的员工编号SELECT MIN(employee_id)FROM employees;# 2) 查询最高的工资SELECT MAX(salary)FROM employees;# 3) 查询员工的信息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后面（仅仅支持标量子查询）

二、select后面（仅仅支持标量子查询）# 【案例1】 查询每个部门的员工个数SELECT d.* , (			SELECT COUNT(*) 			FROM employees e			WHERE e.department_id = d.department_id) 个数FROM departments d# 【案例2】 查询员工号 = 102 的部门名SELECT (					SELECT department_name					FROM departments d					INNER JOIN employees e					ON e.department_id = d.department_id					WHERE e.employee_id = 102	) 部门名;

三、from后面

注意：将子查询结果充当一张表时，必须要起别名，否则会报错

# 三、from后面# 注意：将子查询结果充当一张表时，必须要起别名，否则会报错# 【案例1】 查询每个部门的平均工资的工资等级# 1) 查询每个部门的平均工资SELECT AVG(salary) , department_idFROM employeesGROUP BY department_idSELECT * FROM job_grades;# 2) 连接1）的结果集 和 job_grades表，筛选条件为 平均工资 between lowest_sal and highest_salSELECT ag_dep.* ,  g.grade_levelFROM (				SELECT AVG(salary) ag , department_id				FROM employees				GROUP BY department_id) ag_depINNER JOIN job_grades gON ag_dep.ag BETWEEN lowest_sal AND highest_sal;

四、exists后面（相关子查询）:只关心查询的东西是否存在，存在返回1，不存在返回0

语法：EXISTS(完整的查询语句) 结果：1或0

# 四、exists后面（相关子查询）:只关心查询的东西是否存在，存在返回1，不存在返回0# 语法：#    EXISTS(完整的查询语句)# 结果：#    1或0SELECT EXISTS (SELECT employee_id FROM employees);SELECT EXISTS (SELECT employee_id FROM employees WHERE salary = 300000);# 【案例1】 查询有员工的部门名# 方法1 , existsSELECT department_nameFROM departments dWHERE EXISTS(							SELECT *							FROM employees e , departments d							WHERE d.department_id = e.department_id);# 方法2 , inSELECT department_nameFROM departments dWHERE d.department_id IN(					SELECT department_id 					FROM employees);# 【案例2】 查询没有女朋友的男神信息USE girls;# 方法一，not in SELECT bo.*FROM boys boWHERE bo.id NOT IN (				SELECT boyfriend_id				FROM beauty);# 方法二 not EXISTSSELECT bo.*FROM boys boWHERE NOT EXISTS(					SELECT boyfriend_id					FROM beauty b					WHERE bo.id = b.boyfriend_id);

子查询练习部分：

# 子查询练习部分：# 1、查询和Zlotkey相同部门的员工姓名和工资use myemployees;# 1）查询Zlotkey的部门idSELECT department_idFROM employeesWHERE last_name = 'Zlotkey';# 2) 查询部门号等于 1）的查询结果的 员工姓名和工资SELECT last_name , salary,department_idFROM employeesWHERE department_id = (					SELECT department_id					FROM employees					WHERE 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)FROM employeesGROUP BY department_id;# 2) 把1)查询的结果 与 employees表连接起来，进行筛选SELECT employee_id , last_name , salary,e.department_idFROM employees eINNER JOIN (		SELECT AVG(salary) ag, department_id		FROM employees 		GROUP BY department_id) ag_depON e.department_id = ag_dep.department_idWHERE salary > ag_dep.ag# 4、查询和 姓名中包含字母 u 的员工 在相同部门的员工的员工号和姓名# 1) 查询姓名中包含字母 u 的员工的部门SELECT DISTINCT last_name,department_idFROM employees eWHERE last_name LIKE '%u%';# 2)查询 部门号 = 1）中任意一个的 员工的员工号和姓名SELECT employee_id ,last_name ,department_id FROM employeesWHERE department_id IN(						SELECT DISTINCT department_id						FROM employees						WHERE last_name LIKE '%u%')# 5、查询在部门的location_id 为 1700的部门工作的员工的员工号# 1) 查询location_id为1700的部门号SELECT DISTINCT department_idFROM departmentsWHERE location_id = 1700;# 2) 查询部门号 = 1) 中任意一个部门号的 员工号SELECT employee_idFROM employeesWHERE department_id = ANY(					SELECT DISTINCT department_id					FROM departments					WHERE location_id = 1700);# 6、查询管理者是K_ing 的员工编号# 1) 查询姓名为King的员工编号SELECT employee_idFROM employeesWHERE last_name = 'K_ing';# 2) 查询哪个员工的manger_id = 1）的查询结果SELECT last_name ,salaryFROM employeesWHERE manager_id IN (					SELECT employee_id					FROM employees					WHERE last_name = 'K_ing');# 7、查询工资最高的员工的姓名，要求first_name 和 last_name显示为一列，列名为 姓.名# 1) 查询最高工资SELECT MAX(salary)FROM employees;# 2) 查询工资 = 1)的姓.名SELECT CONCAT(first_name , last_name) "姓.名"FROM employeesWHERE salary = (					SELECT MAX(salary)					FROM employees);