数据库（一）：数据模型与 SQL —— 为何关系型表结构至今仍占主导地位

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29

CREATE TABLE users (
    user_id     SERIAL PRIMARY KEY,
    email       VARCHAR(255) NOT NULL UNIQUE,
    full_name   VARCHAR(200) NOT NULL,
    created_at  TIMESTAMP NOT NULL DEFAULT NOW()
);

CREATE TABLE products (
    product_id  SERIAL PRIMARY KEY,
    name        VARCHAR(300) NOT NULL,
    category    VARCHAR(100),
    price       DECIMAL(10, 2) NOT NULL,
    stock       INT NOT NULL DEFAULT 0
);

CREATE TABLE orders (
    order_id    SERIAL PRIMARY KEY,
    user_id     INT NOT NULL REFERENCES users(user_id),
    status      VARCHAR(20) NOT NULL DEFAULT 'pending',
    created_at  TIMESTAMP NOT NULL DEFAULT NOW()
);

CREATE TABLE order_items (
    item_id     SERIAL PRIMARY KEY,
    order_id    INT NOT NULL REFERENCES orders(order_id),
    product_id  INT NOT NULL REFERENCES products(product_id),
    quantity    INT NOT NULL CHECK (quantity > 0),
    unit_price  DECIMAL(10, 2) NOT NULL
);

1
2
3
4
5

-- 查找价格低于 $500 的 "Electronics" 类别商品
SELECT product_id, name, price
FROM products
WHERE category = 'Electronics'
  AND price < 500.00;

1
2
3
4
5
6

 product_id |         name          | price  
------------+-----------------------+--------
          3 | Wireless Mouse        |  29.99
          7 | USB-C Hub             |  45.00
         12 | Mechanical Keyboard   | 149.99
         18 | Noise-Cancelling Buds | 199.00

1
2
3
4
5

-- 最贵的 5 款商品
SELECT name, price
FROM products
ORDER BY price DESC
LIMIT 5;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14

-- 获取所有已完成订单及其用户信息与商品明细
SELECT
    u.full_name,
    o.order_id,
    p.name AS product_name,
    oi.quantity,
    oi.unit_price,
    (oi.quantity * oi.unit_price) AS line_total
FROM orders o
INNER JOIN users u ON o.user_id = u.user_id
INNER JOIN order_items oi ON o.order_id = oi.order_id
INNER JOIN products p ON oi.product_id = p.product_id
WHERE o.status = 'completed'
ORDER BY o.order_id;

1
2
3
4
5
6

  full_name   | order_id |    product_name     | quantity | unit_price | line_total
--------------+----------+---------------------+----------+------------+------------
 Alice Chen   |        1 | Mechanical Keyboard |        1 |     149.99 |     149.99
 Alice Chen   |        1 | USB-C Hub           |        2 |      45.00 |      90.00
 Bob Martinez |        3 | Wireless Mouse      |        3 |      29.99 |      89.97
 Bob Martinez |        3 | Standing Desk       |        1 |     599.00 |     599.00

1
2
3
4
5

-- 查找从未下过单的用户
SELECT u.user_id, u.email, u.full_name
FROM users u
LEFT JOIN orders o ON u.user_id = o.user_id
WHERE o.order_id IS NULL;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

-- 各品类营收（仅显示营收 > $1000 的品类）
SELECT
    p.category,
    COUNT(DISTINCT o.order_id) AS order_count,
    SUM(oi.quantity) AS total_units,
    SUM(oi.quantity * oi.unit_price) AS total_revenue
FROM order_items oi
JOIN products p ON oi.product_id = p.product_id
JOIN orders o ON oi.order_id = o.order_id
WHERE o.status = 'completed'
GROUP BY p.category
HAVING SUM(oi.quantity * oi.unit_price) > 1000.00
ORDER BY total_revenue DESC;

1
2
3
4
5

  category    | order_count | total_units | total_revenue
--------------+-------------+-------------+--------------
 Electronics  |          47 |         132 |      18432.50
 Furniture    |          23 |          31 |      12890.00
 Books        |          89 |         234 |       4567.80

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15

-- 添加电话号码列
ALTER TABLE users ADD COLUMN phone VARCHAR(20);

-- 为现有列设置默认值
ALTER TABLE products ALTER COLUMN stock SET DEFAULT 0;

-- 重命名列（PostgreSQL）
ALTER TABLE products RENAME COLUMN name TO product_name;

