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中无法显示换行