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

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子句中运用joininner 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就需求衔接表cus

8、tomersnew。.8.2.3 外衔接外衔接分为左外衔接、右外衔接、全外衔接。运用左外衔接查询，前往的结果不仅仅是符合衔接条件的行记录，还包含了左边表中的全部记录，右外衔接与左外衔接相反。全外衔接前往一切匹配胜利的记录，还包含了左、右表中未匹配胜利的记录。了解如下图。.8.2.3 外衔接.8.2.3 外衔接1.左外衔接左外衔接又称左向外衔接，也就是说假设左表的某行记录在右表中没有匹配项，那么在前往结果中右表的一切选择列表均为空，左外衔接运用语法如下图。【例如8-7】左外衔接衔接product_information、categories数据表，显示产品编号、产品分类编号、分类称号。.8.2.

9、3 外衔接2.右外衔接右外衔接又称右向外衔接，与左外衔接相反，将右边表中一切的数据与左表进展匹配，前往的结果显示出了匹配胜利的记录，还包含右表中未匹配胜利的记录，并在其左表对应列补空值。右外衔接运用语法如下图。【例如8-8】右外衔接衔接product_information、categories数据表，显示产品编号、产品分类编号、分类称号。.8.2.3 外衔接3.全外衔接前往一切匹配胜利的记录，并前往左表未匹配胜利的记录，也前往右表未匹配胜利的记录。语法如下图。【例如8-9】全外衔接衔接product_information、categories数据表，显示产品编号、产品分类编号、分类称号。【

10、例如8-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 Mar

11、gret和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_n

12、ame是Margret和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关键字对于在子查询前往多个值的情况，

16、可以运用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、和countr

17、y。【例如8-22】我们运用关键字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查

18、询顾客编号customer_id是101的顾客所定产品的折扣价信息。表order_items测试哪些订单号在该集合中。.8.4.3 子查询的运用方式及限制在子查询过程中，常用的运算符如表所示，表中集合S为子查询前往的结果。运算符用法说明existsexists如果集合S部位空，则条件表达式为真，否则为假in表达式 in S如果表达式的值在集合S中，则条件表达式为真，否则为假=表达式=S如果表达式的值与集合S中的唯一一个元素相等，则条件表达式为真，否则为假。集合S中最多只有一个元素 =与“=”相同进行相应的关系运算any用在集合之前指定要与集合中的任一个元素进行比较all用在集合之前指定要与集合

19、中的所有元素进行比较.8.5 查询语句优化所谓语句优化是在原来的语句根底上为了是语句可以更快速地得到结果的方法。在查询语句中，每一条SQL语句对数据库的操作，产生结果的时间也不同。即使是完成同一功能的SQL语句，最后的呼应时间也是不同的。本小节主要讲解查询语句优化中的运用技巧。.8.5.1 在查询中尽量不运用“*在数据库的查询操作中，操作着最常用的查询某一个数据表的全部记录，此时，需求运用“*。在Oracle数据库中，运用“*后，会浪费很多查询时间，所以，在查询语句中运用“*时，要思索能否有字段是不需求的。假设有不需求的字段就要用指定列名的方式查询，这样就可以提高查询效率。.8.5.2 多表查询中尽量运用表别名在查询中运用别名是比较广泛的，在Oracle数据库中设置表的别名方式主要有两种，如下图。.8.5.3 条件查询多运用where在SQL语句中指定查询条件时主要有两个关键词，一个是where，另一