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

第8章高级查询我们已经初步了解了数据表的基础查询。但是，在实际的数据库操作中，往往需要同时从来自两个或两个以上的表中查询相关数据，连接就是满足这些需求的技术。本章将主要介绍子查询、联合语句、单表关联语句及多表查询的相关使用。8.1连接简介上一章的查询语句都只涉及一个表的数据。在很多情况下，需要查询的数据往往涉及多个表，这时需要对多个表进行连接查询。通过连接，可以根据各个表之间的逻辑关系从两个或多个表中检索数据。如，既要查询订单的信息，又要查询订单所在城市的信息，这就涉及orders和customersnew两个表。8.1.1连接定义多表连接实际上就是实现如何使用一个表中的数据来选择另一个表中的行。而连接条件则主要通过以下方法定义两个表在查询中的关联方式：指定每个表中要用于连接的列，典型的连接条件是在一个表中指定外键，在另一个表中指定与其关联的键。指定比较各列的值时要使用的逻辑运算符（=、<>等）。在select语句中实现多表连接，并不是一个物理存在的实体。换句话说，它在数据表中并不存在，它只是由数据库系统在需要时创建的，只在查询、检索数据期间有效。8.1.2连接的类型连接体现在where子句中指定的条件上，在条件中要指定两个表通过哪些列进行连接。一般情况下进行连接查询的两个表是通过主键和外键进行关联的，所以最简单的条件是一个表的外键与另一个表的主键相等。在SQL中，表的连接类型主要有下面几种。内部连接（innerjoin）外部连接（outer

join）交叉连接（cross

join）8.1.3简单的多表查询表的连接实现最简单的方法就是在select语句的from子句中，罗列要连接的表即可。而要进一步实现复杂的多表连接，则需要在from子句中使用join（innerjoin、outer

join、cross

