零点起飞学Oracle之数据的复杂处理.pptx

《零点起飞学Oracle之数据的复杂处理.pptx》由会员分享，可在线阅读，更多相关《零点起飞学Oracle之数据的复杂处理.pptx（48页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、8.1 统计数据聚合函数返回基于多个行的单一结果。聚集函数可以在select或者select的having子句中使用。当用于select子句时，常常与group by子句一起使用。如果要处理表中的数据，可以使用聚合函数。第1页/共48页8.1.1 求最大值求最大值表示在一组数据中找到其中最大的数。在实际查询中，经常需要获取一组记录中特定列的最大值。Oracle中提供max函数来解决该问题。根据列的数据类型，可以分为以下三种情况。1.max()函数应用于数值型max()函数应用于数值型时，是按照数值的大小顺序来获得最大值。【示例8-1】为了获得薪资最高的员工的薪资情况，可以利用如下所示的SQL语

2、句。第2页/共48页8.1.1 求最大值2.max()函数应用于字符型当max()函数应用于字符类型数据，则会按照字母的编码值找出最大值。【示例8-2】在表employees中，列first_name为字符型，可以利用max()函数来获得其最大值。3.max()函数应用于日期型日期型的实质也是数值型。因此，max()函数应用于日期型与数值型具有完全相同的效果获得最晚日期。【示例8-3】在表employees中，列hire_date为日期型，可以利用max()函数来获得公司最晚来的员工的进入公司的时间。第3页/共48页8.1.2 求最小值求最小值表示在一组数据中找到其中最小的数。Oracle中，

3、min()函数可以获得最小值，与max()函数相反。min()函数同样可应用于数值型、字符型和日期型。【示例8-3】在表employees中，可以利用min()函数来获得last_name的最小值。第4页/共48页8.1.3 求平均值平均值表示对一组数据求平均值的运算。Oracle中，avg()函数用于获得一组数据的平均值。该函数只能应用于数值型。【示例8-4】为了获得表employees中所有员工的平均工资，可以利用如下SQL语句。第5页/共48页8.1.4 求和求和的含义是依次对数据进行累加，得到数据的总和。Oracle中，sum()函数用于获得一组数据的和。该函数同样只能应用于数值型。【

4、示例8-5】为了获得表employees中所有员工薪资之和，可以利用如下SQL语句。第6页/共48页8.1.5 统计记录数统计是用来对一组数据进行统计个数的。Oracle中，count()函数用于计算表中记录的个数或者列中值的数目。计算内容用select语句指定。使用count函数时，必须指定一个列的名称。其语法结构如下。count（*）count（column）第一行表示计算表中的行的总数，即使表中的行的数据是null，也被计入在内。第二行表示计算列包含的行的数目，如果该列中的某行数据位null，则该行不计入统计总数。第7页/共48页8.1.5 统计记录数该函数的常见使用场景有三种：1.统计

5、单列统计单列表示将一个列名作为count()函数的参数。当列值不为空时，将被统计在内，否则将不统计。【示例8-6】我们可以首先向表employees中插入新的数据，并比较空值和非空值的统计情况。目前，表employees中的数据中，employee_id为“207”的员工的first_name的列值为空。可以利用count()函数来比较存在空值与不存在空值在统计数目时的区别。2.统计所有列统计所有列表示将表的所有列被作为count()函数的参数。这种情况下，即使所有列值均为空，Oracle仍将进行计数。第8页/共48页8.1.5 统计记录数3.利用count(1)进行统计对于count()函数

6、来说，count(1)与统计所有列count(*)表示的含义的相同的。【示例8-8】利用count(1)的形式统计表employees中的记录。第9页/共48页8.2 Oracle中常用技巧Oracle除了针对基本数据类型的各种函数之外，还有一些特殊函数，如为空值重新赋值nvl()函数、结果集的行号rownum()函数、强制转换数据类型cast()函数。第10页/共48页8.2.1空值处理nvl()函数数据库中的数据有时出现空值。nvl()函数可以判断表达式的值是否为空。如果为空，则可以返回该函数设置的新值；若不为空，则返回原值。其使用语法如下所示。nvl(表达式,新值/表达式)nvl()函数

