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;

字符函数

字符函数接受字符输入并返回字符或数值

函数	输入	输出	说明
Initcap(char)	Select initcap(‘hello’) from dual;	Hello	将首字母大写
Lower(char)	Select lower(‘FUN’) from dual;	fun	大写转换为小写
Upper(char)	Select upper(‘sun’) from dual;	SUN	小写转换为大写
Ltrim(char,set)	Select ltrim( ‘xyzadams’,’xyz’) from dual;	adams	从左边开始找，删除指定字符
Rtrim(char,set)	Select rtrim(‘xyzadams’,’ams’) from dual;	xyzad	从右边开始找，删除指定字符
Translate(char, from, to)	Select translate(‘jack’,’j’ ,’b’) from dual;	back	替换字符
Replace(char, searchstring,[rep string])	Select replace(‘jack and jue’ ,’j’,’bl’) from dual;	black and blue	替换所有指定字符
Instr (char, m, n)	Select instr (‘worldwide’,’d’) from dual;	5	找到指定字符的位置
Substr (char, m, n)	Select substr(‘abcdefg’,3,2) from dual;	cd	找到指定位置的字符
Concat (expr1, expr2)	Select concat (‘Hello’,’ world’) from dual;	Hello world	拼接字符

以下是一些其它的字符函数：

CHR和ASCII CHR:将ASCII码转换成char字符;ASCII:得到字符的ASCII码

LPAD和RPAD 将不够的字符补齐:

　　例:

select rpad('1234.5', 10, '0') from dual;--输出'1234.50000'

select job, lpad(job, 10, ' ') from emp;

TRIM 去掉两边的空格
LENGTH 计算长度

Select * from emp2 where length(ename) = 4; --名字为4个字符的数据

DECODE

数字函数

数字函数接受数字输入并返回数值结果

函数	输入	输出	说明
Abs(n)	Select abs(-15) from dual;	15	取绝对值
Ceil(n)	Select ceil(44.778) from dual;	45	上取整函数
Cos(n)	Select cos(180) from dual;	-.5984601	余弦函数
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 denrankfrom emp e, dept d WHERE e.deptno = d.deptno;