Oracle-函数

函数

单行函数

分类：

1. 字符函数
2. 数字函数
3. 日期函数
4. 转换函数
5. 通用函数

字符函数：主要是进行字符串数据的操作

• UPPER(字符串|列) 将输入的字符串变为大写返回
• LOWER(字符串|列) 将输入的字符串变为小写返回
• INITCAP(字符串|列) 开头首字母大写
• LENGTH(字符串|列) 求出字符串长度
• REPLACE(字符串|列) 进行替换
• SUBSTR(字符串|列) 开始点[结束点]，字符串截取

转大写

select upper('hello') from dual;
select * from emp where ename=upper('&str');

使用字符“_”替换雇员姓名中的所有字母“A”

select replace(ename,'A','_') from emp;

字符串截取有两种语法：

SUBSTR(字符串|列,开始点)，表示从开始点一直截取到结尾

select ename,substr(ename,3) from emp;

SUBSTR(字符串|列,开始点,截取多少位)，表示从开始点截取多少位

0和1都表示从第一个开始

select ename,substr(ename,0,3) from emp;
select ename,substr(ename,1,3) from emp;

要求截取每个雇员名字的后三个字母

通过长度-2 确定开始点

select ename,substr(ename,length(ename)-2) from emp;

设置负数，表示从后指定截取位置

select ename,substr(ename,-3) from emp;

数字函数

• ROUND(数字|列[,保留小数的范围])：四舍五入的操作
• TRUNC(数字|列[,保留小数的范围]): 舍弃指定位置的内容
• MOD(数字 1，数字 2)：取模，取余数

ROUND

  select round(903.535),round(-903.535),round(903.535,-1),round(903.535,2) from 
dual;

TRUNC

select trunc(903.53567),trunc(-903.53567),trunc(903.53567,-1),trunc(903.53567,2) from dual;

MOD

select mod(10,3) from dual;
select mod(3,10) from dual;

日期函数

注意：在所有开发之中，如果是日期的操作，建议使用以上的函数，可以避免闰年的问题
取得今天的日期，可以使用‘’SYSDATE”

select sysdate from dual;

日期的计算有以下几种计算：

• 日期+数字=日期，表示若干天之后的日期
• 日期-数字=日期，表示若干天之前的日期
• 日期-日期=数字，表示两个日期之间的天数，但是必须大日期减小日期

求出每个雇员到今天为止的雇佣天数

select ename,hiredate,sysdate-hiredate from emp;

四个操作函数

LAST_DAY(日期)：求出指定日期当月的最后一天

求出本月的最后一天日期

select last_day(sysdate) from dual;

NEXT_DAY(日期，星期数)：求出下一个指定星期几的日期

下一个周六是几号

select next_day(sysdate,'sat') from dual;

下一个周日是几号

select next_day(sysdate,'sun') from dual;

ADD_MONTHS(日期，数字)：求出若干月之后的日期

四个月后是几号

select add_months(sysdate,4) from dual;

MOUNTHS_BETWEEN(日期 1，日期 2)：求出两个日期之间所经历的月份

求出每个雇员到今天为止的雇佣月份

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

转换函数

一共有三种转换函数

• TO_CHAR(字符串|列，格式字符串)：将日期或者数字变为字符串显示；
• TO_DATE(字符串，格式字符串)：将字符串转变为 DATE 数据显示；
• TO_NUMBER(字符串)：将字符串变为数字显示.

TO_CHAR()

拆分年月日

格式：年(yyyy),月(mm),日(dd)

select to_char(sysdate,'yyyy-mm-dd'),to_char(sysdate,'yyyy') year,to_char(sysdate,'mm') 
month,to_char(sysdate,'dd') day from dual;

干掉日期中的前导零

select to_char(sysdate,'fmyyyy-mm-dd') day from dual;

使用 sysdate 显示时间

select to_char(sysdate,'yyyy-mm-dd hh:mi:ss') haha from dual;
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(22222222222,'999,999,999,999,999,999,999') shuzi from dual;

格式化数字为货币格式

使用“L”标记表示转换为当前语言环境下的货币符号

select to_char(22222222222,'L999,999,999,999,999,999,999') huobi from dual;

TO_DATE()

TO_DATE(): (熟悉用法)

select to_date('1989-09-12','yyyy-mm-dd') from dual;

TO_NUMBER()

TO_NUMBER(): (基本不用，会用即可)

select to_number('2')+to_number('3') from dual;

在 oracle 中，不使用该函数也可以完成该功能，如下：
select ‘2’+‘3’ from dual;

通用函数

通用函数主要有 NVL(),DECODE()两个,这两个函数算是 oracle 的特色函数

NVL() 处理 null

comm 中的 null 值变成 0

查看转换过程

select ename,sal,comm,nvl(comm,0) from emp;

DECODE()：多数值判断

• 判断的是数值而不是条件，类似编程中的 if…else…
• 说明：这种判断肯定是进行逐行判断，所以这个时候就必须采用
• DECODE(),而此函数的用法如下：
  DECODE(数值|列，判断值 1，显示值 1，判断值 2，显示值 2，…)

统计函数

分类：

• COUNT():查询表中的数据记录
• AVG(): 求出平均值
• SUM(): 求和
• MAX(): 求出最大值
• MIN(): 求出最小值

COUNT()

COUNT()函数的主要功能是进行数据的统计，但是在进行数据统计的时候，如果一张表中没有记录，那么 COUNT()也会返回数据，只是这个数据是“0"
null不计算在内

分组统计

当数据重复的时候分组才有意义，一个人也可以单独分一组，但是没有意义
GROUP BY 子句，语法如下：

SELECT [DISTINCT]*| 字段 [别名][字段 [别名]] | 统计函数
FROM 表名称 [别名],[表名称 [别名],...]
[WHERE 条件(s)]
[GROUP BY 分组字段 1 [,分组字段 2,...]]
[ORDER BY 排序字段 [ASC|DESC][排序字段 [ASC|DESC],...]]

分组函数有以下要求：

1、分组函数可以在没有分组的时候单独使用，可是不能出现其他的查询字段
2、如果要进行分组，则 select 子句之后，只能出现分组的字段和统计函数，其他字段不能出现
3、分组函数允许嵌套，但是嵌套之后的分组函数的查询之中不能再出现任何的其他字段

再次过滤 HAVING

如果要对分组后的数据再次进行过滤，需要使用 HAVING 子句，语法格式如下：

SELECT [DISTINCT]*| 字段 [别名][字段 [别名]] | 统计函数
FROM 表名称 [别名],[表名称 [别名],...]
[WHERE 条件(s)]
[GROUP BY 分组字段 1 [,分组字段 2,...]]
[HAVING 分组后的过滤条件(可以使用分组函数)]
[ORDER BY 排序字段 [ASC|DESC][排序字段 [ASC|DESC],...]]

select d.deptno,d.dname,d.loc,count(e.empno),trunc(nvl(avg(e.sal),0))
from dept d,emp e
where d.deptno=e.deptno(+)
group by d.deptno,d.dname,d.loc
having avg(sal)>2000;

WHERE 和 HAVING 的区别

• WHERE:在执行 GROUP BY 操作之前进行的过滤，表示从全部数据中进行过滤，不能使用统计函数；
• HAVING: 在 GROUP BY 分组之后的再次过滤，可以使用统计函数。