Oracle基礎（五）：多表查詢，oracle基礎資料表查詢

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 server1）文法：

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、顯示僱員總數，以及獲得補助的僱員數

<span style="font-family:Arial;font-size:18px;">SELECT count(*) ,count(comm) FROM emp;</span>

2、顯示管理者的人數

SELECT count(distinct mgr) FROM emp;

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

<span style="font-family:Arial;font-size:18px;">SELECT avg(a.sal),a.deptno,a.job FROM emp a GROUP BY cube(a.deptno,a.job );</span>

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