4多表關(guān)聯(lián)查詢

4.1表的別名

①在多表關(guān)聯(lián)查詢時(shí)，如果多個表之間存在同名的列，則必須用表名限定列的引用如dept.deptno,emp.deptno
②為使語句簡潔，使用表別名，表別名在from子句中定義如 emp e
③表別名一經(jīng)定義，在整個查詢語句中就只能用表別名，而不能用表名 emp.ename × e.ename √
④表別名只在所定義的查詢語句中有效

4.2內(nèi)連接（直連-只顯示關(guān)聯(lián)到的結(jié)果，會遺失部分?jǐn)?shù)據(jù)）

####
select d.dormno, s.name
from dorm d
[inner]join student s--默認(rèn)是inner內(nèi)查詢，不用寫
on d.id=s.dormid;
--用相同的ID來進(jìn)行表之間連接####
select b.buildname,d.dormno,s.namefrom building bjoin dorm don b.id = d.buildidjoin student son d.id = s.dormid;
--三個及以上連接####
select d.dormno, s.namefrom dorm djoin student son d.id = s.dormidwhere s.gender = ;--條件語句放在后面####
內(nèi)連接的另一種寫法（了解即可，千萬別用）
select e.ename,d.dname,e.salfrom emp e,dept d--省去了join on 語句where sal>=3000;--忘記寫where子句會導(dǎo)致笛卡爾積任意關(guān)聯(lián)，會導(dǎo)致非常嚴(yán)重的數(shù)據(jù)庫崩潰--笛卡爾積：兩個表做任意關(guān)聯(lián)，無關(guān)聯(lián)條件，導(dǎo)致數(shù)據(jù)暴增

4.3外連接（可以返回不相關(guān)的數(shù)據(jù)行，不必須有條件語句）

（1）左連接（以左邊的為主表，顯示所有行的結(jié)果，以右邊的為從表，只顯示關(guān)聯(lián)的結(jié)果）

select e.ename,d.dname,e.comm
from dept d
left join emp e
on e.deptno=d.deptno;--此處應(yīng)該在建表時(shí)用id而不用no####
select s.name,d.dormno 
from student s, dorm d 
where s.dormid=d.id(+);
--左連接另種寫法，僅作了解，千萬別用，會造成笛卡爾積數(shù)據(jù)庫崩潰或數(shù)據(jù)丟失
####
▲如果左連接的從表添加where條件，則左連接失效
例：查詢所有人的姓名，如果其他宿舍在2樓，顯示宿舍號
select s.name,d.dormno
from student s
left join dorm d
on d.id=s.dormid
where d.floor=2;--此處where應(yīng)改為and，把選擇條件變成關(guān)聯(lián)條件

（2）右連接

right用法和左連接一樣

（3）全連接（特殊情況才用，一般不用，顯示左右全部數(shù)據(jù)，如果關(guān)聯(lián)則在同一行）

full同上

4.4自然連接(幾乎不用)

自然連接指在檢索多個表時(shí)
oracle會將個表中的列和第二個表中具有相同名稱的列進(jìn)行自動連接
語法： natural join
select empno,ename,job,dname
from emp 
natural join dept
where sal>2000;
--由于自然連接要求表之間必須有相同名稱的列，這樣容易在出現(xiàn)不可預(yù)知的錯，故幾乎不用

4.5自連接（一表兩用）

####
select em2.ename 上層管理者,em1.ename 下屬員工
from emp em1
join emp em2
on em1.mgr=em2.empno--關(guān)鍵在于找到對應(yīng)關(guān)系
order by em1.mgr;####
如果左連接后使用自連接，則自連接會失效
例：
select d.dormno,s.name,s1.name
from student s
left join dorm d
on d.id=s.dormid
left join student s1
on d.headno=s1.stuno;
--僅作語法示例

4.6交叉連接(幾乎不用，會造成笛卡爾積)

