一缭嫡、使用子查詢
- 語(yǔ)法格式
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語(yǔ)句中
- WHERE子句中
- HAVING子句中
- FROM子句中
- 子查詢的使用指導(dǎo)
- 子查詢要用括號(hào)括起來(lái)
- 將子查詢放在比較運(yùn)算符的右邊
- 對(duì)于單行子查詢要使用單行運(yùn)算符
- 對(duì)于多行子查詢要使用多行運(yùn)算符
- 子查詢的類型
- 單行子查詢
- 多行子查詢
- 多列子查詢
二标锄、單行子查詢
- 子查詢只返回一行一列
SELECT ename,job,sal FROM emp WHERE sal = (SELECT sal FROM emp WHERE empno = 7396);
- 使用單行運(yùn)算符
- 顯示和雇員7396從事相同工作并且工資大于雇員7876的員工姓名和工作
SELECT ename, job FROM emp WHERE job = (SELECT job FROM emp WHERE empno = 7369) AND sal > (SELECT sal FROM emp WHERE empno = 7876);
- 在子查詢中使用組函數(shù)
- 查詢工資最低的員工姓名顽铸，崗位及工資
SELECT ename,job,sal FROM emp WHERE sal = (SELECT MIN(sal) FROM emp);
- HAVING子句中使用子查詢
- 查詢部門最低工資比20部門最低工資高的部門編號(hào)及最低工資
SELECT deptno,MIN(sal) FROM emp GROUP BY deptno HAVING MIN(sal) > (SELECT MIN(sal) FROM emp WHERE deptno = 20);
- 查詢哪個(gè)部門的員工人數(shù)高于各部門的平均人數(shù)。
SELECT deptno,COUNT(empno) FROM emp GROUP BY deptno HAVING COUNT(empno) > (SELECT AVG(COUNT(empno)) FROM emp GROUP BY deptno);
三料皇、多行子查詢
- 子查詢返回記錄的條數(shù)谓松，可以是一條或者多條。
- 和多行子查詢進(jìn)行比較時(shí)践剂，需要使用多行操作符鬼譬，多行操作符包括：
- IN
- ANY
- ALL
- IN使用
IN操作符和以前介紹的功能一致，判斷是否與子查詢的任意一個(gè)返回值相同逊脯。
- 查詢是經(jīng)理的員工姓名优质，工資
SELECT ename,sal FROM emp WHERE empno IN (SELECT mgr FROM emp);
- ANY的使用
表示和子查詢的任意一個(gè)結(jié)果進(jìn)行比較，有一個(gè)滿足條件即可军洼。
- "< ANY"：表示小于子查詢結(jié)果集中的任意一個(gè)巩螃，即小于最大值就可以。
- "> ANY"：表示大于子查詢結(jié)果集中的任意一個(gè)匕争，即大于最小值就可以避乏。
- "= ANY"：表示等于子查詢結(jié)果集中的任意一個(gè)，即等于誰(shuí)都可以甘桑，相當(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,name,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,name,job,sal FROM emp WHERE sal < ANY(SELECT sal FROM emp WHERE deptno = 10) AND deptno <> 10;
- ALL的使用
表示和子查詢的所有行結(jié)果進(jìn)行比較冯键，每一行都必須滿足條件
- "< ALL"：表示小于子查詢結(jié)果集中的所有行惹盼，即小于最小值。
- "> ALL"：表示大于子查詢結(jié)果集中的所有行惫确，即大于最大值手报。
- "= ANL"：表示等于子查詢結(jié)果集中的所有行，即等于所有值改化，通常來(lái)說(shuō)沒(méi)有什么實(shí)際意義掩蛤。
- 查詢部門編號(hào)不為10，且工資比10部門任意一名員工工資高的員工編號(hào)陈肛，姓名揍鸟，職位，工資句旱。
SELECT empno,name,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,name,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,name,job,sal FROM emp WHERE sal < ALL(SELECT sal FROM emp WHERE deptno = 10) AND deptno <> 10;
四臊岸、多列子查詢
- 之前講的子查詢都是在一個(gè)條件表達(dá)式內(nèi)和子查詢的一個(gè)列進(jìn)行比較橙数，多列子查詢可以在一個(gè)條件表達(dá)式內(nèi)同時(shí)和子查詢的多個(gè)列進(jìn)行比較。
- 多列子查詢通常用IN操作符完成帅戒。
- 查詢出和1981年入職的任意一個(gè)員工的部門和職位完全相同的員工姓名灯帮、部門崖技、職位、入職日期钟哥，不包括1981年入職員工迎献。
SELECT ename,deptno,job,hiredate FROM emp WHERE (deptno, job) IN (SELECT deptno,job FROM emp WHERE to_char(hiredate,'YYYY')='1981') AND to_char(hiredate,'YYYY')<>'1981';
- 查詢出和1981年入職的任意員工的部門或職位相同的員工姓名、部門腻贰、職位吁恍、入職日期，不包括1981年入職員工播演。
SELECT ename,deptno,job,hiredate FROM emp WHERE (deptno, job) IN (SELECT deptno,job FROM emp WHERE to_char(hiredate,'YYYY')='1981') AND to_char(hiredate,'YYYY')<>'1981';
五冀瓦、子查詢中的空值
- 所有的條件和空值進(jìn)行比較結(jié)果都是空值。
- 因此写烤，無(wú)論什么時(shí)候只要空值有可能成為子查詢結(jié)果集中的一部分翼闽，就不能使用NOT IN操作符。
六洲炊、在FROM子句中使用子查詢
- 查詢比自己部門高平均工資高的員工姓名感局，工資，部門編號(hào)暂衡，部門平均工資询微。
SELECT a.ename,a.sal,a.empno,b.salavg FROM emp a, (SELECT deptno,avg(sal) salavg FROM emp GROUP BY deptno) b WHERE a.deptno = b.deptno AND a.sal > b.salavg; //總結(jié)：在FROM子句中使用子查詢，這個(gè)子查詢相當(dāng)于一張表的作用
七古徒、ROWNUM
1.ROWNUM是一個(gè)偽列拓提，偽列是使用上類似于表中的列，而實(shí)際上并沒(méi)有存儲(chǔ)在表中的特殊列隧膘。
2.ROWNUM的功能是在每次查詢時(shí)代态，返回結(jié)果集的順序號(hào)，這個(gè)順序號(hào)是在記錄輸出時(shí)才一步一步產(chǎn)生的疹吃，第一行為1蹦疑，第二行為2，以此類推萨驶。SELECT rownum,ename,job FROM emp;
- 在使用ROWNUM時(shí)應(yīng)該注意：
- 1.如下SQL語(yǔ)句歉摧，SELECT * FROM emp WHERE ROWNUM > 2查詢不到任何記錄，因?yàn)镽OWNUM是在記錄輸出時(shí)才生成的腔呜，并且總是從1開(kāi)始叁温，所以輸出的第一條記錄不滿足>2這個(gè)條件，被過(guò)濾掉核畴，第二條的ROWNUM又變成了1膝但，又不滿足>2的條件，又被過(guò)濾掉谤草，依次類推跟束，所以永遠(yuǎn)沒(méi)有滿足條件的記錄莺奸，返回空值。所以對(duì)于ROWNUM只能夠執(zhí)行<, <=運(yùn)算冀宴，不能執(zhí)行>, >=或者區(qū)間運(yùn)算BETWEEN ..AND等灭贷。
- 2.ROWNUM和ORDER BY一起使用時(shí)，因?yàn)镽OWNUM在記錄輸出時(shí)生成略贮，而ORDER BY子句在最后執(zhí)行甚疟，所以當(dāng)兩者一起使用時(shí)，需要注意的是RUWNUM已經(jīng)是被排過(guò)序的ROWNUM了刨肃。
八古拴、TOP-N查詢
- TOP-N查詢主要是實(shí)現(xiàn)表中按照某個(gè)列排序，輸出最大或最小的N條記錄真友。
- 語(yǔ)法
SELECT [列名], ROWNUM FROM (SELECT [列名] FROM 表名 ORDER BY Top-N操作的列 ASC|DESC) WHERE ROWNUM <= N （ASC是查詢最小的N條記錄黄痪，DESC是查詢最大的N條記錄）
九、分頁(yè)
- 分頁(yè)查詢
在Oracle中盔然，利用ROWNUM的特性桅打，可實(shí)現(xiàn)數(shù)據(jù)庫(kù)端的分頁(yè)查詢，查詢的語(yǔ)法如下：
- 當(dāng)未指定需要按照某列排序時(shí)愈案，語(yǔ)法為：
SELECT b.* FROM (SELECT ROWNUM rn,[列名1,列名2,....列名n] FROM 表名1,[表名2,...表名n] WHERE [條件表達(dá)式 AND ] ROWNUM <=目標(biāo)頁(yè)數(shù)*每頁(yè)記錄數(shù)) b WHERE rn > (目標(biāo)頁(yè)數(shù)-1)*每頁(yè)記錄數(shù) 或 SELECT b.* FROM (SELECT ROWNUM rn,[列名1,列名2,....列名n] FROM 表名1,[表名2,...表名n] [WHERE 條件表達(dá)式]) b WHERE rn <=目標(biāo)頁(yè)數(shù)*每頁(yè)記錄數(shù) and rn > (目標(biāo)頁(yè)數(shù)-1)*每頁(yè)記錄數(shù)思考：哪種方式效率高挺尾？
- 當(dāng)指定需要按照某列進(jìn)行排序時(shí)，語(yǔ)法為：
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)頁(yè)數(shù)*每頁(yè)記錄數(shù) ) WHERE rn > (目標(biāo)頁(yè)數(shù)-1)*每頁(yè)記錄數(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)頁(yè)數(shù)*每頁(yè)記錄數(shù) and rn > (目標(biāo)頁(yè)數(shù)-1)*每頁(yè)記錄數(shù);
