章節(jié)目標(biāo)
通過本章學(xué)習(xí)，應(yīng)達(dá)到如下目標(biāo)：
掌握子查詢可以解決的問題髓帽；
了解子查詢的分類菠赚；
掌握單行子查詢、多行子查詢郑藏、多列子查詢衡查；
掌握在WHERE、HAVING必盖、FROM子句中編寫子查詢拌牲；
理解子查詢返回空值對主查詢所產(chǎn)生的影響；
掌握T0P-N及分頁查詢;
本章內(nèi)容
為什么使用子查詢
思考如下問題歌粥？
查詢工資比Jones工資高的員工信息塌忽？
查詢工資最低的員工姓名？
為什么使用子查詢
“誰的薪水比 Jones還高呢失驶？”
使用子查詢
SELECT select_list
FROM table
WHERE expr operator
(SELECT select_list
FROM table);
括號(hào)內(nèi)的查詢叫做子查詢土居，也叫內(nèi)部查詢，先于主查詢執(zhí)行嬉探。
子查詢的結(jié)果被主查詢（外部查詢）使用
expr operator包括比較運(yùn)算符擦耀。
單行運(yùn)算符：>、=涩堤、>=眷蜓、<、<>胎围、<=
多行運(yùn)算符： IN账磺、ANY、ALL
使用子查詢
子查詢可以嵌于以下SQL子句中：
WHERE子句
HAVING子句
FROM子句
使用子查詢
使用子查詢
查詢出比JONES的雇員工資高的其他雇員
SQL> SELECT ename
2 FROM emp
3 WHERE sal >
4 (SELECT sal
5 FROM emp
6 WHERE ename='JONES');
ENAME
----------
KING
FORD
SCOTT
使用子查詢
子查詢使用指導(dǎo)
子查詢要用括號(hào)括起來
將子查詢放在比較運(yùn)算符的右邊
對于單行子查詢要使用單行運(yùn)算符
對于多行子查詢要使用多行運(yùn)算符
子查詢的類型
根據(jù)子查詢返回的行和列數(shù)量痊远，分為：
單行子查詢
子查詢只返回一行一列
使用單行運(yùn)算符
顯示和雇員7369從事相同工作并且工資大于雇員7876的雇員的姓名和工作垮抗。
子查詢中使用組函數(shù)
查詢工資最低的員工姓名，崗位及工資
HAVING子句中使用子查詢
查詢部門最低工資比20部門最低工資高的部門編號(hào)及最低工資
HAVING子句中使用子查詢
查詢哪個(gè)部門的員工人數(shù) 高于各部門平均人數(shù)碧聪。
SQL> SELECT deptno, COUNT(empno)
2 FROM emp
3 GROUP BY deptno
4 HAVING COUNT(empno) >
5 (SELECT AVG(COUNT(empno))
6 FROM emp
7 GROUP BY deptno);
這個(gè)語句錯(cuò)在哪冒版？
這條語句會(huì)工作嗎?
練習(xí)1
查詢?nèi)肼毴掌谧钤绲膯T工姓名，入職日期
查詢工資比SMITH工資高并且工作地點(diǎn)在CHICAGO的員工姓名逞姿，工資辞嗡，部門名稱
查詢?nèi)肼毴掌诒?0部門入職日期最早的員工還要早的員工姓名捆等，入職日期
查詢部門人數(shù)大于所有部門平均人數(shù)的的部門編號(hào)，部門名稱续室，部門人數(shù)
多行子查詢
多行子查詢
子查詢返回記錄的條數(shù) 可以是一條或多條栋烤。
和多行子查詢進(jìn)行比較時(shí)，需要使用多行操作符挺狰，多行操作符包括：
IN
ANY
ALL
IN操作符和以前介紹的功能一致明郭，判斷是否與子查詢的任意一個(gè)返回值相同。
IN使用
SELECT empno,ename
FROM emp
WHERE sal =
(SELECT MIN(sal)
FROM emp
GROUP BY deptno);
返回結(jié)果
ERROR at line 4:
ORA-01427: single-row subquery returns more than?one row
查詢是經(jīng)理的員工姓名丰泊，工資
SELECT ename, sal
FROM emp
WHERE empno IN (SELECT mgr
FROM emp);
ANY的使用
ANY：表示和子查詢的任意一行結(jié)果進(jìn)行比較薯定，有一個(gè)滿足條件即可。
< ANY：表示小于子查詢結(jié)果集中的任意一個(gè)瞳购，即小于最大值就可以话侄。
> ANY：表示大于子查詢結(jié)果集中的任意一個(gè)，即大于最小值就可以学赛。
= ANY：表示等于子查詢結(jié)果中的任意一個(gè)年堆，即等于誰都可以，相當(dāng)于IN盏浇。
查詢是經(jīng)理的員工姓名变丧，工資。
SELECT ename, sal
FROM emp
WHERE empno = ANY (SELECT mgr
FROM emp);
查詢部門編號(hào)不為10缠捌，且工資比10部門任意一名員工工資高的員工編號(hào)锄贷，姓名，職位曼月，工資谊却。
SELECT empno, ename, job, sal
FROM emp
WHERE sal > ANY (SELECT sal
FROM emp
WHERE deptno = 10)
AND deptno <> 10;
查詢部門編號(hào)不為10，且工資比10部門任意一名工資低的員工編號(hào)哑芹，姓名炎辨，職位，工資聪姿。
SELECT empno, ename, job, sal
FROM emp
WHERE sal < ANY (SELECT sal
FROM emp
WHERE deptno = 10)
AND deptno <> 10;
ALL的使用
ALL：表示和子查詢的所有行結(jié)果進(jìn)行比較碴萧，每一行必須都滿足條件。
< ALL:表示小于子查詢結(jié)果集中的所有行末购，即小于最小值破喻。
> ALL:表示大于子查詢結(jié)果集中的所有行，即大于最大值盟榴。
= ALL :表示等于子查詢結(jié)果集中的所有行,即等于所有值曹质，通常無意義。
查詢部門編號(hào)不為10，且工資比10部門所有員工工資高的員工編號(hào)羽德，姓名几莽，職位，工資宅静。
SELECT empno, ename,job, sal
FROM emp
WHERE sal > ALL (SELECT sal
FROM emp
WHERE deptno= 10)
AND deptno <> 10;
查詢部門編號(hào)不為10章蚣，且工資比10部門所有員工工資低的員工編號(hào)，姓名姨夹，職位纤垂，工資。
SELECT empno, ename,job, sal
FROM emp
WHERE sal < ALL (SELECT sal
FROM emp
WHERE deptno= 10)
AND deptno <> 10;
查詢部門編號(hào)不為10匀伏，且工資和10部門所有員工工資相等的員工編號(hào)洒忧，姓名蝴韭，職位够颠，工資。
SELECT empno, ename,job, sal
FROM emp
WHERE sal = ALL (SELECT sal
FROM emp
WHERE deptno= 10)
AND deptno <> 10;
練習(xí)2
- 查詢?nèi)肼毴掌诒?0部門任意一個(gè)員工晚的員工姓名榄鉴、入職日期履磨，不包括10部門員工
- 查詢?nèi)肼毴掌诒?0部門所有員工晚的員工姓名、入職日期庆尘，不包括10部門員工
- 查詢職位和10部門任意一個(gè)員工職位相同的員工姓名剃诅，職位，不包括10部門員工
多列子查詢
多列子查詢
之前講的子查詢都是在一個(gè)條件表達(dá)式內(nèi)和子查詢的一個(gè)列進(jìn)行比較驶忌，多列子查詢可以在一個(gè)條件表達(dá)式內(nèi)同時(shí)和子查詢的多個(gè)列進(jìn)行比較矛辕。
多列子查詢通常用IN操作符完成。
查詢出和1981年入職的任意一個(gè)員工的部門和職位完全相同員工姓名付魔、部門聊品、職位、入職日期,不包括1981年入職員工几苍。
SQL> SELECT ename, deptno, job, hiredate
2 FROM emp
3 WHERE (deptno, job) IN
4 (SELECT deptno,job
5 FROM emp
6 WHERE to_char(hiredate,'YYYY')='1981')
7 AND to_char(hiredate,'YYYY')<>'1981';
查詢出和1981年入職的任意一個(gè)員工的部門或職位相同員工姓名翻屈、部門、職位妻坝、入職日期,不包括1981年入職員工伸眶。
SQL> SELECT ename, deptno, job, hiredate
2 FROM emp
3 WHERE (deptno IN (SELECT deptno
4 FROM emp
5 WHERE to_char(hiredate,'YYYY')='1981')
6 OR job IN (SELECT job
7 FROM emp
8 WHERE to_char(hiredate,'YYYY')='1981'))
9 AND to_char(hiredate,'YYYY')<>'1981');
練習(xí)3
查詢職位及經(jīng)理和10部門任意一個(gè)員工職位及經(jīng)理相同的員工姓名，職位刽宪，不包括10部門員工
查詢職位及經(jīng)理和10部門任意一個(gè)員工職位或經(jīng)理相同的員工姓名厘贼，職位，不包括10部門員工
子查詢中的空值
查詢不是經(jīng)理的員工姓名圣拄。
SQL> SELECT ename
2 FROM emp
3 WHERE empno NOT IN
4 (SELECT mgr
5 FROM emp);
no rows selected.
子查詢返回的結(jié)果中含有空值
上面的SQL語句試圖查找出沒有下屬的雇員嘴秸，邏輯上，這個(gè)SQL語句應(yīng)該會(huì)返回8條記錄，但是卻一條也沒返回赁遗，why?
因?yàn)樽硬樵兊慕Y(jié)果中有一條空值署辉，這條空值導(dǎo)致主查詢沒有記錄返回。這是因?yàn)樗械臈l件和空值比較結(jié)果都是空值岩四。因此無論什么時(shí)候只要空值有可能成為子查詢結(jié)果集合中的一部分哭尝，就不能使用NOT IN 運(yùn)算符。
在 FROM 子句中使用子查詢
查詢比自己部門平均工資高的員工姓名剖煌，工資材鹦，部門編號(hào)，部門平均工資
SQL> SELECT a.ename, a.sal, a.deptno, b.salavg
2 FROM emp a, (SELECT deptno, avg(sal) salavg
3 FROM emp
4 GROUP BY deptno) b
5 WHERE a.deptno = b.deptno
6 AND a.sal > b.salavg;
ENAME SAL DEPTNO SALAVG
---------- --------- --------- ----------
KING 5000 10 2600
JONES 2975 20 2335
SCOTT 3000 20 2335
...
6 rows selected.
練習(xí)4
查詢比自己職位平均工資高的員工姓名耕姊、職位桶唐，部門名稱，職位平均工資
查詢職位和經(jīng)理同員工SCOTT或BLAKE完全相同的員工姓名茉兰、職位尤泽，不包括SCOOT和BLAKE本人。
查詢不是經(jīng)理的員工姓名规脸。
ROWNUM
ROWNUM是一個(gè)偽列坯约，偽列是使用上類似于表中的列，而實(shí)際并沒有存儲(chǔ)在表中的特殊列莫鸭；
ROWNUM的功能是在每次查詢時(shí)闹丐，返回結(jié)果集的順序號(hào)，這個(gè)順序號(hào)是在記錄輸出時(shí)才一步一步產(chǎn)生的被因，第一行顯示為1卿拴，第二行為2，以此類推梨与。
SQL> SELECT rownum,empno, ename, job
2 FROM emp;
ROWNUM EMPNO ENAME JOB
--------- ---------- ---------
1 7839 KING PRESIDENT
2 7566 JONES MANAGER
3 7902 FORD ANALYST
4 7788 SCOTT ANALYST
ROWNUM使用的注意點(diǎn)：
- 如下SQL語句堕花，SELECT * FROM EMP WHERE ROWNUM>2;查詢不到任何記錄，因?yàn)镽OWNUM是在記錄輸出時(shí)才生成蛋欣，且總是從1開始航徙，所以輸出的第一條記錄不滿足>2的條件，被過濾掉陷虎，第二條的ROWNUM又成了1到踏，又不滿足〉2的條件，又被過濾掉尚猿，依此類推窝稿，所以永遠(yuǎn)沒有滿足條件的記錄，返回為空凿掂。所以對于ROWNUM只能執(zhí)行<伴榔、<=運(yùn)算纹蝴，不能執(zhí)行>、>=或一個(gè)區(qū)間運(yùn)算Between..And等
- ROWNUM和ORDER BY一起使用時(shí)，因?yàn)镽OWNUM在記錄輸出時(shí)生成，而ORDER BY子句在最后執(zhí)行链烈，所以當(dāng)兩者一起使用時(shí)，需要注意ROWNUM實(shí)際是已經(jīng)被排了序的ROWNUM兼犯。
TOP-N查詢
Top-N查詢主要是實(shí)現(xiàn)表中按照某個(gè)列排序，輸出最大或最小的N條記錄功能集漾。
Top-N分析語法：
SELECT [列名], ROWNUM
FROM (SELECT [列名]
FROM 表名
ORDER BY Top-N操作的列 ASC|DESC)
WHERE ROWNUM <= N;
ASC:查詢最小的N條記錄
DESC：查詢最大的N條記錄
練習(xí)5
查詢?nèi)肼毴掌谧钤绲那?名員工姓名切黔，入職日期。
查詢工作在CHICAGO并且入職日期最早的前2名員工姓名具篇，入職日期纬霞。
分頁
分頁查詢
在Oracle中，利用ROWNUM的特性驱显，可以實(shí)現(xiàn)數(shù)據(jù)庫端的分頁查詢诗芜，查詢語法為：
-
當(dāng)未指定需要按照某列排序，語法為：
SELECT b.*
FROM (SELECT ROWNUM rn,[列名1,列名2,....列名n]
FROM 表名1,[表名2,...表名n]
WHERE [條件表達(dá)式 AND ] ROWNUM <=目標(biāo)頁數(shù)每頁記錄數(shù)) b
WHERE rn > (目標(biāo)頁數(shù)-1)每頁記錄數(shù)
或
SELECT b.*
FROM (SELECT ROWNUM rn,[列名1,列名2,....列名n]
FROM 表名1,[表名2,...表名n]
[WHERE 條件表達(dá)式]) b
WHERE rn <=目標(biāo)頁數(shù)每頁記錄數(shù) and rn > (目標(biāo)頁數(shù)-1)每頁記錄數(shù)
思考：哪種方式效率高秒紧？
練習(xí)6
- 按照每頁顯示5條記錄绢陌，分別查詢第1頁挨下，第2頁熔恢，第3頁信息，要求顯示員工姓名臭笆、入職日期叙淌、部門名稱。
分頁查詢
-
當(dāng)指定需要按照某列排序時(shí)愁铺，語法為：
SELECT *
FROM (SELECT ROWNUM rn, b.*
FROM (SELECT 列名1 [,列名2,....列名n]
FROM 表名1,[表名2,...表名n]
[WHERE 子句]
ORDER BY 要排序的列 ASC|DESC ) b
WHERE ROWNUM <=目標(biāo)頁數(shù)每頁記錄數(shù)
)
WHERE rn > (目標(biāo)頁數(shù)-1)每頁記錄數(shù) ;
或：
SELECT *
FROM (SELECT ROWNUM rn, b.*
FROM (SELECT 列名1 [,列名2,....列名n]
FROM 表名1,[表名2,...表名n]
[WHERE 子句]
ORDER BY 要排序的列 ASC|DESC ) b
)
WHERE rn <=目標(biāo)頁數(shù)每頁記錄數(shù) and rn > (目標(biāo)頁數(shù)-1)每頁記錄數(shù);
練習(xí)7
- 按照每頁顯示5條記錄鹰霍，分別查詢工資最高的第1頁，第2頁茵乱，第3頁信息茂洒，要求顯示員工姓名、入職日期瓶竭、部門名稱督勺、工資。
本章重點(diǎn)總結(jié)
為什么使用子查詢
單行子查詢
多行子查詢
多列子查詢
子查詢中空值問題
FROM語句中子查詢
分頁查詢
課后作業(yè)
查詢工資高于編號(hào)為7782的員工工資斤贰，并且和7369號(hào)員工從事相同工作的員工的編號(hào)智哀、姓名及工資。
查詢工資最高的員工姓名和工資荧恍。
查詢部門最低工資高于10號(hào)部門最低工資的部門的編號(hào)瓷叫、名稱及部門最低工資。
查詢員工工資為其部門最低工資的員工的編號(hào)和姓名及工資。
顯示經(jīng)理是KING的員工姓名摹菠，工資盒卸。
顯示比員工SMITH參加工作時(shí)間晚的員工姓名，工資次氨，參加工作時(shí)間世落。
使用子查詢的方式查詢哪些職員在NEW YORK工作。
寫一個(gè)查詢顯示和員工SMITH工作在同一個(gè)部門的員工姓名糟需，雇用日期屉佳，查詢結(jié)果中排除SMITH。
寫一個(gè)查詢顯示其工資比全體職員平均工資高的員工編號(hào)洲押、姓名武花。
寫一個(gè)查詢顯示其上級(jí)領(lǐng)導(dǎo)是King的員工姓名、工資杈帐。
顯示所有工作在RESEARCH部門的員工姓名体箕，職位。
查詢每個(gè)部門的部門編號(hào)挑童、平均工資累铅，要求部門的平均工資高于部門20的平均工資。
查詢大于自己部門平均工資的員工姓名站叼，工資娃兽，所在部門平均工資，高于部門平均工資的額度尽楔。
列出至少有一個(gè)雇員的所有部門
列出薪金比"SMITH"多的所有雇員
列出入職日期早于其直接上級(jí)的所有雇員
找員工姓名和直接上級(jí)的名字
顯示部門名稱和人數(shù)
顯示每個(gè)部門的最高工資的員工
顯示出和員工號(hào)7369部門相同的員工姓名投储，工資
顯示出和姓名中包含"W"的員工相同部門的員工姓名
顯示出工資大于平均工資的員工姓名，工資
顯示出工資大于本部門平均工資的員工姓名阔馋，工資
顯示每位經(jīng)理管理員工的最低工資玛荞，及最低工資者的姓名
顯示比工資最高的員工參加工作時(shí)間晚的員工姓名，參加工作時(shí)間
顯示出平均工資最高的的部門平均工資及部門名稱
分割線
博主為咯學(xué)編程：父母不同意學(xué)編程呕寝，現(xiàn)已斷絕關(guān)系;戀人不同意學(xué)編程勋眯，現(xiàn)已分手;親戚不同意學(xué)編程，現(xiàn)已斷絕來往;老板不同意學(xué)編程,現(xiàn)已失業(yè)三十年下梢。客蹋。。怔球。嚼酝。。如果此博文有幫到你歡迎打賞竟坛，金額不限闽巩。钧舌。。
