SQL應用與開發：（五）多個資料表的串連，sql多個

SQL應用與開發：（五）多個資料表的串連，sql多個

資料庫中的各個表中儲存著不同的資料，使用者往往需要用多個表中的資料來組合、提煉出所需要的資訊。如果一個查詢需要對多個表進行操作，就稱為串連查詢。串連查詢的結果集或結果表稱為表之間的串連。查詢實際上是通過各個表之間共同列的關聯性來查詢資料的，它是關係型資料庫查詢最主要的特徵。

實現串連的結果是在向資料庫增添新類型的資料方面沒有限制，具有很大的靈活性。通常總是通過串連建立一個新表，以包含不同表中的資料。如果新表有合適的域，就可以將它串連到現有的表。

1.簡單串連操作

最簡單的串連方式是在SELECT語句的FROM子句中用逗號將不同的基表隔開，使用WHERE子句建立的同等串連是使第一個基表中一個或者多個列中的值與第二個基表中相應的一個或者多個列的值相等的串連。這樣在查詢結果中只顯示兩個基表中列的值相匹配的行。但是值得注意的是，無論不同表中的列是否具有相同的列名，都相應的通過增加表名來限制列名。

例如，在資料庫“銷售關係系統”中，從“業務員資訊”和“客戶資訊”連個表中，查詢“業務員編號”大於1004，並且在SELECT列表中增加一個列“業務員編號”：

SELECT 業務員資訊.業務員編號，業務員資訊.業務員編號，客戶資訊.客戶姓名，客戶資訊.客戶地址，客戶資訊.聯絡電話

FROM 業務員資訊，客戶資訊

WHERE 業務員資訊.業務員編號=客戶資訊.所屬業務員編號 AND 業務員資訊.業務員編號>1004

執行後，便是我們條件的結果。

在該查詢中，我們也可以使用相關名稱，“業務員資訊”表用別名A代替，“客戶資訊”表用B代替，當引用這連個表時，除了FROM子句外，在SELECT語句的任何地方都可以使用A和B,那麼我們上述的語句就變成了：

SELECT A.業務員編號，A.業務員編號，B.客戶姓名，B.客戶地址，B.聯絡電話

FROM 業務員資訊 A ，客戶資訊 B

WHERE A.業務員編號=B.所屬業務員編號 AND A.業務員編號>1004

其執行結果，和前面的完全相同。

這也為後面的多個表的串連的 學習奠定了基礎。例如，在資料庫“珠寶銷售系統”中，基於“珠寶資訊”、“珠寶商資訊”和“銷售明細資訊”三個表建立一個查詢。要求返回“珠寶資訊”表中的列“珠寶名稱”，“珠寶商資訊”表中的列“珠寶商姓名”和“銷售明細資訊”表中的列“利潤”：

SELECT A.珠寶名稱，B.珠寶商姓名，C.利潤

FROM 珠寶商資訊 AS A ，珠寶商資訊 AS B，銷售明細資訊 AS C

WHERE A.珠寶代號=C.珠寶代號 AND A.珠寶商編號=B.珠寶商編號

執行後，便是所查詢結果集。

2.內串連

內串連是最常用的串連查詢，一般用INNER JION關鍵字來指定內串連。但是，INNER不是必需的。如果只用JOIN關鍵字，還必須定義一個ON子句。內串連查詢操作列出與串連條件匹配的資料行，它使用比較子比較被串連的列值。

2.1等值串連

所謂等值串連解釋在串連條件中使用等號(=)運算子比較被串連列的值，其查詢結果中列出被串連表中的所有列，包括其中的重複列。換句話說，基表之間的串連通過相等的列值串連起來的查詢就是等值串連查詢。

等值串連查詢可以用兩種表示方式來指定串連條件。例如，在資料庫“銷售關聯絡統”中，基於“商品資訊”和“供應商資訊”兩個表建立一個查詢。限定查詢條件為兩個表中的“供應商編號”相等時返回，並要求返回的列為“商品資訊”表中的“商品名稱”和“供應商資訊”表中的列“供應商名稱”、“連絡人姓名”。

