CodeSprout程式萌芽

CHAPTER 01 · DATABASE FOUNDATIONS

SQL 查詢與欄位

認識資料表、列、欄位與查詢結果，從明確選欄位開始。

這一章學會
  • 分辨表格結構與表內資料
  • 建立 SELECT 欄位與別名
  • 判斷一列一結果與 DISTINCT 去重的差別
先預測結果，再看輸出。

SQL 範例會標明資料表與引擎差異。題庫請直接提交 SELECT 或 WITH 查詢，依題目保留欄位名稱與排序規則。建表、寫入資料與交易範例供本機學習；題庫查詢環境不允許修改資料。

01｜先理解資料與結果

資料表是有名稱及欄位結構的關聯資料；一列代表一筆紀錄。欄位型別限制資料如何儲存及運算，NULL 則表示目前沒有值。資料表沒有可依賴的自然列順序，所以「第三筆」必須先說明排序。SELECT 建立新的結果表，不會改寫原始資料。

02｜建立正確查詢規則

SELECT id, reading 明確指定輸出順序；AS measured_value 只為結果欄位命名，不會改掉原表欄名。SELECT * 適合臨時查看資料，但題庫的欄名與順序也是契約，應直接列出所需欄位。DISTINCT 比較整個 SELECT 組合，不是各欄分別去重。

簡單範例｜感測標籤

依序輸出欄位 id, label，每筆原始紀錄各保留一列，不去除重複值。

先閱讀下方 fixture 的表名、欄位與缺值，再預測輸出。每個引擎使用自己的完整查詢；結果的欄名及列順序也要相同。

範例資料表（JSON）
{"tables": [{"name": "readings", "columns": [{"name": "id", "type": "INTEGER", "nullable": false}, {"name": "zone", "type": "TEXT", "nullable": true}, {"name": "reading", "type": "INTEGER", "nullable": true}, {"name": "weight", "type": "INTEGER", "nullable": true}, {"name": "flag", "type": "INTEGER", "nullable": true}, {"name": "day", "type": "INTEGER", "nullable": false}, {"name": "label", "type": "TEXT", "nullable": true}], "rows": [[10, "west", 0, null, 0, 15, "delta"], [6, null, null, 4, 1, 4, "gamma"], [12, "east", 1, 0, null, 30, "beta"], [3, "north", 5, 2, 1, 2, "alpha"], [9, "", 3, 3, 1, 8, ""], [1, null, null, null, null, 1, null], [11, "south", 9, 2, 1, 20, "atlas"], [8, "north", 9, 1, null, 8, "omega"], [2, "north", 0, 0, 0, 1, ""], [5, "east", -5, 5, 0, 3, "beta"], [4, "west", 5, -2, 1, 2, "ab"], [7, "south", -2, 0, 0, 4, "a"]]}]}
SQLite
SELECT id AS id, label AS label
FROM (
SELECT id AS id, label AS label
FROM readings
) query_result
ORDER BY id ASC;
PostgreSQL
SELECT id AS id, label AS label
FROM (
SELECT id AS id, label AS label
FROM readings
) query_result
ORDER BY id ASC;
MySQL
SELECT id AS id, label AS label
FROM (
SELECT id AS id, label AS label
FROM readings
) query_result
ORDER BY id ASC;
預期查詢結果
id | label
1 | NULL
2 | ""
3 | "alpha"
4 | "ab"
5 | "beta"
6 | "gamma"
7 | "a"
8 | "omega"
9 | ""
10 | "delta"
11 | "atlas"
12 | "beta"

中等範例｜區域索引

輸出 zone 的相異組合；完整欄位組合相同才合併成一列，NULL 在去重時視為相同值。

先閱讀下方 fixture 的表名、欄位與缺值，再預測輸出。每個引擎使用自己的完整查詢；結果的欄名及列順序也要相同。

