Les18oracle数据库教程.ppt
,高级子查询,目标,通过本章学习，您将可以:书写多列子查询子查询对空值的处理在 FROM 子句中使用子查询在SQL中使用单列子查询相关子查询书写相关子查询使用子查询更新和删除数据使用 EXISTS 和 NOT EXISTS 操作符使用 WITH 子句,子查询,子查询是嵌套在 SQL 语句中的另一个SELECT 语句,SELECT.FROM.WHERE.,(SELECT.FROM.WHERE.),主查询,子查询,子查询,子查询(内查询)在主查询执行之前执行主查询使用子查询的结果(外查询),SELECTselect_listFROMtableWHEREexpr operator(SELECT select_list FROM table);,SELECT last_nameFROM employeesWHERE salary(SELECT salary FROM employees WHERE employee_id=149);,子查询应用举例,多列子查询,Main query,WHERE(MANAGER_ID,DEPARTMENT_ID)IN,Subquery,100 90102 60124 50,主查询与子查询返回的多个列进行比较,列比较,多列子查询中的比较分为两种:成对比较不成对比较,成对比较举例,SELECTemployee_id,manager_id,department_idFROMemployeesWHERE(manager_id,department_id)IN(SELECT manager_id,department_id FROM employees WHERE employee_id IN(178,174)ANDemployee_id NOT IN(178,174);,不成对比较举例,SELECT employee_id,manager_id,department_idFROM employeesWHERE 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)ANDemployee_id NOT IN(174,141);,SELECT a.last_name,a.salary,a.department_id,b.salavgFROM employees a,(SELECT department_id,AVG(salary)salavg FROM employees GROUP BY department_id)bWHERE a.department_id=b.department_idAND a.salary b.salavg;,在 FROM 子句中使用子查询,单列子查询表达式,单列子查询表达式是在一行中只返回一列的子查询Oracle8i 只在下列情况下可以使用,例如:SELECT 语句(FROM 和 WHERE 子句)INSERT 语句中的VALUES列表中Oracle9i中单列子查询表达式可在下列情况下使用:DECODE 和 CASESELECT 中除 GROUP BY 子句以外的所有子句中,单列子查询应用举例,在 CASE 表达式中使用单列子查询,SELECT employee_id,last_name,(CASE WHEN department_id=THEN Canada ELSE USA END)locationFROM employees;,(SELECT department_id FROM departmentsWHERE location_id=1800),在 ORDER BY 子句中使用单列子查询,SELECT employee_id,last_nameFROM employees eORDER BY,(SELECT department_name FROM departments d WHERE e.department_id=d.department_id);,相关子查询,相关子查询按照一行接一行的顺序执行，主查询的每一行都执行一次子查询,GET从主查询中获取候选列,EXECUTE子查询使用主查询的数据,USE如果满足内查询的条件则返回该行,相关子查询,SELECT column1,column2,.FROM table1 WHERE column1 operator(SELECT colum1,column2 FROM table2 WHERE expr1=.expr2);,子查询中使用主查询中的列,outer,outer,SELECT last_name,salary,department_idFROM employees outerWHERE salary,相关子查询举例,(SELECT AVG(salary)FROM employees WHERE department_id=outer.department_id);,相关子查询举例,SELECT e.employee_id,last_name,e.job_idFROM employees e WHERE 2=(SELECT COUNT(*)FROM job_history WHERE employee_id=e.employee_id);,EXISTS 操作符,EXISTS 操作符检查在子查询中是否存在满足条件的行如果在子查询中存在满足条件的行:不在子查询中继续查找条件返回 TRUE如果在子查询中不存在满足条件的行:条件返回 FALSE继续在子查询中查找,SELECT employee_id,last_name,job_id,department_idFROM employees outerWHERE EXISTS(SELECT X FROM employees WHERE manager_id=outer.employee_id);,EXISTS 操作符应用举例,SELECT department_id,department_nameFROM departments dWHERE NOT EXISTS(SELECT X FROM employees WHERE department_id=d.department_id);,NOT EXISTS 操作符应用举例,相关更新,使用相关子查询依据一个表中的数据更新另一个表的数据,UPDATE table1 alias1SET column=(SELECT expression FROM table2 alias2 WHERE alias1.column=alias2.column);,相关更新应用举例,ALTER TABLE employeesADD(department_name VARCHAR2(14);,UPDATE employees eSET department_name=(SELECT department_name FROM departments d WHERE e.department_id=d.department_id);,DELETE FROM table1 alias1 WHERE column operator(SELECT expression FROM table2 alias2 WHERE alias1.column=alias2.column);,相关删除,使用相关子查询依据一个表中的数据删除另一个表的数据,DELETE FROM employees EWHERE employee_id=(SELECT employee_id FROM emp_history WHERE employee_id=E.employee_id);,相关删除应用举例,WITH 子句,使用 WITH 子句,可以避免在 SELECT 语句中重复书写相同的语句块WITH 子句将该子句中的语句块执行一次 并存储到用户的临时表空间中使用 WITH 子句可以提高查询效率,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;,总结,通过本章学习,您已经可以:使用多列子查询多列子查询的成对和非成对比较单列子查询相关子查询EXISTS 和 NOT EXISTS操作符相关更新和相关删除WITH子句,Hidden Slide,Hidden Slide,Hidden Slide,Hidden Slide,Hidden Slide,
