数据库篇-oracle函数，数据库篇-oracle函数

1.round

例子：

select round(23.4),round(23.45,1),

round(23.45,-1) from employees;

结果：23 23.5 20

2.trunc 取整精度函数

select trunc(12.35) from dual

结果：12

3.ceil/floor 取整函数

例子：

select ceil(23.45),floor(23.45) from emp;

结果：24 23

4.计算函数

(1) abs 取绝对值

例子：

select abs(23.45),abs(-23),abs(0) from emp;

结果：23.45 23 0

(2) mod(m,n) 取余数

select mod(5,2) from emp 结果： 1

select mod(5,null) from emp 结果就是空

(3) power(m,n) 返回m的N次幂

select power(2,3) from emp;

(4) sqrt：求平方根

select sqrt(16) from emp 结果 4

5.聚合函数

(1)sum 求和函数

select sum(a.salary) from stuinfo a;

(2)count 统计函数

select count(*) from stuinfo a;

(3)avg 平均函数

select avg(a.salary) from stuinfo a;

(4)max 最大值函数

select max(a.salary) from stuinfo a;

(5)min 最小值函数

select min(a.salary) from stuinfo a;

6.字符函数

(1) upper 小写转大写

(2) lower 大写转小写

(3) initcap首字母大写

(4) substr （char,[m[,n]]）获取子字符串函数

注：负号是从结尾往回数

select substr('abcde',2,3),substr('abced',2),

substr('abced',-2,1) from emp;

结果： bcd bcde e

(5) length获取字符串长度

select length('abc') from emp;

结果：3

(6) concat（char1,char2）字符串连接函数或者||

concat（char1,char2）

select concat('ab','cd') from emp;

和select 'ab' || 'cd' from emp;结果一样

(7) TRIM去除子串函数 （c2 from c1）

该函数表示从字符串c1中去除字符c2

select trim('a' from 'abcde') from emp;

结果： bcde

LTRIM（c1[,c2]）：从c1中去除c2

selct ltrim('ababaa','a') from emp;

结果：babaa 结果是去除掉第一个a了

rtrim(c1[,c2]) 从尾部去除字符；

selct rtrim('ababaa','a') from emp;

结果：abab

TRIM(c1):去除左边空格

(8) 替换函数REPLACE(char,s_string[,r_string])

省略r_string用空格替换

例子：将a替换成大写的A

select replace('abcde','a','A') from emp;

结果 Abcde 要是不写‘A’就去掉a了；

替换ab：

select replace('abcde','ab','A') from emp;

结果 Acde 就是ab同时被换

(9) instr查找函数

在一个字符串中查找另外一个字符串

select instr('sdsdddssd','ds') from dual;

从第二位置查找，查找第二次出现ds的位置是多少。

select instr('sdsdddssd','ds',2,2) from dual;

7.日期函数：

(1) sysdate系统时间：

默认格式：DD-MON-RR

select sysdate from emp; --返回当前时间

(2) 日期操作add_months(date,i)

i可以是任何的整数 如果i 是负数 就相应的减去几个月

例子：在当前日期加上3个月或者减去3个月

select add_months(sysdate,3),

add_months(sysdate,-3) from emp;

结果：22-8月-15 22-2月 -15

(3) next_day(date,char) 星期几

下周一是哪天

select next_day(sysdate,'星期一') from emp;

(4) last_day(date) 返回所在月最后一天

select last_day(sysdat) from emp;

(5) MONTHS_BETWEEN(date1,date2)

表示两个日期之间相隔的月份差

