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表进行操作，获得工资大于所有部门的平均工资的员工信息，如下：参见教材P176108.1.7实现现多列子查查询多列子查询询则是指返返回多列数数据的子查查询语句。。当多列子子查询返回回单行数据据时，在WHERE子句中可可以使用单单行操作符符；返回多多行数据时时，在WHERE子子句中必须须使用多行行操作符。。使用子查询询比较多个个列的数据据时，可以以使用下面面两种方式式。成对比较：：要求多个个列的数据据必须同时时匹配。非成对比较较：通过指指定连接关关键字，例例如AND或OR等等，指定多多个列的数数据是否必必须同时匹匹配。如果果使用AND关键字字，表示同同时匹配，，这样就可可以实现与与成对比较较同样的结结果；如果果使用OR关键字，，表示不必必同时匹配配。118.1.7实现现多列子查查询【例8.9】对scott用户的的emp表表进行操作作，使用成成对比较的的方式，获获得每个部部门中工资资最低的员员工信息，，如下：参见教材P177【例8.10】使用非成对对比较的方方式，并且且指定AND关键字字，获得每每个部门中中工资最低低的员工信信息，如下下：参见教材P177128.1.8实现现关联子查查询关联子查询询会引用外外部查询中中的一列或或多列，这这种子查询询之所以被被称为关联联子查询，，是因为它它的确与外外部语句相相关。【例8.11】查询各个部部门中，哪哪些员工的的工资低于于其所在部部门的平均均工资，如如下：参见教材P178138.1.8实现现关联子查查询1．使用EXISTS操作符符在关联子查查询中可以以使用EXISTS或NOTEXISTS操操作符。其其中，EXISTS操作符用用于检查子子查询所返返回的行是是否存在，，它可以在在非关联子子查询中使使用，但是是更常用于于关联子查查询。2．使用NOTEXISTS操作符符在执行的操操作逻辑上上，NOTEXISTS操操作符的作作用与EXISTS操作符相相反。在需需要检查数数据行中是是否不存在在子查询返返回的结果果时，就可可以使用NOTEXISTS。148.1.8实现现关联子查查询3．EXISTS与与IN的比比较在使使用用NOTEXISTS和和NOTIN时时，，如如果果一一个个值值列列表表中中包包含含有有空空值值，，NOTEXISTS返返回回TRUE；；而而NOTIN则则返返回回FALSE。。【例例8.14】】使用用NOTEXISTS操操作作符符，，检检索索最最高高领领导导人人员员的的有有关关信信息息，，也也就就是是该该员员工工没没有有上上级级领领导导，，对对应应的的mgr列列的的值值为为NULL。。具具体体如如下下：参见见教教材材P179【例例8.15】】使用用NOTIN操操作作符符，，重重写写上上述述语语句句，，如如下下：：参见见教教材材P180158.1.9实实现现嵌嵌套套子子查查询询所谓谓嵌嵌套套子子查查询询，，是是指指在在子子查查询询内内部部使使用用其其他他子子查查询询。。嵌嵌套套子子查查询询的的嵌嵌套套层层次次最最多多为为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(2SELECTdeptnoFROMscott.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高高级查查询检索数据时时，通过各各个表之间间共同列的的关联性，，可以查询询存放在多多个表中的的不同实体体的信息。。如果在查询时时需要对多个个表进行操作作，并且指定定多个表的连连接关系，则则该查询就称称为高级查询询，也可以称称为连接查询询。198.3.1使使用等号号(=)实现现多个表的简简单连接在连接查询中中，如果仅仅仅通过SELECT子句句和FROM子句连接多多个表，那么么查询的结果果将是一个通通过笛卡儿积积所生成的表表。【例8.19】使用SELECT子句和和FROM子子句，从scott用户户的emp表表和dept表中检索数数据，如果不不指定检索条条件，将得到到56行记录录。具体如下下：参见教材P183由于scott.emp表中有14行记录，scott.dept表表中有4行记记录，所以笛笛卡儿积所生生成的表一共共有56(14*4=56)行记录。。208.3.2使使用表的的别名设置表的别名名，只需要在在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;218.3.3使使用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……子句，实现现多个表的连连接。228.3.3使使用INNERJOIN实现现多个表的内内连接1．等值连接接所谓等值连接接，是指在连连接条件中使使用等于(=)运算符比比较被连接的的值，也就是是通过相等的的列值连接起起来的查询。。2．不不等连连接所谓不不等连连接，，就是是在连连接条条件中中使用用除等等号(=)外的的其他他比较较运算算符，，构成成非等等值连连接查查询。。可以以使用用的比比较运运算符符包括括：>(大大于)、<(小小于)、>=(大于于等于于)、、<=(小小于等等于)、<>(不等等于)、!=(不等等于)、LIKE、、IN和BETWEEN等。。238.3.3使使用用INNERJOIN实实现现多多个个表表的的内内连连接接3．．自自然然连连接接自然然连连接接(NATURALJOIN)是是在在两两个个表表中中寻寻找找列列名名和和数数据据类类型型都都相相同同的的字字段段，，通通过过相相同同的的字字段段将将两两个个表表连连接接在在一一起起，，并并返返回回所所有有符符合合条条件件的的结结果果。。4．使用USING关键键字简化连接接SQL/92标准可以使使用USING关键字来来简化连接查查询，但是只只有在查询满满足下面两个个条件时，才才能使用USING关键键字进行简化化：查询必须是等等值连接。等值连接中的的列必须具有有相同的名称称和数据类型型。248.3.3使使用INNERJOIN实现现多个表的内内连接【例8.25】使用USING关键字，，重写例8.22中的语语句，如下：：参见教材P187使用USING关键字简简化连接时，，需要注意以以下几点。(1) 使用用emp表和和dept表表中的deptno列进进行连接时，，在USING子句和SELECT子句中，都都不能为deptno列列指定表名或或表别名。(2) 如果果在连接查询询时使用了两两个表中相同同的多个列，，那么就可以以在USING子句中指指定多个列名名，形式如下下：参见教材P187(3) 如果果对多个表进进行检索，那那么就必须多多次使用USING关键键字进行指定定，形式如下下：参见教材P187258.3.4使使用OUTERJOIN实现现多个表的外外连接对于外连接，，Oracle中可以使使用加号(+)来表示，，也可以使用用LEFT、、RIGHT和FULLOUTERJOIN关键字。。外连接可以分分为下面这3类：左外连接(LEFTOUTERJOIN或或LEFTJOIN)右外连接(RIGHTOUTERJOIN或RIGHTJOIN)全外连接(FULLOUTERJOIN或或FULLJOIN)268.3.4使使用OUTERJOIN实现现多个表的外外连接1．左外连接接左外连接是在在检索结果中中除了显示满满足连接条件件的行外，还还显示JOIN关键字左左侧表中所有有满足检索条条件的行。2．右外连接接右外连接是在在结果中除了了显示满足连连接条件的行行外，还显示示JOIN右右侧表中所有有满足检索条条件的行。3．全外连接接全外连接是在在结果中除了了显示满足连连接条件的行行外，还显示示JOIN两两侧表中所有有满足检索条条件的行。278.3.5使使用CROSSJOIN实现现交叉连接使用CROSSJOIN关键字，，可以实现两两个表的交叉叉连接，所得得到的结果将将是这两个表表中各行数据据的所有组合合，即这两个个表所有数据据行的笛卡儿儿积。交叉连接与简简单连接操作作非常相似，，不同的是，，使用交叉连连接时，在FROM子句句中多个表名名之间不是用用逗号，而是是使用CROSSJOIN关键字字隔开。另外外，在交叉连连接中不需要要使用关键字字ON限定连连接条件，但但是可以添加加WHERE子句设置连连接条件。【例8.29】使用交叉连接接，查询emp表和dept表中，，部门编号为为10的员工工信息和部门门信息，如下下：参见教材P190288.3.6使使用UNION操作作符获取两个个结果集的并并集使用UNION操作符的的语法如下：：select_statementUNION[ALL]select_statement[UNION[ALL]select_statement][...]其中，select_statement是查查询的SELECT语句句；ALL选选项表示将所所有行合并到到结果集中，，不指定该项项，则只保留留重复行中的的一行。UNION操作作符含义如图图8-1所示示。298.3.6使使用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