今年鐵人賽的最後一天讓我們好好用幾個基礎的 SQL statements 練習在關聯式資料庫裡查詢特定的資料吧!

基礎 SQL statements

• SELECT
• DISTINCT
• WHERE
• LIKE
• ORDER BY
• LIMIT

使用工具小精靈 - DB Browser for SQLite

從 file 新建資料庫

選擇 File → 選取 Import: 有兩種建資料庫的方式

1. Database from SQL file:

• 包括 CREATE TABLE statement 以及如何新建資料庫的說明指示
• 可以包含 row 資料

比如這邊我們從本地引入一個名為 Games 的 CREATE TABLE statement, 是一個 .sql 檔案, 接著選取想要儲存此資料庫的 db 空間, 就可以成功瀏覽到 Games 資料表

2. Table from CSV file:

• 都是資料來源, 無 CREATE TABLE 說明指令
• 只有在資料庫已創建且被打開時可用

比如這邊從本地引入了一個已創建好的資料庫進來, 並且設定資料表名稱為 menu

執行 SQL Statements

點選 Execute SQL tab,

• 上方空間處: 寫任何你想要的 SQL statements
• 中間空間處: 返回以 table 格式呈現的資料, 而資料與上方寫的 statements 是相符的
• 下方空間處: 包含錯誤訊息, 返回的 # rows, 有關 SQL statement 的訊息
  

當你完成想要編寫的 SQL statements 後, 點選 GUI 中的 Execute all/selected SQL, 便可執行。
假如同時上方寫有好幾組不同的 SQL statements, 可以把特定想要執行的 statements 反白, select 起來, 再點選 Execute all/selected SQL即可。

SELECT

SELECT statement 用於從資料庫返回資料。
它返回結果資料表中的資料, 其中包含給定欄位名的 row 資料。 資料表和欄位名稱有區分大小寫。

學習連結: https://www.w3schools.com/sql/sql_select.asp

語法:

SELECT column(s) FROM table;

Example:

SELECT name, genre 
FROM Games;

DISTINCT

DISTINCT 可以幫助我們從結果集 (result set) 中去除重複的資料。
學習連結: https://www.w3schools.com/sql/sql_distinct.asp

語法:

SELECT DISTINCT column(s) FROM table;

Example:

// 沒有使用 DISTINCT
SELECT release_year
FROM Games;

// 使用 DISTINCT
SELECT DISTINCT release_year
FROM Games;

可以發現 1996 是重複的 release_year 資料, 所以加上 DISTINCT 後, 重複的資料就會從結果資料表中被去除了。

WHERE

WHERE 查詢子句根據其欄位的資料值過濾掉 rows。 在大型資料庫中, 使用 WHERE 查詢子句來縮小結果集(result set) 的大小是重要的。
建議: 在嘗試寫 query 時, 首先要考慮 FROM 的部分, 然後是 WHERE 的部分, 最後才是 SELECT 的部分。

學習連結: https://www.w3schools.com/sql/sql_where.asp

語法:

SELECT column(s) FROM table WHERE condition(s);

Example:

SELECT name, platform 
FROM Games 
WHERE publisher = 'Nintendo';

此外, SELECT statements 的 WHERE 部分可以使用以下屬性:

• =, >, >=, < <=
• <> or != (not equal)
• BETWEEN min AND max
• LIKE pattern
• IN (value, value, ..., value)

語法:

WHERE column operator value(s)

Example:

SELECT name, platform, release_year
FROM Games
WHERE release_year < 2000;

不可不知的是, 我們還可以使用 AND 或 OR 組合多個 WHERE 條件
語法:

SELECT name, platform, release_year 
FROM Games
WHERE release_year < 2000 AND genre='Racing';

LIKE

The LIKE command is used in a WHERE clause to search for a specified pattern in a column.

學習連結: https://www.w3schools.com/sql/sql_ref_like.asp

• LIKE 'text%' 搜索以給定 前綴開頭 的文本
• LIKE '%text' 搜索以給定 後綴結尾 的文本
• LIKE '%text%' 搜索包含給定 substring (子字串) 的文本
• 小小注意一下: 在 SQLite 中, LIKE string 中的文本是不區分大小寫的。

語法:

WHERE column LIKE pattern

Example:

SELECT name, release_year
FROM Games
WHERE name LIKE 'Pokemon%'

ORDER BY

ORDER BY 關鍵字用於對 result set 進行 遞增 (ASC) 或 遞減 (DESC) 的排序, 而未指定的話, 則為遞增排序。

學習連結: https://www.w3schools.com/sql/sql_orderby.asp

語法:

SELECT column(s) FROM table
ORDER BY column(s) ASC|DESC;

Example:

// 預設為遞增排序
SELECT name, release_year
FROM Games
ORDER BY name

// 遞減排序
SELECT name, release_year
FROM Games 
ORDER BY name DESC;

LIMIT

LIMIT 可以獲取前 N 個給定類別的 record。它還可以用作 sanity check/test 以確保你的 query 不返回 100000 行。

學習連結: https://www.w3schools.com/sql/sql_top.asp

語法:

LIMIT number

Example:

SELECT name, release_year 
FROM Games
WHERE genre='Wii'
ORDER BY release_year  
LIMIT 5;

綜合練習:

最後讓我們來驗收一下大家的學習成果吧! 這邊有幾個簡單設計的 SQL query 題目, 有興趣的夥伴可以一起練習看看!

Task 1:
寫一個 SQL query, 要返回由 Games 資料表中的前 20 個最早發布的 video games 的遊戲的名稱 (name) 和發布年份 (release_year)

SELECT name, release_year
FROM Games
ORDER BY release_year
LIMIT 20;

Task 2:
寫一個 SQL query, 要返回遊戲標題中包含 “Spyro” 且標題中不包含 “Skylanders” 的所有遊戲的 name、platform 和 release_year。

提示: 適時使用 AND/OR/NOT

SELECT name, platform, release_year
FROM Games
WHERE name LIKE '%Spyro%'
AND NOT (name LIKE '%Skylanders%');

Task 3:
寫一個 SQL query, 要返回 Games 資料表中遊戲的平均 release_year。 使用 ROUND 函數將結果四捨五入為最接近的整數, 並使用別名 “avg_release_year” 重新命名欄位。

必備知識: AVG, ROUND, AS

SELECT ROUND(AVG(release_year)) AS avg_release_year
FROM Games;

Task 4:
寫一個 SQL query, 要返回 Games 資料表中 “puzzle” 類型遊戲其最早一年發布的 puzzle 遊戲的 name 和 release_year。

必備知識: Subqueries (非常重要!), MIN

SELECT g1.name, g1.release_year
FROM Games g1
WHERE g1.genre = 'puzzle'
AND g1.release_year = (SELECT MIN(g2.release_year)
                       FROM Games g2
                       WHERE g2.genre = 'puzzle');