Oracle 常用SQL函数
Oracle内置SQL函数主要分为一下类别:数字函数、字符函数、日期函数、转换函数、集合函数、分析/聚合函数
一、单行函数
1、数字函数
abs(n):返回数字n的绝对值
ceil(n):返回>=数字n的最小整数
floor(n):返回<=数字n的最大整数
round(n,[m]):四舍五入运算,如果m缺省则四舍五入到整数位
m<0,四舍五入到小数点的前m位,m>0四舍五入到小数点的后m位
trunc(n,[m]):截取数字,如果m缺省则将小数位截去
m<0,截取到小数点的前m位,m>0截取到小数点的后m位
select ceil(14.1),floor(14.1) from dual;
执行后为
select abs(-100)from dual;
执行后为
mod 取余函数
select mod(9,2) from dual;
执行后为:
当2换为0时,则为9
sign 返回数的符号 正数为1 负数为-1,0为0
select sign(-2),sign(0),sign(2) from dual;
执行后为
Power(n1,n2) 返回 n1的n2次幂 Exp(n) 返回e的n次幂
select power(3,3),Exp(2) from dual;
执行后为
2、字符函数
ascii(n):返回字符c的ascii值
chr(n):将ascii值转换为对应的字符
initcap(s):将字符串s所有的单词(依空格或非字母字符划分单词区间)的首字母大写,其余小写
lower(s):将字符串s中所有的字符转换为小写
upper(s):将字符串s中所有的字符转换为大写
concat(s1,s2):将字符串s2连接在s1后面,等同于操作符||
length(s):将返回字串s的长度,返回的长度包括其中的所有空格(尾部空格也算);如果s为null,则返回null
cast转换类型
select ascii('qwer') from dual;
执行后为
select length('qwer') from dual;
select cast('123'as Integer) from dual;
lpad(s1,n,s2):在字串s1的左端填充字串s2,直至填充后的s1的总长度为n
如果不指定s2则默认为空格
如果s1的长度>n,则直接返回s1左端的n个字符
rpad(s1,n,s2):在字串s1的右端填充字串s2,直至填充后的s1的总长度为n
如果不指定s2则默认为空格
如果s1的长度>n,则直接返回s1左端的n个字符
- SELECT lpad('a',10,'8'),lpad('LaaaaaaR',6,'8'),
- rpad('a',10,'8'),rpad('LaaaaaaR',6,'8')
- FROM dual
--执行结果:
lpad('a',10,'8') 888888888a
lpad('laaaaaar',6,'8') laaaaa
rpad('a',10,'8') a888888888
rpad('laaaaaar',6,'8') laaaaa
instr(s1,s2,n,m): 取得子串s2在字串s1中的位置
n表示在s1中开始搜索的位置,m表示字串s2出现的次数
如果n为负数,则表示从尾部开始搜索,n与m默认为1
substr(s1,m,n): 取得字串s1从m开始,长度为n的子串,m>0表示从头开始搜索,m<0表示从尾开始
replace(s1,s2,s3):将s1字串中的子串s2用s3替代,如果s2为null则返回原来的字串s1
注意:如果s3为null,则会去掉子串s2
3、日期函数
sysdate:返回系统当前日期时间
systimestamp:返回系统当前日期时间和时区
current_date:返回当前回话时区所对应的日期和时间
current_timestamp:返回当前回话时区所对应的日期时间
localtimestamp:返回当前回话时区所对应的日期时间
systimestamp:返回系统当前日期时间和时区
sessiontimezone:返回当前回话所在的时区
dbtimezone:返回资料库所在的时区
执行结果:
SYSDATE 2015/3/25 13:29:38
SYSTIMESTAMP 25-3月 -15 01.29.38.867634 下午 +08:00
CURRENT_DATE 2015/3/25 13:29:38
CURRENT_TIMESTAMP 25-3月 -15 01.29.38.867641 下午 +08:00
LOCALTIMESTAMP 25-3月 -15 01.29.38.867641 下午
SESSIONTIMEZONE +08:00
DBTIMEZONE +08:00
4.转换函数
to_date("要转换的字符串","转换的格式") 两个参数的格式必须匹配,否则会报错。
即按照第二个参数的格式解释第一个参数。
to_char(日期,"转换格式" )即把给定的日期按照“转换格式”转换。
select to_char(sysdate,'yy-mm-dd') from dual;
Select to_date('2005-12-25,13:25:59','yyyy-mm-dd,hh24:mi:ss') from dual
5.其他函数
Lvvnl 用于某个语句的where子句中的条件,如果条件为true就返回false;如果条件为UNKNOWN或者false就返回true。该函数不能用于复合条件如AND, OR, or BETWEEN中。
select * from empwhere lnnvl(sal<=3000)
nvl (expr1,expr2) 如果expr1为null 则返回expr2 否则返回expr1 替换时常用 Select productname,nvl(quantity,0) from productinfo 表示若quantity为NULL返回0否则返回quantity的值
select nvl(stuname,'xxxxxx') from student
Nvl2(expr1,expr2,expr3) 意思一样当expr1为空返回expr3,不为空返回expr2
select nvl2(stuname,'xxxxxx','yyyyyyyyy') from student
order by 默认asc(从小到大排序)desc(从大到小排序)
select * from student order by stuno desc;
group By
select Count(*) from empgroup by hiredate;
执行完后可以发现有2个一样的
Having 一般与 Group by 公用的限制语句他不与单个值有关而是与组有关
select Count(*) from empgroup by hiredate having count(*)>1;
执行完后查询按日期分组并且日期一样的
7、分析聚合函数
rank(expr1,expr2,...) within group(order by col1,col2,...):返回特定數值在統計數值中所佔據的等 級,expr1,ex pr2,...必須為常數
dense_rank(expr1,expr2,...) within group(order by expr1,expr2,...):返回特定數值在一組行數據中的等 級
percent_rank(expr1,expr2,...) within group(order by expr1,expr2,...):返回特定數值在統計數值在統計 級別中所占的比例
over()視窗函數
over ([partition by col1] order by col2) ) 表示依col1分組,依col2在分組類排序
over函數不能夠單獨使用,必須和其它的分組/分析函數配合使用
over函數不可用在where子句中
--结果:
姓名 | 部門 | 薪水 | 排序 |
BX | 2G60 | 77884 | 1 |
AX | 2G60 | 33086 | 2 |
SS | 2G88 | 70994 | 1 |
DX | 2G88 | 56273 | 2 |
row_number() 、rownum、rank()、dense_rank()區別說明
row_number() over(partition by col1 order by col2):此函数计算的值就表示每组内部排序后的顺序编号(组内连续的唯一的)
row_number()和rownum差不多,功能更强一点(可以在各个分组内从1开时排序)
rank():是跳跃排序,有两个第二名时接下来就是第四名(同样是在各个分组内)
dense_rank():是连续排序,有两个第二名时仍然跟着第三名.相比之下row_number是没有重复值的.