7、首先判断第一个表达式参数的值是否为空；如果为空，则返回第二个参数的值；如果不为空，则返回第一个参数表达式的值。【示例8-10】表employees表中存储了员工工资信息。我们可以通过查询获得员工编号以及薪资信息。第11页/共48页8.2.2结果集的行号rownum()函数rownum函数可以返回结果集的行号。返回第一行，分配的是1；返回第二行，分配的是2。1.利用rownum为搜寻结果添加一列【示例8-11】rownum的最简单应用场景是为搜寻结果添加一列，如下所示。2.rownum与order by子句【示例8-12】对表中数据排序，然后获得排名在某个范围之内的记录是一种常见需求。例如，用户

8、可能要求获得表employees中、按员工姓名排序前7位的员工信息。一种常见的思路为综合利用rownum与order by子句来排序并增加过滤条件rownum小于等于7。第12页/共48页8.2.2结果集的行号rownum()函数3.rownum与比较运算符“=”rownum是oracle系统顺序分配为从查询返回的行的编号，返回的第一行分配的是1，第二行是2，依此类推。rownum可以与比较运算符“=”的结合使用。【示例8-13】现需要获得表employees中，行号为1的员工信息。最容易想到的SQL语句如下所示。第13页/共48页8.2.3强制转换数据类型cast()函数Oracle中的ca

9、st()函数可以强制转换列或变量的数据类型，即将内部类型或集合类型的值转换为另一种集合类型或者内部类型。其使用语法如下所示。cast(原数据 as 新的数据类型)【示例8-15】利用一个已有表来创建一个新表。在创建过程中，使用cast()函数将列的数据类型进行转换。例如，表jobs存储了员工职位信息，其数据结构如下所示。第14页/共48页8.3常见数据运算运算符是一种符号，用来指定要在一个或者多个表达式中执行的操作，执行列、常量或者变量之间的数学运算和比较操作。表达式是标识符、值和运算符的组合。简单的表达式可以是一个常量、函数、列名、变量与子查询。可以用运算符将两个或更多的简单表达式连接起来组

10、成复杂的表达式。运算就是根据数学法则进行计算。Oracle中的常用运算包括数学运算、逻辑运算、比较运算和按位运算。本节将就这几种运算进行简要介绍。第15页/共48页8.3.1 数学运算数学运算是用于执行数字型表达式的算术运算。Oracle中的数学运算包括加（+）、减（-）、乘（*）、除（/）四种。无论操作数是何种数据类型，都将首先转换为数值型，然后才参与运算。【示例8-16】本示例演示四种基本数学运算及不同数据类型的操作数。第16页/共48页8.3.2 逻辑运算逻辑运算符用来对逻辑条件进行测试，已获得真实情况。它与比较运算符一样，根据测试结果返回布尔值true、null、false。Oracl

11、e中的逻辑运算包括：大于运算，可用于数值型、日期型和字符串类型的比较；=：大于等于运算，可用于数值型、日期型和字符串类型的比较；：小于运算，可用于数值型、日期型和字符串类型的比较；=：小于等于运算，可用于数值型、日期型和字符串类型的比较；=：等于，可用于数值型、日期型和字符串类型的比较；：不等于，可用于数值型、日期型和字符串类型的比较；!=：与用法相同；NOT：取反操作；AND：布尔值的与操作；OR：布尔值的或操作。【示例8-16】需要注意的是，无论哪种逻辑运算，只要其中一个操作数据为null，运算结果一定为假。第17页/共48页8.3.3 按位运算按位运算即允许按照位来操作整型变量。Orac

12、le仅仅提供了bitand()函数来实现按位与运算。按位或与按位异或，则可以利用bitand()函数间接获得。第18页/共48页8.3.3 按位运算1.按位与Oracle中最常用的按位运算为按位与运算。按位与运算是双目运算符。它的作用是使参与运算的两数各对应的二进位相与。只有对应的两个二进位均为1时，结果位才为1，否则为0。每个十进制整数都可以转换为二进制，按位与运算应该使用bitand()函数。该函数有两个参数，其使用语法如下所示。bitand(数值1,数值2)当然，如果数值参数不为整数，Oracle总是先将其转换为整数转换规则为直接截取整数部分，然后才进行运算。第19页/共48页8.3.3

