Oracle基礎（五）：多表查詢

Oracle基礎（五）：多表查詢

一、多表查詢

（一）簡單多表查詢

 

1、多表查詢的機制 1）SQL:

SELECT * FROM emp; --14條記錄
SELECT * FROM dept;--4條記錄
SELECT * FROM emp,dept;--顯示56條數據？？為什麼

2）分析：
先從 dept 選擇一條記錄（deptno=10），分別於 emp中的14條記錄分別匹配，顯示14條記錄。
再從 dept 選擇一條記錄（deptno=20），分別於 emp中的14條記錄分別匹配，顯示14條記錄。
依次類推。

2、笛卡爾集，
在多表查詢的時候，如果不帶任何條件，則會笛卡爾集。
避免：查詢的條件不能少於表個數減一。（2表，必須有1個條件） 3、多表查詢實例 1）顯示雇員名、雇員工資及所在部門的名稱

SELECT a.ename,a.sal,b.dname FROM emp a ,dept b WHERE a.deptno = b.deptno;

部門號匹配即可。 2）顯示部門號為10 的部門名、員工名和工資

SELECT a.ename,a.sal,b.dname FROM emp a ,dept b WHERE a.deptno = b.deptno and a.deptno=10;

3）顯示各個員工的姓名、工資，及其工資的級別

SELECT a.ename,a.sal,b.grade FROM emp a, salgrade b WHERE a.sal between b.losal and b.hisal;

4）顯示雇員名、雇員工資及所在部門的名字，並按部門排序

SELECT a.ename,a.sal,b.dname FROM emp a ,dept b WHERE a.deptno = b.deptno order by b.dname;

 

（二）自連接

1、含義：自連接是指在同一張表的連接查詢，即把一張表看成 2 張表。

 

2、實例 1）顯示員工Ford的上級領導的姓名

SELECT a.* FROM emp a WHERE a.empno = (SELECT a.mgr FROM emp a WHERE a.ename = 'FORD') ;

分析：

 

 

先查詢出FORD 的領導的員工編號。
然後在根據員工編號，查詢其信息
單個可以使用子查詢解決，使用容易出錯：【單行子查詢返回多個行】 2）顯示各員工的姓名和他的上級領導姓名

 

 

SELECT distinct a.ename,b.ename,b.* FROM emp a, emp b WHERE a.mgr = b.empno;--13條數據
SELECT distinct a.ename,b.ename,b.* FROM emp a, emp b WHERE a.mgr = b.empno(+);--14條數據

 

【+】寫在右邊，表明是左外連接，左邊的表列全部顯示。
分析：
把 emp 一張表看成2張表，然後雇員表的 mgr 等於領導表的 empno。
【King】沒有上級，不會顯示。要求顯示時，需要使用外連接。
使用自連接解決問題。

 

（三）子查詢*

 

1、含義： 1）子查詢是指嵌入其他sql語句中的 select語句，也叫嵌套查詢 2）單行子查詢：單行子查詢是指只返回一行數據的子查詢語句。 3）多行子查詢：多行子查詢指返回多行數據的子查詢。
2、實例 1）顯示與SMITH同一部門的所有員工（包括SMITH）（單行子查詢：“=”）

 

 

SELECT a.* FROM emp a WHERE a.deptno = (SELECT a.deptno FROM emp a WHERE a.ename='SMITH' );

 

分析
SELECT a.deptno FROM emp a WHERE a.ename='SMITH' ，返回一行記錄，即單行子查詢。
SQL解析默認從右往左，即先解析子查詢語句。

注意：where 子查詢中,不能使用order by 排序。
SELECT a.* FROM emp a WHERE a.deptno = (SELECT a.deptno FROM emp a WHERE a.ename='SMITH' order by a.empno);
報錯，“缺失右括號” 2）查詢和10號部門的工作崗位相同的雇員的名字、崗位、工資、部門號 （多行子查詢：“in”）

SELECT a.ename,a.job,a.sal,a.deptno FROM emp a WHERE a.job in (SELECT distinct a.job FROM emp a WHERE a.deptno = 10) ;

分析：
先查詢出10號部門的所有工作崗位，返回多行結果，為多行子查詢。
顯示和他的崗位有一個相同的員工。
使用容易出錯：【單行子查詢返回多個行】 3、all 和 any 操作符 1）顯示工資比30號部門的所有員工的工資高的員工的姓名、工資和部門號
方法1：使用max()聚合函數

SELECT a.* FROM emp a WHERE a.sal > (SELECT max(a.sal) FROM emp a WHERE a.deptno=30);

方法2：使用 all

SELECT a.* FROM emp a WHERE a.sal >all (SELECT a.sal FROM emp a WHERE a.deptno=30);

