精通Oracle核心技術(shù)和項目實戰(zhàn)之高級查詢

1、第8章 高級查詢我們已經(jīng)初步了解了數(shù)據(jù)表的基礎(chǔ)查詢。但是，在實際的數(shù)據(jù)庫操作中，往往需要同時從來自兩個或兩個以上的表中查詢相關(guān)數(shù)據(jù)，連接就是滿足這些需求的技術(shù)。本章將主要介紹子查詢、聯(lián)合語句、單表關(guān)聯(lián)語句及多表查詢的相關(guān)使用。8.1 連接簡介上一章的查詢語句都只涉及一個表的數(shù)據(jù)。在很多情況下，需要查詢的數(shù)據(jù)往往涉及多個表，這時需要對多個表進(jìn)行連接查詢。通過連接，可以根據(jù)各個表之間的邏輯關(guān)系從兩個或多個表中檢索數(shù)據(jù)。如，既要查詢訂單的信息，又要查詢訂單所在城市的信息，這就涉及orders和customersnew兩個表。8.1.1 連接定義多表連接實際上就是實現(xiàn)如何使用一個表中的數(shù)據(jù)來選擇另一個

2、表中的行。而連接條件則主要通過以下方法定義兩個表在查詢中的關(guān)聯(lián)方式：指定每個表中要用于連接的列，典型的連接條件是在一個表中指定外鍵，在另一個表中指定與其關(guān)聯(lián)的鍵。指定比較各列的值時要使用的邏輯運算符（=、等）。在select語句中實現(xiàn)多表連接，并不是一個物理存在的實體。換句話說，它在數(shù)據(jù)表中并不存在，它只是由數(shù)據(jù)庫系統(tǒng)在需要時創(chuàng)建的，只在查詢、檢索數(shù)據(jù)期間有效。8.1.2 連接的類型連接體現(xiàn)在where子句中指定的條件上，在條件中要指定兩個表通過哪些列進(jìn)行連接。一般情況下進(jìn)行連接查詢的兩個表是通過主鍵和外鍵進(jìn)行關(guān)聯(lián)的，所以最簡單的條件是一個表的外鍵與另一個表的主鍵相等。在SQL中，表的連接類型

3、主要有下面幾種。內(nèi)部連接（inner join）外部連接（outer join）交叉連接（cross join）8.1.3 簡單的多表查詢表的連接實現(xiàn)最簡單的方法就是在select語句的from子句中，羅列要連接的表即可。而要進(jìn)一步實現(xiàn)復(fù)雜的多表連接，則需要在from子句中使用join（inner join、outer join、cross join等）關(guān)鍵詞，以后的章節(jié)將詳細(xì)講解。8.1.3 簡單的多表查詢1.如何實現(xiàn)多表查詢實現(xiàn)多表查詢，可以在from子句中通過join關(guān)鍵詞連接需要查詢的表。而簡單的多表查詢，則可以不使用join關(guān)鍵詞，而直接列出所要連接的表，利用逗號把from子句后的表

4、名隔開?！臼纠?-1】使用from子句直接實現(xiàn)product_information和categories兩個表的連接。8.1.3 簡單的多表查詢2.指定連接條件直接使用from子句連接表，返回的是兩個表的記錄的笛卡爾積，這在實際應(yīng)用中意義不大。在實際應(yīng)用中，通常需要連接的兩個表的記錄滿足一定的關(guān)系。這時，可以在select語句的where子句中指定連接條件?！臼纠?-2】使用where子句指定product_information和categories兩個表的連接條件。8.1.3 簡單的多表查詢3.使用別名作為表名的簡寫在上面的示例中，select語句中有關(guān)列名均使用了“product_in

5、formation.category_id”的完整表達(dá)式。在連接的兩個表中，如果不是相同的列名，可以省略表名；對于相同的列名，在使用時必須指明其對應(yīng)的表名。而使用表名比較繁瑣，我們可以為表定義別名?！臼纠?-3】使用別名簡化product_information和categories兩個表的連接。8.1.3 簡單的多表查詢4.自連接在查詢信息時，有時需要將表與其自身進(jìn)行連接，即自連接，這時就需要為表定義別名。【示例8-4】從customersnew表中查詢賒銷限額credit_limit比顧客編號839大的所有顧客信息。8.2 表的連接查詢在關(guān)系型數(shù)據(jù)庫中實現(xiàn)多表之間的聯(lián)系，我們引入了連接查詢

