Oracle的常用函式
1。 單行函式
字元函式
initcap('akd')
把akd的首字母轉化為大寫
length('xxx')
求xxx的長度
substr(‘ksfkasd’,2,4)
從字串 ksfkasd 的第二個字元開始擷取長度為4的字串
instr
函式返回要擷取的字串在源字串中的位置。
語法如下:instr( string1, string2 [, start_position [, nth_appearance ] ] )
string1
源字串,要在此字串中查詢。
string2
要在
string1
中查詢的字串。
start_position
代表
string1
的哪個位置開始查詢。此引數可選,如果省略預設為1。字串索引從1開始。如果此引數為正,從左到右開始檢索,如果此引數為負,從右到左檢索,返回要查詢的字串在源字串中的開始索引。
nth_appearance
代表要查詢第幾次出現的
string2
。 此引數可選,如果省略,預設為 1。如果為負數系統會報錯。
注意: 如果String2在String1中沒有找到,instr函式返回0。
trim(‘a’ from ‘adkf’)
把第一次出現在adfk字串中的a去除 ——>只帶一個引數時,代表去除字串左右兩邊的空格
ltrim(‘adfsad’, ‘ad’)
左裁剪,結果為fsad ——>只帶一個引數時,代表去除左邊的空格
rtrim(‘klsadk’, ‘dk’)
右裁剪,結果為klsa ——>只帶一個引數時,代表去除右邊的空格
replace(‘引數一’ , ‘引數二’ ,‘引數三’)
引數一中出現的引數二替換為引數三,當引數三省略時,引數二會被null替換
數字函式
ABS(N)
取絕對值
ceil(n)
向上取整
floor(n)
向下取整
sin(n)
正弦
cos(n)
餘弦
sign(n)
取符號
power(m,n)
m的n次冪
mod(m,n)
m對n取模
round(m,n)
對m四捨五入,並保留n位小數
trunc(m,n)
對m這個小數進行截斷,小數點後面留下n位。不寫n表示小數全部截掉
sqrt(n)
開根
dbms_random。value(m,n)
取m——n之間的隨機數,是一個小數,小數位數為15 -(n的位數)
日期函式
sysdate
獲取系統日期,預設格式為(DD-MON-RR)天、月、日to_char(sysdate,‘YYYY-MM-DD HH24:MI:SS’) ——->結果為:2019-04-22 20:30:44
add_months(date, i)
返回把(i)月份加到(date)日期上的新日期。i可以是任何數字,如果i寫成小數,自動擷取整數部分。如果i是負數,則相當於原日期減去i個月。
next_day(date, char)
第一個引數(date)為日期,第二個引數(char)為星期幾(中文環境下輸中文,英文環境下輸英文)。作用是返回date日期後,下一次char對應的日期。例如:select next_day(sysdate, ‘星期二’) from dual;
last_day(date)
返回date日期所在月的最後一天,一般用於判斷當月有幾天
month_between(date1,date2)
表示date1與date2兩個日期間相隔的月份。date1位大的日期,date2位小的日期。
extract(date from datetime)
返回datetime日期對應的date形式。例如: select extract(day from sysdate) ——->返回當前日期的的號數。year ——->年month ————>月day ————>日
trunc(date, format)
對日期按format進行截斷trunc(‘25-12月-18’, ‘yyyy’) 按年進行截斷,結果為2018年1月1日(1-1月-18)
轉換函式
日期/數字 轉換為字元的函式:
to_char(date , fmt , params)
date:將要轉換的日期;
fmt:轉換的格式;
params:指定日期的語言(可不寫,自動根據作業系統進行改變)
fmt的引數有:
YY YYYY ——->年
MM MONTH ——->月DD(日期)
DAY(星期) ——->日
HH24 HH12 ——->時
MI ——->分
SS ——->秒
to_char(1324 , ‘99999’) 把1324轉換為字串,引數一最大可以為99999(5位)。如果引數2的位數小於引數一的位數,轉換不成功。如果引數二的位數大於引數一的位數,轉換後的結果前面不空格。
字元轉換成日期
to_date(char , fmt , params)
引數含義同to_char引數一和引數二的格式需要相同,否則會出現文字與格式字串不匹配的錯誤
字元轉換成數字
to_number(char , fmt)
引數含義同上
日期和日期做減法運算時,不用管格式
to_date(‘2017/4/5’,‘yyyy/mm/dd’) - to_date(‘2018-5-5’,‘yyyy-mm-dd’)
利用to_char和to_date可以求出某年某月某日是星期
select to_char(to_date(sysdate,‘YYYY-MM-DD’),‘day’) from dual
其他函式
nvl(x,0)
如果x的值為空,則返回0
nvl(x, y, z)
如果x的值為null,則返回y的值,否則返回z的值
decode(value, if1, then1, if2, then2, 。。。, else)
—— 如果value的值為if1,則返回then1的值,為if2, 返回then2的值,如果都不是,返回else的值。
2。 聚合函式(分組函式)
聚合函式基於一組行來返回結果,為每一行返回一個值;
sum()
求和
avg()
求平均值
count() 統計行數
一般使用count(1)來統計,也可以使用count(*),但不建議使用;
還可以使用count(列名)來統計,但是會出現一個問題,如果某條記錄中用來統計但這個列名的值為空,則不會統計它,於是可能出現統計出的值比實際值小的情況。
例:
max()
求最大值
min()
求最小值
where不支援使用分組函式,如果後面要跟分組條件過濾,使用having,having後面可以跟分組函式。
3。 分析函式
分析函式一般用來做排名處理。分析函式根據一組行來計算聚合值,用於計算完成聚集的累計排名等;分析函式為每組記錄返回多個行。
rank( ) over( )
:存在並列的情況 ,會發生跳躍
從rk的排名可以看出,排名會出現並列情況,並且下一名出現了跳躍。
dense_rank( ) over( )
:存在並列的情況 ,不會發生跳躍
row_number( ) over( )
:不存在並列的情況 ,不會發生跳躍
從drk可以看出,dese_rank()會出現並列情況,但是不會發生跳躍;
從rn可以看到row_number()既不會出現並列,也不會出現跳躍。
where後面也不能跟分析函式。比如我們需要把上面結果加上薪資等於第二名的人,這時候我們
不能
在後面加
where rk = 2
,或者
where rank() over(order by sal desc) = 2
。這時可以在查出的結果外面再包一個select來進行查詢,這是在where 後面加 rk = 2就可以了,就是把排名後的結果當作一張表,對這張表再次進行查詢。