第8章Oracle資料學(xué)習

1、第8章 子查詢與高級查詢4本章要點：掌握在WHERE子句中使用子查詢。掌握在HAVING子句中使用子查詢。熟練掌握使用IN、ANY和ALL操作符實現(xiàn)子查詢。熟練掌握關(guān)聯(lián)子查詢。熟練掌握嵌套子查詢。掌握簡單連接。熟練掌握多個表之間的內(nèi)連接。熟練掌握多個表之間的外連接。了解多個表之間的交叉連接。掌握使用集合操作符實現(xiàn)集合查詢。28.1 子 查 詢4在外部的SELECT、UPDATE或DELETE語句內(nèi)部使用SELECT語句，這個內(nèi)部SELECT語句稱為子查詢(Subquery)。4使用子查詢，主要是將子查詢的結(jié)果作為外部主查詢的查找條件。38.1.1 子查詢的類型4 在子查詢中可以使用兩種比較操作

2、符單行操作符和多行操作符。 單行操作符：例如=、=、=、!=。 多行操作符：例如ALL、ANY、IN、EXISTS。4 可以把子查詢分為兩種類型： 單行子查詢和多行子查詢。4 另外，子查詢還有下面3種子類型，這3種子類型可以返回一行或多行查詢結(jié)果。 多列子查詢：向外部的SQL語句返回多列。 關(guān)聯(lián)子查詢：引用外部的SQL語句中的一列或多列。在關(guān)聯(lián)子查詢中，可以使用EXISTS和NOT EXISTS操作符。 嵌套子查詢：在子查詢中包含有子查詢。4 指定子查詢時，需要注意以下幾點： 子查詢需要使用括號 ( ) 括起來。 子查詢要放在比較操作符的右邊。 當子查詢的返回值是一個集合而不是一個值時，不能使

3、用單行操作符，而必須根據(jù)需要使用ANY、IN、ALL或EXISTS等操作符。48.1.2 在WHERE子句中使用子查詢41使用子查詢在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可以是單

4、行和多行操作符；()中的內(nèi)容表示子查詢內(nèi)容。58.1.2 在WHERE子句中使用子查詢42不能使用ORDER BY子句在子查詢的SELECT語句中，可以使用FROM子句、WHERE子句、GROUP BY子句和HAVING子句等，但是有些情況下不能使用ORDER BY子句，例如在WHERE子句中使用子查詢時，子查詢語句中就不能使用ORDER BY子句。68.1.3 在HAVING子句中使用子查詢4在SELECT語句中使用HAVING子句，可以實現(xiàn)對數(shù)據(jù)進行分組過濾。在HAVING子句中，如果使用子查詢，那么就可以實現(xiàn)對子查詢返回的結(jié)果根據(jù)分組進行過濾。4【例例8.3】對scott用戶的emp表進