6、，連接用于指定多數(shù)據(jù)源（表、視圖）之間如何組合，以形成最終的數(shù)據(jù)源。前面介紹的表的連接，是通過from子句列出所要連接的表，結(jié)合使用where子句列出連接條件來實現(xiàn)。本節(jié)將主要為大家介紹使用關(guān)鍵字join實現(xiàn)表的連接，主要包括自然連接、內(nèi)連接，外連接。使用join關(guān)鍵字有助于將連接操作與where的搜索條件分開。8.2.1 自然連接自然連接將兩個數(shù)據(jù)源中具有相同名稱的列進(jìn)行連接。在自然連接中，用戶不需要明確指定進(jìn)行連接的列，系統(tǒng)會自動完成這一任務(wù)。連接后相同的列只保留一個。如現(xiàn)在存在兩個表：R表和S表，這兩個表存在相同的列是B列。如果將這兩個表進(jìn)行自然連接，連接的具體情況如圖所示。8.2.1

7、自然連接8.2.1 自然連接自然連接使用natrual join關(guān)鍵字。語法如圖所示?！臼纠?-5】在示例數(shù)據(jù)庫中，product_information和categories都包含category_id這列，現(xiàn)在我們把這兩個表進(jìn)行自然連接。8.2.2 內(nèi)連接內(nèi)連接也稱為簡單連接，它會把兩個或多個表進(jìn)行連接，最后只能查詢出匹配的記錄，不匹配的記錄將無法查詢出來。理解如圖所示。8.2.2 內(nèi)連接8.2.2 內(nèi)連接這種連接查詢是平時最常用的查詢。語法如圖所示?！臼纠?-6】在示例數(shù)據(jù)庫中，orders數(shù)據(jù)表存儲的是訂單信息，這時如果想知道訂單者的聯(lián)系方式phone_number就需要連接表cust

8、omersnew。8.2.3 外連接外連接分為左外連接、右外連接、全外連接。使用左外連接查詢，返回的結(jié)果不僅僅是符合連接條件的行記錄，還包含了左邊表中的全部記錄，右外連接與左外連接相反。全外連接返回所有匹配成功的記錄，還包含了左、右表中未匹配成功的記錄。理解如圖所示。8.2.3 外連接8.2.3 外連接1.左外連接左外連接又稱左向外連接，也就是說如果左表的某行記錄在右表中沒有匹配項，則在返回結(jié)果中右表的所有選擇列表均為空，左外連接使用語法如圖所示?！臼纠?-7】左外連接連接product_information、categories數(shù)據(jù)表，顯示產(chǎn)品編號、產(chǎn)品分類編號、分類名稱。8.2.3 外連

9、接2.右外連接右外連接又稱右向外連接，與左外連接相反，將右邊表中所有的數(shù)據(jù)與左表進(jìn)行匹配，返回的結(jié)果顯示出了匹配成功的記錄，還包含右表中未匹配成功的記錄，并在其左表對應(yīng)列補空值。右外連接使用語法如圖所示?！臼纠?-8】右外連接連接product_information、categories數(shù)據(jù)表，顯示產(chǎn)品編號、產(chǎn)品分類編號、分類名稱。8.2.3 外連接3.全外連接返回所有匹配成功的記錄，并返回左表未匹配成功的記錄，也返回右表未匹配成功的記錄。語法如圖所示?！臼纠?-9】全外連接連接product_information、categories數(shù)據(jù)表，顯示產(chǎn)品編號、產(chǎn)品分類編號、分類名稱。【示例8

10、-10】利用外連接的簡略寫法來改寫示例8-8。8.3 單表中的聯(lián)合語句聯(lián)合語句是指兩個或多個select語句是并列關(guān)系，并且對這些select語句所捕獲的記錄集進(jìn)行集合操作，以獲得最終的結(jié)果集。這些聯(lián)合語句包括以下幾種：union查詢、union all查詢、intersect查詢和minus查詢。8.3.1 union查詢該操作符用于取得兩個查詢結(jié)果集的并集。理解如圖所示。當(dāng)使用該操作符時，會自動去掉結(jié)果集中重復(fù)行。語法結(jié)構(gòu)如圖所示。8.3.1 union查詢【示例8-11】我們使用關(guān)鍵字union，顯示customersnew表中，cust_first_name是Bruce Margret

