1、北 京物 資學 院第第6章章 高級查詢高級查詢Oracle數據庫管理本章概述本章概述p在第一章介紹設計數據庫時，曾經介紹過為了在第一章介紹設計數據庫時，曾經介紹過為了數據庫的規范化，將數據表進行了分隔，這雖數據庫的規范化，將數據表進行了分隔，這雖然損壞了信息的完整性，但消除了數據冗余、然損壞了信息的完整性，但消除了數據冗余、添入異常和刪除異常。添入異常和刪除異常。p在查詢數據時，為了獲取完整的信息就要將多在查詢數據時，為了獲取完整的信息就要將多個表連接起來。這也是關系數據庫的一個重要個表連接起來。這也是關系數據庫的一個重要特性特性表之間存關系。表之間存關系。p這種關系可以將兩個表的數據聯系在一

2、起。多這種關系可以將兩個表的數據聯系在一起。多表查詢就是根據這種關系，實現從多個表中獲表查詢就是根據這種關系，實現從多個表中獲取數據還原信息。取數據還原信息。Oracle數據庫管理本章學習目標本章學習目標p理解什么是連接操作理解什么是連接操作p如何進行簡單的連接操作如何進行簡單的連接操作p使用表別名限定列使用表別名限定列p掌握如何進行內連接、外連接和交叉連接掌握如何進行內連接、外連接和交叉連接p理解結果集的并、交和差運算理解結果集的并、交和差運算p創建返回多行的子查詢創建返回多行的子查詢p創建返回單值的子查詢創建返回單值的子查詢p掌握嵌套子查詢的使用掌握嵌套子查詢的使用Oracle數據庫管理本

3、章學習要點本章學習要點p掌握在掌握在WHERE子句中使用子查詢。子句中使用子查詢。p掌握在掌握在HAVING子句中使用子查詢。子句中使用子查詢。p熟練掌握使用熟練掌握使用IN、ANY和和ALL操作符實現子查詢。操作符實現子查詢。p熟練掌握關聯子查詢。熟練掌握關聯子查詢。p熟練掌握嵌套子查詢。熟練掌握嵌套子查詢。p掌握簡單連接。掌握簡單連接。p熟練掌握多個表之間的內連接。熟練掌握多個表之間的內連接。p熟練掌握多個表之間的外連接。熟練掌握多個表之間的外連接。p了解多個表之間的交叉連接。了解多個表之間的交叉連接。p掌握使用集合操作符實現集合查詢。掌握使用集合操作符實現集合查詢。Oracle數據庫管理

4、本章內容本章內容p6.1 子查詢子查詢p6.2 實驗演示實驗演示獲取工作經歷次數獲取工作經歷次數最多的前最多的前5個人個人p6.3 高級查詢高級查詢Oracle數據庫管理6.1 子查詢子查詢p在外部的在外部的SELECT、UPDATE或或DELETE語語句內部使用句內部使用SELECT語句，這個內部語句，這個內部SELECT語句稱為子查詢語句稱為子查詢(Subquery)。p使用子查詢，主要是將子查詢的結果作為外使用子查詢，主要是將子查詢的結果作為外部主查詢的查找條件。部主查詢的查找條件。Oracle數據庫管理6.1 子查詢子查詢p6.1.1 子查詢的類型子查詢的類型p6.1.2 在在WHER

5、E子句中使用子查詢子句中使用子查詢p6.1.3 在在HAVING子句中使用子查詢子句中使用子查詢p6.1.4 使用使用IN操作符實現指定匹配查詢操作符實現指定匹配查詢p6.1.5 使用使用ANY操作符實現任意匹配查詢操作符實現任意匹配查詢p6.1.6 使用使用ALL操作符實現全部匹配查詢操作符實現全部匹配查詢p6.1.7 實現多列子查詢實現多列子查詢p6.1.8 實現關聯子查詢實現關聯子查詢p6.1.9 實現嵌套子查詢實現嵌套子查詢p6.1.10 在在UPDATE和和DELETE語句中使用子查詢語句中使用子查詢Oracle數據庫管理p在子查詢中可以使用兩種比較操作符在子查詢中可以使用兩種比較操

6、作符單行操作符和多行操單行操作符和多行操作符。作符。單行操作符：例如單行操作符：例如=、=、=、!=。多行操作符：例如多行操作符：例如ALL、ANY、IN、EXISTS。p可以把子查詢分為兩種類型：可以把子查詢分為兩種類型：單行子查詢和多行子查詢。單行子查詢和多行子查詢。p另外，子查詢還有下面另外，子查詢還有下面3種子類型，這種子類型，這3種子類型可以返回一行種子類型可以返回一行或多行查詢結果。或多行查詢結果。多列子查詢：向外部的SQL語句返回多列。關聯子查詢：引用外部的SQL語句中的一列或多列。在關聯子查詢語句中的一列或多列。在關聯子查詢中，可以使用中，可以使用EXISTS和和NOT EXI

