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 中的窗口函数、触发器和索引策略等高级主题。