1>字符处理
upper(str)---将字符串str全部转换成大写
lower(str)---将字符串str全部转换成小写
initcap(str)---将字符串中每个单词的首字母大写
concat(str1,str2)---将字符串str1与str2连接起来(也可以通过'||'号直接相连)
substr(str,a,b)---取字符串str中的指定字符，从位置a开始取长度为b的字符串，假如a为正则从左边开始，否则从右边开始，缺省时，一直返回到str末端
length(str)---计算字符串str的长度
lpad(st1,n[,st2]) 返回右对齐的st,st为在st1的左边用st2填充直至长度为n,st2的缺省为空格
lpad(str,12,'*')---左填充，即将字符串str长度填充到12，假如其不足12位则在左边以*号填充
rpad(st1,n[,st2]) 返回左对齐的st,st为在st1的右边用st2填充直至长度为n,st2的缺省为空格
rpad(str,12,'*')---右填充，同上
ltrim(st[,set]) 返回st,st为从左边删除set中字符直到第一个不是set中的字符。缺省时，指的是空格
rtrim(st[,set]) 返回st,st为从右边删除set中字符直到第一个不是set中的字符。缺省时，指的是空格
replace(st,search_st[,replace_st]) 将每次在st中出现的search_st用replace_st替换，返回一个st。缺省时，删除search_st
instr(st1,st2[,m[,n]]) 数值，返回st1从第m字符开始，st2第n次出现的位置，m及n的缺省值为1
instr(str,'z')---取得str字符串中从左边开始每一次出现z字符的下标位置(下标从1开始)
例：
1.
select initcap('THOMAS'),initcap('thomas') from test;
initca initca
------ ------
Thomas Thomas
2.
select concat('abc','def') "first" from test;
first
-----
abcdef
3.
select 'abc'||' '||'def' "first" from test;
first
-----
abc def
4.
select lpad(name,10),rpad(name,5,'*') from test;
lpad(name,10) rpad(name,5,'*')
------------ ----------------
mmx mmx**
abcdef abcde
5.
去掉地址字段末端的点及单词st和rd
select rtrim(address,'. st rd') from test
6.
select name,replace(name,'a','*') from test;
name replace(name,'a','*')
---- ---------------------
great gre*t
7.
select substr('archibald bearisol',6,9) a,substr('archibald bearisol',11) b from test;
a b
------- -------
bald bear bearisol
8.
select name,instr(name,' ') a,instr(name,' ',1,2) b from test;
name a b
------- -------- ---------
li lei 3
2>数字函数
abs(m) m的绝对值
mod(m,n) m被n除后的余数
power(m,n) m的n次方
round(m[,n]) m四舍五入至小数点后n位的值（n缺省为0）
round(45.926,2)---将前一数保留指定的小数位，并四舍五入(45.93)，假如指定位是负数则意
为在小数点左边保留指定位,如
round(45.923,-1)=50,rount(45.923,0)=46,round(45.93,-2)=0,round(55.93,-2)=100
trunc(m[,n]) m截断n位小数位的值（n缺省为0）
trunc(45.926,2)---同上，得不四舍五入(45.92)
mod(1600,300)---求余(100)
3>日期型函数
add_months(d,n) 日期d加n个月
last_day(d) 包含d的月份的最后一天的日期
month_between(d,e) 日期d与e之间的月份数，e先于d
new_time(d,a,b) a时区的日期和时间d在b时区的日期和时间
next_day(d,day) 比日期d晚，由day指定的周几的日期
sysdate 当前的系统日期和时间
greatest(d1,d2,...dn) 给出的日期列表中最后的日期
least(d1,k2,...dn) 给出的日期列表中最早的日期
to_char(d [,fmt]) 日期d按fmt指定的格式转变成字符串
to_date(st [,fmt]) 字符串st按fmt指定的格式转成日期值，若fmt忽略，st要用缺省格式
round(d [,fmt]) 日期d按fmt指定格式舍入到最近的日期
trunc(d [,fmt]) 日期d按fmt指定格式截断到最近的日期
附：
日期格式：
－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－
格式代码 说明 举例或可取值的范围
－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－
DD 该月某一天 1－3
DY 三个大写字母表示的周几 SUN，...SAT
DAY 完整的周几，大写英文 SUNDAY，...SATURDAY
MM 月份 1－12
MON 三个大写字母表示的月份 JAN，...DEC
MONTH 完整 JANUARY,...DECEMBER
RM 月份的罗马数字 I,...XII
YY或YYYY 两位，四位数字年
HH:MI:SS 时：分：秒
HH12或HH24 以12小时或24小时显示
MI 分
SS 秒
AM或PM 上下午指示符
SP 后缀SP要求拼写出任何数值字段
TH 后缀TH表示添加的数字是序数 4th,1st
FM 前缀对月或日或年值，禁止填充
－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－
例：
1.
下一个周五的日期
select next_day(sysdate,6) from test;
2.
两个月前的今天的日期
select add_months(sysdate,-2) from test;
oracle中默认的格式是：DD-MON-RR。
oracle中有个到当前系统时间--sysdate，如：
select sysdate from dual
可对日期进行自述运算：
select (sysdate-mybirthday)/7 from person
months_between('01-sep-95','11-jan-94')---取得二个日期之间的间隔月数(19.6774194)
add_months('11-jan-94',6)---给指定日期加上指定的月份后得到一个新的日期(11-jul-94)
next_day('01-sep-85','friday')---取得当前日期中下个周五的日期(01-jul-95)
last_day('01-feb-95')---取得当前日期中月份的最后一天(28-feb-95)
round进行四舍五入，trunc则否,以下是我的操作结果：
sysdate为：
SYSDATE
----------
28-7月 -06
select
round(sysdate,'month') RM,
round(sysdate,'year') RY,
trunc(sysdate,'month') TM,
trunc(sysdate,'year') TY
from dual;
RM RY TM TY
---------- ---------- ---------- ----------
01-8月 -06 01-1月 -07 01-7月 -06 01-1月 -06
4>转换函数
to_char(m[,fmt]) m从一个数值转换为指定格式的字符串fmt缺省时，fmt值的宽度正好能容纳所有的有效数字
to_number(st[,fmt]) st从字符型数据转换成按指定格式的数值，缺省时数值格式串的大小正好为整个数
隐式转换:系统自动转换,如：
varchar2 or char to number
varchar2 or char to date
number to varchar2
date to varchar2
显式转换：人为以函数加以转换
日期，字符，数据三者之间可以相互转换:日期<-->字符<-->数据
日期格式:YYYY
日期-->字符
select to_char(sysdate,'yyyy-mm-dd') ch from dual
CH
----------
数字-->字符
to_char(number,'format_model'),oramat_model有如下：
9---用对应数字表示，每个9代表结果中的一位数字
0---强制用０表示
$---加一$符号，美元符号打印在数的左边
L---前加本地货币单位表示
.---十进制点，打印十进制的小数点
,---千进制点，打印代表千分位的逗号
select to_char(0917,'l9999.99') local from dual
LOCAL
------------------
RMB917.00
字符-->日期
tselect to_date('19830917','yyyy-mm-dd') bir from dual
BIR
----------
17-9月 -83
字符-->数字
select to_number('19821217','999999999') mybr from dual
MYBR
----------
19821217
注：所有函数均可以嵌套使用
例：
1.
select to_number('123.45')+to_number('234.56') form test;
to_number('123.45')+to_number('234.56')
----------------------------------------
358.01
2.
select to_char(987654321) from test;
to_char(987654321)
------------------
987654321
3.
select to_char(123,'$9,999,999') a,to_char(54321,'$9,999,999') b,to_char(9874321,'$9,999,999') c from test;
a b c
------- ---------- -----------
$123 $54,321 $9,874,321
4.
select to_char(1234.1234,'999,999.999') a,to_char(0.4567,'999,999.999') b,to_char(1.1,'999,999.999') from test;
a b c
--------- ---------- ------------
1,234.123 .457 1.100
5>通用函数
nvl(expr1,expr2)---expr1为空则显示expr2,否则显示expr1
nvl2(expr1,expr2,expr3)---expr1为空则显示expr2,否则显示expr3
nullif(expr1,expr2)---二个相等则显示空符,否则显示expr1
coalesce(expr1,expr2,...,exprn)---从expr1开始依次找到不为空的expr，找到就显示，直到最后，否则显
示exprn
case表达式，如下图:
6>多表查询
对普通的多表查询，也就是不加where条件的时候实际上查询结果是各表的笛卡尔集
外连接：oracle实现外连接时在=号的二边加+号就OK，当+在左边时称为右连接，反之为左连接,它常常用来当要求未受限制对象的表数据也要求显示时，如下：
select * from student
ID NAME ADDRESS
-------------------- --------------
1 zhangshan
3 lishi
7 lily
select * from person
ID NAME ADDRESS
- -------------------- -----------
1 zhangshan
3 lishi
7 lily
select p.id,p.sex,s.id,s.name,s.address from person p ,student s where p.id(+) = s.id
ID SEX ID NAME ADDRESS
-- ---- ---------- -------------------- ------------
1 boy 1 zhangshan
3 girl 3 lishi
7 lily
select p.id,p.sex,s.id,s.name,s.address from person p ,student s where p.id = s.id(+)
ID SEX ID NAME ADDRESS
-- ---- ---------- -------------------- --------------
1 boy 1 zhangshan
2 girl
3 girl 3 lishi
4 boy
5 girl
self-join，就是同一张表连接，用自连接的时候要注意排除重复的记录（自身，循环重复等），比如说找出student表中所有住在同一个地方的人。
select * from student
ID NAME ADDRESS
--- -------------------- ---------
1 zhangshan zhejiang
3 lishi hangzhou
7 lily guangzhou
2 name2 guangzhou
4 name4 guangzhou
5 name5 hangzhou
6 name6 shanghai
8 name8 shanghai
没有排除重复记录时的结果：
select t1.name,t2.name
from student t1, student t2
where t1.address = t2.address
NAME NAME
-------------------- --------------
lily lily
name2 lily
name4 lily
lily name2
name2 name2
name4 name2
lily name4
name2 name4
name4 name4
lishi lishi
name5 lishi
NAME NAME
-------------------- --------------
lishi name5
name5 name5
name6 name6
name8 name6
name6 name8
name8 name8
zhangshan zhangshan
已选择18行
排除重复与循环记录之后：
select t1.name,t2.name,t1.address
from student t1, student t2
where t1.address = t2.address
and t1.id > t2.id
NAME NAME ADDRESS
-------------------- -------------------- -------------------
lily name2
name4 name2
lily name4
name5 lishi
name8 name6 shanghai
cross join,无条件连接，实际上跟不带where时一样得到的是笛卡尔集
natural join（也可以直接记作join），根据表中同名栏位来连接记录,若表中可能出现多个同名栏位，则用using(col_name)来指定所栏位。colname不能指定表的别名！限制条件用on来指定
select id,p.sex,id,s.name from person p join student s using(id);
ID SEX ID NAME
---- ---- ---------- --------------------
1 boy 1 zhangshan
2 girl 2 name2
3 girl 3 lishi
4 boy 4 name4
5 girl 5 name5
在sql标准语法中,左(右)连接用left(right) out join,限制条件用on就可以了。
full out join完全外连接,顾名思义就是左外连接与右外连接都有
select id,p.sex,id,s.name from person p full join student s using(id);
ID SEX ID NAME
---- ---- ---------- ------------------
1 boy 1 zhangshan
3 girl 3 lishi
2 girl 2 name2
4 boy 4 name4
5 girl 5 name5
8 8 name8
6 6 name6
7 7 lily
7>分组函数：
avg([distinct/all] n) 列n的平均值
count([all] *) 返回查询范围内的行数包括重复值和空值
count([distinct/all] n) 非空值的行数
max([distinct/all] n) 该列或表达式的最大值
min([distinct/all] n) 该列或表达式的最小值
stdev([distinct/all] n) 该列或表达式的标准偏差，忽略空值
sum([distinct/all] n) 该列或表达式的总和
variance([distinct/all] n) 该列或表达式的方差，忽略空值
