第6章高级查询

1、北 京物 资学 院第第6章章 高级查询高级查询Oracle数据库管理本章概述本章概述p在第一章介绍设计数据库时，曾经介绍过为了在第一章介绍设计数据库时，曾经介绍过为了数据库的规范化，将数据表进行了分隔，这虽数据库的规范化，将数据表进行了分隔，这虽然损坏了信息的完整性，但消除了数据冗余、然损坏了信息的完整性，但消除了数据冗余、添入异常和删除异常。添入异常和删除异常。p在查询数据时，为了获取完整的信息就要将多在查询数据时，为了获取完整的信息就要将多个表连接起来。这也是关系数据库的一个重要个表连接起来。这也是关系数据库的一个重要特性特性表之间存关系。表之间存关系。p这种关系可以将两个表的数据联系在一

2、起。多这种关系可以将两个表的数据联系在一起。多表查询就是根据这种关系，实现从多个表中获表查询就是根据这种关系，实现从多个表中获取数据还原信息。取数据还原信息。Oracle数据库管理本章学习目标本章学习目标p理解什么是连接操作理解什么是连接操作p如何进行简单的连接操作如何进行简单的连接操作p使用表别名限定列使用表别名限定列p掌握如何进行内连接、外连接和交叉连接掌握如何进行内连接、外连接和交叉连接p理解结果集的并、交和差运算理解结果集的并、交和差运算p创建返回多行的子查询创建返回多行的子查询p创建返回单值的子查询创建返回单值的子查询p掌握嵌套子查询的使用掌握嵌套子查询的使用Oracle数据库管理本

3、章学习要点本章学习要点p掌握在掌握在WHERE子句中使用子查询。子句中使用子查询。p掌握在掌握在HAVING子句中使用子查询。子句中使用子查询。p熟练掌握使用熟练掌握使用IN、ANY和和ALL操作符实现子查询。操作符实现子查询。p熟练掌握关联子查询。熟练掌握关联子查询。p熟练掌握嵌套子查询。熟练掌握嵌套子查询。p掌握简单连接。掌握简单连接。p熟练掌握多个表之间的内连接。熟练掌握多个表之间的内连接。p熟练掌握多个表之间的外连接。熟练掌握多个表之间的外连接。p了解多个表之间的交叉连接。了解多个表之间的交叉连接。p掌握使用集合操作符实现集合查询。掌握使用集合操作符实现集合查询。Oracle数据库管理

4、本章内容本章内容p6.1 子查询子查询p6.2 实验演示实验演示获取工作经历次数获取工作经历次数最多的前最多的前5个人个人p6.3 高级查询高级查询Oracle数据库管理6.1 子查询子查询p在外部的在外部的SELECT、UPDATE或或DELETE语语句内部使用句内部使用SELECT语句，这个内部语句，这个内部SELECT语句称为子查询语句称为子查询(Subquery)。p使用子查询，主要是将子查询的结果作为外使用子查询，主要是将子查询的结果作为外部主查询的查找条件。部主查询的查找条件。Oracle数据库管理6.1 子查询子查询p6.1.1 子查询的类型子查询的类型p6.1.2 在在WHER

5、E子句中使用子查询子句中使用子查询p6.1.3 在在HAVING子句中使用子查询子句中使用子查询p6.1.4 使用使用IN操作符实现指定匹配查询操作符实现指定匹配查询p6.1.5 使用使用ANY操作符实现任意匹配查询操作符实现任意匹配查询p6.1.6 使用使用ALL操作符实现全部匹配查询操作符实现全部匹配查询p6.1.7 实现多列子查询实现多列子查询p6.1.8 实现关联子查询实现关联子查询p6.1.9 实现嵌套子查询实现嵌套子查询p6.1.10 在在UPDATE和和DELETE语句中使用子查询语句中使用子查询Oracle数据库管理p在子查询中可以使用两种比较操作符在子查询中可以使用两种比较操

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

