在介绍了Oracle中的基本数据类型及相应的处理函数后，本章将介绍Oracle中较为复杂的数据处理。
接下来，本文将介绍：
1、利用聚合函数来统计数据；
2、常用技巧；
3、oracle中的基本运算；
4、oracle特殊判式；
5、oracle中的分析与窗口函数
1. 利用聚合函数统计数据
前面几篇博客介绍的，都有一个共同的特点：均针对单一数据。
而聚合函数则是针对多个数据获得统计结果。使用聚合函数的场景大多为表中的数据，接下来的所有示例均基于表employees，其数据结构及内容如下所示。
SQL>select * from employees
表employees中存储了员工的基本信息，有员工ID、员工姓名、员工职位、员工年龄、员工家庭住址。
1.1 求最大值----max()函数
max()函数可以用于获得一组数据中的最大值，这里的“一组数据”通常为表中某列的所有值，max()函数可以应用的数据类型包括数值型和字符型。
（1）max()函数应用于数值型
max()函数应用于数值型时，是按照数值的大小顺序来获得最大值。
例子：为了获得员工的最大年龄可以利用如下所示的SQL语句
SQL>select max(employee_age) max_age from employees;
------
37
from employees提供了表employees中的所有记录作为数据源；max(employee_age)中的employee_age为数据列，该列的所有数据组成了max()函数的参数，max()函数则统计该组数据中的最大值。
max()函数的返回值是一个统计值，与数据表中的元数据并没有一一对应的关系。例如：表employees中可能存储了多个37岁的员工信息，而max()函数的返回值只是一个统计值。
（2）max()函数应用于字符型
max()函数不仅可以应用于数值型，还可以应用于字符型。当应用于字符型时，oracle会依照字母表由前到后的顺序进行排序。
例子：在表employees中，列employee_name为字符型，可以利用max()函数来获得去最大值。
SQL>select max(employee_name ) max_name from employees;
--------
钟小平
（3）max()函数应用于日期型
日期型的实质也是数值型，因此，max()函数应用于日期型与数值型具有完全相同的效果-----获得最晚日期。
1.2 求最小值-----min()函数
与max()函数相反，min()函数可以获得最小值。min()函数同样可应用于数值型、字符型和日期型。
1.3 求平均值-----avg()函数
avg()函数用于获得一组数据的平均值，该函数只能应用于数值型。
avg()函数的返回值是一个number型。
1.4求和----sum()函数
sum()函数用于获得一组数据的和。该函数同样只能应用于数值型。
例子：为了获得employees中所有员工年龄之和，可以利用如下SQL语句。
SQL.>select sum(employee_age ) sum_age from employees;
1.5 统计记录数-----count()函数
count()函数用于统计记录数目。该函数的常见使用场景有3种。
（1）统计单列
对于统计单列来说，列名作为count()函数的参数，当列值不为空时，将计数1；否则，将计数0。
（2）统计所有列
当表的所有列被作为count()函数的参数，即count(*)进行统计时，即使所有列值均为空，oracle仍将进行计数。
select count(*) from employees;
（3）利用count(1)进行统计
对于count()函数来说，还可以利用count(1)的形式进行统计。count(1)与count(*)返回的值相同，对每条记录都计数1.
SQL>select count(1) from employee;
2、oracle中的常用技巧
除了针对基本数据类型的各种函数之外，oracle还提供了一些其他技巧性函数。比如：
多值判断---decode()函数、为空值重新赋值---nvl()函数、结果集的行号----rownum()函数、强制转换数据类型-----cast()函数
2.1 多值判断-----decode()函数
在oracle中，可以利用if else和case语句进行判断，当然还有decode()函数处理该问题，从而减少了代码量，并增强了SQL语句的可读性。
decode()函数的使用语法如下：
decode(表达式，比较值1，结果值1，比较值2，结果值2，....，默认值)
decode()函数的处理过程类似于解码过程，第一个参数表达式往往为变量或者数据表中的列；其后的参数总是以“比较值---结果值”对的形式出现，表达式的值与“比较值”进行比较，如果二者相等，则返回相应的“结果值”；若果所有比较都不成功，那么将返回默认值。
例子：在表employees所存储的数据表中，列employee_position标识了员工的职位，现需要对员工进行分类：职位是工程师的员工规划技术部，其他员工划为行政管理部。那么可以利用decode()函数来实现这一用法。
decode(sign(instr(employee_position,'工程师')), 1, '技术部', '行政管理部')中 sign(instr(employee_position,'工程师'))用于获得列employee_position中字符串“工程师”的位置，并判断其正负性；1，‘技术部’是第一个“比较值---结果值”键值对，表示当员工为工程师时，返回字符串“技术部”；最后一个参数“行政管理部”为默认值。
2.2 空值处理-----nvl()函数
数据库中的数据难免出现空值，但是对于用户来说，空值并不够友好。用户更愿意获得有意义的数据，nvl()函数可以判断表达式的值是否为空。如果为空，则可以返回新值；若不为空，则返回原值。其使用语法如下所示：
nvl(表达式,新值/表达式)
nvl()函数首先判断第一个表达式参数的值是否为空；如果为空，则返回第二个参数的的值；如果不为空，则返回第一个参数表达式的值。
例子：表employees和表salary分别存储了员工及员工工资信息，可以通过分组获得员工及员工总额信息。
nvl(sum(salary), 0 ) 用于判断员工的工资总额是否为空，如果为空则返回0，因此记录1--5的工资总额仍然保持原值，而记录6--8的工资总额被置为0.
2.3 结果集的行号----rownum()函数
rownum()函数可以返回结果集的行号，对于select语句，没捕获一条记录，rownum()函数的返回值将累加1，否则，rownum()的返回值保持不变。
（1）利用rownum为搜寻结果添加一列
例子：rownum的最简单应用场景是为搜寻结果添加一列，如下所示。
SQL>select e.employee_id, e.employee_name, rownum from emplyees e;
select e.employee_id, e.employee_name, rownum from emplyees e用于遍历表employees，并捕获每条记录中的employee_id和employee_name列；每捕获一条记录，rownum的返回值将累加1，因此，rownum依次返回1--8的值。
（2）rownum与order by子句。
对表中数据排序，然后获得排名在某个范围之内的记录是一种常见需求。例如，要求获得表employees中、按员工姓名排序前3位的员工信息。一种常见的思路为综合利用rownum与order by子句排序并增加过滤条件----rownum小于等于3.
SQL>select e.employee_id, employee_name, rownum from employees e where rownum <= 3 order by e.employee_name;
EMPLOYEE_ID EMPLOYEE_NAME ROWNUM
------------------- ------------------------- -----------
3 刘俊 3
1 王晓 1
2 钟小平 2
where rownum<=3用于限制rownum小于等于3的记录；order by e.employee_name用于对表employees中的记录按照employee_name列进行排序。
从上面的查询结果可以看出，并不是预期的效果。原因在于where条件先于order by子句执行。也就是说，Oracle总是先利用where条件过滤搜寻记录，最后才利用order by进行排序。而rownum的返回值用于where条件判断，这造成了所有rownum执行完毕之后在再执行排序操作。
该需求的正确解决方式为，首先利用内嵌式图获得排序之后的结果，然后对内嵌视图所代表的结果集使用rownum()函数，SQL语句如下所示：
SQL>select t.*, rownum from (
select e.employee_id, employee_name from employees e order by e.employee_name) t where rownum<=3
EMPLOYEE_ID EMPLOYEE_NAME ROWNUM
-------------------- ------------------------- ---------------
7 李四 1
3 刘俊 2
4 王龙 3
内嵌视图 t 的定义为select e.employee_id, employee_name from employees e order by e.employee_name.该内嵌视图所代表的结果集如下：
EMPLOYEE_ID EMPLOYEE_NAME
--------------------- ---------------------------
7 李四
3 刘俊
4 王龙
8 王五
1 王晓
6 张三
5 钟文
2 钟小平
对该结果集来说，rownum<=3将返回前3条记录，即为：
EMPLOYEE_ID EMPLOYEE_NAME
------------------- ----------------------------
7 李四
3 刘俊
4 王龙
因此，对于rownum()函数的使用来说，首先要观察rownum()函数出现在查询语句的哪个子句中，而子句的执行时机往往决定rownum()函数的返回值。
（3）rownum与比较运算符“=‘
rownum()函数常常出现在查询条件中，SO，rownum()函数与比较运算符“=”的结合使用。
例子：现需要获得employees中，行号为1的员工信息，最容易想到的SQL语句如下：
SQL>select e.employee_id, e.employee_name, rownum from employees e where rownum =1;
分析查询结果可知，利用rownum=1可以获得预期的结果，但是，利用该方法无法成功获得行号为4的员工信息。
【分析】这是因为rownum总是从1开始，如果不能获得rownum为1的记录，那么rownum的返回值永远无法达到大于1的数字。可以通过分析该SQL语句的执行过程解释查询失败的原因。
select e.employee_id, e.employee_name, rownum from employees e 用于自表employees中获取记录；where rownum=4则指定了过滤条件；当Oracle遍历至表employees的第一条记录时，rownum为1，但是where rownum=4为假，那么该记录不在捕获范围；当遍历至表employees的第二条记录时，因为捕获记录为0，rownum仍然返回 1,；以此类推，无论Oracle遍历多少条记录，rownum总是为1，也永远无法满足rownum=4的条件。
事实上，单纯利用rownum=n作为查询语句的过滤条件，当n大于1室，返回的查询结果永远为空。此处，仍然可以通过内嵌视图来解决该问题。
（4）rownum与比较运算符“>”
例子：与比较运算符“=”类似，如果对rownum使用了对于运算，而比较值又大于等于 1 时，Oracle同样返回空的结果集。
如果，当rownum>n中的 n 小于1，那么查询语句将返回所有记录。
2.4 强制转换数据类型-----cast()函数
Oracle中的cast()函数可以强制转换列或变量的数据类型。其语法如下所示：
cast( 元数据 as 新的数据类型 )
3、Oracle中的运算
Oracle中的常用运算包括数学运算、逻辑运算、比较运算和按位运算。
4、Oracle中的特殊判式
Oracle进行条件判断时，最常见的操作符为“=’。除此之外，oracle还提供了若干特殊判式进行逻辑判断。这些判式包括：
between-----范围测试；in-----集合成员测试；like----模糊匹配；is null-----空值判断；exits-----存在性判断；all、some、any----数量判断。
4.1 between-----范围测试
between可以用于判断列或表达式的值是否处于某个范围之内。其使用语法如下：
between x and y
其中，x和y限定了范围的临界值。
4.2 in -------集合成员测试
in判式用于判断某个列或表达式的值是否处于某个结合之内
4.3 like-----模糊匹配
用户在查询数据时，往往并不能提供精确的匹配条件。例如：用户试图通过公司名称查询记录时，并不能提供精确的公司全称；又如，用户可能希望获得某位员工的信息，而只能确定员工的姓氏。这些情况下，即可使用模糊查询，oracle中提供了like判式实现模糊匹配。
（1）、匹配任意字符串----“%”
对于like判式，最常用的通配符为“%”，该通配符可用于匹配任意长度的任意字符。
比如：'刘%'，指定查询条件为该列的值以“刘”为开头，后面跟任意长度的任意字符，因此将返回所有刘姓员工的姓名。
（2）、匹配单个字符-----“_”
对于like判式，另外一个常用通配符为“_”。该通配符可用于匹配任意的单个字符。
例子：'刘_'，指的是刘后面只能跟一个字符。。
当然，多个通配符“_”可以同时使用，比如：'刘__'，刘后面跟着两个“_”。
（3）原义字符
在like判式中，通配符“%”和“_” 有着特殊的意义。但是有时候用户希望判断标准中出现了这两个字符，那么将需要二者的原义字符。
例子：对于一个字符串，百分比“20%”，用户希望判断是否该字符串以“%”结尾，直接在判式中使用“%”无法达到预期效果。
因此，需要用到"\"； like ‘%\%’中的第一个%，表示通配符；而后面的表示原义字符。
4.4 is null -----空值判断
oracle中不能使用“=”判断一个列或者表达式的值是否为空 ，而是利用特殊判式 is null.
当然，is null判式的对立面是is not null，为了获得所有列值均完整无缺的记录，相应的SQL语句如下所示。
SQL> select * from employees
where employee_id is not null
and employee_name in not null
and ....
............
利用is not null对表中的所有列进行判断，可以获得所有列值均不为空的记录。
4.5 exits---存在性判断
exits判式可以用于判断记录的存在性，exits判式的操作对象是结果集，当结果集汇中记录大于0时，将返回为真，否则，将返回为假。
592
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