範例資料表（JSON）
{"tables": [{"name": "readings", "columns": [{"name": "id", "type": "INTEGER", "nullable": false}, {"name": "zone", "type": "TEXT", "nullable": true}, {"name": "reading", "type": "INTEGER", "nullable": true}, {"name": "weight", "type": "INTEGER", "nullable": true}, {"name": "flag", "type": "INTEGER", "nullable": true}, {"name": "day", "type": "INTEGER", "nullable": false}, {"name": "label", "type": "TEXT", "nullable": true}], "rows": [[10, "west", 0, null, 0, 15, "delta"], [6, null, null, 4, 1, 4, "gamma"], [12, "east", 1, 0, null, 30, "beta"], [3, "north", 5, 2, 1, 2, "alpha"], [9, "", 3, 3, 1, 8, ""], [1, null, null, null, null, 1, null], [11, "south", 9, 2, 1, 20, "atlas"], [8, "north", 9, 1, null, 8, "omega"], [2, "north", 0, 0, 0, 1, ""], [5, "east", -5, 5, 0, 3, "beta"], [4, "west", 5, -2, 1, 2, "ab"], [7, "south", -2, 0, 0, 4, "a"]]}]}
SQLite
SELECT zone AS zone
FROM (
SELECT DISTINCT zone AS zone
FROM readings
) query_result
ORDER BY CASE WHEN zone IS NULL THEN 1 ELSE 0 END ASC, zone COLLATE BINARY ASC;
PostgreSQL
SELECT zone AS zone
FROM (
SELECT DISTINCT zone AS zone
FROM readings
) query_result
ORDER BY CASE WHEN zone IS NULL THEN 1 ELSE 0 END ASC, zone COLLATE "C" ASC;
MySQL
SELECT zone AS zone
FROM (
SELECT DISTINCT zone AS zone
FROM readings
) query_result
ORDER BY CASE WHEN zone IS NULL THEN 1 ELSE 0 END ASC, zone COLLATE utf8mb4_bin ASC;
預期查詢結果
zone
""
"east"
"north"
"south"
"west"
NULL

困難範例｜區域大寫

把 zone 的 ASCII 英文字母轉成大寫；NULL 保持 NULL。

先閱讀下方 fixture 的表名、欄位與缺值，再預測輸出。每個引擎使用自己的完整查詢；結果的欄名及列順序也要相同。

範例資料表（JSON）
{"tables": [{"name": "readings", "columns": [{"name": "id", "type": "INTEGER", "nullable": false}, {"name": "zone", "type": "TEXT", "nullable": true}, {"name": "reading", "type": "INTEGER", "nullable": true}, {"name": "weight", "type": "INTEGER", "nullable": true}, {"name": "flag", "type": "INTEGER", "nullable": true}, {"name": "day", "type": "INTEGER", "nullable": false}, {"name": "label", "type": "TEXT", "nullable": true}], "rows": [[10, "west", 0, null, 0, 15, "delta"], [6, null, null, 4, 1, 4, "gamma"], [12, "east", 1, 0, null, 30, "beta"], [3, "north", 5, 2, 1, 2, "alpha"], [9, "", 3, 3, 1, 8, ""], [1, null, null, null, null, 1, null], [11, "south", 9, 2, 1, 20, "atlas"], [8, "north", 9, 1, null, 8, "omega"], [2, "north", 0, 0, 0, 1, ""], [5, "east", -5, 5, 0, 3, "beta"], [4, "west", 5, -2, 1, 2, "ab"], [7, "south", -2, 0, 0, 4, "a"]]}]}
SQLite
SELECT id AS id, value AS value
FROM (
SELECT id AS id, UPPER(zone) AS value
FROM readings
) query_result
ORDER BY id ASC;
PostgreSQL
SELECT id AS id, value AS value
FROM (
SELECT id AS id, UPPER(zone) AS value
FROM readings
) query_result
ORDER BY id ASC;
MySQL
SELECT id AS id, value AS value
FROM (
SELECT id AS id, UPPER(zone) AS value
FROM readings
) query_result
ORDER BY id ASC;
預期查詢結果
id | value
1 | NULL
2 | "NORTH"
3 | "NORTH"
4 | "WEST"
5 | "EAST"
6 | NULL
7 | "SOUTH"
8 | "NORTH"
9 | ""
10 | "WEST"
11 | "SOUTH"
12 | "EAST"

