幕課oracle學(xué)習(xí)筆記
--！０杵痢潮针！scott用戶
--一.分組查詢
--1.常用的分組函數(shù)：AVG(平均數(shù))，SUM倚喂，MIN每篷，MAX，COUNT端圈，WM_CONCAT(行轉(zhuǎn)列)
select avg(sal),sum(sal) from emp;--工資的平均值焦读，工資的總和
select max(sal),min(sal) from emp;--工資的最大值，最小值
select count(*) from emp;--員工的總數(shù)
select count(distinct deptno) from emp;--根據(jù)部門號(hào)取重查詢
select deptno 部門號(hào),wm_concat(ename) 部門中的員工 from emp group by deptno;--行轉(zhuǎn)列,查出各個(gè)部門的人員名字
--分組函數(shù)會(huì)自動(dòng)過(guò)濾空值舱权，使用NVL函數(shù)使分組函數(shù)無(wú)法忽略空值
select avg(comm) from emp;--平均獎(jiǎng)金為550
select avg(nvl (comm,0)) from emp;--平均獎(jiǎng)金為157.14
--2.分組數(shù)據(jù):
--（1）GROUP BY子句:select a,b,c,組函數(shù)（x） from? table_name? group by a,b,c;在select列表中所有未包含在組函數(shù)中的列都應(yīng)該包含在group by子句中
select deptno,avg(sal) from emp group by deptno;
--多個(gè)列分組
select deptno,job,sum(sal) from emp group by deptno,job;--求出每個(gè)部門各個(gè)職位的工資綜合
--（2）HAVING子句：過(guò)濾分組數(shù)據(jù)
select deptno,avg(sal) from emp group by deptno having avg(sal)>2000;--求出平局工資大于2000的部門
--where子句中不能使用分組函數(shù)矗晃，可以在having幾組中使用;
--從sql優(yōu)化的角度，沒(méi)有組函數(shù).分組函數(shù)時(shí)where和having通用宴倍，盡量使用where（效率高）
select deptno,avg(sal) from emp group by deptno having deptno=10;--10號(hào)部門的平均工資
select deptno,avg(sal) from emp where deptno=10 group by deptno ;--10號(hào)部門的平均工資
--（3）order by子句：默認(rèn)升序,降序+desc
select deptno,avg(sal) 平均工資 from emp group by deptno order by 平均工資;--各部門平均工資并升序排列
select deptno,avg(sal) 平均工資 from emp group by deptno order by 2;--根據(jù)select語(yǔ)句的第二列排序
select deptno,avg(sal) 平均工資 from emp group by deptno order by 2 desc;
--（4）分組函數(shù)的嵌套
select max(avg(sal)) from emp group by deptno;--平均工資最高的部門的平均工資
--（5）group by語(yǔ)句的增強(qiáng):rollup();主要應(yīng)用于報(bào)表
select deptno,job,sum(sal) from emp group by deptno,job;
select deptno,sum(sal) from emp group by deptno;
select sum(sal) from emp;--以上三句查詢與下面的增強(qiáng)查詢結(jié)果一致
select deptno,job,sum(sal) from emp group by rollup(deptno,job);
--二.多表查詢
--笛卡爾積：多張表的列數(shù)相加张症，行數(shù)相乘所得的新表。;為了避免笛卡爾積啊楚，可以在where加入有效的連接條件
--四種連接（等值連接吠冤，不等值連接，外連接恭理，自連接）拯辙；
(1)等值連接
select e.empno,e.ename,e.sal,d.dname from emp e,dept d where e.deptno=d.deptno;
(2)不等值連接
select e.empno,e.ename,e.sal,s.grade from emp e,salgrade s where e.sal between s.losal and? s.hisal;
或select e.empno,e.ename,e.sal,s.grade from emp e,salgrade s where e.sal>=s.losal and? e.sal<=s.hisal;
(3)外連接：把對(duì)于連接條件不成立的記錄，仍然包含在結(jié)果中;
--下列語(yǔ)句查詢丟失了deptno=40的部門,因?yàn)?0部門人數(shù)為0
select d.deptno 部門號(hào),d.dname 部門名稱,count(e.empno) 部門人數(shù) from emp e,dept d where e.deptno=d.deptno group by d.deptno,d.dname;
--左外連接:連接條件不成立時(shí)，等號(hào)左邊的仍然被包含涯保；在連接條件左邊加 ： (+)
select d.deptno 部門號(hào),d.dname 部門名稱,count(e.empno) 部門人數(shù) from emp e,dept d where e.deptno(+)=d.deptno group by d.deptno,d.dname;
--右外連接：連接條件不成立時(shí)诉濒，等號(hào)右邊的仍然被包含；在連接條件右邊加 ： (+)
(4)自連接(不適合操作大表):通過(guò)別名夕春，將同一張表視為多張表
select e.ename,b.ename from emp e,emp b where e.mgr=b.empno;--獲得員工的名字和他上級(jí)的名字
--層次查詢(解決自連接操作大表的笛卡爾積問(wèn)題):某種情況下可以替代自連接未荒，本質(zhì)上是一個(gè)單表查詢;結(jié)果沒(méi)有自連接直觀
select level,empno,ename,sal,mgr from emp connect by prior empno=mgr start with mgr is null order by 1;
ps:connect by 上一層的員工號(hào)=老板號(hào)；start with 老板號(hào)為空及志，即子節(jié)點(diǎn)開(kāi)始片排；level：偽列，代表樹(shù)形結(jié)構(gòu)等級(jí)速侈；order by 1 表示按level第一層開(kāi)始排序
--三.子查詢(select語(yǔ)句的嵌套)
--(1)可以使用子查詢的位置：where率寡，select，having倚搬，from
select ename from? emp where sal>(select sal from emp where ename='SCOTT');--工資比scott高的員工
select empno,ename,sal,(select job from emp where empno=7839) from? emp;--所有員工的職位都是7839的職位
select deptno,avg(sal) from emp group by deptno having avg(sal)>(select max(sal) from emp where deptno=30);--部門平均工資大于30部門工資最大值的部門
select * from (select empno,ename,sal from emp);
--(2)不可以使用子查詢的位置：group by
***錯(cuò)誤提示：ORA-22818:這里不允許出現(xiàn)子查詢表達(dá)式**
select avg(sal) from emp group by(select deptno from emp);
--(3)*from 后面的子查詢
*******：在已知條件的基礎(chǔ)上得到更多的已知條件
select * from (select empno,ename,sal from emp);
select * from (select empno,ename,sal 月薪,sal*12 年薪 from emp);
--(4)主查詢冶共，子查詢可以不是同一張表,只要子查詢的結(jié)果主查詢可以使用就可以！
select * from emp where deptno=(select deptno from dept where dname='SALES');
--也可以使用多表查詢實(shí)現(xiàn)上述查詢
select e.* from emp e,dept d where e.deptno=d.deptno and d.dname='SALES';
--理論上盡量使用多表查詢每界，只有一個(gè)from捅僵，只訪問(wèn)一次數(shù)據(jù)庫(kù).
--(5)一般不在子查詢中使用排序，但在TOP-N分析問(wèn)題中眨层，必須對(duì)子查詢排序
--TOP-N問(wèn)題:例如薪水前三的員工
--rownum? 行號(hào)庙楚，oracle提供的偽列；永遠(yuǎn)按照默認(rèn)的順序生成（無(wú)任何條件的查詢表時(shí)的順序）谐岁，只能使用<,<=;不能使用>,>=(分頁(yè)查詢時(shí)使用此特性)
--解決上述偽列默認(rèn)順序?qū)е虏樵冇肋h(yuǎn)是前三個(gè)員工醋奠，而非工資前三的員工的問(wèn)題
--不使用原表，而使用倒序排列后的新表作為查詢表
select rownum,empno,ename,sal from (select? * from emp order by sal desc)? where rownum<=3;
--(6)一般先執(zhí)行子查詢伊佃，再執(zhí)行主查詢窜司；但相關(guān)子查詢例外
--查詢員工標(biāo)中薪水大于本部門平均水平的員工（相關(guān)子查詢：主查詢的中的值作為參數(shù)傳給子查詢,主查詢必須起別名）
select empno,ename,sal,(select avg(sal) from emp where deptno=e.deptno) avgsal from emp e where sal>(select avg(sal) from emp where deptno=e.deptno);
--(7)單行子查詢(返回單行記錄)只能使用單行操作符(=,>,>=,<,<=,<>)
--職位與7566員工一樣，薪水大于7782員工的薪水
select *? from emp where job=(select job from emp where empno=7566)? and? sal >(select sal from emp where empno=7782);
--查詢薪水最低的員工信息
select *? from? emp where sal=(select min(sal) from emp);
--查詢最低工資大于20號(hào)部門最低工資的部門號(hào)和部門的最低工資
select? deptno,min(sal) from emp group by deptno having min(sal)>(select min(sal) from emp where deptno=20);
--多行子查詢(返回多行記錄)只能使用多行操作符(in:等于列表中的任何一個(gè),any：和子查詢返回的任意一個(gè)值比較,all：和子查詢返回的所有值比較)
--in：查詢部門名稱是sales和accounting的員工信息
select * from emp where deptno in (select deptno from dept where dname='SALES' or dname='ACCOUNTING');--子查詢
select e.* from emp e ,dept d where e.deptno=d.deptno and (d.dname='SALES' or d.dname='ACCOUNTING');--多表聯(lián)查(加括號(hào)航揉，必須先執(zhí)行or塞祈，再執(zhí)行and)
--any：查詢工資比30號(hào)部門任意一個(gè)員工高的員工信息
select * from emp where sal>any(select sal from emp where deptno=30);
select * from emp where sal>(select min(sal) from emp where deptno=30);
--all:查詢工資比30部門所有員工高的員工信息
select * from emp where sal>all(select sal from emp where deptno=30);
select * from emp where sal>(select max(sal) from emp where deptno=30);
--(8)子查詢中的null值問(wèn)題（主要是多行子查詢中）
--查詢不是老板的員工
select * from emp where empno not in(select mgr from emp where mgr is not null);--(子查詢中不能有空值，否則就查不到任何數(shù)據(jù))
ps：? a not in (10,20,null)就等同于 a帅涂！=10 and a!=20 and a!=null;因?yàn)閍!=null永遠(yuǎn)為假议薪，所有條件就永遠(yuǎn)為假，就查不出任何結(jié)果
--四.示例
(1)分頁(yè)查詢顯示員工信息：顯示員工號(hào)媳友，姓名斯议，月薪(每頁(yè)顯示四條；顯示第二頁(yè)的員工醇锚；按照月薪降序排列)
--rownum偽列哼御，始終從1開(kāi)始坯临，無(wú)法使用大于(>)和大于等于(>=)
select rownum,r,empno,ename,sal from
(select rownum r,empno,ename,sal from(select rownum,empno,ename,sal from emp order by sal desc) e1 where rownum<=8) e2
where r>=5;
(2)員工表中薪水大于本部門平均薪水的員工
select empno,ename,sal,(select avg(sal) from emp where deptno=e.deptno) avgsal
from emp e
where sal>(select avg(sal) from emp where deptno=e.deptno);--相關(guān)子查詢--相比多表查詢更節(jié)省cpu資源
--------------------------------------------
select e.empno,e.ename,e.sal,d.avgsal
from emp e,(select deptno,avg(sal) avgsal from emp group by deptno) d
where e.deptno=d.deptno and e.sal>d.avgsal;--多表聯(lián)查
(3)按部門統(tǒng)計(jì)員工，分別查出總?cè)藬?shù)恋昼，及1980看靠，1981，1982液肌，1987年入職的員工
select count(*) total,--使用函數(shù)的方式
sum(decode(to_char(hiredate,'YYYY'),'1980',1,0)) "1980",--to_char轉(zhuǎn)換出入職日期中的年
sum(decode(to_char(hiredate,'YYYY'),'1981',1,0)) "1981",--并與四位年份比較
sum(decode(to_char(hiredate,'YYYY'),'1982',1,0)) "1982",--相同則返回1挟炬，不同返回0
sum(decode(to_char(hiredate,'YYYY'),'1987',1,0)) "1987"--sum算出1和0的總和，即為該年份入職的人數(shù)
from emp;
-------------------------------------------------
select --使用子查詢+偽表dual的方式
(select count(*) from emp ) total,
(select count(*) from emp where to_char(hiredate,'yyyy')='1980') "1980",
(select count(*) from emp where to_char(hiredate,'yyyy')='1981') "1981",
(select count(*) from emp where to_char(hiredate,'yyyy')='1982') "1982",
(select count(*) from emp where to_char(hiredate,'yyyy')='1987') "1987"
from dual;
