[IT认证]Oracle 10g基础教程.ppt

第1页,第7章 SQL高级查询技术,本章概述 本章要点 教学过程,第2页,本章概述,除了前一章介绍的SQL基本功能之外，SQL还可以用来完成其他一些重要且复杂的操作。 对于全世界来说，不同的地点有不同的时间，不同地点的人可能操纵相同数据库中的数据。数据库中需要存储准确的数据，这些数据离不开时间。在Oracle Database 10g系统中，可以灵活地支持有关日期和时间的操作。 在许多实际应用中，数据之间的关系是层次关系。例如，由零部件组成的汽车结构、组织结构和家族谱系等都是典型的层次结构关系。Oracle Database 10g系统，提供了层次查询的功能。 现在，数据库系统越来越强调对业务数据的分析，强调业务数据如何为决策人员提供帮助，数据仓库技术就是用来解决这种数据分析问题的。从Oracle Database 10g系统提供的分析查询技术可以看到，数据库技术和数据仓库技术由分离走向融合的趋势。 本章将对有关日期和时间、层次查询、分析查询等内容进行详细分析。,第3页,本章要点,日期的默认格式 日期和时间函数 层次查询技术 情景查询技术 翻译查询技术 分析查询技术,第4页,教学过程,7.1 日期和时间 7.2 层次查询 7.3 情景查询 7.4 使用翻译函数查询 7.5 分析查询,第5页,教学过程,7.1 日期和时间 7.2 层次查询 7.3 情景查询 7.4 使用翻译函数查询 7.5 分析查询,第6页,7.1 日期和时间,地球是一个广袤的空间，不同的地点有不同的时间，不同地点的人可能操纵相同数据库中的数据。数据库中需要存储准确的数据，这些数据离不开时间。那么如何在这种分布式数据库环境中准确地存储和使用时间呢？ Oracle Database 10g系统提供了有关日期和时间数据的完整解决方案。,第7页,设置默认的日期格式,在默认情况下，日期数据的格式是dd-mon-yy。其中，dd表示两位数字的日，例如10日。mon表示3位字符的月份，在英文版的Oracle系统中，月份分别是相应英文月份名称的简写形式，例如Jun；而在中文版中，月份的表示形式是“x月”或“xx月”，例如“8月”。yy表示两位数字年，例如90年和06年。 在插入数据时，默认情况下，应该按照dd-mon-yy格式插入数据。系统会自动将两位数字年识别成4位数字年。 在中文版的Oracle Database 10g系统中，应该按照“x月”或“xx月”格式输入月份。 在插入数据时，如果使用date关键字，那么可以采用yyyy-mm-dd的格式输入日期数据。其中，yyyy表示4位数字年，mm表示两位数字月，即112月，dd表示两位数字日。,第8页,日期数据格式,日期数据格式由nls_date_format系统参数来设置。系统参数存储在init.ora文件和spfile.ora文件中。可以使用show parameters命令查看这些系统参数的值。如果需要改变这些系统参数的值，可以通过执行alter system命令或alter session命令来实现。 alter system命令表示修改了系统参数的文件，这种修改设置在今后的数据库操作中一直起作用。 而alter session命令的设置只在当前的会话中起作用，该会话结束之后，其设置就会失效。 使用alter session命令设置日期的语法格式如下： alter session set nls_date_format date_format; 在上面的语法格式中，date_format参数既可以采用month-dd-yyyy格式，也可以采用yyyy-month-dd格式，还可以采用yyyy-dd-month格式。,第9页,转换日期数据,在Oracle Database 10g系统中，既可以把字符数据转变成日期数据，也可以把日期数据转变成字符数据。可以使用 to_char()函数 to_date()函数 完成日期和字符数据之间转换操作。 下面介绍这两个转换函数的使用方法。,第10页,to_char()函数,使用to_char()函数可以完成从日期数据到字符串数据的转换过程。 to_char()函数的语法格式如下： to_char(x, format) 在上面的语法格式中，主要参数的意义如下： x参数表示将要转换的源日期表达式类型。 format参数表示将要转变的源日期表达式的格式类型。格式类型由许多参数组成，这些参数如表7-1所示。,第11页,to_date()函数,使用to_date()函数可以把指定的字符串转换成一个日期和时间。 to_date()函数的语法格式如下： to_date(x, format) 在上面的语法格式中，该函数将字符串表达式x按照由format指定的格式转变成日期和时间。如果没有使用format日期格式参数，则按照默认的日期格式进行转换。,第12页,两位数字年和四位数字年,从本质上看，千年虫问题就是两位数字年的问题。 在前面的表7-1中讨提到可以使用yy和rr表示两位数字的年。实际上，这两种不同形式的年的表示方法存在着很大的差别。 如果使用yy形式表示两位年，那么这种年的世纪与当前系统的日期世纪是相同的，并不会因为当前系统年的变化而变化。 但是，如果使用rr形式表示两位年，那么这种年的世纪不仅取决于当前系统的日期世纪，而且还取决于当前系统的日期年。,第13页,日期和时间函数,Oracle Database 10g系统提供了许多用来处理日期和时间的函数，这些函数的类型和功能如表7-2所示。,第14页,教学过程,7.1 日期和时间 7.2 层次查询 7.3 情景查询 7.4 使用翻译函数查询 7.5 分析查询,第15页,7.2 层次查询,在许多实际的应用中，数据之间的关系是层次关系。例如，图书编码、由零部件组成的汽车结构、组织结构和家族谱系等都是典型的层次结构关系。在各种的ERP应用中，产品结构都是由一种树状的层次结构。 Oracle Database 10g系统提供了层次查询的功能。用户利用这些功能可以很方便地查询各种层次结构的数据。 本节将详细介绍Oracle Database 10g系统提供的层次查询功能。,第16页,自行车结构示例,第17页,bicycle表结构,第18页,bicycle表数据,第19页,层次查询的基本语法,为了执行层次查询，需要在select语句中添加start with子句和connect by子句。添加这些子句后的select语句的语法格式如下： select level, column_name, expression, from table_name where where_condition start with start_condition connect by prior prior_condition;,第20页,层次查询示例,首先执行最基本的层次查询，不显示树状结构的层次。在如图7-14所示的层次查询示例中，由于没有使用level列，该层次查询的结果与没有使用层次查询的普通查询的结果类似。 练习在层次查询中使用level伪列，该伪列表示树状结构的层次。执行层次查询，并且使用level伪列，其中order by子句用于对level列的数据进行排序。该层次查询的执行结果如图7-15所示。从该图可知，该树状结构的最高层次是5。 为了使显示的结果更加直观，可以对层次查询的查询结果进行格式化。使用column命令设置partName列为35位长的字符，设置mp_cost列按照99999.99的格式显示数字数据。在层次查询中，使用lpad()函数为执行的数据增加空格，使得这些数据按照不同的层次进行缩进。该层次查询的执行结果如图7-16所示。从结果中可以看出，由于采用字符缩进，所以这种树状结构更加直观。,第21页,教学过程,7.1 日期和时间 7.2 层次查询 7.3 情景查询 7.4 使用翻译函数查询 7.5 分析查询,第22页,7.3 情景查询,在执行select操作过程中，经常需要对不同的数据显示不同的结果。也就是说，在不同的情景下，需要显示不同的结果。这种情景查询非常类似于ifthenelse程序流程控制语句。 在Oracle Database 10g系统中，可以使用decode()函数和case表达式完成情景查询。,第23页,decode()函数,在Oracle Database 10g系统中，可以使用decode()函数执行类似于ifthenelse程序流程控制语句的操作。decode()函数的语法格式如下： decode(value_expression, search_value, result_value, default_value),第24页,case表达式,除了使用decode()函数执行情景查询之外，还可以使用case表达式执行情景查询。与decode()函数相比，case表达式的功能更加强大，使用更加灵活。在Oracle Database 10g系统中，有两种类型的case表达式，即简单case表达式和搜索case表达式。,第25页,简单case表达式,case search_expression when expression_1 then display_result_1 when expression_2 then display_result_2 when expression_n then display_result_n else default_display_result end,第26页,搜索case表达式,case when expression_1 then display_result_1 when expression_2 then display_result_2 when expression_n then display_result_n else default_display_result end,第27页,教学过程,7.1 日期和时间 7.2 层次查询 7.3 情景查询 7.4 使用翻译函数查询 7.5 分析查询,第28页,7.4 使用翻译函数查询,除了前面介绍的查询之外，Oracle Database 10g系统还提供了一个有意义的翻译函数translate()。顾名思义，可以使用translate()函数把指定的表达式翻译成另一个表达式。该函数类似于加密函数，可以使用一种密钥加密表达式，并且可以使用同样的密钥解密。因此，可以说translate()函数是一种对称加密方式。 translate()函数的语法格式如下： transalate(message_expression, source_encode_key, destination_encode_key),第29页,教学过程,7.1 日期和时间 7.2 层次查询 7.3 情景查询 7.4 使用翻译函数查询 7.5 分析查询,第30页,7.5 分析查询,本节首先介绍分析查询的基本概念 接着介绍分析函数的重要性和分类 最后介绍一个将采用分析查询的实例,第31页,分析查询和分析函数,在执行分析查询过程中需要借助于分析函数。Oracle Database 10g系统提供了许多可以用于分析查询的分析函数。可以把这些分析函数分成以下几种类型。 窗口函数：允许用户在指定的窗口中计算累加值、移动值等运算； 制表函数：允许计算诸如市场份额、销售总计和销售比率等数据； 第一个值和最后一个值函数：可以在一组数据中得到第一个值和最后一个值； 落后位置和提前位置函数：可以使得行中的某一个列的数据位置落后或提前指定的距离； 等级函数：可以计算一组数据的等级、百分比等； 线性回归函数：可以计算最小方框回归线。,第32页,数据仓库,在数据仓库的设计中，经常采用事实表和维度表组成的星型结构。 在这种结构中，事实表是数据仓库中最关键的数据表，该表存储了业务领域的主要数据。 维度表存储了用于分析事实表的维度。维度表中的粒度与对事实表分析的精细程度紧密相关。 星型结构示意图如图7-28所示。,第33页,sales_fact_2006事实表的结构,第34页,sales_fact_2006事实表中的数据,第35页,窗口函数,窗口函数是分析函数中最基本的一类函数，也是使用得最为广泛的分析函数。下面，首先介绍窗口函数的作用、类型和特点，然后详细介绍窗口函数的常见操作方法。 使用窗口函数可以在不同的分组上计算各种累计值、移动值等。本书前面介绍过的合计函数，例如sum()、avg()、max()、min()、count()和variance()等函数，以及first_value()和last_value()函数都可以用在窗口函数中。 窗口函数的基本语法格式如下： function_name (argument_list) over( ),第36页,制表函数,制表函数用于计算各种合计值和比率值。下面首先介绍制表函数的特点，然后通过具体示例介绍如何使用制表函数。 制表函数与窗口函数非常类似。事实上，这些制表函数的名称大多数也相同，例如sum()、min()和max()等。不同之处在于，窗口函数用于数据的一个窗口上，而制表函数则用于一个分区或分组上。在over子句中，窗口函数必须有order by子句，而制表函数没有order by子句，因为制表函数应用于分区或分组中的所有数据上。 制表函数的基本语法格式如下： function_name (argument_list) over(),第37页,检索当前行的周围行,如果需要检索的行不是当前行，而是当前行