語法： cross join
select count(*) --計(jì)算查詢結(jié)果的行數(shù)
from dept
cross join emp;
--執(zhí)行結(jié)果是一個笛卡爾積等同于select count(*)
from dept,emp;
--執(zhí)行結(jié)果是一個笛卡爾積

5子查詢

又稱嵌套查詢在執(zhí)行數(shù)據(jù)操作時(shí)
某個操作要依賴另一個select語句的查詢結(jié)果
可以select語句嵌入該語句中注意：
①子查詢子句必須用括號括起來
②子查詢不能包括order by
③子查詢允許多層（不超過255行）嵌套
④子查詢比多表關(guān)聯(lián)查詢更靈活、功能更強(qiáng)大、更容易理解，但效率更低

5.1單行子查詢

指子查詢語句的返回結(jié)果只有一行
當(dāng)在where子句中引用單行子查詢時(shí)可以用單行比較運(yùn)算符（= > < >= <= <>）①關(guān)聯(lián)子查詢在select語句（查詢結(jié)果中）
select s.name,s.gender,s.groupno,(select avg(t.age)from student t)--此處的平均年齡只作為常量，不是聚合函數(shù)
from student s;
--查詢每個人的姓名、年齡和班級平均年齡②關(guān)聯(lián)子查詢在條件語句中
select empno, ename, sal--外查詢from empwhere sal > (select min(sal) from emp)--括號內(nèi)是內(nèi)查詢and sal < (select max(sal) from emp);
--查詢emp表中不是高或低工資的員工編號、姓名、工資

5.2多行子查詢

指子查詢語句的返回結(jié)果不止一行
當(dāng)在where子句中引用多行子查詢時(shí)必須用多行比較符（in any all）

（1.1）in運(yùn)算符

在多行子查詢中使用in運(yùn)算符時(shí)
外查詢會嘗試與子查詢結(jié)果中任何一個結(jié)果進(jìn)行匹配
只要有一個匹配成功則外查詢會返回當(dāng)前的檢索記錄
select empno,ename,job
from emp
where deptno in
(select deptno from dept where dname<>'SALES');
--查詢emp表中不是銷售部門的員工信息

（1.2）exists運(yùn)算符

exists存在（前面可以加not）--查詢有預(yù)備黨員的小組
select s.groupnofrom student swhere s.groupno in select t.groupnofrom student twhere t.political = '預(yù)備黨員');
select distinct s.groupnofrom student swhere exists (select t.groupnofrom student twhere t.political = '預(yù)備黨員'and s.groupno = t.groupno);
--查詢沒有預(yù)備黨員的小組
select distinct s.groupnofrom student swhere not exists (select t.groupnofrom student twhere t.political = '預(yù)備黨員'and s.groupno = t.groupno);工作中常用 exists代替 in（速度更快）in關(guān)聯(lián)子查詢先子查詢?nèi)吭僦鞑樵?#xff08;速度慢，但容易想）exists：關(guān)聯(lián)子查詢先主查詢一條一條查詢再子查詢（子查詢不需要查詢?nèi)?#xff0c;速度更快）
①將原 SQL中 in改為 exists
②在子查詢中添加條件（原子查詢的結(jié)果=原主查詢中 in的前面一致）

（2）any運(yùn)算符

any運(yùn)算符必須與單行操作符結(jié)合使用
并且返回行只要匹配子查詢的任何一個結(jié)果即可
select deptno,ename,sal
from emp
where 
sal>any (select sal from emp where deptno=30)
and deptno<>30;--and與sal語句是并列的
--查詢emp表中工資大于30號部門的任意一個員工工資的其他部門的員工信息
--實(shí)質(zhì)上查詢emp表中工資大于30號部門的低的一個員工工資的其他部門的員工信息

（3）all運(yùn)算符

all運(yùn)算符必須與單行操作符結(jié)合使用
并且返回行必須匹配子查詢的所有結(jié)果
select deptno,ename,sal
from emp
where 
sal>all (select sal from emp where deptno=30);
----查詢emp表中工資大于30號部門的所有員工工資的其他部門的員工信息
--與any相比較

