总结的oracle常用函数(适合初学者)

1、字符函数

select concat('z','d') c from dual/*链接字符串的x和y*/;
select ascii('a') from dual/*返回字符的ASCII码*/;
select instr('abacacdc','a',4,1) from /*在字符串中查找某个字符或字符串,从第几位开始,出现了几次*/dual;
select length('dfsafadsdfda') from /*字符串长度,相当于Java中的length()*/dual;
select lower('kivenN') /*转为小写*/ from dual;
select upper('kivenN') /*转为大写*/ from dual;
select ltrim(' kiven N ') from /*去掉左面空格*/ dual;
select rtrim(' kiven N ') from /*去掉右面空格*/dual;
select trim(' kiven N ') from /*去掉左右两面空格*/dual;
select REGEXP_REPLACE(' kiven N k a', '( ){1,}', '')/*可以使用正则表达式*/ from dual;
select replace(' kiven N k a', 'k', 'e') /*将k替换为e*/ from dual;
select substr('kiven',0,1) /*相当于Java中的substring*/ from dual;

2、数字函数

select abs(-43) from /*绝对值*/ dual;
select ACOS(0.98) from /*反余弦*/ dual;
select cos(1) /*余弦*/ from dual;
select ceil(2.34) /*大于等于这个数的最大整数*/from dual;
select floor(2.34) /*小于等于这个数的最小整数*/from dual;
select log(2,4) /*2为底的对数*/ from dual;
select mod(8,3) /*8÷3的余数*/ from dual;
select power(2,3) /*2的3次方*/ from dual;
select round(3.4568787,2) /*保留两位小数,且四舍五入*/ from dual;
select sqrt(4) from /*平方根*/ dual;
select TRUNC(3.4568787,2) from /*保留两位小数,且不四舍五入*/ dual;

3、日期函数

/*sysdate为系统当前时间*/
select add_months(sysdate,2) /*在指定时间加'2'个月*/ from dual;
select last_day(sysdate) /*指定当月日期的最后一天*/ from dual;
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') as nowTime from /*日期转化为字符串*/ dual;      
select to_char(sysdate,'yyyy') as nowYear   from /*获取时间的年*/  dual;   
select to_char(sysdate,'mm')    as nowMonth from /*获取时间的月*/ dual;      
select to_char(sysdate,'dd')    as nowDay    from /*获取时间的日*/ dual;  
select to_char(sysdate,'hh24') as nowHour   from /*获取时间的时*/ dual;   
select to_char(sysdate,'mi')    as nowMinute from /*获取时间的分*/ dual;  
select to_char(sysdate,'ss')    as nowSecond from /*获取时间的秒*/ dual;
select to_char(sysdate,'day') from /*星期几*/dual;
select floor(sysdate - to_date('20020405','yyyymmdd')) from /*相差天数*/ dual; 
select next_day(sysdate,'星期五') from /*下一个星期五*/dual;
SELECT EXTRACT(minute FROM TIMESTAMP '2013-01-30 11:46:42') from /*获取字符串的年月日时分秒 year,month,day,hour,minute,second*/ dual;
select TO_CHAR(SYSDATE,'DDD'),sysdate /*一年的第几天*/ from dual;
select to_date('2018-04-08','yyyy-MM-dd') from /*转为日期类型*/ dual;

4、聚合函数

--oracle 12c 创建数据库后自带 EMP、DEPT、BONUS、SALGRADE共四张表
--聚合函数常与group by 一起使用
select avg(t.sal) from /*计算平均值*/ emp t;
select max(t.sal) from /*获取最大值*/ emp t;
select min(t.sal) from /*获取最小值*/ emp t;
select sum(t.sal) from /*获取总值*/ emp t;
select sum(t.sal),count(*) from /*根据mgr分组,其他聚合函数相同*/ emp t group by mgr;
select count(*) from /*统计个数*/ emp;
select deptno,sum(sal) over(partition by deptno) sum_sal from /*返回多行,其中over是分析函数*/ emp;

5、其他函数

select decode(1,1,0,2) from /*如果1=1,则返回0,否则返回2*/ dual;