Oracle-多表查詢

內連線

select * from 表名 (別名) (inner) join 表名 (別名) on 連線條件

連線emp表和dep表（emp表中與dep表中的did是關聯欄位，這樣查詢did會出現2次）：

select * from emp,dep where emp.did = dep.did

相當於

select * from emp inner join dep on emp.did=dep.did

相當於

select * from emp join dep on emp.did=dep.did

如果只想出現一次關聯欄位，需要像下書寫：

select emp.*,dep.name,dep.daddress from emp join dep on emp.did=dep.did

給表指定別名

select * from 表名 別名 inner join 表名 別名 on 連線條件

-- 需要顯示的指明匹配條件，查詢結果兩個關聯列因為名稱可能不同，所以全部顯示

select * from dept d join emp e on d.deptno = e.deptno

注意：內連線的 inner 可以省略，直接 … join … on … 預設內連線；查詢指定欄位時，如果多張表有這個欄位，需要指明是那張表的欄位

外連線

select * from 表名 (別名) left/right (outer) join 表名  (別名)  where 連線條件

左外連線

以左表為參考，左表中資料全部輸出，右表與左表資料匹配的資料才顯示

select * from emp e left outer join dep d on e.did = d.did

相當於

select * from emp e,dep d where e.did = d.did(+)

以沒有（+）的一邊為參考（這裡是emp），沒有（+）的記錄全部輸出，有（+）的記錄，只有左右表相互匹配才輸出

右外連線

以右表為參考，右表資料全部輸出，右表與左表資料匹配的資料才顯示

select * from emp e right outer join dep d on e.did = d.did

相當於

select * from emp e,dep d where e.did(+) = d.did

以沒有（+）的一邊表為參考（這裡是dep），沒有（+）的記錄全部輸出，有（+）的記錄，只有左右表相互匹配後才輸出

select * from dep d right outer join emp e on e.did = d.did

注意：外連線的 outer 可以省略，直接 … left/right join … on …

子查詢

非相關子查詢 — 單行子查詢

子查詢結果只有一行，外查詢可以直接使用 = > < 運算子進行比較
子查詢可以獨立執行先進行子查詢再進行外查詢子查詢不依賴於外查詢

--查得比“CR7”工資高的員工的資訊

select * from emp where salary > (select salary from emp where ename = `CR7`)

--查詢工資高於平均工資的僱員名字和工資

select ename,salary from emp where salary > (select avg(salary) from emp)

--查詢職務和CR7相同，比CR7僱傭工資高的員工

---- 查詢職務

select ejob from emp where ename = `CR7`  -

---- 查詢工資

select esalary from emp where ename = `CR7`

---- 綜合起來

select * from emp

where job = (select ejob from emp where ename = `CR7`)

and hiredate > (select esalary from emp where ename = `CR7` )

非相關子查詢 — 多行子查詢

子查詢結果有多行

外查詢進行比較需要使用 all（所有），ang（任意），in，not in

--查詢比任意一個‘java開發’工資高的員工（查詢比‘java開發’最高工資低的員工）

select * from emp

where esalary < any(select esalary from emp where ejob=`java開發`)

--相當於

select * from emp

where esalary < (select max(esalary) from emp where ejob=`java開發`)

> < ang(...) 查詢結果是 “比查出來資料中最大的小”， > ang(...) 查詢結果是  “比查出來資料中最小的大”，任意一個用ang()

--查詢比任何一個‘java開發’工資高的員工（查詢比‘java開發’最低工資低的員工）

select * from emp

where esalary < all(select esalary from emp where ejob=`java開發`)

--相當於

select * from emp

where esalary < (select min(esalary) from emp where ejob=`java開發`)

>< all(...) 查詢結果是 “比查出來資料中最小的小” ，  >all(...) 查詢結果是“比查出來資料中最大的大”，所有的用all

總結

–子查詢 : 一個語句包含多個select

— — 不相關子查詢：子查詢不依賴外查詢，可以獨立執行，先內後外

— — 相關子查詢：子查詢依賴外查詢，不可以獨立執行，先外後內

— — — 單行子查詢：子查詢返回一行結果，可以直接使用 = > < 等

— — — 多行子查詢：子查詢返回多行結果，需要藉助 in ang all

偽列 rowid rownum

—-實際表附加的列，叫偽列，像表中的列一樣但是表中不儲存

—-偽劣只用來查詢，不能進行增刪改

rowid

—- 在表中每一行資料都有一個實體地址，rowid返回該行的實體地址

—- 作用：快速定位某一行，唯一的，自動分配的，不重複

select rowid,ename,ejob from emp

rownum

—-查詢的結果集，rownum為每一行記錄標識一個行號，第一行返回1，第二行返回2 ……

—-作用：限制查詢的行數，分頁

select rownum,ename,ejob,esalary from emp

查詢員工表中前五名員工的姓名，工作，工資

select rownum,ename,ejob,esalary from emp where rownum <=5

查詢員工表中工資最高的前五名員工的姓名，工作，工資

select rownum,ename,ejob,esalary from emp where rownum <=5 order by esalary desc

----先按工資排序

select rownum,ename,ejob,esalary from emp order by esalary desc

----再進行篩選

select rownum,r.*

from (select rownum,ename,ejob,esalary from emp order by esalary desc) r

where rownum <=5


--查詢員工表中第3條到第6條員工的姓名，工作，工資

select r.r,e.ename,e.ejob,e.esalary from emp e,(select rownum r,eid from emp) r

where e.eid = r.eid and r.r between 3 and 6


select * from (select rownum r,eid,ename,ejob,esalary from emp)

where r between 3 and 6

--相當於

select * from (select rownum r,eid,ename,ejob,esalary from emp where rownum <=6)

<font size=2>注意：where r >= 3 -- 子表中 rownum 必須用別名，因為外部查詢語句不能識別內部的 rownum ，但是可以識別內部 rownum 的別名