【SQL Basic】基礎入門語法與觀念
SQL 是大部分數據分析相關職位最必備、最重要的能力，在面試中也經常有複雜的白板考題出現，本次一系列 SQL 課程再次邀請到郭耀仁老師帶社員認識資料庫與 SQL 的運用！
本篇文章的幾個重要 take away：
- 資料庫相關概念
- SQL 基本語法、使用時機
本文目錄：
Part 1：SQL、資料庫介紹
1.SQL 介紹
SQL 全名為 Structured Query Language（結構化查詢語言），是一個能針對資料庫中的數據進行 資料操作 的語言。早於 1970 年代問世，50 年後仍然是資料科學與軟體開發從業者最重要的技能之一。
具體而言， 資料操作 可以再細分為創造 Create、查詢 Read、更新 Update、刪除 Delete 四個動詞，舉例來說，在使用任何的網頁或手機應用程式時，我們的滑鼠點擊與手勢觸控都會被轉換成 CRUD：
- 創造 Create：發佈新的動態。
- 查詢 Read：瀏覽追蹤對象的動態。
- 更新 Update：編輯先前發佈動態的內容。
- 刪除 Delete：撤掉先前所發佈的動態。
2.資料庫介紹
資料庫（Database）是儲存在電腦中的資料集合，我們可以透過撰寫 SQL 有效率地對資料庫中的數據進行 資料操作。大至銀行的存款資訊與交易資訊、小至手機的通話紀錄與通訊錄，背後都有資料庫在運作支撐。
具有以下兩個特徵的資料集合會被稱為資料庫：
1. 資料觀測值具有屬性（attributes）。（如下方右圖）
2. 儲存有元資料（Metadata）。元資料（Metadata）常見的解釋為「data about data」、「描述資料的資料」。
3.資料庫管理系統介紹
資料庫管理系統全名為 DataBase Management System（DBMS），透過資料庫管理系統撰寫 SQL 語法，使 CRUD 「資料操作」可更加「自動化」且「規模化」 。
1.自動化：不需要透過人工就能夠更新、編輯、刪除資料。（安全、隱私要求較高時，較無法實現）
2.規模化：能夠讓成千上萬個使用者同時更新、編輯、刪除資料。
資料庫管理系統可以分為兩種大類：
- 關聯式資料庫管理系統（RDBMS, Relational Database Management System）
- 非關聯式資料庫管理系統（NoSQL DBMS, Not only SQL Database Management System）
常見的關聯式資料庫管理系統有甲骨文的 Oracle Database、微軟的 SQL Server、IBM 的 DB2，開放原始碼的 SQLite、MySQL、PostgreSQL，以下介紹會以 SQLite 為例，以常見關聯式資料庫管理系統都支援的標準 SQL 基本語法為主。
SQL、關聯式資料庫與關聯式資料庫管理系統示意圖
Part 2：環境安裝
1. 下載 SQLite
2. 準備資料集 .db
3. 連結資料集
4. 將 SQLiteStudio 與資料庫相連結
確認開發及運行環境已經安裝妥當後，這是 SQL 初學者的第一個查詢敘述。
Part 3：SQL 基礎語法
一、以 SELECT 與 FROM 敘述從資料表選擇
儲存在資料庫中的資料表是有兩個維度的資料樣態
- 第一個維度稱為觀測值（Records），有時亦稱為列（Rows）。
- 第二個維度稱為變數（Variables），有時亦稱為欄（Columns）。
1. SELECT 與 FROM 敘述是從指定的資料表中選擇欄位的查詢語法，SELECT * 表示選擇資料表的「所有」欄位
2. SELECT column_name 表示只選擇指定欄位
3. 若想指定多個欄位，可用逗號 , 將多個欄位名稱隔開
二、替查詢結果的欄位取別名
使用 AS 敘述為查詢結果的變數取個新名字
三、查詢結果僅顯示獨一值
使用 DISTINCT 敘述省略查詢結果中重複的資料(排除重複值)
1. 未使用 DISTINCT 的時候，會抓出該欄位所有列
2. 使用 DISTINCT 的時候，則只保留該列不重複的值
注意事項- 用分號; 表示一段 SQL 的結束。
- SELECT 與 FROM 大小寫沒有差別。
- 寫作一段 SQL 可以單行或者換行。
Part 4：計算欄位 (calculated fields)
在沒有 FROM 的情況下，SELECT 敘述能夠單獨存在於一段 SQL 之中
在 SELECT 敘述後面擺放的常數被稱為 literal values，常見類型有：
- 整數（INTEGER）
- 帶小數點的浮點數（REAL）
- 用單引號標記的文字（TEXT）
- 空值、遺漏值（NULL），Null 可能是空值，或長度 0 的資料點
即便在資料表中沒有儲存常數，依然能透過 literal values 在查詢中使用
1. 數值運算符
針對整數（INTEGER）與帶小數點的浮點數（REAL）可以使用數值運算符衍生計算欄位（Calculated fields）
- 相加：+
- 相減：-
- 相乘：*
- 相除：/
- 回傳餘數：%
- 優先運算：()
在使用 / 相除兩個整數的時候要特別注意所衍生的欄位依然會以整數類型存在，若希望衍生欄位是浮點數類型，則要為分子或者分母加上小數點。
2. 文字運算符
針對文字（TEXT）可以使用文字運算符衍生計算欄位，兩垂直線（可透過 Shift + \ 按出來）|| 能夠連接文字。
Part 5：函數 (functions)
函數是預先被定義好的運算處理邏輯，透過它的作用，能夠將「輸入」對應為「輸出」，進而完成數值計算、字串操作與數值聚合等任務。
函數的運作有四個組成：
- 函數的名稱。
- 輸入。
- 參數。
- 輸出。
在 SQL 中的函數可依照功能分為：
- 用來轉換資料類型。
- 用來計算數值。
- 用來操作文字。
- 用來操作日期時間。
- 用來彙總資訊。（另一系列）
一、用來轉換資料類型的函數
1. 使用 CAST() 函數可以將查詢結果的資料類型轉換為指定資料類型，如讓分子或分母變成浮點數，使數值運算結果為浮點數
2. 使用 COALESCE() function 可以將空值（或稱遺漏值）轉換為指定的資料
二、用來計算數值的函數
1. 使用 ROUND() 函數可以調整查詢結果的小數點位數
2. 使用 ABS() 函數可以將數值調整為絕對值
三、用來操作文字的函數
1. 使用 INSTR() 函數可以判斷指定特徵出現在文字中的第幾個位置
2. 使用 LENGTH() 函數可以計算文字中有幾個字元（包含空格、標點符號）
3. 使用 TRIM() 函數可以去除文字前後多餘的空白
4. 使用 REPLACE() 函數可以將文字中的特徵取代為指定內容
5. 使用 SUBSTR() 函數可利用索引值將文字中的指定段落擷取出來
6. 使用 LOWER() 與 UPPER() 函數可以調整英文的大小寫
四、用來操作日期時間的函數
1. 使用 DATE() 函數操作日期，*args 為不定參數
標準的日期格式為 YYYY-MM-DD，專有名詞稱之為 ISO8601 格式
- 四位數的西元年。
- 二位數的月。
- 二位數的日。
- 中間以減號 — 相連。
2. 使用 TIME()函數操作時間
標準的時間格式為 HH:MM:SS，專有名詞同樣稱之為 ISO8601 格式
- 二位數的小時。
- 二位數的分鐘。
- 二位數的秒。
- 中間以冒號
:相連。
3. 使用 DATETIME()函數操作日期時間
標準的日期時間格式為 YYYY-MM-DD HH:MM:SS，專有名詞同樣稱之為 ISO8601 格式
4. 使用 STRFTIME()函數調整日期、時間或日期時間的格式
常用的日期、時間與日期時間格式（注意大小寫）
- %d：二位數的日（01–31）
- %H：二位數的小時（00–24）
- %j：一年中的第幾天（001–366）
- %m：二位數的月（01–12）
- %M：二位數的分（00–59）
- %S：二位數的秒（00–59）
- %w：一星期中的第幾天（0–6）
- %W：一年中的第幾週（00–53）
- %Y：四位數的年（0000–9999）
五、用來彙總資訊的函數
整體而言，函數可以粗分為兩大類：
- 通用（Universal functions）
- 聚合（Aggregate functions）
常用的聚合函數
AVG(column_name)：計算變數的平均數COUNT(column_name)：計算變數的「非」遺漏值數COUNT(*)：計算資料表的觀測值數MAX(column_name)：計算變數的最大值MIN(column_name)：計算變數的最小值SUM(column_name)：計算變數的加總
Part 6：排序、簡化查詢結果
一、以 ORDER BY 敘述排序查詢結果
截至目前，以 SELECT 與 FROM敘述所獲得的查詢結果是依據其存在資料表中的順序所呈現。
1. 加入 ORDER BY敘述能夠指定變數來排序呈現查詢結果
排序方式有兩種：
- 遞增（或稱升冪）排序
- 遞減（或稱降冪）排序
預設的排序方式為遞增，如想明確表達「遞增」排序，可加上保留字 ASC （Ascending），若指定排序的欄位是文字（TEXT）類型，遞增就是由 A 至 Z。如希望讓查詢結果「遞減」排序，必須加上保留字 DESC（Descending）
2. 使用多個變數排序
在 ORDER BY敘述後，可以指定多個變數搭配不同的排序方式，指定多個變數排序時，會依照先指定的變數排序，在排序相同的情況下再依據後指定的變數排序。
3. 指定衍生計算變數排序
在 ORDER BY敘述後除了能夠指定資料表中的變數以外，還可以指定衍生計算欄位（Calculated fields）
二、以 LIMIT 敘述顯示部分查詢結果
1. 加入 LIMIT敘述僅呈現查詢結果的前 m 筆觀測值
2. 結合 LIMIT敘述與 ORDER BY敘述，就可以進行「前 m 高」或者「前 m 低」的資料分析
Part 7：以 WHERE 敘述篩選資料
截至目前，我們所撰寫的 SQL 幾乎都是將資料表中「所有」的觀測值回傳為查詢結果。在應用場景中，更常見的是需要資料表中「特定」的觀測值，如：
- 在 nba 資料庫中找出 Fantasy Game 想要選的球員。
- 在 imdb 資料庫中找出 1994 年上映的經典電影。
- 在 twElection2020 資料庫中找出台北市的資料。
加入 WHERE 敘述能夠以「條件」作為篩選觀測值的依據
撰寫條件（condition）的時候，我們要暸解兩個觀念：
- 比較運算符：能夠產生布林的運算符號。
- 布林（Boolean）：用來表示比較結果的兩個值（True: 1、False: 0）。
一、基礎比較運算符
- =：相等
- !=：不相等
- >：大於
- <：小於
- >=：大於等於
- <=：小於等於
1. 比較結果為「真」的布林，SQLite 以 1 表示
2. 比較結果為「假」的布林，SQLite 以 0 表示
針對資料表的欄位撰寫條件會產生由布林所組成的查詢結果，將條件撰寫在 WHERE 敘述之後，會篩選布林為真（1）的觀測值回傳，撰寫條件不只可以針對文字類型的變數，亦能夠針對數值類型的變數
二、特徵比對
除了基礎比較運算符，對文字類型的變數撰寫條件時，還能夠使用具備特徵比對（Pattern matching）性質的比較運算符 LIKE。
使用 LIKE 比較運算符的時候需要搭配萬用字元（Wildcards）
- %：表示任意文字，包含空字串
- _：表示剛好一個文字
三、邏輯運算符
當 WHERE 敘述之後的條件超過一個的時候，必須使用邏輯運算符連結這些條件。
基礎的邏輯運算符有：
AND代表條件的交集：使用AND連結兩個條件時，要兩皆為真（1）才為真，其餘狀況均為假（0），可以使用BETWEEN邏輯運算符來縮減程式碼。
OR代表條件的聯集：使用OR連結兩個條件時，要兩皆為假（0）才為假，其餘狀況均為真（1），可以使用IN邏輯運算符來縮減程式碼。
NOT反轉真假：使用NOT將條件的比較結果反轉，亦即真假互換
四、遺漏值的比較運算符
NULL 遺漏值（或稱空值）不適用基礎比較運算符
要判斷是否為遺漏值，必須使用 IS NULL 作為比較運算符
Part 8：以 CASE 敘述衍生欄位
條件除了能夠運用在 WHERE 敘述篩選資料，另外一個常見的應用場景是在 CASE 敘述來衍生欄位。
當條件有交集的時候，撰寫 CASE 敘述要特別注意順序(先符合的條件會先歸類)，如果不想特別注意順序，亦可以將條件設計為互斥
一、以 CASE 敘述衍生的欄位排序查詢結果
除了能夠在 SELECT 加入 CASE 敘述，亦能夠在 ORDER BY 敘述加入。
也能僅在 ORDER BY 敘述中加入 CASE，不用先加 SELECT
二、以 CASE 敘述衍生的欄位篩選資料
除了能夠在 SELECT 以及 ORDER BY 加入 CASE 敘述，亦能夠在 WHERE 敘述加入。
Part 9：分組聚合
一、使用分組 GROUP BY 敘述的時候可以視為 DISTINCT 與 ORDER BY 兩個敘述同時作用
在「函數」的章節中，我們介紹過一種「用來彙總資訊」的函數，稱為聚合函數（Aggregate functions）。
單獨使用聚合函數的時候，是將一整欄變數的資訊彙總後輸出。
1. 結合聚合函數與 GROUP BY 敘述可以便捷地完成分組聚合
2. 作用在「分組聚合結果」的 HAVING 敘述
GROUP BY 敘述後可以加入不只一個變數
二、以 HAVING 敘述篩選分組聚合結果
Part 10：子查詢 (subquery)
在「函數」的章節中，我們看過函數中包含著其他函數的使用方法，前一個函數的輸出，成為下一個函數的輸入。在一段 SQL 中包含著另外一段 SQL 的使用方法，被稱為子查詢（Subquery）。
在什麼樣的情境下我們會想使用子查詢呢？
- 查詢的篩選條件是必須要先做一個查詢才能得知。
- 查詢的計算內容必須要先做一個查詢才能得知。
- 查詢所需要的資料來自不同的資料表。
查詢的篩選條件是必須要先做一個查詢才能得知，舉例來說，我們想知道 top_rated_movies IMDb 最高評價的 250 部電影中最高評價的電影是哪一部？
- 先查詢最高評價的數值為多少。
- 再以前一個查詢結果作為篩選條件。
查詢的計算內容必須要先做一個查詢才能得知，舉例來說，我們想知道 top_rated_movies IMDb 最高評價的 250 部電影中，在 2000 年之後上映的電影佔比為多少？
- 先查詢在 2000 年之後上映的電影有幾部。
- 再以前一個查詢結果作為計算內容。
查詢所需要的資料來自不同的資料表，舉例來說，我們想透過 players 球員基本資料表與 careerSummaries 球員生涯攻守數據資料表得知誰是聯盟中的總得分王？
- 先查詢最高的生涯總得分是多少。
- 再以前一個查詢結果作為篩選條件尋找球員 ID。
- 再以前一個查詢結果作為篩選條件尋找球員姓名。
常見的子查詢可以分為三類：
- 將一段 SQL 查詢結果作為資料表。
- 將一段 SQL 查詢結果作為條件。
- 將一段 SQL 查詢結果作為衍生計算欄位。
一、將一段 SQL 查詢結果作為資料表
在 FROM 敘述後接另外一段 SQL 查詢
二、將一段 SQL 查詢結果作為條件
在 WHERE 敘述後接另外一段 SQL 查詢
在 HAVING 敘述後接另外一段 SQL 查詢
三、將一段 SQL 查詢結果作為衍生計算欄位
在 SELECT 敘述後接另外一段 SQL 查詢
重點回顧：SQL 常用語法
SQL 的概念較少於生活中出現，即使是最基本的語法，也要費一番工夫才能理解完全，本次請郭耀仁老師講解 SQL 基礎的主題，帶領各位入門，相信透過開源軟體以及資料庫等資源，也能快速建立練習、精進的環境，在 SQL 使用上更加得心應手！
郭耀仁老師也於 Hahow 開設 SQL 與資料分析的課程，有更詳盡、完整的相關教學，提供各位作為進修資源參考：
- SQL 的五十道練習：初學者友善的資料庫入門 https://hahow.in/cr/sqlfifty
- 如何成為資料分析師：從問題解決到行動方案 https://hahow.in/cr/dajourney
歡迎各位拍手交流，NTUDAC 也會持續在 Medium、Facebook 與 LinkedIn 更新社課的資訊，歡迎對資料有興趣的大家持續關注，跟我們交流想法~
