本文共 4469 字，大约阅读时间需要 14 分钟。

Oracle SQL语句中经常使用到Oracle自带的函数，这些函数丰富了SQL的语言功能，为Oracle SQL提供了更多的操作性。Oracle函数可以接受零个或者多个输入参数，并返回一个输出结果。 Oracle 数据库中主要使用两种类型的函数：

一、单行函数：

对每一个函数应用在表的记录中时，只能输入一行中的列值作为输入参数(或常数)，并且返回一个结果。

例如1：MOD(X,Y) 是求余函数，返回的X除以Y的余数，其中X和Y可以是列值，也可以是常数。

例如2：TO_CHAR(X,‘YYYYMMDD’)是时间类型转字符串的函数，其中X可以是行中某一时间类型（date）的列，也可以是一个时间类型的常数。

常用的单行函数大致以下几类：

1、字符串函数：

对字符串进行操作

（1）substr(str1,起始索引,长度)截取部分字符串

select substr('abcde',0,3) from dual;

注意：起始索引不管写0还是1 都是从第一个字符开始截取

select substr('abcde',1,3) from dual;

select substr('abcde',2,3) from dual;

select substr('abcde',-1,3) from dual;

（2） 获取字符串长度 length()

select length('abcdefg') from dual;

（3）去除字符左右两边空格

select trim(' hello ') from dual;

（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 '路人甲'       endfrom emp;

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

select      case ename        when 'SMITH' then '史密斯'        WHEN 'ALLEN' then '爱兰'               else '路人甲'       end "中文名字"from emp;

2、case…when，Oracle当中特有的写法：decode(字段if1,then1,if2,then2,else1);

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

select decode(ename,'SMITH','史密斯','ALLEN','爱伦','SCOTT','史考特','路人乙') from emp;

四、分组排序

1、分组表达式group by

select 分组的条件,分组之后的操作 from 表名 group by  分组的条件 having 条件过滤

2、分组统计所有部门的平局工资，找出平局工资大于2000的部门

select ename,avg(sal) from emp group by ename having avg(sal) > 2000;

3、SQL 的编写顺序：

select->from->where->group by->having->order by

4、SQL执行顺序

from->where->group by->having->select->order by

5、where和having的区别

where 后面不能接聚合函数，可以接单行函数

having 是在group by 后面执行，可以接聚合函数

转载地址：https://code100.blog.csdn.net/article/details/108173670 如侵犯您的版权，请留言回复原文章的地址，我们会给您删除此文章，给您带来不便请您谅解！