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

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

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

3、用單行操作符，而必須根據(jù)需要使用ANY、IN、ALL或EXISTS等操作符。48.1.2 在WHERE子句中使用子查詢41使用子查詢?cè)赪HERE子句中使用子查詢的一般用法形式如下：|SELECT column_list FROM table_name WHERE expression operator (|SELECT column_name FROM table_name WHERE condition |GROUP BY exp HAVING having);其中，在外部SELECT語(yǔ)句的WHERE子句中，expression用來(lái)指定一個(gè)表達(dá)式，也可以是表中的一列；operator可以是單

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

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語(yǔ)句中使用子查詢4 1在UPDATE語(yǔ)句中使用子查詢?cè)赨PDATE語(yǔ)句中使用子查詢，可以將子查詢返回的結(jié)果賦值給需要更新的列?！纠?.17】將員工編號(hào)為7839的員工的工資設(shè)置為平均工資，如下：|SQL UPDATE scott.emp SET sal = (| 2

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

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

9、實(shí)體的信息。4如果在查詢時(shí)需要對(duì)多個(gè)表進(jìn)行操作，并且指定多個(gè)表的連接關(guān)系，則該查詢就稱為高級(jí)查詢，也可以稱為連接查詢。198.3.1 使用等號(hào)(=)實(shí)現(xiàn)多個(gè)表的簡(jiǎn)單連接4在連接查詢中，如果僅僅通過(guò)SELECT子句和FROM子句連接多個(gè)表，那么查詢的結(jié)果將是一個(gè)通過(guò)笛卡兒積所生成的表。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子句中引用該表時(shí)，將表別名跟在表的實(shí)際名稱后面即可。表別名和表的實(shí)際名稱之間使用空格進(jìn)行分隔。4【例例8.21】使用表別名的方式，重寫例8.20中所示的語(yǔ)句。為scott.emp表設(shè)置表別名為e，為scott.dept表設(shè)置表別名為d。語(yǔ)句如下：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實(shí)現(xiàn)多個(gè)表的內(nèi)

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

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

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

14、子句中，都不能為子句中，都不能為deptno列指定表列指定表名或表別名。名或表別名。|(2)如果在連接查詢時(shí)使用了兩個(gè)表中相同的多個(gè)列，那如果在連接查詢時(shí)使用了兩個(gè)表中相同的多個(gè)列，那么就可以在么就可以在USING子句中指定多個(gè)列名，形式如下：子句中指定多個(gè)列名，形式如下：v參見教材參見教材P187|(3)如果對(duì)多個(gè)表進(jìn)行檢索，那么就必須多次使用如果對(duì)多個(gè)表進(jìn)行檢索，那么就必須多次使用USING關(guān)鍵字進(jìn)行指定，形式如下：關(guān)鍵字進(jìn)行指定，形式如下：v參見教材參見教材P187258.3.4 使用OUTER JOIN實(shí)現(xiàn)多個(gè)表的外連接4對(duì)于外連接，Oracle中可以使用加號(hào)(+)來(lái)表示，也可以使用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實(shí)現(xiàn)多個(gè)表的外連接41左外連接左外連接是在檢索結(jié)果中除了顯示滿足連接條件的行外，還顯示JOIN關(guān)鍵字左側(cè)表中所有滿足檢索條件的行。42右外連接右外連接是在結(jié)果中除了顯示滿足連接條件的行外，還顯示JOIN右側(cè)表中所有滿足檢索條件的行。43全外連接全外連接是在結(jié)果中除了顯示滿足連接條件的

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

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

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