1、DISTINCT ,COUNToracle 中的 distinc 关键字和 count 函数需要经常组合起来使用，例如 ,如果我们拿到一个仅有员工基本信息的列表，我们希望得到这个公司共有多少个部门。我们可以这样做：select count(a.deptno) from (select distinct deptno from scott.emp) a;但这样做太复杂了,我们可以将 discint 和 count 函数用在一起例如：select count(distinct deptno) from scott.emp;二者效果是一样的。NVL,NLV2,NULLIF, CoalesceOrac
2、le 中函数以前介绍的字符串处理，日期函数，数学函数,以及转换函数等等，还有一类函数是通用函数。1.NVL 函数NVL 函数的格式如下：NVL(expr1,expr2)含义是：如果 oracle 第一个参数为空那么显示第二个参数的值，如果第一个参数的值不为空，则显示第一个参数本来的值。例如：SQL select ename,NVL(comm, -1) from emp;ENAME NVL(COMM,-1) SMITH -1ALLEN 300WARD 500JONES -1MARTIN 1400BLAKE -1FORD -1MILLER -1其中显示-1 的本来的值全部都是空值的2 NVL2 函
3、数NVL2 函数的格式如下：NVL2(expr1,expr2, expr3)含义是：如果该函数的第一个参数为空那么显示 expr3，如果第一个参数的值不为空，则显示 expr2 。SQL select ename,NVL2(comm,-1,1) from emp;ENAME NVL2(COMM,-1,1) SMITH 1ALLEN -1WARD -1JONES 1MARTIN -1BLAKE 1CLARK 1SCOTT 1上面的例子中。凡是结果是-1 的原来都不为空，而结果是 1 的原来的值就是空。3. NULLIF 函数NULLIF(exp1,expr2)函数的作用是如果 exp1 和 ex
4、p2 相等则返回空 (NULL)，否则返回第一个值。下面是一个例子。使用的是 oracle 中 HR schema，如果 HR 处于锁定，请启用这里的作用是显示出那些换过工作的人员原工作，现工作。SQL SELECT e.last_name, e.job_id,j.job_id,NULLIF(e.job_id, j.job_id) “Old Job ID”FROM employees e, job_history jWHERE e.employee_id = j.employee_idORDER BY last_name;LAST_NAME JOB_ID JOB_ID Old Job ID D
5、e Haan AD_VP IT_PROG AD_VPHartstein MK_MAN MK_REP MK_MANKaufling ST_MAN ST_CLERK ST_MANKochhar AD_VP AC_MGR AD_VPKochhar AD_VP AC_ACCOUNT AD_VPRaphaely PU_MAN ST_CLERK PU_MANTaylor SA_REP SA_MAN SA_REPTaylor SA_REP SA_REPWhalen AD_ASST AC_ACCOUNT AD_ASSTWhalen AD_ASST AD_ASST可以看到凡是 employee。job_id 和
6、 job_histroy.job_id 相等的，都会在结果中输出 NULL 即为空，否则显示的是 employee。job_id4.Coalesce 函数Coalese 函数的作用是的 NVL 的函数有点相似，其优势是有更多的选项。格式如下：Coalesce(expr1, expr2, expr3. exprn)Coalesce 是这样来处理这些参数的。如果第一个参数为空，则看第二个参数是否是空，否则则显示第一个参数，如果第二个参数是空再看第三个参数是否为空，否则显示第二个参数，依次类推。这个函数实际上是 NVL 的循环使用，在此就不举例子了。正则表达式,TO_CHAR正则表达式是很多编程语言
7、中都有的。可惜 oracle8i、oracle9i 中一直迟迟不肯加入，好在oracle10g 中终于增加了期盼已久的正则表达式功能。你可以在 oracle10g 中使用正则表达式肆意地匹配你想匹配的任何字符串了。所谓正则表达式是对于字符串进行匹配的一种模式。举个例子来说字符串1980-9$可以匹配1980-1989 ，也即 80 后出生的年份。如果希望统计出公司那些员工是 80 后的，就可以使用如下的 SQL 语句：select * from emp where regexp_like(to_char(birthdate,yyyy),1980-9$);这里用到了 regexp_like 和
8、to_char 函数。这里在正则表达式中用到的、$ 、0-9都被称为元数据(metacharacter) ，正则表达式都是由多元表达式组成的。在这里，表示一个字符串的开头，$表示一个字符换的结尾，因此198 表示以 198 开头的字符串，而0-9$则表示以 0-9 的数字结尾的字符串。因此整体上1980-9$就能匹配所有 1980-1989 的字符串。正则表达式中常用到的元数据(metacharacter)如下： 匹配字符串的开头位置。$ 匹配字符串的结尾位置。* 匹配该字符前面的一个字符 0 次，1 次或者多次出现。例如 52*oracle 可以匹配 5oracle,52oracle,522
9、oracle,5222oracle 等等。+ 匹配该字符前面的一个字符 1 次或者多次出现。例如 52+oracle 可以匹配 52oracle,522oracle,5222oracle 等等? 匹配该字符前面的一个字符 0 次或 1 次或者多次出现。例如 52?oracle 只能匹配5oracle,52oracle 等等n 匹配一个字符串 n 次，n 为正整数。例如：hel2o 所匹配的是 hellon,m 匹配一个字符串至少 n 次，至多 m 次。其中 n 和 m 都是整数。. 匹配除了 null 之外的任何单个字符串(pattern) 这个是用来匹配指定模式的一个子表达式x|y 匹配 x
10、 或者 y，其中 x 和 y 是一个或者多个字符abc 匹配括号中的任意一个字符。例如：abbc 可以匹配 abc 和 bbca-z 匹配指定范围内的任意字符串。例如A-Ghi 可以匹配 Ahi 至 Ghi:指定一个字符类，可以匹配该类中的任意字符 这里的字符类包括：:alphanum: 可以匹配字符 0-9、A-Z、a-z:alpha:可以匹配字符 A-Z、a-z:blank:可以匹配空格或者 tab 键:digit:可以匹配数字 0-9:gragh:可以匹配非空字符:punct:可以匹配. , ” 等标点符号。:upper:可以匹配字符 A-Z:lower:可以匹配字符 a-z这里列出的是
11、一些常见的正则表达式中的元数据。更多的正则表达式的内容请参照 oracle官网上的正则表达式的相关内容关于 orace 中的正则表达式只能通过 oracle 特意为正则表达式设计的 4 个函数来使用。这4 个函数分别是：regexp_like,regexp_instr,regexp_replace,regexp_substr。关于这 4 个函数的具体用法，会在稍后介绍，这里简单说一下：regexp_like(x,pattern)当 x 能正确匹配字符串时返回 true。regexp_instr(x,pattern)在 x 中尝试匹配 pattern，并返回匹配的位置。regexp_replac
12、e(x,pattern,replacestring)在 x 中尝试匹配 pattern,并将其替换成 replacestring。regexp_substr(x,pattern）返回 x 中匹配 pattern 的一个字符串。前面介绍了 oracle 中正则表达式构成的元数据，但仅仅知道元数据是不够的，oracle 中的正则表达式是结合其特定的 4 个函数使用的，其中用的最多的就是现在要介绍的的这个函数：regexp_likeregexp_like 函数的基本构成是：regexp_like(x,pattern,match_option)其基本功能是在 x 中查找 pattern，如果能找到返回
13、 true 否则返回 false,这里的可选参数match_option 可以有如下几种形式：c 表明进行匹配时区分大小写( 这也是默认选项)。i 表明在匹配时不区分大小写。n 表明允许使用匹配任何字符串的元数据，即.。m将 x 作为一个包含多行的字符串。以下是两个关于 regexp_like 的例子：这个例子是寻找员工中的 80 后的员工。select * from emp where regexp_like(to_char(birthdate,yyyy),1980-9$);这个例子是寻找名字是以M或者m打头的那些员工的名字select * from emp where regexp_lik
14、e(ename,j,i)前面我们介绍了 oracle 中正则表达式的基本元字符和常用的两个函数 regexp_like 和 regexp_instr。这里我们再继续介绍剩下的两个正则表达式函数 regexp_replace 和regexp_substr。regexp_replace 函数和 regexp_substr 函数的功能类似与字符函数 replace 和 substr,只是这里的应用更加灵活，可以一下子匹配一系列的结果，而原来的函数只能一下子匹配一个固定的字符换。具体来讲：regexp_replace 的意义是找到于给定模式匹配的字符串并用其他的字符串来替代。其原型是：regexp_r
15、eplace(x,pattern,replace_string,start,occurencematch_option)每个参数的意思分别是：x 待匹配的函数pattern 正则表达式元字符构成的匹配模式replace_string 替换字符串start 开始位置occurence 匹配次数match_option 匹配参数，这里的匹配参数和 regexp_like 是完全一样的，可参考前面的一篇文章。举例来讲：select regexp_replace(hello everybody,may I have your attention please?,b:alpha:3,one) from
16、dual 将会返回结果：hello everyone,may I have your attention please?而 regexp_substr 函数的意义找出与给定模式匹配的字符串并返回，其原型是：regexp_substr(x,pattern,start,occurencematch_option)这里各参数的意义与前面的函数 regexp_replace 的含义是一样做的。regexp_substr 的例子如下：select regexp_substr(I love oracle very much,o:alpha:5) from dual;这里将会匹配出结果：oracle 来。这
17、也是这个函数的返回结果。和其他的关系型数据库一样，oracle 中也能进行一些隐式的数据转换，这对我们写 SQL 语句有非常用，我们可以不必麻烦地手动转化很多类型的字符。虽然前面我们介绍了一些使用例如 to_char,to_date 的函数进行强制转换的方法，但是隐式转换也还是不错的。Oracle 可以隐式地进行一些变量类别之间转化，例如从字符串转换到数值,看下面的例子。SQL select ename,sal from emp where sal = 1100;ENAME SAL -SMITH 1100ADAMS 1100这里用了对员工的工资进行了选择，我们明知道员工的工资是数值型的，但我们
18、故意把他写成了字符串型的。结果 oracle 仍然得到了正确的结果。这说明 oracle 进行了隐式的从字符串到数值直接的转换。再比如下面的例子。SQL Select last_day(26-4 月 -08) from dual;LAST_DAY(-30-4 月 -08这里我们的 last_day 函数本来是需要提供一个日期类型的参数，我们故意提供了一个字符串类型的参数。但 oracle 仍然给我们返回了正确的结果，这说明 oracle 内部进行了从字符串到日期类型的隐式转换。学要说明的是，如果这个例子在你的机器上没有成功的执行，那很又能是你的默认日期格式和这里的不同，如果你不知道你的日期格式
19、的话，你可以用 select sysdate from dual 这条 SQL 语句返回的结果来查看你的机器到底是什么日期格式。一般这个和 NLS_lang 参数的值有关这两个例子都说明了 oracle 内部确实能进行某些隐式的函数转换。下面是 oracle 中隐式转换的一般情况。从到Varchar2 or CharNumberVarchar2 or CharDateNumberVarchar2DateVarchar2需要注意的就是从 Varchar2、char 到 date 的隐式转换过程中，必须保证其格式是本机的默认时间格式。除了隐式转换以外，oracle 还提供了更为灵活的数据类型的显示
20、转换，这种转换方式更为灵活。nvlto_charto_dateaa_monthsto_char 你可以使用 select ename, hiredate, sal from emp where deptno = 10;显示信息，可是，在某些情况下，这个并不能满足 你的需求。 问题：日期是否可以显示 时/分/ 秒 SQL select ename, to_char(hiredate, yyyy-mm-dd hh24:mi:ss) from emp; 问题：薪水是否可以显示指定的货币符号 SQL yy：两位数字的年份 200404 yyyy：四位数字的年份 2004 年 mm：两位数字的月份 8
21、月08 dd：两位数字的天 30 号 30 hh24： 8 点 20 hh12： 8 点08 mi、ss 显示分钟 秒 9：显示数字，并忽略前面 0 0：显示数字，如位数不足，则用 0 补齐 .：在指定位置显示小数点 ,：在指定位置显示逗号 $：在数字前加美元 L：在数字前面加本地货币符号 C：在数字前面加国际货币符号 G：在指定位置显示组分隔符、 D：在指定位置显示小数点符号(.) 分析函数over(Partition by.) 一个超级牛皮的 ORACLE 特有函数。天天都用 ORACLE，用了快 2 年了。最近才接触到这个功能强大而灵活的函数。真实惭愧啊！oracle 的分析函数 ove
22、r 及开窗函数一：分析函数 overOracle 从 8.1.6 开始提供分析函数，分析函数用于计算基于组的某种聚合值，它和聚合函数的不同之处是对于每个组返回多行，而聚合函数对于每个组只返回一行。 下面通过几个例子来说明其应用。 1：统计某商店的营业额。 date sale1 202 153 144 185 30规则：按天统计：每天都统计前面几天的总额得到的结果：DATE SALE SUM- - -1 20 20 -1 天 2 15 35 -1 天2 天 3 14 49 -1 天2 天3 天 4 18 67 . 5 30 97 .2:统计各班成绩第一名的同学信息NAME CLASS S - -
23、 - fda 1 80 ffd 1 78 dss 1 95 cfe 2 74 gds 2 92 gf 3 99 ddd 3 99 adf 3 45 asdf 3 55 3dd 3 78 通过： -select * from ( select name,class,s,rank()over(partition by class order by s desc) mm from t2) where mm=1 -得到结果：NAME CLASS S MM - - - - dss 1 95 1 gds 2 92 1 gf 3 99 1 ddd 3 99 1 注意：1.在求第一名成绩的时候，不能用 row
24、_number()，因为如果同班有两个并列第一，row_number()只返回一个结果 2.rank()和 dense_rank()的区别是：-rank()是跳跃排序，有两个第二名时接下来就是第四名-dense_rank()l 是连续排序，有两个第二名时仍然跟着第三名3.分类统计 (并显示信息)A B C - - - m a 2 n a 3 m a 2 n b 2 n b 1 x b 3 x b 2 x b 4 h b 3 select a,c,sum(c)over(partition by a) from t2 得到结果：A B C SUM(C)OVER(PARTITIONBYA) - -
25、- - h b 3 3 m a 2 4 m a 2 4 n a 3 6 n b 2 6 n b 1 6 x b 3 9 x b 2 9 x b 4 9 如果用 sum，group by 则只能得到A SUM(C) - - h 3 m 4 n 6 x 9 无法得到 B 列值 select * from test数据:A B C 1 1 1 1 2 2 1 3 3 2 2 5 3 4 6 -将 B 栏位值相同的对应的 C 栏位值加总select a,b,c, SUM(C) OVER (PARTITION BY B) C_Sumfrom testA B C C_SUM 1 1 1 1 1 2 2 7
26、 2 2 5 7 1 3 3 3 3 4 6 6 -如果不需要已某个栏位的值分割, 那就要用 nulleg: 就是将 C 的栏位值 summary 放在每行后面select a,b,c, SUM(C) OVER (PARTITION BY null) C_Sumfrom testA B C C_SUM 1 1 1 17 1 2 2 17 1 3 3 17 2 2 5 17 3 4 6 17求个人工资占部门工资的百分比 SQL select * from salary;NAME DEPT SAL- - -a 10 2000b 10 3000c 10 5000d 20 4000SQL select
27、 name,dept,sal,sal*100/sum(sal) over(partition by dept) percent from salary;NAME DEPT SAL PERCENT- - - -a 10 2000 20b 10 3000 30c 10 5000 50d 20 4000 100二：开窗函数 开窗函数指定了分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的变化而变化，举例如下： 1： over（order by salary） 按照 salary 排序进行累计，order by 是个默认的开窗函数over（partition by deptno）按照部门分区2：