-- 添加复合唯一约束
ALTER TABLE order_items ADD CONSTRAINT uq_order_product
    UNIQUE (order_id, product_id);

-- 删除列（生产环境务必谨慎！）
ALTER TABLE users DROP COLUMN phone;

1
2
3
4
5
6

| order_id | customer_name | customer_email      | product_name | product_price | quantity |
|----------|---------------|---------------------|--------------|---------------|----------|
| 1        | Alice Chen    | alice@example.com   | Keyboard     | 149.99        | 1        |
| 1        | Alice Chen    | alice@example.com   | USB Hub      | 45.00         | 2        |
| 2        | Alice Chen    | alice@example.com   | Mouse        | 29.99         | 1        |
| 3        | Bob Martinez  | bob@example.com     | Keyboard     | 149.99        | 1        |

1
2
3

| order_id | products                |
|----------|-------------------------|
| 1        | Keyboard, USB Hub       |  -- 逗号分隔 = 违反 1NF

1
2
3

| user_id | zip_code | city       | state |
|---------|----------|------------|-------|
| 1       | 94105    | San Francisco | CA |

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

-- 为避免每次查询都 JOIN orders + order_items + products，
-- 直接在 orders 表中缓存预计算的总金额
ALTER TABLE orders ADD COLUMN total_amount DECIMAL(12, 2);

-- 当订单项变更时更新该字段
UPDATE orders o
SET total_amount = (
    SELECT SUM(oi.quantity * oi.unit_price)
    FROM order_items oi
    WHERE oi.order_id = o.order_id
)
WHERE o.order_id = 42;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21

-- 查找总消费额超过平均用户消费额的用户
SELECT u.full_name, user_totals.total_spent
FROM users u
JOIN (
    SELECT o.user_id, SUM(oi.quantity * oi.unit_price) AS total_spent
    FROM orders o
    JOIN order_items oi ON o.order_id = oi.order_id
    WHERE o.status = 'completed'
    GROUP BY o.user_id
) user_totals ON u.user_id = user_totals.user_id
WHERE user_totals.total_spent > (
    SELECT AVG(sub.total_spent)
    FROM (
        SELECT SUM(oi.quantity * oi.unit_price) AS total_spent
        FROM orders o
        JOIN order_items oi ON o.order_id = oi.order_id
        WHERE o.status = 'completed'
        GROUP BY o.user_id
    ) sub
)
ORDER BY user_totals.total_spent DESC;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

WITH user_spending AS (
    SELECT
        o.user_id,
        SUM(oi.quantity * oi.unit_price) AS total_spent
    FROM orders o
    JOIN order_items oi ON o.order_id = oi.order_id
    WHERE o.status = 'completed'
    GROUP BY o.user_id
),
avg_spending AS (
    SELECT AVG(total_spent) AS avg_spent
    FROM user_spending
)
SELECT
    u.full_name,
    us.total_spent,
    a.avg_spent,
    ROUND(us.total_spent / a.avg_spent, 2) AS spending_ratio
FROM user_spending us
JOIN users u ON us.user_id = u.user_id
CROSS JOIN avg_spending a
WHERE us.total_spent > a.avg_spent
ORDER BY us.total_spent DESC;

1
2
3
4
5

  full_name    | total_spent | avg_spent | spending_ratio
---------------+-------------+-----------+----------------
 David Park    |     4523.90 |    892.34 |           5.07
 Alice Chen    |     2891.45 |    892.34 |           3.24
 Carol White   |     1567.20 |    892.34 |           1.76

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17

-- 员工组织架构：查找某经理下属的所有员工
WITH RECURSIVE reports AS (
    -- 基础情况：经理本人
    SELECT employee_id, name, manager_id, 0 AS depth
    FROM employees
    WHERE employee_id = 1

    UNION ALL

    -- 递归情况：查找当前层级员工的直接下属
    SELECT e.employee_id, e.name, e.manager_id, r.depth + 1
    FROM employees e
    JOIN reports r ON e.manager_id = r.employee_id
)
SELECT depth, employee_id, name
FROM reports
ORDER BY depth, name;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19

