11、子查询

最新推荐文章于 2024-05-23 11:00:28 发布

原创最新推荐文章于 2024-05-23 11:00:28 发布 · 680 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#sql #数据库

MySQL笔记专栏收录该内容

12 篇文章

订阅专栏

文章目录

11、子查询

11、子查询

含义：

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

按子查询出现的位置：
	select后面：
		仅仅支持标量子查询
	from后面：
		支持表子查询
		
	where或having后面： *
		标量子查询（单选）
		列子查询  （多行）
		
		行子查询
		
	exists后面（相关子查询)
		表子查询

按结果集的行列数不同：

	标量子查询（结果集只有一行一列）
	列子查询（结果集只有一列多行）
	行子查询（结果集有一行多列）
	表子查询（结果集一般为多行多列）

11.1、where或having后面

1、标量子查询（单行子查询）
2、列子查询（多行子查询）

3、行子查询（多列多行）

特点：
1、子查询放在小括号内
2、子查询一般放在条件的右侧
3、标量子查询，一般搭配着单行操作符使用
> < >= <= = <>

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

4、子查询的执行文件优先于主查询执行，主查询的条件用到了子查询的结果

11.1、标量子查询

11.1.1、案例1：谁的工资比 Abel 高？

#第一步：查询 Abel的工资
SELECT salary
FROM employees
WHERE last_name = 'Abel';


#第二步：查询员工的信息，满足 salary>第1步的结果
SELECT *
FROM employees
WHERE salary > (
	SELECT salary
	FROM employees
	WHERE last_name = 'Abel'
);

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

#第一步：查询141号员工的job_id
SELECT job_id 
FROM employees
WHERE employee_id = 141


#第二步：查询143号员工的 salary
SELECT salary 
FROM employees
WHERE employee_id = 143


#第三步：查询员工的姓名,job_id和工资，要求 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
);

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

#第一步：查询公司的最低工资
SELECT MIN(salary)
FROM employees

#第二步：查询last_name,job_id和salary，要求salary = 第一步
SELECT last_name , job_id , salary
FROM employees
WHERE salary = (
	SELECT MIN(salary)
	FROM employees
);

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

#第一步：查询50号部门的最低工资
SELECT MIN(salary)
FROM employees
WHERE department_id = 50

#第二步：查询每个部门的最低工资
SELECT MIN(salary),department_id
FROM employees
GROUP BY department_id

#第三步：筛选第二步，满足 min(salary) > 第一步
SELECT MIN(salary),department_id
FROM employees
GROUP BY department_id
HAVING MIN(salary) > (
	SELECT MIN(salary)
	FROM employees
	WHERE department_id = 50
);

11.1.5、非法使用标量子查询(错误)

SELECT MIN(salary),department_id
FROM employees
GROUP BY department_id
HAVING MIN(salary) > (
	#这里出错了，因为返回的结果有多个值，上面只一个
	SELECT salary
	FROM employees
	WHERE department_id = 50
);

11.2、列子查询（多行子查询）

11.2.1、案例1：返回location_id是1400 或1700的部门中的所有员工姓名

#第一步：查询location_id是1400 或1700的部门编号 
SELECT DISTINCT department_id
FROM departments
WHERE location_id IN (1400,1700);


#第二步:查询员工姓名，要求部门号是第一步列表中的某一个

SELECT last_name
FROM employees 
WHERE department_id IN (
	SELECT  DISTINCT department_id
	FROM departments
	WHERE location_id IN (1400,1700)
);

11.2.2、案例2：返回其它工种中比job_id为’IT_PROG’工种一工资低的员工的员工号、姓名、job_id 及salary

#第一步：查询job_id为'IT_PROG'的部门任一工资
SELECT DISTINCT salary
FROM employees
WHERE job_id = 'IT_PROG'

#第二步：查询 员工号、姓名、job_id，以及salary，salary < 第一步的任意一个
SELECT employee_id , last_name,job_id ,salary
FROM employees
WHERE salary < ANY(
	SELECT DISTINCT salary
	FROM employees
	WHERE job_id = 'IT_PROG'
) AND job_id <> 'IT_PROG';

11.2.3、案例3：返回其它工种中比job_id为’IT_PROG’工种所有一工资低的员工的员工号、姓名、job_id 及salary

SELECT last_name,employee_id,job_id,salary
FROM employees
WHERE salary < ALL(
	SELECT DISTINCT salary
	FROM employees
	WHERE job_id = 'IT_PROG'
) AND job_id <> 'IT_PROG';


