SQL 快速入門

概述

本文將以 SQLite 方言為例，介紹 SQL 的基本要點。我們先建立幾個示例表並填充資料，這些表將作為後續各節示例的基礎。讀完本概述後，你將擁有一個可以上手操作的簡單模式（schema），並瞭解如何為常見的 SQL 操作準備好環境。

-- Create a table for users
CREATE TABLE IF NOT EXISTS users (
    id INTEGER PRIMARY KEY,
    username TEXT NOT NULL,
    email TEXT NOT NULL,
    joined_date TEXT NOT NULL  -- stored as ISO-8601 strings in SQLite
);

-- Create a table for orders
CREATE TABLE IF NOT EXISTS orders (
    id INTEGER PRIMARY KEY,
    user_id INTEGER NOT NULL,
    product TEXT NOT NULL,
    quantity INTEGER NOT NULL,
    order_date TEXT NOT NULL,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

-- Insert sample data into users
INSERT INTO users (username, email, joined_date) VALUES
    ('alice', 'alice@example.com', '2023-01-15'),
    ('bob', 'bob@example.com', '2023-02-10'),
    ('carol', 'carol@example.com', '2023-03-05');

-- Insert sample data into orders
INSERT INTO orders (user_id, product, quantity, order_date) VALUES
    (1, 'Keyboard', 2, '2023-04-01'),
    (1, 'Mouse', 1, '2023-04-07'),
    (2, 'Monitor', 1, '2023-04-15'),
    (3, 'Desk Lamp', 3, '2023-05-02'),
    (2, 'USB Cable', 5, '2023-05-10');

這兩個表 —— users 和 orders —— 將作為我們查詢的基礎。你可以在 SQLite 環境中（例如 sqlite3 命令列工具或任意圖形介面工具）執行這些語句，以便動手試驗後面的示例。

常用語法

本節介紹四種最常用的 SQL 語句：

1. SELECT
2. UPDATE
3. DELETE
4. INSERT

我們只關注每種語句的典型用法，不會涵蓋所有可能的選項或關鍵字。按照慣例，所有 SQL 關鍵字都以大寫形式書寫，這是一個好習慣。不過對 SQL 引擎來說，關鍵字通常不區分大小寫。

1. SELECT

SELECT 語句用於從一個或多個表中檢索資料。最簡單的形式是指定所需的列以及要從中檢索的表：

SELECT id, username, email
FROM users;

如果需要所有列，使用 *：

SELECT *
FROM orders;

可以使用 WHERE 篩選結果：

SELECT username, email
FROM users
WHERE joined_date >= '2023-02-01';

要對結果排序，使用 ORDER BY：

SELECT id, product, quantity, order_date
FROM orders
WHERE quantity > 1
ORDER BY order_date DESC;

限制返回的行數：

SELECT *
FROM orders
LIMIT 3;

組合多個子句：

SELECT username, email
FROM users
WHERE username LIKE 'a%'
ORDER BY joined_date;

這裡的 LIKE 關鍵字匹配所有以 a 開頭的名稱。

2. UPDATE

使用 UPDATE 修改已有的行。務必加上 WHERE 子句，以免無意中更新了所有行：

-- Change Bob's email address
UPDATE users
SET email = 'bob.new@example.com'
WHERE username = 'bob';

可以一次更新多個列：

UPDATE users
SET email = 'alice.updated@example.com',
    joined_date = '2023-01-20'
WHERE username = 'alice';

好的做法是先用相同的 WHERE 執行一次 SELECT：

SELECT * FROM users WHERE username = 'alice';
-- Review the row, then:
UPDATE users
SET email = 'alice.updated@example.com'
WHERE username = 'alice';

3. DELETE

DELETE 語句從表中刪除行。同樣，WHERE 子句必不可少：

-- Delete Carol's record
DELETE FROM users
WHERE username = 'carol';

刪除表中的所有行（但保留表結構）：

DELETE FROM orders;

SQLite 支援不帶 WHERE 的 DELETE FROM table_name; 來刪除每一行。如果只想刪除特定的行：

DELETE FROM orders
WHERE order_date < '2023-04-01';

4. INSERT

INSERT 向表中新增新行。我們在概述中已經用它填充了 users 和 orders。基本語法需要指定列和值：

INSERT INTO users (username, email, joined_date)
VALUES ('dave', 'dave@example.com', '2023-06-01');

如果按確切順序為每一列都提供了值，可以省略列列表：

INSERT INTO orders VALUES (NULL, 4, 'Webcam', 2, '2023-06-02');
-- NULL for id since it's an AUTOINCREMENT primary key

如果要從另一個表插入資料（這是在 INSERT 中使用 SELECT 的一種簡單形式）：

INSERT INTO orders (user_id, product, quantity, order_date)
SELECT id, 'Headphones', 1, '2023-06-05'
FROM users
WHERE username = 'bob';

技巧

上面的示例涵蓋了基本的 CRUD 操作。本節重點介紹 SQLite 中更高階但也很常用的 SELECT 模式，包括隱式連線、顯式連線、公用表表示式（WITH）和子查詢。當你需要合併多個表中的資料或得出聚合結果時，這些技巧尤其有用。

1. 在 FROM 中使用多個表的隱式連線

隱式連線（有時稱為“逗號連線”）在 FROM 子句中列出多個表，並在 WHERE 中指定連線條件。這種寫法簡潔，但在複雜查詢中可能不夠清晰：

-- List every order along with the username of the user who placed it
SELECT u.username, o.product, o.quantity, o.order_date
FROM users u, orders o
WHERE u.id = o.user_id;

這裡，u 是 users 的別名，o 是 orders 的別名。連線條件 u.id = o.user_id 將 users 中的行與 orders 中匹配的行關聯起來。

你可以新增篩選條件：

-- Only orders with quantity > 1
SELECT u.username, o.product, o.quantity
FROM users u, orders o
WHERE u.id = o.user_id
  AND o.quantity > 1
ORDER BY o.order_date;

2. 顯式連線語法（INNER JOIN、LEFT JOIN）

SQLite 支援標準的顯式 JOIN 語法，為了清晰起見，推薦使用這種寫法：

-- INNER JOIN retrieves only matching rows
SELECT u.username, o.product, o.order_date
FROM users u
INNER JOIN orders o
    ON u.id = o.user_id;

左連線（或左外連線）返回左表中的所有行，以及右表中匹配的行（如果有）：

-- List all users, including those who have not placed orders
SELECT u.username, o.product, o.order_date
FROM users u
LEFT JOIN orders o
    ON u.id = o.user_id;

如果某個使用者沒有匹配的 orders，則 product 和 order_date 列將為 NULL。

多重連線 當涉及兩個以上的表時，可以將連線串聯起來：

-- Suppose we add a table for payment info
CREATE TABLE IF NOT EXISTS payments (
    id INTEGER PRIMARY KEY,
    order_id INTEGER NOT NULL,
    amount REAL NOT NULL,
    payment_date TEXT NOT NULL,
    FOREIGN KEY (order_id) REFERENCES orders(id)
);

-- Sample data
INSERT INTO payments (order_id, amount, payment_date) VALUES
    (1, 49.98, '2023-04-02'),
    (2, 19.99, '2023-04-08'),
    (3, 149.99, '2023-04-16');

-- Query: find username, product, and payment amount for each paid order
SELECT u.username,
       o.product,
       p.amount,
       p.payment_date
FROM users u
INNER JOIN orders o
    ON u.id = o.user_id
INNER JOIN payments p
    ON o.id = p.order_id
ORDER BY p.payment_date;

3. WITH 子句（公用表表示式）

由 WITH 定義的公用表表示式（CTE）可以建立一個臨時的、具名的結果集，供後續查詢引用。這有助於把複雜查詢拆分成更易讀的若干部分。

-- Example: Find users who have spent more than $50 in total
WITH user_totals AS (
    SELECT u.id AS user_id,
           u.username,
           SUM(p.amount) AS total_spent
    FROM users u
    INNER JOIN orders o
        ON u.id = o.user_id
    INNER JOIN payments p
        ON o.id = p.order_id
    GROUP BY u.id, u.username
)
SELECT username, total_spent
FROM user_totals
WHERE total_spent > 50;

在這個示例中：

1. 名為 user_totals 的 CTE 彙總了每個使用者的總消費。
2. 外層 SELECT 根據彙總後的消費額篩選使用者。

CTE 也可以是遞迴的，但作為快速入門，請先專注於非遞迴 CTE。

4. 子查詢

子查詢（也稱為內部查詢或巢狀查詢）是嵌入在其他 SQL 語句中的查詢。它們可以出現在 SELECT 列表、FROM 子句或 WHERE 子句中。

4.1 SELECT 列表中的子查詢

-- Add a column showing the number of orders each user has placed
SELECT u.username,
       u.email,
       (
           SELECT COUNT(*)
           FROM orders o
           WHERE o.user_id = u.id
       ) AS order_count
FROM users u;

子查詢 (SELECT COUNT(*) FROM orders o WHERE o.user_id = u.id) 會針對 users 中的每一行執行一次。

4.2 WHERE 子句中的子查詢

-- Find users who have never placed an order
SELECT username, email
FROM users
WHERE id NOT IN (
    SELECT DISTINCT user_id
    FROM orders
);

也可以改用 NOT EXISTS：

SELECT username, email
FROM users u
WHERE NOT EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.user_id = u.id
);

