1灵再、分頁查詢
Oracle分頁關(guān)鍵字：rownum
MySql的分頁關(guān)鍵字：limit
1）、rownum：偽列
該列在數(shù)據(jù)庫表中并不存在庇勃，但是它又“存在于”
任何的表中檬嘀。該字段的取值取決于查詢語句的記錄數(shù)。
rownum其實(shí)就是對查詢出的記錄數(shù)據(jù)做編號處理责嚷，
編號從1開始鸳兽。
rownum存在的sql，必須是對真實(shí)字段名的查詢罕拂。
--rownum不能直接跟*一起使用揍异。
select *,rownum from emp;
--rownum正確使用
select empno,ename,hiredate,sal,deptno,rownum
from emp;
--查詢r(jià)ownum <= 終止位置
--查詢前10條數(shù)據(jù)
select empno,ename,hiredate,sal,deptno,rownum
from emp
where rownum <= 10;
--若按每頁5條數(shù)據(jù)
--查詢第一頁為前5條數(shù)據(jù):1~5
select empno,ename,hiredate,sal,deptno,rownum
from emp
where rownum >= 1 and rownum <= 5;
--查詢第二頁為后5條數(shù)據(jù)：6~10
select empno,ename,hiredate,sal,deptno,rownum
from emp
where rownum >= 6 and rownum <= 10;
結(jié)論：在第一次進(jìn)行rownum查詢時(shí)，不建議使用rownum
做大于判斷爆班，否則可能達(dá)不到預(yù)期效果衷掷。
如：上面的第一頁有數(shù)據(jù)，第二頁無數(shù)據(jù)柿菩。
原因：rownum默認(rèn)從1開始戚嗅，而rownum的編號自增前提
根據(jù)條件能查詢到下一條數(shù)據(jù)。查詢一條數(shù)據(jù)枢舶，才會(huì)
有rownum的自增懦胞。而查詢語句中where條件中要求
一上來就rownum>=6，這里有矛盾關(guān)系凉泄，
rownum從1開始躏尉，要想rownum>=6,必須使得rownum先
自增到6才行。
所以導(dǎo)致where rownum>=6的條件后众，永遠(yuǎn)都不可能滿足胀糜。
解決方案：如何才能做rownum的大于操作颅拦？
先編號，再分頁教藻。
--后分頁
select empno,ename,hiredate,sal,deptno,temp.num
from(
--先編號
select empno,ename,hiredate,sal,deptno,
rownum num --****給rownum取別名
from emp
) temp
where temp.num >= 6 and temp.num <= 10;
2）距帅、分頁要排序
按部門排序
因?yàn)椋簊elect ruwnum執(zhí)行順序在order by之前，
會(huì)導(dǎo)致查詢的數(shù)據(jù)怖竭，先編號锥债，再排序。
排序的結(jié)果比如會(huì)導(dǎo)致編號順序雜亂痊臭，也僅僅是
對固定編號內(nèi)的結(jié)果數(shù)據(jù)哮肚，進(jìn)行小范圍的排序而已。
根本也無法實(shí)現(xiàn)真正意義上的排序广匙。
結(jié)論：排序一定要在編號之前完成允趟。
最后的分頁sql就得按照如下順序：
先排序 、再編號鸦致、最后分頁
--最后分頁
select empno,ename,hiredate,sal,deptno,num
from(
--再編號
select empno,ename,hiredate,sal,deptno,
rownum num --****給rownum去別名
from (
--先排序
select * from emp order by deptno
)
--where rownum <= 10 --小于操作也可以此處潮剪。
)
where num >= 6 and num <= 10;--推薦使用這樣編寫
3）、分頁參數(shù)
a分唾、pageSize：
每頁顯示條數(shù)（記錄數(shù)）
即一頁多少條數(shù)據(jù)抗碰。
b、pageNum：
頁碼數(shù)
即需要查詢?yōu)榈趲醉摗?/p>
舉例：每頁顯示5條
第1頁：1~5
第2頁：6~10
第3頁：11~15
...
第100頁：496~50
對應(yīng)頁碼的記錄數(shù)為：
rownum起始位置：
(pageNum - 1) * pageSize + 1
rownum終止位置：
pageNum * pageSize;
4）绽乔、終極分頁sql
--最后分頁
select empno,ename,hiredate,sal,deptno,num
from(
--再編號
select empno,ename,hiredate,sal,deptno,
rownum num --****給rownum去別名
from (
--先排序
select * from emp order by deptno
)
)
where num >= ((pageNum - 1) * pageSize + 1)
and num <= (pageNum * pageSize);
2弧蝇、高級函數(shù)
1）、decode函數(shù)
可以實(shí)現(xiàn)類似switch-case效果折砸。
語法：
decode(字段名,
search1,result1,
search2,result2,
...
searchN,resultN, --如果沒有default看疗，逗號省略
[default]
)
可以使用decode函數(shù)，用在兩個(gè)方面：
①睦授、分組
統(tǒng)計(jì)人數(shù)
--按照job職位分組統(tǒng)計(jì)人數(shù)
select job,count(*) from emp
group by job;
--將MANAGER與ANALYST統(tǒng)計(jì)為vip組两芳，
--其他職位統(tǒng)計(jì)為other組
--要求統(tǒng)計(jì)vip與other組的人數(shù)。
select decode(job,
'MANAGER','vip',
'ANALYST','vip',
'other'
) 職位,count(*) from emp
group by
decode(job,
'MANAGER','vip',
'ANALYST','vip',
'other'
);
②去枷、排序
--按薪資怖辆、部門、job排序
select * from emp
order by job;--按職位的首字母ASIIC碼自然排序
--現(xiàn)實(shí)中删顶，必須要嚴(yán)格遵循職位重要性來排序疗隶。
PRESIDENT
MANAGER
ANALYST
CLERK
SALESMAN
select * from emp
order by
decode(job,
'PRESIDENT',1,
'MANAGER',2,
'ANALYST',3,
'CLERK',4,
5
);
2）、case-when函數(shù)
與decode效果一致翼闹，但是語法復(fù)雜很多，建議使用decode蒋纬。
語法：
case 字段名
when search1 then result1
when search2 then result2
...
when searchN then resultN
else resultN+1
end;
--現(xiàn)實(shí)中猎荠，必須要嚴(yán)格遵循職位重要性來排序坚弱。
PRESIDENT
MANAGER
ANALYST
CLERK
SALESMAN
select * from emp
order by
(case job
when 'PRESIDENT' then 1
when 'MANAGER' then 2
when 'ANALYST' then 3
when 'CLERK' then 4
else 5
end);
decode(字段名,
search1,result1,
search2,result2,
...
searchN,resultN, --如果沒有default，逗號省略
[default]
)
3）关摇、排序函數(shù)
查詢員工信息荒叶，根據(jù)部門分組，工資順序排序输虱。
select * from emp
group by deptno
order by sal;
怎么辦些楣？？宪睹？
對于如上組內(nèi)排序的sql愁茁，可以直接使用高級排序函數(shù)來實(shí)現(xiàn)。
根據(jù)排序結(jié)果亭病，分為：
①鹅很、組內(nèi)（編號）連續(xù)且唯一排序
row_number
語法：row_number() --連續(xù)且唯一編號
--按以下規(guī)則
over(
partition by 字段1 --按字段1分組
order by 字段2 --按字段2排序
)
如：查詢員工信息，根據(jù)部門分組罪帖，工資順序排序促煮。
select empno,ename,hiredate,sal,deptno,
(row_number() --連續(xù)且唯一編號
--按以下規(guī)則
over(
partition by deptno --按字段1分組
order by sal --按字段2排序
)) 連續(xù)且唯一
from emp;
②、組內(nèi)（編號）連續(xù)不唯一排序
dense_rank
語法：dense_rank() --連續(xù)不唯一編號
--按以下規(guī)則
over(
partition by 字段1 --按字段1分組
order by 字段2 --按字段2排序
)
如：查詢員工信息整袁，根據(jù)部門分組菠齿，工資順序排序。
select empno,ename,hiredate,sal,deptno,
(dense_rank() --連續(xù)不唯一編號
--按以下規(guī)則
over(
partition by deptno --按字段1分組
order by sal --按字段2排序
)) 連續(xù)不唯一編號
from emp;
③坐昙、組內(nèi)（編號）不連續(xù)不唯一排序
rank
語法：rank() --不連續(xù)不唯一編號
--按以下規(guī)則
over(
partition by 字段1 --按字段1分組
order by 字段2 --按字段2排序
)
如：查詢員工信息绳匀，根據(jù)部門分組，工資順序排序民珍。
select empno,ename,hiredate,sal,deptno,
(rank() --不連續(xù)不唯一編號
--按以下規(guī)則
over(
partition by deptno --按字段1分組
order by sal --按字段2排序
)) 不連續(xù)不唯一編號
from emp;
2襟士、高級函數(shù)
1）、分支函數(shù)
decode
分支效果嚷量。
來實(shí)現(xiàn)排序陋桂、分組。
case-when與decode函數(shù)功能相同
2）蝶溶、排序函數(shù)
row_number()：組內(nèi)連續(xù)且唯一嗜历。
rank()：組內(nèi)不連續(xù)不唯一。
dense_rank()：組內(nèi)連續(xù)不唯一抖所。
正課：
1梨州、高級函數(shù)
1）、分支函數(shù)
2）田轧、排序函數(shù)
3）暴匠、集合操作
①、并集
union：自動(dòng)去重聯(lián)合查詢
--得到15條數(shù)據(jù)
select * from emp -- 員工表集合 15
union
select * from leader;--領(lǐng)導(dǎo)表集合 6
union all：不去重聯(lián)合查詢
--得到21條數(shù)據(jù)
select * from emp -- 員工表集合 15
union all
select * from leader;--領(lǐng)導(dǎo)表集合 6
②傻粘、交集
intersect
--得到6條數(shù)據(jù)
select * from emp -- 員工表集合 15
intersect
select * from leader;--領(lǐng)導(dǎo)表集合 6
③每窖、差集
minus
集合1 差 集合2
--得到9條數(shù)據(jù)
select * from emp --員工表集合 15
minus
select * from leader;--領(lǐng)導(dǎo)表集合 6
集合2 差 集合1
--得到0條數(shù)據(jù)
select * from leader--領(lǐng)導(dǎo)表集合 6
minus
select * from emp;--員工表集合 15