2）顯示工資比30號部門的任意一個員工的工資高的員工姓名、工資和部門號，不是任何一個都高
方法1：使用min()聚合函數

SELECT a.* FROM emp a WHERE a.sal > (SELECT min(a.sal) FROM emp a WHERE a.deptno=30);

方法2：使用 any

SELECT a.* FROM emp a WHERE a.sal >any (SELECT a.sal FROM emp a WHERE a.deptno=30);

4、多列子查詢 1）差別
單行子查詢：單行單列
多行子查詢：多行單列
多列子查詢：多列 2）實例：查詢與SMITH 的部門和崗位完全相同的所有雇員

SELECT a.* FROM emp a WHERE (a.deptno,a.job) = (SELECT a.deptno,a.job FROM emp a WHERE a.ename='SMITH' );
SELECT a.* FROM emp a WHERE (a.deptno,a.job) in (SELECT a.deptno,a.job FROM emp a WHERE a.ename='SMITH' );

這邊“=”與 in 效果一致。
5、在 From 子句中使用子查詢 1）顯示高於自己部門平均工資的員工的信息

SELECT a.ename,a.sal,b.avg_sal,a.deptno FROM emp a ,(SELECT avg(a.sal) as avg_sal,a.deptno as deptno FROM emp a group by a.deptno) b WHERE a.deptno = b.deptno and a.sal > b.avg_sal;

分析：
先要知道各個部門的平均工資是多少，然後作為一個臨時表
利用emp 與臨時表多表查詢 2）查找每個部門工資最高的人的詳細資料

方法1：

SELECT a.ename,a.sal,t.max_sal , a.deptno FROM emp a, (SELECT max(a.sal) as max_sal,a.deptno as deptno FROM emp a group by a.deptno) t WHERE a.deptno = t.deptno and a.sal = t.max_sal;

得到各個部門的最高工資，作為一個臨時表
利用emp 與臨時表多表查詢
方法2：

SELECT a.* FROM emp a WHERE a.sal = (SELECT max(b.sal) FROM emp b WHERE b.deptno = a.deptno ) ;

這種方法，無法顯示子查詢中的列數據，有缺陷。 3）顯示每個部門的信息和人員數量

SELECT a.*,t.count_empno FROM dept a, (SELECT count(a.empno) as count_empno,a.deptno FROM emp a group by a.deptno) t WHERE a.deptno = t.deptno(+);

先獲取每個部門的人員數量，作為一個臨時表
利用dept 表與臨時表多表查詢
+：表示外連接 6、小技巧
1）行遷移

 

 

create table temp# as select empno, ename from emp where ename like 'J%';

 

2）蠕蟲復制

insert into myTest (empno,ename,job) select empno,ename,job from myTest;

3）子查詢更新數據

update emp  set (empno,ename,job) = (select  empno,ename,job from emp where ename = 'SMITH')  where ename='SCOTT';

（四）合並查詢

 

1、UNION：並集去重，排序 1）解釋：該操作符用於取得兩個結果集的並集。當使用該操作符時，會自動去掉結果集中重復行。

SELECT a.* FROM emp a WHERE a.sal> 2500 UNION SELECT b.* FROM emp b WHERE b.job='MANAGER';

2、UNION ALL：並集不去重，並且不排序

 

 

SELECT a.* FROM emp a WHERE a.sal> 2500 UNION ALL SELECT b.* FROM emp b WHERE b.job='MANAGER'; 

3、INTERSECT：交集

 

 

4、MINUS：差集，總之不會顯示第二個集合中的數據 1）有交集的情況：

 

2）沒有交集的情況：

二、分頁

（一）其他數據庫的分頁

1、MySQL
1）語法：

 

select * from 表名 where 條件 limit 從第幾條，取幾條
select * from emp  where empno=1234 limit 1，3;

 

2、Sql server 1）語法：

select top 2 * from 表名 where id not in (select top 4 id from 表名 where 條件)
排除前4條，再取2條，實際上是取5-6條。

（二）Oracle：
1、語法：rownum

 

SELECT rownum, emp.* FROM emp where rownum <6；
SELECT * FROM (SELECT t.*, rownum num FROM (SELECT * FROM emp) t WHERE rownum < 10) t2 WHERE t2.num > 4；

2、分析：

 

 

分析：rownum 不能直接使用大於號（>）
使用三層嵌套，其實就是在from中使用子查詢。
rownum三層嵌套，分別在第二層和第三層中使用效率最高。
第一層：SELECT * FROM emp
第二層：SELECT t.*, rownum num FROM (SELECT * FROM emp) t WHERE rownum < 10
第三層：SELECT * FROM (SELECT t.*, rownum num FROM (SELECT * FROM emp) t WHERE rownum < 10) t2 WHERE t2.num > 4； 3、實例 1）按照入職時間的先後順序，查詢從第 7 到第10 個。

