MySQL学习笔记9：子查询(内查询)

简介

本系列（MySQL学习笔记）是我基于B站上SQL播放量第一的MySQL 基础+高级篇- 数据库 -sql -尚硅谷视频所做的笔记，方便大家学习和掌握MySQL。

说明

1.这个系列基本包含了视频中老师讲课的所有内容，包括知识点、案例、部分测试题。
2.所需的配套资料（来自B站评论区）

@黎曼的猜想：
配套资料下载–>公众号公众号 DragonWell 回复：mysql 注意：是公众号！！是公众号！！是公众号，点那个 搜一搜 搜索！！！
不是私人号，是公众号！！！ (可以直接在 搜一搜 搜索 dragonwell) 名称是：DragonWell公众号

3.SQLyog的安装（来自B站评论区）

@江左萌粽猪 链接：https://pan.baidu.com/s/18PDjbqEeDSAjQM0VQye6og 提取码：qjuz

目录

MySQL学习笔记1：数据查询语言（DQL语言）select基本用法
MySQL学习笔记2：where 条件查询
MySQL学习笔记3：order by 排序查询
MySQL学习笔记4：常见函数（一） 单行函数
MySQL学习笔记5：常见函数（二） 分组函数
MySQL学习笔记6：group by 分组查询
MySQL学习笔记7：join 连接查询（SQL 92标准）
MySQL学习笔记8：join 连接查询（SQL 99标准）
MySQL学习笔记9：子查询(内查询)
MySQL学习笔记10：union 联合查询
MySQL学习笔记11：limit 分页查询
MySQL学习笔记12：数据操纵语言（DML语言）insert update delete
MySQL学习笔记13：数据定义语言（DDL语言）create alter drop
MySQL学习笔记14：常见数据类型
MySQL学习笔记15：常见约束
MySQL学习笔记16：事务控制语言（TCL语言）
MySQL学习笔记17：视图
MySQL学习笔记18：存储过程
MySQL学习笔记19：函数
MySQL学习笔记20：流程控制结构

子查询

含义：出现在其他语句中的select语句，称为子查询或内查询
外部的查询语句，称为主查询

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

一.where或having后面

#1.标量子查询（单行子查询）

#2.列子查询（多行子查询）

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

列子查询：一般搭配多行操作符使用
in、any/some、all

1.标量子查询

谁的工资比Abel高？

SELECT *
FROM employees 
WHERE salary>(
	SELECT salary 
	FROM employees 
	WHERE last_name='Abel' # 里面不加分号
);

查询job_id与141号(employee_id)员工相同，salary比143号员工多的员工姓名，job_id,salary
SELECT last_name,job_id,salary
FROM employees 
WHERE job_id=
(
	SELECT job_id 
	FROM employees 
	WHERE employee_id = 141
)
AND salary > (
	SELECT salary
	FROM employees
	WHERE employee_id = 143
);

查询公司工资最少的员工的last_name job_id salary
SELECT last_name,job_id,salary
FROM employees
WHERE salary=
(
	SELECT MIN(salary)
	FROM employees
);

查询最低工资大于50号部门员工最低工资的部门 id和其最低工资
SELECT department_id,MIN(salary)
FROM employees
GROUP BY department_id
HAVING MIN(salary)>
(
	SELECT MIN(salary)
	FROM employees
	WHERE department_id=50
);

非法使用标量子查询
将上个例子中子查询min(salary)改成salary

2.列子查询（多行子查询）

 in/not in(查询语句) 等于列表中的任意一个
 any/some 和子查询返回的某一个值比较
# a>any(10,20,30)可以替换成a>min(10,20,30)
 all 和子查询返回的所有值比较
# a>all(10,20,30)可以替换成a>max(10,20,30)

返回location_id 是1400 或1700的部门中所有员工姓名
SELECT last_name
FROM employees AS e 
LEFT JOIN departments AS d
ON e.`department_id`=d.`department_id`
WHERE location_id IN (1400,1700)
ORDER BY last_name;

SELECT last_name
FROM employees
WHERE department_id !=ALL #in等于any都表示任意一个,not in等于!=all
( 
	SELECT department_id 
	FROM departments
	WHERE location_id IN (1400,1700) 
)
ORDER BY last_name;

返回其他部门中比job_id ='IT PROG' 部门任一工资低的员工的员工号、姓名、job_id、salary
SELECT employee_id,last_name,job_id,salary
FROM employees
WHERE salary < ANY
(
	SELECT  salary  #加DISTINCT结果也一样
	FROM employees
	WHERE job_id = 'IT_PROG'
)
AND job_id != 'IT_PROG';

 返回其他部门中比job_id ='IT PROG' 部门所有工资低的员工的员工号、姓名、job_id、salary