SELECT A.商品名稱，B.供應商名稱，B.連絡人姓名，A.單價

FROM 商品資訊 A，供應商名稱 B

WHERE A.供應商編號=B.供應商編號

在上述語句的WHERE子句中，用"="指定查詢為等值串連查詢。

還可以在查詢語句的FROM子句中使用INNER JOIN關鍵字連指定查詢是等值串連查詢。

SELECT A.商品名稱，B.供應商名稱，B.連絡人姓名，A.單價

FROM 商品資訊 A INNER JOIN 供應商資訊 B

                           ON A.供應商編號=B.供應商編號 

執行後，與上述語句所得結果相同。

也可以對串連查詢所得的結果利用ORDER BY子句進行排序。例如，將上述的的等值串連查詢的查詢按列“單價”的降序進行排列。

SELECT A.商品名稱，B.供應商名稱，B.連絡人姓名，A.單價

FROM 商品資訊 A INNER JOIN 供應商資訊 B

                           ON A.供應商編號=B.供應商編號 

ORDER BY A.單價 DESC

運行後，唯一與上述結果不同的是，該查詢的結果根據“單價”對查詢的結果進行了降序排序。

2.2非等值串連

在等值串連查詢的串連條件中不適用等號，而使用其他比較符就構成了非等值串連查詢。也就是說，非等值串連查詢是在串連條件中使用了等於運算子以外的其他比較子比較被串連列的列值。在非等值串連查詢中，可以使用的比較傲運算子有：>、<、>=、<=、!=，還可以使用BETWEEN...AND之類的關鍵字。

例如，從資料庫“銷售管理系統”中，基於“供應商資訊”和“入庫單資訊”兩個表建立一個查詢。限定查詢條件為兩個表中的“供應商編號”不相等，並返回列“供應商名稱”和“供應商所在城市”。

SELECT 供應商名稱，供應商所在城市

FROM 供應商資訊 A INNER JOIN 入庫單資訊 B

ON A.供應商編號!=B.供應商編號

執行上述語句後，其查詢結果返回80行，單大部分都是重複的。我們利用下述語句來消除重複行。

SELECT  DISTINCT 供應商名稱，供應商所在城市

FROM 供應商資訊 A INNER JOIN 入庫單資訊 B

ON A.供應商編號!=B.供應商編號

該查詢結果中資訊與“供應商資訊”表中列“供應商名稱”和“供應商所在城市”中的資訊完全相同，所以上述查詢並沒有實際應用價值，這就說明非等值串連查詢往往需要同其他串連查詢結合使用，尤其是與等值串連查詢結合。

2.3自然串連

自然串連是在串連條件中使用等號(=)運算子比較被串連列的列值，但它使用挑選清單指出查詢結果集中所包含的列，並刪除串連表中的重複列。簡單的說，在等值串連中去掉重複的屬性列，即為自然串連。

自然串連為具有相同名稱的列自動進行記錄匹配。自然串連不必指定任何同等串連條件。SQL實現方式判斷出具有相同名稱的列然後形成匹配。然而，自然串連雖然可以指定查詢結果包括的列，但是不能指定被匹配的列。

例如，在資料庫“珠寶營銷系統”中，基於“顧客資訊”和“珠寶商資訊”兩個表建立了一個自然串連查詢。這個串連查詢的限定條件是兩個表中“消費者所在城市”和“珠寶商所在城市”相同，並按照列“消費者姓名”、“消費者地址”、“珠寶商姓名”和“珠寶商地址”返回查詢結果。

SELECT 消費者姓名，消費者地址，消費者所在城市 AS 城市，珠寶商姓名。珠寶商地址

FROM 顧客資訊 A INNER JOIN B

