数据库-进阶7-子查询

进阶7-子查询

# 进阶7：子查询
/*
	含义：
		出现在其他语句中的 select 语句，称为子查询或内查询
		外部的查询语句，称为主查询或外查询
	分类：
		按子查询的位置：
			select 后面
				仅仅支持标量子查询
			from 后面
				支持表子查询
			where 或 having 后面      ⭐
				标量子查询（单行）√
				列子查询（多行）  √
				行子查询(使用较少)
			exists 后面(相关子查询)
				表子查询
		按结果集的行列数不同：
			标量子查询（结果集只有一行一列）
			列子查询（结果集只有一列多行）
			行子查询（结果集有多行多列）
			表子查询（结果集一般为多行多列）
*/
#一、where 或 having 后面
	#1.标量子查询（单行子查询）
	#2.列子查询（多行查询）
	
	#3.行子查询（多行多列查询）
	
	#特点：
		# ① 子查询放在小括号内
		# ② 子查询一般放在条件的右侧
		# ③ 标量子查询 ，一般搭配着单行操作符来使用
			# > < >= <= = <>
		# 列子查询：一般搭配着多行操作符的使用
			# in、any/some、all
		# ④ 子查询的执行优先于主查询的执行，主查询的条件用到了子查询的结果	
			
		#1.标量子查询
			#案例1：谁的工资比 Abel 高
				# ① 查询 Abel 的工资
					SELECT salary
					FROM employees
					WHERE last_name = 'Abel';
				# ② 查询员工的信息，满足 salary > ① 结果
					SELECT e.*
					FROM employees
					WHERE salary > (
						SELECT salary
						FROM employees
						WHERE last_name = 'Abel';
					);
			#案例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
					);
			#案例3：返回公司工资最少的员工的 last_name,job_id 和 salary
				# ① 查询公司的 最低工资
					SELECT MIN(salary)
					FROM employees
				# ② 查询 last_name,job_id,salary 要求 工资 = ①
					SELECT last_name,job_id,salary
					FROM employees
					WHERE salary = (
						SELECT MIN(salary)
						FROM employees
					);
			#案例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
					);
			# 非法使用标量子查询
				SELECT MIN(salary),department_id
				FROM employees
				GROUP BY department_id
				HAVING MIN(salary) > (
					SELECT salary
					FROM employees
					WHERE department_id = 250
				);
		#2.列子查询（多行子查询）
			#案例1：返回 location_id 是 1400 或 1700 的部门中的所有员工姓名
				# ① 查询 location_id 是 1400 或 1700 的部门的部门编号
					SELECT department_id 
					FROM departments
					WHERE department_id IN(1400,1700);
				# ② 查询员工姓名，要求部门号是 ① 列表中的某一个
					SELECT last_name
					FROM employees
					WHERE department_id IN(
						SELECT department_id 
						FROM departments
						WHERE department_id IN(1400,1700);
					);
			# 案例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 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';
					# 或
					SELECT last_name,employee_id,job_id,salary
					FROM employees
					WHERE salary < (
						SELECT MAX(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 < 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';
		#3.行子查询（结果集一行多列或者多行多列）
			#案例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
					);
#二、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 d.department_id = e.department_id
			WHERE e.department_id = 102
		) 部门名;
#三、from 后面
/*
	要求子查询结果充当一张表，要求必须起别名
*/
	#案例：查询每个部门的平均工资的工资等级
		# ① 查询每个部门的平均工资
			SELECT AVG(salary),department_id
			FROM employees
			GROUP BY department_id
		# ② 连接 ① 的结果集和 job_grades 表，筛选条件平均工资 between lowest_sal and 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;
#四、exists 后面(相关子查询)
/*
	语法：
	exists(完整的查询语句)
	结果：
		1或0
*/
	SELECT EXISTS (SELECT employee_id FROM employees WHERE salary = 30000);
	#案例1：查询有员工的部门名
		#exists
		SELECT department_name
		FROM departments d
		WHERE EXISTS(
			SELECT *
			FROM employees e
			WHERE d.department_id =e.department_id
		);
		
		#in
		SELECT department_name
		FROM departments d 
		WHERE d.department_id IN(
			SELECT department_id
			FROM employees 
		);
	#案例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 beauy b
			WHERE bo.id = b.boyfriend_id	
		);
#例题1：查询和 Zlotkey 相同部门的员工姓名和工资
	# ① 查询 Zlotkey 的部门
		SELECT department_id
		FROM employees
		WHERE last_name = 'Zlotkey';
	# ② 查询部门号 = 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 last_name,employee_id,salary
		FROM employees
		WHERE salary > (
			SELECT AVG(salary)
			FROM employees
		);
#例题3：查询各部门中工资比本部门平均工资高的员工的员工号，姓名和工资	
	# ① 查询各部门的平均工资
		SELECT AVG(salary),department_id
		FROM employees
		GROUP BY department_id;
	# ② 连接 ① 结果集和 employees 表，进行筛选
		SELECT last_name,employee_id,salary,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 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 的部门工作的员工的员工号
	# ① 查询 location_id 为 1700 的部门
		SELECT DISTINCT department_id
		FROM departments
		WHERE location_id = 1700
	# ② 查询部门号 = ① 中任意一个的员工号
		SELECT employee_id
		FROM employees
		WHERE department_id i = ANY(
			SELECT DISTINCT department_id
			FROM departments
			WHERE location_id = 1700
		);
#例题6：查询管理者是 King 的员工姓名和工资
	# ① 查询姓名为 king 的员工编号
		SELECT employee_id
		FROM employees
		WHERE last_name = 'King'
	# ② 查询那个员工的 manager_id = ①
		SELECT last_name,salary
		FROM employees
		WHERE manager_id IN(
			SELECT employee_id
			FROM employees
			WHERE last_name = 'King'
		);
#例题7：查询工资最高的员工的姓名，要求 first_name 和 last_name 显示为一列，列名为 姓.名
	# ① 查询最高工资
		SELECT MAX(salary)
		FROM employees
	# ② 查询工资 = ① 的姓.名
		SELECT CONCAT(first_name,last_name) "姓.名"
		FROM employees
		WHERE salary = (
			SELECT MAX(salary)
			FROM employees
		);