4.3 FROM 子句中的子查詢

-- Join users to a derived table that calculates order counts
SELECT t.username, t.order_count
FROM (
    SELECT u.id AS user_id,
           u.username,
           COUNT(o.id) AS order_count
    FROM users u
    LEFT JOIN orders o
        ON u.id = o.user_id
    GROUP BY u.id, u.username
) AS t
WHERE t.order_count >= 2;

這裡，FROM 子句中的子查詢（別名為 t）計算每個使用者的訂單數，外層查詢再按 order_count 進行篩選。

5. 聚合與 GROUP BY

嚴格來說，聚合也屬於 SELECT 模式的一部分，但它實在太常用，值得單獨介紹：

-- Total quantity ordered per product
SELECT product,
       SUM(quantity) AS total_quantity
FROM orders
GROUP BY product
ORDER BY total_quantity DESC;

HAVING

要對聚合值進行篩選，應使用 HAVING 而不是 WHERE：

-- Only products with total quantity > 2
SELECT product,
       SUM(quantity) AS total_quantity
FROM orders
GROUP BY product
HAVING total_quantity > 2;

6. 綜合運用

下面將其中幾種技巧組合成一個更完整的示例：

1. 列出每個使用者、其訂單總數、訂購總數量以及最近一次下單日期。
2. 只包含至少下過一個訂單的使用者。
3. 按訂購總數量降序排列。