13、 按位运算【示例8-17】与运算的本质是，如果两个运算位均为1，将返回为1，否则返回为0。对于一个整数，我们有时很想知道其二进制形式下某位为1还是0，那么，可以利用bitand来实现。例如，判断数字189二进制形式的第6位是0或者1。我们可以通过分解数字189为二进制，并与32的二进制形式进行与运算，如图所示。第20页/共48页8.3.3 按位运算2.按位或Oracle并未提供专门的函数来实现按位或运算。但是我们可以通过bitand()函数来间接实现。【示例8-18】对于表达式x+y，其本质如图所示（以随机数字189，57为例）。第21页/共48页8.3.3 按位运算3.按位异或异或运算规则为

14、，两个值不相同，则异或结果为真。反之，为假。概括为，不同为1,相同为0。【示例8-19】Oracle同样未提供直接计算按位异或的函数，而利用已有知识，我们同样可以很容易的推导出按位异或的运算公式。我们首先利用随机数189和57来查看按位异或的计算方式，如图所示。第22页/共48页8.4特殊数据运算Oracle进行条件查询时，除了使用比较表达式作为搜索条件之外，还可以使用简单的特殊判式。在where子句中可以使用多个搜索条件选择记录。这些判式包括：BETWEEN范围测试、IN集合成员测试、LIKE模糊匹配、IS NULL空值判断、EXITS存在性判断、ALL、SOME、ANY数量判断。第23页/

15、共48页8.4.1 范围测试在Oracle中的where子句中，使用between关键字可以方便的限制查询数据的范围。使用between关键字查询时包含了边界值。使用not between关键字查询限定数据范围之外的记录。其使用语法如下所示between x and y其中，x和y限定了范围的临界值。【示例8-20】利用between判式获得表employees中薪资处于20005000之间的员工信息。第24页/共48页8.4.2 集合成员测试同between 关键字一样，in判式也是为了更好更方便地检查数据的范围。使用in关键字一般是在子查询中。【示例8-21】使用in关键字，结合表empl

16、oyees、departments查询所有部门在某一地区（1400）的员工信息。表employees测试哪些员工在该集合中。第25页/共48页8.4.3 模糊匹配在实际应用中，用户不会总是能给出精确的查询条件。因此。经常需要根据一些并不确切的线索来搜索信息。这种情况下，即可使用模糊查询。Oracle中提供了like判式来实现模糊匹配。通常情况下，like子句会和通配符配合使用。使用通配符可以实现更为复杂的模糊查询条件。以下主要讲解这些通配符的使用。1.匹配任意字符串“%”对于like判式，最常用的通配符为“%”。该通配符可替代个数不确定的字符。【示例8-22】在表employees中获得所有员

17、工的的姓是以“K”开头的员工信息，那么可以利用匹配符“%”。第26页/共48页8.4.3 模糊匹配2.匹配单个字符“_”对于like判式，另外一个常用通配符为“_”。该通配符可用于匹配任意的单个字符。【示例8-23】对于示例8-22，使用通配符“_”来代替“%”。3.原义字符在like判式中，通配符“%”和“_”有着特殊的意义。但是有时候用户希望判断标准中出现了这两个字符，那么将需要二者的原义字符。【示例8-24】对于一个字符串，例如百分比“30%”，用户希望判断是否该字符串以“%”结尾，那么直接在判式中使用“%”无法达到预期要求。第27页/共48页8.4.4 空值判断在数据库中，null值是

18、一个特定的术语，用来描述记录中没有定义内容的字段值。通常，我们称之为空。在Oracle中，判断某个条件的值时，不能使用“=”来判断一个列或表达式的值是否为空，而是利用特殊判式is null。【示例8-25】查询表employees中，员工的department_id为空的员工信息。第28页/共48页8.4.5 存在性判断在一般情况下，在查询时，如果只是需要考虑是否满足条件，而对数据本身没有要求的话，就可以用exists判式。exists判式的操作对象是结果集，当结果集中记录数大于0时，将返回为真；否则，将返回为假。exists判式一般也用在子查询中。【示例8-26】exists往往可以作为in

19、判式的替代语法。结合表employees、departments查询所有部门在某一地区（1400）的员工信息。表employees测试哪些员工在该集合中。第29页/共48页8.4.6 集合判断在查询过程，有时往往需要针对集合进行逻辑运算。此时就需要用some,any和all对子查询中返回的多行结果进行处理。Some在此表示满足其中一个的意义。any也表示满足其中一个的意义。All则表示满足其中所有的查询结果的含义，使用and串起来的比较从句第30页/共48页8.4.6 集合判断【示例8-27】在表employees中存储了员工薪资salary。现需要获得薪资大于所有IT部门的员工信息。IT部门