select months_between('20-5月-15','10-1月-15‘)

from emp;

结果：4

(6) extract(date from datetime)

返回当前日期的年份

select extract(year from hire_date),

extract(month from hire_date),

extract(day from hire_date) from employees;

例子：

返回年份

select extract(year from sysdate) from emp;

返回月份

select extract(month from sysdate) from emp;

返回天

select extract(day from sysdate) from emp;

获取小时

select extract(hour from timestamp '2015-10-1 17:25:13')

from emp;

8.转换函数

(1) 日期转换成字符的函数to_char

to_char(date[,fmt[,params]])

参数说明：date:将要转换的日期 。

fmt: 转换的格式 params：日期的语言

日期转换日期的函数如下

格式： YY YYYY YEAR 年 。

MM MONTH 。 月

DD DAY 。 天

HH24 HH12 。 时

MI SS 。 分

例子：select

to_char(sysdate,'YYYY-MM-DD HH24:MI:SS')

FROM emp;

(2) 字符转换成日期的 to_date

to_date和to_cahr区别就是 to_date转换后的日期

格式跟系统一样，一般用to_char这样是可以自己

选择要什么样的格式，to_date: 03-9月-17

to_date(date[,fmt[,params]])

select

to_date('2015-05-22','YYY-MM-DD')

from emp;

一般结果会根据系统设置的一样

结果：03-9月-17

例子2：

查询2006年入职的员工信息日期是2006/1/16

select * from employees

where to_char(hire_date,'YYYY/MM/DD')

between '2006/01/01' and '2007/01/01';

select * from employees

where hire_date like '%';

(3) 数字转换成字符的函数

to_char(number[,fmt])

9:显示数字并忽略前面的0

0:显示数字，位数不足，用0补齐

.或D：显示小数点

,或G: 显示千位符

$:美元符号

S：加正负号（前后都可以）

例子：

select to_char(12345.678,'$99.999.999')

from emp;

结果：$12,345.678

select to_char(12345.678,'S99.999.999')

from emp;正负号：

结果： 12，345.678

(4) 字符转换成数字的函数

to_number(char,[,fmt])

例子：select to_number('$1000','$9999')

from emp;

结果：1000

9. 填充函数

左填充 lpad 右填充 rpad

select lpad('abcd',10,'*') 左，

rpad('abcd',10,'*') 右

from dual;

结果：

******abcd abcd******

10. NVL通用函数

(1) NVL(expr1,expr2)

如果oracle第一个参数为空那么显示第二个参数的值，

如果第一个参数的值不为空，则显示第一个参数本来

的值。

例如：

SQL> select ename,NVL(comm, -1) from dual;

(2) NVL2函数

如果该函数的第一个参数不为空那么显示第二个参数

的值，如果第一个参数的值为空，则显示第三个参数

的值.

SQL> select ename,NVL2(comm,-1,1)

from emp;

(3) NULLIF函数

NULLIF(exp1,expr2)函数

作用是如果exp1和exp2相等则返回空(NULL)，

否则返回第一个值。

11. Coalesce函数

Coalese函数的作用是的NVL的函数有点相似，

其优势是有更多的选项。

格式如下：

Coalesce(expr1, expr2, expr3….. exprn)

含义：

返回表达式中第一个非空表达式

SELECT COALESCE(NULL,NULL,3,4,5)

FROM dual 　

其返回结果为：3

如果所有自变量均为 NULL，则 COALESCE

返回 NULL 值。

12.wm_concat:字段合并函数

将一列以行的形式展现

原表name是：

a

b

c

d

转化后

select wm_concat(name) from dual;

结果a,b,c,d,e

把结果里的逗号替换成"|"

select replace(wm_concat(name),',','|')

from dual;

13.over分析函数

按部门“连续”求总和

sum(sal) over (partition by deptno order by ename) ;

按部门求总和

sum(sal) over (partition by deptno) ;

不按部门“连续”求总和

sum(sal) over (order by deptno，ename) ;

不按部门，求所有员工总和，效果等同于sum(sal)。

sum(sal) over ()

常用的分析函数如下所列:

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 ...)

14.decode函数

select employee_id,

decode(sign(salary-17000),1,'优秀',0,'优秀',-1,

decode(sign(salary-6000),1,'良好',0,'良好',-1,

decode(sign(salary-4200),1,'及格',0,'及格',-1,'不及格')))

from employees;

,