9. 子查询

子查询指一个查询语句嵌套在另一个查询语句内部的查询。

子查询的基本使用

  • 子查询(内查询)在主查询之前一次执行完成。
  • 子查询的结果被主查询(外查询)使用 。
-- 1.查询工资比'Abel'高的员工姓名和工资
-- 自连接
SELECT e2.last_name,e2.salary
FROM employees e1,employees e2
WHERE e1.last_name = 'Abel'
AND e1.`salary` < e2.`salary`
-- 子查询
SELECT last_name,salary
FROM employees
WHERE salary > (
			  SELECT salary
    	      FROM employees
		      WHERE last_name = 'Abel'
			    );

注意事项

  1. 子查询要包含在括号内
  2. 将子查询放在比较条件的右侧
  3. 单行操作符对应单行子查询,多行操作符对应多行子查询

子查询的分类

分类方式:

  • 按内查询的结果返回一条还是多条记录,将子查询分为单行子查询 、多行子查询
  • 按内查询是否被执行多次,将子查询划分为相关(或关联)子查询不相关(或非关联)子查询

2. 单行子查询

单行比较操作符

操作符 含义
= 相等
> 大于
>= 大于等于
< 小于
<= 小于等于
<> 不等于
-- 'Kimberely'的department_id为NULL,即子查询中的结果为NULL
-- 当使用=操作符时最终结果为NULL
SELECT first_name, department_id
FROM employees
WHERE department_id <=>
(SELECT department_id
FROM employees
WHERE first_name = 'Kimberely');

单行子查询中可以使用<=>对NULL进行判断

HAVING 中的子查询

-- 查询最低工资大于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);

CASE中的子查询

-- 显式员工的employee_id,last_name和location。
-- 其中,若员工department_id与location_id为1800
-- 的department_id相同,则location为’Canada’,其余则为’USA’。
SELECT employee_id, last_name,
(CASE department_id WHEN
(SELECT department_id FROM departments
WHERE location_id = 1800)
THEN 'Canada' ELSE 'USA' END) location
FROM employees;

注意:

  1. 子查询中的空值问题
    子查询不返回任何行

  2. 非法使用子查询
    多行子查询使用单行比较符

3. 多行子查询

  1. 也称为集合比较子查询
  2. 内查询返回多行
  3. 使用多行比较操作符

多行比较操作符

75cfb4e55ef1cd0007f94a228e2c324d.png


-- 查询平均工资最低的部门信息
SELECT *FROM departments
WHERE department_id=(SELECT department_id
					FROM employees
					GROUP BY department_id
					HAVING AVG(salary)=(SELECT AVG(salary) sal
										FROM employees
										GROUP BY department_id
										ORDER BY sal
										LIMIT 1));
-- #方式2:
SELECT department_id
FROM employees
GROUP BY department_id
HAVING AVG(salary) <= ALL (SELECT AVG(salary) avg_sal
							FROM employees
							GROUP BY department_id
)

注意:
空值问题

  • IN中的null是无效的,不能得到对应数据,NULL的情况需要单独判断
  • NOT IN的列表中不能包含NULL否则返回值为空。

4. 相关子查询

如果子查询的执行依赖于外部查询,通常情况下都是因为子查询中的表用到了外部的表,并进行了条件关联,因此每执行一次外部查询,子查询都要重新计算一次,这样的子查询就称之为 关联子查询 。

-- 查询员工中工资大于本部门平均工资的员工的
-- last_name,salary和其department_id
SELECT last_name,salary,department_id
FROM employees e1
WHERE salary>(SELECT AVG(salary)
			  FROM employees e2
			  WHERE e2.`department_id`=e1.`department_id`);

在 FROM 中使用子查询

-- 查询员工中工资大于本部门平均工资的员工的
-- last_name,salary和其department_id
SELECT last_name,salary,e1.department_id
FROM employees e1,(SELECT department_id,
				   AVG(salary) dept_avg_sal FROM employees 
				   GROUP BY department_id) e2
				   WHERE e1.`department_id` = e2.department_id
				   AND e2.dept_avg_sal < e1.`salary`;

在ORDER BY 中使用子查询:

-- 查询员工的id,salary,按照department_name 排序
SELECT employee_id,salary
FROM employees e
ORDER BY (SELECT department_name
		  FROM departments d
		  WHERE e.`department_id` = d.`department_id`);

在SELECT中,除了GROUP BY 和 LIMIT之外,其他位置都可以声明子查询!

EXISTS 与 NOT EXISTS关键字

关联子查询通常也会和 EXISTS操作符一起来使用,用来检查在子查询中是否存在满足条件的行。

  • 如果在子查询中不存在满足条件的行:
    • 条件返回 FALSE
    • 继续在子查询中查找
  • 如果在子查询中存在满足条件的行:
    • 不在子查询中继续查找
    • 条件返回 TRUE
  • NOT EXISTS关键字表示如果不存在某种条件,则返回TRUE,否则返回FALSE。
-- 查询公司管理者的employee_id,last_name,job_id,department_id信息
-- 方式一:自连接
SELECT DISTINCT e1.employee_id, e1.last_name, e1.job_id, e1.department_id
FROM employees e1 JOIN employees e2
WHERE e1.employee_id = e2.manager_id;
-- 方式二:
SELECT employee_id,last_name,job_id,department_id
FROM employees
WHERE employee_id IN (
	SELECT DISTINCT manager_id
	FROM employees
);
-- 方式三:
SELECT employee_id, last_name, job_id, department_id
FROM employees e1
WHERE EXISTS ( SELECT *FROM employees e2
			  WHERE e2.manager_id =e1.employee_id);

相关更新

使用相关子查询依据一个表中的数据更新另一个表的数据。

-- 在employees中增加一个department_name字段,
-- 数据为员工对应的部门名称
# 1ALTER TABLE employees
ADD(department_name VARCHAR2(14));
# 2UPDATE employees e
SET department_name = (SELECT department_name
					   FROM departments d
					   WHERE e.department_id = 
					   d.department_id);

相关删除

使用相关子查询依据一个表中的数据删除另一个表的数据。

-- 删除表employees中,其与emp_history表皆有的数据
DELETE FROM employees e
WHERE employee_id in(SELECT employee_id
					 FROM emp_history
					 WHERE employee_id = e.employee_id);

posted @   LemonPuer  阅读(17)  评论(0编辑  收藏  举报
相关博文:
阅读排行:
· CSnakes vs Python.NET:高效嵌入与灵活互通的跨语言方案对比
· DeepSeek “源神”启动!「GitHub 热点速览」
· 我与微信审核的“相爱相杀”看个人小程序副业
· Plotly.NET 一个为 .NET 打造的强大开源交互式图表库
· 上周热点回顾(2.17-2.23)
点击右上角即可分享
微信分享提示