Mysql数据库基础第二章：（七）子查询
Mysql数据库基础系列
软件下载地址
提取码：7v7u
数据下载地址
提取码：e6p9
mysql数据库基础第一章：（一）数据库基本概念
mysql数据库基础第一章：（二）mysql环境搭建
mysql数据库基础第二章：（一）基础查询
mysql数据库基础第二章：（二）条件查询
mysql数据库基础第二章：（三）排序查询
mysql数据库基础第二章：（四）常见函数
mysql数据库基础第二章：（五）分组查询
mysql数据库基础第二章：（六）连接查询
mysql数据库基础第二章：（七）子查询
mysql数据库基础第二章：（八）子查询经典案例
mysql数据库基础第二章：（九）分页查询
mysql数据库基础第二章：（十）连接查询
mysql数据库基础第三章：DML语言
mysql数据库基础第四章：DDL（数据定义语言）：库表的管理、数据类型与约束条件
mysql数据库基础第五章：（一）事务
mysql数据库基础第五章：（二）视图
mysql数据库基础第六章：变量、存储过程与函数
mysql数据库基础第七章：流程控制结构
mysql数据库基础第八章：窗口函数和公用表达式（CTE）
文章目录
一、子查询基本概念
1.1含义：
出现在其他语句中的select语句，称为子查询或内查询。外部的查询语句，称为主查询或外查询
分类：
按子查询出现的位置：
select后面：
仅仅支持标量子查询
from后面：
支持表子查询
where或having后面：用的较多
标量子查询（单行）
列子查询 （多行）
行子查询
exists后面（相关子查询）
表子查询
按结果集的行列数不同：
标量子查询（结果集只有一行一列）
列子查询（结果集只有一列多行）
行子查询（结果集有一行多列）
表子查询（结果集一般为多行多列）
1.2特点与注意事项：
1.子查询放在括号内
2.子查询放在条件语句的后面
3.标量子查询配合操作符使用 < > = <= >= <>
4.如果是条件子查询，子查询的执行优先于主查询执行，主查询的条件用到了子查询的结果
二、 where或having后面
2.1 标量子查询
标量子查询的子查询结果是一个值，单行单列
因此，筛选条件时，要注意对应关系
#1查询工资比Abel高的员工
SELECT *
FROM employees
WHERE salary>(
SELECT salary
FROM employees
WHERE last_name = 'Abel'
);
#2返回job_id与141号员工相同，salary比143号员工多的员工 姓名，job_id 和工资
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
);
#3查询最低工资大于50号部门最低工资的部门id和其最低工资
先查询50号部门的最低工资，再进行筛选
SELECT MIN(salary),department_id
FROM employees
GROUP BY department_id
HAVING MIN(salary)>(
SELECT MIN(salary)
FROM employees
WHERE department_id = 50
);
2.2 列子查询（多行子查询）
列子查询的结果是多行一列，在进行筛选时要注意对应关系
#返回location_id是1400或1700的部门中的所有员工姓名
先查询location_id是1400或1700所有部门，
SELECT DISTINCT department_id
FROM departments
WHERE location_id IN(1400,1700)
上述返回的结果是一列department_id值
接下来查询员工信息
SELECT last_name
FROM employees
WHERE department_id IN (
SELECT DISTINCT department_id
FROM departments
WHERE location_id IN(1400,1700)
);
#2.返回其它工种中比job_id为‘IT_PROG’工种任一工资低的员工的员工号、姓名、job_id 以及salary
SELECT last_name,employee_id,job_id,salary
FROM employees
WHERE salary<ANY(
SELECT DISTINCT salary
FROM employees
WHERE job_id = 'IT_PROG'
) AND job_id<>'IT_PROG';
#3.返回其它部门中比job_id为‘IT_PROG’部门所有工资都低的员工 的员工号、姓名、job_id 以及salary
SELECT last_name,employee_id,job_id,salary
FROM employees
WHERE salary<(
SELECT MIN( salary)
FROM employees
WHERE job_id = 'IT_PROG'
) AND job_id<>'IT_PROG';
2.3 行子查询（一行多列，或者多行多列）
#1.查询员工编号最小且工资最高的员工信息
SELECT *
FROM employees
WHERE (employee_id,salary)=(
SELECT MIN(employee_id),MAX(salary)
FROM employees
);
三、select后面接子查询
效果类似连接查询，但仅仅支持标量子查询
#1.查询每个部门的员工个数
SELECT d.*,(
SELECT COUNT(*)
FROM employees e
WHERE e.department_id = d.`department_id`
) 个数
FROM departments d;
四、from后面的子查询
将子查询的结果当做 一章表，注意要起别名
#案例：查询每个部门的平均工资的工资等级
SELECT ag_dep.*,g.`grade_level`
FROM (
SELECT AVG(salary) ag,department_id
FROM employees
GROUP BY department_id
) ag_dep
INNER JOIN job_grades g
ON ag_dep.ag BETWEEN lowest_sal AND highest_sal;
五、exists后面（相关子查询）
exists(完整的查询语句)返回结果：1或0
#1.查询有员工的部门名
SELECT department_name
FROM departments d
WHERE EXISTS(
SELECT *
FROM employees e
WHERE d.`department_id`=e.`department_id`
);
有员工的部门名相当于先在employees找到部门编号,再对应到departments表中。因此也可以用where子查询
SELECT department_name
FROM departments d
WHERE d.`department_id` IN(
SELECT department_id
FROM employees
)
六、练习
#1.查询和Zlotkey相同部门的员工姓名和工资
SELECT last_name,salary
FROM employees
WHERE department_id = (
SELECT department_id
FROM employees
WHERE last_name = 'Zlotkey'
)
#2.查询工资比公司平均工资高的员工的员工号，姓名和工资。
SELECT last_name,employee_id,salary
FROM employees
WHERE salary>(
SELECT AVG(salary)
FROM employees
);
#3.查询各部门中工资比本部门平均工资高的员工的员工号, 姓名和工资
SELECT employee_id,last_name,salary,e.department_id
FROM employees e
INNER JOIN (
SELECT AVG(salary) ag,department_id
FROM employees
GROUP BY department_id
) ag_dep
ON e.department_id = ag_dep.department_id
WHERE salary>ag_dep.ag ;
#4. 查询和姓名中包含字母u的员工在相同部门的员工的员工号和姓名
SELECT last_name,employee_id
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 =ANY(
SELECT DISTINCT department_id
FROM departments
WHERE location_id = 1700
);
#6.查询管理者是King的员工姓名和工资
SELECT last_name,salary
FROM employees
WHERE manager_id IN(
SELECT employee_id
FROM employees
WHERE last_name = 'K_ing'
);
#7.查询工资最高的员工的姓名，要求first_name和last_name显示为一列，列名为 姓.名
SELECT CONCAT(first_name,last_name) "姓.名"
FROM employees
WHERE salary=(
SELECT MAX(salary)
FROM employees
);