11、和city是Roma的顧客編號、顧客姓名、所在城市。8.3.2 union all查詢該操作符與union相似，但是它不會取消重復(fù)行，不會排序。理解如圖所示。8.3.2 union all查詢【示例8-12】我們使用關(guān)鍵字union all，顯示customersnew表中，cust_first_name是Bruce或Margret和city是Roma的顧客編號、顧客姓名、所在城市。8.3.3 intersect查詢intersect操作符用于兩個查詢結(jié)果的交集，理解如圖所示。【示例8-13】我們使用關(guān)鍵字intersect，顯示customersnew表中，cust_first_name是M

12、argret和city又是Roma的顧客編號、顧客姓名、所在城市。8.3.4 minus查詢minus集合運算可以查詢兩個給定集合之間的差集，理解如圖所示?！臼纠?-14】我們使用關(guān)鍵字minus，顯示customersnew表中credit_limit在30005000之間的顧客信息。8.4 子查詢子查詢是指嵌套在查詢語句中的查詢語句。子查詢出現(xiàn)的位置一般為條件語句，如where條件，它本質(zhì)上是where后的一個條件表達(dá)式。Oracle會首先執(zhí)行子查詢，然后執(zhí)行父查詢。8.4.1 理解子查詢子查詢是嵌入在其他SQL語句中的select語句，也叫嵌套查詢。子查詢首先生成結(jié)果集，并將結(jié)果集應(yīng)用于

13、條件語句。子查詢可以出現(xiàn)在插入、查詢、更新和刪除語句中。建立子查詢的目的在于更加有效的限制where子句中的條件，并可以將復(fù)雜的查詢邏輯梳理的更加清晰。子查詢與父查詢關(guān)系情況如圖所示。8.4.2 子查詢使用在select語句中，where子句或者h(yuǎn)aving子句中的條件往往不能用一個確定的表達(dá)式來確定，而要依賴于另一個查詢，這個被嵌套使用的查詢就是子查詢。子查詢返回單行。查詢語法如圖所示。8.4.3 子查詢的使用方式及限制在實際應(yīng)用中，子查詢一般出現(xiàn)在where子句中，包含子查詢的語句通常采用圖中所列格式中的一種。8.4.3 子查詢的使用方式及限制1.比較運算符引入子查詢凡是表達(dá)式可以出現(xiàn)的地

14、方幾乎都可以使用子查詢，只是SQL對查詢的結(jié)果施加了某些限制。即子查詢用在比較運算符之后的表達(dá)式中，該子查詢必須返回單值。【示例8-15】子查詢顯示顧客編號customer_id是101的顧客所定產(chǎn)品的折扣價?！臼纠?-16】子查詢顯示顧客編號customer_id是101并且sales_rep_id是159的顧客所定產(chǎn)品的折折扣價。8.4.3 子查詢的使用方式及限制【示例8-17】利用子查詢，從customersnew表中查詢賒銷限額credit_limit比顧客編號為839大的所有顧客信息。8.4.3 子查詢的使用方式及限制2.子查詢中的聚合函數(shù)之前在講解基本查詢時，我們可以在查詢結(jié)果中包

15、括聚合函數(shù)，在這里也可以將聚合函數(shù)作為where子句的搜索條件的一部分得到聚合函數(shù)返回的值。聚合函數(shù)返回的是單值，可以在比較運算符引入子查詢?！臼纠?-18】查詢產(chǎn)品單價unit_price高于平均價格并且line_item_id為1的訂單的order_id、ine_item_id、unit_price的詳細(xì)信息。【示例8-19】查詢各個品種的訂單產(chǎn)品的產(chǎn)品單價unit_price高于平均價格的訂單的order_id、ine_item_id、unit_price的詳細(xì)信息。8.4.3 子查詢的使用方式及限制2.在子查詢中使用in、all關(guān)鍵字對于在子查詢返回多個值的情況，可以使用in關(guān)鍵字。當(dāng)