5.3多列子查詢

select *
from student s 
where (s.age,s.gender)=(select t.age,t.gender from student t where name='張簡簡');
--查詢年齡和性別都和張簡簡相同的人
--注意s.age,s.gender和t.age,t.gender前后順序要一致

5.4關(guān)聯(lián)子查詢

在單行或多行子查詢中內(nèi)查詢和外查詢是分開執(zhí)行的
外查詢僅僅使用內(nèi)查詢的終結(jié)果在一些特殊需求的子查詢中內(nèi)查詢和外查詢相互關(guān)聯(lián)
被稱為關(guān)聯(lián)子查詢①關(guān)聯(lián)子查詢在select語句（查詢結(jié)果中）
select s.name,s.gender,s.groupno,(select avg(t.age)from student t where t.groupno=s.groupno)
from student s;
--使用了兩個表別名
--不需要使用分組函數(shù)
--例 select avg(t.age) from student t where t.groupno*=1即可查詢1組的平均年齡②關(guān)聯(lián)子查詢在條件語句中
select s.name,s.age
from student s
where s.age>(select avg(t.age) from student t where t.groupno=s.groupno);
--查詢比小組平均年齡大的人

5.5子查詢難點(diǎn)用法

--查詢A型血人數(shù)比B型血人數(shù)多的宿舍
select ssaxx.dormno
from (select d.dormno,count(1) ssaxxrs from student s join dorm d on s.dormid=d.id where s.xo='A' group by d.dormno) ssaxx--把A血型的寢室和人數(shù)看作一個表
join (select d.dormno,count(1) ssbxxrs from student s join dorm d on s.dormid=d.id where s.xo='B' group by d.dormno) ssbxx--把B血型的寢室和人數(shù)看作一個表
on ssaxx.dormno=ssbxx.dormno
where ssaxx.ssaxxrs>ssbxx.ssbxxrs;
--當(dāng)問題不清晰的時(shí)候拆解成多個表來解決問題即可

6 開窗函數(shù)

也稱分析函數(shù)窗，就是范圍在over子句所限定的范圍內(nèi)進(jìn)行查詢，速度優(yōu)于子查詢。

6.1 partition by

select s.name,s.groupno,s.age,max(s.age) over(partition by s.groupno) age1
from student s;
--查詢所有同學(xué)姓名、組號、年齡和其最大年齡
--partition by 類似于 group by具有分組的作用
--【max(s.age) over(partition by s.groupno) age1】是一個整體使用多個聚合函數(shù)時(shí)每個要單獨(dú)加over語句
--由于受開窗范圍的影響別名（此處為age1）要放在 order by后

6.2 order by

select s.name,s.groupno,s.age,max(s.age)over(partition by s.groupno order by s.age)
from student s;
--查詢所有同學(xué)姓名、組號年齡和窗口內(nèi)大年齡
--order by不僅僅具有排序的作用只有 order by 沒有 partition by 則僅有排序功能 會使窗口發(fā)生變化窗口變化為從 partition by選定的窗口的行數(shù)據(jù)開始到與被查詢主體 order by后的列名（此處為s.age）相同值的所有數(shù)據(jù)行為止（不明白就運(yùn)行代碼試試）

6.3 排序類開窗函數(shù) row_number()、 rank()、dense_rank()?

在窗口范圍內(nèi)對 order by 后指定的數(shù)據(jù)進(jìn)行排序select s.name,s.groupno,s.age,row_number() over(partition by s.groupno order by s.age)
from student s;
--相同年齡也分先后順序如：排序 1  2  3  4  5年齡 18 19 19 19 20
select s.name,s.groupno,s.age,rank() over(partition by s.groupno order by s.age)
from student s;
--并列第二然后第五如：排序 1  2  2  2  5年齡 18 19 19 19 20
select s.name,s.groupno,s.age,dense_rank() over(partition by s.groupno order by s.age)
from student s;
--并列第二然后第三如：排序 1  2  2  2  3年齡 18 19 19 19 20

