大數(shù)據(jù)分析SQL數(shù)據(jù)庫鏈接教程

　　第一次學(xué)習(xí)SQL時(shí)，通常在單個(gè)表中處理數(shù)據(jù)。在現(xiàn)實(shí)世界中，數(shù)據(jù)庫通常具有多個(gè)表中的數(shù)據(jù)。如果我們希望能夠使用該數(shù)據(jù)，則必須在一個(gè)查詢中合并多個(gè)表。在此SQL聯(lián)接教程中，我們將學(xué)習(xí)如何使用聯(lián)接從多個(gè)表中選擇數(shù)據(jù)。
 

　　我們假設(shè)您了解使用SQL的基礎(chǔ)知識(shí)，包括過濾，排序，聚合和子查詢。如果您不這樣做，我們的SQL基礎(chǔ)課程將教授所有這些概念，您可以免費(fèi)參加該課程。
 

　　概況資料庫
 

　　我們將使用具有兩個(gè)表的CIA World Factbook(Factbook)數(shù)據(jù)庫版本。第一個(gè)表格稱為facts，每行代表Factbook中的國家/地區(qū)。這是facts表格的前5行：

　　除了該facts表外，還有第二個(gè)表cities，其中包含《概況》中各個(gè)國家/ 地區(qū)的主要城市區(qū)域的信息(在本教程的其余部分中，我們將使用“城市”一詞來表示與“主要城市區(qū)域”相同的含義) 。讓我們看一下該表的前幾行，以及每列代表的描述：

　　1)id –每個(gè)城市的唯一ID。

　　2)name –城市名稱。

　　3)population –城市人口。

　　4)capital–城市是否為省會(huì)城市：1如果是，0如果不是。

　　5)facts_id– facts表中國家的ID 。
 

　　最后一列對我們特別有意義，因?yàn)樗窃糵acts表中也存在的一列數(shù)據(jù)。表之間的鏈接很重要，因?yàn)樗糜诤喜⒉樵冎械臄?shù)據(jù)。下面是一個(gè)架構(gòu)圖，它顯示了數(shù)據(jù)庫中的兩個(gè)表，其中的列以及如何鏈接這兩個(gè)表。

　　模式圖中的線清楚地顯示id了facts表中的facts_id列與cities表中的列之間的鏈接。
 

　　如果您想下載數(shù)據(jù)庫以在自己的計(jì)算機(jī)上繼續(xù)學(xué)習(xí)，則可以將數(shù)據(jù)集下載為SQLite數(shù)據(jù)庫。
 

　　我們的第一個(gè)SQL連接
 

　　使用SQL連接數(shù)據(jù)的最常見方法是使用內(nèi)部連接。內(nèi)部聯(lián)接的語法為：

　　內(nèi)部連接子句由兩部分組成：
 

　　1)INNER JOIN，它告訴SQL引擎您希望在查詢中聯(lián)接的表的名稱，并希望使用內(nèi)部聯(lián)接。

　　2)ON，它告訴SQL引擎使用哪些列來連接兩個(gè)表。
 

　　聯(lián)接通常在FROM子句之后的查詢中使用。讓我們看一下一個(gè)基本的內(nèi)部聯(lián)接，在其中合并兩個(gè)表中的數(shù)據(jù)。

　　讓我們看一下其中包含聯(lián)接的查詢行：
 

　　1)INNER JOIN cities：這告訴SQL引擎我們希望cities使用內(nèi)部聯(lián)接將表聯(lián)接到查詢中。

　　2)ON cities.facts_id = facts.id：按照語法告訴SQL引擎在連接數(shù)據(jù)時(shí)使用哪些列table_name.column_name。
 

　　您可能會(huì)認(rèn)為這SELECT * FROM facts將意味著查詢僅返回表中的列facts，但是將*通配符與聯(lián)接一起使用時(shí)，將為您提供兩個(gè)表中的所有列。這是此查詢的結(jié)果：

　　該查詢?yōu)槲覀兲峁┝藘蓚€(gè)表中的所有列，以及每行中idfrom facts和facts_idfrom 之間的匹配項(xiàng)cities(僅限于前5行)。
 

　　了解SQL內(nèi)部聯(lián)接
 

　　現(xiàn)在，我們已經(jīng)將兩個(gè)表合并在一起，以向我們提供有關(guān)中每行的更多信息cities。讓我們仔細(xì)看看這個(gè)內(nèi)部聯(lián)接是如何工作的。
 