# 或

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';

11.3、行子查询（结果集一行多行或多行多列）

11.3.1、案例1：查询员工编号最小并且工资最高的员工信息

SELECT *
FROM employees
WHERE (employee_id,salary) = (
	SELECT MIN(employee_id),MAX(salary)
	FROM employees
);


#第一步：查询最小员工编号
SELECT MIN(employee_id)
FROM employees

#第二步：查询员工最高工资
SELECT MAX(salary)
FROM employees

#第三步：查询员工信息
SELECT *
FROM employees
WHERE employee_id = (
	SELECT MIN(employee_id)
	FROM employees
) 
AND salary=(
	SELECT MAX(salary)
	FROM employees
);

11.4、select 后面

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

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

11.4.2、案例2：查询员工号=102号的部门名

SELECT (
	SELECT department_name
	FROM departments d
	INNER JOIN employees e
	ON d.department_id = e.`department_id`
	WHERE e.`employee_id` = 102
) 部门名;

11.5、from 后面

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

11.5.1、案例：查询每个部门的平均工资的工资等级

#第一步：查询每个部门的平均工资
SELECT AVG(salary),department_id
FROM employees
GROUP BY department_id;

SELECT * FROM job_grades;

#第二点步：连接第一步的结果和job_grades表，筛选条件平均工资  BETWEEN lowest_sal , highest_sal
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;

11.6、exists后面（相关子查询）

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

SELECT EXISTS(SELECT employee_id FROM employees); # 存在返回结果1


SELECT EXISTS(SELECT employee_id FROM employees WHERE salary = 11); # 不存在返回结果0

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

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

#exists
SELECT  department_name
FROM departments d
WHERE EXISTS(
	SELECT *
	FROM employees e
	WHERE d.`department_id` = e.`department_id`
	
);

11.6.2、案例2：查询没有女朋友的男神信息

#in
SELECT bo.*
FROM boys bo
WHERE bo.id NOT IN(
	SELECT boyfriend_id 
	FROM beauty
);



#exists
SELECT bo.*
FROM boys bo
WHERE NOT EXISTS (
	SELECT boyfriend_id
	FROM beauty b
	WHERE bo.id = b.`boyfriend_id`
)

11.7、练习题

#   测  试
#1.  查询和 Zlotkey 相同部门的员工姓名和工资
#第一步：查询姓名是 Zlotkey 是部门
SELECT department_id
FROM employees
WHERE last_name = 'Zlotkey';

#第二步：查询部门号=第一步的姓名和工资
SELECT last_name , salary
FROM employees
WHERE department_id = (
	SELECT department_id
	FROM employees
	WHERE last_name = 'Zlotkey'

);

#2.  查询工资比公司平均工资高的员工的员工号，姓名和工资。
#第一步：查询公司平均工资
SELECT AVG(salary)
FROM employees;

#第二步：查询工资 > 第一步的员工名，姓名和工资
SELECT employee_id , last_name,salary
FROM employees
WHERE salary > (
	SELECT AVG(salary)
	FROM employees
);


####*****
#3.  查询各部门中工资比本部门平均工资高的员工的员工号, 姓名和工资
#第一步：查询各部门的平均工资
SELECT AVG(salary),department_id
FROM employees
GROUP BY department_id

#第二步：连接第一步结果集和employees表，进行筛选
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 的员工在相同部门的员工的员工号和姓名

#第一步：查询姓名中包含字母 u 的员工的部门
SELECT DISTINCT department_id
FROM employees
WHERE last_name LIKE '%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 的部门工作的员工的员工号

#第一步：查询 location_id 为 1700的部门
SELECT DISTINCT department_id
FROM departments
WHERE location_id = 1700;

#第二步：查询部门号=第一步中的任意一个员工号
SELECT employee_id
FROM employees
WHERE department_id IN (
	SELECT DISTINCT department_id
	FROM departments
	WHERE location_id = 1700
);

#6. 查询管理者是 King 的员工姓名和工资
#第一步：查询姓名为King的员工编号 
SELECT employee_id
FROM employees
WHERE last_name = 'K_ing';

#第二步：查询哪个员工的manager_id = 第一步的结果 
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 MAX(salary)
FROM employees

#t第二步：查询工资= 第一步的姓名.名
SELECT CONCAT(first_name,last_name) '姓.名'
FROM employees
WHERE salary = (
	SELECT MAX(salary)
	FROM employees
);