WITH user_orders AS (
    SELECT u.id    AS user_id,
           u.username,
           COUNT(o.id)           AS order_count,
           SUM(o.quantity)       AS total_quantity,
           MAX(o.order_date)     AS last_order_date
    FROM users u
    INNER JOIN orders o
        ON u.id = o.user_id
    GROUP BY u.id, u.username
)
SELECT username,
       order_count,
       total_quantity,
       last_order_date
FROM user_orders
WHERE order_count >= 1
ORDER BY total_quantity DESC;

說明：

• CTE user_orders 按使用者彙總與訂單相關的指標。
• 外層查詢過濾掉訂單數為零的使用者，並對結果排序。

付諸實踐

下面是一些額外的示例和變體，在使用 SQLite 進行日常資料分析時可能會用到：

1. 總消費排名前 3 的使用者 結合 payments 和 orders 對使用者進行排名。

   WITH spending AS (
       SELECT u.id         AS user_id,
              u.username,
              SUM(p.amount) AS total_spent
       FROM users u
       INNER JOIN orders o
           ON u.id = o.user_id
       INNER JOIN payments p
           ON o.id = p.order_id
       GROUP BY u.id, u.username
   )
   SELECT username, total_spent
   FROM spending
   ORDER BY total_spent DESC
   LIMIT 3;
   

2. 尋找沒有付款記錄的訂單 使用左連線，並在付款一側尋找 NULL。

   SELECT o.id AS order_id,
          u.username,
          o.product,
          o.order_date
   FROM orders o
   LEFT JOIN payments p
       ON o.id = p.order_id
   INNER JOIN users u
       ON o.user_id = u.id
   WHERE p.id IS NULL;
   

3. 通過子查詢更新 假設我們想在一個新列中把某個日期之前加入的使用者標記為“legacy”。首先新增一列：

   ALTER TABLE users ADD COLUMN status TEXT DEFAULT 'active';
   
   UPDATE users
   SET status = 'legacy'
   WHERE joined_date < '2023-02-01';
   

4. 基於關聯條件刪除 如果需要刪除已取消訂單的付款記錄（假設存在一個 orders.status = 'canceled' 列），可以這樣寫：

   DELETE FROM payments
   WHERE order_id IN (
       SELECT id
       FROM orders
       WHERE status = 'canceled'
   );
   

5. 巢狀 CTE 串聯多個 CTE 來分解一個複雜問題：

   WITH recent_orders AS (
       SELECT *
       FROM orders
       WHERE order_date >= '2023-04-01'
   ),
   recent_payments AS (
       SELECT o.id     AS order_id,
              o.user_id,
              p.amount,
              p.payment_date
       FROM recent_orders o
       INNER JOIN payments p
           ON o.id = p.order_id
   )
   SELECT u.username,
          SUM(rp.amount) AS recent_spending
   FROM users u
   INNER JOIN recent_payments rp
       ON u.id = rp.user_id
   GROUP BY u.username
   ORDER BY recent_spending DESC;
   

結語

本文以 SQLite 方言介紹了 SQL 的基礎知識，重點講解了最常用的語句 —— SELECT、UPDATE、DELETE 和 INSERT —— 以及若干實用的 SELECT 技巧。現在你已經有了包含 users、orders 和 payments 的模式可供試驗，並且瞭解瞭如何：

• 建立表並插入資料。
• 執行簡單的 CRUD 操作。
• 以隱式和顯式方式連線表。
• 使用公用表表示式（WITH）。
• 在語句的不同部分編寫子查詢。
• 聚合資料並使用 HAVING 進行篩選。

這些示例應能為日常資料分析任務打下堅實的基礎。隨著你越來越熟練，可以進一步探索 SQLite 中的視窗函式、觸發器和索引策略等高階主題。