7、STS操作符。操作符。嵌套子查询：在子查询中包含有子查询。嵌套子查询：在子查询中包含有子查询。p指定子查询时，需要注意以下几点：指定子查询时，需要注意以下几点：子查询需要使用括号子查询需要使用括号 ( ) 括起来。括起来。子查询要放在比较操作符的右边。子查询要放在比较操作符的右边。当子查询的返回值是一个集合而不是一个值时，不能使用单行操作当子查询的返回值是一个集合而不是一个值时，不能使用单行操作符，而必须根据需要使用符，而必须根据需要使用ANY、IN、ALL或或EXISTS等操作符。等操作符。6.1.1 子查询的类型子查询的类型Oracle数据库管理6.1.2 在在WHERE子句中使用子查询子

8、句中使用子查询p1使用子查询使用子查询在在WHERE子句中使用子查询的一般用法形式如子句中使用子查询的一般用法形式如下：下：SELECT column_list FROM table_name WHERE expression operator (SELECT column_name FROM table_name WHERE condition GROUP BY exp HAVING having);其中，在外部其中，在外部SELECT语句的语句的WHERE子句中，子句中，expression用来指定一个表达式，也可以是表中的用来指定一个表达式，也可以是表中的一列；一列；operator可以

9、是单行和多行操作符；可以是单行和多行操作符；()中的中的内容表示子查询内容。内容表示子查询内容。Oracle数据库管理6.1.2 在在WHERE子句中使用子查询子句中使用子查询p2不能使用不能使用ORDER BY子句子句在子查询的在子查询的SELECT语句中，可以使用语句中，可以使用FROM子句、子句、WHERE子句、子句、GROUP BY子句和子句和HAVING子句等，但是有些情况下子句等，但是有些情况下不能使用不能使用ORDER BY子句，例如在子句，例如在WHERE子句中使用子查询时，子查询语句子句中使用子查询时，子查询语句中就不能使用中就不能使用ORDER BY子句。子句。如果确实需要

10、使用如果确实需要使用ORDER BY子句对结果子句对结果进行排序，可以在外部查询语句中使用该进行排序，可以在外部查询语句中使用该子句。子句。Oracle数据库管理6.1.3 在在HAVING子句中使用子查询子句中使用子查询p在在SELECT语句中使用语句中使用HAVING子句，可以实现对数据进行子句，可以实现对数据进行分组过滤。在分组过滤。在HAVING子句中，如果使用子查询，那么就可子句中，如果使用子查询，那么就可以实现对子查询返回的结果根据分组进行过滤。以实现对子查询返回的结果根据分组进行过滤。p【例】对对scott用户的用户的emp表进行检索，在表进行检索，在HAVING子句中使子句中使

