oracle 常用函数

新人一枚，由于对数据库比较感兴趣，所以想把学习心得记录下来，如有错误欢迎指正！！！

以下列出oracle部分函数，我将其分为以下几类：

一，字符串相关函数

二，数字相关函数

三，日期相关函数

四，分组相关函数

五，其他函数

------------------------------------------------------------------------

注意：oracle下标从1开始，select 函数 from dual;

一，字符串相关函数

1.substr  字符串截取

substr(string,start,count)

取子字符串,从start开始,取count个

select substr('ABCDEFG',1,3) from dual;

结果：'ABC'

为了简化，故以下我全以这样的格式写：

substr('ABCDEFG',1,3)--'ABC'

2.concat 连接两个字符串

concat('abc','ABC')--abcABC

3. initcap   返回字符串并将字符串的第一个字母变为大写;

initcap ('abc')--Abc

4. instr (str1,str2,m,n)

在一个字符串中搜索指定的字符,返回指定字符的位置;

str1    被搜索的字符串

str2    希望搜索的字符串

m     搜索的开始位置,默认值为1

n     从m开始的第几个Str2,默认值为1

instr ('abcdbce','bc',1,1) --2

instr('abcdbce','bc',3,1) --5

instr('abcdbce','bc',3,2) --0

instr('abcdbce','bc',1,2)--5

5.length 返回字符串的长度

length('aaa') --3

6.lower upper

lower:返回字符串,并将所有的字符小写

upper:返回字符串,并将所有的字符大写

lower('ABC')--abc

upper('abc')--ABC

7. prad  lpad 粘贴字符

RPAD   在列的右边粘贴字符    

LPAD  在列的左边粘贴字符

数字是总的长度（输出字符串的全长）    长度不够就截取

rpad ('aaa',4,'*')--aaa*

lpad('bbb',5,'*')--**bbb

lpad('abc',2,'*')--ab

8.REPLACE(string,str1,str2)

string    原字符串

str1       string中被替换的字符串

str2       要替换的字符串

replace('stringin','in','oo')--stroogoo

9.trim     ltrim        rtrim

trim:删除字符串两边的空格

ltrim:删除左边出现的字符串

rtrim:删除右边出现的字符串

trim(' a bc ')--a_bc

ltrim(' a bc ')--a_bc_        _代表空格

rtrim(' a bc ')--_a_bc

高级：

9.1 trim()只能删除半角空格。 

trim('　a　bc　')-- __a__bc__    全角的视觉上看比半角的要宽　

9.2 trim(str1 from str2)  分别从字符串str2的两边开始，删除指定的str1。

trim('s' from 'sstring')--tring

trim('g' from 'stringg')--strin

trim('s' from 'sstrinsgs')--trinsg     从首尾删除,如遇到非指定的字符便停止删除

9.3  trim ([ leading  |  trailing  |  both ] str  from  string)　从字符串string中删除指定的字符str。

  leading:从字符串的头开始删除。

  trailing:从字符串的尾部开始删除。

   both :从字符串的两边删除。

不跳跃字符，从首尾删除,如遇到非指定的字符便停止删除。

trim ( leading 's' from 'sstrinsgs') --trinsgs

trim(leading's' from 'sstrinsgs') --sstrinsg

trim(both 's' from 'sstrinsgs') --trinsg

----------------------------------------------------------

二，数字相关函数

1.abs  返回指定值的绝对值

abs(-1)--1

2.to_number        将给出的字符转换为数字

to_number('1999') --1999

to_number('aaa') --ORA-01722:无效数字

3.还有很多但是一般不常用

----------------------------------------------------------

三， 日期相关函数

1. sysdate    得到系统的当前日期

2.to_char(date,'format')   --date 的类型和sysdate的类型一致

to_char(sysdate,'yyyy/mm/dd hh24:mi:ss')--2015/05/13 17:44:41

3. to_date (string,'format')   将字符串转化为ORACLE中的日期

to_date ('20140101','yyyy/mm/dd')--2014/1/1

4. add_months         增加或减去月份

add_months ( to_date ('199912','yyyymm'),0)--1999/12/1

add_months ( to_date ('199912','yyyymm'),2)--2000/2/1