-- 按月份统计用户消费，并在每月内排名
SELECT
    u.full_name,
    DATE_TRUNC('month', o.created_at) AS month,
    SUM(oi.quantity * oi.unit_price) AS monthly_spend,
    RANK() OVER (
        PARTITION BY DATE_TRUNC('month', o.created_at)
        ORDER BY SUM(oi.quantity * oi.unit_price) DESC
    ) AS spend_rank,
    ROW_NUMBER() OVER (
        PARTITION BY DATE_TRUNC('month', o.created_at)
        ORDER BY SUM(oi.quantity * oi.unit_price) DESC
    ) AS row_num
FROM orders o
JOIN users u ON o.user_id = u.user_id
JOIN order_items oi ON o.order_id = oi.order_id
WHERE o.status = 'completed'
GROUP BY u.full_name, DATE_TRUNC('month', o.created_at)
ORDER BY month, spend_rank;

1
2
3
4
5
6
7
8

  full_name    |   month    | monthly_spend | spend_rank | row_num
---------------+------------+---------------+------------+---------
 David Park    | 2023-10-01 |       1245.00 |          1 |       1
 Alice Chen    | 2023-10-01 |        892.50 |          2 |       2
 Carol White   | 2023-10-01 |        892.50 |          2 |       3
 Bob Martinez  | 2023-10-01 |        334.99 |          4 |       4
 Alice Chen    | 2023-11-01 |       1567.20 |          1 |       1
 David Park    | 2023-11-01 |        998.00 |          2 |       2

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21

-- 对比本月营收与上月营收
WITH monthly_revenue AS (
    SELECT
        DATE_TRUNC('month', o.created_at) AS month,
        SUM(oi.quantity * oi.unit_price) AS revenue
    FROM orders o
    JOIN order_items oi ON o.order_id = oi.order_id
    WHERE o.status = 'completed'
    GROUP BY DATE_TRUNC('month', o.created_at)
)
SELECT
    month,
    revenue,
    LAG(revenue) OVER (ORDER BY month) AS prev_month_revenue,
    ROUND(
        (revenue - LAG(revenue) OVER (ORDER BY month))
        / LAG(revenue) OVER (ORDER BY month) * 100,
        1
    ) AS growth_pct
FROM monthly_revenue
ORDER BY month;

1
2
3
4
5
6
7

   month    |  revenue  | prev_month_revenue | growth_pct
------------+-----------+--------------------+------------
 2023-08-01 |  12450.00 |                    |
 2023-09-01 |  15670.30 |          12450.00  |       25.9
 2023-10-01 |  14230.50 |          15670.30  |       -9.2
 2023-11-01 |  18900.00 |          14230.50  |       32.8
 2023-12-01 |  24560.80 |          18900.00  |       30.0

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14

