oracle 常用函数总结

oracle的执行顺序: from --> where --->group by ---> having --->select -->order by

字符串函数：

userenv('language')查看oracle字符集

upper(字段名)：转换成大写

lower(字段名)：转换成小写

substr(字段名，初始位置，[结束位置]) ：字段截取函数（中文算一个长度）,若想字节截取用substrb

lpad(字段名,length,[char])：字段长度若不大于length,则在字段值左边补char，若省略则补空格

lpad(字段名,length，[char])：字段长度若不大于length,则在字段值右边边补char，若省略则补空格

instr(str,char,[index],[index1]):查找字符串str中位置index后的字符匹配char字符index1次的位置

length(字段名) ：取字段长度，中文字符也算一个长度。

lengthb(字段名) ：取字段长度，中文字符也算两个长度。

trim(字段名)：去前后空格

ltrim(字段名)：去前空格

rtrim(字段名)：去后空格

lpad(字段名,length)：字段长度若不大于length,则在字段值左边补空格

lpad(字段名,length)：字段长度若不大于length,则在字段值右边补空格



数值函数：

round(n[,保留位])：对n四舍五入到保留位
trunc(n[,保留闰])：对n截取到保留位


日期函数：

to_date('2015-01-01 00:01:01','yyyy-mm-dd hh24:mi:ss') 把字符串转换成时间类型（hh 是12小时制，也可用HH或HH24）

to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') 把时间转换成字段串类型

extract(year或month或day或hour或minute或second  from 日期)：获取日期的指定部分

last_day(日期)：当月份的最后一天

next_day(日期，n)：获取下个星期第n天的日期,若n为3，则取的是下周二的日期

months_between(日期，日期)：取两个时间之间的月份差

round(日期[,yyyy || mm]):对指定时间段进行四舍五入，如不指定后面的参数则取下一天

trunc(日期[,yyyy || mm]):对指定时间段进行截取，如不指定后面的参数则舍去时分秒



空值处理函数：

nvl(字段,res1)：如字段是null则返回res1

nvl2(字段，res1,res2)：如字段有值则返回res1,无值则返回res2



聚合函数：

count(字段名或*或1)，min(字段名)，max(字段名),num(字段名),avg(字段名):聚合函数，where条件后需要有group by (字段名)，若没有则全表查询



条件判断函数：

decode(表达式，值1，结果1，值2，结果2....默认结果)（oracle独有）表达式为固定值：
例：select dept_id,sum(decode(sex,'F',1,0)) F,sum(decode(sex,'M',1,0)) M from company group by dept_id;---统计各部门男女人数据

case when 表达式1 then  值1  when 表达式2 then 值2  .... else  值3 end :
例：select a.qujian,count(*) from (select (case when sal<1000 then '0-1000'
                when sal<2000 then '1000-2000'
                when sal<5000 then '2000-5000'
                 else '5000以上'  end) qujian from emp_zh a) group by qujian;
                
                

集合函数：将结果集进行合并，交集或差集

union:将结果集合并，不允许放重复集 A union B

union all:将结果集合并，允许放重复集 A union all B

intersect:将两结果集相同记录取出 A intersect B

minus:将两结果集不同记录取出，取差集
例：A(1,3,5,7,9),B(3,5,6,7,8):A minus B 则是1,9;  B minus A 则是6,8



排序函数：
rownum：伪列编号
row_number以组为单位编号

row_number() over(partition by 分组字段 order by 排序字段)：将查询结果按分组字段划分成各个小组，
之后将组内数据排序（序号是顺序）。最后追加row_number()组内序号列
例：select * from (select empno,job,ename,deptno,sal,row_number() over(partition by job order by sal desc) rn from emp_zh) where rn<2
//查询每个职位工资最高的员工
select empno,ename,deptno,sal,row_number() over(partition by deptno order by sal) rn
from emp_zh )  where rn<2; ／／查询每个部门工资最高的员工

rank() over(partition by 分组字段 order by 排序字段)：
与row_number的区别是条件相同的记录序号也相同，会跳号 1，2，2，4

dense_rank() over(partition by 分组字段 order by 排序字段)：
与rank()不同的是重复记录序号是连续的


高级分组函数：

在使用group by字句时可使用下列高级分组函数，实现小计和总计的功能

rollup(字段):增加总计
例:select nvl(job,'总计'),sum(sal) from emp_zh group by rollup(job);---每个职位小计总工资及（多出一行）全公司总计总工资
   select deptno,nvl(job,'总计'),sum(sal) from emp_zh where deptno is not null group by rollup(deptno,job) order by deptno,job
   ---分组统计小计和总公司总计
  
cube()函数:单个字段时和rollup()效果一样，多个字段进会分别进行小计
例：select nvl(job,'合计'),sum(sal) from emp_zh group by cube(job);
    select deptno,nvl(job,'合计'）,sum(sal)from emp_zh group by cube(deptno,job) order by deptno,job;
   
grouping sets():
grouping sets(字段1,字段2) 提取按字段1和字段2计算的小计记录,但没有总合计 (指定两个字段,一个字段没效果)
  select deptno,nvl(job,'合计'）,sum(sal)from emp_zh group by grouping sets(deptno,job) order by deptno,job;