20、的department_id是60。此时，可以使用all判式，来将列salary与集合进行比较。利用all判式来实现列与集合进行逻辑判断。【示例8-28】除了all判式之外，Oracle还提供了另外一个类似的判式some和any。从语义上分析可以很容易得出结论，只要对集合中的某个值的逻辑运算为真，那么条件即可成立，相当于对多个逻辑判式使用了or连接运算。而some和any可以实现完全相同的功能。在示例8-27中，利用some或any代替all判式，相当于获得表employees中年龄大于任一位IT部门的薪资，搜寻条件即可成立。第31页/共48页8.5分析函数与窗口函数Oracle提供了强大的分

21、析函数与窗口函数。这极大增强了Oracle的查询功能。分析函数和窗口函数也为报表统计等复杂查询提供了更多的解决方案。本节将讲述分析函数与窗口函数在Oracle中的应用，主要内容包括：分析函数与窗口简介、窗口详解、主要的分析函数。第32页/共48页8.5.1分析函数与窗口函数简介在前面所讲述的函数中，普通函数总是针对单条记录，而聚合函数则针对整个结果集。而对于分析函数和窗口函数，它们总是结合使用，所作用的对象与普通函数和聚合函数有着极大的不同。它们总是为每条记录准备与之相关的一个新的结果集，分析函数就作用于结果集之上，而这些结果集就类似于“窗口”。对于每条记录，窗口函数可以为其提供一个结果集类似

22、于窗口。当然，图中所演示的结果集窗口只是一种示例。至于窗口的真实大小，则由窗口函数的实际定义决定。第33页/共48页8.5.2 排名函数最常用的分析函数是排名函数。排名函数用于返回当前记录在窗口中的位置。常用的排名函数包括：rank()、dense_rank()和row_number()。因为排名函数的返回值总是依赖于记录的次序。因此，排名函数所对应的窗口函数必须指定排序规则order by子句，而Oracle为每条记录提供的默认窗口为，表中第一条记录开始，直至与当前记录具有相同排名的所有记录。第34页/共48页8.5.2 排名函数1.rank()函数的使用【示例8-29】现欲获得表emplo

23、yees中的员工，按照年龄大小升序排列，每位员工的排名情况。2.dense_rank()函数的使用【示例-30】与rank()函数不同，dense_rank()函数返回的排名则不具有跳跃性。3.row_number()函数的使用【示例8-31】row_number()函数单纯返回当前记录在窗口中的所处位置的行号。这里的行号具有唯一性。利用row_number()函数来代替dense_rank()函数。第35页/共48页8.5.3 默认窗口在上面示例中，只使用了order by来定义窗口。此时的窗口为，按照排序规则，第一条记录至当前排名的所有记录。这里值得注意的是，由于排序时关键字相同，造成多条

24、记录具有相同排名。此时将出现多条记录具有相同的窗口。我们可以通过另外一个函数count()来查看窗口中实际的记录数目来验证这以情况。第36页/共48页8.5.4 分区窗口对于分析函数来说，默认窗口往往并不常用。而另外一种窗口分区窗口则相对常用的多。分区窗口是指与当前记录拥有相同的分区标准的所有记录。创建分区窗口的语法如下所示。partition by 列名partition by语句首先根据列名获得当前记录的列值，接着获得表中具有相同列值的所有记录，并将该记录集合作为当前记录的窗口。第37页/共48页8.5.4 分区窗口【示例8-32】对于表employees中的员工记录，我们希望获得员工姓名

25、和薪资的同时，也获得该部门员工的平均薪资，即可使用分区窗口。【示例8-33】在分区窗口中，同样可以对其中的记录进行排序。例如，在获得员工信息的同时，获得在同职位中、按薪资大小排序的位置，则可以同时利用partition by与order by来定义窗口。第38页/共48页8.5.5 窗口子句对于每条记录，一旦使用了窗口函数，都有对应的窗口记录集合。而使用窗口子句则可以进一步限制窗口范围。而这样的窗口子句一般以两种方式出现：利用rows子句进行限制利用range子句进行限制第39页/共48页8.5.5 窗口子句1.rows子句rows子句的使用前提为窗口已经利用order by进行了排序。当窗口

