1、wm_concat函数:已逗号分隔联接列的值;
如:select wmsys.wm_concat(NAME) from employee;
结果: user1,user2
2、dual表:他是一张虚拟表,我们可以理解为伪表;
如:select 90*80 Results from dual
3、nvl函数:NVL是Oracle PL/SQL中的一个函数。它的格式是NVL( string1, replace_with)。它的功能是如果string1为NULL,则NVL函数返回replace_with的值,否则返回string1的值,如果两个参数都为NULL ,则返回NULL;
4.regexp_like函数:他类似like,可以使用正则表达式来进行匹配;
如:--查询以12或者1b开头的记录.区分大小写。
select * from fzq where regexp_like(value,'^1[2B]');
5.round函数:传回一个数值,该数值是按照指定的小数位元数进行四舍五入运算的结果。
如:select round(123.456, 0) from dual; 回传 123
select round(123.456, 1) from dual; 回传 123.5
select round(123.456, 2) from dual; 回传 123.46
6.nullif函数:如果两个表达式的值相等则返回空值,否则返回第一个表达式的值
7.decode函数:
decode(条件,值1,翻译值1,值2,翻译值2,...值n,翻译值n,缺省值)的理解如下:
if (条件==值1)
then
return(翻译值1)
elsif (条件==值2)
then
return(翻译值2)
......
elsif (条件==值n)
then
return(翻译值n)
else
return(缺省值)
end if
8.chr()函数:将ASCII码转换为字符
如:select chr(39) from dual
9.ascii():将字符转换为ascii码
如:select ascii('A') from dual
10.nlssort()函数:在Oracle9i之前,中文是按照二进制编码进行排序的,在Oracle9i中增加了拼音、部首、笔画排序功能。
ALTER SESSION SET NLS_SORT=''; //排序影响整个会话
拼音 SELECT * FROM 表名 ORDER BY NLSSORT(字段名,'NLS_SORT = SCHINESE_PINYIN_M')
笔划 SELECT * FROM 表名 ORDER BY NLSSORT(字段名,'NLS_SORT = SCHINESE_STROKE_M')先笔划在部首
部首 SELECT * FROM 表名 ORDER BY NLSSORT(字段名,'NLS_SORT = SCHINESE_RADICAL_M')先部首再笔划
11.over()函数:可实现按指定的字段分组排序,对于相同分组字段的结果集进行排序。
over(partition by columnname1 order by columnname2)
其中partition by为分组字段,order by为排序字段,over不能单独使用,要和分析函数:rank(),dense_rank(),row_number()等一起使用。
分析函数用于计算基于组的某种聚合值,他和聚合函数的不同之处在于每个组返回多行;而聚合函数对于每个组只返回一行。
如:
(1)原始数据:select rowid,e.* from employInfo e
(2)select e.userid,e.username,e.sal,e.depno,
sum(e.sal) over (order by e.userid) 连续求和,
sum(e.sal) over () 总和, -- 此处sum(sal) over () 等同于sum(sal)
100*round(sal/sum(sal) over (),4) "份额(%)"
from employInfo e
(3)select e.userid,e.username,e.sal,e.depno,
sum(e.sal) over(partition by e.depno order by e.userid)
from employInfo e
(4)select e.userid,e.username,e.sal,e.depno,
sum(sal) over (partition by depno order by userid) DepConSum, --按部门“连续”求总和
sum(sal) over (partition by depno) DepSum, --按部门求总和
sum(sal) over (order by depno,userid) NoDepSum, --不按部门“连续”求总和
sum(sal) over () AllSum--不按部门,求所有员工总和,效果等同于sum(sal)。
from employInfo e
12.Rank(), Dense_rank(), row_number()函数:
①ROW_NUMBER:
Row_number函数返回一个唯一的值,当碰到相同数据时,排名按照记录集中记录的顺序依次递增。
②DENSE_RANK:
Dense_rank函数返回一个唯一的值,除非当碰到相同数据时,此时所有相同数据的排名都是一样的。
③RANK:
Rank函数返回一个唯一的值,除非遇到相同的数据时,此时所有相同数据的排名是一样的,同时会在最后一条相同记录和下一条不同记录的排名之间空出排名。
13.TRUNC()函数:
关于TRUNC函数的format,format为day时,只精确到天,而不管几年几月只要是符合的day就可以了,要想确定一年中的某月的某一天就要用trunc(date,''dd'').
通俗的说吧,format为年时,精确到-----年
为月时,精确到------年,月(不管哪年,只要是相同的月和哪天)
为日时,精确到------年,月,日(不管哪年的哪月,只关心是哪天)
1.TRUNC(for dates)
TRUNC函数为指定元素而截去的日期值。
其具体的语法格式如下:
TRUNC(date[,fmt])
其中:
date 一个日期值
fmt 日期格式,该日期将由指定的元素格式所截去。忽略它则由最近的日期截去
下面是该函数的使用情况:
TRUNC(TO_DATE(’24-Nov-1999 08:00 pm’,’dd-mon-yyyy hh:mi am’))
=’24-Nov-1999 12:00:00 am’
TRUNC(TO_DATE(’24-Nov-1999 08:37 pm’,’dd-mon-yyyy hh:mi am’,’hh’)) =’24-Nov-1999 08:00:00 am’
round (date,''format'')未指定format时,如果日期中的时间在中午之前,则将日期中的时间截断为12 A.M.(午夜,一天的开始),否则进到第二天。
TRUNC(date,''format'')未指定format时,将日期截为12 A.M.,不考虑是否在中午之前的条件。
2.TRUNC(for number)
TRUNC函数返回处理后的数值,其工作机制与ROUND函数极为类似,只是该函数不对指定小数前或后的部分做相应舍入选择处理,而统统截去。
其具体的语法格式如下
TRUNC(number[,decimals])
其中:
number 待做截取处理的数值
decimals 指明需保留小数点后面的位数。可选项,忽略它则截去所有的小数部分
下面是该函数的使用情况:
TRUNC(89.985,2)=89.98
TRUNC(89.985)=89
TRUNC(89.985,-1)=80
注意:第二个参数可以为负数,表示为小数点左边指定位数后面的部分截去,即均以0记。