11、用子查询。获取哪些部门的员工平均工资小于全体员工的平用子查询。获取哪些部门的员工平均工资小于全体员工的平均工资。具体如下：均工资。具体如下：SELECT deptno , AVG(sal) FROM scott.emp GROUP BY deptno HAVING AVG(sal) 、 ( SELECT MAX(AVG(sal) FROM scott.emp WHERE deptno IN (SELECT deptno FROM scott.dept WHERE loc IN (NEW YORK , CHICAGO) ) GROUP BY deptno );Oracle数据库管理6.1.10

12、在在UPDATE和和DELETE语句中使用子查询语句中使用子查询p1在在UPDATE语句中使用子查询语句中使用子查询在在UPDATE语句中使用子查询，可以将子查询返回的结果赋值给需语句中使用子查询，可以将子查询返回的结果赋值给需要更新的列。要更新的列。【例】将员工编号为将员工编号为7839的员工的工资设置为平均工资，如下：的员工的工资设置为平均工资，如下：UPDATE scott.emp SET sal = ( SELECT AVG(sal) FROM scott.emp ) WHERE empno = 7839 ;p2在在DELETE语句中使用子查询语句中使用子查询在在DELETE语句中使用

13、子查询，可以根据子查询返回的结果删除指语句中使用子查询，可以根据子查询返回的结果删除指定的行。定的行。【例】删除工作地点在删除工作地点在NEW YORK的所有员工信息，如下：的所有员工信息，如下：DELETE FROM scott.emp WHERE deptno IN( SELECT deptno FROM scott.dept WHERE loc = NEW YORK);Oracle数据库管理6.2 实验演示实验演示获取工作经历次数最多的前获取工作经历次数最多的前5个人个人p实验演示：从实验演示：从hr.emloyees和和job_history表查询表查询先从表先从表employees中

14、进行检索基本信息；然后结合工作经中进行检索基本信息；然后结合工作经历表历表jot_history，获得每个人的工作经历；最后按照每个，获得每个人的工作经历；最后按照每个人的工作次数降序排列，获得前人的工作次数降序排列，获得前5个人的信息即可。具体个人的信息即可。具体如下：如下：Select * from employees where employee_id in (select employee_id from (select employee_id from job_history group by employee_id order by count(employee_id) desc)

15、 where rownum(大于大于)、=(大于等于大于等于)、=(小于等于小于等于)、(不等不等于于)、!=(不等于不等于)、LIKE、IN和和BETWEEN等。等。通过通过emp和和salgrade表，查询员工的工资等级。表，查询员工的工资等级。Oracle数据库管理6.3.3 使用使用INNER JOIN实现多个表的内连接实现多个表的内连接p3自然连接（在等值连接中把目标列中重复自然连接（在等值连接中把目标列中重复的属性列去掉）的属性列去掉）自然连接自然连接(NATURAL JOIN)是在两个表中寻找列是在两个表中寻找列名和数据类型都相同的字段，通过名和数据类型都相同的字段，通过相同的字

16、段相同的字段将将两个表连接在一起，并返回所有符合条件的结果。两个表连接在一起，并返回所有符合条件的结果。例例:使用使用INNER JOIN连接两个不同的表连接两个不同的表emp和和dept，ON来设置连接条件，使用来设置连接条件，使用WHERE子句限子句限制查询范围，检索制查询范围，检索ACCOUNTING部门的员工信部门的员工信息。息。Oracle数据库管理6.3.3 使用使用INNER JOIN实现多个表的内连接实现多个表的内连接p4使用使用USING关键字简化连接关键字简化连接SQL/92标准可以使用标准可以使用USING关键字来简化连接关键字来简化连接查询，但是只有在查询满足下面两个条

17、件时，才查询，但是只有在查询满足下面两个条件时，才能使用能使用USING关键字进行简化：关键字进行简化：查询必须是等值连接。查询必须是等值连接。等值连接中的列必须具有相同的名称和数据类型。等值连接中的列必须具有相同的名称和数据类型。Oracle数据库管理6.3.3 使用使用INNER JOIN实现多个表的内连接实现多个表的内连接p【例】使用使用USING关键字，重写前例中的语句关键字，重写前例中的语句.使用使用INNER JOIN连接两个不同的表连接两个不同的表emp和和dept，ON来设置连接条件，使用来设置连接条件，使用WHERE子句限制查询范围，检索子句限制查询范围，检索ACCOUNTI

18、NG部门的员工信息。部门的员工信息。使用使用USING关键字简化连接时，需要注意以下几点。关键字简化连接时，需要注意以下几点。(1)使用使用emp表和表和dept表中的表中的deptno列进行连接时，在列进行连接时，在USING子句和子句和SELECT子句中，都不能为子句中，都不能为deptno列指定表名或表别名。列指定表名或表别名。(2)如果在连接查询时使用了两个表中相同的多个列，那么就如果在连接查询时使用了两个表中相同的多个列，那么就可以在可以在USING子句中指定多个列名子句中指定多个列名.(3)如果对多个表进行检索，那么就必须多次使用如果对多个表进行检索，那么就必须多次使用USING关

19、键关键字进行指定字进行指定.Oracle数据库管理6.3.4 使用使用OUTER JOIN实现多个表的外连接实现多个表的外连接p对于外连接，对于外连接，Oracle中可以使用加号中可以使用加号(+)来表示，来表示，也可以使用也可以使用LEFT、RIGHT和和FULL OUTER JOIN关键字。关键字。p外连接可以分为下面这外连接可以分为下面这3类：类：左外连接左外连接(LEFT OUTER JOIN或或LEFT JOIN)右外连接右外连接(RIGHT OUTER JOIN或或RIGHT JOIN)全外连接全外连接(FULL OUTER JOIN或或FULL JOIN)Oracle数据库管理6

20、.3.4 使用使用OUTER JOIN实现多个表的外连接实现多个表的外连接p1左外连接左外连接左外连接是在检索结果中除了显示满足连接条件的行外，左外连接是在检索结果中除了显示满足连接条件的行外，还显示还显示JOIN关键字左侧表中所有满足检索条件的行。关键字左侧表中所有满足检索条件的行。使用左外连接，检索使用左外连接，检索emp和和salgrade表，获得员工的工资表，获得员工的工资等级。等级。为了显示效果，先向为了显示效果，先向emp表插入一行表插入一行sal小于小于700的记录。的记录。使用左连接查询：使用左连接查询： Select e.empno, e.ename, e.sal, d.gr

21、ade from scott.emp e LEFT OUTER JOIN scott.salgrade d ON e.sal BETWEEN d.losal and d.hisal等价于：等价于： Select e.empno, e.ename, e.sal, d.grade from scott.emp e , scott.salgrade d where e.sal BETWEEN d.losal and d.hisal(+)Oracle数据库管理6.3.4 使用使用OUTER JOIN实现多个表的外连接实现多个表的外连接p2右外连接右外连接右外连接是在结果中除了显示满足连接条件的行外，还

22、右外连接是在结果中除了显示满足连接条件的行外，还显示显示JOIN右侧表中所有满足检索条件的行。右侧表中所有满足检索条件的行。使用右外连接，检索使用右外连接，检索emp和和dept表所包含的部门编号。表所包含的部门编号。Select distinct e.deptno, d.deptno from scott.emp e RIGHT OUTER JOIN scott.dept d ON e.deptno=d.deptno等价于：等价于： Select distinct e.deptno, d.deptno from scott.emp e , scott.dept d where e.deptn

23、o=d.deptno(+)Oracle数据库管理6.3.4 使用使用OUTER JOIN实现多个表的外连接实现多个表的外连接p3全外连接全外连接全外连接是在结果中除了显示满足连接条件的行外，还全外连接是在结果中除了显示满足连接条件的行外，还显示显示JOIN两侧表中所有满足检索条件的行。两侧表中所有满足检索条件的行。如：使用全外连接，检索如：使用全外连接，检索emp和和dept表中所包含的部门表中所包含的部门编号编号Select distinct e.deptno,d.deptno from scott.emp e FULL OUTER JOIN scott.dept d ON e.deptno

24、=d.deptnoOracle数据库管理6.3.5 使用使用CROSS JOIN实现交叉连接实现交叉连接p使用使用CROSS JOIN关键字，可以实现两个表的交叉关键字，可以实现两个表的交叉连接，所得到的结果将是这两个表中各行数据的所连接，所得到的结果将是这两个表中各行数据的所有组合，即这两个表所有数据行的笛卡儿积。有组合，即这两个表所有数据行的笛卡儿积。p交叉连接与简单连接操作非常相似，不同的是，使交叉连接与简单连接操作非常相似，不同的是，使用交叉连接时，在用交叉连接时，在FROM子句中多个表名之间不是子句中多个表名之间不是用逗号，而是使用用逗号，而是使用CROSS JOIN关键字隔开。另外

25、，关键字隔开。另外，在交叉连接中不需要使用关键字在交叉连接中不需要使用关键字ON限定连接条件，限定连接条件，但是可以添加但是可以添加WHERE子句设置连接条件。子句设置连接条件。p【例】使用交叉连接，查询使用交叉连接，查询emp表和表和dept表中，部表中，部门编号为门编号为10的员工信息和部门信息的员工信息和部门信息.Oracle数据库管理6.3.6 使用使用UNION操作符获取两个结果集的并集操作符获取两个结果集的并集p使用使用UNION操作符的语法如下：操作符的语法如下：select_statement UNION ALL select_statement UNION ALL selec

26、t_statement . p其中，其中，select_statement是查询的是查询的SELECT语句；语句；ALL选项表示将所选项表示将所有行合并到结果集中，不指定该项，有行合并到结果集中，不指定该项，则只保留重复行中的一行。则只保留重复行中的一行。UNION操作符含义如左图所示。操作符含义如左图所示。Oracle数据库管理6.3.6 使用使用UNION操作符获取两个结果集的并集操作符获取两个结果集的并集p【例】使用使用UNION ALL操作符，对操作符，对scott用用户的户的emp表进行操作，获得员工编号大于表进行操作，获得员工编号大于7800或者所在部门编号为或者所在部门编号为10的员工信息