add_months ( to_date ('199912','yyyymm'),-2)--1999/10/1

5. last_day            返回日期的最后一天

last_day ( sysdate )--2015/5/31 10:39:30

6. months_between (date2,date1)        date2-date1，有先后顺序

months_between ( sysdate , add_months ( sysdate ,2))--    -2

----------------------------------------------------------

四，分组相关函数

建立一张简单的表，并添加如下数据

create table test(
id number(5),
name varchar2(10),
price number(5)
)

select * from test;

1.avg(distinct|all)   all表示对所有的值求平均值,distinct只对不同的值求平均值

select avg(price) from test; --2.75  (2+2+4+3)/4=2.75

select avg(distinctprice) from test; --3  (2+4+3)/3=3

2.max(distinct|all)   求最大值,ALL表示对所有的值求最大值,DISTINCT表示对不同的值求最大值,相同的只取一次

3.min(distinct|all)   求最小值,ALL表示对所有的值求最小值,DISTINCT表示对不同的值求最小值,相同的只取一次

select min(price) from test; --2

select min(distinct price) from test;--2

4.sum(distinct|all) 求和,ALL表示对所有的值求和,DISTINCT表示对不同的值求和,相同的只取一次

select sum(price) from test;--11   2+2+4+3=11

select sum(distinct price) from test;--9  2+4+3=9

5.group by   主要用来对一组数进行统计,要用组函数avg,max,min,sum等配合

select sum(price) from test group by price; --根据价格分组，分了三组2,2  3  4并对每组分别进行求和 

其中有个4是2+2得到的

5.having    对分组统计再加限制条件

select sum(price) from test group by price having price = 3; --3

select sum(price) from test group by price having price = 2; --4  2+2=4

select sum(price) from test group by price having price = 0; --无结果  分组中没有价格等于0的组    

6.order by  用于对查询到的结果进行排序输出

select * from test order by price; --默认为升序

select * from test order by price asc; --升序

select * from test order by price desc; --降序

select * from test order by pricedesc,id  desc ;  --降序  对多个条件从前向后进行排序，不指定排序默认为升序，

7.count  统计数据条数

select count(*) from test; --4

select count(1) from test;  --4

select count( distinct price) from test; --3

----------------------------------------------------------

五，其他函数

1. nvl(x,n)   判断是否为空

如果x为空，结果为n.如果x非空，结果为x

nvl(null,1)    --1 

nvl(1,2)    --1

nvl2(x,m,n)   判断是否为空

nvl2(1,'first','second')--first

nvl2(null,'first','second')--second

2.sign  比较大小

sign()函数根据某个值是0、正数还是负数，分别返回0、1、-1

sign(10-20)--  -1

sign(20-10)--   1

sign(10-10)--   0

3. decode   条件判断

decode(value,if1,then1,if2,then2,if3,then3,...,else)，表示如果value 等于if1时，DECODE函数的结果返回then1,...,如果不等于任何一个if值，则返回else。

decode (1,1,'a',2,'b','c')--    a

decode (2,1,'a',2,'b','c')--    b

decode (33,1,'a',2,'b','c')--    c

组合：select  decode (sign(变量1-变量2),-1,变量1,变量2) from dual; --取较小值

 select decode(sign(变量1-变量2),+1,变量1,变量2) from dual; --取较大值

selectdecode(sign(10-20),-1,10,20) from dual;  --10
select decode(sign(10-20),+1,10,20) from dual;  --20

4.union     union  all

union：取交集

union alll:取并集

select 'aa' from dual union all

select 'bb' from dual union all

select 'aa' from dual 

select 'aa' from dual union 

select 'bb' from dual union 

select 'aa' from dual 

5.user    返回当前用户的名字

 select user from  dual;

6. userenv(x)      返回当前用户环境的信息,x(x是字符串)可以是:

ENTRYID(返回会话人口标志),

SESSIONID(返回会话标志),TERMINAL,ISDBA,LABLE,

LANGUAGE(返回当前环境变量),

CLIENT_INFO,LANG,VSIZE

INSTANCE(返回当前INSTANCE的标志)

select userenv('isdba') from dual;--true