SQL應用與開發：（七）資料操作 · 查 · （三）使用子查詢通路和修改資料

3.使用子查詢通路和修改資料

子查詢和連接配接查詢一樣提供了使用單個查詢通路多個表中的資料的方法。子查詢在其他結果的基礎上提供一種有效地方式來表示where子句的條件。子查詢是一個select語句，它定義在select、insert、update或delect語句或者另一個子查詢中。子查詢的select語句可與外部查詢指向不同的表。

嵌套的子查詢或嵌套的select語句是指包含一個或多個子查詢的select語句。子查詢可嵌套在外部的select、insert、update或delect語句的where或having子句或其他子查詢内。如果嵌套了多層，則總是首先評估最内層的查詢。子查詢也成為内查詢，也可以嵌套任意數量的子查詢。任何可以使用表達式的地方都可以使用子查詢，隻要它傳回的是單個值。

3.1傳回多行的子查詢

子查詢是在select語句中的where子句實作的，可以把where子句中的子查詢分成兩類：傳回多行的子查詢和隻傳回一個值的子查詢。

3.1.1使用in關鍵字

可以使用in關鍵字來判斷一個表中指定列的值是否包含在已定義的清單中，或者在另一個表中。在前一種情況下，可以指定列名、in關鍵字和用來指定列進行比較的值清單；在後一種情況下，可以指定列名、in關鍵字和引用另外一個表的子查詢。

舉例來說，從資料庫”銷售管理系統“中，查詢(沒有)接待過顧客的業務員的相關資訊。

select 業務員姓名，家庭住址，電話

from 業務員資訊

where 業務員編号 （not）in 

( select 所屬業務員編号

from 客戶資訊 )

3.1.2使用exists關鍵字

在一些情況下，隻需要傳回一個真值或者假值。exists關鍵字隻注重子查詢是否傳回行。如果子查詢傳回一個或多個行，那麼exists便傳回為真，否則為假。子查詢必須是一個真實的值，它用來比較不同表中兩列的值。

使用exists關鍵字引入一個子查詢時，就相當于進行一次是否存在的測試。它的作用是在where子句中測試子查詢傳回的行是否存在。exisits子查詢實際上不産生任何資料，它隻傳回true或false。

舉例來說，在資料庫”銷售管理系統“中，查詢”供應商資訊“表中，”供應商編号“為1006的供應商所提供的商品的相關資訊。

select 商品名稱，産地，單價

from 商品資訊

where exisits 

(select *

from 供應商資訊

where 供應商編号=商品資訊.供應商編号

and 供應商編号=1006)

not exists和exists相反，如果子查詢沒有傳回行，則滿足not exists中的where子句，即在子查詢傳回行時，not exists查詢成功。

舉例來說，在”珠寶銷售系統“中，查詢在”銷售資訊“中沒有提供珠寶并且其所在城市不是北京市的珠寶商的相關資訊，并按”珠寶商所在城市“進行排序。

select 珠寶商姓名，珠寶商位址，珠寶商所在城市，電話

from 珠寶商資訊

where not exists 

from 銷售資訊

where 珠寶商編号=珠寶商資訊.珠寶商編号

)

and   珠寶商所在城市<>'北京市'

order by 珠寶商所在城市 

3.1.3使用比較運算符

子查詢可以由一個比較運算符引入。與使用關鍵字in引入的子查詢一樣，由比較運算符與一些關鍵字引入的子查詢傳回一個值清單。

sql支援的在子查詢中使用到的比較運算符有any、some和all。any和some關鍵字隻注重是否有傳回值滿足搜尋條件，它們的含義相同，可以互相替換使用。all關鍵字隻注重是否所有的傳回值都滿足搜尋條件。

舉例來說，在資料庫”銷售管理系統“的”出庫單明細資訊“表中，查詢”出庫商品金額“大于任意一個”入庫單明細資訊“表中”入庫商品金額“高于10000的出庫單的相關資訊。

select *

from 出庫單明細資訊

where 出庫單商品金額 > any

(select 入庫商品金額

from 入庫單明細資訊

where 入庫商品金額 > 10000)

為了友善學習兩者使用情況，我們進行對比學習，下面就舉一個相同的例子，在資料庫”銷售管理系統“的”出庫單明細資訊“表中，查詢”出庫商品金額“大于所有”入庫單明細資訊“表中”入庫 商品金額“低于10000的出庫單的相關資訊。

where 出庫單商品金額 > all

where 入庫商品金額 < 10000)

3.2傳回單個值的子查詢

這樣的子查詢隻傳回一個值，然後将一列值與單個子查詢傳回的值進行比較，這時可以使用比較運算符。

由未修改的比較運算符（後面不跟any或all的比較運算符）引入的子查詢必須傳回單個值而不是值清單。

舉例來說，在”珠寶銷售系統“中，查詢與”消費者編号“為27的消費者同處一個城市的珠寶商相關資訊，要求以列”珠寶商姓名“、”珠寶商位址“、”珠寶商所在城市“和”電話“的形式傳回查詢結果。

select 珠寶商姓名，珠寶商位址，珠寶山所在城市，電話

where 珠寶商所在城市=

