零点起飞学oracle之数据的复杂处理

《零点起飞学oracle之数据的复杂处理》由会员分享，可在线阅读，更多相关《零点起飞学oracle之数据的复杂处理（48页珍藏版）》请在金锄头文库上搜索。

1、第8章 数据的复杂处理,到目前为止，前面章节已经讲解了如何从数据库选择（select）记录，where子句如何限定返回满足所定义条件的行的数量。但有时，在访问数据库时，经常需要对表中的某列数据进行统计分析，比如求其最大值、最小值、平均值、方差等。所有这些针对其中一列或者多列数据的分析就叫做聚合分析。Oracle中的聚合函数就是可以是实现数据的聚合分析。,8.1 统计数据,聚合函数返回基于多个行的单一结果。聚集函数可以在select或者select的having子句中使用。当用于select子句时，常常与group by子句一起使用。如果要处理表中的数据，可以使用聚合函数。,8.1.1 求最大值

2、,求最大值表示在一组数据中找到其中最大的数。在实际查询中，经常需要获取一组记录中特定列的最大值。Oracle中提供max函数来解决该问题。根据列的数据类型，可以分为以下三种情况。 1. max()函数应用于数值型 max()函数应用于数值型时，是按照数值的大小顺序来获得最大值。 【示例8-1】为了获得薪资最高的员工的薪资情况，可以利用如下所示的SQL语句。,8.1.1 求最大值,2. max()函数应用于字符型 当max()函数应用于字符类型数据，则会按照字母的编码值找出最大值。 【示例8-2】在表employees中，列first_name为字符型，可以利用max()函数来获得其最大值。 3

3、. max()函数应用于日期型 日期型的实质也是数值型。因此，max()函数应用于日期型与数值型具有完全相同的效果获得最晚日期。 【示例8-3】在表employees中，列hire_date为日期型，可以利用max()函数来获得公司最晚来的员工的进入公司的时间。,8.1.2 求最小值,求最小值表示在一组数据中找到其中最小的数。Oracle中，min()函数可以获得最小值，与max()函数相反。min()函数同样可应用于数值型、字符型和日期型。 【示例8-3】在表employees中，可以利用min()函数来获得last_name的最小值。,8.1.3 求平均值,平均值表示对一组数据求平均值的运

4、算。Oracle中，avg()函数用于获得一组数据的平均值。该函数只能应用于数值型。 【示例8-4】为了获得表employees中所有员工的平均工资，可以利用如下SQL语句。,8.1.4 求和,求和的含义是依次对数据进行累加，得到数据的总和。Oracle中，sum()函数用于获得一组数据的和。该函数同样只能应用于数值型。 【示例8-5】为了获得表employees中所有员工薪资之和，可以利用如下SQL语句。,8.1.5 统计记录数,统计是用来对一组数据进行统计个数的。Oracle中，count()函数用于计算表中记录的个数或者列中值的数目。计算内容用select语句指定。使用count函数时，

5、必须指定一个列的名称。其语法结构如下。count（*）count（column） 第一行表示计算表中的行的总数，即使表中的行的数据是null，也被计入在内。第二行表示计算列包含的行的数目，如果该列中的某行数据位null，则该行不计入统计总数。,8.1.5 统计记录数,该函数的常见使用场景有三种： 1. 统计单列 统计单列表示将一个列名作为count()函数的参数。当列值不为空时，将被统计在内，否则将不统计。 【示例8-6】我们可以首先向表employees中插入新的数据，并比较空值和非空值的统计情况。目前，表employees中的数据中，employee_id为“207”的员工的first_n

6、ame的列值为空。可以利用count()函数来比较存在空值与不存在空值在统计数目时的区别。 2. 统计所有列 统计所有列表示将表的所有列被作为count()函数的参数。这种情况下，即使所有列值均为空，Oracle仍将进行计数。,8.1.5 统计记录数,3. 利用count(1)进行统计 对于count()函数来说，count(1)与统计所有列count(*)表示的含义的相同的。 【示例8-8】利用count(1)的形式统计表employees中的记录。,8.2 Oracle中常用技巧,Oracle除了针对基本数据类型的各种函数之外，还有一些特殊函数，如为空值重新赋值nvl()函数、结果集的行号

7、rownum()函数、强制转换数据类型cast()函数。,8.2.1 空值处理nvl()函数,数据库中的数据有时出现空值。nvl()函数可以判断表达式的值是否为空。如果为空，则可以返回该函数设置的新值；若不为空，则返回原值。其使用语法如下所示。 nvl(表达式, 新值/表达式) nvl()函数首先判断第一个表达式参数的值是否为空；如果为空，则返回第二个参数的值；如果不为空，则返回第一个参数表达式的值。 【示例8-10】表employees表中存储了员工工资信息。我们可以通过查询获得员工编号以及薪资信息。,8.2.2 结果集的行号rownum()函数,rownum函数可以返回结果集的行号。返回第