join等）关键词，以后的章节将详细讲解。简单的多表查询1.如何实现多表查询实现多表查询，可以在from子句中通过join关键词连接需要查询的表。而简单的多表查询，则可以不使用join关键词，而直接列出所要连接的表，利用逗号把from子句后的表名隔开。【示例8-1】使用from子句直接实现product_information和categories两个表的连接。简单的多表查询2.指定连接条件直接使用from子句连接表，返回的是两个表的记录的笛卡尔积，这在实际应用中意义不大。在实际应用中，通常需要连接的两个表的记录满足一定的关系。这时，可以在select语句的where子句中指定连接条件。【示例8-2】使用where子句指定product_information和categories两个表的连接条件。简单的多表查询3.使用别名作为表名的简写在上面的示例中，select语句中有关列名均使用了“product_information.category_id”的完整表达式。在连接的两个表中，如果不是相同的列名，可以省略表名；对于相同的列名，在使用时必须指明其对应的表名。而使用表名比较繁琐，我们可以为表定义别名。【示例8-3】使用别名简化product_information和categories两个表的连接。简单的多表查询4.自连接在查询信息时，有时需要将表与其自身进行连接，即自连接，这时就需要为表定义别名。【示例8-4】从customersnew表中查询赊销限额credit_limit比顾客编号839大的所有顾客信息。8.2表的连接查询在关系型数据库中实现多表之间的联系，我们引入了连接查询，连接用于指定多数据源（表、视图）之间如何组合，以形成最终的数据源。前面介绍的表的连接，是通过from子句列出所要连接的表，结合使用where子句列出连接条件来实现。本节将主要为大家介绍使用关键字join实现表的连接，主要包括自然连接、内连接，外连接。使用join关键字有助于将连接操作与where的搜索条件分开。8.2.1自然然连连接接自然然连连接接将将两两个个数数据据源源中中具具有有相相同同名名称称的的列列进进行行连连接接。。在在自自然然连连接接中中，，用用户户不不需需要要明明确确指指定定进进行行连连接接的的列列，，系系统统会会自自动动完完成成这这一一任任务务。。连连接接后后相相同同的的列列只只保保留留一一个个。。如现现在在存存在在两两个个表表：：R表和和S表，，这这两两个个表表存存在在相相同同的的列列是是B列。。如如果果将将这这两两个个表表进进行行自自然然连连接接，，连连接接的的具具体体情情况况如如图图所所示示。。8.2.1自然然连连接接自然然连连接接自然然连连接接使使用用natrualjoin关键键字字。。语语法法如如图图所所示示。。【示例例8-5】】在示示例例数数据据库库中中，，product_information和categories都包包含含category_id这列列，，现现在在我我们们把把这这两两个个表表进进行行自自然然连连接接。。内连连接接内连连接接也也称称为为简简单单连连接接，，它它会会把把两两个个或或多多个个表表进进行行连连接接，，最最后后只只能能查查询询出出匹匹配配的的记记录录，，不不匹匹配配的的记记录录将将无无法法查查询询出出来来。。理理解解如如图图所所示示。。内连连接接内连连接接这种种连连接接查查询询是是平平时时最最常常用用的的查查询询。。语语法法如如图图所所示示。。【示例例8-6】】在示示例例数数据据库库中中，，orders数据据表表存存储储的的是是订订单单信信息息，，这这时时如如果果想想知知道道订订单单者者的的联联系系方方式式phone_number就需需要要连连接接表表customersnew。外连连接接外连连接接分分为为左左外外连连接接、、右右外外连连接接、、全全外外连连接接。。使使用用左左外外连连接接查查询询，，返返回回的的结结果果不不仅仅仅仅是是符符合合连连接接条条件件的的行行记记录录，，还还包包含含了了左左边边表表中中的的全全部部记记录录，，右右外外连连接接与与左左外外连连接接相相反反。。全全外外连连接接返返回回所所有有匹匹配配成成功功的的记记录录，，还还包包含含了了左左、、右右表表中中未未匹匹配配成成功功的的记记录录。。理理解解如如图图所所示示。。外连连接接外连连接接1.左外外连连接接左外外连连接接又又称称左左向向外外连连接接，，也也就就是是说说如如果果左左表表的的某某行行记记录录在在右右表表中中没没有有匹匹配配项项，，则则在在返返回回结结果果中中右右表表的的所所有有选选择择列列表表均均为为空空，，左左外外连连接接使使用用语语法法如如图图所所示示。。【示例例8-7】】左外外连连接接连连接接product_information、categories数据据表表，，显显示示产产品品编编号号、、产产品品分分类类编编号号、、分分类类名名称称。。外连连接接2.右外外连连接接右外外连连接接又又称称右右向向外外连连接接，，与与左左外外连连接接相相反反，，将将右右边边表表中中所所有有的的数数据据与与左左表表进进行行匹匹配配，，返返回回的的结结果果显显示示出出了了匹匹配配成成功功的的记记录录，，还还包包含含右右表表中中未未匹匹配配成成功功的的记记录录，，并并在在其其左左表表对对应应列列补补空空值值。。右右外外连连接接使使用用语语法法如如图图所所示示。。【示例例8-8】】右外外连连接接连连接接product_information、categories数据据表表，，显显示示产产品品编编号号、、产产品品分分类类编编号号、、分分类类名名称称。。外连连接接3.全外外连连接接返回回所所有有匹匹配配成成功功的的记记录录，，并并返返回回左左表表未未匹匹配配成成功功的的记记录录，，也也返返回回右右表表未未匹匹配配成成功功的的记记录录。。语语法法如如图图所所示示。。【示例8-9】全外连连接连连接product_information、categories数据表表，显显示产产品编编号、、产品品分类类编号号、分分类名名称。。【示例8-10】】利用外外连接接的简简略写写法来来改写写示例例8-8。8.3单表中中的联联合语语句联合语语句是是指两两个或或多个个select语句是是并列列关系系，并并且对对这些些select语句所所捕获获的记记录集集进行行集合合操作作，以以获得得最终终的结结果集集。这这些联联合语语句包包括以以下几几种：：union查询、、unionall查询、、intersect查询和和minus查询。。查询该操作作符用用于取取得两两个查查询结结果集集的并并集。。理解解如图图所示示。当使用用该操操作符符时，，会自自动去去掉结结果集集中重重复行行。语语法结结构如如图所所示。。查询【示例8-11】】我们使使用关关键字字union，显示示customersnew表中，，cust_first_name是BruceMargret和city是Roma的顾客客编号号、顾顾客姓姓名、、所在在城市市。查询该操作作符与与union相似，，但是是它不不会取取消重重复行行，不不会排排序。。理解解如图图所示示。查询【示例8-12】】我们使使用关关键字字unionall，显示示customersnew表中，，cust_first_name是Bruce或Margret和city是Roma的顾客客编号号、顾顾客姓姓名、、所在在城市市。查询intersect操作符符用于于两个个查询询结果果的交交集，，理解解如图图所示示。【示例8-13】】我们使使用关关键字字intersect，显示示customersnew表中，，cust_first_name是Margret和city又是Roma的顾客客编号号、顾顾客姓姓名、、所在在城市市。查询minus集合运运算可可以查查询两两个给给定集集合之之间的的差集集，理理解如如图所所示。。【示例8-14】】我们使使用关关键字字minus，显示示customersnew表中credit_limit在3000~5000之间的的顾客客信息息。8.4子查询询子查询询是指指嵌套套在查查询语语句中中的查查询语语句。。子查查询出出现的的位置置一般般为条条件语语句，，如where条件，，它本本质上上是where后的一一个条条件表表达式式。Oracle会首先先执行行子查查询，，然后后执行行父查查询。。理解子子查询询子查询询是嵌嵌入在在其他他SQL语句中中的select语句，，也叫叫嵌套套查询询。子子查询询首先先生成成结果果集，，并将将结果果集应应用于于条件件语句句。子子查询询可以以出现现在插插入、、查询询、更更新和和删除除语句句中。。建立立子查查询的的目的的在于于更加加有效效的限限制where子句中中的条条件，，并可可以将将复杂杂的查查询逻逻辑梳梳理的的更加加清晰晰。子子查询询与父父查询询关系系情况况如图图所示示。子查询询使用用在select语句中中，where子句或或者having子句中中的条条件往往往不不能用用一个个确定定的表表达式式来确确定，，而要要依赖赖于另另一个个查询询，这这个被被嵌套套使用用的查查询就就是子子查询询。子子查询询返回回单行行。查查询语语法如如图所所示。。子查询询的使使用方方式及及限制制在实际际应用用中，，子查查询一一般出出现在在where子句中中，包包含子子查询询的语语句通通常采采用图图中所所列格格式中中的一一种。。子查询询的使使用方方式及及限制制1.比较运运算符符引入入子查查询凡是表表达式式可以以出现现的地地方几几乎都都可以以使用用子查查询，，只是是SQL对查询询的结结果施施加了了某些些限制制。即即子查查询用用在比比较运运算符符之后后的表表达式式中，，该子子查询询必须须返回回单值值。【示例8-15】】子查询询显示示顾客客编号号customer_id是101的顾客客所定定产品品的折扣扣价。【示例8-16】】子查询询显示示顾客客编号号customer_id是101并且sales_rep_id是159的顾客客所定定产品品的折折折扣扣价。。子查询询的使使用方方式及及限制制【示例8-17】】利用子子查询询，从从customersnew表中查查询赊赊销限限额credit_limit比顾客客编号号为839大的所所有顾顾客信信息。。子查询询的使使用方方式及及限制制2.子查询询中的的聚合合函数数之前在在讲解解基本本查询询时，，我们们可以以在查查询结结果中中包括括聚合合函数数，在在这里里也可可以将将聚合合函数数作为为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的详细细信息息。子查询询的使使用方方式及及限制制2.在子查查询中中使用用in、all关键字字对于在在子查查询返返回多多个值值的情情况，，可以以使用用in关键字字。当当使用用in运算符符引入入子查查询时时，就就是对对子查查询集集合成成员测测试，，即把把源表表中的的列值值与子子查询询的返返回结结果进进行比比较，，如果果列值值与返返回结结果集集中的的列数数据值值之一一匹配配，那那么in判别式式求值值为true，查询询结果果就包包含这这行数数据。。【示例8-20】】使用子子查询询，结结合in关键字字，显显示顾顾客编编号customer_id是101的顾客客所定定产品品的折折扣价价。子查询询的使使用方方式及及限制制【示例8-21】】我们使使用关关键字字all，在数数据表表customersnew中查询询显示示credit_limit比country为IT的所有有的顾顾客的的credit_limit高的customer_id、cust_first_name、和country。【示例8-22】】我们使使用关关键字字any，在数数据表表customersnew中查询询，显显示credit_limit比country为IT的顾客客的credit_limit高的customer_id、cust_first_name、和country。子查询询的使使用方方式及及限制制3.在子查查询中中使用用exists关键字字在某些些情况况下，，只需需要查查询返返回true或false，子查查询的的数据据本身身并不不重要要。这这时，，就可可以使使用exists判别式式来引引入子子查询询。使用exists引入子子查询询时，，就相相当于于进行行一次次存在在性测测试。。【示例9-23】】exists往往可可以作作为in判式的的替代代语法法。结结合表表order_items、orders查询顾顾客编编号customer_id是101的顾客客所定定产品品的折折扣价价信息息。表表order_items测试哪哪些订订单号号在该该集合合中。。子查询询的使使用方方式及及限制制在子查查询过过程中中，常常用的的运算算符如如表所所示，，表中中集合合S为子查查询返返回的的结果果。运算符用法说明existsexists如果集合S部位空，则条件表达式为真，否则为假in表达式inS如果表达式的值在集合S中，则条件表达式为真，否则为假=表达式=S如果表达式的值与集合S中的唯一一个元素相等，则条件表达式为真，否则为假。集合S中最多只有一个元素<><=>=与“=”相同进行相应的关系运算any用在集合之前指定要与集合中的任一个元素进行比较all用在集合之前指定要与集合中的所有元素进行比较8.5查询语语句优优化所谓语语句优优化是是在原原来的的语句句基础础上为为了是是语句句能够够更快快速地地得到到结果果的方方法。。在查查询语语句中中，每每一条条SQL语句对对数据据库的的操作作，产产生结结果的的时间间也不不同。。即使使是完完成同同一功功能的的SQL语句，，最后后的响响应时时间也也是不不同的的。本本小节节主要要讲解解查询询语句句优化化中的的使用用技巧巧。在查询中中尽量不不使用““*”在数据库库的查询询操作中中，操作作着最常常用的查查询某一一个数据据表的全全部记录录，此时时，需要要使用““*”。。在Oracle数据库中中，使用用“*””后，会会浪费很很多查询询时间，，所以，，在查询询语句中中使用““*”时时，要考考虑是否否有字段段是不需需要的。。如果有有不需要要的字段段就要用用指定列列名的方方式查询询，这样样就可以以提高查查询效率率。8.5.2多表查询询中尽量量使用表表别名在查询中中使用别别名是比比较广泛泛的，在在Oracle数据库中中设置表表的别名名方式主主要有两两种，如如图所示示。8.5.3条件查询询多使用用where在SQL语