CodeFarmer 技術週報 #24 - SQL 基礎入門 (1)

猴子也學得會的 SQL，但猴子並不想會。

嗨嗨大家，歡迎閱讀最近加班已成常態之說好要寫雙週報的第 24 期 CodeFarmer 技術週報。

最近剛好回頭在看一些 K8s 中一些不太懂的知識點像是 istio sidecar，原本想著要來延續之前的 K8s 系列，但上一期中說好要來了解一些 DB 的基礎知識，所以就來另闢個戰場先。

最近工作上苟延殘喘地完成了一個後端相關的任務，過程中踩到不少雷，像是對於純 SQL 不夠熟悉、data type 定義與轉換 (尤其時間、時區的 data type 完全可以花一篇文章篇幅再仔細搞清楚)、讀取大量資料時應注意的記憶體效能問題等等，因此覺得該抽空來補強一下這方面的硬實力。

正所謂千里之行始於足下，這篇就先由淺入深來複習一下各種常見的 SQL 語法吧，希望再幾個月後至少可以縱橫在各種 DB 管理工具甚至是 SQL CLI 中，隨手撈出一段想看的資料。

PS. 敘述參考自小賴的這篇文章，不知為什麼下這種入門的標題第一個冒出的註解就是這句話。

SQL 學習路徑

身為一個前端經驗比較多，對於 SQL 有一些淺薄的記憶或概念如我的人來說，搭配著 SQLBolt 的章節安排，覺得由淺入深可以這樣學習：

基礎語法

• 基礎語法

  • SELECT、FROM、WHERE

  • 限制條件：BETWEEN … AND …, LIKE… 等

  • 排序：ORDER BY … ASC/DESC

  • 限制筆數：LIMIT … OFFSET …

  • 去重複：DISTINCT

• Multi-table 的 JOIN 查詢

  • INNER JOIN

  • LEFT JOIN

  • RIGHT JOIN

  • FULL JOIN

  • SELF JOIN

• 寫入操作

  • INSERT

  • UPDATE

  • DELETE

進階語法

• Queries with aggregates

  • COUNT

  • MIN/MAX

  • AVG

  • SUM

  • GROUP BY … HAVING

• DDL (Data Definition Language)

  • CREATE TABLE

  • ALTER TABLE

  • DROP TABLE

  • TRUNCATE TABLE

• 其他小撇步

  • 確認 table 的 schema：DESCRIBE

  • 確認當前時區：SELECT NOW()

  • 擷取 JSON 欄位內特定值：JSON_EXTRACT()、JSON_VALUE()

  • 數值轉成 16 進位表示：HEX()

  • 資料型別轉換：CAST()

  • … 等等歡迎 SQL 高手分享各種常見的小撇步

進階觀念

• SQL Subquery

• Database Index

• Foreign Keys, Dates and Times, Views, Sequence…

• TCL (Transaction Control Language)

• … 等等關於 SQL、DB、data type 等等的學習也是水很深

基本上好像可以先把基礎與進階語法摸熟就可以騙吃騙喝了，之後就是看碰到什麼不懂加強什麼這樣。

1. 基礎語法

列了那麼多還是從最基本開始練起吧，學語法這東西還是做中學最快，這裡懶得想範例直接參考前幾週提到的好用練習網站 — SQLBolt 來搭配學習。

1-1. SELECT

參考下圖的五個小題目，想從 movies 這張表去撈出想要的資料：

很基本的可以寫出以下的答案，基本上的大觀念就是從 (FROM) 哪張表去選擇 (SELECT) 哪些欄位出來，如果要把所有欄位選出來就是簡單用個 * 解決

以下也用 SQL 中的註解語法 (- -) 來標示每個小題：

-- 找出每部電影的標題欄位
SELECT title FROM movies;

-- 找出每部電影的導演欄位
SELECT director FROM movies;

-- 找出每部電影的標題、導演欄位
SELECT title, director FROM movies;

-- 找出每部電影的標題、上映年份欄位
SELECT title, year FROM movies;

-- 找出每部電影的所有欄位
SELECT * FROM movies;

BTW，如果是 SQL 超新手的話，對於 SELECT、FROM、WHERE 這些關鍵字其實寫成大小寫都可以，但一般為了方便區分關鍵字與選取欄位、表等易讀性，建議用全大寫來表示。

如果是在一些 DB 管理工具上像是 TablePlus、DBeaver 等寫 SQL 撈資料時，也都會有 syntax highlight (語法突顯) 的效果。

本篇的範例中因為 Substack 不支援 syntax highlight，用粗體來表示更好讀一些。

1-2. 有條件地拉資料

以上是最基本的想從某個資料表 (table) 中去拉出某個或全部欄位 (column) 資料的 SQL 語法，那如果想搜尋某些範圍的資料，就需要搭配 WHERE 來接一些條件式拉出某個範圍的資料。

直接看個範例：

-- 找出電影 id 為 6 的所有資料
SELECT * FROM movies WHERE id = 6;

-- 找出電影上映年份介於 2000~2010 間的所有資料
SELECT * FROM movies WHERE year BETWEEN 2000 AND 2010;

-- 找出電影上映年份不介於 2000~2010 間的所有資料
SELECT * FROM movies WHERE year NOT BETWEEN 2000 AND 2010;

-- 找出前 5 筆電影資料
SELECT title, year FROM movies LIMIT 5;
-- or
SELECT title, year FROM movies WHERE year <= 2003;