5、行檢索，在HAVING子句中使用子查詢。獲取哪些部門的員工平均工資小于全體員工的平均工資。具體如下：SQL SELECT deptno , AVG(sal) FROM scott.emp GROUP BY deptno 2 HAVING AVG(sal) 、 SELECT empno , ename , sal , deptno FROM scott.emp 2 WHERE sal ( 3 SELECT MAX(AVG(sal) FROM scott.emp WHERE deptno IN ( 4 SELECT deptno FROM scott.dept 5 WHERE loc IN (NE

6、W YORK , CHICAGO) ) 6 GROUP BY deptno ); EMPNOENAME SAL DEPTNO - - - 7566 JONES 2975 20 7788 SCOTT 3000 20 7839 KING 5000 10 7902 FORD 3000 20168.1.10 在UPDATE和DELETE語句中使用子查詢4 1在UPDATE語句中使用子查詢在UPDATE語句中使用子查詢，可以將子查詢返回的結(jié)果賦值給需要更新的列?！纠?.17】將員工編號為7839的員工的工資設(shè)置為平均工資，如下：|SQL UPDATE scott.emp SET sal = (| 2

7、SELECT AVG(sal) FROM scott.emp )| 3 WHERE empno = 7839 ;|已更新已更新 1 行。行。4 2在DELETE語句中使用子查詢在DELETE語句中使用子查詢，可以根據(jù)子查詢返回的結(jié)果刪除指定的行?！纠?.18】刪除工作地點在NEW YORK的所有員工信息，如下：|SQL DELETE FROM scott.emp WHERE deptno IN(| 2 SELECT deptno FROM scott.dept WHERE loc = NEW YORK);|已刪除已刪除3行。行。178.2 實驗指導(dǎo)獲取借閱次數(shù)最多的前5本圖書4實驗指導(dǎo)8-1

8、：獲取借閱次數(shù)最多的前5本圖書由于需要顯示圖書信息，所以從圖書表book中進行檢索；然后結(jié)合借書表lend，獲得每本書的借閱情況；最后按照每本書的借閱次數(shù)降序排列，獲得前5本圖書信息即可。具體如下：|參見教材參見教材P182上述語句中，在最外層SELECT語句的WHERE子句中使用了子查詢，并且使用IN操作符指定匹配查詢；第3行的SELECT語句中，在FROM子句中使用子查詢，通過子查詢獲得分組和排序之后的bookid；最后在WHERE子句中指定rownum列小于等于5，指定排序之后的前5行記錄。188.3 高 級 查 詢4檢索數(shù)據(jù)時，通過各個表之間共同列的關(guān)聯(lián)性，可以查詢存放在多個表中的不同

9、實體的信息。4如果在查詢時需要對多個表進行操作，并且指定多個表的連接關(guān)系，則該查詢就稱為高級查詢，也可以稱為連接查詢。198.3.1 使用等號(=)實現(xiàn)多個表的簡單連接4在連接查詢中，如果僅僅通過SELECT子句和FROM子句連接多個表，那么查詢的結(jié)果將是一個通過笛卡兒積所生成的表。4【例例8.19】使用SELECT子句和FROM子句，從scott用戶的emp表和dept表中檢索數(shù)據(jù)，如果不指定檢索條件，將得到56行記錄。具體如下：參見教材P1834由于scott.emp表中有14行記錄，scott.dept表中有4行記錄，所以笛卡兒積所生成的表一共有56(14 * 4 = 56)行記錄。20

10、8.3.2 使用表的別名4設(shè)置表的別名，只需要在FROM子句中引用該表時，將表別名跟在表的實際名稱后面即可。表別名和表的實際名稱之間使用空格進行分隔。4【例例8.21】使用表別名的方式，重寫例8.20中所示的語句。為scott.emp表設(shè)置表別名為e，為scott.dept表設(shè)置表別名為d。語句如下：SQL SELECT empno , ename, sal , e.deptno , d.deptno , dname 2 FROM scott.emp e , scott.dept d 3 WHERE e.deptno = d.deptno;218.3.3 使用INNER JOIN實現(xiàn)多個表的內(nèi)

11、連接4在FROM子句中，使用JOIN連接的語法形式如下：FROM join_table1 join_type join_table2 ON ( join_condition ) join_type . ON join_condition , . 4語法說明如下。join_table1、join_table2：參與連接操作的表名。join_type：連接類型，連接類型有INNER JOIN(內(nèi)連接)、OUTER JOIN(外連接)和CROSS JOIN(交叉連接)。join_condition：連接條件，由被連接表中的列和比較運算符、邏輯運算符等構(gòu)成?？梢允褂枚嘟Mjoin_type ON joi

12、n_condition 子句，實現(xiàn)多個表的連接。228.3.3 使用INNER JOIN實現(xiàn)多個表的內(nèi)連接41等值連接所謂等值連接，是指在連接條件中使用等于(=)運算符比較被連接的值，也就是通過相等的列值連接起來的查詢。42不等連接所謂不等連接，就是在連接條件中使用除等號(=)外的其他比較運算符，構(gòu)成非等值連接查詢?？梢允褂玫谋容^運算符包括：(大于)、=(大于等于)、=(小于等于)、(不等于)、!=(不等于)、LIKE、IN和BETWEEN等。238.3.3 使用INNER JOIN實現(xiàn)多個表的內(nèi)連接43自然連接自然連接(NATURAL JOIN)是在兩個表中尋找列名和數(shù)據(jù)類型都相同的字段，通

13、過相同的字段將兩個表連接在一起，并返回所有符合條件的結(jié)果。44使用USING關(guān)鍵字簡化連接SQL/92標準可以使用USING關(guān)鍵字來簡化連接查詢，但是只有在查詢滿足下面兩個條件時，才能使用USING關(guān)鍵字進行簡化：查詢必須是等值連接。等值連接中的列必須具有相同的名稱和數(shù)據(jù)類型。248.3.3 使用INNER JOIN實現(xiàn)多個表的內(nèi)連接4【例例8.25】使用USING關(guān)鍵字，重寫例8.22中的語句，如下：參見教材P187使用USING關(guān)鍵字簡化連接時，需要注意以下幾點。|(1)使用使用emp表和表和dept表中的表中的deptno列進行連接時，在列進行連接時，在USING子句和子句和SELECT

14、子句中，都不能為子句中，都不能為deptno列指定表列指定表名或表別名。名或表別名。|(2)如果在連接查詢時使用了兩個表中相同的多個列，那如果在連接查詢時使用了兩個表中相同的多個列，那么就可以在么就可以在USING子句中指定多個列名，形式如下：子句中指定多個列名，形式如下：v參見教材參見教材P187|(3)如果對多個表進行檢索，那么就必須多次使用如果對多個表進行檢索，那么就必須多次使用USING關(guān)鍵字進行指定，形式如下：關(guān)鍵字進行指定，形式如下：v參見教材參見教材P187258.3.4 使用OUTER JOIN實現(xiàn)多個表的外連接4對于外連接，Oracle中可以使用加號(+)來表示，也可以使用L

15、EFT、RIGHT和FULL OUTER JOIN關(guān)鍵字。4外連接可以分為下面這3類：左外連接(LEFT OUTER JOIN或LEFT JOIN)右外連接(RIGHT OUTER JOIN或RIGHT JOIN)全外連接(FULL OUTER JOIN或FULL JOIN)268.3.4 使用OUTER JOIN實現(xiàn)多個表的外連接41左外連接左外連接是在檢索結(jié)果中除了顯示滿足連接條件的行外，還顯示JOIN關(guān)鍵字左側(cè)表中所有滿足檢索條件的行。42右外連接右外連接是在結(jié)果中除了顯示滿足連接條件的行外，還顯示JOIN右側(cè)表中所有滿足檢索條件的行。43全外連接全外連接是在結(jié)果中除了顯示滿足連接條件的

16、行外，還顯示JOIN兩側(cè)表中所有滿足檢索條件的行。278.3.5 使用CROSS JOIN實現(xiàn)交叉連接4使用CROSS JOIN關(guān)鍵字，可以實現(xiàn)兩個表的交叉連接，所得到的結(jié)果將是這兩個表中各行數(shù)據(jù)的所有組合，即這兩個表所有數(shù)據(jù)行的笛卡兒積。4交叉連接與簡單連接操作非常相似，不同的是，使用交叉連接時，在FROM子句中多個表名之間不是用逗號，而是使用CROSS JOIN關(guān)鍵字隔開。另外，在交叉連接中不需要使用關(guān)鍵字ON限定連接條件，但是可以添加WHERE子句設(shè)置連接條件。4【例例8.29】使用交叉連接，查詢emp表和dept表中，部門編號為10的員工信息和部門信息，如下：參見教材P190288.3

17、.6 使用UNION操作符獲取兩個結(jié)果集的并集4使用UNION操作符的語法如下：select_statement UNION ALL select_statement UNION ALL select_statement . 4其中，select_statement是查詢的SELECT語句；ALL選項表示將所有行合并到結(jié)果集中，不指定該項，則只保留重復(fù)行中的一行。UNION操作符含義如圖8-1所示。298.3.6 使用UNION操作符獲取兩個結(jié)果集的并集4【例例8.30】使用UNION ALL操作符，對scott用戶的emp表進行操作，獲得員工編號大于7800或者所在部門編號為10的員工信息。

18、使用ORDER BY語句將結(jié)果集按照deptno列升序排列輸出。具體如下：參見教材P1914【例例8.31】使用UNION操作符，但是不指定ALL關(guān)鍵字，獲得員工編號大于7800或者所在部門編號為10的員工信息。具體如下：參見教材P191308.3.7 使用INTERSECT操作符獲取兩個結(jié)果集的交集4使用INTERSECT操作符，獲取結(jié)果集的公共行，也稱為獲取結(jié)果集的交集。INTERSECT操作符的使用語法同UNION，只是操作符不再是UNION ALL，而是INTERSECT。4INTERSECT操作符的含義如圖8-2所示。318.3.8 使用MINUS操作符獲取兩個結(jié)果集的差集4SQL語言中的MINUS集合運算，表示獲得給定集合之間的差異，也就意味著所得到的結(jié)果集中，其中的元素僅存在于前一個集合中，而不存在于另一個集合。4MINUS操作符的含義如圖8-3所示。328.3.8 使用M