SQL基本函數(shù)學(xué)習(xí)筆記
?
??? 這是我很久以前剛開始學(xué)習(xí)SQL時作的筆記，今天翻資料的時候被我翻了出來，覺得放在那里以后遺失掉了也是可惜，不如放到博客上，以后也可以看看，還可以幫助一些剛開始學(xué)習(xí)SQL的朋友入個門。不過都是按我自己的掌握程度來記的，估計也不太適合別人，就隨便看看吧。
?
?
一、to_char函數(shù)
?
Parameter??? Explanation
YEAR?????????? Year spelled out
YYYY?????????? 4 digits of year
YYY??????????? 3 digits of year
YY???????????? 2 digits of year
Y????????????? 1 digit of year
IYYY?????????? 4digits year based on the ISO standard
IYY??????????? 3 digits of ISO year
IY???????????? 2 digits of ISO year
I????????????? 1 digit of ISO year
Q????????????? Quarter of year (1 .. 4)
MM???????????? Month (01 ..12)
MON??????????? Abbreviated name of month
MONTH????????? Name of month,
padded with blanks to length of 9 characters.
RM???????????? Roman numeral month (I .. XII)
WW???????????? Week of year (1-53) where 7 days 1 week (與星期幾無關(guān))
W????????????? Week of month (1-5) where 7 days 1 week (與星期幾無關(guān))
IW???????????? Week of year (1-52 or 1-53) based on the ISO standard.
?????????????? (周一到周日為一周，若1日為周五-周日，則為上年最后一周)
D????????????? Day of week (周日1 .. 周六7)
DY???????????? Abbreviated name of day.
DAY??????????? Name of day
DD???????????? Day of month (1-31)
DDTH?????????? Day of month (1-31)
DDD??????????? Day of year (1-366)
J????????????? Julian day；the number of days since January 1, 4712 BC.
HH???????????? Hour of day (1-12).
HH12?????????? Hour of day (1-12).
HH24?????????? Hour of day (0-23).
MI???????????? Minute (0-59).
SS???????????? Second (0-59).
SSSSS????????? Seconds past midnight (0-86399).
FF???????????? Fractional seconds.
XXXXX????????? 轉(zhuǎn)換為8進(jìn)制
?
to_char(1210.73, '9999.9')??? ?? would return '1210.7'
to_char(1210.73, '9,999.99')? ?? would return '1,210.73'
to_char(1210.73, '$9,999.00')? ? would return '$1,210.73'
to_char(21, '000099')????????? ? would return '000021'
to_char(21, '999999')????????? ? would return '??? 21'
to_char(21, 'FM999999')????????? would return '21'
to_char(sysdate, 'FMYYY')??????? would return '8'
?? ???? --FM表示去掉0或空格??
?
to_char(125, 'XXXXX')??????????? would return '7D'
to_number('7D','XXXXX')????????? would return '125'?
?
?
另注：trunc與to_char的比較
?
trunc原意為截取數(shù)據(jù)小數(shù)部分，例如：
?
trunc(23.48429387)??? 返回23
trunc(23.48429387,3)? 返回23.484
trunc(-1.443432)????? 返回-1
?
但trunc(date) 具有與to_char(date) 相似的功能，但有區(qū)別：
?
trunc(sysdate,'cc')?? 取當(dāng)世紀(jì)的第一天???? to_char(sysdate,'cc')?? 取當(dāng)世紀(jì)數(shù)值
trunc(sysdate,'yyyy') 取當(dāng)年的第一天?????? to_char(sysdate,'yyyy') 取當(dāng)年數(shù)值
trunc(sysdate,'iyyy') 取上年的最后一天???? to_char(sysdate,'iyyy') 取當(dāng)年數(shù)值
trunc(sysdate,'q')??? 取當(dāng)季第一天???????? to_char(sysdate,'iyyy') 取當(dāng)季數(shù)值
trunc(sysdate,'mm')?? 取當(dāng)月第一天???????? to_char(sysdate,'mm')?? 取當(dāng)月數(shù)值
trunc(sysdate,'ww')?? 取當(dāng)周第一天(周二)?? to_char(sysdate,'ww')?? 取當(dāng)周數(shù)值(第幾周)
trunc(sysdate,'iw')?? 取當(dāng)周第一天(周一)?? to_char(sysdate,'iw')?? 取當(dāng)周數(shù)值(第幾周)
?
總結(jié)：trunc對日期的截取由后面參數(shù)決定位置后將之后所有數(shù)值為默認(rèn)初始值！
?
?
?
二、order by函數(shù)
?
--
自動將結(jié)果列表按字段順序?qū)?yīng)排序
order
by
1
,
2
,
3
?
--
可對字段
decode
后再排序，下例為將
2222
、
1111
排在前兩位，其他按順序排列
select
a,b,
c
from
t1
order
by
decode(a,
'2222'
,
1
,
'1111'
,
2
,a)
?
--
如遇到空值時，
order by
默認(rèn)將空值排在最下面，如要排在最上面，則：
order
by
nulls
first
?
?
?
三、取整類函數(shù)整理
?
ceil
：
取整
(
大
)
???
select
ceil (-
1.001
)
value
from
dual??? /-
1
floor
：取整（小）
???
select
floor(-
1.001
)
value
from
dual??? /-
2
trunc
：取整（截取）
???
select
trunc(-
1.001
)
value
from
dual??? /-
1
round
：取整
(
舍入
)
select
round(-
1.001
)
value
from
dual??? /-
1
?
應(yīng)用舉例：(根據(jù)時間算年齡)
trunc(months_between(
sysdate
,birthday)/
12
) Age
?
?
?
四、LPAD與RPAD的用法：
?
比較：select LPAD('WhaT is tHis',5),LPAD('WhaT is tHis',25),LPAD('WhaT is tHis',25,'-') from dual;
??????????? |WhaT|?????????????? WhaT is tHis| -------------WhaT is tHis
???? select RPAD('WhaT is tHis',5),RPAD('WhaT is tHis',25),RPAD('WhaT is tHis',25,'-') from dual;
??????????? |WhaT|? WhaT is tHis???????????? | WhaT is tHis-------------
?
作用：作為調(diào)整格式的輸出，例：
?
with
x
as
(
select
'aa'
chr
from
dual
union
all
select
'bb'
chr
from
dual)
select
level
,chr,lpad(
' '
,(
level
-
1
)*
5
,
'-'
)||chr other
from
x
connect
by
level
<=
3
?
說明：若LPAD對空字符串操作無效，因此至少必須有' '空格符!
?
?
LPAD的實際應(yīng)用：
?
select
distinct
lpad(selltype,
2
,
'0'
)
from
lccont;
?
由于系統(tǒng)中其他的selltype字段均為01、02等2位，但出現(xiàn)7，另有null值
所以使用 lpad(selltype,2,'0') 可以即保存null值又將7更新為07
?
?
?
五、rank() order by()和row_number() order by()的區(qū)別：
?
with
t
as
(
select
1
a
from
dual
union
all
select
2
a
from
dual
union
all
select
1
a
from
dual
)
select
a,rank() over(
order
by
a) rank,row_number() over(
order
by
a) num
from
t;
?
?
?
六、translate和replace的區(qū)別：
?
select translate('What is this','ait','-*%') from dual;---Wh-% *s %h*s
selectreplace('What is this','ait','-*%') from dual;-----What is this
selectreplace('What is this','hat','-*%') from dual;-----W-*% is this
?
translate的實際應(yīng)用：
select translate('12XXX5869XXXX','0123456789'||'12XXX5869XXXX','0123456789')from dual;
<取字符串中的所有數(shù)字>
?
?
?
七、sysdate與current_date的差別：
?
select?sysdate,current_date?from dual;
某些情況下current_date會比sysdate快一秒。
我們認(rèn)為current_date是將current_timestamp中毫秒四舍五入后的返回
雖然沒有找到文檔支持，但是想來應(yīng)該八九不離十。
?
?
?
八、一些有用的時間函數(shù)：
?
select NEXT_DAY(sysdate,5) from dual;--下一個星期四(不算今天)
select NEXT_DAY(sysdate,'星期三') from dual;--下一個星期一(大小寫都可)
select LAST_DAY(sysdate) from dual;--當(dāng)月最后一天
?
?
?
九、一些有用的數(shù)字/字符函數(shù)：
?
select GREATEST(a,b) Greatest from t2;----------求最大值
select LEAST(a,b) LEAST from t2;-------------求最小值
select NULLIF('a','b'),NULLIF('a','a') from dual;-------a=b則返回null；a<>b則返回a
select nvl(null,'a'),nvl('1','a') from dual;------------為null時返回a，不會null返回原值
select nvl2(null,'a','b'),nvl2('1','a','b') from dual;--為null時返回b，不為null返回a
selectCOALESCE(null,5,6,null,9) from dual;-----返回第一個非空值
select POWER(2.2,2.2) from dual;? ----a的b次方
?
?
?
十、一些有用的字符串操作函數(shù)：
?
select CHR(95) from dual;-------------ASCII碼對應(yīng)字符
select ASCII('_') from dual;----------字符對應(yīng)ASCII碼
select concat('aa','bb') from dual;------------等同于||
select INITCAP('whaT is this') from dual;------首字母大寫，其余小寫
select TO_MULTI_BYTE('ABC abc 中華') from dual;----------半角變?nèi)?/span>
select TO_SINGLE_BYTE('ＡＢＣ ａｂｃ中華') from dual;------全角變半角
select VSIZE('abc中華') from dual;-----返回字節(jié)數(shù)
select INSTR('CORPORATE FLOOR','OR',3,2) from dual;----從第3位開始查找第2個'OR'
?
?
?
十一、WMSYS.WM_CONCAT函數(shù)應(yīng)用：
?
此函數(shù)作用在于將某字段所有值列出到一個單元格中
?
select
replace
(WMSYS.WM_CONCAT(num),
','
,
' '
)
from
t1;
?
行列轉(zhuǎn)換中最簡單的一種方法。
?
?
?
十二、單元格內(nèi)文本換行的方法：
?
Tab鍵 chr(9)
換行符chr(10)
回車符chr(13)
空格符chr(32)
select
'a'
||chr(
9
)||
'b'
from
dual;
select
'a'
||chr(
13
)||
'b'
from
dual;
注：須在SQLPlus中查看結(jié)果，PL/SQL Developer中無法顯示換行
?
?
?