ON A.消費者所在城市=B.珠寶商所在城市

執行上述語句後，對其結果進行分析。儘管利用自然查詢能夠消除查詢結果中重複的行，但是從上述語句的查詢結果中能夠發現，該查詢結果也是由笛卡爾積形成的。

3.外串連

當至少有一個同屬於兩個表的行符合串連條件時，內串連才返回行。而外串連返回所有的匹配航和一定的不匹配行，這主要取決於所建立的串連的類型。SQL支援的3種類型的外串連：

左外串連：返回所有的匹配行並從關鍵字JOIN左邊的表中返回所有不匹配的行。

右外串連：返回所有的匹配行並從關鍵字JOIN右邊的表中返回所有不匹配的行。

完全串連：返回所有的匹配行和不匹配的行。

3.1左外串連

左外串連的查詢結果集中包括指定左表中的所有行，而不僅僅是串連列所匹配的行。如果左表的某行在右表中沒有找到相匹配的行，則結果集中的右表的相對應位置為NULL。

在左外串連查詢的SELECT語句中，用LEFT OUTER JOIN關鍵字對兩個基表進行串連。

例如，在資料庫“珠寶營銷系統”中，以在同一個城市的消費者和珠寶商為條件，對“消費者資訊”和“珠寶商資訊”表進行左外串連查詢。

SELECT  消費者姓名，消費者地址，消費者所在城市 AS 城市，珠寶商姓名，珠寶商地址

FROM 顧客資訊 A LEFT OUTER JOIN 珠寶商資訊 B

ON A.消費者所在城市=B.珠寶商所在城市

執行後，查詢其結果中包含了三行含有NULL值得資料，儘管這三行沒有匹配列，但在查詢結果中仍然被包括進去，原因就是這三行中列“消費者姓名”中的資訊包含在左表中。這就是說，在進行左外串連查詢時，無論左表中的行是否能夠在右表中找到匹配的行，查詢結果中都將被顯示在該行，與其他行唯一不同的是，右表中與該行對應的位置用NULL值代替。

3.2右外串連

右外串連就是左外串連的反向串連，只不過在插敘結果集中包括的是指定右表的所有行。如果右表的某行在左表中沒有找到相匹配的行，則結果集中的左表的相對應位置為NULL。

在右外串連查詢的SELECET語句中，用RIGHT OUTER JOIN關鍵字對兩個基表進行串連。

例如，在資料庫“珠寶商營銷系統”中，以在同一個城市的消費者和珠寶商為條件，對“消費者資訊”和“珠寶商資訊”表進行右外串連。

SELECT  消費者姓名，消費者地址，消費者所在城市 AS 城市，珠寶商姓名，珠寶商地址

FROM 顧客資訊 A RIGHT OUTER JOIN 珠寶商資訊 B

ON A.消費者所在城市=B.珠寶商所在城市

執行後，查詢其結果中包含了兩行含有NULL值得資料，儘管這兩行沒有匹配列，但在查詢結果中仍然被包括進去，原因就是這兩行中列“珠寶商姓名”中的資訊包含在右表中。這就是說，在進行右外串連查詢時，無論右表中的行是否能夠在左表中找到匹配的行，查詢結果中都將被顯示在該行，與其他行唯一不同的是，左表中與該行對應的位置用NULL值代替。

3.3完全串連

完全串連返回左表和右表中的所有行。當某行在另一個表中沒有匹配行時，則另一個表與之相對應列值為NULL。如果表之間右匹配行，則整個結果集包含基表的資料值。

在完全串連查詢的SELECT語句中，用FULL OUTER JOIN關鍵字對兩個基表進行串連。

例如，在資料庫“珠寶商營銷系統”中，以在同一個城市的消費者和珠寶商為條件，對“消費者資訊”和“珠寶商資訊”表進行完全串連。

SELECT  消費者姓名，消費者地址，消費者所在城市 AS 城市，珠寶商姓名，珠寶商地址

