oracle sql常用的函数,[数据库]Oracle学习笔记六 SQL常用函数

[数据库]Oracle学习笔记六 SQL常用函数

0 2015-11-28 20:01:22

函数的分类

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');

分析函数分析函数根据一组行来计算聚合值用于计算完成聚集的累计排名、移动平均数等

分析函数为每组记录返回多个行