0基礎初探SQL (一)

在這個數據宇宙瀚海中星光熠熠，SQL如同一把通往知識寶庫大門的鑰匙。想像一下，每一條SELECT語句都像是一段魔法咒語，喚醒沉睡在數據庫深處的信息。

啟程地圖

程式語言麻瓜如我，使用下列兩個學習材料以實作探索SQL的應用:

1. SQL Murder Mystery (https://mystery.knightlab.com/walkthrough.html): 偵探迷的我意外找到這個這個有趣的網站，雖然是全英 但內容不複雜，下面的範例也會以這個網站的資料為主。
2. 牛客網 > SQL 非技術快速入門 : 我的訴求是不安裝任何東西，開網頁就能直接寫code, 所以註冊了這個網站會員，使用上很直覺。寫不出來時可以去看一眼其他人怎麼寫的 (旁邊的「解題」提供思路 or 找更快的方式)、題目引導上很細緻，甚至會敘述表格該欄資料代表的意思。
   同時，右側執行可以先「自測運行」再提交 (但是他跳錯的提示很rough, 能參考的有限.....還是要自己看)

題目畫面

如果實作中還是困難重重？我還有兩個錦囊妙計：

1. 使用 ChatGPT 4.0 直接詢請AI step by step 教學 (但建議先看完上面資料1 的基礎簡介，最起碼先知道 SELECT, FROM 是什麼)，例如我的prompt: 「我是個小白 可以手把手的教我 引導我寫這題嗎? 我只有很基礎的SELECT FROM 的知識 」
2. 申請外援 哈利波特男友 (aka 其他懂SQL的工程師): 因為看到解題討論中，有許多很fancy的寫法，有些不知是聰明的簡潔還是思慮不周，這時就需要引路人回答。

在這篇文章中，我將一步步揭開：

1. 數據庫和ERD的溫暖擁抱：ERD如何幫助我們親近並理解數據的結構
2. SQL查詢語句的魔法咒語：讓數據按我們的願望輕聲回答
3. 多表關聯的進階魔法：教你如何引領數據探索更多可能

與我一起踏上這段探秘之旅，從理解Entity Relationship Diagram（ERD）的圖譜開始，學習如何解讀數據之間微妙的聯繫。不僅如此，還將揭開SQL基本查詢指令的神秘面紗，學習如何巧妙地與數據對話！

ERD 實體關係圖 (Entity Relationship Diagram)

探索資料瀚海前總要有個地圖吧？若你有這個想法的話，你可能正在尋找 ERD~

ERD 用於判斷SQL資料集中每個關係，當中有金鑰匙的是primary key,也就是資料中的唯一值，藍色箭頭代表 foreign key會拿來跟不同資料表做交叉對照的唯一值。

例如在person 這張表(entity)當中id 是primary key, 這個primary key可以對應到 facebook_event_check_in這張表當中的person_id, person_id就是foerign key。

schema

*這篇文章中, Column 是欄位，就是上述的部分；Row是列。
中國大陸的用詞欄&列好像是相反的 (?!) 如果有看到簡體的材料需要注意下。

Data Schema 與 ERD?

有接觸過資料分析的人，可能聽過 Data Schema, 但這個詞牧牧幾乎沒聽過通用的中文翻譯(?) 有人叫「資料綱要」 或者 「數據模式」。ERD 與Data Schema 雖然都用於描述數據庫結構，但它們之間存在一些主要的區別：

1. 表示方式：
2. • ERD：通過實體（Entity）、關聯（Relationship）和屬性（Attribute）的圖形化表示來描述數據模型。它主要用於數據庫設計的概念階段，幫助設計者和利益相關者理解數據庫結構和業務需求。
   • Data Schema：是更為具體的數據組織方式，包括具體的表、字段、數據類型、約束和索引等詳細信息。數據模式通常包含邏輯模式和物理模式，它們分別描述數據庫的邏輯結構和物理存儲方式。
2. 目的和使用階段：
3. • ERD：主要用於數據庫的設計階段，幫助分析和確定數據間的關係，是一種高層次的、更偏向概念性的模型。它幫助開發者和非技術合作方理解系統的數據結構。
   • Data Schema：在ERD的基礎上進一步發展，轉化為具體的數據庫設計和實現細節。它是實際建立和維護數據庫時需要遵循的藍圖。
3. 細節層次：
4. • ERD：更加抽象，主要關注實體之間的關係，不深入到具體的實現細節，如數據類型或存儲細節。
   • Data Schema：包含更多的細節，如數據類型、長度、默認值、索引、約束條件等，是對數據庫結構的具體和詳細描述。

總結來說，ERD是在數據庫設計的早期階段用來幫助理解和規劃數據結構的工具，而數據模式則是更具體、更技術性的描述，用於指導數據庫的實際建立和維護。ERD可以被視為通往詳細數據模式設計的橋樑。

*埋個坑: 哈利波特說, 對於primary key, foreign key的應用同步需要參照index此一概念，但太多index會降低資料效能。

基礎咒語 | 基本規則和概念

SQL 可讀性很高，不一定需要全大寫SELECT ，但通常都會大寫，也可分成多行增加可讀性。查詢的結尾通常有分號;，表示查詢的結束。

SELECT count(*)

FROM person;

示例解釋1

• SELECT：用於從資料庫中選擇數據。SELECT後面跟著你想要選擇的列（columns）或者使用*代表選擇所有列。
• FROM：指定SELECT語句將要從哪個表格（table）中選擇數據。在此例子中，表格名稱是person。
• count()函數：這是一個聚合函數，用於計算表中的行數count(*)會計算表中的總行數，包括所有列中的空值（null values）。
• WHERE: (帶下詳細說)

示例解釋2

這行SQL代碼的意思是從person表中選取所有的列，但只返回結果集的前10行。這裡的LIMIT 10用於限制查詢結果的數量，以便只獲取前10條記錄。

SELECT * FROM person LIMIT 10;

初探資料

首次接觸一個資料庫或一個新表時，除了先看資料關係圖 (ERD or Data Schema) 或架構 (database structure, like the ERD) ，看看資料中有哪些數值或種類，初步探勘非常重要，可以了解數據的結構、質量和潛在的分析價值。

下面是一些常見的初步探勘步驟，以及如何檢查哪些列可能含有空值（NULL）的方法。

1. 總行數

首先，確定表中的總行數，可以給你一個基本的數據量感覺：

SELECT COUNT(*) FROM person;

2. 探查屬性與唯一值

• 唯一值計數：查犯罪報告中，每個唯一值的欄位屬性為何，可使用DISTINCT type。
• 寫例題的時候也滿常使用的

SELECT DISTINCT type FROM crime_scene_report;

3. 查詢多少非空值

接著，為了檢查哪些列含有空值，可以對每一列進行計數，並與總行數對比。這可以通過對每個欄位使用COUNT(欄位名)來完成，因為COUNT(欄位名)只計數非空的列。

例如，檢查license_id列中有多少非空值：

SELECT COUNT(license_id) FROM person;

如果COUNT(license_id)的結果小於總行數，那麼license_id列中就存在空值。

為了綜合檢查所有列的空值情況，你可以寫一個查詢，對每一列都進行這樣的檢查：

SELECT
    COUNT(id) AS id_non_null,
    COUNT(name) AS name_non_null,
    COUNT(license_id) AS license_id_non_null,
    COUNT(address_number) AS address_number_non_null,
    COUNT(address_street_name) AS address_street_name_non_null,
    COUNT(ssn) AS ssn_non_null
FROM person;

這個查詢會給你每一列非空值的數量。通過與總行數對比，你可以輕易地識別出哪些列包含空值。

其他初步數據探勘

除了檢查空值外，還有幾個其他的初步數據探勘步驟可以給你更深入的數據洞察：

• 唯一值計數：確定每個列有多少唯一值，可以使用COUNT(DISTINCT 列名)。

SELECT COUNT(DISTINCT name) FROM person;

• 數據摘要：對於數值型列，計算基本的統計數據，如最小值、最大值、平均值和標準差。

SELECT MIN(address_number), MAX(address_number), AVG(address_number), STDDEV(address_number) FROM person;

• 分佈情況：了解某些列的分佈情況，如地址或名稱的分佈，可以使用GROUP BY語句和COUNT函數。

SELECT address_street_name, COUNT(*) FROM person GROUP BY address_street_name;

這些步驟可以幫助你建立對數據庫表中數據的基本了解，從而更好地進行後續的分析工作。

常用咒語 | SQL 中的基本元素

SQL中的keyword

除了最常使用的SELECT 和 FROM, 還可以加上其他條件調取資料：

• WHERE語句：WHERE是篩選條件，例如下列是使用要從人名(name)的欄位中，篩選出包含下列三個人名的列(使用**IN**)。

SELECT * FROM person WHERE name IN ('Yessenia Fossen', 'Ted Denfip', 'Davina Gangwer');

*上述 WHERE則是篩選條件也可以寫成 OR 但會比較冗長

SELECT * FROM person WHERE name = 'Yessenia Fossen' OR name = 'Ted Denfip' OR name = 'Davina Gangwer';

• AND語句: 多條件篩選 ，例如此處就是去篩選 type欄位中是'theft' (盜竊) 的 並且 city 是在 'Chicago' (芝加哥)

SELECT * FROM crime_scene_report
WHERE type = 'theft'
AND city = 'Chicago';

• BETWEEN語句: 在SQL中也可以使用 > <等符號，同時也可以使用between

SELECT DISTINCT city
FROM crime_scene_report
WHERE city BETWEEN 'W%' AND 'Z%';

• 如果搜尋時不確定大小寫的話，也可以使用LOWER(X) 或者 UPPER(X) 幫助搜尋，下列的例子會搜尋 SQL City, 但不一定會依照其大小寫，這樣的轉換原因是 ’提供的是字串，通常會是精準搜尋。

SELECT DISTINCT city 
FROM crime_scene_report 
WHERE LOWER(city) = 'sql city';

模糊匹配: LIKE加上 ‘%’ 與 ‘_’ 萬用字元 / 通配符（wildcards）

在SQL中，通配符（wildcards）是用來匹配未知字符的特殊符號，主要用於LIKE語句中實現部分匹配查詢。SQL支持兩種主要的通配符：%和_。

% 通配符

• %：代表任意數量的字符（包括零個字符）。當你在查詢字符串中使用%通配符時，SQL會返回所有在該位置匹配任何字符的結果。

例如，查詢LIKE 'Ca%a'可以匹配"Canada"和"California"，因為這些值以"Ca"開頭並以"a"結尾，中間可以是任意數量的字符。

_ 通配符

• _：代表恰好一個任意字符。當你在查詢字符串中使用_通配符時，SQL會返回在該位置有任意單一字符的結果。

例如，查詢LIKE 'B_b'會匹配"Bob"和"Bub"，因為這些值在"B"和"b"之間恰好有一個任意字符。但它不會匹配"Babe"（因為中間有兩個字符）或"Bb"（因為中間沒有字符）。

使用通配符時的注意事項

當使用通配符進行模糊匹配時，不能使用等於符號（=），而應該使用LIKE關鍵字。這是因為等於符號用於精確匹配，而LIKE則設計用來與通配符一起使用，實現模糊匹配。

例如，如果你想找到所有名字以"J"開頭的人，你可以使用以下SQL查詢：

SELECT * FROM person WHERE name LIKE 'J%';

這樣，你就可以獲得所有名字以"J"開頭的記錄，無論其後跟隨多少字符。通配符使得SQL查詢更加靈活，能夠應對只知道部分信息的情況。

那如果現在運營團隊想查看所有大學中帶有「北京」的用戶信息，提取相應數據：

SELECT device_id, gender, age, university FROM user_profile
WHERE university LIKE '%北京%';

其他延伸示例

1. select * from emp where name like '[ac]%';
2. • 這個查詢使用方括號來定義一個字符集合，它會匹配所有在name列中以'a'或'c'開頭的記錄，後面可以跟隨任意數量的字符。%代表任何數量的字符。
2. select * from emp where name like '[!ac]%';
3. • 這個查詢將匹配所有在name列中不以'a'或'c'開頭的記錄，後面可以跟隨任意數量的字符。[!ac]表示“不是'a'或'c'的任何單一字符”。

在MySQL中，會用REGEXP或RLIKE來替代LIKE並使用正則表達式來達成相同的功能。

SQL的學習之路漫漫，通過探索希望你也能把SQL看作是一種溝通方式，一種讓數據說話的技巧，而不僅僅是一堆無機的代碼。希望這篇文章讓你對ERD的視覺語言和SQL查詢的基本節奏有了初步的理解，並開始感知到那些冷冷的數字和指令背後的生命力。

在下一篇文章中，將進一步深化咒語，探索如何利用更多的SQL技巧來進行更高級的數據操縱。我們將學習如何讓數據以更複雜的方式呈現，期待在這個遼闊的數據世界中，帶著探索的熱忱和對數據世界的好奇，讓我們繼續在這片數據的大海裡航行，尋覓著那些尚未發現的知識寶藏！