8、一行，分配的是1；返回第二行，分配的是2。 1. 利用rownum为搜寻结果添加一列 【示例8-11】rownum的最简单应用场景是为搜寻结果添加一列，如下所示。 2. rownum与order by子句 【示例8-12】对表中数据排序，然后获得排名在某个范围之内的记录是一种常见需求。例如，用户可能要求获得表employees中、按员工姓名排序前7位的员工信息。一种常见的思路为综合利用rownum与order by子句来排序并增加过滤条件rownum小于等于7。,8.2.2 结果集的行号rownum()函数,3. rownum与比较运算符“=” rownum是oracle系统顺序分配为从查询返

9、回的行的编号，返回的第一行分配的是1，第二行是2，依此类推。rownum可以与比较运算符“=”的结合使用。 【示例8-13】现需要获得表employees中，行号为1的员工信息。最容易想到的SQL语句如下所示。,8.2.3 强制转换数据类型cast()函数,Oracle中的cast()函数可以强制转换列或变量的数据类型，即将内部类型或集合类型的值转换为另一种集合类型或者内部类型。其使用语法如下所示。 cast(原数据 as 新的数据类型) 【示例8-15】利用一个已有表来创建一个新表。在创建过程中，使用cast()函数将列的数据类型进行转换。例如，表jobs存储了员工职位信息，其数据结构如下所

10、示。,8.3 常见数据运算,运算符是一种符号，用来指定要在一个或者多个表达式中执行的操作，执行列、常量或者变量之间的数学运算和比较操作。表达式是标识符、值和运算符的组合。简单的表达式可以是一个常量、函数、列名、变量与子查询。可以用运算符将两个或更多的简单表达式连接起来组成复杂的表达式。运算就是根据数学法则进行计算。Oracle中的常用运算包括数学运算、逻辑运算、比较运算和按位运算。本节将就这几种运算进行简要介绍。,8.3.1 数学运算,数学运算是用于执行数字型表达式的算术运算。Oracle中的数学运算包括加（+）、减（-）、乘（*）、除（/）四种。无论操作数是何种数据类型，都将首先转换为数值型

11、，然后才参与运算。 【示例8-16】本示例演示四种基本数学运算及不同数据类型的操作数。,8.3.2 逻辑运算,逻辑运算符用来对逻辑条件进行测试，已获得真实情况。它与比较运算符一样，根据测试结果返回布尔值true、null、false。Oracle中的逻辑运算包括： ：大于运算，可用于数值型、日期型和字符串类型的比较； =：大于等于运算，可用于数值型、日期型和字符串类型的比较； ：不等于，可用于数值型、日期型和字符串类型的比较； !=：与用法相同； NOT：取反操作； AND：布尔值的与操作； OR：布尔值的或操作。 【示例8-16】需要注意的是，无论哪种逻辑运算，只要其中一个操作数据为null

12、，运算结果一定为假。,8.3.3 按位运算,按位运算即允许按照位来操作整型变量。Oracle仅仅提供了bitand()函数来实现按位与运算。按位或与按位异或，则可以利用bitand()函数间接获得。,8.3.3 按位运算,1. 按位与 Oracle中最常用的按位运算为按位与运算。按位与运算是双目运算符。它的作用是使参与运算的两数各对应的二进位相与。只有对应的两个二进位均为1时，结果位才为1，否则为0。每个十进制整数都可以转换为二进制，按位与运算应该使用bitand()函数。该函数有两个参数，其使用语法如下所示。 bitand(数值1, 数值2) 当然，如果数值参数不为整数，Oracle总是先将

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

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

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

16、n关键字，结合表employees、departments查询所有部门在某一地区（1400）的员工信息。表employees测试哪些员工在该集合中。,8.4.3 模糊匹配,在实际应用中，用户不会总是能给出精确的查询条件。因此。经常需要根据一些并不确切的线索来搜索信息。这种情况下，即可使用模糊查询。Oracle中提供了like判式来实现模糊匹配。通常情况下，like子句会和通配符配合使用。使用通配符可以实现更为复杂的模糊查询条件。以下主要讲解这些通配符的使用。 1. 匹配任意字符串“%” 对于like判式，最常用的通配符为“%”。该通配符可替代个数不确定的字符。 【示例8-22】在表employees中获得所有员工的的姓是以“K”开头的员工信息，那么可以利用匹配符“%”。,8.4.3 模糊匹配,2. 匹配单个字符“_” 对于like判式，另外一个常用通配符为“_”。该通配符可用于匹配任意的单个字符。 【示例8-23】对于示例8-22，使用通配符“_”来代替“%”。 3. 原义字符 在like判式中，通配符“%”和“_”有着特殊的意义。但是有时候用户希望判断标准中出现了这两个字符，那么将需要二者的原义字符。 【示例8-24】对于一个字符串，例如百分比“30%”，用户希望判断是否该字符串以“%”结尾，那么直接在判式中使用“%”无法达到预期要求。,