精通Oracle核心技术和项目实战之高级查询

1、第8章 高级查询我们已经初步了解了数据表的基础查询。但是，在实际的数据库操作中，往往需要同时从来自两个或两个以上的表中查询相关数据，连接就是满足这些需求的技术。本章将主要介绍子查询、联合语句、单表关联语句及多表查询的相关使用。8.1 连接简介上一章的查询语句都只涉及一个表的数据。在很多情况下，需要查询的数据往往涉及多个表，这时需要对多个表进行连接查询。通过连接，可以根据各个表之间的逻辑关系从两个或多个表中检索数据。如，既要查询订单的信息，又要查询订单所在城市的信息，这就涉及orders和customersnew两个表。8.1.1 连接定义多表连接实际上就是实现如何使用一个表中的数据来选择另一个

2、表中的行。而连接条件则主要通过以下方法定义两个表在查询中的关联方式：指定每个表中要用于连接的列，典型的连接条件是在一个表中指定外键，在另一个表中指定与其关联的键。指定比较各列的值时要使用的逻辑运算符（=、等）。在select语句中实现多表连接，并不是一个物理存在的实体。换句话说，它在数据表中并不存在，它只是由数据库系统在需要时创建的，只在查询、检索数据期间有效。8.1.2 连接的类型连接体现在where子句中指定的条件上，在条件中要指定两个表通过哪些列进行连接。一般情况下进行连接查询的两个表是通过主键和外键进行关联的，所以最简单的条件是一个表的外键与另一个表的主键相等。在SQL中，表的连接类型

3、主要有下面几种。内部连接（inner join）外部连接（outer join）交叉连接（cross join）8.1.3 简单的多表查询表的连接实现最简单的方法就是在select语句的from子句中，罗列要连接的表即可。而要进一步实现复杂的多表连接，则需要在from子句中使用join（inner join、outer join、cross join等）关键词，以后的章节将详细讲解。8.1.3 简单的多表查询1.如何实现多表查询实现多表查询，可以在from子句中通过join关键词连接需要查询的表。而简单的多表查询，则可以不使用join关键词，而直接列出所要连接的表，利用逗号把from子句后的表

4、名隔开。【示例8-1】使用from子句直接实现product_information和categories两个表的连接。8.1.3 简单的多表查询2.指定连接条件直接使用from子句连接表，返回的是两个表的记录的笛卡尔积，这在实际应用中意义不大。在实际应用中，通常需要连接的两个表的记录满足一定的关系。这时，可以在select语句的where子句中指定连接条件。【示例8-2】使用where子句指定product_information和categories两个表的连接条件。8.1.3 简单的多表查询3.使用别名作为表名的简写在上面的示例中，select语句中有关列名均使用了“product_in

5、formation.category_id”的完整表达式。在连接的两个表中，如果不是相同的列名，可以省略表名；对于相同的列名，在使用时必须指明其对应的表名。而使用表名比较繁琐，我们可以为表定义别名。【示例8-3】使用别名简化product_information和categories两个表的连接。8.1.3 简单的多表查询4.自连接在查询信息时，有时需要将表与其自身进行连接，即自连接，这时就需要为表定义别名。【示例8-4】从customersnew表中查询赊销限额credit_limit比顾客编号839大的所有顾客信息。8.2 表的连接查询在关系型数据库中实现多表之间的联系，我们引入了连接查询

6、，连接用于指定多数据源（表、视图）之间如何组合，以形成最终的数据源。前面介绍的表的连接，是通过from子句列出所要连接的表，结合使用where子句列出连接条件来实现。本节将主要为大家介绍使用关键字join实现表的连接，主要包括自然连接、内连接，外连接。使用join关键字有助于将连接操作与where的搜索条件分开。8.2.1 自然连接自然连接将两个数据源中具有相同名称的列进行连接。在自然连接中，用户不需要明确指定进行连接的列，系统会自动完成这一任务。连接后相同的列只保留一个。如现在存在两个表：R表和S表，这两个表存在相同的列是B列。如果将这两个表进行自然连接，连接的具体情况如图所示。8.2.1

