引言

虚表dual ，它是一行一列，没有任何数据，常常用于测试。

它是oracle特有的用于做函数测试的。

I Oracle常用函数

1.1 字符函数

   upper(str) --,转为大写
   lower(str)--,转为小写
   substr(str,n,m)-- ,从n位开始，截取m个字符
   substr(str,n)--,从n位开始，截取后面字符
   length(str),--得到字符串的长度
   ltrim(str),--去掉左边空格
   rtrim(str),--去掉右边空格
   instr(str,c),--得到字符c在str的位置
   lpad(str,n,c),--将str补足为n位长度,不足左边用字符c代替
   rpad(str,n,c),--将str补足为n位长度,不足右边以字符c代替 
   concat --连接字符串
   initcap(参数) --使当前的字符串的单词的首字母大写

select  initcap('hello zhangkunnan') from dual;

select substr('hello',1,2) from dual ;--小标从1开始

select * from emp where ename=upper（‘&name’）;

1.2 数值函数

• ceil : 向上取整
• floor: 向下取整
• round(col,n) 四舍五入

round(457.628,2),小数点后2位四舍五入
      结果 457.63
round(457.628,-1),小数点前1位四舍五入
      结果460

trunc(col,l) 截断数值

当l为正数时，表示截取小数点右边的位数；l为负数时，表示过滤小数点左边的位数。当l不写的时候，效果与l=0一样。

trunc(457.628,2)  结果457.62
trunc(457.628,-1) 结果450 

1.3 日期函数

months_between(date1,date2),两个日期间的月数，结果为实数。
add_months(date,m),增加m个月，m可以为负数，结果为减少m个月。
round,日期四舍五入
trunc,截断日期
last_day ,当月最后一天

日期函数例子

--当前日期增加1个月
select add_months(sysdate,1) from dual;
--去年同月
select add_months(sysdate,-12) from dual;
--年
select trunc(sysdate,'YYYY') from dual;
--月
select  trunc(sysdate,'MM') from dual;
--精确到天，截断小时分秒
select  trunc(sysdate) from dual;
-- 当月最后一天
select last_day(sysdate) from dual;

1.4 转换函数

1. 日期转为字符:

to_char(date1,’format_model’)
--format_model:转换后的显示格式
--YYYY  年,MM 月,DD 日,HH24 小时,MI 分,SS 秒

例子:

select  to_char(sysdate,'YYYY-MM-DD HH24:MI:SS') rq
from dual;

1. 字符转为日期

to_date('2007-11-11','YYYY-MM-DD')

1. 数值转为字符

select  to_char(55676,'fm99,999.00')  from dual
--fm表示去掉前面的空格和0
--结果: 55,676.00

1.5 通用函数

1. nvl(字段，默认值)： 用于判断字段是否为空，如果为空就返回指定的值，否则返回字段本身的值。

select enamel，sal+nvl(comm,0) from emp;

1. Decode： 分支判断

    Decode(表达式, 条件1,结果1,
                                   条件2,结果2,
                                    …
                                      ,默认结果)
   select decode（&no,1,'值1'，2，'值2'） from dual

II 聚合函数(组函数)

多行函数，输入一组记录，输出一行记录。

max、 min、 avg、 sum和 count函数。

• 聚合函数，不能出现在where子句中， 比如where avg(salary) >4000。
• 除了count(*)之外，其它的不统计null值

  count(*)  --,所有行数量
  count(swjg_mc)--,swjg_mc非null值的记录的数量
  count(distinct swjg_dm)--,去掉重复的记录
  count(1),--第一列非null值的记录的数量

III 伪列：给返回结果加上序号

rownum伪列，可以给返回结果加上序号。

如果有order by,序号是乱的，因为序号是order by之前分配的。

加一个子查询可以解决序号是乱的问题

select t.*, rownum
  from (select swjg_dm, swjg_mc 
             from dm_swjg 
       order by swjg_dm ) t

IV 常用的分析函数

row_number() over(partition by ... order by ...) 
rank() over(partition by ... order by ...) 
dense_rank() over(partition by ... order by ...) 
count() over(partition by ... order by ...) 
max() over(partition by ... order by ...) 
min() over(partition by ... order by ...) 
sum() over(partition by ... order by ...) 
avg() over(partition by ... order by ...) 
first_value() over(partition by ... order by ...) 
last_value() over(partition by ... order by ...) 
lag() over(partition by ... order by ...) 
lead() over(partition by ... order by ...)