Oracle快速入门（常用函数大全）

（4）替换字符串

select replace(‘hello’,‘l’,‘a’) from dual;//将hello当中的l替换为a

2、数值函数：

对数值进行计算或操作，返回一个数字。例如：ABS()、MOD()、ROUND()等等。

1）四舍五入

（1）ceil()向上取整

select ceil(2121.1212) from dual;

（2）floor()向下取整

select floor(45.926,2) from dual;//保留两位小数

（3）round()保留小数

select round(45.926,1) from dual;//保留一位小数

select round(45.926,0) from dual;取整

select round(45.926,-1) from dual;//对个位进位

select round(45.926,-2) from dual;//对十位进行进位

select round(65.926,-2) from dual;

（4）截断函数：trunc()不会进位直接截断

select trunc(45.926,2) from dual;//截断小数点后两位

select trunc(45.926,1) from dual;//截断小数点后一位

select trunc(45.926,0) from dual;//截断取整

select trunc(45.926,-1) from dual;//截断小数点前一位

select trunc(65.926,-2) from dual;//截断小数点前两位

（5）求余

select mod(9,3) from dual;

select mod(9,4) from dual;

3、转换函数：

将一种数据类型转换成另外一种类型：例如：TO_CHAR()、TO_NUMBER()、TO_DATE()等等。

数值转字符 字符转数值 日期类型

（1）字符转数值to_number(str)

select 100+‘10’ from dual;//已经自动转换

select 100 + to_number(‘10’) from dual;

两种方式效果相同

（2）数值转字符

select ename,to_char(sal,‘$9,999.99’) from emp;

（3）日期转字符to_char()

• 设置日期格式

select to_char(sysdate,‘yyyy-mm-dd hh:mi:ss’) from dual;

• 设置日期格式显示24小时制

select to_char(sysdate,‘yyyy-mm-dd hh24:mi:ss’) from dual;

• 设置年

select to_char(sysdate,‘yyyy’) from dual;

• 代表一个星期的第几天

select to_char(sysdate,‘d’) from dual;

• 代表一个月中的第几天

select to_char(sysdate,‘dd’) from dual;

• 代表一年中的第几天

select to_char(sysdate,‘ddd’) from dual;

• 获取星期

select to_char(sysdate,‘day’) from dual;//获取星期的全称

select to_char(sysdate,‘dy’) from dual;//获取星期的缩写

4、日期函数：

对时间和日期进行操作的函数。

例如：TRUNC()、SYSDATE()、ADD_MONTHS()等等。

（1）查询今天的日期

select sysdate from dual;

（2）查询3个月后的今天的日期

select add_months(sysdate,3) from dual;

（3）查询3天后的今天的日期

select sysdate + 3 from dual;

（4）查询员工入职的天数

select ename,ceil(sysdate - HIREDATE) from emp;

（5）查询员工入职的周数

select ename,ceil((sysdate - HIREDATE)/7) from emp;

（6）查询员工入职的月数

select ename,ceil(months_between(sysdate,hiredate)) from emp;

（7）查询员工入职年份

select ename,ceil(months_between(sysdate,hiredate)/12) from emp;

（8）字符转日期：

select to_date(‘2017-04-10’,‘yyyy-mm-dd’) from dual;

（9）查询1981年到1985年入职的员工信息

select * from emp where hiredate between to_date(‘1981’,‘yyyy’) and to_date(‘1985’,‘yyyy’);

5、通用函数：

（1）nvl(参数1,参数2);//如果参数1为null返回参数2

（2）nvl(参数1,参数2,参数3);//如果参数1位null就返回参数3，否则返回参数2

select nvl2(null,5,6) from dual;

select nvl2(1,5,6) from dual;

（3）nullif(参数1,参数2) 如果 参数1 等于 参数2 那么就返回null,否则返回 参数1

select nullif(1,1) from dual;

select nullif(1,2) from dual;

select nullif(5,6) from dual;

select nullif(6,6) from dual;

（4）coalesce() 返回第一个不为null参数的函数

select coalesce(null,null,3,5,6) from dual;

select coalesce(null,null,null,5,6) from dual;

二、聚合函数（多行函数）：

---

聚合函数同时可以对多行数据进行操作，并返回一个结果。比如 SUM(x)返回结果集中 x 列的总合。

聚合函数直接忽略空值（null）

max();

min();

count();

sum();

avg();

1、统计员工工资总和

select sum(sal) from emp;

2、统计员工奖金总和

select sum(comm) from emp;

3、统计员工人数

select count(1) from emp;

4、统计员工的平均奖金

select avg(comm) from emp;

这样是错误的结果在计算的时候加上了空值

需要这样计算

select sum(comm)/count(1) from emp;

三、条件表达式

---

1、case…when通用的写法，在Oracle和mysql当中都可以使用

case 字段

when 值1 then 值

when 值2 then 值

else 默认值

end

（1）给表中项目起一个中文名

select

case ename

when ‘SMITH’ then ‘史密斯’

WHEN ‘ALLEN’ then ‘爱兰’

else ‘路人甲’

end

from emp;

（2）起一个别名 在end后面双引号号内添加

select

case ename

when ‘SMITH’ then ‘史密斯’