Oracle数据库应用教程子查询与高级查询

第8章子查询与高级查询本章要点：掌握在WHERE子句中使用子查询。掌握在HAVING子句中使用子查询。熟练掌握使用IN、ANY和ALL操作符实现子查询。熟练掌握关联子查询。熟练掌握嵌套子查询。掌握简单连接。熟练掌握多个表之间的内连接。熟练掌握多个表之间的外连接。了解多个表之间的交叉连接。掌握使用集合操作符实现集合查询。28.1子查询在外部的SELECT、UPDATE或DELETE语句内部使用SELECT语句，这个内部SELECT语句称为子查询(Subquery)。使用子查询，主要是将子查询的结果作为外部主查询的查找条件。38.1.1子查询的类型在子查询中可以使用两种比较操作符——单行操作符和多行操作符。单行操作符：例如=、>、>=、<、<=、<>、!=。多行操作符：例如ALL、ANY、IN、EXISTS。可以把子查询分为两种类型：单行子查询和多行子查询。另外，子查询还有下面3种子类型，这3种子类型可以返回一行或多行查询结果。多列子查询：向外部的SQL语句返回多列。关联子查询：引用外部的SQL语句中的一列或多列。在关联子查询中，可以使用EXISTS和NOTEXISTS操作符。嵌套子查询：在子查询中包含有子查询。指定子查询时，需要注意以下几点：子查询需要使用括号()括起来。子查询要放在比较操作符的右边。当子查询的返回值是一个集合而不是一个值时，不能使用单行操作符，而必须根据需要使用ANY、IN、ALL或EXISTS等操作符。48.1.2在WHERE子句中使用子查询1．使用子查询在WHERE子句中使用子查询的一般用法形式如下：SELECTcolumn_listFROMtable_nameWHEREexpressionoperator( SELECTcolumn_nameFROMtable_nameWHEREcondition GROUPBYexpHAVINGhaving);其中，在外部SELECT语句的WHERE子句中，expression用来指定一个表达式，也可以是表中的一列；operator可以是单行和多行操作符；()中的内容表示子查询内容。58.1.2在WHERE子句中使用子查询2．不能使用ORDERBY子句在子查询的SELECT语句中，可以使用FROM子句、WHERE子句、GROUPBY子句和HAVING子句等，但是有些情况下不能使用ORDERBY子句，例如在WHERE子句中使用子查询时，子查询语句中就不能使用ORDERBY子句。68.1.3在HAVING子句中使用子查询在SELECT语句中使用HAVING子句，可以实现对数据进行分组过滤。在HAVING子句中，如果使用子查询，那么就可以实现对子查询返回的结果根据分组进行过滤。【例8.3】对scott用户的emp表进行检索，在HAVING子句中使用子查询。获取哪些部门的员工平均工资小于全体员工的平均工资。具体如下：SQL>SELECTdeptno,AVG(sal)FROMscott.empGROUPBYdeptno2HAVINGAVG(sal)<(3 SELECTAVG(sal)FROMscott.emp);DEPTNO AVG(SAL)---------- -------------30 1566.6666778.1.4使用IN操作符实现指定匹配查询1．使用IN操作符IN操作符用来检查在一个值列表中是否包含指定的值。这个值列表可以是子查询的返回结果。2．使用NOTIN操作符NOTIN操作符用来检查在一个值列表中是否不包含指定的值，NOTIN执行的操作正好与IN在逻辑上相反。3．常见的操作错误多行子查询可以返回多行记录，如果接收子查询结果的操作符是单行操作符，那么在执行语句时，可能会出现错误提示。88.1.5使用ANY操作符实现任意匹配查询在进行多行子查询时，使用ANY操作符，用来将一个值与一个列表中的所有值进行比较，这个值只需要匹配列表中的一个值即可，然后将满足条件的数据返回。其中，值列表可以是子查询的返回结果。在使用ANY操作符之前，必须使用一个单行操作符，例如=、>、<、<=等。【例8.7】对scott用户的emp表进行操作，获得工资大于任意一个部门的平均工资的员工信息，如下：参见教材P17698.1.6使用ALL操作符实现全部匹配查询在进行子查询时，使用ALL操作符，用来将一个值与一个列表中的所有值进行比较，这个值需要匹配列表中的所有值，然后将满足条件的数据返回。其中，值列表可以是子查询的返回结果。在使用ALL操作符之前，必须使用一个单行操作符，例如=、>、<、<=等。【例8.8】对scott用户的emp表进行操作，获得工资大于所有部门的平均工资的员工信息，如下：参见教材P17610实实现多列列子查询多列子查询则则是指返回多多列数据的子子查询语句。。当多列子查查询返回单行行数据时，在在WHERE子句中可以以使用单行操操作符；返回回多行数据时时，在WHERE子句中中必须使用多多行操作符。。使用子查询比比较多个列的的数据时，可可以使用下面面两种方式。。成对比较：要要求多个列的的数据必须同同时匹配。非成对比较：：通过指定连连接关键字，，例如AND或OR等，，指定多个列列的数据是否否必须同时匹匹配。如果使使用AND关关键字，表示示同时匹配，，这样就可以以实现与成对对比较同样的的结果；如果果使用OR关关键字，表示示不必同时匹匹配。11实实现多列列子查询【例8.9】】对scott用户的emp表进行操操作，使用成成对比较的方方式，获得每每个部门中工工资最低的员员工信息，如如下：参见教材P177【例8.10】使用非成对比比较的方式，，并且指定AND关键字字，获得每个个部门中工资资最低的员工工信息，如下下：参见教材P17712实实现关联联子查询关联子查询会会引用外部查查询中的一列列或多列，这这种子查询之之所以被称为为关联子查询询，是因为它它的确与外部部语句相关。。【例8.11】查询各个部门门中，哪些员员工的工资低低于其所在部部门的平均工工资，如下：：参见教材P17813实实现关联联子查询1．使用EXISTS操操作符在关联子查询询中可以使用用EXISTS或NOTEXISTS操作符符。其中，EXISTS操作符用于于检查子查询询所返回的行行是否存在，，它可以在非非关联子查询询中使用，但但是更常用于于关联子查询询。2．使用NOTEXISTS操作作符在执行的操作作逻辑上，NOTEXISTS操操作符的作用用与EXISTS操作符符相反。在需需要检查数据据行中是否不不存在子查询询返回的结果果时，就可以以使用NOTEXISTS。14实实现关联联子查询3．EXISTS与IN的比较在使用NOTEXISTS和NOTIN时时，如果一个个值列表中包包含有空值，，NOTEXISTS返回TRUE；而NOTIN则则返回FALSE。【例8.14】使用NOTEXISTS操作符，，检索最高领领导人员的有有关信息，也也就是该员工工没有上级领领导，对应的的mgr列的的值为NULL。具体如如下：参见教材P179【例8.15】使用NOTIN操作符符，重写上述述语句，如下下：参见教材P18015实实现嵌套套子查询所谓嵌套子查查询，是指在在子查询内部部使用其他子子查询。嵌套套子查询的嵌嵌套层次最多多为255层层。大多数情情况下，嵌套套子查询都在在外层子查询询的WHERE子句中。。【例8.16】已知工作地点点在NEWYORK和和CHICAGO的两个个部门，要求求根据这两个个部门的平均均工资中的最最大值，获取取工资大于这这个最大值的的员工信息，，如下：SQL>SELECTempno,ename,sal,deptnoFROMscott.emp2WHEREsal>(3 SELECTMAX(AVG(sal))FROMscott.empWHEREdeptnoIN(4SELECTdeptnoFROMscott.dept5WHERElocIN('NEWYORK','CHICAGO'))6 GROUPBYdeptno);EMPNOENAMESALDEPTNO-------------------- ---------- ----------7566JONES2975207788SCOTT3000207839KING5000107902FORD300020168.1.10在UPDATE和和DELETE语句中使使用子查询1．在UPDATE语句句中使用子查查询在UPDATE语句中使使用子查询，，可以将子查查询返回的结结果赋值给需需要更新的列列。【例8.17】将员工编号为为7839的的员工的工资资设置为平均均工资，如下下：SQL>UPDATEscott.empSETsal=(2 SELECTAVG(sal)FROMscott.emp)3WHEREempno=7839;已更新1行行。2．在DELETE语句句中使用子查查询在DELETE语句中使使用子查询，，可以根据子子查询返回的的结果删除指指定的行。【例8.18】删除工作地点点在NEWYORK的的所有员工信信息，如下：：SQL>DELETEFROMscott.empWHEREdeptnoIN(2 SELECTdeptnoFROMscott.deptWHEREloc='NEWYORK');已删除3行。。178.2实验指导——获取借阅次数数最多的前5本图书实验指导8-1：获取借借阅次数最多多的前5本图图书由于需要显示示图书信息，，所以从图书书表book中进行检索索；然后结合合借书表lend，获得得每本书的借借阅情况；最最后按照每本本书的借阅次次数降序排列列，获得前5本图书信息息即可。具体体如下：参见教材P182上述语句中，，在最外层SELECT语句的WHERE子句句中使用了子子查询，并且且使用IN操操作符指定匹匹配查询；第第3行的SELECT语语句中，在FROM子句句中使用子查查询，通过子子查询获得分分组和排序之之后的bookid；最最后在WHERE子句中中指定rownum列小小于等于5，，指定排序之之后的前5行行记录。188.3高高级查询询检索数据时，，通过各个表表之间共同列列的关联性，，可以查询存存放在多个表表中的不同实实体的信息。。如果在查询时时需要对多个个表进行操作作，并且指定定多个表的连连接关系，则则该查询就称称为高级查询询，也可以称称为连接查询询。19使使用等号号(=)实现现多个表的简简单连接在连接查询中中，如果仅仅仅通过SELECT子句句和FROM子句连接多多个表，那么么查询的结果果将是一个通通过笛卡儿积积所生成的表表。【例8.19】使用SELECT子句和和FROM子子句，从scott用户户的emp表表和dept表中检索数数据，如果不不指定检索条条件，将得到到56行记录录。具体如下下：参见教材P183由于scott.emp表中有14行记录，scott.dept表表中有4行记记录，所以笛笛卡儿积所生生成的表一共共有56(14*4=56)行记录。。20使使用表的的别名设置表的别名名，只需要在在FROM子子句中引用该该表时，将表表别名跟在表表的实际名称称后面即可。。表别名和表表的实际名称称之间使用空空格进行分隔隔。【例8.21】使用表别名的的方式，重写写例8.20中所示的语语句。为scott.emp表设置置表别名为e，为scott.dept表设置置表别名为d。语句如下下：SQL>SELECTempno,ename,sal,e.deptno,d.deptno,dname2FROMscott.empe,scott.deptd3WHEREe.deptno=d.deptno;21使使用INNERJOIN实现现多个表的内内连接在FROM子子句中，使用用JOIN连连接的语法形形式如下：FROMjoin_table1join_typejoin_table2[ON(join_condition)][join_type...ONjoin_condition,...]语法说明如下下。join_table1、join_table2：参与与连接操作的的表名。join_type：连连接类型，连连接类型有INNERJOIN(内连接)、、OUTERJOIN(外连接)和CROSSJOIN(交叉连连接)。join_condition：连连接条件，由由被连接表中中的列和比较较运算符、逻逻辑运算符等等构成。可以以使用多组join_type……ONjoin_condition……子句，实现现多个表的连连接。22使使用INNERJOIN实现现多个表的内内连接1．等值连接接所谓等值连接接，是指在连连接条件中使使用等于(=)运算符比比较被连接的的值，也就是是通过相等的的列值连接起起来的查询。。2．不等连接接所谓不等连接接，就是在连连接条件中使使用除等号(=)外的其其他比较运算算符，构成非非等值连接查查询。可以使使用的比较运运算符包括：：>(大于)、<(小于于)、>=(大于等于)、<=(小小于等于)、、<>(不等等于)、!=(不等于)、LIKE、IN和BETWEEN等。23使使用INNERJOIN实现现多个表的内内连接3．自然连接接自然连接(NATURALJOIN)是在两两个表中寻找找列名和数据据类型都相同同的字段，通通过相同的字字段将两个表表连接在一起起，并返回所所有符合条件件的结果。4．使用USING关键键字简化连接接SQL/92标准可以使使用USING关键字来来简化连接查查询，但是只只有在查询满满足下面两个个条件时，才才能使用USING关键键字进行简化化：查询必须是等等值连接。等值连接中的的列必须具有有相同的名称称和数据类型型。24使使用INNERJOIN实现现多个表的内内连接【例8.25】使用USING关键字，，重写例8.22中的语语句，如下：：参见教材P187使用USING关键字简简化连接时，，需要注意以以下几点。(1) 使用用emp表和和dept表表中的deptno列进进行连接时，，在USING子句和SELECT子句中，都都不能为deptno列列指定表名或或表别名。(2) 如果果在连接查询询时使用了两两个表中相同同的多个列，，那么就可以以在USING子句中指指定多个列名名，形式如下下：参见教教材P187(3)如如果对对多个个表进进行检检索，，那么么就必必须多多次使使用USING关键键字进进行指指定，，形式式如下下：参见教教材P18725使使用用OUTERJOIN实现现多个个表的的外连连接对于外外连接接，Oracle中中可以以使用用加号号(+)来来表示示，也也可以以使用用LEFT、RIGHT和FULLOUTERJOIN关键键字。。外连接接可以以分为为下面面这3类：：左外连连接(LEFTOUTERJOIN或或LEFTJOIN)右外连连接(RIGHTOUTERJOIN或RIGHTJOIN)全外连连接(FULLOUTERJOIN或或FULLJOIN)26使使用用OUTERJOIN实现现多个个表的的外连连接1．左左外连连接左外连连接是是在检检索结结果中中除了了显示示满足足连接接条件件的行行外，，还显显示JOIN关关键字字左侧侧表中中所有有满足足检索索条件件的行行。2．右右外连连接右外连连接是是在结结果中中除了了显示示满足足连接接条件件的行行外，，还显显示JOIN右右侧表表中所所有满满足检检索条条件的的行。。3．全全外连连接全外连连接是是在结结果中中除了了显示示满足足连接接条件件的行行外，，还显显示JOIN两两侧表表中所所有满满足检检索条条件的的行。。27使使用用CROSSJOIN实现现交叉叉连接接使用CROSSJOIN关关键字字，可可以实实现两两个表表的交交叉连连接，，所得得到的的结果果将是是这两两个表表中各各行数数据的的所有有组合合，即即这两两个表表所有有数据据行的的笛卡卡儿积积。交叉连连接与与简单单连接接操作作非常常相似似，不不同的的是，，使用用交叉叉连接接时，，在FROM子子句中中多个个表名名之间间不是是用逗逗号，，而是是使用用CROSSJOIN关键键字隔隔开。。另外外，在在交叉叉连接接中不不需要要使用用关键键字ON限限定连连接条条件，，但是是可以以添加加WHERE子子句设设置连连接条条件。。【例8.29】】使用交交叉连连接，，查询询emp表表和dept表表中，，部门门编号号为10的的员工工信息息和部部门信信息，，如下下：参见教教材P19028使使用用UNION操操作符符获取取两个个结果果集的的并集集使用UNION操作作符的的语法法如下下：select_statementUNION[ALL]select_statement[UNION[ALL]select_statement][...]其中，，select_statement是查查询的的SELECT语句句；ALL选项项表示示将所所有行行合并并到结结果集集中，，不指指定该该项，，则只只保留留重复复行中中的一一行。。UNION操操作符符含义义如图图8-1所所示。。29使使用用UNION操操作符符获取取两个个结果果集的的并集集【例8.30】使用UNIONALL操作作符，对scott用户的emp表进进行操作，，获得员工工编号大于于7800或者所在在部门编号号为10的的员工信息息。使用ORDERBY语语句将结果果集按照deptno列升序序排列输出出。具体如如下：参见教材P191【例8.31】使用UNION操作作符，但是是不指定ALL关键键字，获得得员工编号号大于7800或者者所在部门门编号为10的员工工信息。具具体如下：：参见教材P191308.3.7使用用INTERSECT操作符符获取两个个结果集的的交集使用INTERSECT操作作符，获取取结果集的的公共行，，也称为获获取结果集集的交集。。INTERSECT操作符符的使用语语法同UNION，，只是操作作符不再是是UNION[ALL]，，而是INTERSECT。。INTERSECT操作符的的含义如图图