SQL基本函數學習筆記
?
??? 這是我很久以前剛開始學習SQL時作的筆記，今天翻資料的時候被我翻了出來，覺得放在那里以后遺失掉了也是可惜，不如放到博客上，以后也可以看看，還可以幫助一些剛開始學習SQL的朋友入個門。不過都是按我自己的掌握程度來記的，估計也不太適合別人，就隨便看看吧。
?
?
一、to_char函數
?
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 (與星期幾無關)
W????????????? Week of month (1-5) where 7 days 1 week (與星期幾無關)
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????????? 轉換為8進制
?
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原意為截取數據小數部分，例如：
?
trunc(23.48429387)??? 返回23
trunc(23.48429387,3)? 返回23.484
trunc(-1.443432)????? 返回-1
?
但trunc(date) 具有與to_char(date) 相似的功能，但有區別：
?
trunc(sysdate,'cc')?? 取當世紀的第一天???? to_char(sysdate,'cc')?? 取當世紀數值
trunc(sysdate,'yyyy') 取當年的第一天?????? to_char(sysdate,'yyyy') 取當年數值
trunc(sysdate,'iyyy') 取上年的最后一天???? to_char(sysdate,'iyyy') 取當年數值
trunc(sysdate,'q')??? 取當季第一天???????? to_char(sysdate,'iyyy') 取當季數值
trunc(sysdate,'mm')?? 取當月第一天???????? to_char(sysdate,'mm')?? 取當月數值
trunc(sysdate,'ww')?? 取當周第一天(周二)?? to_char(sysdate,'ww')?? 取當周數值(第幾周)
trunc(sysdate,'iw')?? 取當周第一天(周一)?? to_char(sysdate,'iw')?? 取當周數值(第幾周)
?
總結：trunc對日期的截取由后面參數決定位置后將之后所有數值為默認初始值！
?
?
?
二、order by函數
?
--
自動將結果列表按字段順序對應排序
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
默認將空值排在最下面，如要排在最上面，則：
order
by
nulls
first
?
?
?
三、取整類函數整理
?
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
?
應用舉例：(根據時間算年齡)
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-------------
?
作用：作為調整格式的輸出，例：
?
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的實際應用：
?
select
distinct
lpad(selltype,
2
,
'0'
)
from
lccont;
?
由于系統中其他的selltype字段均為01、02等2位，但出現7，另有null值
所以使用 lpad(selltype,2,'0') 可以即保存null值又將7更新為07
?
?
?
五、rank() order by()和row_number() order by()的區別：
?
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的區別：
?
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的實際應用：
select translate('12XXX5869XXXX','0123456789'||'12XXX5869XXXX','0123456789')from dual;
<取字符串中的所有數字>
?
?
?
七、sysdate與current_date的差別：
?
select?sysdate,current_date?from dual;
某些情況下current_date會比sysdate快一秒。
我們認為current_date是將current_timestamp中毫秒四舍五入后的返回
雖然沒有找到文檔支持，但是想來應該八九不離十。
?
?
?
八、一些有用的時間函數：
?
select NEXT_DAY(sysdate,5) from dual;--下一個星期四(不算今天)
select NEXT_DAY(sysdate,'星期三') from dual;--下一個星期一(大小寫都可)
select LAST_DAY(sysdate) from dual;--當月最后一天
?
?
?
九、一些有用的數字/字符函數：
?
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次方
?
?
?
十、一些有用的字符串操作函數：
?
select CHR(95) from dual;-------------ASCII碼對應字符
select ASCII('_') from dual;----------字符對應ASCII碼
select concat('aa','bb') from dual;------------等同于||
select INITCAP('whaT is this') from dual;------首字母大寫，其余小寫
select TO_MULTI_BYTE('ABC abc 中華') from dual;----------半角變全角
select TO_SINGLE_BYTE('ＡＢＣ ａｂｃ中華') from dual;------全角變半角
select VSIZE('abc中華') from dual;-----返回字節數
select INSTR('CORPORATE FLOOR','OR',3,2) from dual;----從第3位開始查找第2個'OR'
?
?
?
十一、WMSYS.WM_CONCAT函數應用：
?
此函數作用在于將某字段所有值列出到一個單元格中
?
select
replace
(WMSYS.WM_CONCAT(num),
','
,
' '
)
from
t1;
?
行列轉換中最簡單的一種方法。
?
?
?
十二、單元格內文本換行的方法：
?
Tab鍵 chr(9)
換行符chr(10)
回車符chr(13)
空格符chr(32)
select
'a'
||chr(
9
)||
'b'
from
dual;
select
'a'
||chr(
13
)||
'b'
from
dual;
注：須在SQLPlus中查看結果，PL/SQL Developer中無法顯示換行
?
?
?