其中的第 4 小題小卡了一下，原來是想要拉出前 5 個 row 資料的 title 與 year 欄位，後來對照著正解用的是 WHERE year <= 2003 的做法，這已經算是透過觀察資料特徵在做條件限制了吧。

1-3. LIKE

接下來練習下模糊搜尋的 LIKE：

-- 找到電影標題中含有 "Toy Story" 關鍵字的電影
SELECT * FROM movies WHERE title LIKE '%Toy Story%';

-- 找到導演為 "John Lasseter" 的電影
SELECT * FROM movies WHERE director = 'John Lasseter';

-- 找到導演不是 "John Lasseter" 的電影
SELECT * FROM movies WHERE director != 'John Lasseter';

-- 找到電影標題中含有 "WALL-*" 關鍵字的電影
SELECT * FROM movies WHERE title LIKE 'WALL-_';

如果想搜尋某個欄位中的關鍵字，可以用 LIKE 這個 operator 搭配 % 或 _ 等等萬用字元 (wildcards) 來做模糊搜尋：

• %：代表 0 到多個任意字元

• _：代表 1 個任意字元

• []：代表 1 個在中括號內的任意字元，舉例 a[bcd]，則 ab, ac, ad 都符合條件

• 更多 wildcard 資訊可搜尋關鍵字有許多更完整資源

1-4. 排序與限制筆數

接著來練習兩個語法：

• ORDER BY … DESC/ASC：根據某個欄位以字母與數字的順序 (alpha-numerically) 來遞增 (ASC) 或遞減 (DESC) 排序。如果資料裡有大小寫、國際文字這種進階狀況，可另外搭配 COLLATE 關鍵字來指定排序規則。

• LIMIT … OFFSET：限制取出幾筆，以及要從前面位移幾筆。概念上有點接近前端在做分頁一樣，用來決定每個 pagination size 多大，現在在第幾頁。

-- 列出所有不重複的導演資料，並以導演名稱做遞增字母排序 (A to Z)
SELECT DISTINCT director FROM movies ORDER BY director ASC;

-- 列出最近 4 部釋出的電影
SELECT * FROM movies ORDER BY year DESC LIMIT 4;

-- 列出最早釋出的 5 部電影
SELECT * FROM movies ORDER BY title ASC LIMIT 5;

-- 列出最早釋出的第 6~10 部電影
SELECT * FROM movies ORDER BY title ASC LIMIT 5 OFFSET 5;

開始有點難度了，記錄一下筆記：

• 第 1 題需要搭配 DISTINCT 來移掉重複的資料

• 第 2 題要找 4 部最近釋出的電影，因此需要根據年份做遞減 (DESC) 排序並搭配 LIMIT 來篩出前 4 部

• 找前 5 筆資料看到有一種寫法會寫成 SELECT TOP 5 * FROM movies，查了下發現是 Microsoft SQL Server 特有的寫法，一般常見的 MySQL、PostgreSQL 都是用 LIMIT。甚至還有另一種可能會碰到的 Oracle DB 體系是使用 ROWNUM。

1-5. 綜合練習

再來一題進階的做綜合練習，這次用另一張表來練習，會是關於北美不同國家與城市的經緯度、人口資訊：

-- 列出所有加拿大的城市與對應人口數
SELECT city, population FROM north_american_cities WHERE country = 'Canada';

-- 用緯度從北至南排序所有美國城市
SELECT * FROM north_american_cities WHERE country = 'United States' ORDER BY latitude DESC;

-- 列出所有比芝加哥更西邊的城市，並從西邊排序至東邊
SELECT * FROM north_american_cities WHERE longitude < -87.629798 ORDER BY longitude ASC;

-- 列出人口數前兩大的墨西哥城市
SELECT * FROM north_american_cities WHERE country = 'Mexico' ORDER BY population DESC LIMIT 2;

-- 列出人口數第三、第四大的美國城市
SELECT * FROM north_american_cities WHERE country = 'United States' ORDER BY population DESC LIMIT 2 OFFSET 2;

這個練習題可能需要參雜一些地理知識，像是北半球國家的緯度 (latitude) 會是正數，越大代表越北；而東半球經度 (longitude) 會是正數，越大代表越東；反之兩者對應的負數就是越南邊或越西邊。

第 3 小題中要找到比芝加哥更西邊的所有城市，以為用 magic number 是個爛方法，結果解答也是一樣，後來問了 GPT 後學到原來也可以用 Subquery 的方式來查，又學到一招：

SELECT c.*
FROM north_american_cities c
JOIN (
  SELECT longitude
  FROM north_american_cities
  WHERE city = 'Chicago'
  LIMIT 1
) AS chicago
  ON c.longitude < chicago.longitude
ORDER BY c.longitude ASC;

小結

今天時間不小心有點晚了先寫到這，這篇中練習了許多 SQL 基礎語法，下一篇應該會回過頭來補一些 K8s 的知識，之後幾篇再來交錯插入 SQL 學習筆記。

其實更建議想練習的讀者直接用 SQLBolt 即時用互動方式練習與接受回饋是否寫的正確應該會比純閱讀來的有效，以上也就是複習 SQL 語法的一些筆記。畢竟我自認自己的學習方式是更習慣 learning by 輸出文字的人，所以也才會用這個技術週報督促自己每週多少學一些自己不足的基本知識。

---

以上就是這期週報的所有內容了。若內容有什麼問題與討論也都歡迎透過以下管道與我交流，或直接留言與回覆這封電子信我也能收到：

• IG：@codefarmer.tw

• Email：codefarmer.tw@gmail.com