根据尚硅谷的视频教程学习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大于结果1
SELECT * FROM employees
WHERE salary >
(SELECT salary FROM employees WHERE last_name = 'Abel');
例2:返回job_id与141号员工相同,salary比143号员工多的员工 姓名、Job_id 和工资
#1 查询141号员工的job_id
SELECT 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, 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:返回公司工资最少的员工last_name, job_id,salary
#1: 查询公司的最低工资
SELECT MIN(salary) FROM employees;
#2:查询员工last_name, job_id,salary,要求工资等于结果1
SELECT last_name, job_id, salary
FROM employees
WHERE 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_id
FROM employees GROUP BY department_id
HAVING MIN(salary) >
(SELECT MIN(salary) FROM employees WHERE department_id = 50)
案例5:【列子查询-多行子查询】返回location_id是1400或1700的部门中的所有员工姓名
#1 查询location_id是1400或1700的部门编号(建议去重)
SELECT DISTINCT department_id
FROM departments WHERE location_id IN (1400,1700);
#2 查询部门号在结果1中的员工姓名
SELECT last_name FROM employees
WHERE department_id IN
(SELECT DISTINCT department_id
FROM departments WHERE location_id IN (1400,1700));
案例6:返回其他工种中比job_id为‘IT PROG’部门任一工资低的员工的员工号,姓名、job_id, salary
#1 查询job_id为 IT PROG的所有工资数据
SELECT salary FROM employees
WHERE job_id = 'IT_PROG'
#2 查询员工薪资比结果1任一数据低的员工信息(使用ANY)
SELECT employee_id, last_name, job_id, salary
FROM employees
WHERE salary <
ALL(SELECT salary FROM employees
WHERE job_id = 'IT_PROG')
AND job_id <> 'IT_PROG';
#或者小于最大值即可满足小于任一值
SELECT employee_id, last_name, job_id, salary
FROM employees
WHERE salary <
ALL(SELECT MAX(salary) FROM employees
WHERE 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 employees
WHERE job_id = 'IT_PROG'
#2 查询员工薪资比结果1任一数据低的员工信息(使用ALL)
SELECT employee_id, last_name, job_id, salary
FROM employees
WHERE salary <
ALL(SELECT salary FROM employees
WHERE job_id = 'IT_PROG')
AND job_id <> 'IT_PROG';
#或者小于最小值
SELECT employee_id, last_name, job_id, salary
FROM employees
WHERE salary <
ALL(SELECT MIN(salary) FROM employees
WHERE 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 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后的子查询
只支持标量子查询
案例1:查询每个部门的员工个数
SELECT d.*,
(SELECT COUNT(*) FROM employees e
WHERE e.department_id=d.department_id) 个数
FROM departments d
案例2:查询员工号 = 102的部门名 (一般可以用其他方式完成,不建议select后子查询)
SELECT
(SELECT department_name FROM departments d
WHERE e.department_id = d.department_id) as 部门
FROM employees e
WHERE 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.*, grade
FROM
(SELECT department_id, AVG(salary) ag
FROM employees
GROUP BY department_id) ag_dep
JOIN sal_grade s
ON ag_dep. ag BETWEEN min_salary AND max_salary
四、exists后面(相关子查询)
exists(完整的查询语句) – 结果是1或0,判断查询语句是否有结果,先执行外部查询,再执行相关子查询
案例1:查询有员工的部门名
SELECT department_name FROM departments d
WHERE EXISTS(
SELECT *
FROM employees e
WHERE e.department_id = d.department_id)
五、习题练习
练习1:查询和Zlotkey相同部门的员工姓名和工资
#1 查询Zlotkey所在的部门
SELECT department_id
FROM employees
WHERE last_name = 'Zlotkey';
#2 查询部门编号为结果1的员工姓名和工资
SELECT last_name, salary
FROM employees
WHERE 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, salary
FROM employees
WHERE salary >
(SELECT AVG(salary) FROM employees);
练习3:查询各部门中工资比本部门平均工资高的员工号、姓名、部门编号、工资
#1 查询各部门的平均工资
SELECT department_id, AVG(salary) as ag
FROM employees
GROUP BY department_id;
#2 连接结果1和employees表,进行筛选
SELECT employee_id, last_name,e.department_id, salary
FROM employees e
JOIN
(SELECT department_id, AVG(salary) as ag
FROM employees
GROUP BY department_id) ae
ON e.department_id = ae.department_id
WHERE e.salary > ae.ag
练习4:查询和姓名中包含字母u的员工在相同部门的员工号和姓名
#1 查询姓名中包含u的员工所在的部门
SELECT department_id
FROM employees
WHERE last_name LIKE '%u%'
#2 查询结果1包含的部门中的员工号和姓名,把名字中包含u的员工去除
SELECT employee_id, last_name
FROM employees
WHERE department_id IN (
SELECT department_id
FROM employees
WHERE last_name LIKE '%u%')
AND last_name NOT LIKE '%u%';
练习5:查询在部门的location_id为1700的部门工作的员工的员工号
#1 查询location_id为1700的部门编号
SELECT department_id FROM departments
WHERE location_id = 1700
#2 查询结果1的部门的员工号
SELECT employee_id
FROM employees
WHERE department_id IN (
SELECT department_id
FROM departments
WHERE location_id = 1700)
练习6:查询管理者是King的员工姓名和工资
#1 查询King对应的员工号,作为manager_id使用
SELECT employee_id FROM employees
WHERE last_name = 'K_ing'
#2 查询manager_id是结果1的员工姓名、薪资
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显示为一列,列名为姓名
#1 查询最高工资
SELECT MAX(salary) FROM employees
#2 查询工资 = 结果1的员工姓名
SELECT CONCAT(first_name, " ", last_name) as "姓.名"
FROM employees
WHERE salary = (
SELECT MAX(salary) FROM employees);