SELECT employee_id,last_name,job_id,salary
FROM employees
WHERE salary < ALL
(
	SELECT  salary  #加DISTINCT结果也一样
	FROM employees
	WHERE job_id = 'IT_PROG'
)
AND job_id != 'IT_PROG';

3.行子查询（多行多列，一行多列）

查询员工编号最小并且工资最高的 员工信息
SELECT * 
FROM employees
WHERE employee_id =
(
	SELECT MIN(employee_id)
	FROM employees
)
AND salary=
(
	SELECT MAX(salary)
	FROM employees
);
# 可以写成
SELECT *
FROM employees
WHERE (employee_id,salary)=
(
	SELECT MIN(employee_id),MAX(salary)
	FROM employees
)

二.放在select后面（用其他方式代替）

仅仅支持标量子查询

查询每个部门的员工个数
SELECT d.*,
(
	SELECT COUNT(*) 
	FROM employees AS e
	WHERE e.`department_id`=d.department_id
) AS 个数
FROM departments AS d;

查询员工号=102的部门名
# 用 连接查询
SELECT department_name
FROM employees AS e
INNER JOIN departments AS d
ON e.`department_id`=d.`department_id`
WHERE e.`employee_id`=102;
# 或者
SELECT 
(
	SELECT department_name
	FROM employees AS e
	INNER JOIN departments AS d
	ON e.`department_id`=d.`department_id`
	WHERE e.`employee_id`=102
) AS 部门名;

三.放在from后面

将子查询结果充当一张表，要求必须起别名

# 查询每个部门的平均工资的工资等级
SELECT ag_dep.*,jg.grade_level
FROM
(
	SELECT AVG(salary) AS ag,department_id
	FROM employees
	GROUP BY department_id
) AS ag_dep
INNER JOIN job_grades AS jg
ON ag_dep.ag BETWEEN jg.`lowest_sal` AND jg.`highest_sal`;


SELECT AVG(salary),department_id,grade_level 
FROM employees AS e
INNER JOIN job_grades AS jg
ON e.`salary` BETWEEN jg.`lowest_sal` AND jg.`highest_sal`
GROUP BY department_id;

四.放在exists后面（相关子查询）

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


SELECT EXISTS(
	SELECT employee_id
	FROM employees
); # 返回1

查询有员工的部门名
SELECT department_name
FROM departments AS d
WHERE EXISTS(
	SELECT last_name
	FROM employees AS e
	WHERE e.`department_id`=d.`department_id`
);  

SELECT department_name
FROM departments AS d
WHERE d.`department_id`
IN 
(
	SELECT department_id 
	FROM employees
);

查询没有女朋友的男神信息

SELECT boys.*
FROM boys
WHERE boys.`id`
NOT IN
(
	SELECT boyfriend_id
	FROM beauty 
);
# 或
SELECT boys.*
FROM boys
WHERE NOT EXISTS
(
	SELECT boyfriend_id
	FROM beauty
	WHERE boys.`id`=beauty.`boyfriend_id`
);

测试题

1.查询和Zlotkey相同部门的员工姓名和工资
SELECT last_name,salary 
FROM employees 
WHERE department_id= 
(
	SELECT department_id 
	FROM employees
	WHERE last_name = 'Zlotkey'
);

2.查询工资比公司平均工资高的员工的员工号、姓名和工资
SELECT employee_id,last_name,salary
FROM employees
WHERE salary>
(
	SELECT AVG(salary)
	FROM employees
);

3.查询各部门中工资比本部门平均工资高的员工号、姓名和工资、部门号
#连接两个表
SELECT employee_id,last_name,salary,e.`department_id`
FROM employees AS e
INNER JOIN 
(
	SELECT AVG(salary) AS a,department_id
	FROM  employees
	GROUP BY department_id
) AS ag_dep
ON ag_dep.department_id = e.`department_id`
WHERE e.`salary`>ag_dep.a;

4.查询和姓名中包含字母u的员工在相同部门的 员工的员工号和姓名
SELECT employee_id,last_name
FROM employees 
WHERE department_id IN
(
	SELECT DISTINCT department_id
	FROM employees
	WHERE last_name LIKE '%u%' 
) ;

5.查询在部门的location_id 为1700的部门工作的 员工的员工号
SELECT employee_id 
FROM employees
WHERE department_id IN 
(
	SELECT department_id 
	FROM departments
	WHERE location_id = 1700
);

6.查询管理者是K_ing的员工姓名和工资
SELECT last_name ,salary
FROM employees
WHERE manager_id IN 
(
	SELECT employee_id
	FROM employees
	WHERE last_name='K_ing'  # K_ing不是一个人，所以有好几个编号
);

7.查询工资最高的员工的姓名，要求first_name和last_name显示为一列 列名为 姓 名
SELECT CONCAT(first_name,' ',last_name) AS 姓名
FROM employees
WHERE salary=
(
	SELECT MAX(salary)
	FROM employees
);