• 1. 高级子查询
  • 2. 目标通过本章学习,您将可以: 书写多列子查询 子查询对空值的处理 在 FROM 子句中使用子查询 在SQL中使用单列子查询 相关子查询 书写相关子查询 使用子查询更新和删除数据 使用 EXISTS 和 NOT EXISTS 操作符 使用 WITH 子句
  • 3. 子查询子查询是嵌套在 SQL 语句中的另一个SELECT 语句SELECT ... FROM ... WHERE ...(SELECT ... FROM ... WHERE ...)主查询子查询
  • 4. 子查询子查询 (内查询) 在主查询执行之前执行 主查询使用子查询的结果 (外查询)SELECT select_list FROM table WHERE expr operator (SELECT select_list FROM table);
  • 5. SELECT last_name FROM employees WHERE salary > (SELECT salary FROM employees WHERE employee_id = 149) ;子查询应用举例10500
  • 6. 多列子查询Main queryWHERE (MANAGER_ID, DEPARTMENT_ID) INSubquery100 90 102 60 124 50主查询与子查询返回的多个列进行比较
  • 7. 列比较多列子查询中的比较分为两种: 成对比较 不成对比较
  • 8. 成对比较举例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 (178,174)) AND employee_id NOT IN (178,174);
  • 9. 不成对比较举例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);
  • 10. SELECT a.last_name, a.salary, a.department_id, b.salavg FROM employees a, (SELECT department_id, AVG(salary) salavg FROM employees GROUP BY department_id) b WHERE a.department_id = b.department_id AND a.salary > b.salavg;在 FROM 子句中使用子查询
  • 11. 单列子查询表达式单列子查询表达式是在一行中只返回一列的子查询 Oracle8i 只在下列情况下可以使用, 例如: SELECT 语句 (FROM 和 WHERE 子句) INSERT 语句中的VALUES列表中 Oracle9i中单列子查询表达式可在下列情况下使用: DECODE 和 CASE SELECT 中除 GROUP BY 子句以外的所有子句中
  • 12. 单列子查询应用举例在 CASE 表达式中使用单列子查询SELECT employee_id, last_name, (CASE WHEN department_id = THEN 'Canada' ELSE 'USA' END) location FROM employees; (SELECT department_id FROM departments WHERE location_id = 1800)在 ORDER BY 子句中使用单列子查询 SELECT employee_id, last_name FROM employees e ORDER BY 20(SELECT department_name FROM departments d WHERE e.department_id = d.department_id);
  • 13. 相关子查询 相关子查询按照一行接一行的顺序执行,主查询的每一行都执行一次子查询GET 从主查询中获取候选列EXECUTE 子查询使用主查询的数据USE 如果满足内查询的条件则返回该行
  • 14. 相关子查询 SELECT column1, column2, ... FROM table1 WHERE column1 operator (SELECT colum1, column2 FROM table2 WHERE expr1 = .expr2); 子查询中使用主查询中的列outerouter
  • 15. SELECT last_name, salary, department_id FROM employees outer WHERE salary >相关子查询举例 (SELECT AVG(salary) FROM employees WHERE department_id = outer.department_id) ;
  • 16. 相关子查询举例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);
  • 17. EXISTS 操作符EXISTS 操作符检查在子查询中是否存在满足条件的行 如果在子查询中存在满足条件的行: 不在子查询中继续查找 条件返回 TRUE 如果在子查询中不存在满足条件的行: 条件返回 FALSE 继续在子查询中查找
  • 18. SELECT employee_id, last_name, job_id, department_id FROM employees outer WHERE EXISTS ( SELECT 'X' FROM employees WHERE manager_id = outer.employee_id);EXISTS 操作符应用举例
  • 19. SELECT department_id, department_name FROM departments d WHERE NOT EXISTS (SELECT 'X' FROM employees WHERE department_id = d.department_id);NOT EXISTS 操作符应用举例
  • 20. 相关更新 使用相关子查询依据一个表中的数据更新另一个表的数据 UPDATE table1 alias1 SET column = (SELECT expression FROM table2 alias2 WHERE alias1.column = alias2.column);
  • 21. 相关更新应用举例ALTER TABLE employees ADD(department_name VARCHAR2(14));UPDATE employees e SET department_name = (SELECT department_name FROM departments d WHERE e.department_id = d.department_id);
  • 22. DELETE FROM table1 alias1 WHERE column operator (SELECT expression FROM table2 alias2 WHERE alias1.column = alias2.column);相关删除使用相关子查询依据一个表中的数据删除另一个表的数据
  • 23. DELETE FROM employees E WHERE employee_id = (SELECT employee_id FROM emp_history WHERE employee_id = E.employee_id);相关删除应用举例
  • 24. WITH 子句使用 WITH 子句, 可以避免在 SELECT 语句中重复书写相同的语句块 WITH 子句将该子句中的语句块执行一次 并存储到用户的临时表空间中 使用 WITH 子句可以提高查询效率
  • 25. WITH 子句应用举例WITH dept_costs AS ( SELECT d.department_name, SUM(e.salary) AS dept_total FROM employees e, departments d WHERE e.department_id = d.department_id GROUP BY d.department_name), avg_cost AS ( SELECT SUM(dept_total)/COUNT(*) AS dept_avg FROM dept_costs) SELECT * FROM dept_costs WHERE dept_total > (SELECT dept_avg FROM avg_cost) ORDER BY department_name;
  • 26. 总结通过本章学习,您已经可以: 使用多列子查询 多列子查询的成对和非成对比较 单列子查询 相关子查询 EXISTS 和 NOT EXISTS操作符 相关更新和相关删除 WITH子句
  • 27. Hidden Slide
  • 28. Hidden Slide
  • 29. Hidden Slide
  • 30. Hidden Slide
  • 31. Hidden Slide