(select 消費者所在城市

from  顧客資訊

where 消費者編号=27

由于聚集函數可以傳回一個單值，是以可以在子好擦尋中包含聚集函數。

舉例來說，在資料庫”銷售管理系統“中的”商品資訊“表中，查詢單價大于平均價格的商品的相關資訊。要求以列”商品名稱“、”産地“和”單價“的形式傳回查詢結果。

where 單價>

(select avg(單價)

3.3使用相關子查詢

在之前說明的子查詢中，sql隻對子查詢進行一次評估，然後替換搜尋條件中的子查詢結果，以及依據搜尋條件的值來對外部查詢進行評估。但是，有些子查詢的執行過程依賴于值得外部查詢。結果是，反複的執行子查詢，對外部查詢選擇的每行都執行一次。這樣的子查詢稱為相關子查詢。

因為相關子查詢依賴于其結果的外部查詢，是以他們不能進行單獨評估。相關子查詢的where子句引用外部查詢的from子句中的表。也就是說，相關子查詢是一個在外部查詢中包含表的引用外部查詢的from子句中的表。也就是說，相關子查詢是一個在外部插叙中包含表的引用的子查詢，它不能再外部查詢之前求值。

舉例來說，在”銷售管理系統“中，查詢由”供應商名稱“為”北京世紀葵花“提供的商品的相關資訊。要求以列”商品名稱“、”産地“和”單價“的形式傳回查詢結果。

where '北京世紀葵花' in 

(select 供應商名稱

where 商品資訊.供應商編号=供應商資訊.供應商編号

在相關子查詢中，也可以為表指定表名，利用别名來代替表名。但是如果對于同一個表進行相關子查詢時，則必須為表指定别名。

舉例來說，在”珠寶營銷系統“的”顧客資訊“表中，查詢顧客所居住的不同城市。

select distinct a.消費者所在城市

from 顧客資訊 a 

where a.消費者所在城市 in 

(select b.消費者所在城市

from 顧客資訊 b

where a.消費者編号<>b.消費者編号

上述子查詢的語句等同于下面的自連接配接查詢語句（關于自連接配接的相關介紹，請查閱這裡），将該自連接配接查詢語句運作後，其查詢結果與上述的子查詢語句的結果完全相同。

from 顧客資訊 a inner join 顧客資訊 b

on a.消費者所在城市=b.消費者所在城市

and a.消費者編号<>b.消費者編号

3.4使用嵌套子查詢

前面介紹了隻有一個子查詢的select語句，然而，在select語句中還可以包含多個子查詢，即一個子查詢中還包含其它子查詢，這樣的查詢稱為嵌套子查詢。

在select語句中使用多個子查詢的一種方法是把它們作為這個語句的不同組成部分。例如，一個where子句可能含有兩個關鍵字來引導兩個子查詢語句。還有一種在select語句中使用多個子查詢的方法是把一個子查詢嵌套到另一個子查詢中。

舉例來說，在資料庫“銷售管理系統”中，查詢客戶在2005年6月1日至2005年12月31日之間購買商品時，接待客戶的業務員的相關資訊。要求以列“業務員姓名”、“家庭住址”和“電話”的形式傳回查詢結果。

where 業務員編号 in

(select 所屬業務員編号

from 客戶資訊

where 客戶編号 in

(select 客戶編号

from 出庫單資訊

where 出庫日期 between '2005-6-1' and '2005-12-31'

上述語句運作順序為：綠色部分，紫色部分，藍色部分，即由内到外，逐次查詢。

3.5使用子查詢修改資料

子查詢還可以用來修改資料庫中的資料。使用子查詢修改資料主要是通過下面3個關鍵字來實作insert、update和delete。

3.5.1插入資料

insert語句可以向已有表中添加資料。它可以直接向表中插入資料，也可以用視圖向隐含表中插入資料。如果要在insert語句中使用子查詢，必須把它作為values子句中定義的一個值。

舉例來說，在資料庫“銷售管理系統”中的“業務員資訊”表中增加一名業務員的一行新資料，該行資料中“業務員編号”為1009，“業務員姓名”、“家庭住址”和“電話”來自“客戶資訊”表中“客戶編号”為1008的“客戶姓名”、“客戶位址”和“聯系電話”。

insert into 業務員資訊 values

(1009,

(select 客戶姓名

where 客戶編号=1008),

(select 客戶位址

(select 聯系電話

where 客戶編号=1008)

再執行下面語句進行檢視：select *   from 業務員資訊

在insert語句中使用子查詢向表中插入資料時，必須确定子查詢的傳回結果隻能傳回一個值。如果傳回的查詢結果中多于一個值，就會出現錯誤，并且子查詢中傳回的單個值必須和目标列的資料類型及其他限制一緻。

3.5.2更新資料

update語句允許修改表中已有資料。和insert語句一樣，可以直接對表中的資料進行修改。如果視圖可更新，也可以通過視圖進行修改。要在update語句中使用子查詢，子查詢則由where子句引入。

舉例來說，在上個例子中，将資料庫“銷售管理系統”的“業務員資訊”表中添加的那一行資料所對應的“業務員姓名”、“家庭住址”和“電話”分别更改為“趙奇”、“北京市西城區”和“13585452343”。

update 業務員資訊

set 業務員姓名='趙奇',家庭住址='北京市西城區',電話='13585452343'

where 業務員姓名=

where 客戶姓名='薛紅林'

3.5.3删除資料

delete語句實作的功能是删除資料庫表中的資料。在delete語句中的where子句中使用子查詢與update語句相似。

舉例來說，将資料庫“銷售管理系統”的“業務員資訊”表中插入的那一行資料删除。

delete 業務員資訊

where 客戶編号=1008

4.學習小結

資料庫的查操作是資料庫的重點學習部分，在這幾部分的學習和總結的過程中，深感乏力，無論知識體系的龐大還是其中相關聯系的錯綜複雜，都是十分重要的，卻也是最難以了解和掌握的。

自己也是用了半月的時間把資料庫的查操作的總結用最後的時間總結了出來，做一分享，當然，不得不說的是，對于資料庫的相關知識，希望努力去實踐，在操作中體會用法，以便更好地掌握其要領，也希望自己今後可以做到更好。