06｜本機建表與型別

以下只在獨立本機練習庫執行，不能貼進題庫查詢環境。CREATE TABLE 定義結構，INSERT INTO 指定要填的欄位。PRIMARY KEY 保證 id 唯一且不可缺值；CHECK 限制有效 reading 範圍，但 NULL 的 CHECK 結果未知不會排除，所以允許缺測。SQLite 型別採 affinity，PostgreSQL／MySQL 型別規則不同，不要把 SQLite 能儲存某值當成跨引擎保證。

SQLite
CREATE TABLE scratch_readings (
  id INTEGER NOT NULL PRIMARY KEY,
  zone VARCHAR(20),
  reading INTEGER CHECK (reading BETWEEN -100 AND 100)
);
INSERT INTO scratch_readings(id,zone,reading) VALUES (1,'north',3),(2,'west',NULL);
SELECT id AS id,zone AS zone,reading AS reading FROM scratch_readings ORDER BY id;
PostgreSQL
CREATE TABLE scratch_readings (
  id INTEGER NOT NULL PRIMARY KEY,
  zone VARCHAR(20),
  reading INTEGER CHECK (reading BETWEEN -100 AND 100)
);
INSERT INTO scratch_readings(id,zone,reading) VALUES (1,'north',3),(2,'west',NULL);
SELECT id AS id,zone AS zone,reading AS reading FROM scratch_readings ORDER BY id;
MySQL
CREATE TABLE scratch_readings (
  id INTEGER NOT NULL PRIMARY KEY,
  zone VARCHAR(20),
  reading INTEGER CHECK (reading BETWEEN -100 AND 100)
);
INSERT INTO scratch_readings(id,zone,reading) VALUES (1,'north',3),(2,'west',NULL);
SELECT id AS id,zone AS zone,reading AS reading FROM scratch_readings ORDER BY id;
預期查詢結果
id | zone | reading
1 | "north" | 3
2 | "west" | NULL

WHEN SOMETHING GOES WRONG

遇到錯誤，先檢查這裡

欄位順序不符合題意

先列出題目指定的欄位，再檢查每個 AS 別名。

用 DISTINCT 隱藏重複原紀錄

不同 id 的紀錄可以具有相同 reading；除非要求去重，兩列都應保留。

以插入順序作答

明寫 ORDER BY 並提供平手鍵，資料表本身不承諾順序。

PAUSE AND TRY

先不要急著看別人的寫法

  1. 把一列 label 改為 NULL，預測選欄位與長度計算的差別。
  2. 同一 reading 配不同 zone 時，DISTINCT reading 與 DISTINCT zone,reading 的列數相同嗎？
  3. 新增一筆相同 reading、不同 id 的紀錄，哪些結果應增加一列？

先改一列資料，預測查詢結果，再切換引擎確認語法與排序。

把這幾件事帶走

  • 分辨表格結構與表內資料
  • 建立 SELECT 欄位與別名
  • 判斷一列一結果與 DISTINCT 去重的差別

FROM READING TO PRACTICE

用三道免費題，練習本章觀念

從簡單題確認本章語法，再挑戰中等與困難題。三題都來自本章的免費 SQL 題庫，SQLite、PostgreSQL、MySQL 都可作答。

簡單

001. 感測標籤

開始練習 中等

009. 區域索引

開始練習 困難

018. 區域大寫

開始練習

延伸閱讀：資料庫官方文件

需要查閱更完整的規則時，可參考以下官方文件。