子查询
一、需求分析与问题解决
1.1 实际问题
| |
| |
| |
| SELECT cno FROM j_course WHERE cname='英语'; |
| |
| SELECT |
| j1.sname, |
| j2.score |
| FROM |
| j_student j1 |
| INNER JOIN j_score j2 ON j1.sno = j2.sno |
| WHERE |
| j2.cno = 2; |
| |
| |
| SELECT |
| j1.sname, |
| j2.score |
| FROM |
| j_student j1 |
| INNER JOIN j_score j2 ON j1.sno = j2.sno INNER JOIN j_course j3 ON j2.cno=j3.cno |
| WHERE |
| j3.cname='英语'; |
| |
| |
| SELECT |
| j1.sname, |
| j2.score |
| FROM |
| j_student j1 |
| INNER JOIN j_score j2 ON j1.sno = j2.sno |
| WHERE |
| j2.cno IN (SELECT cno FROM j_course WHERE cname='英语' ); |
| |
| |

1.3 子查询的基本使用
| SELECT select_list |
| FROM table |
| WHERE expr operator ( |
| SELECT select_list |
| FROM table); |
-
子查询(内查询)在主查询之前一次执行完成。
-
子查询的结果被主查询(外查询)使用 。
-
注意事项
1.3 子查询的分类
二、单行子查询
2.1 单行比较操作符
操作符 |
含义 |
= |
equal to |
> |
greater than |
>= |
greater than or equal to |
< |
less than |
<= |
less than or equal to |
<> |
not equal to |
2.2 代码示例
- 查询预估成绩为85分,且出生年份大于1984年的学生信息
| SELECT |
| * |
| FROM |
| j_student |
| WHERE |
| pre_score = ( SELECT pre_score FROM j_student WHERE sbir > '1984-01-01' ); |

- 返回最小预估成绩的学生信息
| SELECT *FROM j_student WHERE pre_score =(SELECT MIN(pre_score) FROM j_student); |
| |

- 查询预估成绩为70或75的学生姓名和性别信息
| |
| SELECT sname,ssex |
| FROM j_student |
| WHERE sname IN (SELECT sname FROM j_student WHERE pre_score IN(70,75)) |
| AND ssex IN (SELECT ssex FROM j_student WHERE pre_score IN(70,75)) ; |
| |
| |
| SELECT sname,ssex |
| FROM j_student |
| WHERE (sname,ssex) IN (SELECT sname,ssex FROM j_student WHERE pre_score IN(70,75)) ; |
| |

2.3 HAVING中的子查询
- 首先执行子查询;
- 向主查询中的HAVING子句返回结果。
| |
| SELECT department_id, MIN(salary) |
| FROM employees |
| WHERE department_id IS NOT NULL |
| GROUP BY department_id |
| HAVING MIN(salary) > |
| (SELECT MIN(salary) |
| FROM employees |
| WHERE department_id = 50); |

2.4 CASE中的子查询
| |
| |
| 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; |

2.5 子查询中的空值问题
| |
| |
| SELECT last_name, job_id |
| FROM employees |
| WHERE job_id = |
| (SELECT job_id |
| FROM employees |
| WHERE last_name = 'Haas'); |
2.6 非法使用子查询
| |
| |
| |
| SELECT employee_id, last_name |
| FROM employees |
| WHERE salary = |
| (SELECT MIN(salary) |
| FROM employees |
| GROUP BY department_id); |

三、多行子查询
- 也称为集合比较子查询
- 内查询返回多行
- 使用多行比较操作符、
3.1 多行比较操作符
操作符 |
含义 |
IN |
等于列表中的任意一个 |
ANY |
需要和单行比较操作符一起使用,和子查询返回的某一个值比较 |
ALL |
需要和单行比较操作符一起使用,和子查询返回的所有值比较 |
SOME |
实际上是ANY的别名,作用相同,一般常使用ANY |
3.2 代码示例
| |
| |
| SELECT employee_id, last_name |
| FROM employees |
| WHERE salary IN |
| (SELECT MIN(salary) |
| FROM employees |
| GROUP BY department_id); |

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

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

