您好,欢迎访问三七文档
当前位置:首页 > IT计算机/网络 > 数据库 > Oracle_9i详解之高级子查询
ORACLE_9I详解之高级子查询目标通过本章学习,您将可以:书写多列子查询子查询对空值的处理在FROM子句中使用子查询在SQL中使用单列子查询相关子查询书写相关子查询使用子查询更新和删除数据使用EXISTS和NOTEXISTS操作符使用WITH子句子查询子查询是嵌套在SQL语句中的另一个SELECT语句SELECT...FROM...WHERE...(SELECT...FROM...WHERE...)主查询子查询子查询子查询(内查询)在主查询执行之前执行主查询使用子查询的结果(外查询)SELECTselect_listFROMtableWHEREexproperator(SELECTselect_listFROMtable);SELECTlast_nameFROMemployeesWHEREsalary(SELECTsalaryFROMemployeesWHEREemployee_id=149);子查询应用举例10500多列子查询MainqueryWHERE(MANAGER_ID,DEPARTMENT_ID)INSubquery100901026012450主查询与子查询返回的多个列进行比较列比较多列子查询中的比较分为两种:成对比较不成对比较成对比较举例SELECTemployee_id,manager_id,department_idFROMemployeesWHERE(manager_id,department_id)IN(SELECTmanager_id,department_idFROMemployeesWHEREemployee_idIN(178,174))ANDemployee_idNOTIN(178,174);不成对比较举例SELECTemployee_id,manager_id,department_idFROMemployeesWHEREmanager_idIN(SELECTmanager_idFROMemployeesWHEREemployee_idIN(174,141))ANDdepartment_idIN(SELECTdepartment_idFROMemployeesWHEREemployee_idIN(174,141))ANDemployee_idNOTIN(174,141);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子句中使用子查询单列子查询表达式单列子查询表达式是在一行中只返回一列的子查询Oracle8i只在下列情况下可以使用,例如:SELECT语句(FROM和WHERE子句)INSERT语句中的VALUES列表中Oracle9i中单列子查询表达式可在下列情况下使用:DECODE和CASESELECT中除GROUPBY子句以外的所有子句中单列子查询应用举例在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);相关子查询相关子查询按照一行接一行的顺序执行,主查询的每一行都执行一次子查询GET从主查询中获取候选列EXECUTE子查询使用主查询的数据USE如果满足内查询的条件则返回该行相关子查询SELECTcolumn1,column2,...FROMtable1WHEREcolumn1operator(SELECTcolum1,column2FROMtable2WHEREexpr1=.expr2);子查询中使用主查询中的列outerouterSELECTlast_name,salary,department_idFROMemployeesouterWHEREsalary相关子查询举例(SELECTAVG(salary)FROMemployeesWHEREdepartment_id=outer.department_id);相关子查询举例SELECTe.employee_id,last_name,e.job_idFROMemployeeseWHERE2=(SELECTCOUNT(*)FROMjob_historyWHEREemployee_id=e.employee_id);EXISTS操作符EXISTS操作符检查在子查询中是否存在满足条件的行如果在子查询中存在满足条件的行:不在子查询中继续查找条件返回TRUE如果在子查询中不存在满足条件的行:条件返回FALSE继续在子查询中查找SELECTemployee_id,last_name,job_id,department_idFROMemployeesouterWHEREEXISTS(SELECT'X'FROMemployeesWHEREmanager_id=outer.employee_id);EXISTS操作符应用举例SELECTdepartment_id,department_nameFROMdepartmentsdWHERENOTEXISTS(SELECT'X'FROMemployeesWHEREdepartment_id=d.department_id);NOTEXISTS操作符应用举例相关更新使用相关子查询依据一个表中的数据更新另一个表的数据UPDATEtable1alias1SETcolumn=(SELECTexpressionFROMtable2alias2WHEREalias1.column=alias2.column);相关更新应用举例ALTERTABLEemployeesADD(department_nameVARCHAR2(14));UPDATEemployeeseSETdepartment_name=(SELECTdepartment_nameFROMdepartmentsdWHEREe.department_id=d.department_id);DELETEFROMtable1alias1WHEREcolumnoperator(SELECTexpressionFROMtable2alias2WHEREalias1.column=alias2.column);相关删除使用相关子查询依据一个表中的数据删除另一个表的数据DELETEFROMemployeesEWHEREemployee_id=(SELECTemployee_idFROMemp_historyWHEREemployee_id=E.employee_id);相关删除应用举例WITH子句使用WITH子句,可以避免在SELECT语句中重复书写相同的语句块WITH子句将该子句中的语句块执行一次并存储到用户的临时表空间中使用WITH子句可以提高查询效率WITH子句应用举例WITHdept_costsAS(SELECTd.department_name,SUM(e.salary)ASdept_totalFROMemployeese,departmentsdWHEREe.department_id=d.department_idGROUPBYd.department_name),avg_costAS(SELECTSUM(dept_total)/COUNT(*)ASdept_avgFROMdept_costs)SELECT*FROMdept_costsWHEREdept_total(SELECTdept_avgFROMavg_cost)ORDERBYdepartment_name;总结通过本章学习,您已经可以:使用多列子查询多列子查询的成对和非成对比较单列子查询相关子查询EXISTS和NOTEXISTS操作符相关更新和相关删除WITH子句
本文标题:Oracle_9i详解之高级子查询
链接地址:https://www.777doc.com/doc-3606629 .html