一套基于简单商城业务场景的 MySQL SQL 练习资料，包含建库建表、测试数据、查询、聚合、JOIN、子查询、事务、视图和运行结果截图记录。
先在 MySQL 中执行第 1-3 部分完成初始化，然后按编号逐条执行 SQL。每条 SQL 执行后，把截图保存到对应路径，例如 docs/screenshots/04-01.png，上传到 GitHub 后本页面会自动显示截图。
Section 01
创建并切换到练习数据库。
DROP DATABASE IF EXISTS sql_practice_shop;
CREATE DATABASE sql_practice_shop
DEFAULT CHARACTER SET utf8mb4
DEFAULT COLLATE utf8mb4_unicode_ci;
USE sql_practice_shop;
docs/screenshots/01-01.pngSection 02
创建 customers、products、orders、order_items 四张练习表。
-- 业务场景：一个简单商城
-- customers 客户表
-- products 产品表
-- orders 订单表
-- order_items 订单明细表
DROP TABLE IF EXISTS order_items;
DROP TABLE IF EXISTS orders;
DROP TABLE IF EXISTS products;
DROP TABLE IF EXISTS customers;
CREATE TABLE customers (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
city VARCHAR(80) NOT NULL,
email VARCHAR(160) NOT NULL UNIQUE,
level ENUM('normal', 'vip') NOT NULL DEFAULT 'normal',
created_at DATE NOT NULL
) ENGINE=InnoDB;
CREATE TABLE products (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(120) NOT NULL,
category VARCHAR(80) NOT NULL,
price DECIMAL(10, 2) NOT NULL,
stock INT NOT NULL DEFAULT 0,
status ENUM('active', 'inactive') NOT NULL DEFAULT 'active'
) ENGINE=InnoDB;
CREATE TABLE orders (
id INT AUTO_INCREMENT PRIMARY KEY,
customer_id INT NOT NULL,
order_no VARCHAR(40) NOT NULL UNIQUE,
status ENUM('pending', 'paid', 'shipped', 'cancelled') NOT NULL DEFAULT 'pending',
order_date DATE NOT NULL,
note VARCHAR(255),
CONSTRAINT fk_orders_customer
FOREIGN KEY (customer_id) REFERENCES customers(id)
ON UPDATE CASCADE
ON DELETE RESTRICT
) ENGINE=InnoDB;
CREATE TABLE order_items (
id INT AUTO_INCREMENT PRIMARY KEY,
order_id INT NOT NULL,
product_id INT NOT NULL,
quantity INT NOT NULL,
unit_price DECIMAL(10, 2) NOT NULL,
CONSTRAINT fk_order_items_order
FOREIGN KEY (order_id) REFERENCES orders(id)
ON UPDATE CASCADE
ON DELETE CASCADE,
CONSTRAINT fk_order_items_product
FOREIGN KEY (product_id) REFERENCES products(id)
ON UPDATE CASCADE
ON DELETE RESTRICT
) ENGINE=InnoDB;
docs/screenshots/02-01.pngSection 03
插入一批商城测试数据，后续 SELECT / JOIN / GROUP BY 都基于这些数据。
INSERT INTO customers (name, city, email, level, created_at) VALUES
('Alice Wang', 'Toronto', '[email protected]', 'vip', '2024-01-10'),
('Bob Chen', 'Vancouver', '[email protected]', 'normal', '2024-02-15'),
('Cindy Li', 'Montreal', '[email protected]', 'vip', '2024-03-20'),
('David Zhang', 'Calgary', '[email protected]', 'normal', '2024-04-05'),
('Eva Liu', 'Toronto', '[email protected]', 'normal', '2024-04-18'),
('Frank Sun', 'Ottawa', '[email protected]', 'vip', '2024-05-01');
INSERT INTO products (name, category, price, stock, status) VALUES
('Composite Decking Board', 'Decking', 29.90, 500, 'active'),
('Decking Starter Clip', 'Accessories', 0.45, 3000, 'active'),
('Decking Joist', 'Substructure', 12.50, 800, 'active'),
('Wall Cladding Panel', 'Cladding', 35.80, 260, 'active'),
('Corner Trim', 'Accessories', 8.90, 600, 'active'),
('Old Sample Board', 'Sample', 3.00, 0, 'inactive'),
('Fascia Board', 'Decking', 18.60, 180, 'active'),
('Screw Pack', 'Accessories', 6.50, 1200, 'active');
INSERT INTO orders (customer_id, order_no, status, order_date, note) VALUES
(1, 'SO20240501001', 'paid', '2024-05-01', 'VIP customer'),
(2, 'SO20240503001', 'pending', '2024-05-03', NULL),
(3, 'SO20240505001', 'shipped', '2024-05-05', 'Urgent delivery'),
(1, 'SO20240510001', 'cancelled', '2024-05-10', 'Customer changed plan'),
(5, 'SO20240512001', 'paid', '2024-05-12', NULL),
(6, 'SO20240515001', 'paid', '2024-05-15', 'Repeat customer'),
(4, 'SO20240518001', 'shipped', '2024-05-18', NULL),
(3, 'SO20240520001', 'paid', '2024-05-20', NULL);
INSERT INTO order_items (order_id, product_id, quantity, unit_price) VALUES
(1, 1, 20, 29.90),
(1, 2, 200, 0.45),
(1, 8, 5, 6.50),
(2, 4, 10, 35.80),
(2, 5, 8, 8.90),
(3, 1, 30, 29.90),
(3, 3, 12, 12.50),
(3, 2, 300, 0.45),
(4, 6, 5, 3.00),
(5, 7, 15, 18.60),
(5, 8, 3, 6.50),
(6, 4, 12, 35.80),
(6, 5, 10, 8.90),
(6, 8, 2, 6.50),
(7, 1, 25, 29.90),
(7, 3, 10, 12.50),
(8, 1, 10, 29.90),
(8, 4, 6, 35.80),
(8, 5, 4, 8.90);
docs/screenshots/03-01.pngSection 04
SELECT *
FROM customers;
docs/screenshots/04-01.pngSELECT name, city, email
FROM customers;
docs/screenshots/04-02.pngSELECT
name AS product_name,
category AS product_category,
price AS unit_price
FROM products;
docs/screenshots/04-03.pngSELECT *
FROM products
LIMIT 5;
docs/screenshots/04-04.pngSection 05
SELECT *
FROM customers
WHERE city = 'Toronto';
docs/screenshots/05-01.pngSELECT *
FROM customers
WHERE level = 'vip';
docs/screenshots/05-02.pngSELECT *
FROM products
WHERE price > 20;
docs/screenshots/05-03.pngSELECT *
FROM products
WHERE stock > 0
AND status = 'active';
docs/screenshots/05-04.pngSELECT *
FROM products
WHERE category IN ('Decking', 'Accessories');
docs/screenshots/05-05.pngSELECT *
FROM products
WHERE name LIKE '%Board%';
docs/screenshots/05-06.pngSELECT *
FROM orders
WHERE order_date > '2024-05-10';
docs/screenshots/05-07.pngSection 06
SELECT *
FROM products
ORDER BY price ASC;
docs/screenshots/06-01.pngSELECT *
FROM products
ORDER BY stock DESC;
docs/screenshots/06-02.pngSELECT *
FROM orders
ORDER BY status ASC, order_date DESC;
docs/screenshots/06-03.pngSection 07
SELECT COUNT(*) AS customer_count
FROM customers;
docs/screenshots/07-01.pngSELECT AVG(price) AS average_price
FROM products;
docs/screenshots/07-02.pngSELECT
MAX(price) AS max_price,
MIN(price) AS min_price
FROM products;
docs/screenshots/07-03.pngSELECT SUM(stock) AS total_stock
FROM products;
docs/screenshots/07-04.pngSection 08
SELECT
city,
COUNT(*) AS customer_count
FROM customers
GROUP BY city
ORDER BY customer_count DESC;
docs/screenshots/08-01.pngSELECT
category,
COUNT(*) AS product_count
FROM products
GROUP BY category
ORDER BY product_count DESC;
docs/screenshots/08-02.pngSELECT
status,
COUNT(*) AS order_count
FROM orders
GROUP BY status;
docs/screenshots/08-03.pngSELECT
category,
AVG(price) AS average_price
FROM products
GROUP BY category
ORDER BY average_price DESC;
docs/screenshots/08-04.pngSection 09
SELECT
city,
COUNT(*) AS customer_count
FROM customers
GROUP BY city
HAVING COUNT(*) > 1;
docs/screenshots/09-01.pngSELECT
category,
AVG(price) AS average_price
FROM products
GROUP BY category
HAVING AVG(price) > 10;
docs/screenshots/09-02.pngSection 10
INSERT INTO customers (name, city, email, level, created_at)
VALUES ('Grace Yang', 'Toronto', '[email protected]', 'normal', CURDATE());
docs/screenshots/10-01.pngSELECT *
FROM customers
WHERE email = '[email protected]';
docs/screenshots/10-02.pngUPDATE customers
SET level = 'vip'
WHERE email = '[email protected]';
docs/screenshots/10-03.pngDELETE FROM customers
WHERE email = '[email protected]';
docs/screenshots/10-04.pngSection 11
SELECT
o.id AS order_id,
o.order_no,
o.status,
o.order_date,
c.name AS customer_name,
c.city
FROM orders o
INNER JOIN customers c ON o.customer_id = c.id
ORDER BY o.order_date DESC;
docs/screenshots/11-01.pngSELECT
o.order_no,
c.name AS customer_name,
p.name AS product_name,
oi.quantity,
oi.unit_price
FROM order_items oi
INNER JOIN orders o ON oi.order_id = o.id
INNER JOIN customers c ON o.customer_id = c.id
INNER JOIN products p ON oi.product_id = p.id
ORDER BY o.order_date DESC, o.order_no;
docs/screenshots/11-02.pngSELECT
o.order_no,
p.name AS product_name,
oi.quantity,
oi.unit_price,
oi.quantity * oi.unit_price AS line_total
FROM order_items oi
INNER JOIN orders o ON oi.order_id = o.id
INNER JOIN products p ON oi.product_id = p.id;
docs/screenshots/11-03.pngSELECT
o.order_no,
c.name AS customer_name,
o.status,
SUM(oi.quantity * oi.unit_price) AS order_total
FROM orders o
INNER JOIN customers c ON o.customer_id = c.id
INNER JOIN order_items oi ON o.id = oi.order_id
GROUP BY o.id, o.order_no, c.name, o.status
ORDER BY order_total DESC;
docs/screenshots/11-04.pngSELECT
o.order_no,
c.name AS customer_name,
SUM(oi.quantity * oi.unit_price) AS order_total
FROM orders o
INNER JOIN customers c ON o.customer_id = c.id
INNER JOIN order_items oi ON o.id = oi.order_id
WHERE o.status = 'paid'
GROUP BY o.id, o.order_no, c.name
ORDER BY order_total DESC;
docs/screenshots/11-05.pngSection 12
SELECT
c.id,
c.name,
c.city,
COUNT(o.id) AS order_count
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
GROUP BY c.id, c.name, c.city
ORDER BY order_count DESC;
docs/screenshots/12-01.pngSELECT
c.*
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
WHERE o.id IS NULL;
docs/screenshots/12-02.pngSELECT
p.id,
p.name,
p.category,
COUNT(oi.id) AS sold_line_count
FROM products p
LEFT JOIN order_items oi ON p.id = oi.product_id
GROUP BY p.id, p.name, p.category
ORDER BY sold_line_count DESC;
docs/screenshots/12-03.pngSection 13
SELECT *
FROM products
WHERE price > (
SELECT AVG(price)
FROM products
);
docs/screenshots/13-01.pngSELECT *
FROM customers
WHERE id IN (
SELECT customer_id
FROM orders
);
docs/screenshots/13-02.pngSELECT *
FROM customers
WHERE id NOT IN (
SELECT customer_id
FROM orders
);
docs/screenshots/13-03.pngSection 14
SELECT
name,
price,
CASE
WHEN price >= 30 THEN 'high'
WHEN price >= 10 THEN 'middle'
ELSE 'low'
END AS price_level
FROM products;
docs/screenshots/14-01.pngSELECT
order_no,
status,
CASE status
WHEN 'pending' THEN '待付款'
WHEN 'paid' THEN '已付款'
WHEN 'shipped' THEN '已发货'
WHEN 'cancelled' THEN '已取消'
ELSE '未知'
END AS status_cn
FROM orders;
docs/screenshots/14-02.pngSection 15
SELECT
order_no,
order_date,
YEAR(order_date) AS order_year,
MONTH(order_date) AS order_month
FROM orders;
docs/screenshots/15-01.pngSELECT
DATE_FORMAT(order_date, '%Y-%m') AS order_month,
COUNT(*) AS order_count
FROM orders
GROUP BY DATE_FORMAT(order_date, '%Y-%m')
ORDER BY order_month;
docs/screenshots/15-02.pngSELECT *
FROM customers
WHERE created_at >= DATE_SUB(CURDATE(), INTERVAL 30 DAY);
docs/screenshots/15-03.pngSection 16
SELECT
p.id,
p.name,
p.category,
SUM(oi.quantity) AS total_quantity,
SUM(oi.quantity * oi.unit_price) AS total_sales
FROM products p
INNER JOIN order_items oi ON p.id = oi.product_id
INNER JOIN orders o ON oi.order_id = o.id
WHERE o.status IN ('paid', 'shipped')
GROUP BY p.id, p.name, p.category
ORDER BY total_sales DESC
LIMIT 5;
docs/screenshots/16-01.pngSELECT
c.id,
c.name,
c.city,
COALESCE(SUM(oi.quantity * oi.unit_price), 0) AS total_spent
FROM customers c
LEFT JOIN orders o
ON c.id = o.customer_id
AND o.status IN ('paid', 'shipped')
LEFT JOIN order_items oi
ON o.id = oi.order_id
GROUP BY c.id, c.name, c.city
ORDER BY total_spent DESC;
docs/screenshots/16-02.pngSELECT
c.id,
c.name,
SUM(oi.quantity * oi.unit_price) AS total_spent
FROM customers c
INNER JOIN orders o ON c.id = o.customer_id
INNER JOIN order_items oi ON o.id = oi.order_id
WHERE o.status IN ('paid', 'shipped')
GROUP BY c.id, c.name
HAVING SUM(oi.quantity * oi.unit_price) > 500
ORDER BY total_spent DESC;
docs/screenshots/16-03.pngSELECT
c.city,
SUM(oi.quantity * oi.unit_price) AS city_sales
FROM customers c
INNER JOIN orders o ON c.id = o.customer_id
INNER JOIN order_items oi ON o.id = oi.order_id
WHERE o.status IN ('paid', 'shipped')
GROUP BY c.city
ORDER BY city_sales DESC;
docs/screenshots/16-04.pngSELECT
o.order_no,
COUNT(DISTINCT oi.product_id) AS product_type_count,
SUM(oi.quantity) AS total_quantity
FROM orders o
INNER JOIN order_items oi ON o.id = oi.order_id
GROUP BY o.id, o.order_no
ORDER BY total_quantity DESC;
docs/screenshots/16-05.pngSection 17
CREATE INDEX idx_orders_order_date ON orders(order_date);
docs/screenshots/17-01.pngCREATE INDEX idx_orders_status ON orders(status);
docs/screenshots/17-02.pngCREATE INDEX idx_products_category ON products(category);
docs/screenshots/17-03.pngEXPLAIN
SELECT *
FROM orders
WHERE status = 'paid'
ORDER BY order_date DESC;
docs/screenshots/17-04.pngSection 18
用事务一次完成新增订单、插入订单明细、扣减库存，最后 COMMIT 提交。
-- 场景：创建订单，同时扣减库存
START TRANSACTION;
INSERT INTO orders (customer_id, order_no, status, order_date, note)
VALUES (2, 'SO20240601001', 'paid', CURDATE(), 'Transaction test order');
SET @new_order_id = LAST_INSERT_ID();
INSERT INTO order_items (order_id, product_id, quantity, unit_price)
VALUES
(@new_order_id, 1, 2, 29.90),
(@new_order_id, 8, 1, 6.50);
UPDATE products
SET stock = stock - 2
WHERE id = 1;
UPDATE products
SET stock = stock - 1
WHERE id = 8;
COMMIT;
docs/screenshots/18-01.png验证事务执行后订单、订单明细和产品信息是否正确关联。
SELECT
o.order_no,
o.status,
p.name AS product_name,
oi.quantity,
oi.unit_price
FROM orders o
INNER JOIN order_items oi ON o.id = oi.order_id
INNER JOIN products p ON oi.product_id = p.id
WHERE o.order_no = 'SO20240601001';
docs/screenshots/18-02.pngSection 19
把订单、客户、订单金额汇总逻辑封装成视图，后续可以直接查询视图。
DROP VIEW IF EXISTS v_order_summary;
CREATE VIEW v_order_summary AS
SELECT
o.id AS order_id,
o.order_no,
o.status,
o.order_date,
c.name AS customer_name,
c.city,
SUM(oi.quantity * oi.unit_price) AS order_total
FROM orders o
INNER JOIN customers c ON o.customer_id = c.id
INNER JOIN order_items oi ON o.id = oi.order_id
GROUP BY o.id, o.order_no, o.status, o.order_date, c.name, c.city;
docs/screenshots/19-01.png像查询普通表一样查询视图。
SELECT *
FROM v_order_summary
ORDER BY order_date DESC;
docs/screenshots/19-02.pngSection 20
建议先自己写 SQL，再回到前面的示例中查找类似写法。