FROM 顧客資訊 A FULL OUTER JOIN 珠寶商資訊 B

ON A.消費者所在城市=B.珠寶商所在城市

執行後，查詢其結果中包含了六行含有NULL值得資料，儘管這六行沒有匹配列，但在查詢結果中仍然被包括進去，原因就是這兩行中列“珠寶商姓名”中的資訊包含在右表中。這就是說，在進行完全串連查詢時，無論左表中還是右表是否能夠找到匹配的行，它都在查詢結果中顯示該行，而只是在找不到匹配的位置上用NULL值代替。

4.聯集查詢

如果有多個不同的查詢結果，但又不希望將它們串連在一起，組成資料。在這種情況下，可以使用UNION子句。使用UNION子句的查詢稱為聯集查詢，它可以將兩個或者更多查詢的結果組合為一個單個結果集，該結果集包含聯集查詢中所有查詢結果集中的全部行資料。聯集查詢不同於對兩個表中的列進行串連查詢，前者是組合兩個表中的行，後者是匹配兩個表中的列資料。聯集查詢的文法格式為：

SELECT <select_list>

FROM <table_reference>

[WHERE <search_condition>]

{UNION [ALL]

SELECT <select_list>

FROM <table_reference>

[WHERE <search_condition>]}

[ORDER BY <order_condition>]

在進行聯集查詢時，UNION子句會自動重複資料刪除的行，查詢結果的欄位標題為第一個查詢語句的欄位標題。因此，必須在第一個查詢語句中定義欄位標題。

例如，從資料庫”珠寶營銷系統“的"顧客資訊"表中，查詢”消費者所在城市“為”北京市“的消費者的姓名、家庭住址和聯絡電話，並為其增加類型列為”類別“，列的內容為”消費者“；從”珠寶商資訊“表中，查詢”珠寶商所在城市“同樣是”北京市“的珠寶商的相關資訊，並增加一個列，列的內容為“珠寶商”；最後，將兩個查詢的結果聯合在一起。

SELECT 消費者姓名 AS 姓名，消費者地址 AS 家庭住址，聯絡電話，'消費者' AS 類別

FROM 顧客資訊

WHERE 消費者所在城市='北京市'

UNION

SELECT 珠寶商姓名，珠寶商地址，電話，'珠寶商'

FROM 珠寶商資訊

WHERE 珠寶商所在城市='北京市'

在使用UNION的SELECT語句中，如果要對聯集查詢結果進行排序，則必須使用第一個查詢語句中的列名、欄位標題或者列序號。並且排序子句ORDER BY中最好用數字來指定排序次序，如果不用數字，則聯集查詢的子查詢中的列名就需要相同，也可以使用別名來統一列名。

另外，在對聯集查詢的結果進行排序的結果進行排序時，必須把ORDER BY子句放在SELECT子句的後面。

例如，從資料庫“銷售管理系統”的“客戶資訊”表中，查詢“客戶編碼”不大於1005的客戶的姓名、家庭住址和聯絡電話，並為其增加類型列“職能”，列的內容為“客戶”；從“業務員資訊”表中，查詢“業務員編號”不大於1005的業務員的姓名、家庭住址和聯絡電話，並增加一個列，列的內容為“業務員”；最後，將兩個查詢結果聯合在一起，並按類型“職能”的升序排列。

SELECT 客戶姓名 AS 姓名，客戶地址 AS 家庭住址，聯絡電話，'客戶' AS 職能

FROM 客戶資訊

WHERE 客戶編碼<= 1005

UNION

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

FROM 業務員資訊

WHERE 業務員編號<=1005

ORDER BY 職能

UNION ALL是另外一種對錶進行聯合的方法。它與UNION唯一不同的區別是它不重複資料刪除的行，也不對行進行自動排序。在對錶進行聯集查詢時，如果以希望在查詢結果中顯示重複的行，就可以使用UNION ALL。在此，不再一一舉例。