7、STS操作符。操作符。嵌套子查詢：在子查詢中包含有子查詢。嵌套子查詢：在子查詢中包含有子查詢。p指定子查詢時，需要注意以下幾點：指定子查詢時，需要注意以下幾點：子查詢需要使用括號子查詢需要使用括號 ( ) 括起來。括起來。子查詢要放在比較操作符的右邊。子查詢要放在比較操作符的右邊。當子查詢的返回值是一個集合而不是一個值時，不能使用單行操作當子查詢的返回值是一個集合而不是一個值時，不能使用單行操作符，而必須根據需要使用符，而必須根據需要使用ANY、IN、ALL或或EXISTS等操作符。等操作符。6.1.1 子查詢的類型子查詢的類型Oracle數據庫管理6.1.2 在在WHERE子句中使用子查詢子

8、句中使用子查詢p1使用子查詢使用子查詢在在WHERE子句中使用子查詢的一般用法形式如子句中使用子查詢的一般用法形式如下：下：SELECT column_list FROM table_name WHERE expression operator (SELECT column_name FROM table_name WHERE condition GROUP BY exp HAVING having);其中，在外部其中，在外部SELECT語句的語句的WHERE子句中，子句中，expression用來指定一個表達式，也可以是表中的用來指定一個表達式，也可以是表中的一列；一列；operator可以

9、是單行和多行操作符；可以是單行和多行操作符；()中的中的內容表示子查詢內容。內容表示子查詢內容。Oracle數據庫管理6.1.2 在在WHERE子句中使用子查詢子句中使用子查詢p2不能使用不能使用ORDER BY子句子句在子查詢的在子查詢的SELECT語句中，可以使用語句中，可以使用FROM子句、子句、WHERE子句、子句、GROUP BY子句和子句和HAVING子句等，但是有些情況下子句等，但是有些情況下不能使用不能使用ORDER BY子句，例如在子句，例如在WHERE子句中使用子查詢時，子查詢語句子句中使用子查詢時，子查詢語句中就不能使用中就不能使用ORDER BY子句。子句。如果確實需要

10、使用如果確實需要使用ORDER BY子句對結果子句對結果進行排序，可以在外部查詢語句中使用該進行排序，可以在外部查詢語句中使用該子句。子句。Oracle數據庫管理6.1.3 在在HAVING子句中使用子查詢子句中使用子查詢p在在SELECT語句中使用語句中使用HAVING子句，可以實現對數據進行子句，可以實現對數據進行分組過濾。在分組過濾。在HAVING子句中，如果使用子查詢，那么就可子句中，如果使用子查詢，那么就可以實現對子查詢返回的結果根據分組進行過濾。以實現對子查詢返回的結果根據分組進行過濾。p【例】對對scott用戶的用戶的emp表進行檢索，在表進行檢索，在HAVING子句中使子句中使