| |
| |
| |
| #方式1: |
| SELECT department_id |
| FROM employees |
| GROUP BY department_id |
| HAVING AVG(salary) = ( |
| SELECT MIN(avg_sal) |
| FROM ( |
| SELECT AVG(salary) avg_sal |
| FROM employees |
| GROUP BY department_id |
| ) dept_avg_sal |
| ) |
| |
| #方式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 |
| ) |

3.3 空值问题
| |
| SELECT last_name |
| FROM employees |
| WHERE employee_id NOT IN ( |
| SELECT manager_id |
| FROM employees |
| ); |
| |
3.4 相关子查询
3.4.1 相关子查询执行流程
- 如果子查询的执行依赖于外部查询,通常情况下都是因为子查询中的表用到了外部的表,并进行了条件 关联,因此每执行一次外部查询,子查询都要重新计算一次,这样的子查询就称之为 关联子查询 。 相关子查询按照一行接一行的顺序执行,主查询的每一行都执行一次子查询。

3.4.2 代码示例
- 题目一:查询员工中工资大于本部门平均工资的员工的last_name,salary和其department_id
| |
| SELECT last_name,salary,department_id |
| FROM employees outer |
| WHERE salary > |
| (SELECT AVG(salary) |
| FROM employees |
| WHERE department_id= |
| outer.department_id); |
| |
| |
| |
| |
| |
| SELECT e1.last_name,e1.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; |

- 题目二:查询员工的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之外,其他位置都可以声明子查询!
- 题目三:若employees表中employee_id与job_history表中employee_id相同的数目不小于2,输出这些相同 id的员工的employee_id,last_name和其job_id
| SELECT e.employee_id, last_name,e.job_id |
| FROM employees e |
| WHERE 2 <= (SELECT COUNT(*) |
| FROM job_history |
| WHERE employee_id = e.employee_id); |
| |

3.4.3 EXISTS 与 NOT EXISTS关键字
| |
| |
| |
| 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); |
| |
| |
| 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 department_id,department_name |
| FROM departments d |
| WHERE NOT EXISTS ( |
| SELECT * |
| FROM employees e |
| WHERE e.department_id = d.department_id |
| ); |
| |
| |
| SELECT d.department_id,d.department_name |
| FROM departments d RIGHT JOIN employees e |
| ON d.department_id = e.department_id |
| WHERE e.department_id IS NULL; |

3.4.4 相关更新
- 使用相关子查询依据一个表中的数据更新另一个表的数据。
| UPDATE table1 alias1 |
| SET column = (SELECT expression |
| FROM table2 alias2 |
| WHERE alias1.column = alias2.column); |
| |
| |
| # 1) |
| ALTER TABLE employees |
| ADD(department_name VARCHAR2(14)); |
| |
| # 2) |
| UPDATE employees e |
| SET department_name = ( |
| SELECT department_name |
| FROM departments d |
| WHERE e.department_id = d.department_id |
| ); |
3.4.5 相关删除
- 使用相关子查询依据一个表中的数据删除另一个表的数据。
| DELETE FROM table1 alias1 |
| WHERE column operator ( |
| SELECT expression |
| FROM table2 alias2 |
| WHERE alias1.column = alias2.column |
| ); |
| |
| DELETE FROM employees e |
| WHERE employee_id in ( |
| SELECT employee_id |
| FROM emp_history |
| WHERE employee_id = e.employee_id |
| ); |
| |
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· CSnakes vs Python.NET:高效嵌入与灵活互通的跨语言方案对比
· DeepSeek “源神”启动!「GitHub 热点速览」
· 我与微信审核的“相爱相杀”看个人小程序副业
· Plotly.NET 一个为 .NET 打造的强大开源交互式图表库
· 上周热点回顾(2.17-2.23)