　　內(nèi)部聯(lián)接的工作方式是僅包含每個(gè)表中具有使用ON子句指定的匹配項(xiàng)的行。讓我們看一下上一個(gè)屏幕中的聯(lián)接如何工作的圖。我們包含了一些行，這些行最能說明連接：

　　我們的內(nèi)部聯(lián)接將包括：
 

　　a.從行cities表中有cities.facts_id一個(gè)匹配facts.id的facts。
 

　　我們的內(nèi)部聯(lián)接將不包括：
 

　　a.從行cities表中有一個(gè)cities.facts_id不匹配任何facts.id從facts。

　　b.從行facts表中有一個(gè)facts.id不匹配任何cities.facts_id從cities。

　　您可以看到這以維恩圖表示：

　　我們已經(jīng)知道如何使用別名為列指定自定義名稱，例如：

　　我們還可以為表名創(chuàng)建別名，這使帶有聯(lián)接的查詢更易于讀寫。代替：

　　我們可以這樣寫：

　　就像列名一樣，using AS是可選的。通過寫可以得到相同的結(jié)果：

　　我們還可以將別名與通配符結(jié)合使用-例如，使用上面創(chuàng)建的別名，c.*將為我們提供表中的所有列cities。
 

　　雖然我們在上一個(gè)屏幕中的查詢包含了該ON子句中的兩列，但ON在最終的列列表中，我們不需要使用該子句中的任何一列。這很有用，因?yàn)檫@意味著我們只能顯示我們感興趣的信息，而不必每次都包含兩個(gè)聯(lián)接列。
 

　　讓我們使用我們學(xué)到的內(nèi)容來構(gòu)建原始查詢。好：
 

　　a.加入cities到facts使用INNER JOIN。

　　b.使用別名作為表名。

　　c.按順序包括：

　　　　1)來自的所有列cities。

　　　　2)name從facts別名到 的列country_name。

　　d.僅包括前5行。

　　在SQL中練習(xí)內(nèi)部聯(lián)接
 

　　讓我們練習(xí)編寫一個(gè)查詢，以使用內(nèi)部聯(lián)接來回答數(shù)據(jù)庫中的問題。假設(shè)我們要使用到目前為止所學(xué)的知識(shí)，從數(shù)據(jù)庫中生成國家及其首都城市的表格。我們的第一步是考慮在最終查詢中需要哪些列。我們需要：
 

　　1)name來自 的專欄facts

　　2)name來自 的專欄cities
 

　　鑒于我們已經(jīng)確定需要兩個(gè)表中的數(shù)據(jù)，因此需要考慮如何將它們聯(lián)接。前面的模式圖表明，每個(gè)表中只有一列將它們鏈接在一起，因此我們可以對這些列使用內(nèi)部聯(lián)接來聯(lián)接數(shù)據(jù)。
 

　　到目前為止，考慮到我們的問題，我們已經(jīng)可以編寫大部分查詢了(與我們之前編寫的查詢幾乎相同)：

　　我們過程的最后一部分是確保我們具有正確的行。從前面的兩個(gè)屏幕，我們知道，像這樣的查詢將返回所有行cities中具有相應(yīng)匹配從facts在facts_id列。我們只對“城市”表中的首都感興趣，因此我們需要WHERE在該capital列上使用一個(gè)子句，該子句的值是1城市是否為首都，是否為首都0：

　　現(xiàn)在，我們可以將所有這些放在一起編寫一個(gè)查詢，以回答我們的問題。我們將其限制為僅前10行，以便可以管理輸出量。

SQL中的左聯(lián)接
 

　　如前所述，內(nèi)部聯(lián)接將不包括兩個(gè)表中沒有相互匹配的行。這意味著可能存在我們在查詢中看不到的信息，其中行不匹配。
 

　　我們可以使用SQL查詢來探索這一點(diǎn)：

　　通過運(yùn)行這兩個(gè)查詢，我們可以看到facts表中有些國家的表中沒有對應(yīng)的城市cities，這表明我們可能有一些不完整的數(shù)據(jù)。
 

　　讓我們看一下如何創(chuàng)建查詢以使用新型連接(左連接)來探索丟失的數(shù)據(jù)。
 

　　左聯(lián)接包括內(nèi)部聯(lián)接將選擇的所有行，以及第一(或左)表中與第二表不匹配的任何行。我們可以看到這以維恩圖表示。

