oracle数据库ppt+中科院培训专用Les18_cn

Copyright©OracleCorporation,2001.Allrightsreserved.高级子查询18-2Copyright©OracleCorporation,2001.Allrightsreserved.目标完成本课后,您应当能够：•写多列子查询•在返回空值时描述并解释子查询的行为•写一个在FROM子句中的子查询•SQL中使用标量子查询•描述能够用相关子查询解决的问题类型•写相关子查询•用相关子查询更新和删除行•使用EXISTS和NOTEXISTS操作•使用WITH子句18-3Copyright©OracleCorporation,2001.Allrightsreserved.什么是子查询?子查询是一个嵌入在另一个SQL语句的子句中的SELECT语句SELECT...FROM...WHERE...(SELECT...FROM...WHERE...)主查询子查询18-4Copyright©OracleCorporation,2001.Allrightsreserved.子查询•子查询(内嵌查询)在主查询中执行一次•子查询的结果被用于主查询(外查询)SELECTselect_listFROMtableWHEREexproperator(SELECTselect_listFROMtable);18-5Copyright©OracleCorporation,2001.Allrightsreserved.SELECTlast_nameFROMemployeesWHEREsalary(SELECTsalaryFROMemployeesWHEREemployee_id=149);使用子查询1050018-6Copyright©OracleCorporation,2001.Allrightsreserved.多列子查询MainqueryWHERE(MANAGER_ID,DEPARTMENT_ID)INSubquery100901026012450主查询的每行与一个多行多列子查询的值比较18-7Copyright©OracleCorporation,2001.Allrightsreserved.列比较在一个多列子查询中的列比较能够被：•成对地比较•非成对的比较18-8Copyright©OracleCorporation,2001.Allrightsreserved.成对比较子查询显示雇员的细节，这些雇员被同一个经理管理，并且，工作在同一个部门，但不包括EMPLOYEE_ID为178或174的雇员SELECTemployee_id,manager_id,department_idFROMemployeesWHERE(manager_id,department_id)IN(SELECTmanager_id,department_idFROMemployeesWHEREemployee_idIN(178,174))ANDemployee_idNOTIN(178,174);18-9Copyright©OracleCorporation,2001.Allrightsreserved.非成对比较子查询SELECTemployee_id,manager_id,department_idFROMemployeesWHEREmanager_idIN(SELECTmanager_idFROMemployeesWHEREemployee_idIN(174,141))ANDdepartment_idIN(SELECTdepartment_idFROMemployeesWHEREemployee_idIN(174,141))ANDemployee_idNOTIN(174,141);显示经理与雇员174或141相同，并且，工作在同一个部门，不包括EMPLOYEE_ID为174或141的雇员的详细信息18-10Copyright©OracleCorporation,2001.Allrightsreserved.SELECTa.last_name,a.salary,a.department_id,b.salavgFROMemployeesa,(SELECTdepartment_id,AVG(salary)salavgFROMemployeesGROUPBYdepartment_id)bWHEREa.department_id=b.department_idANDa.salaryb.salavg;在FROM子句中使用子查询18-11Copyright©OracleCorporation,2001.Allrightsreserved.标量子查询表达式•一个标量子查询表达式是一个从一行中返回确切的一个列值的子查询•在Oracle8i中，标量子查询仅在一些有限情况的情况下被支持，例如：–SELECT语句(FROM和WHERE子句)–在一个INSERT语句中的列表值•在Oracle9i中，标量子查询能够被用于：–DECODEandCASE的条件和表达式部分–除GROUPBY以外所有的SELECT子句18-12Copyright©OracleCorporation,2001.Allrightsreserved.标量子查询:例子在CASE表达式中的标量子查询SELECTemployee_id,last_name,(CASEWHENdepartment_id=THEN'Canada'ELSE'USA'END)locationFROMemployees;(SELECTdepartment_idFROMdepartmentsWHERElocation_id=1800)在ORDERBY子查询中的标量子查询SELECTemployee_id,last_nameFROMemployeeseORDERBY20(SELECTdepartment_nameFROMdepartmentsdWHEREe.department_id=d.department_id);18-14Copyright©OracleCorporation,2001.Allrightsreserved.相关子查询相关子查询被用于row-by-row处理。对外查询的每一行，每个子查询被执行一次GET取来自外查询的候选行EXECUTE用候选行值执行内查询USE用内查询的值确认或取消候选行18-15Copyright©OracleCorporation,2001.Allrightsreserved.相关子查询SELECTcolumn1,column2,...FROMtable1WHEREcolumn1operator(SELECTcolum1,column2FROMtable2WHEREexpr1=.expr2);子查询参考在父查询中的表的一个列outerouter18-16Copyright©OracleCorporation,2001.Allrightsreserved.SELECTlast_name,salary,department_idFROMemployeesouterWHEREsalary使用相关子查询外查询中的行每被处理一次，内查询就求值一次找出所有的雇员，他们挣的薪水高于该部门的平均薪水(SELECTAVG(salary)FROMemployeesWHEREdepartment_id=outer.department_id);18-17Copyright©OracleCorporation,2001.Allrightsreserved.使用相关子查询SELECTe.employee_id,last_name,e.job_idFROMemployeeseWHERE2=(SELECTCOUNT(*)FROMjob_historyWHEREemployee_id=e.employee_id);显示雇员的详细信息，这些雇员至少变换过两次工作18-18Copyright©OracleCorporation,2001.Allrightsreserved.EXISTS操作EXISTS操作对在子查询的结果集中存在的行进行检验：•如果一个子查询行值被找到：–在内查询中的搜索不再继续–条件被标记为TRUE•如果一个子查询行值未找到：–条件被标记为FALSE–在内查询中的搜索继续18-19Copyright©OracleCorporation,2001.Allrightsreserved.SELECTemployee_id,last_name,job_id,department_idFROMemployeesouterWHEREEXISTS(SELECT'X'FROMemployeesWHEREmanager_id=outer.employee_id);使用EXISTS操作查找至少有一个雇员的经理18-20Copyright©OracleCorporation,2001.Allrightsreserved.SELECTdepartment_id,department_nameFROMdepartmentsdWHERENOTEXISTS(SELECT'X'FROMemployeesWHEREdepartment_id=d.department_id);使用NOTEXISTS操作找出所有的没有任何雇员的部门18-21Copyright©OracleCorporation,2001.Allrightsreserved.相关UPDATE用一个相关子查询来更新在一个表中的行，该表中的行基于另一个表中的行UPDATEtable1alias1SETcolumn=(SELECTexpressionFROMtable2alias2WHEREalias1.column=alias2.column);18-22Copyright©OracleCorporation,2001.Allrightsreserved.相关UPDATE•用一个附加的列来存储部门名称，反向规格化EMPLOYEES表•用相关更新填充表ALTERTABLEemployeesADD(department_nameVARCHAR2(14));UPDATEemployeeseSETdepartment_name=(SELECTdepartment_nameFROMdepartmentsdWHEREe.department_id=d.department_id);18-24Copyright©OracleCorporation,2001.Allrightsreserved.DELETEFROMtable1alias1WHEREcolumnoperator(SELECTexpressionFROMtable2alias2WHEREalias1.column=alias2.column);相关DELETE用一个相关子查询删除表中的行，该表基于另一个表中的行18-25Copyright©OracleCorporation,2001.Allrightsreserved.DELETEFROMemployeesEWHEREemployee_id=(SELECTemployee_idFROMemp_historyWHEREemployee_id=E.employee_id);用一个相关子查询删除那些在EMPLOYEES表和EMP_HISTORY表中的employee_id列值相同的行相关DELETE18-26Copyright©OracleCorporation,2001.Allrightsreserved.WITH子句•当一个查询块在一个复杂的查询中出现多次时，使用WITH子句，能够用在SELECT语句中使用相同查询块•WITH子句取回查询块的结果，并且将它存在用户的临时表空间中•WITH子句可以改善性能18-27Copyright©OracleCorporation,2001.Allrightsreserved.WITH子句:例子用WITH子句，写一个查询来显示部门名称和该