26、中的记录经过排序，当前记录一定处于某个位置，那么可以利用rows子句，按照位置向前或向后追溯，以进一步限制窗口大小。其使用语法如下所示。over(order by 列名 rows between 位移量 preceding and 位移量 following)在rows子句中，利用preceding向前追溯（排序之后，处于当前记录之前的记录），利用following向后追溯（排序之后，处于当前记录之后的记录）。【示例8-34】在表employees中，除了可以获得当前员工信息，还希望获得当前薪资和相邻的两位员工的薪资总和，则可以综合利用rows子句进行限制。第40页/共48页8.5.5 窗口子

27、句2.range子句rows子句以相对位置作为获取记录的标准，而range子句则以相对列值作为筛选记录的标准。其使用语法如下所示。over(order by 列名 range between 差值 preceding and 差值 following)在range子句中，利用preceding向前追溯（排序之后，处于当前记录之前的记录），利用following向后追溯（排序之后，处于当前记录之后的记录），追溯的范围为当前列值差值。【示例8-35】在表employees中，除了可以获得当前员工信息，还希望获得与当前员工薪资相差1000之内的员工数目，则可以利用range子句。第41页/共48页8

28、.5.5 窗口子句3.current row与unbounded窗口子句中，除了可以利用确定的数值来限定窗口之外，还可以之间使用current row来指定当前记录，使用unbounded来代替数值，表示不受限制的窗口范围。【示例8-36】currrent row将直接定位当前记录，例如，在表employees中，可以利用综合利用unbounded和current row获得排序之后，第一条记录至当前记录的窗口大小。【示例8-37】当然，也可以对rows子句的前后位移均不进行限制。这样，每条记录所获得的窗口均为表中所有记录。第42页/共48页8.5.6 常用分析函数分析函数的实质非常类似于聚合

29、函数，因此很多聚合函数均可与窗口函数结合使用，如max()、min()、sum()等。除此之外，Oracle还提供了另外几种常用的分析函数。本小节将简要讲述Oracle中常用的几种分析函数：fist_value()函数、last_value()函数、lead()函数、lag()函数。1.first_value()函数first_value()函数用于返回已排序窗口中第一条记录相关的信息。其应用场景非常广泛，例如，对于员工工资来说，为了在获得员工信息的同时，也获得本部门员工的最高工资，则可以分区窗口进行降序排序，并利用first_value()函数获得最高工资。【示例8-38】在表employe

30、es中，我们可以获得按照同部门中，获取薪资最高的员工信息。第43页/共48页8.5.6 常用分析函数2.last_value()函数【示例8-39】与first_value()函数相反，last_value()函数返回分区中最后一条记录的相关信息。但是值得注意的是，直接利用last_value()函数来代替示例8-38中的first_value()函数无法获得同龄人中，按姓名升序排列，处于最后一位的员工姓名。第44页/共48页8.5.6 常用分析函数3.lag()函数对于一个窗口所确定的结果集，first_value()和last_value()函数可以分别返回第一条和最后一条记录的相关信息。

31、而对于窗口中的其他记录则无能为力。Oracle还提供了lead()函数和lag()函数来灵活地处理其他记录。lag()函数以当前记录为坐标，按照特定位移向上搜索，并尝试捕获记录。其使用语法如下所示。lead(列名或列的表达式,位移,默认值)其中，列名或列的表达式针对捕获的记录；位移参数则表示从当前行开始的偏移量；默认值是指，如果未捕获记录，列名或列的表达式将返回为null，此时使用默认值作为返回值。【示例8-40】在表employees中，可以利用lag()函数获得按薪资排序的前一位员工的信息，如下所示。第45页/共48页8.5.6 常用分析函数4.lead()函数与lag()函数非常相似，lead()函数也用于在当前窗口中，以当前记录为坐标移动某个位移，以获得新的记录的相关信息。与lag()函数不同的是，lead()函数的位移方向为向下偏移。【示例8-41】我们可以利用lead()函数来代替示例8-40中的lag()函数。第46页/共48页8.6本章小结本章讲解了Oracle中聚合函数常用技巧、Oracle中的基本运算、Oracle中的特殊判式、Oracle中的分析与窗口函数。本章重点是Oracle中的聚合函数的灵活运用。本章难点是分析函数与窗口函数的结合使用。下一章将学习Oracle中的控制语句。第47页/共48页感谢您的观看！第48页/共48页