　　讓我們看一個(gè)示例，方法INNER JOIN是LEFT JOIN從我們編寫的第一個(gè)查詢中替換為，并與之前的圖中的行進(jìn)行相同的選擇

　　在這里我們可以看到，對于與(237、238、240和244)facts.id中的任何值都不匹配cities.facts_id的行，結(jié)果中仍包含這些行。發(fā)生這種情況時(shí)，cities表中的所有列都將填充空值。
 

　　我們可以使用這些空值將結(jié)果過濾到僅cities包含WHERE子句的國家/地區(qū)。在SQL中與null進(jìn)行比較時(shí)，我們使用IS關(guān)鍵字而不是=符號(hào)。如果要選擇列為空的行，可以編寫：

　　如果要選擇列名不為空的行，請使用：

　　讓我們使用左聯(lián)接來探索cities表中不存在的國家。

　　通過查看我們在上一個(gè)屏幕中編寫的查詢結(jié)果，我們可以看到許多不同的原因，導(dǎo)致國家在以下方面沒有相應(yīng)的值cities：
 

　　a.人口少和/或沒有主要城市地區(qū)(定義為人口超過750,000)的國家，例如圣馬力諾，科索沃和瑙魯。

　　b.摩納哥和新加坡等城市州。

　　c.本身不是國家的領(lǐng)土，例如香港，直布羅陀和庫克群島。

　　d.不是國家/地區(qū)的地區(qū)和海洋，例如歐盟和太平洋。

　　e.真實(shí)的數(shù)據(jù)丟失案例，例如臺(tái)灣。
 

　　每當(dāng)您使用內(nèi)部聯(lián)接時(shí)要謹(jǐn)記自己可能會(huì)排除重要數(shù)據(jù)，這一點(diǎn)很重要，尤其是如果要基于數(shù)據(jù)庫模式中未鏈接的列進(jìn)行聯(lián)接時(shí)，這一點(diǎn)很重要。
 

　　右連接和外連接
 

　　SQLite不支持兩種不常見的聯(lián)接類型，您應(yīng)該注意這些聯(lián)接類型。首先是正確的聯(lián)接。顧名思義，右連接與左連接完全相反。左聯(lián)接包括該子句之前JOIN表中的所有行，而右聯(lián)接包括該JOIN子句中新表中的所有行。我們可以在下面的維恩圖中看到一個(gè)右連接：

　　以下兩個(gè)查詢，一個(gè)使用左聯(lián)接，一個(gè)使用右聯(lián)接，產(chǎn)生相同的結(jié)果。

　　使用正確聯(lián)接的主要原因是當(dāng)您聯(lián)接兩個(gè)以上的表時(shí)。在這些情況下，最好使用右連接，因?yàn)樗梢员苊庵亟M整個(gè)查詢以連接一個(gè)表。除此之外，右聯(lián)接很少使用，因此對于簡單聯(lián)接，使用左聯(lián)接比右聯(lián)接更好，因?yàn)槠渌烁菀组喿x和理解您的查詢。
 

　　SQLite不支持的另一種聯(lián)接類型是完全外部聯(lián)接。完整的外部聯(lián)接將包括聯(lián)接兩側(cè)表中的所有行。我們可以在下面的維恩圖中看到完整的外部聯(lián)接：

　　像右連接一樣，完全外連接通常是不常見的。完全外部聯(lián)接的標(biāo)準(zhǔn)SQL語法為：

　　當(dāng)進(jìn)行聯(lián)接cities并facts使用完全外部聯(lián)接時(shí)，結(jié)果將與上面的左右聯(lián)接相同，因?yàn)橹袥]有cities.facts_id不存在的值facts.id。
 

　　讓我們并排查看每種聯(lián)接類型的維恩圖，這應(yīng)該可以幫助您比較到目前為止我們已經(jīng)討論過的四種聯(lián)接中每種聯(lián)接的區(qū)別。

　　接下來，讓我們練習(xí)使用聯(lián)接來回答有關(guān)數(shù)據(jù)的一些問題。
 

　　尋找人口最多的首都城市
 

　　以前，我們在為查詢結(jié)果指定順序時(shí)使用了列名，如下所示：

　　我們可以在查詢中使用一個(gè)方便的快捷方式，使我們可以跳過列名，而使用列在SELECT子句中的顯示順序。在這種情況下，migration_rate是SELECT子句中的第二列，因此我們可以使用2而不是列名：

