搜索
第8章高级查询我们已经初步了解了数据表的基础查询。但是，在实际的数据库操作中，往往需要同时从来自两个或两个以上的表中查询相关数据，连接就是满足这些需求的技术。本章将主要介绍子查询、联合语句、单表关联语句及多表查询的相关使用。8.1连接简介上一章的查询语句都只涉及一个表的数据。在很多情况下，需要查询的数据往往涉及多个表，这时需要对多个表进行连接查询。通过连接，可以根据各个表之间的逻辑关系从两个或多个表中检索数据。如，既要查询订单的信息，又要查询订单所在城市的信息，这就涉及orders和customersnew两个表。8.1.1连接定义多表连接实际上就是实现如何使用一个表中的数据来选择另一个表中的行。而连接条件则主要通过以下方法定义两个表在查询中的关联方式：指定每个表中要用于连接的列，典型的连接条件是在一个表中指定外键，在另一个表中指定与其关联的键。指定比较各列的值时要使用的逻辑运算符（=、等）。在select语句中实现多表连接，并不是一个物理存在的实体。换句话说，它在数据表中并不存在，它只是由数据库系统在需要时创建的，只在查询、检索数据期间有效。8.1.2连接的类型连接体现在where子句中指定的条件上，在条件中要指定两个表通过哪些列进行连接。一般情况下进行连接查询的两个表是通过主键和外键进行关联的，所以最简单的条件是一个表的外键与另一个表的主键相等。在SQL中，表的连接类型主要有下面几种。内部连接（innerjoin）外部连接（outerjoin）交叉连接（crossjoin）8.1.3简单的多表查询表的连接实现最简单的方法就是在select语句的from子句中，罗列要连接的表即可。而要进一步实现复杂的多表连接，则需要在from子句中使用join（innerjoin、outerjoin、crossjoin等）关键词，以后的章节将详细讲解。8.1.3简单的多表查询1.如何实现多表查询实现多表查询，可以在from子句中通过join关键词连接需要查询的表。而简单的多表查询，则可以不使用join关键词，而直接列出所要连接的表，利用逗号把from子句后的表名隔开。【示例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_information.category_id”的完整表达式。在连接的两个表中，如果不是相同的列名，可以省略表名；对于相同的列名，在使用时必须指明其对应的表名。而使用表名比较繁琐，我们可以为表定义别名。【示例8-3】使用别名简化product_information和categories两个表的连接。8.1.3简单的多表查询4.自连接在查询信息时，有时需要将表与其自身进行连接，即自连接，这时就需要为表定义别名。【示例8-4】从customersnew表中查询赊销限额credit_limit比顾客编号839大的所有顾客信息。8.2表的连接查询在关系型数据库中实现多表之间的联系，我们引入了连接查询，连接用于指定多数据源（表、视图）之间如何组合，以形成最终的数据源。前面介绍的表的连接，是通过from子句列出所要连接的表，结合使用where子句列出连接条件来实现。本节将主要为大家介绍使用关键字join实现表的连接，主要包括自然连接、内连接，外连接。使用join关键字有助于将连接操作与where的搜索条件分开。8.2.1自然连接自然连接将两个数据源中具有相同名称的列进行连接。在自然连接中，用户不需要明确指定进行连接的列，系统会自动完成这一任务。连接后相同的列只保留一个。如现在存在两个表：R表和S表，这两个表存在相同的列是B列。如果将这两个表进行自然连接，连接的具体情况如图所示。8.2.1自然连接8.2.1自然连接自然连接使用natrualjoin关键字。语法如图所示。【示例8-5】在示例数据库中，product_information和categories都包含category_id这列，现在我们把这两个表进行自然连接。8.2.2内连接内连接也称为简单连接，它会把两个或多个表进行连接，最后只能查询出匹配的记录，不匹配的记录将无法查询出来。理解如图所示。8.2.2内连接8.2.2内连接这种连接查询是平时最常用的查询。语法如图所示。【示例8-6】在示例数据库中，orders数据表存储的是订单信息，这时如果想知道订单者的联系方式phone_number就需要连接表customersnew。8.2.3外连接外连接分为左外连接、右外连接、全外连接。使用左外连接查询，返回的结果不仅仅是符合连接条件的行记录，还包含了左边表中的全部记录，右外连接与左外连接相反。全外连接返回所有匹配成功的记录，还包含了左、右表中未匹配成功的记录。理解如图所示。8.2.3外连接8.2.3外连接1.左外连接左外连接又称左向外连接，也就是说如果左表的某行记录在右表中没有匹配项，则在返回结果中右表的所有选择列表均为空，左外连接使用语法如图所示。【示例8-7】左外连接连接product_information、categories数据表，显示产品编号、产品分类编号、分类名称。8.2.3外连接2.右外连接右外连接又称右向外连接，与左外连接相反，将右边表中所有的数据与左表进行匹配，返回的结果显示出了匹配成功的记录，还包含右表中未匹配成功的记录，并在其左表对应列补空值。右外连接使用语法如图所示。【示例8-8】右外连接连接product_information、categories数据表，显示产品编号、产品分类编号、分类名称。8.2.3外连接3.全外连接返回所有匹配成功的记录，并返回左表未匹配成功的记录，也返回右表未匹配成功的记录。语法如图所示。【示例8-9】全外连接连接product_information、categories数据表，显示产品编号、产品分类编号、分类名称。【示例8-10】利用外连接的简略写法来改写示例8-8。8.3单表中的联合语句联合语句是指两个或多个select语句是并列关系，并且对这些select语句所捕获的记录集进行集合操作，以获得最终的结果集。这些联合语句包括以下几种：union查询、unionall查询、intersect查询和minus查询。8.3.1union查询该操作符用于取得两个查询结果集的并集。理解如图所示。当使用该操作符时，会自动去掉结果集中重复行。语法结构如图所示。8.3.1union查询【示例8-11】我们使用关键字union，显示customersnew表中，cust_first_name是BruceMargret和city是Roma的顾客编号、顾客姓名、所在城市。8.3.2unionall查询该操作符与union相似，但是它不会取消重复行，不会排序。理解如图所示。8.3.2unionall查询【示例8-12】我们使用关键字unionall，显示customersnew表中，cust_first_name是Bruce或Margret和city是Roma的顾客编号、顾客姓名、所在城市。8.3.3intersect查询intersect操作符用于两个查询结果的交集，理解如图所示。【示例8-13】我们使用关键字intersect，显示customersnew表中，cust_first_name是Margret和city又是Roma的顾客编号、顾客姓名、所在城市。8.3.4minus查询minus集合运算可以查询两个给定集合之间的差集，理解如图所示。【示例8-14】我们使用关键字minus，显示customersnew表中credit_limit在3000~5000之间的顾客信息。8.4子查询子查询是指嵌套在查询语句中的查询语句。子查询出现的位置一般为条件语句，如where条件，它本质上是where后的一个条件表达式。Oracle会首先执行子查询，然后执行父查询。8.4.1理解子查询子查询是嵌入在其他SQL语句中的select语句，也叫嵌套查询。子查询首先生成结果集，并将结果集应用于条件语句。子查询可以出现在插入、查询、更新和删除语句中。建立子查询的目的在于更加有效的限制where子句中的条件，并可以将复杂的查询逻辑梳理的更加清晰。子查询与父查询关系情况如图所示。8.4.2子查询使用在select语句中，where子句或者having子句中的条件往往不能用一个确定的表达式来确定，而要依赖于另一个查询，这个被嵌套使用的查询就是子查询。子查询返回单行。查询语法如图所示。8.4.3子查询的使用方式及限制在实际应用中，子查询一般出现在where子句中，包含子查询的语句通常采用图中所列格式中的一种。8.4.3子查询的使用方式及限制1.比较运算符引入子查询凡是表达式可以出现的地方几乎都可以使用子查询，只是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.子查询中的聚合函数之前在讲解基本查询时，我们可以在查询结果中包括聚合函数，在这里也可以将聚合函数作为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关键字。当使用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】我们使用关键字any，在数据表customersnew中查询，显示credit_limit比country为IT的顾客的credit_limit高的customer_id、cust_first_name、和country。8.4.3子查询的使用方式及限制3.在子查询中使用exists关键字在某些情况下，只需要查询返回true或false，子查询的数据