oracle之函数

 

Oracle常用函数分类：

1.字符函数

2.数字函数

3.时间函数

4.转换函数

5.聚合函数

 

常用字符函数：

 

Lower:将字母转换为小写

例子：select lower(ename) from emp;

Upper:将字母转换为大写

例子：select upper(ename) from emp

Length：返回字符长度

例子：select*  from emp  where   length(ename)=3;

Substr(str,startIndex,length):截取字符串，str被截取的字符串，startIndex,截取的起始位置，length,截取长度

例子：select  substr(ename,1,1) from emp

Trim：去空格，去除两边的空格，字符中间空格不去除

例子：select  trim(' hello oracle  ')  from dual

Ltrim:只去除左边空格

例子：select  ltrim(' hello oracle ')   from dual

Replace(x,y):替换字符，用y替换x

例子：select   replace('Hello Oracle','Oracle','java')  from dual

 

---将员工的名字格式改为：Mrt;首字母大写，其他字母小写

select   upper(substr(ename,1,1))||lower(substr(ename,2))from emp

 

 

数字函数：

Abs(x):返回x绝对值

Acos(x):返回反余弦

Cos(x):余弦

Ceil(x):返回大于或等于x的最小值

Ceil(5.4):6

Floor(x):小于或等于x的最大值

Floor(5.4):5

Log(x,y)：x为底，y的对数

例子：select log(10,1) from dual返回结果为：0

Mod(x,y):返回x除以y的余数，即x对y的余数，即x对y取模

例子；Select  mod(5,2) from dual

Power(x,y):返回x的y次方结果。

例子：select  power(2,3) from dual

Round(x,y)：在小数点后面y+1位进行四舍五入，即保留y位小数，如果没有y,默认为0

例子：

select  round(3.123,1)   from dual

Sqrt(x):返回x的平方根

例子：select  sqrt(4)  from dual

Trunct(x,[y]):直接截取，不四舍五入

 

 

日期函数:

常用日期函数：

1.     add_months(d,n)在某一个日期d上，加上指定月数n

例子： select add_months(sysdate,1)from dualà返回结果为：2013年12月6日

2.     last_day(d):返回指定日期d的月份的最后一天

例子：select last_day(sysdate)from dualà返回结果：2013年11月30日星期六

3.     round(d[,fmt]):返回一个以fmt为格式的四舍五入日期值。

Fmt的值；“DDD”默认值，即某月中一天。以正午12点作为中间线

                      Year:则四舍五入到某年的1月1日。以6月作为中间线

                      Month:则四舍五入到某月的1日以16日作为中间线

                      Day:则四舍五入到最近的星期日以周四作为中间线

4.     extract(fmt from d):提取日期中的特定部分。

Fmt的值；year表示提取年份；

                      Month表示提取月份；

                      Day表示提取天数

                      Hour:表示提取小时

                      Minute:表示提取分

                      Second：表示提取秒

 

例子：

 

Select extract(yearfromsysdate) from dual—提取年

Select extract(monthfromsysdate) from dual—提取月

Select  extract(dayfromsysdate) from dual—提取天数

 

转换函数：将值从一种数据类型转换成另外一种数据类型

1. to_char(d|n[,fmt]):将日期和数字转换为字符创，其中fmt为日期转换为字符串的格式

例子：时间转换

select to_char(sysdate,'yyyy"年"mm"月"dd"日" hh:mm:ss')from dual

例子：数字转换

select to_char(5432334,'L999,999,999.00')from dual

2. to_date(x[,fmt]):将字符串按照fmt的格式转换成日期

例子：

select to_date(201311061039,'yyyy--mm--dd hh:mi:ss')from dual

3. to_number(x[,fmt])：将字符串按照fmt的格式转换成数字

例子：

select to_number('$123456789''&999,999,999,999.99')from dual

5.     nvl(x,value):如果x值为空，则返回value,不为空则返回x

例子：

select ename,nvl(comm,0)from emp

6.     nvl2(x,value1,value2):如果x值不为空则返回value1,为空则返回value2

例子：

      select ename,nvl2(comm,comm,0)from emp

 

 

聚合函数：

 

1.     sum():求和

例子：select  sum(comm)from emp

2.     avg():求平均值：

例子：select  avg(comm)from emp

3.     min()：求最小值

例子：

4.     max():求最大值

例子：

5.     count():统计行

select  count(empno)from emp

 

 

分组关键字：

Group by分组关键

在group by查询语句中，select后面只能接group by分组字段和聚合函数表达式

例子：select deptno,avg(sal)from emp groupby deptno

 

Having:用于grop by后面添加条件：

只统计部门编号为20和30的部门

例子：

select deptno,avg(sal)from emp groupby deptnohaving deptno=20or deptno=30