　　您可以在ORDER BYor GROUP BY子句中使用此快捷方式。請記住，您要確保查詢?nèi)匀豢勺x，因此對于更復(fù)雜的查詢，鍵入完整的列名可能會(huì)更好。
 

　　讓我們利用我們所學(xué)的知識(shí)，按人口列出前十大首都城市的清單。由于我們對facts沒有相應(yīng)城市的國家不感興趣cities，因此應(yīng)使用INNER JOIN。

　　將SQL連接與子查詢結(jié)合
 

　　子查詢可用于替代部分查詢，使我們能夠找到更復(fù)雜問題的答案。我們也可以連接到子查詢的結(jié)果，就像可以創(chuàng)建表一樣。
 

　　這是一個(gè)使用聯(lián)接和子查詢來生成國家及其首都城市表的示例，就像我們在任務(wù)早期所做的那樣。

　　最初，讀取子查詢可能會(huì)讓人不知所措，因此，我們將分幾個(gè)步驟來分解本示例中發(fā)生的情況。要記住的重要一點(diǎn)是，任何子查詢的結(jié)果總是首先計(jì)算得出，因此我們從內(nèi)而外讀取。
 

　　a.首先計(jì)算紅色框中的子查詢。這個(gè)簡單的查詢從中選擇所有列cities，通過將值設(shè)置capital為1 來過濾標(biāo)記為省會(huì)城市的行。

　　b.在INNER JOIN加入子查詢結(jié)果，如別名c，在facts基于表ON的條款。

　　c.從聯(lián)接結(jié)果中選擇兩列：

　　　　1)f.name，別名為country。

　　　　2)c.name，別名為capital_city。

　　d.結(jié)果僅限于前10行。
 

　　以下是此查詢的輸出：

　　使用此示例作為模型，我們將編寫一個(gè)類似的查詢來查找人口超過1000萬的非首都城市。

　　SQL挑戰(zhàn)：具有聯(lián)接和子查詢的復(fù)雜查詢
 

　　讓我們把以前學(xué)過的所有東西都用起來，并用它來編寫更復(fù)雜的查詢。發(fā)現(xiàn)“ SQL思維”需要一點(diǎn)時(shí)間來適應(yīng)并不少見，因此，如果一開始看起來很難理解該查詢，不要?dú)鈕ur。通過實(shí)踐，它將變得更加容易!
 

　　當(dāng)您使用聯(lián)接和子查詢編寫復(fù)雜的查詢時(shí)，遵循以下過程將有所幫助：
 

　　a.考慮一下最終輸出中需要哪些數(shù)據(jù)

　　b.確定您需要聯(lián)接哪些表，以及是否需要聯(lián)接到子查詢。

　　　　1)如果需要加入子查詢，請首先編寫子查詢。
 

　　c.然后開始編寫您的SELECT子句，然后是join和您需要的任何其他子句。

　　d.不要害怕逐步地編寫查詢，隨心所欲地運(yùn)行它，例如，在編寫外部查詢之前，可以先將子查詢作為“獨(dú)立”查詢運(yùn)行，以確保它看起來像您想要的。
 

　　我們將寫一個(gè)查詢來查找城市中心(城市)人口占該國總?cè)丝谝话胍陨系膰摇＞帉懘瞬樵兊姆椒ㄓ卸喾N，但我們將逐步介紹一種方法。
 

　　我們可以從編寫查詢開始，以匯總每個(gè)國家/地區(qū)城市的所有城市人口。我們可以通過不分組的方式做到這一點(diǎn)facts_id(在下面的示例中將使用限制以使輸出可管理)：

　　接下來，我們將facts表格連接到該子查詢，選擇國家/地區(qū)名稱，城市人口和總?cè)丝?同樣，我們使用限制來保持整潔)：

　　最后，我們將創(chuàng)建一個(gè)新列，該列將城市人口除以總?cè)丝?，并使用WHERE和ORDER BY過濾/排序結(jié)果：

　　您可以看到，雖然我們的最終查詢很復(fù)雜，但是如果逐步構(gòu)建它，則更容易理解。
 

　　SQL聯(lián)接教程：后續(xù)步驟
 

　　在此sql join教程中，我們了解到：
 

　　a.內(nèi)部聯(lián)接和左聯(lián)接之間的區(qū)別。

　　b.正確和外部連接的作用

　　c.如何選擇適合您任務(wù)的聯(lián)接。

　　d.對子查詢，聚合函數(shù)和其他SQL技術(shù)使用聯(lián)接。