7、自然连接8.2.1 自然连接自然连接使用natrual join关键字。语法如图所示。【示例8-5】在示例数据库中，product_information和categories都包含category_id这列，现在我们把这两个表进行自然连接。8.2.2 内连接内连接也称为简单连接，它会把两个或多个表进行连接，最后只能查询出匹配的记录，不匹配的记录将无法查询出来。理解如图所示。8.2.2 内连接8.2.2 内连接这种连接查询是平时最常用的查询。语法如图所示。【示例8-6】在示例数据库中，orders数据表存储的是订单信息，这时如果想知道订单者的联系方式phone_number就需要连接表cust

8、omersnew。8.2.3 外连接外连接分为左外连接、右外连接、全外连接。使用左外连接查询，返回的结果不仅仅是符合连接条件的行记录，还包含了左边表中的全部记录，右外连接与左外连接相反。全外连接返回所有匹配成功的记录，还包含了左、右表中未匹配成功的记录。理解如图所示。8.2.3 外连接8.2.3 外连接1.左外连接左外连接又称左向外连接，也就是说如果左表的某行记录在右表中没有匹配项，则在返回结果中右表的所有选择列表均为空，左外连接使用语法如图所示。【示例8-7】左外连接连接product_information、categories数据表，显示产品编号、产品分类编号、分类名称。8.2.3 外连

9、接2.右外连接右外连接又称右向外连接，与左外连接相反，将右边表中所有的数据与左表进行匹配，返回的结果显示出了匹配成功的记录，还包含右表中未匹配成功的记录，并在其左表对应列补空值。右外连接使用语法如图所示。【示例8-8】右外连接连接product_information、categories数据表，显示产品编号、产品分类编号、分类名称。8.2.3 外连接3.全外连接返回所有匹配成功的记录，并返回左表未匹配成功的记录，也返回右表未匹配成功的记录。语法如图所示。【示例8-9】全外连接连接product_information、categories数据表，显示产品编号、产品分类编号、分类名称。【示例8

10、-10】利用外连接的简略写法来改写示例8-8。8.3 单表中的联合语句联合语句是指两个或多个select语句是并列关系，并且对这些select语句所捕获的记录集进行集合操作，以获得最终的结果集。这些联合语句包括以下几种：union查询、union all查询、intersect查询和minus查询。8.3.1 union查询该操作符用于取得两个查询结果集的并集。理解如图所示。当使用该操作符时，会自动去掉结果集中重复行。语法结构如图所示。8.3.1 union查询【示例8-11】我们使用关键字union，显示customersnew表中，cust_first_name是Bruce Margret

11、和city是Roma的顾客编号、顾客姓名、所在城市。8.3.2 union all查询该操作符与union相似，但是它不会取消重复行，不会排序。理解如图所示。8.3.2 union all查询【示例8-12】我们使用关键字union all，显示customersnew表中，cust_first_name是Bruce或Margret和city是Roma的顾客编号、顾客姓名、所在城市。8.3.3 intersect查询intersect操作符用于两个查询结果的交集，理解如图所示。【示例8-13】我们使用关键字intersect，显示customersnew表中，cust_first_name是M

12、argret和city又是Roma的顾客编号、顾客姓名、所在城市。8.3.4 minus查询minus集合运算可以查询两个给定集合之间的差集，理解如图所示。【示例8-14】我们使用关键字minus，显示customersnew表中credit_limit在30005000之间的顾客信息。8.4 子查询子查询是指嵌套在查询语句中的查询语句。子查询出现的位置一般为条件语句，如where条件，它本质上是where后的一个条件表达式。Oracle会首先执行子查询，然后执行父查询。8.4.1 理解子查询子查询是嵌入在其他SQL语句中的select语句，也叫嵌套查询。子查询首先生成结果集，并将结果集应用于

13、条件语句。子查询可以出现在插入、查询、更新和删除语句中。建立子查询的目的在于更加有效的限制where子句中的条件，并可以将复杂的查询逻辑梳理的更加清晰。子查询与父查询关系情况如图所示。8.4.2 子查询使用在select语句中，where子句或者having子句中的条件往往不能用一个确定的表达式来确定，而要依赖于另一个查询，这个被嵌套使用的查询就是子查询。子查询返回单行。查询语法如图所示。8.4.3 子查询的使用方式及限制在实际应用中，子查询一般出现在where子句中，包含子查询的语句通常采用图中所列格式中的一种。8.4.3 子查询的使用方式及限制1.比较运算符引入子查询凡是表达式可以出现的地

