Oracle提供很多函数可以用来辅助数据查询。若有不当之处,欢迎批评指正!
1、ceil()函数
用法:ceil(n),取大于等于数值n的最小整数。
select mgr,mgr/100,ceil(mgr/100) from scott.emp;
2、mod()函数
用法:mod(m,n),取m整除n后的余数。
select mgr,mod(mgr,1000) ,mod(mgr,100) ,mod(mgr,10) from scott.emp;
3、power()函数
用法:power(m,n),取m的n次方。
select mgr,power(mgr,2),power(mgr,3) from scott.emp;
4、round()函数
用法:round(m,n),四舍五入,保留n位。
select mgr,round(mgr/100,2),round(mgr/1000,2) from scott.emp;
5、sign()函数
用法:sign(n),n>0,取1,;n=0,取0;n<0,取-1。
select mgr,mgr-7800,sign(mgr-7800) from scott.emp;
6、avg()函数
用法:avg(字段名),求平均值,要求字段为数值型。
select avg(mgr) 平均值 from scott.emp;
7、count()函数
用法:count(字段名)或count(*),统计总数。
select count(*) 记录总数 from scott.emp;
8、min()函数
用法:min(字段名),计算数值型字段的最小数。
select min(sal) 最低薪水 from scott.emp;
9、max()函数
用法:max(字段名),计算数值型字段的最大数。
select max(sal) 最高薪水 from scott.emp;
10、sum()函数
用法:sum(字段名),计算数值型字段总和。
select sum(sal) 薪水总和 from scott.emp;
11、lower()函数
用法:lower(c),将指定字符串内字符变为小写,支持CHAR,VARCHAR2,NCHAR,NVARCHAR2,CLOB,NCLOB类型。
select lower('WhaT iS tHIs') from dual;
12、upper()函数
用法:upper(c),将指定字符串内字符变为大写,支持CHAR,VARCHAR2,NCHAR,NVARCHAR2,CLOB,NCLOB类型。
select upper('WhaT iS tHIs') from dual;
13、trim('s' from 'string')
leading 剪掉前面的字符
trailing 剪掉后面的字符
如果不指定,默认为空格符
14、ltrim和rtrim
ltrim 删除左边出现的字符串
rtrim 删除右边出现的字符串
select ltrim(rtrim(' hello ',' '),' ') from dual; -----hello
15、substr(string,strat,count)
取子字符串,从start开始,取count个
select substr('130888888888999',3,8)from dual; --08888888
16、replace('string','s1','s2')
string 希望被替换的字符或变量
s1 被替换的字符串
s2 要替换的字符串
select replace('he love you','he','i') from dual; ---i love you
转换函数(Conversion Functions):
1、TO_CHAR()本函数又可以分三小类,分别是
转换字符---->字符TO_CHAR(c):将char,nvarchar2,clob,nclob类型转换为char类型;
例如:select TO_CHAR('AABBCC') from dual;
转换时间---->字符TO_CHAR(d[.fmt]):将指定的时间(data,timestamp,timestamp with zone)按照指定格式转换varchar2类型;
例如:select TO_CHAR(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;
转换数值---->字符TO_CHAR(n[.fmt]):将指定数值n按照指定格式fmt转换为varchar2类型并返回;
例如:select TO_CHAR(-100,'L99G999D99MI') from dual;
2、TO_DATE(c[.fmt[.nls]])将char,nchar,varchar2,nvarchar2转换为日期类型,如果fmt参数不为空,则按照fmt中指定格式进行转换。注意这里的fmt参数。如果fmt为‘J’则表示按照公元制(Julian day)转换,c则必须为大于0并小于5373484的正整数(因为Oracle的DATE类型的取值范围是公元前4721年1月1日至公元9999年12月31日:select TO_CHAR(TO_DATE('9999-12-31','yyyy-mm-dd hh24:mi:ss'),'j') from dual;)。
例如:select TO_DATE(2454336,'J') from dual;
select TO_DATE('2007-8-23 23:23:00','yyyy-mm-dd hh24:mi:ss') from dual;
3、TO_NUMBER(c[.fmt[.nls]])将char,nchar,varchar2,nvarchar2型字串按照fmt中指定的格式转换为数值类型并返回。
例如:select TO_NUMBER('-100.00','9G999D99') from dual;
用来转换空值的函数:
1、NVL(expr1,expr2)
如果该函数的第一个参数为空那么显示第二个参数的值,如果第一个参数的值不为空,则显示第一个参数本来的值
2、NVL2(expr1,expr2,expr3)
如果该函数的第一个参数为空那么显示第三个参数的值,如果第一个参数的值不为空,则显示第二个参数本来的值
3、NULLIF(expr1,expr2)
如果expr1和expr2相等则返回空(NULL),否则返回第一个值
分组函数:
基于一组行来返回结果
为每一组行返回一个值