5.交叉串連和自串連

在串連查詢中還有兩個比較特殊的串連查詢方式：交叉串連和自串連。在交叉串連的查詢結果中，兩個表中每兩個可能成對的行佔一行。自串連就是一個表與自身進行串連查詢。

5.1交叉串連

兩個表進行交叉串連將產生來自這兩個表的各行的所有可能組合。交叉串連在不帶WHERE子句時，返回的是被串連的兩個表所有行的笛卡爾積，即返回到結果集中的行數等於第一個表中符合查詢條件的行數乘以第二個表中符合查詢條件的行數。當交叉串連帶有WHERE子句時，返回的是串連兩個表的所有行的笛卡爾積減去WHERE子句所現在而省略的所有行數。

交叉串連與前面介紹的基本串連操作非常相似。唯一不同的是，在FROM子句中，多個表名之間不是用逗號，而是用CROSS JOIN關鍵字隔開；並且在進行交叉串連時，不能像內串連和外串連一樣使用ON 關鍵字來限定串連條件，但是可以將串連條件限定在WHERE子句中。

例如，在資料庫“銷售管理系統”中，對“商品資訊”表和“供應商資訊”表進行交叉串連。要求返回商品的產地和供應商所在城市都是“上海市”或者構思“南京市”的相關資訊。

SELECT 商品資訊.商品名稱，商品資訊.產地，供應商資訊.供應商名稱

FROM 商品資訊 CROSS JOIN 供應商資訊

WHERE 商品資訊.產地=供應商資訊.供應商所在城市

AND (商品資訊.產地='上海市' OR 商品資訊.產地='南京市')

ORDER BY 商品資訊.產地

5.2自串連

串連不僅可以再不同表之間進行，也可以使一個表同其自身進行串連，這種串連成為自串連，相應的查詢稱為子串連查詢。子串連是與表本身進行的內串連或者外串連。

子串連的串連操作可以利用別名的方法實現一個表自身的串連。實質上，這種子身串連方法與兩個表的串連操作完全相似。只是在每次列出這個表時便為它命名一個別名。

例如，對資料庫“銷售管理系統”中的“客戶資訊”表進行自串連，查詢“客戶地址”相同的客戶資訊，並在查詢結果中只返回“客戶地址”為“北京市海澱區”或者“北京市朝陽區”的相關資訊。

SELECT A.客戶姓名，A.聯絡電話，A.客戶地址，B.客戶姓名，B.聯絡電話

FROM 客戶資訊 A INNER JOIN 客戶資訊 B

ON A.客戶地址=B.客戶地址

WHERE A.客戶地址='北京市海澱區'

OR A.客戶地址='北京市朝陽區'

由於此查詢涉及“客戶資訊”表與自身的串連，因此“客戶資訊”表以兩種角色顯示。要區分這連個角色，必須在FROM子句中為“客戶資訊”表中提供兩個不同的別名（A 和B），這兩個別名用來限定查詢中的列名。如果希望消除客戶與自身相匹配的行，則只需在上述語句中添加一個限定條件，即將上述語句改為：

SELECT A.客戶姓名，A.聯絡電話，A.客戶地址，B.客戶姓名，B.聯絡電話

FROM 客戶資訊 A INNER JOIN 客戶資訊 B

ON A.客戶地址=B.客戶地址

WHERE (A.客戶地址='北京市海澱區'

OR A.客戶地址='北京市朝陽區')

AND A.客戶姓名<>B.客戶姓名

該查詢結果和上述查詢結果相比較，發現在該查詢結果中消除了客戶與其自身相匹配的行。

6.學習小結

在學習本章節的內容時，很是感到頭疼，這內容也的確是多了點兒。但是，其聯絡卻是十分的緊密，學起來不是很費力，總算把學的知識總結起來了。

在對於多個資料表的串連學習中，資料表的聯絡很是實際化，在不斷練習中學到更多的知識。