Investigator Veritatis

02-MySql语句练习

6 min read

1 建表 — 电商订单场景

用一个电商场景，3 张表覆盖所有语句练习。

-- 用户表
CREATE TABLE users (
    id     INT PRIMARY KEY AUTO_INCREMENT,
    name   VARCHAR(50)  NOT NULL,
    city   VARCHAR(50),
    status VARCHAR(20)  DEFAULT 'active'
);
 
-- 商品表
CREATE TABLE products (
    id    INT PRIMARY KEY AUTO_INCREMENT,
    name  VARCHAR(100) NOT NULL,
    price DECIMAL(10,2),
    stock INT DEFAULT 0
);
 
-- 订单表
CREATE TABLE orders (
    id         INT PRIMARY KEY AUTO_INCREMENT,
    user_id    INT,
    product_id INT,
    quantity   INT,
    amount     DECIMAL(10,2),
    status     VARCHAR(20) DEFAULT 'pending',
    created_at DATETIME    DEFAULT NOW(),
    FOREIGN KEY (user_id)    REFERENCES users(id),
    FOREIGN KEY (product_id) REFERENCES products(id)
);

2 填充初始数据

INSERT INTO users (name, city, status) VALUES
('张三', 'Tokyo',    'active'),
('李四', 'Osaka',    'active'),
('王五', 'Tokyo',    'inactive'),
('赵六', 'Kyoto',    'active');
 
INSERT INTO products (name, price, stock) VALUES
('机械键盘', 599.00, 100),
('鼠标',     199.00, 200),
('显示器',  2999.00,  30),
('耳机',     399.00,  50);
 
INSERT INTO orders (user_id, product_id, quantity, amount, status) VALUES
(1, 1, 1,  599.00, 'paid'),
(1, 2, 2,  398.00, 'paid'),
(2, 3, 1, 2999.00, 'pending'),
(3, 4, 1,  399.00, 'cancelled'),
(4, 1, 2, 1198.00, 'paid'),
(2, 2, 1,  199.00, 'paid');

3 增 INSERT 实战

3.1.1 单行插入：新用户注册。

INSERT INTO users (name, city, status)
VALUES ('钱七', 'Tokyo', 'active');

3.1.2 批量插入：批量导入商品。

INSERT INTO products (name, price, stock) VALUES
('摄像头', 299.00, 80),
('麦克风', 499.00, 60);

3.1.3 查询结果插入：把已取消订单的用户单独归档到 vip 候选表（假设已建表）。

-- 先建归档表
CREATE TABLE cancelled_users AS SELECT * FROM users WHERE 1=0;
 
-- 将有取消订单的用户插入归档
INSERT INTO cancelled_users (id, name, city, status)
SELECT DISTINCT u.id, u.name, u.city, u.status
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE o.status = 'cancelled';

3.1.4 Upsert：商品价格同步，存在就更新，不存在就插入。

INSERT INTO products (id, name, price, stock)
VALUES (1, '机械键盘', 649.00, 100)
ON DUPLICATE KEY UPDATE
    price = VALUES(price);

4 删 DELETE 实战

4.1.1 条件删除：删除已取消的订单。

DELETE FROM orders
WHERE status = 'cancelled';

4.1.2 关联删除：删除 inactive 用户的所有订单。

DELETE o
FROM orders o
JOIN users u ON o.user_id = u.id
WHERE u.status = 'inactive';

4.1.3 子查询删除：删除从未下过单的用户。

DELETE FROM users
WHERE id NOT IN (
    SELECT DISTINCT user_id FROM orders
);

5 改 UPDATE 实战

5.1.1 条件更新：将 Tokyo 用户全部标记为 vip。

UPDATE users
SET status = 'vip'
WHERE city = 'Tokyo';

5.1.2 关联更新：根据订单金额回写用户等级（假设 users 有 level 列）。

ALTER TABLE users ADD COLUMN level VARCHAR(20) DEFAULT 'normal';
 
UPDATE users u
JOIN (
    SELECT user_id, SUM(amount) AS total
    FROM orders
    GROUP BY user_id
) t ON u.id = t.user_id
SET u.level = CASE
                  WHEN t.total >= 1000 THEN 'gold'
                  WHEN t.total >= 500  THEN 'silver'
                  ELSE 'normal'
              END;

5.1.3 CASE WHEN 更新：一条语句批量修正订单状态。

UPDATE orders
SET status = CASE
                 WHEN amount > 1000 THEN 'vip_paid'
                 WHEN amount > 500  THEN 'normal_paid'
                 ELSE status
             END
WHERE status = 'paid';

6 查 SELECT 实战

6.1.1 完整骨架：统计每个城市活跃用户的总消费，只看总消费超 500 的城市，按金额降序。

SELECT   u.city,
         COUNT(DISTINCT u.id)  AS user_cnt,
         SUM(o.amount)         AS total_amount
FROM     users u
JOIN     orders o ON u.id = o.user_id
WHERE    u.status = 'active'
GROUP BY u.city
HAVING   SUM(o.amount) > 500
ORDER BY total_amount DESC
LIMIT    10;

6.1.2 JOIN 对比：INNER vs LEFT 的差异一目了然。

-- INNER JOIN：只返回有订单的用户
SELECT u.name, o.amount
FROM users u
INNER JOIN orders o ON u.id = o.user_id;
 
-- LEFT JOIN：所有用户都返回，没订单的 amount 显示 NULL
SELECT u.name, o.amount
FROM users u
LEFT JOIN orders o ON u.id = o.user_id;

6.1.3 子查询三形态：

-- 标量子查询：每个用户旁边显示其最大单笔金额
SELECT name,
       (SELECT MAX(amount) FROM orders WHERE user_id = u.id) AS max_order
FROM users u;
 
-- IN 子查询：查询 Tokyo 用户的所有订单
SELECT * FROM orders
WHERE user_id IN (
    SELECT id FROM users WHERE city = 'Tokyo'
);
 
-- 派生表：查询人均消费高于整体平均的城市
SELECT t.city, t.avg_amount
FROM (
    SELECT u.city, AVG(o.amount) AS avg_amount
    FROM users u JOIN orders o ON u.id = o.user_id
    GROUP BY u.city
) t
WHERE t.avg_amount > (SELECT AVG(amount) FROM orders);

6.1.4 CTE：先筛活跃用户，再关联订单，逻辑分层清晰。

WITH
  active_users AS (
      SELECT id, name, city
      FROM users
      WHERE status IN ('active', 'vip')
  ),
  user_stats AS (
      SELECT user_id,
             COUNT(*)    AS order_cnt,
             SUM(amount) AS total
      FROM orders
      WHERE status = 'paid'
      GROUP BY user_id
  )
SELECT u.name, u.city, s.order_cnt, s.total
FROM   active_users u
JOIN   user_stats   s ON u.id = s.user_id
ORDER BY s.total DESC;

6.1.5 窗口函数：每个用户的订单按时间排名，同时显示部门（城市）总消费。

SELECT
    u.name,
    u.city,
    o.amount,
    o.created_at,
    RANK()      OVER (PARTITION BY u.city ORDER BY o.amount DESC) AS rank_in_city,
    SUM(o.amount) OVER (PARTITION BY u.city)                      AS city_total,
    LAG(o.amount) OVER (PARTITION BY u.id ORDER BY o.created_at)  AS prev_order_amount
FROM orders o
JOIN users u ON o.user_id = u.id;

Recent Notes

Graph View