Oracle学习笔记SQL常用函数

函数的分类
Oracle 提供一系列用于执行特定操作的函数

SQL 函数带有一个或多个参数并返回一个值
以下是SQL函数的分类：

单行函数对于从表中查询的每一行只返回一个值
可以出现在 SELECT 子句中和 WHERE 子句中
单行函数可以大致划分为：
　　1.日期函数
　　2.数字函数
　　3.字符函数
　　4.转换函数
　　5.其他函数
日期函数

日期函数对日期值进行运算，并生成日期数据类型或数值类型的结果
日期函数包括：
　　1.ADD_MONTHS
　　2.MONTHS_BETWEEN
　　3.LAST_DAY
　　4.ROUND
　　5.NEXT_DAY
　　6.TRUNC
　　7.EXTRACT

--Sysdate:获取系统时间函数
Select sysdate from dual;

--Add_months:指定日期增加（减少）月份的时间
Select add_months('24-10月-15', 12) from dual;
Select add_months(sysdate, 10) from dual;

--Months_between:返回两个时间之间的月份的差
Select months_between(sysdate, '24-12月-15') from dual;

--Last_day:返回本月最后一天
Select last_day(sysdate) from dual;

--Next_day:下一个日期是什么时候
Select next_day(sysdate, '星期六') from dual;

--To_date(日期， 日期格式):将一个日期按照指定的格式转成一个日期数据;
Select to_date('2015/10/24', 'yyyy-mm-dd') from dual;

1. 字符函数

数字函数

余弦函数

Sin(n)

Select sin(0) from dual;

0
正弦函数
Floor(n)

Select floor(100.2) from dual;

100
四舍五入
Power(m,n)

  Select power(4,2) from dual;

16

幂函数

Mod(m,n)

Select mod(10,3) from dual;

1

求余函数

Round(m,n)

Select round(100.256,2) from dual;

100.26

向上把数值字段舍入为指定的小数位数

Trunc(m,n)

Select trunc(100.256,2) from dual;

100.25
向下把数值字段舍入为指定的小数位数
Sqrt(n)
Select sqrt(4) from dual;
2
平方根函数
Sign(n)
Select sign(-30) from dual;
-1
符号函数
当x<0时，sgn(x)=-1
当x=0时，sgn(x)=0
当x>0时，sgn(x)=1

转换函数

转换函数将值从一种数据类型转换为另一种数据类型
常用的转换函数有：
　　TO_CHAR --转换为字符
　　TO_DATE --转换为时间类型
　　TO_NUMBER --转换为Number类型

转换空值函数:

以下是几个用来转换空值的函数：
　　NVL
　　NVL2
　　NULLIF

Select sal, comm, sal+nvl(comm, 0) from emp;  --如果comm为null，用0表示
select itemdesc, NVL(re_level,0) from itemfile;
select itemdesc, NVL2(re_level,re_level,max_level) from itemfile;
select itemdesc, NULLIF(re_level,max_level) from itemfile;

• 分组函数

分组函数基于一组行来返回结果
为每一组行返回一个值

Avg:求整个列的平均值

Select avg(sal) from emp; --平均工资

Min:最小值

Select min(sal) from emp;

Max:最大值

Sum:总和

Count:计算条数；注：如果写的是列名，则不统计null行。

Select count(*) from emp where job='MANAGER'; --统计有多少个经理
Select sum(sal) from emp;  --统计工资的总和
Select count(*) from emp where deptno = 30;   --统计部门号为30的有多少个人
select count(distinct job) from emp;  --有多少个岗位，去除重复数据

注：查询出来的结果不能喝其他行列一同显示：

select ename, min(sal) from emp;–错误写法

GROUP BY子句

用于将信息划分为更小的组
每一组行返回针对该组的单个结果
select p_category, MAX(itemrate) from itemfile group by p_category;
HAVING子句

用于指定 GROUP BY 子句检索行的条件

select p_category, MAX(itemrate) from itemfile group by p_category having p_category not in ('accessories');

分析函数
分析函数根据一组行来计算聚合值
用于计算完成聚集的累计排名、移动平均数等
分析函数为每组记录返回多个行

以下三个分析函数用于计算一个行在一组有序行中的排位，序号从1开始
1.ROW_NUMBER 返回连续的排位，不论值是否相等
2.RANK 具有相等值的行排位相同，序数随后跳跃
3.DENSE_RANK 具有相等值的行排位相同，序号是连续的

select d.dname, e.ename, e.sal, DENSE_RANK()
  OVER (partition by e.deptno order by e.sal desc)
  as denrank
from emp e, dept d WHERE e.deptno = d.deptno;