-- 含 JSONB 列的表，用于存储灵活的元数据
CREATE TABLE events (
    event_id BIGSERIAL PRIMARY KEY,
    event_type VARCHAR(50) NOT NULL,
    user_id INT REFERENCES users(user_id),
    payload JSONB NOT NULL DEFAULT '{}',
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- 插入不同结构的事件
INSERT INTO events (event_type, user_id, payload) VALUES
('page_view', 1, '{"url": "/products/42", "duration_ms": 3200, "referrer": "google"}'),
('purchase', 1, '{"order_id": 1001, "items": [{"sku": "KB-01", "qty": 1}], "total": 149.99}'),
('error', 2, '{"code": 500, "message": "timeout", "stack": "at Connection.query..."}');

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20

-- 提取字段（返回文本）
SELECT payload->>'url' AS url, payload->>'duration_ms' AS ms
FROM events
WHERE event_type = 'page_view';

-- 嵌套访问
SELECT payload->'items'->0->>'sku' AS first_item_sku
FROM events
WHERE event_type = 'purchase';

-- 按 JSON 字段过滤
SELECT *
FROM events
WHERE event_type = 'error'
  AND (payload->>'code')::int >= 500;

-- 包含运算符（使用 GIN 索引）
SELECT *
FROM events
WHERE payload @> '{"code": 500}';

1
2
3
4
5
6

-- GIN 索引用于包含查询（@>, ?, ?|, ?&）
CREATE INDEX idx_events_payload ON events USING GIN (payload);

-- 针对特定 JSON 路径的表达式索引
CREATE INDEX idx_events_error_code ON events ((payload->>'code'))
WHERE event_type = 'error';

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

-- 错误：整行都是 JSON（等于用关系数据库模拟文档数据库）
CREATE TABLE bad_design (
    id SERIAL PRIMARY KEY,
    data JSONB  -- 所有东西都塞在这里
);

-- 正确：将频繁查询的 JSON 字段提升为真实列
ALTER TABLE events ADD COLUMN error_code INT;
UPDATE events SET error_code = (payload->>'code')::int WHERE event_type = 'error';
CREATE INDEX idx_events_error_code ON events (error_code);

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

WITH RECURSIVE cte_name AS (
    -- 基础条件：起始行
    SELECT ... FROM table WHERE condition

    UNION ALL

    -- 递归步骤：回连 CTE 自身
    SELECT ... FROM table JOIN cte_name ON ...
)
SELECT * FROM cte_name;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30

CREATE TABLE categories (
    category_id INT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    parent_id INT REFERENCES categories(category_id)
);

INSERT INTO categories VALUES
(1, '全部商品', NULL),
(2, '电子产品', 1),
(3, '计算机', 2),
(4, '笔记本', 3),
(5, '台式机', 3),
(6, '手机', 2),
(7, '家具', 1),
(8, '办公桌', 7);

-- 查找"电子产品"的所有后代及深度
WITH RECURSIVE subtree AS (
    SELECT category_id, name, parent_id, 0 AS depth, name::text AS path
    FROM categories
    WHERE name = '电子产品'

    UNION ALL

    SELECT c.category_id, c.name, c.parent_id, s.depth + 1,
           s.path || ' > ' || c.name
    FROM categories c
    JOIN subtree s ON c.parent_id = s.category_id
)
SELECT depth, path FROM subtree ORDER BY path;

1
2
3
4
5
6
7

 depth |              path
-------+----------------------------------
     0 | 电子产品
     1 | 电子产品 > 计算机
     2 | 电子产品 > 计算机 > 台式机
     2 | 电子产品 > 计算机 > 笔记本
     1 | 电子产品 > 手机

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

WITH RECURSIVE tree AS (
    SELECT id, parent_id, ARRAY[id] AS path, false AS cycle
    FROM nodes WHERE id = 1

    UNION ALL

    SELECT n.id, n.parent_id, t.path || n.id,
           n.id = ANY(t.path)  -- 检测循环
    FROM nodes n
    JOIN tree t ON n.parent_id = t.id
    WHERE NOT t.cycle  -- 检测到循环则停止
)
SELECT * FROM tree WHERE NOT cycle;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17

-- 不用 LATERAL：窗口函数方式（需加载所有订单到内存）
WITH ranked AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC) AS rn
    FROM orders
)
SELECT * FROM ranked WHERE rn <= 3;

-- 用 LATERAL：每个用户扫描 3 行即停（大表更高效）
SELECT u.user_id, u.full_name, recent.*
FROM users u
CROSS JOIN LATERAL (
    SELECT order_id, total_amount, created_at
    FROM orders o
    WHERE o.user_id = u.user_id
    ORDER BY o.created_at DESC
    LIMIT 3
) recent;

1
2
3
4
5

-- 解构 JSONB 数组并与关联表 join
SELECT e.event_id, e.event_type, item->>'sku' AS sku, item->>'qty' AS qty
FROM events e
CROSS JOIN LATERAL jsonb_array_elements(e.payload->'items') AS item
WHERE e.event_type = 'purchase';

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28

WITH product_revenue AS (
    SELECT
        p.product_id,
        p.name AS product_name,
        p.category,
        SUM(oi.quantity * oi.unit_price) AS revenue
    FROM order_items oi
    JOIN products p ON oi.product_id = p.product_id
    JOIN orders o ON oi.order_id = o.order_id
    WHERE o.status = 'completed'
    GROUP BY p.product_id, p.name, p.category
),
ranked AS (
    SELECT
        *,
        RANK() OVER (PARTITION BY category ORDER BY revenue DESC) AS rank_in_category,
        SUM(revenue) OVER (PARTITION BY category) AS category_total
    FROM product_revenue
)
SELECT
    category,
    product_name,
    revenue,
    category_total,
    ROUND(revenue / category_total * 100, 1) AS pct_of_category
FROM ranked
WHERE rank_in_category = 1
ORDER BY revenue DESC;

1
2
3
4
5

  category    |    product_name     | revenue  | category_total | pct_of_category
--------------+---------------------+----------+----------------+-----------------
 Electronics  | Mechanical Keyboard |  8934.50 |      18432.50  |            48.5
 Furniture    | Standing Desk       |  7190.00 |      12890.00  |            55.8
 Books        | DDIA                |  1890.30 |       4567.80  |            41.4