SELECT * FROM (SELECT t.*, rownum num FROM (SELECT a.* FROM emp a order by a.hiredate ) t WHERE rownum < 10) t2 WHERE t2.num > 7；

排序等特殊處理等情況，請在最內層處理 2）測試，模擬10萬的數據

 

 

創建表與數據：
以存在的表為模板創建新表：(行遷移)

create table myTest as select empno,ename,job from emp;

自我復制（蠕蟲復制）

insert into myTest (empno,ename,job) select empno,ename,job from myTest;

測試：

SELECT * FROM (SELECT t.*, rownum num FROM (SELECT * FROM myTest ) t WHERE rownum < 51010) t2 WHERE t2.num > 50997;

 

三、內、外連接

 

（一）內連接 1、概述：內連接就是利用where 子句對2張表形成的笛卡爾集進行篩選。 2、格式 1）普通：

 

 

語法：select 列名... from 表1 表2.. where 條件

SELECT a.ename,a.deptno,b.dname FROM emp a,dept b WHERE a.deptno = b.deptno;

2）【inner】 Join on
語法：select 列名... from 表1 inner Join 表2 on （條件）

SELECT a.ename,a.deptno,b.dname FROM emp a inner join dept b on (a.deptno = b.deptno) 

3、內連接的特點：
只有2張表都匹配上的記錄，才會顯示。 （二）外連接 left|right|full【outer】join on 1、創建2 新表 :

 

 

2、左外連 1）特點：left join
左表全部顯示
右表匹配顯示 2）顯示所有人的成績，如果沒有成績，也要顯示該人的姓名和id號，成績顯示為空

 

 

SQL：

內連接：

SELECT a.id,a.name,b.grade FROM stu a,exam b WHERE a.id = b.id; 

只顯示2條數據，沒有成績的學生不會顯示
外連接：

SELECT a.id,a.name,b.grade FROM stu a,exam b WHERE a.id = b.id(+); 
SELECT a.id,a.name,b.grade FROM stu a left join exam b on( a.id = b.id);

分析： 內連接的時候，需要2張表都匹配上的記錄 左外連接：stu（左表） 表中與exam 中沒有匹配的，也需要顯示。


3、右外連 1）特點：right join
左表匹配顯示
右表全部顯示 2）顯示左右成績，如果沒有名字匹配，顯示為空

 

 

SQL：

SELECT a.id,a.name,b.grade FROM stu a,exam b WHERE a.id(+) = b.id; 
SELECT a.id,a.name,b.grade FROM stu a right join exam b on( a.id = b.id); 

分析：
右外連接：exam（右表） 表與 stu 表中沒有匹配的，也需要顯示。

 

 

4、完全外連 1）特點： full outer join
左表全部顯示
右表全部顯示 2）顯示所有成績和所有人的名字，如果沒有相應的匹配值，則顯示為空

 

 

SQL：

SELECT a.id,a.name,b.grade FROM stu a full join exam b on( a.id = b.id); 
SELECT a.id,a.name,b.grade FROM stu a full outer join exam b on( a.id = b.id); 

分析：
右外連接：exam（右表） 表與 stu 表中沒有匹配的，也需要顯示。

 

 

5、總結： 1）左、右外連可以相互轉換，只需要換表的位置即可。

SELECT a.id,a.name,b.grade FROM stu a right join exam b on(a.id = b.id); 
SELECT a.id,a.name,b.grade FROM exam b left join stu a on( a.id = b.id); 

2）小結

6、練習：

1）列出部門名稱和這些部門員工的員工信息，同時列出那些沒有員工的部門

 

SELECT b.dname,a.* FROM emp a right join dept b on (a.deptno = b.deptno) order by b.deptno;
SELECT b.dname,a.* FROM dept b left join emp a on (a.deptno = b.deptno) order by b.deptno;
SELECT b.dname,a.* FROM emp a, dept b WHERE b.deptno = a.deptno(+) order by b.deptno;
SELECT b.dname,a.* FROM emp a, dept b WHERE a.deptno(+) = b.deptno order by b.deptno;

 

 

四、練習

 

1、顯示雇員總數，以及獲得補助的雇員數

SELECT count(*) ,count(comm) FROM emp;

2、顯示管理者的人數

SELECT count(distinct mgr) FROM emp;

3、顯示每個部門每個崗位的平均工資、每個部門的平均工資、每個崗位的平均工資。

SELECT avg(a.sal),a.deptno,a.job FROM emp a GROUP BY cube(a.deptno,a.job );

cube：立方體函數，對deptno分組，job分組，對（deptno、job）分組