6.4 偏移類開窗函數(shù) lag() lead()

select s.name,s.groupno,s.age,lag(s.age) over(partition by s.groupno order by s.age)
from student s;
--lad(參數(shù)1，參數(shù)2，參數(shù)3)參數(shù)1（列名）所處位置的數(shù)據(jù)往上偏移參數(shù)2個位置如果偏移后數(shù)據(jù)為空則用參數(shù)3的數(shù)據(jù)填充
--lead(參數(shù)1，參數(shù)2，參數(shù)3)往下偏移

6.5 了解部分

（1）first_value() last_value()

--不可和聚合函數(shù)同用
select s.name,s.groupno,s.age,first_value(s.age) over(partition by s.groupno order by s.age),last_value(s.name) over(partition by s.groupno order by s.age)
from student s;    
--注意要寫逗號
--first_value(col_name)返回該窗口中某列的個值
--last_value(col_name)返回該窗口中某列的后一個值

（2）影響開窗范圍的參數(shù)(range between 參數(shù)1 and 參數(shù)2)（可以有聚合函數(shù)，必須有order by）

range between 參數(shù)1 and 參數(shù)2
在原來的窗口范圍內(nèi)再進(jìn)行選定select q.realname,q.groupno,q.age,max(q.age) over(partition by q.groupno order by q.agerange between 參數(shù)1 and 參數(shù)2) 
from qqinfo q;參數(shù)可以替換為以下：
unbounded preceding 組內(nèi)首行
current row 當(dāng)前行
unbounded following 組內(nèi)末行
1 preceding 組內(nèi)當(dāng)前行前面1行
1 following 組內(nèi)當(dāng)前行后面1行
range 值比較--不了解
rows 行比較--不了解

7 其他

7.1 查詢中的集合操作

兩個集合間 交 并 差
（1）并 union （自帶去重效果）（去重必帶排序）（oracle中去重和排序都非常慢）union all （不去除重復(fù)內(nèi)容）（執(zhí)行速度更快）
例：select s.name from student sunionselect e.name from emp e
此外要注意多列同時(shí)運(yùn)算的情況：對應(yīng)列如s.age和e.age的屬性和類型要一致select s.name，s.age from student sunionselect e.name,e.age from emp e
（2）交（ intersect ）
（3）差 （ minus ）

7.2 case when

查詢結(jié)果根據(jù)類別不同，查詢方式隨之不同
select s.name，--逗號不能少case--開始when s.gender=1 then '男生'when s.gender= then '女生'--也可以只有一個 when then語句 else '其他'end--結(jié)束
from student s;需要注意的是 case when 是順序執(zhí)行的
如果前面的條件包含了后面的條件
則后面的條件
如：select s.name,casewhen s.score>60 then '及格'when s.score>80 then ''else '不及格'endfrom student s;--則60分以上的都是及格，即使是100也是及格而不是當(dāng) case when 中的條件是確定值（即用等號=）時(shí)可等價(jià)于decode如：select s.name,--逗號不能少decode(s.gendere,1,'男生',,'女生','其他')from student s;

7.3 行列相互轉(zhuǎn)換

（1）行轉(zhuǎn)列【常面】

將同一列內(nèi)容分成多列
① group by
② case when
③ 聚合函數(shù)例（查詢班內(nèi)全部人數(shù)，男生數(shù)量，女生數(shù)量）：select count(1),count(case when s.gender=1 then '是' end) nans,count(case when s.gender= then '是' end) nvsfrom student s;
例（查詢各組全部人數(shù)，男生數(shù)量，女生數(shù)量）：select s.groupno，count(1),count(case when s.gender=1 then '是' end) nans,count(case when s.gender= then '是' end) nvsfrom student sgroup by s.groupno;

（2）列轉(zhuǎn)行【常面】

將不同列的內(nèi)容匯總到同一列
用 union[all]（并-操作）select name,'男' gender from stul where nans=1
union
select name,'女' from stu1 where nvs=1;