sql扩充知识尚硅谷宋红康14高级子查询幻灯片.pptxVIP

第14节高级子查询 讲师：宋红康 新浪微博：尚硅谷-宋红康 目 标 通过本章学习，您将可以: 书写多列子查询 在 FROM 子句中使用子查询 在SQL中使用单列子查询 书写相关子查询 使用 EXISTS 和 NOT EXISTS 操作符 使用子查询更新和删除数据 使用 WITH 子句 子查询 子查询是嵌套在 SQL 语句中的另一个SELECT 语句 SELECT ... FROM ... WHERE ... (SELECT ... FROM ... WHERE ...) 主查询 子查询 子查询 子查询 (内查询) 在主查询执行之前执行 主查询(外查询)使用子查询的结果 SELECT select_list FROM table WHERE expr operator (SELECT select_list FROM table); SELECT last_name FROM employees WHERE salary (SELECT salary FROM employees WHERE employee_id = 149) ; 子查询应用举例 问题：查询工资大于149号员工工资的员工的信息 一、多列子查询 Main query WHERE (MANAGER_ID, DEPARTMENT_ID) IN Subquery 100 90102 60124 50 主查询与子查询返回的多个列进行比较 列比较 多列子查询中的比较分为两种: 成对比较 不成对比较 成对比较举例 SELECT employee_id, manager_id, department_id FROM employees WHERE (manager_id, department_id) IN (SELECT manager_id, department_id FROM employees WHERE employee_id IN (141,174)) AND employee_id NOT IN (141,174); 问题：查询与141号或174号员工的manager_id和department_id相同的其他员工的employee_id, manager_id, department_id 不成对比较举例 SELECT employee_id, manager_id, department_id FROM employees WHERE manager_id IN  (SELECT manager_id FROM employees WHERE employee_id IN (174,141)) AND department_id IN  (SELECT department_id FROM employees WHERE employee_id IN (174,141)) AND employee_id NOT IN(174,141); select last_name,department_id,salary, (select avg(salary)from employees e3 where e1.department_id = e3.department_id group by department_id) avg_salary from employees e1 where salary (select avg(salary) from employees e2 where e1.department_id = e2.department_id group by department_id ) 二、在 FROM 子句中使用子查询 问题：返回比本部门平均工资高的员工的last_name, department_id, salary及平均工资 方法一 SELECT a.last_nam