只是简单读了读《oracle查询优化改写》，就让我获益匪浅，想写好sql，这一本书就够了！
目錄
寫在前面
基礎(chǔ)知識
空值
返回前幾行
獲取隨機(jī)數(shù)
like
排序
union
分頁（6-10條）
表關(guān)聯(lián)
復(fù)制表
日期
日期加減
trunc對于日期的用法
獲取時(shí)間
判斷是否是閏年（只需要判斷二月底是哪一天）：
數(shù)字
聚集函數(shù)使用前先把null轉(zhuǎn)化為0：coalesce(a,0)
ROUND（number[,decimals]）
trunc對數(shù)字的操作（取整）
其他取大小值
字符串
原樣輸出字符串Q-quote：? q'[內(nèi)容]'
計(jì)算str中逗號出現(xiàn)的次數(shù)
字符串中字符替換
正則表達(dá)式
實(shí)例
相關(guān)函數(shù)
求余數(shù)
返回第一個(gè)不是空的值
Case具有兩種格式。簡單Case函數(shù)和Case搜索函數(shù)。
translate函數(shù)
lag與lead函數(shù)
書中還有很多知識點(diǎn)需要去挖掘！
寫在前面
前些日子寫sql語句，因?yàn)橹皩憇ql僅僅局限于簡單的增刪改查，碰到復(fù)雜難寫的sql就得冥思苦想，真正意義上的面向百度編程。
簡單讀了《oracle查詢優(yōu)化改寫》，真的是讓我豁然開朗，非常建議新人朋友、甚至想要提高自己的技術(shù)高人，都有時(shí)間來讀一讀。
sql語法僅限oracle！！！
基礎(chǔ)知識
空值
篩選條件只能是 is null 或者is not null。
null不支持加減乘除大小相等比較，否則只能為空。
order by 3 nulls first -----------空值排在前
order by 3 nulls lase ------------空值排在后
返回前幾行
返回前2行（如果要用=號，只能再加一層并且加上rownum進(jìn)行篩選） ： where rownum<=2
rownum可以在sql語句后面直接where！！
獲取隨機(jī)數(shù)
dbms_random.value
dbms_random.value可以在sql語句后面直接order by！！
like
like '%e_' ------%代表一個(gè)或多個(gè)字符，_代表一個(gè)字符。
like '\_abc%'? escape'\' -------轉(zhuǎn)義字符\就可以匹配出_abc，escape定義轉(zhuǎn)義字符。
like '\\abc%' ------就可以匹配出\abc。
排序
asc 、 desc? ?升序 降序
order by 3 ----按第三列排序，這種排序可以不用使用字段！！也不用再加一層
order by 3 nulls first -----------空值排在前
order by 3 nulls lase ------------空值排在后
order by 可以直接跟case when 函數(shù)，（相當(dāng)于把這一列提取出來到order by里面了，而不用在列中顯示）
union
union是求兩個(gè)（或者多個(gè)）集合的并集（不允許重復(fù)元素），得到的集合是{1，2，3，4，5，6，7，8，9}；
union all也是求兩個(gè)（或者多個(gè)）集合的并集（允許重復(fù)元素），得到的集合是{1，2，3，4，5，5，6，7，8，9}；
而minus是求兩個(gè)集合的差集，并且是前面一個(gè)集合去掉重復(fù)元素的部分，得到的集合是{1，2，3，4}；
分頁（6-10條）
根據(jù)rownum，先獲取前10條，再在最外層獲取6-10條。
表關(guān)聯(lián)
1、select * from A where id in (select id from B)--使用in
2、select * from A where exists(select B.id from B where B.id=A.id)--使用exists
也可以完全不使用in和exists（這種相當(dāng)于inner join！）：
3、select A.* from A,B where A.id=B.id--不使用in和exists
還有not in、not exists表示相反的意思。
left join?
right join?
full join
inner join?
復(fù)制表
create table test2 as select * from test;
只復(fù)制表的定義：
create table test2 as select * from test where 1=2;
多表插入（當(dāng)使用了ALL時(shí)，每一個(gè)WHEN子句都會(huì)進(jìn)行判斷；使用FIRST，FIRST則是按順序判斷WHEN子句，只執(zhí)行第一個(gè)滿足條件的WHEN子句，后面的WHEN子句則直接被跳過）：
INSERT ALL
WHEN sum_orders < 10000 THEN INTO small_customers
WHEN sum_orders >= 10000 AND sum_orders < 100000 THEN INTO medium_customers
ELSE INTO lager_customers
SELECT customer_id, SUM(order_total) sum_orders
FROM oe.orders
GROUP BY customer_id;
日期
日期加減
在oracle中，date類型直接相減得相差天數(shù)，加減數(shù)字就是加減天數(shù)，加減月份要用add_months函數(shù)： hiredate - 5 -------減五天 hiredate + 5 -------加五天 add_months(hiredate,-5) ------減五個(gè)月 add_months(hiredate,5) ------加五個(gè)月 add_months(hiredate,-5*12) ------減五年 add_months(hiredate,5*12) ------加五年 hiredate - 5/24/60/60 -----減五秒 hiredate + 5/24/60/60 -----加五秒 hiredate - 5/24/60 -----減五分鐘 hiredate + 5/24/60 -----加五分鐘 hiredate - 5/24 -----減五小時(shí) hiredate + 5/24 -----加五小時(shí)date1 - date2 ----------------間隔天數(shù) (date1 -date2)*24 --------------間隔小時(shí)數(shù) (date1 -date2)*24*60 --------------間隔分鐘數(shù) (date1 -date2)*24*60*60 --------------間隔秒數(shù) months_between(date1,date2)------間隔月 months_between(date1,date2)/12---------間隔年trunc對于日期的用法
select to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss'),to_char(trunc(sysdate), 'yyyy-mm-dd hh24:mi:ss')from dual t;--sysdate和trunc(sysdate)是不一樣的 sysdate在當(dāng)天的零時(shí)零分零秒等于trunc(sysdate) select trunc(sysdate, 'dd'), trunc(sysdate) from dual t; --今天 20140703 零時(shí)零分零秒 select to_char(trunc(sysdate, 'dd'), 'yyyy/mm/dd hh24:mi:ss'),to_char(trunc(sysdate), 'yyyy/mm/dd hh24:mi:ss')from dual;select trunc(sysdate-to_date('20140709 23:12:12','yyyy/mm/dd hh24:mi:ss')) from dual t;--兩個(gè)日期相差的天數(shù) select trunc(sysdate-1) from dual t;--昨天 select trunc(sysdate+1) from dual t;--明天 select trunc(sysdate, 'yy') from dual;--當(dāng)年第一天 select trunc(sysdate, 'year') from dual;--當(dāng)年第一天 select trunc(sysdate, 'yyyy') from dual;--當(dāng)年第一天 select trunc(sysdate, 'q') from dual;--當(dāng)前時(shí)間所在的季度的第一天 select trunc(sysdate, 'mm') from dual;--當(dāng)月第一天 select trunc(sysdate, 'month') from dual;--當(dāng)月第一天 select trunc(sysdate, 'd') from dual;--返回本周的第一天（周日為第一天） select trunc(sysdate,'day') from dual;--返回本周的第一天（周日為第一天） select trunc(sysdate, 'iw') from dual;--本周第二天（周日為第一天） select trunc(sysdate, 'hh') from dual; --當(dāng)前時(shí)間，精確到小時(shí) select trunc(sysdate, 'hh24') from dual;--當(dāng)前時(shí)間。精確到小時(shí) select trunc(sysdate, 'mi') from dual;--當(dāng)前時(shí)間。精確到分鐘 沒有精確到秒的精度獲取時(shí)間
to_number(to_char(date1,'hh24'))-----時(shí) to_number(to_char(date1,'mi'))-----分 to_number(to_char(date1,'ss'))-----秒 to_number(to_char(date1,'dd'))-----日 to_number(to_char(date1,'mm'))-----月 to_number(to_char(date1,'yyyy'))-----年 to_number(to_char(date1,'ddd'))-----年內(nèi)第幾天 trunc(date1,'dd')----一天之始 trunc(date1,'day')----周初 trunc(date1,'mm')----月初 last_day(date1)-----月末 add_months(trunc(date1,'mm'),1)---下月初 trunc(date1,'yy')-----年初 to_char(date1,'day')----周幾 to_char(date1,'month')----月份 next_day(date1,1) ---- 下個(gè)周日獲取時(shí)間（to_char獲取的是字符串，extract獲取的是數(shù)字類型）： extract(YEARfrom date1)--------年 extract(MONTH from date1)--------月 extract(DAY from date1)--------日 extract(HOUR from date1)--------小時(shí) extract(MINUTE from date1)--------分鐘 extract(SECOND from date1)--------秒判斷是否是閏年（只需要判斷二月底是哪一天）：
last_day(add_months(trunc(hiredate,'y'),1)) as 二月底
數(shù)字
聚集函數(shù)使用前先把null轉(zhuǎn)化為0：coalesce(a,0)
avg(a) ----平均值
min(a) -------最小值
max(a) ------ 最大值
sum(a) ------總數(shù)
count(*) -----總行數(shù)
sum(a) over(order by b) ----從第一行到當(dāng)前行的總和，（先根據(jù)b排序之后）
ROUND（number[,decimals]）
四舍五入，decimals為可選值，表示小數(shù)點(diǎn)后的位數(shù)。可為負(fù)數(shù)！
trunc對數(shù)字的操作（取整）
/* TRUNC（number,num_digits） Number 須要截尾取整的數(shù)字。 Num_digits 用于指定取整精度的數(shù)字。 Num_digits 的默認(rèn)值為 0。 正數(shù)向后截取，負(fù)數(shù)向前截取 TRUNC()函數(shù)截取時(shí)不進(jìn)行四舍五入 */ select trunc(123.458) from dual; --123 select trunc(123.458, 0) from dual; --123 select trunc(123.458, 1) from dual; --123.4 select trunc(123.458, -1) from dual; --120 select trunc(123.458, -4) from dual; --0 select trunc(123.458, 4) from dual; --123.458 select trunc(123) from dual; --123 select trunc(123, 1) from dual; --123 select trunc(123, -1) from dual; --120其他取大小值
min(sal) over(partition by deptno) 部門最低工資, max(sal) over(partition by deptno) 部門最高工資lead(sal, 1, 0) over(partition by deptno order by sal) 比自己工資高的部門前一個(gè), lag(sal, 1, 0) over(partition by deptno order by sal) 比自己工資低的部門后一個(gè), nvl(lead(sal) over(partition by deptno order by sal) - sal, 0) 比自己工資高的部門前一個(gè)差額, nvl(sal - lag(sal) over(partition by deptno order by sal), 0) 比自己工資高的部門后一個(gè)差額取最大值最小值也可以用： first_value(a) over(partition by deptno) last_value(a) over(partition by deptno)字符串
substr(a,1,2)--------截取a字符串的從第1個(gè)開始數(shù)2個(gè)字符。
lower('A') --------字符串替換成小寫
listagg(a,',') within group(order by b)-----用在group by中，多個(gè)列用逗號間隔顯示
原樣輸出字符串Q-quote：? q'[內(nèi)容]'
SELECT Q'[I'm a boy,my name is 'david']' FROM DUAL; --結(jié)果：I'm a boy,my name is 'david' SELECT Q'(I'm a boy,my name is 'david')' FROM DUAL; --結(jié)果：I'm a boy,my name is 'david' SELECT Q'{I'm a boy,my name is 'david'}' FROM DUAL; --結(jié)果：I'm a boy,my name is 'david' SELECT Q'|I'm a boy,my name is 'david'|' FROM DUAL; --結(jié)果：I'm a boy,my name is 'david' SELECT Q'aI'm a boy,my name is 'david'a' FROM DUAL; --結(jié)果：I'm a boy,my name is 'david' SELECT Q'2I'm a boy,my name is 'david'2' FROM DUAL; --結(jié)果：I'm a boy,my name is 'david' SELECT Q'@I'm a boy,my name is 'david'@' FROM DUAL; --結(jié)果：I'm a boy,my name is 'david' SELECT Q'*I'm a boy,my name is 'david'*' FROM DUAL; --結(jié)果：I'm a boy,my name is 'david' SELECT Q'#I'm a boy,my name is 'david'#' FROM DUAL; --結(jié)果：I'm a boy,my name is 'david' SELECT Q'[[@#$%^&*~?/" ']]' FROM DUAL; --結(jié)果：[@#$%^&*~?/" ']計(jì)算str中逗號出現(xiàn)的次數(shù)
regexp_count(str,',')-------計(jì)算str中逗號出現(xiàn)的次數(shù)
字符串中字符替換
replace(str_source,str1,str2)??把 str_source 中 str1 字符串替換為 str2 字符串，當(dāng) str2 為 null 或'' 時(shí)，把str_source 中的 str1 字符串剔除
replace(str_source,str1)?????????把str_source 中的 str1 字符串剔除
regexp_replace(str_source,pattern_str,rep_str) 支持正則表達(dá)式，用法類似于 replace，但功能更強(qiáng)大
regexp_replace(str_source,pattern_str)???把 str_source 中的 pattern_str 字符串剔除
translate(str_source,chr1,chr2) 以字符為單位，把 str_source 中的 chr1 字符對應(yīng)替換為 chr2。如果 chr1 比chr2 長，那么在 chr1 中而不在 chr2 中的字符將被剔除，因?yàn)闆]有對應(yīng)的替換字符。需注意 chr2 不能為 null 或''，否則返回值也為空 （translate(str,'1abc','1')---去除abc）
比方：
select REGEXP_SUBSTR(‘虹橋/北京’,’[^/]+’,1,1) from dual -----返回的是虹橋
select REGEXP_SUBSTR(‘虹橋/北京’,’[^/]+’,1,2) from dual----------返回的是北京
第一個(gè)參數(shù)是要解析的字符串，第二個(gè)是正在表達(dá)式，第三個(gè)表示字符串解析時(shí)的起始位置。比方：
select REGEXP_SUBSTR(‘虹橋/北京’,’[^/]+’,2,1) from dual------返回的是橋
第四個(gè)參數(shù)表示取拆分后數(shù)組里的第幾個(gè)值。
正則表達(dá)式
ORACLE中的支持正則表達(dá)式的函數(shù)主要有下面四個(gè)：
1，REGEXP_LIKE ：與LIKE的功能相似
2，REGEXP_INSTR ：與INSTR的功能相似
3，REGEXP_SUBSTR ：與SUBSTR的功能相似
4，REGEXP_REPLACE ：與REPLACE的功能相似
5，REGEXP_COUNT? ：?regexp_count(str,',')-------計(jì)算str中逗號出現(xiàn)的次數(shù)
它們在用法上與Oracle SQL 函數(shù)LIKE、INSTR、SUBSTR 和REPLACE 用法相同，
但是它們使用POSIX 正則表達(dá)式代替了老的百分號（%）和通配符（_）字符。
POSIX 正則表達(dá)式由標(biāo)準(zhǔn)的元字符（metacharacters）所構(gòu)成：
'^' 匹配輸入字符串的開始位置，在方括號表達(dá)式中使用，此時(shí)它表示不接受該字符集合。
'$' 匹配輸入字符串的結(jié)尾位置。如果設(shè)置了 RegExp 對象的 Multiline 屬性，則 $ 也匹
配 '/n' 或 '/r'。
'.' 匹配除換行符之外的任何單字符。
'?' 匹配前面的子表達(dá)式零次或一次。
'+' 匹配前面的子表達(dá)式一次或多次。
'*' 匹配前面的子表達(dá)式零次或多次。
'|' 指明兩項(xiàng)之間的一個(gè)選擇。例子'^([a-z]+|[0-9]+)$'表示所有小寫字母或數(shù)字組合成的
字符串。
'( )' 標(biāo)記一個(gè)子表達(dá)式的開始和結(jié)束位置。
'[]' 標(biāo)記一個(gè)中括號表達(dá)式。
'{m,n}' 一個(gè)精確地出現(xiàn)次數(shù)范圍，m=<出現(xiàn)次數(shù)<=n，'{m}'表示出現(xiàn)m次，'{m,}'表示至少
出現(xiàn)m次。
/num 匹配 num，其中 num 是一個(gè)正整數(shù)。對所獲取的匹配的引用。
字符簇：
[[:alpha:]] 任何字母。
[[:digit:]] 任何數(shù)字。
[[:alnum:]] 任何字母和數(shù)字。
[[:space:]] 任何白字符。
[[:upper:]] 任何大寫字母。
[[:lower:]] 任何小寫字母。
[[:punct:]] 任何標(biāo)點(diǎn)符號。
[[:xdigit:]] 任何16進(jìn)制的數(shù)字，相當(dāng)于[0-9a-fA-F]。
各種操作符的運(yùn)算優(yōu)先級
/轉(zhuǎn)義符
(), (?:), (?=), [] 圓括號和方括號
*, +, ?, {n}, {n,}, {n,m} 限定符
^, $, anymetacharacter 位置和順序
*/
實(shí)例
--創(chuàng)建表
create table fzq
(
id varchar(4),
value varchar(10)
);
--數(shù)據(jù)插入
insert into fzq values
('1','1234560');
insert into fzq values
('2','1234560');
insert into fzq values
('3','1b3b560');
insert into fzq values
('4','abc');
insert into fzq values
('5','abcde');
insert into fzq values
('6','ADREasx');
insert into fzq values
('7','123 45');
insert into fzq values
('8','adc de');
insert into fzq values
('9','adc,.de');
insert into fzq values
('10','1B');
insert into fzq values
('10','abcbvbnb');
insert into fzq values
('11','11114560');
insert into fzq values
('11','11124560');
--regexp_like
--查詢value中以1開頭60結(jié)束的記錄并且長度是7位
select * from fzq where value like '1____60';
select * from fzq where regexp_like(value,'1....60');
--查詢value中以1開頭60結(jié)束的記錄并且長度是7位并且全部是數(shù)字的記錄。
--使用like就不是很好實(shí)現(xiàn)了。
select * from fzq where regexp_like(value,'1[0-9]{4}60');
-- 也可以這樣實(shí)現(xiàn)，使用字符集。
select * from fzq where regexp_like(value,'1[[:digit:]]{4}60');
-- 查詢value中不是純數(shù)字的記錄
select * from fzq where not regexp_like(value,'^[[:digit:]]+$');
-- 查詢value中不包含任何數(shù)字的記錄。
select * from fzq where regexp_like(value,'^[^[:digit:]]+$');
--查詢以12或者1b開頭的記錄.不區(qū)分大小寫。
select * from fzq where regexp_like(value,'^1[2b]','i');
--查詢以12或者1b開頭的記錄.區(qū)分大小寫。
select * from fzq where regexp_like(value,'^1[2B]');
-- 查詢數(shù)據(jù)中包含空白的記錄。
select * from fzq where regexp_like(value,'[[:space:]]');
--查詢所有包含小寫字母或者數(shù)字的記錄。
select * from fzq where regexp_like(value,'^([a-z]+|[0-9]+)$');
--查詢?nèi)魏伟瑯?biāo)點(diǎn)符號的記錄。
select * from fzq where regexp_like(value,'[[:punct:]]');
相關(guān)函數(shù)
求余數(shù)
mod(a,2)---求余數(shù)
返回第一個(gè)不是空的值
只能寫兩個(gè)參數(shù)： nvl(a,b)
可以寫多個(gè)參數(shù) ： coalesce(a,b,c,d)
Case具有兩種格式。簡單Case函數(shù)和Case搜索函數(shù)。
--簡單Case函數(shù)??
CASE sex??
WHEN '1' THEN '男'??
WHEN '2' THEN '女'??
ELSE '其他' END??
--Case搜索函數(shù)??
CASE WHEN sex = '1' THEN '男'??
WHEN sex = '2' THEN '女'??
ELSE '其他' END??
translate函數(shù)
translate(a,b,c)------把a(bǔ)中b的內(nèi)容依次替代為c的內(nèi)容
translate('ab你好bcadefg','abcdefg','1234567')? ---------12你好2314567
translate(a,b,'') ----------結(jié)果為null
translate('ab你好bcadefg','1abcdefg','1')-------結(jié)果為'你好'
lag與lead函數(shù)
lag與lead函數(shù)是跟偏移量相關(guān)的兩個(gè)分析函數(shù)，通過這兩個(gè)函數(shù)可以在一次查詢中取出同一字段的前N行的數(shù)據(jù)(lag)和后N行的數(shù)據(jù)(lead)作為獨(dú)立的列,從而更方便地進(jìn)行進(jìn)行數(shù)據(jù)過濾。這種操作可以代替表的自聯(lián)接，并且LAG和LEAD有更高的效率。
?
over()表示 lag()與lead()操作的數(shù)據(jù)都在over()的范圍內(nèi)，他里面可以使用partition by 語句（用于分組） order by 語句（用于排序）。partition by a order by b表示以a字段進(jìn)行分組，再 以b字段進(jìn)行排序，對數(shù)據(jù)進(jìn)行查詢。
?
例如：lead(field, num, defaultvalue) field需要查找的字段，num往后查找的num行的數(shù)據(jù)，defaultvalue沒有符合條件的默認(rèn)值。
書中還有很多知識點(diǎn)需要去挖掘！
總結(jié)
以上是生活随笔為你收集整理的只是简单读了读《oracle查询优化改写》，就让我获益匪浅，想写好sql，这一本书就够了！的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： weblogic从入门到飞起（部署应用、
- 下一篇： spring框架如何调用异步方法？快进来