二、Oracle函数

2.1单行函数

字符操作

• lower转小写

  select lower('Qin') from dual;

• upper转大写

  select upper('Qin') from dual;

• initcap 首字母大写

  select upper('qin') from dual;

• substr(a,b)截取字符串

  select substr('Hello,How Are You',3) from dual;

  从a中，第b位开始取

• substr(a,b,c)截取字符串

  select substr('Qin Hai Bo',7,3) from dual;

  从a中，第b位开始取，取后面c个字符

• instr(a,b)

  select instr('hello','ll') from dual;

  b字符串在a中的位置

• length('') 字符数

• lengthb('')字节数

• lpad(a,b,c)||rpad()

  select lpad('abc',10,'*') from dual;

  把a用c填充为b位长度

• trim 去掉前后指定字符

  select trim('abcd ') from dual;

• replace(a,b,c)

  select replace('hello','ll','**')from dual;

  把a中的b替换为c

浮点操作

• round(a,b)四舍五入

  select round(166.345,2) from dual;

  把a保留b位小数四舍五入

• trunc(a,b)截断

  select trunc(233.234,2);

  把a截取b位后面的数字

时间格式化

操作	示例
sysdate 当前时间	select sysdate from dual;
to_char 格式化时间	select to_char(sysdate,'yyyy-mm-dd' HH24:mi:ss) from dual;
时间相加减	select sysdate-1;
months_between相差月	select months_between(sysdate,hiredate) from emp;
add_months当前加n个月的时间	select add_month(sysdate,12) from dual;
last_day 最后一天	select last_day(sysdate) from dual;
next_day 下个星期几	select next_day(sysdate,'星期日') from dual;用于设置自动备份数据
to_char(a,b)也可以格式数字	select to_char(sal,'L9999.99') from emp;

函数常用格式

字符	含义
9	数字
0	零
$	美元符
L	本地货币符号
.	小数点
，	千位符

空操作

函数	示例
nvl2(a,b,c) 当a=null时返回c,否则返回b	select username,nvl2(haibo,haibo,0) from dual;
nullif(a,b) 当a=b时候返回null,否则返回a	select nullif('abc','abc') from dual;
coalesce(a,b)从左到右找第一个不为null的值	select comm,sal ,coalesce(comm,sal) from emp;
nvl(a,b) 当a为null,返回b否则返回a本身

条件判断

• case when then end

  SELECT 
  CASE 
  WHEN `beginDate` <= '2018-01-01' THEN '段前'
  WHEN `beginDate` >= '2019-01-01' THEN '段后'
  WHEN `beginDate` >  '2018-01-01' AND `beginDate` <  '2019-01-01' THEN '段中'
  END AS `types`, COUNT(beginDate)
  FROM employee
  GROUP BY `types` 
  

• decode

  select id,
         name,
         age 涨前,
         decode(name, '王五', age + 10, '王五2', age + 20) 涨后
    from test;
  

2.2分组函数

常用函数组

• avg() 平均数
• count(*) 记录数
• max() 最大值
• min() 最小值
• sum() 求和

group by

select [column,...] group function(column),...
from table
[where condition]
[group by column]
[group by column]
[having...]