11、用子查詢。獲取哪些部門的員工平均工資小于全體員工的平用子查詢。獲取哪些部門的員工平均工資小于全體員工的平均工資。具體如下：均工資。具體如下：SELECT deptno , AVG(sal) FROM scott.emp GROUP BY deptno HAVING AVG(sal) 、 ( SELECT MAX(AVG(sal) FROM scott.emp WHERE deptno IN (SELECT deptno FROM scott.dept WHERE loc IN (NEW YORK , CHICAGO) ) GROUP BY deptno );Oracle數據庫管理6.1.10

12、在在UPDATE和和DELETE語句中使用子查詢語句中使用子查詢p1在在UPDATE語句中使用子查詢語句中使用子查詢在在UPDATE語句中使用子查詢，可以將子查詢返回的結果賦值給需語句中使用子查詢，可以將子查詢返回的結果賦值給需要更新的列。要更新的列。【例】將員工編號為將員工編號為7839的員工的工資設置為平均工資，如下：的員工的工資設置為平均工資，如下：UPDATE scott.emp SET sal = ( SELECT AVG(sal) FROM scott.emp ) WHERE empno = 7839 ;p2在在DELETE語句中使用子查詢語句中使用子查詢在在DELETE語句中使用

13、子查詢，可以根據子查詢返回的結果刪除指語句中使用子查詢，可以根據子查詢返回的結果刪除指定的行。定的行。【例】刪除工作地點在刪除工作地點在NEW YORK的所有員工信息，如下：的所有員工信息，如下：DELETE FROM scott.emp WHERE deptno IN( SELECT deptno FROM scott.dept WHERE loc = NEW YORK);Oracle數據庫管理6.2 實驗演示實驗演示獲取工作經歷次數最多的前獲取工作經歷次數最多的前5個人個人p實驗演示：從實驗演示：從hr.emloyees和和job_history表查詢表查詢先從表先從表employees中

14、進行檢索基本信息；然后結合工作經中進行檢索基本信息；然后結合工作經歷表歷表jot_history，獲得每個人的工作經歷；最后按照每個，獲得每個人的工作經歷；最后按照每個人的工作次數降序排列，獲得前人的工作次數降序排列，獲得前5個人的信息即可。具體個人的信息即可。具體如下：如下：Select * from employees where employee_id in (select employee_id from (select employee_id from job_history group by employee_id order by count(employee_id) desc)

15、 where rownum(大于大于)、=(大于等于大于等于)、=(小于等于小于等于)、(不等不等于于)、!=(不等于不等于)、LIKE、IN和和BETWEEN等。等。通過通過emp和和salgrade表，查詢員工的工資等級。表，查詢員工的工資等級。Oracle數據庫管理6.3.3 使用使用INNER JOIN實現多個表的內連接實現多個表的內連接p3自然連接（在等值連接中把目標列中重復自然連接（在等值連接中把目標列中重復的屬性列去掉）的屬性列去掉）自然連接自然連接(NATURAL JOIN)是在兩個表中尋找列是在兩個表中尋找列名和數據類型都相同的字段，通過名和數據類型都相同的字段，通過相同的字

16、段相同的字段將將兩個表連接在一起，并返回所有符合條件的結果。兩個表連接在一起，并返回所有符合條件的結果。例例:使用使用INNER JOIN連接兩個不同的表連接兩個不同的表emp和和dept，ON來設置連接條件，使用來設置連接條件，使用WHERE子句限子句限制查詢范圍，檢索制查詢范圍，檢索ACCOUNTING部門的員工信部門的員工信息。息。Oracle數據庫管理6.3.3 使用使用INNER JOIN實現多個表的內連接實現多個表的內連接p4使用使用USING關鍵字簡化連接關鍵字簡化連接SQL/92標準可以使用標準可以使用USING關鍵字來簡化連接關鍵字來簡化連接查詢，但是只有在查詢滿足下面兩個條

17、件時，才查詢，但是只有在查詢滿足下面兩個條件時，才能使用能使用USING關鍵字進行簡化：關鍵字進行簡化：查詢必須是等值連接。查詢必須是等值連接。等值連接中的列必須具有相同的名稱和數據類型。等值連接中的列必須具有相同的名稱和數據類型。Oracle數據庫管理6.3.3 使用使用INNER JOIN實現多個表的內連接實現多個表的內連接p【例】使用使用USING關鍵字，重寫前例中的語句關鍵字，重寫前例中的語句.使用使用INNER JOIN連接兩個不同的表連接兩個不同的表emp和和dept，ON來設置連接條件，使用來設置連接條件，使用WHERE子句限制查詢范圍，檢索子句限制查詢范圍，檢索ACCOUNTI

18、NG部門的員工信息。部門的員工信息。使用使用USING關鍵字簡化連接時，需要注意以下幾點。關鍵字簡化連接時，需要注意以下幾點。(1)使用使用emp表和表和dept表中的表中的deptno列進行連接時，在列進行連接時，在USING子句和子句和SELECT子句中，都不能為子句中，都不能為deptno列指定表名或表別名。列指定表名或表別名。(2)如果在連接查詢時使用了兩個表中相同的多個列，那么就如果在連接查詢時使用了兩個表中相同的多個列，那么就可以在可以在USING子句中指定多個列名子句中指定多個列名.(3)如果對多個表進行檢索，那么就必須多次使用如果對多個表進行檢索，那么就必須多次使用USING關

19、鍵關鍵字進行指定字進行指定.Oracle數據庫管理6.3.4 使用使用OUTER JOIN實現多個表的外連接實現多個表的外連接p對于外連接，對于外連接，Oracle中可以使用加號中可以使用加號(+)來表示，來表示，也可以使用也可以使用LEFT、RIGHT和和FULL OUTER JOIN關鍵字。關鍵字。p外連接可以分為下面這外連接可以分為下面這3類：類：左外連接左外連接(LEFT OUTER JOIN或或LEFT JOIN)右外連接右外連接(RIGHT OUTER JOIN或或RIGHT JOIN)全外連接全外連接(FULL OUTER JOIN或或FULL JOIN)Oracle數據庫管理6

20、.3.4 使用使用OUTER JOIN實現多個表的外連接實現多個表的外連接p1左外連接左外連接左外連接是在檢索結果中除了顯示滿足連接條件的行外，左外連接是在檢索結果中除了顯示滿足連接條件的行外，還顯示還顯示JOIN關鍵字左側表中所有滿足檢索條件的行。關鍵字左側表中所有滿足檢索條件的行。使用左外連接，檢索使用左外連接，檢索emp和和salgrade表，獲得員工的工資表，獲得員工的工資等級。等級。為了顯示效果，先向為了顯示效果，先向emp表插入一行表插入一行sal小于小于700的記錄。的記錄。使用左連接查詢：使用左連接查詢： Select e.empno, e.ename, e.sal, d.gr

21、ade from scott.emp e LEFT OUTER JOIN scott.salgrade d ON e.sal BETWEEN d.losal and d.hisal等價于：等價于： Select e.empno, e.ename, e.sal, d.grade from scott.emp e , scott.salgrade d where e.sal BETWEEN d.losal and d.hisal(+)Oracle數據庫管理6.3.4 使用使用OUTER JOIN實現多個表的外連接實現多個表的外連接p2右外連接右外連接右外連接是在結果中除了顯示滿足連接條件的行外，還

22、右外連接是在結果中除了顯示滿足連接條件的行外，還顯示顯示JOIN右側表中所有滿足檢索條件的行。右側表中所有滿足檢索條件的行。使用右外連接，檢索使用右外連接，檢索emp和和dept表所包含的部門編號。表所包含的部門編號。Select distinct e.deptno, d.deptno from scott.emp e RIGHT OUTER JOIN scott.dept d ON e.deptno=d.deptno等價于：等價于： Select distinct e.deptno, d.deptno from scott.emp e , scott.dept d where e.deptn

23、o=d.deptno(+)Oracle數據庫管理6.3.4 使用使用OUTER JOIN實現多個表的外連接實現多個表的外連接p3全外連接全外連接全外連接是在結果中除了顯示滿足連接條件的行外，還全外連接是在結果中除了顯示滿足連接條件的行外，還顯示顯示JOIN兩側表中所有滿足檢索條件的行。兩側表中所有滿足檢索條件的行。如：使用全外連接，檢索如：使用全外連接，檢索emp和和dept表中所包含的部門表中所包含的部門編號編號Select distinct e.deptno,d.deptno from scott.emp e FULL OUTER JOIN scott.dept d ON e.deptno

24、=d.deptnoOracle數據庫管理6.3.5 使用使用CROSS JOIN實現交叉連接實現交叉連接p使用使用CROSS JOIN關鍵字，可以實現兩個表的交叉關鍵字，可以實現兩個表的交叉連接，所得到的結果將是這兩個表中各行數據的所連接，所得到的結果將是這兩個表中各行數據的所有組合，即這兩個表所有數據行的笛卡兒積。有組合，即這兩個表所有數據行的笛卡兒積。p交叉連接與簡單連接操作非常相似，不同的是，使交叉連接與簡單連接操作非常相似，不同的是，使用交叉連接時，在用交叉連接時，在FROM子句中多個表名之間不是子句中多個表名之間不是用逗號，而是使用用逗號，而是使用CROSS JOIN關鍵字隔開。另外

25、，關鍵字隔開。另外，在交叉連接中不需要使用關鍵字在交叉連接中不需要使用關鍵字ON限定連接條件，限定連接條件，但是可以添加但是可以添加WHERE子句設置連接條件。子句設置連接條件。p【例】使用交叉連接，查詢使用交叉連接，查詢emp表和表和dept表中，部表中，部門編號為門編號為10的員工信息和部門信息的員工信息和部門信息.Oracle數據庫管理6.3.6 使用使用UNION操作符獲取兩個結果集的并集操作符獲取兩個結果集的并集p使用使用UNION操作符的語法如下：操作符的語法如下：select_statement UNION ALL select_statement UNION ALL selec

26、t_statement . p其中，其中，select_statement是查詢的是查詢的SELECT語句；語句；ALL選項表示將所選項表示將所有行合并到結果集中，不指定該項，有行合并到結果集中，不指定該項，則只保留重復行中的一行。則只保留重復行中的一行。UNION操作符含義如左圖所示。操作符含義如左圖所示。Oracle數據庫管理6.3.6 使用使用UNION操作符獲取兩個結果集的并集操作符獲取兩個結果集的并集p【例】使用使用UNION ALL操作符，對操作符，對scott用用戶的戶的emp表進行操作，獲得員工編號大于表進行操作，獲得員工編號大于7800或者所在部門編號為或者所在部門編號為10的員工信息