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

1、第8章子子查查询与高高级查询询本章要点点：掌握在WHERE子句句中使用用子查询询。掌握在HAVING子子句中使使用子查查询。熟练掌握握使用IN、ANY和和ALL操作符符实现子子查询。熟练掌握握关联子子查询。熟练掌握握嵌套子子查询。掌握简单单连接。熟练掌握握多个表表之间的的内连接接。熟练掌握握多个表表之间的的外连接接。了解多个个表之间间的交叉叉连接。掌握使用用集合操操作符实实现集合合查询。28.1子子查查询询在外部的的SELECT、UPDATE或DELETE语语句内部部使用SELECT语语句，这这个内部部SELECT语句称称为子查查询(Subquery)。使用子查查询，主主要是将将子查询询的结果

2、果作为外外部主查查询的查查找条件件。38.1.1子子查询询的类型型在子查询询中可以以使用两两种比较较操作符符单单行操作作符和多多行操作作符。单行操作作符：例例如=、=、=、!=。多行操作作符：例例如ALL、ANY、IN、EXISTS。可以把子子查询分分为两种种类型：单行子查查询和多多行子查查询。另外，子子查询还还有下面面3种子子类型，这3种种子类型型可以返返回一行行或多行行查询结结果。多列子查查询：向向外部的的SQL语句返返回多列列。关联子查查询：引引用外部部的SQL语句句中的一一列或多多列。在在关联子子查询中中，可以以使用EXISTS和和NOT EXISTS操作作符。嵌套子查查询：在在子查询

3、询中包含含有子查查询。指定子查查询时，需要注注意以下下几点：子查询需需要使用用括号() 括起起来。子查询要要放在比比较操作作符的右右边。当子查询询的返回回值是一一个集合合而不是是一个值值时，不不能使用用单行操操作符，而必须须根据需需要使用用ANY、IN、ALL或EXISTS等等操作符符。48.1.2在在WHERE子句中中使用子子查询1使用用子查询询在WHERE子子句中使使用子查查询的一一般用法法形式如如下：SELECTcolumn_listFROMtable_name WHERE expressionoperator(SELECTcolumn_nameFROMtable_name WHERE

4、conditionGROUPBYexpHAVING having);其中，在在外部SELECT语语句的WHERE子句句中，expression用来指指定一个个表达式式，也可可以是表表中的一一列；operator可以以是单行行和多行行操作符符；()中的内内容表示示子查询询内容。58.1.2在在WHERE子句中中使用子子查询2不能使使用ORDERBY子句在子查询询的SELECT语句中，可以使使用FROM子句、WHERE子句、GROUPBY子句和HAVING子句等，但是有有些情况况下不能能使用ORDERBY子句，例例如在WHERE子句中使使用子查查询时，子查询询语句中中就不能能使用ORDERBY子句

5、。68.1.3在在HAVING子句句中使用用子查询询在SELECT语句中中使用HAVING子子句，可可以实现现对数据据进行分分组过滤滤。在HAVING子子句中，如果使使用子查查询，那那么就可可以实现现对子查查询返回回的结果果根据分分组进行行过滤。【例8.3】对scott用用户的emp表表进行检检索，在在HAVING子句中中使用子子查询。获取哪哪些部门门的员工工平均工工资小于于全体员员工的平平均工资资。具体体如下：SQL SELECTdeptno,AVG(sal)FROM scott.empGROUPBYdeptno2HAVINGAVG(sal)、 SELECTempno, ename ,sal

