尚硅谷_宋红康_14_高级子查询

第14节高级子查询 讲师 宋红康新浪微博 尚硅谷 宋红康 目标 通过本章学习 您将可以 书写多列子查询在FROM子句中使用子查询在SQL中使用单列子查询书写相关子查询使用EXISTS和NOTEXISTS操作符使用子查询更新和删除数据使用WITH子句 子查询 子查询是嵌套在SQL语句中的另一个SELECT语句 SELECT FROM WHERE SELECT FROM WHERE 主查询 子查询 子查询 子查询 内查询 在主查询执行之前执行主查询 外查询 使用子查询的结果 SELECTselect listFROMtableWHEREexproperator SELECTselect listFROMtable SELECTlast nameFROMemployeesWHEREsalary SELECTsalaryFROMemployeesWHEREemployee id 149 子查询应用举例 问题 查询工资大于149号员工工资的员工的信息 一 多列子查询 Mainquery WHERE MANAGER ID DEPARTMENT ID IN Subquery 100901026012450 主查询与子查询返回的多个列进行比较 列比较 多列子查询中的比较分为两种 成对比较不成对比较 成对比较举例 SELECTemployee id manager id department idFROMemployeesWHERE manager id department id IN SELECTmanager id department idFROMemployeesWHEREemployee idIN 141 174 ANDemployee idNOTIN 141 174 问题 查询与141号或174号员工的manager id和department id相同的其他员工的employee id manager id department id 不成对比较举例 SELECTemployee id manager id department idFROMemployeesWHEREmanager idIN SELECTmanager idFROMemployeesWHEREemployee idIN 174 141 ANDdepartment idIN SELECTdepartment idFROMemployeesWHEREemployee idIN 174 141 ANDemployee idNOTIN 174 141 selectlast name department id salary selectavg salary fromemployeese3wheree1 department id e3 department idgroupbydepartment id avg salaryfromemployeese1wheresalary selectavg salary fromemployeese2wheree1 department id e2 department idgroupbydepartment id 二 在FROM子句中使用子查询 问题 返回比本部门平均工资高的员工的last name department id salary及平均工资 方法一 SELECTa last name a salary a department id b salavgFROMemployeesa SELECTdepartment id AVG salary salavgFROMemployeesGROUPBYdepartment id bWHEREa department id b department idANDa salary b salavg 二 在FROM子句中使用子查询 问题 返回比本部门平均工资高的员工的last name department id salary及平均工资 方法二 三 单列子查询表达式 单列子查询表达式是在一行中只返回一列的子查询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 问题 显式员工的employee id last name和location 其中 若员工department id与location id为1800的department id相同 则location为 Canada 其余则为 USA 单列子查询应用举例 SELECTemployee id last nameFROMemployeeseORDERBY SELECTdepartment nameFROMdepartmentsdWHEREe department id d department id 在ORDERBY子句中使用单列子查询 问题 查询员工的employee id last name 要求按照员工的department name排序 四 相关子查询 相关子查询按照一行接一行的顺序执行 主查询的每一行都执行一次子查询 GET从主查询中获取候选列 EXECUTE子查询使用主查询的数据 USE如果满足内查询的条件则返回该行 相关子查询 SELECTcolumn1 column2 FROMtable1WHEREcolumn1operator SELECTcolum1 column2FROMtable2WHEREexpr1 expr2 outer outer 子查询中使用主查询中的列 SELECTlast name salary department idFROMemployeesouterWHEREsalary 相关子查询举例 SELECTAVG salary FROMemployeesWHEREdepartment id outer department id 问题 查询员工中工资大于本部门平均工资的员工的last name salary和其department id 相关子查询举例 SELECTe employee id last name e job idFROMemployeeseWHERE2 SELECTCOUNT FROMjob historyWHEREemployee id e employee id 问题 若employees表中employee id与job history表中employee id相同的数目不小于2 输出这些相同id的员工的employee id last name和其job id 五 EXISTS操作符 EXISTS操作符检查在子查询中是否存在满足条件的行如果在子查询中存在满足条件的行 不在子查询中继续查找条件返回TRUE如果在子查询中不存在满足条件的行 条件返回FALSE继续在子查询中查找 SELECTemployee id last name job id department idFROMemployeesouterWHEREEXISTS SELECT X FROMemployeesWHEREmanager id outer employee id EXISTS操作符应用举例 问题 查询公司管理者的employee id last name job id department id信息 SELECTdepartment id department nameFROMdepartmentsdWHERENOTEXISTS SELECT X FROMemployeesWHEREdepartment id d department id NOTEXISTS操作符应用举例 问题 查询departments表中 不存在于employees表中的部门的department id和department name 六 相关更新 使用相关子查询依据一个表中的数据更新另一个表的数据 UPDATEtable1alias1SETcolumn SELECTexpressionFROMtable2alias2WHEREalias1 column alias2 column 相关更新应用举例 ALTERTABLEemployeesADD department nameVARCHAR2 14 UPDATEemployeeseSETdepartment name SELECTdepartment nameFROMdepartmentsdWHEREe department id d department id 1 2 DELETEFROMtable1alias1WHEREcolumnoperator SELECTexpressionFROMtable2alias2WHEREalias1 column alias2 column 相关删除 使用相关子查询依据一个表中的数据删除另一个表的数据 DELETEFROMemployeesEWHEREemployee id SELECTemployee idFROMemp historyWHEREemployee id E employee id 相关删除应用举例 问题 删除表employees中 其与emp history表皆有的数据 七 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