14、方几乎都可以使用子查询，只是SQL对查询的结果施加了某些限制。即子查询用在比较运算符之后的表达式中，该子查询必须返回单值。【示例8-15】子查询显示顾客编号customer_id是101的顾客所定产品的折扣价。【示例8-16】子查询显示顾客编号customer_id是101并且sales_rep_id是159的顾客所定产品的折折扣价。8.4.3 子查询的使用方式及限制【示例8-17】利用子查询，从customersnew表中查询赊销限额credit_limit比顾客编号为839大的所有顾客信息。8.4.3 子查询的使用方式及限制2.子查询中的聚合函数之前在讲解基本查询时，我们可以在查询结果中包

15、括聚合函数，在这里也可以将聚合函数作为where子句的搜索条件的一部分得到聚合函数返回的值。聚合函数返回的是单值，可以在比较运算符引入子查询。【示例8-18】查询产品单价unit_price高于平均价格并且line_item_id为1的订单的order_id、ine_item_id、unit_price的详细信息。【示例8-19】查询各个品种的订单产品的产品单价unit_price高于平均价格的订单的order_id、ine_item_id、unit_price的详细信息。8.4.3 子查询的使用方式及限制2.在子查询中使用in、all关键字对于在子查询返回多个值的情况，可以使用in关键字。当

16、使用in运算符引入子查询时，就是对子查询集合成员测试，即把源表中的列值与子查询的返回结果进行比较，如果列值与返回结果集中的列数据值之一匹配，那么in判别式求值为true，查询结果就包含这行数据。【示例8-20】使用子查询，结合in关键字，显示顾客编号customer_id是101的顾客所定产品的折扣价。8.4.3 子查询的使用方式及限制【示例8-21】我们使用关键字all，在数据表customersnew中查询显示credit_limit比country为IT的所有的顾客的credit_limit高的customer_id、cust_first_name、和country。【示例8-22】我们

17、使用关键字any，在数据表customersnew中查询，显示credit_limit比country为IT的顾客的credit_limit高的customer_id、cust_first_name、和country。8.4.3 子查询的使用方式及限制3.在子查询中使用exists关键字在某些情况下，只需要查询返回true或false，子查询的数据本身并不重要。这时，就可以使用exists判别式来引入子查询。使用exists引入子查询时，就相当于进行一次存在性测试。【示例9-23】exists往往可以作为in判式的替代语法。结合表order_items、orders查询顾客编号customer

18、_id是101的顾客所定产品的折扣价信息。表order_items测试哪些订单号在该集合中。8.4.3 子查询的使用方式及限制在子查询过程中，常用的运算符如表所示，表中集合S为子查询返回的结果。运算符运算符用法用法说明说明existsexists如果集合如果集合S部位空，则条件表达式为真，否则为假部位空，则条件表达式为真，否则为假in表达式 in S如果表达式的值在集合如果表达式的值在集合S中，则条件表达式为真，否中，则条件表达式为真，否则为假则为假=表达式=S如果表达式的值与集合如果表达式的值与集合S中的唯一一个元素相等，则中的唯一一个元素相等，则条件表达式为真，否则为假。集合条件表达式为真

19、，否则为假。集合S中最多只有一个中最多只有一个元素元素 =与“=”相同进行相应的关系运算进行相应的关系运算any用在集合之前指定要与集合中的任一个元素进行比较指定要与集合中的任一个元素进行比较all用在集合之前用在集合之前指定要与集合中的所有元素进行比较指定要与集合中的所有元素进行比较8.5 查询语句优化所谓语句优化是在原来的语句基础上为了是语句能够更快速地得到结果的方法。在查询语句中，每一条SQL语句对数据库的操作，产生结果的时间也不同。即使是完成同一功能的SQL语句，最后的响应时间也是不同的。本小节主要讲解查询语句优化中的使用技巧。8.5.1 在查询中尽量不使用“*”在数据库的查询操作中，操作着最常用的查询某一个数据表的全部记录，此时，需要使用“*”。在Oracle数据库中，使用“*”后，会浪费很多查询时间，所以，在查询语句中使用“*”时，要考虑是否有字段是不需要的。如果有不需要的字段就要用指定列名的方式查询，这样就可以提高查询效率。8.5.2 多表查询中尽量使用表别名在查询中使用别名是比较广泛的，在Oracle数据库中设置表的别名方式主要有两种，如图所示。8.5.3 条件查询多使用where在SQL语句中指定查询条件时主要有两个