6、 ,deptno FROMscott.emp2WHEREsal(3SELECT MAX(AVG(sal)FROMscott.empWHEREdeptno IN (4SELECTdeptnoFROMscott.dept5WHERE locIN(NEWYORK, CHICAGO)6GROUPBYdeptno );EMPNOENAMESALDEPTNO-7566JONES2975207788SCOTT3000207839KING5000107902FORD300020168.1.10在在UPDATE和和DELETE语句中中使用子子查询1在UPDATE语语句中使使用子查查询在UPDATE语句中中使用子

7、子查询，可以将将子查询询返回的的结果赋赋值给需需要更新新的列。【例8.17】将员工编编号为7839的员工工的工资资设置为为平均工工资，如如下：SQL UPDATEscott.empSET sal= (2SELECT AVG(sal) FROMscott.emp )3WHEREempno= 7839;已更新1行行。2在DELETE语语句中使使用子查查询在DELETE语句中中使用子子查询，可以根根据子查查询返回回的结果果删除指指定的行行。【例8.18】删除工作作地点在在NEW YORK的的所有员员工信息息，如下下：SQL DELETEFROM scott.empWHEREdeptnoIN(2SEL

8、ECT deptnoFROM scott.deptWHEREloc =NEWYORK);已删除3行。178.2实验指导导获取借阅阅次数最最多的前前5本图书实验指导导8-1：获取取借阅次次数最多多的前5本图书书由于需要要显示图图书信息息，所以以从图书书表book中中进行检检索；然然后结合合借书表表lend，获获得每本本书的借借阅情况况；最后后按照每每本书的的借阅次次数降序序排列，获得前前5本图图书信息息即可。具体如如下：参见教材材P182上述语句句中，在在最外层层SELECT语句的的WHERE子子句中使使用了子子查询，并且使使用IN操作符符指定匹匹配查询询；第3行的SELECT语语句中，在FRO

9、M子子句中使使用子查查询，通通过子查查询获得得分组和和排序之之后的bookid；最后在在WHERE子子句中指指定rownum列小小于等于于5，指指定排序序之后的的前5行行记录。188.3高高级级查查 询检索数据据时，通通过各个个表之间间共同列列的关联联性，可可以查询询存放在在多个表表中的不不同实体体的信息息。如果在查查询时需需要对多多个表进进行操作作，并且且指定多多个表的的连接关关系，则则该查询询就称为为高级查查询，也也可以称称为连接接查询。198.3.1使使用等等号(=)实现现多个表表的简单单连接在连接查查询中，如果仅仅仅通过过SELECT子句和和FROM子句句连接多多个表，那么查查询的结结

10、果将是是一个通通过笛卡卡儿积所所生成的的表。【例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】使

11、用表别别名的方方式，重重写例8.20中所示示的语句句。为scott.emp表表设置表表别名为为e，为为scott.dept表设设置表别别名为d。语句句如下：SQL SELECTempno, ename,sal,e.deptno, d.deptno ,dname2FROM scott.empe ,scott.deptd3WHEREe.deptno= d.deptno;218.3.3使使用INNERJOIN实现多多个表的的内连接接在FROM子句句中，使使用JOIN连连接的语语法形式式如下：FROM join_table1 join_typejoin_table2 ON (join_conditio

12、n) join_type.ONjoin_condition,.语法说明明如下。join_table1、join_table2：参参与连接接操作的的表名。join_type：连接类类型，连连接类型型有INNER JOIN(内连接接)、OUTERJOIN(外连连接)和和CROSSJOIN(交交叉连接接)。join_condition：连连接条件件，由被被连接表表中的列列和比较较运算符符、逻辑辑运算符符等构成成。可以以使用多多组join_type ON join_condition 子子句，实实现多个个表的连连接。228.3.3使使用INNERJOIN实现多多个表的的内连接接1等值值连接所谓等值值连

13、接，是指在在连接条条件中使使用等于于(=)运算符符比较被被连接的的值，也也就是通通过相等等的列值值连接起起来的查查询。2不等等连接所谓不等等连接，就是在在连接条条件中使使用除等等号(=)外的的其他比比较运算算符，构构成非等等值连接接查询。可以使使用的比比较运算算符包括括：(大于)、=(大于于等于)、=(小于于等于)、(不等等于)、!=(不等于于)、LIKE、IN和BETWEEN等等。238.3.3使使用INNERJOIN实现多多个表的的内连接接3自然然连接自然连接接(NATURALJOIN)是是在两个个表中寻寻找列名名和数据据类型都都相同的的字段，通过相相同的字字段将两两个表连连接在一一起，并

14、并返回所所有符合合条件的的结果。4使用用USING关关键字简简化连接接SQL/92标标准可以以使用USING关键键字来简简化连接接查询，但是只只有在查查询满足足下面两两个条件件时，才才能使用用USING关关键字进进行简化化：查询必须须是等值值连接。等值连接接中的列列必须具具有相同同的名称称和数据据类型。248.3.3使使用INNERJOIN实现多多个表的的内连接接【例8.25】使用USING关键字字，重写写例8.22中中的语句句，如下下：参见教材材P187使用USING关键字字简化连连接时，需要注注意以下下几点。(1)使使用emp表表和dept表表中的deptno列列进行连连接时，在USING

15、子句和和SELECT子句中中，都不不能为deptno列列指定表表名或表表别名。(2)如如果在在连接查查询时使使用了两两个表中中相同的的多个列列，那么么就可以以在USING子句中中指定多多个列名名，形式式如下：参见教材材P187(3)如如果对对多个表表进行检检索，那那么就必必须多次次使用USING关键键字进行行指定，形式如如下：参见教材材P187258.3.4使使用OUTERJOIN实现多多个表的的外连接接对于外连连接，Oracle中中可以使使用加号号(+)来表示示，也可可以使用用LEFT、RIGHT和FULL OUTER JOIN关关键字。外连接可可以分为为下面这这3类：左外连接接(LEFTO

16、UTERJOIN或LEFT JOIN)右外连接接(RIGHT OUTER JOIN或或RIGHTJOIN)全外连接接(FULLOUTERJOIN或FULL JOIN)268.3.4使使用OUTERJOIN实现多多个表的的外连接接1左外外连接左外连接接是在检检索结果果中除了了显示满满足连接接条件的的行外，还显示示JOIN关键键字左侧侧表中所所有满足足检索条条件的行行。2右外外连接右外连接接是在结结果中除除了显示示满足连连接条件件的行外外，还显显示JOIN右右侧表中中所有满满足检索索条件的的行。3全外外连接全外连接接是在结结果中除除了显示示满足连连接条件件的行外外，还显显示JOIN两两侧表中中所有

17、满满足检索索条件的的行。278.3.5使使用CROSSJOIN实现交交叉连接接使用CROSS JOIN关关键字，可以实实现两个个表的交交叉连接接，所得得到的结结果将是是这两个个表中各各行数据据的所有有组合，即这两两个表所所有数据据行的笛笛卡儿积积。交叉连接接与简单单连接操操作非常常相似，不同的的是，使使用交叉叉连接时时，在FROM子句中中多个表表名之间间不是用用逗号，而是使使用CROSS JOIN关关键字隔隔开。另另外，在在交叉连连接中不不需要使使用关键键字ON限定连连接条件件，但是是可以添添加WHERE子句设设置连接接条件。【例8.29】使用交叉叉连接，查询emp表表和dept表表中，部部门

18、编号号为10的员工工信息和和部门信信息，如如下：参见教材材P190288.3.6使使用UNION操作作符获取取两个结结果集的的并集使用UNION操作符符的语法法如下：select_statement UNION ALL select_statementUNIONALLselect_statement .其中，select_statement是查询询的SELECT语句句；ALL选项项表示将将所有行行合并到到结果集集中，不不指定该该项，则则只保留留重复行行中的一一行。UNION操作作符含义义如图8-1所所示。298.3.6使使用UNION操作作符获取取两个结结果集的的并集【例8.30】使用UNIO

19、N ALL操作作符，对对scott用用户的emp表表进行操操作，获获得员工工编号大大于7800或或者所在在部门编编号为10的员员工信息息。使用用ORDERBY语语句将结结果集按按照deptno列升升序排列列输出。具体如如下：参见教材材P191【例8.31】使用UNION操作符符，但是是不指定定ALL关键字字，获得得员工编编号大于于7800或者者所在部部门编号号为10的员工工信息。具体如如下：参见教材材P191308.3.7使使用INTERSECT操操作符获获取两个个结果集集的交集集使用INTERSECT操作作符，获获取结果果集的公公共行，也称为为获取结结果集的的交集。INTERSECT操作符符的使用用语法同同UNION，只是操操作符不不再是UNIONALL，而而是INTERSECT。INTERSECT操操作符的的含义如如图8-2所示示。318.3.8使使用MINUS操作作符获取取两个结结果集的的差集SQL语语言中的的MINUS集集合运算算，表示示获得给给定集合合之间的的差异，也就意意味着所所得到的的结果集集中，其其中的元元素仅存存在于前前一个集集合中，而不存存在于另另