SQL

序言

雖然我是前端，但我也是想試試看後端的。某次面試，公司要找後端開發，但寫到 SQL 時，我才發現自己對後端最重要的資料庫一無所知。

很明顯，這次面試被打槍了。有沒有人懂非對稱加密怎麼做的（小聲）

要學 SQL 的話，除了自己架資料庫外，SQLZoo 這網站也是個好資源。

2020/03/22 增筆：我最近發現以探案為主題的sql-mysteries也頗好玩：遊戲用生動的故事與查探機制，來教人怎麼用 JOIN 指令，關聯其他的資料。

資料庫資訊

資料庫（Database）下面會有資料表（Table）；而資料表的基本架構和表格一樣，以欄與列構成。而這些資料表，都要有一個主鍵（Key value）以資辨識。

關聯式資料庫

在關聯式資料庫（Relational database）的體系下，開發者可以透過資料表的鍵值與外來鍵（Foreign key），與其他的資料表結合。只不過，一個資料表的鍵值，只能對應另一個資料表的外來鍵。如果需要對應多個資料表的外來鍵，也就是被稱為多型關聯（Polymorphic Associations）的東西......我還沒看到解決辦法，但似乎與後端程式語言有關？ 🤔

指令備查

以 MySQL/MariaDB 系列指令為準。

• SELECT：選取需要的欄位。欄位中間以 , 分開。例如 SELECT name,gdp。
• FROM：FROM 後面要接的是資料表名稱。假設 sqlzoo 資料庫下面有 world 資料表的話，可以用：
  • FROM world: 這會在 sqlzoo 資料庫下面尋找 world 資料表。
  • FROM sqlzoo.world: 這會在 sqlzoo 資料庫下面選取 world 資料表。
• WHERE：限定搜尋條件。資料表查找的靈魂。
  • 可以在 WHERE 裡面，用其他資料來查找資料：如 WHERE population > (SELECT population FROM world WHERE name='Canada') 就是找出所有人口多於 Canada 的國家。
• LEFT( 欄位名稱, 給定字數 )：針對文字欄位，傳回指定數字的字元。例如 LEFT( name, 5 ) 就會給出 Afgha, Cuba, Japan, Zimba 這樣的字元。
• =, <>：= 是等於、 <> 是不等於。報告完畢。
• LIKE：模糊字詞搜尋，而不只有單純的全有全無。
  • %會配對任何字元，包括空白、直到盡頭； _則會配對任何一個字元。
  • 配對字元放在特定字元前面，就會去查找特定字元前面的字元，反之亦然。
  • Java% 會配對到 Java, JavaScript, Java island。
  • %SQL% 會配對到 MySQL, SQL Server。
  • __SQL 會配對到 MySQL。
• ROUND( 欄位名稱, 初始進位數 )：針對數字欄位，傳回指定的小數。小數取捨為四捨五入。例如 1234.56 這數字：
  • ROUND( 1234.56 ), ROUND( 1234.56, 0 )：1235
  • ROUND( 1234.56, 1 )：1234.6
  • ROUND( 1234.56, 2 )：1234.56
  • ROUND( 1234.56, 4 )：1234.5600
  • ROUND( 1234.56, -1 )：1230
  • ROUND( 1234.56, -2 )：1200
• 邏輯配對「或與非」（OR, AND, NOT, XOR）。比較特別的是 XOR：它會把全都符合與全都不符合過濾掉。
• LENGTH( 輸入名稱 )：可以給出該輸入的字串長度。例如 LENGTH( name ) 就會顯示出各國的字串長度Afghanistan 為 11、Austria 為 7 之類的。
• 數字欄位可以被計算，例如 gdp/population。
• LIMIT 數字：資料庫可能一個條件內有很多資料。如果預期只想抓到幾個資料，可以下 LIMIT 關鍵字：例如 WHERE population > 100000000 LIMIT 3，就會給出三個人口超過 100000000（一億）的國家。
• ORDER BY：按照某種順序排序。後面能下關鍵字：可以是數字、也可以是羅馬字母。
  • DESC 為升序、ASC 為降序。
• JOIN：與其他資料表作結合，產出的資料表就會同時有兩個資料表的資料了。這個指令需要配合幾個指令：
  • 首先你要決定用什麼條件，與其他資料表做結合。決定好後，再用 ON 來決定這個條件。
  • 結合方法
    • INNER：必須要在前述的兩者吻合時，才收入這筆資料。不吻合的，就會自動不列入。
    • CROSS：以出現順序，無條件結合資料。
    • LEFT：以出現順序，無條件結合資料。不吻合的，以左邊（被結合的）資料表為準。
    • RIGHT：以出現順序，無條件結合資料。不吻合的，以右邊（要結合的）資料表為準。
  • ON：與其他資料表作結合的依據。如果不下這個指令，則資料表會以出現順序，無條件結合資料（也就是 CROSS JOIN）；但如果下了，就會按照兩個資料表所指定的數值為結合依據：例如說 JOIN something ON something.sid = another.thing 的話，資料將比對 something 的 sid 和 another 的 thing，並將吻合兩者的欄位，當作是同一筆資料。
• INSERT INTO 是需要插入數值的指令；而 VALUES 則是需要插入數值的資料。
  • 也就是說你會這樣用：INSERT INTO some VALUES (1,'PIECE');。

Nested query & subquery (巢狀查詢與子查詢)

捲毛蔡：「在 SELECT 指令內再放一個 SELECT 指令查詢；通常放在 SELECT 後面的 WHERE 子句，可取得詳細查詢條件。」

-- https://learnsql.com/blog/sql-nested-select/
SELECT * FROM Students WHERE Students.GPA > ( SELECT AVG(Students.GPA) FROM Students );
SELECT AVG(Classes.number_of_students) FROM Classes WHERE Classes.teacher_id IN (
    SELECT Teachers.id FROM Teachers WHERE Teachers.subject = 'English' OR Teachers.subject = 'History'
);

People id Name Age 1 Titan 18 2 John 6 3 Alex 40 4 Back 36 5 Deed 22

Marriage id spouse_id 1 5 5 1 3 4 4 3

-- To find someone who is single
SELECT * FROM People WHERE People.id NOT IN ( SELECT Marriage.spouse_id FROM Marriage );

-- Show:
-- id 	Name 	Age
-- 2 	John 	6

• https://learnsql.com/blog/sql-nested-select

correlated or synchronized sub-query （相關子查詢或同步子查詢）

「我們可以在子查詢，參閱外部查詢的數值。我們為表格再命名，便可以分別內外兩個不同的表格。」

連結

• SQLZoo
• Basic SQL Statements