高级子查询查询语法子查询是嵌套在SQL语句中的另一个SELECT语句一、多列子查询主查询与子查询返回的多个列进行比较多列子查询中的比较
分为两种:成对比较不成对比较不成对比较举例(原子查询写法）例：查询与141号或174号员工的manager_id和departm
ent_id相同的其他员工的employee_id,manager_id,department_id。SELECTempl
oyee_id,manager_id,department_idFROMemployeesWHEREmanager_idI
N(SELECTmanager_idFROMemployeesWHEREemployee_idIN(17
4,141))ANDdepartment_idIN(SELECTdepartment_idFROM
employeesWHEREemployee_idIN(174,141))ANDemployee_idnotin(
174,141);成对比较举例例：查询与141号或174号员工的manager_id和department_id相同的其他员工的e
mployee_id,manager_id,department_idSELECTemployee_id,manager
_id,department_idFROMemployeesWHERE(manager_id,department_id
)IN(SELECTmanager_id,department_idFROMemployeesWHEREem
ployee_idIN(141,174))ANDemployee_idNOTIN(141,174);在FROM子句
中使用子查询(不使用from与使用from）例(不使用）：返回比本部门平均工资高的员工的last_name,department
_id,salary及平均工资Selectlast_name,department_id,salary,(selectav
g(salary)fromemployeese3wheree1.department_id=e3.department
_idgroupbydepartment_id)avg_salaryFromemployeese1Wheresala
ry>(selectavg(salary)fromemployeese2wheree1.department_id=e
2.department_idgroupbydepartment_id)例(使用）：返回比本部门平均工资高的员工的last_
name,department_id,salary及平均工资SELECTa.last_name,a.salary,a
.department_id,b.salavgFROMemployeesa,(SELECTdepartment_id,
AVG(salary)salavgFROMemployeesGROUPBYdepartment_id)b
WHEREa.department_id=b.department_idANDa.salary>b.salavg三、单列子
查询表达式单列子查询表达式是在一行中只返回一列的子查询Oracle8i只在下列情况下可以使用,例如:SELECT语句(FR
OM和WHERE子句)INSERT语句中的VALUES列表中Oracle9i中单列子查询表达式可在下列情况下使用:DECO
DE和CASESELECT中除GROUPBY子句以外的所有子句中单列子查询应用举例在CASE表达式中使用单列子查
询例：显式员工的employee_id,last_name和location。其中，若员工department_id与locati
on_id为1800的department_id相同，则location为’Canada’,其余则为’USA’。Selectem
ployees_id,last_name,(CASEwhendepartment_id=(SELECTdepartmen
t_idFROMdepartmentswherelocation_id=1800)THEN''Canada''ELS
E''USA''END)locationFromemployees在ORDERBY子句中使用单列子查询例：查询员工的em
ployee_id,last_name,要求按照员工的department_name排序Selectemployee_id,la
st_name,department_nameFromemployeeseOrberby(selectdepartmen
t_nameFromdepartmetnsdWheree.department_id=d.department_i
d)四、相关子查询相关子查询按照一行接一行的顺序执行，主查询的每一行都执行一次子查询相关子查询例：若employees表中empl
oyee_id与job_history表中employee_id相同的数目不小于2，输出这些相同id的员工的employee_id
,last_name和其job_idSELECTe.employee_id,last_name,e.job_idFROM
employeeseWHERE2<=(SELECTCOUNT()FROMjob_historyWHERE
employee_id=e.employee_id)五、EXISTS操作符EXISTS操作符检查在子查询中是否存在满足条件
的行如果在子查询中存在满足条件的行:不在子查询中继续查找条件返回TRUE如果在子查询中不存在满足条件的行:条件返回FALSE继
续在子查询中查找EXISTS操作符应用举例例：查询公司管理者的employee_id,last_name,job_id,depa
rtment_id信息例：SELECTemployee_id,last_name,job_id,department_id
FROMemployeese1WHEREEXISTS(SELECT''A''FROMemployeese2
Fromemployeese2Wheree1.employee_id=e2.manger_id)例：Selectem
ployee_id,last_name,job_id,department_idFromemployeese1Wheree1
.employee_idin(Selectmanger_idFromemployeese2Wheree1.em
ployee_id=e2.manger_id)例：selectdistincte1.employee_id,e1.last_
name,e1.job_id,e1.department_idFromemployeese1,employeese2Wher
ee1.employee_id=e2.manager_idNOTEXISTS操作符应用举例例：查询departments表中
，不存在于employees表中的部门的department_id和department_nameSELECTdepartmen
t_id,department_nameFROMdepartmentsdWHERENOTEXISTS(SELECT''
X''FROMemployeesWHEREdepartment_id=d.department_id);例：se
lectdepartment_idFromdepartmentsdMinusselectdepartment_idFro
memployees六、相关更新使用相关子查询依据一个表中的数据更新另一个表的数据UPDATEtable1alias1SET
column=(SELECTexpressionFROMtable2alias2WHEREalias
1.column=alias2.column);例：1）ALTERTABLEemployeesADD(department
_nameVARCHAR2(14));2）UPDATEemployeeseSETdepartment_name=(SE
LECTdepartment_nameFROMdepartmentsdWHEREe.department_id
=d.department_id);相关删除使用相关子查询依据一个表中的数据删除另一个表的数据DELETEFROMtabl
e1alias1WHEREcolumnoperator(SELECTexpressionFROMtable
2alias2WHEREalias1.column=alias2.column);例：删除表employees中，其
与emp_history表皆有的数据DELETEFROMemployeesEWHEREemployee_id=(S
ELECTemployee_idFROMemp_historyWHEREemployee_id=E.employe
e_id);七、WITH子句使用WITH子句,可以避免在SELECT语句中重复书写相同的语句块WITH子句将该子句中
的语句块执行一次并存储到用户的临时表空间中使用WITH子句可以提高查询效率例：查询公司中各部门的总工资大于公司中各部门的平均总
工资的部门信息WITHdept_costsAS(SELECTd.department_name,SUM(e.sala
ry)ASdept_totalFROMemployeese,departmentsdWHEREe.department_id=d.department_idGROUPBYd.department_name),avg_costAS(SELECTSUM(dept_total)/COUNT()ASdept_avgFROMdept_costs)SELECTFROMdept_costsWHEREdept_total>(SELECTdept_avgFROMavg_cost)ORDERBYdepartment_name; |
|