16、使用in運算符引入子查詢時，就是對子查詢集合成員測試，即把源表中的列值與子查詢的返回結(jié)果進(jìn)行比較，如果列值與返回結(jié)果集中的列數(shù)據(jù)值之一匹配，那么in判別式求值為true，查詢結(jié)果就包含這行數(shù)據(jù)?！臼纠?-20】使用子查詢，結(jié)合in關(guān)鍵字，顯示顧客編號customer_id是101的顧客所定產(chǎn)品的折扣價。8.4.3 子查詢的使用方式及限制【示例8-21】我們使用關(guān)鍵字all，在數(shù)據(jù)表customersnew中查詢顯示credit_limit比country為IT的所有的顧客的credit_limit高的customer_id、cust_first_name、和country?！臼纠?-22】我們

17、使用關(guān)鍵字any，在數(shù)據(jù)表customersnew中查詢，顯示credit_limit比country為IT的顧客的credit_limit高的customer_id、cust_first_name、和country。8.4.3 子查詢的使用方式及限制3.在子查詢中使用exists關(guān)鍵字在某些情況下，只需要查詢返回true或false，子查詢的數(shù)據(jù)本身并不重要。這時，就可以使用exists判別式來引入子查詢。使用exists引入子查詢時，就相當(dāng)于進(jìn)行一次存在性測試。【示例9-23】exists往往可以作為in判式的替代語法。結(jié)合表order_items、orders查詢顧客編號customer

18、_id是101的顧客所定產(chǎn)品的折扣價信息。表order_items測試哪些訂單號在該集合中。8.4.3 子查詢的使用方式及限制在子查詢過程中，常用的運算符如表所示，表中集合S為子查詢返回的結(jié)果。運算符運算符用法用法說明說明existsexists如果集合如果集合S部位空，則條件表達(dá)式為真，否則為假部位空，則條件表達(dá)式為真，否則為假in表達(dá)式 in S如果表達(dá)式的值在集合如果表達(dá)式的值在集合S中，則條件表達(dá)式為真，否中，則條件表達(dá)式為真，否則為假則為假=表達(dá)式=S如果表達(dá)式的值與集合如果表達(dá)式的值與集合S中的唯一一個元素相等，則中的唯一一個元素相等，則條件表達(dá)式為真，否則為假。集合條件表達(dá)式為真

19、，否則為假。集合S中最多只有一個中最多只有一個元素元素 =與“=”相同進(jìn)行相應(yīng)的關(guān)系運算進(jìn)行相應(yīng)的關(guān)系運算any用在集合之前指定要與集合中的任一個元素進(jìn)行比較指定要與集合中的任一個元素進(jìn)行比較all用在集合之前用在集合之前指定要與集合中的所有元素進(jìn)行比較指定要與集合中的所有元素進(jìn)行比較8.5 查詢語句優(yōu)化所謂語句優(yōu)化是在原來的語句基礎(chǔ)上為了是語句能夠更快速地得到結(jié)果的方法。在查詢語句中，每一條SQL語句對數(shù)據(jù)庫的操作，產(chǎn)生結(jié)果的時間也不同。即使是完成同一功能的SQL語句，最后的響應(yīng)時間也是不同的。本小節(jié)主要講解查詢語句優(yōu)化中的使用技巧。8.5.1 在查詢中盡量不使用“*”在數(shù)據(jù)庫的查詢操作中，操作著最常用的查詢某一個數(shù)據(jù)表的全部記錄，此時，需要使用“*”。在Oracle數(shù)據(jù)庫中，使用“*”后，會浪費很多查詢時間，所以，在查詢語句中使用“*”時，要考慮是否有字段是不需要的。如果有不需要的字段就要用指定列名的方式查詢，這樣就可以提高查詢效率。8.5.2 多表查詢中盡量使用表別名在查詢中使用別名是比較廣泛的，在Oracle數(shù)據(jù)庫中設(shè)置表的別名方式主要有兩種，如圖所示。8.5.3 條件查詢多使用where在SQL語句中指定查詢條件時主要有兩個