从零入门到电商实战

1. 什么是 MySQL？

MySQL 是客户端/服务器架构的关系型数据库管理系统。一台 MySQL 服务器可以管理多个数据库，每个数据库包含多张表。它使用 SQL（Structured Query Language）与客户端通信。

核心概念

• 数据库（Database）：类似一个文件夹，存放一组相关的表
• 表（Table）：类似一张 Excel 工作表，包含行和列
• 行（Row/Record）：一条完整数据记录
• 列（Column/Field）：一种数据字段类型
• 主键（Primary Key）：唯一标识每一行的字段
• 外键（Foreign Key）：关联另一张表主键的字段

2. 常用数据类型

类型	用途	示例
INT	整数	age INT
VARCHAR(N)	可变长字符串	name VARCHAR(100)
TEXT	长文本	content TEXT
DECIMAL(M,D)	精确小数	price DECIMAL(10,2)
DATE / DATETIME	日期时间	created_at DATETIME
BOOLEAN (TINYINT)	布尔值	is_active BOOLEAN
ENUM	枚举	gender ENUM('M','F')

3. 创建电商数据库实战

场景分析：一个简单电商需要三张表

• 用户表 users
• 商品表 products
• 订单表 orders（关联用户）

CREATE DATABASE shop CHARACTER SET utf8mb4;
USE shop;

-- 用户表
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    balance DECIMAL(10,2) DEFAULT 0.00,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 商品表
CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(200) NOT NULL,
    price DECIMAL(10,2) NOT NULL,
    stock INT DEFAULT 0,
    description TEXT
);

-- 订单表
CREATE TABLE orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    product_id INT NOT NULL,
    quantity INT NOT NULL DEFAULT 1,
    total_amount DECIMAL(10,2),
    status ENUM('pending','paid','shipped','done') DEFAULT 'pending',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id),
    FOREIGN KEY (product_id) REFERENCES products(id)
);

4. CRUD 操作大全

-- === INSERT ===
INSERT INTO users (username, email, password_hash) VALUES
    ('alice', 'alice@example.com', 'hashed_pwd1'),
    ('bob', 'bob@example.com', 'hashed_pwd2');

INSERT INTO products (name, price, stock, description) VALUES
    ('机械键盘', 299.00, 50, 'Cherry MX 青轴'),
    ('无线鼠标', 89.00, 200, '蓝牙 5.0 静音'),
    ('27寸显示器', 1999.00, 10, '4K IPS HDR');

-- === SELECT ===
-- 基本查询
SELECT * FROM products WHERE price < 500;

-- 聚合函数
SELECT COUNT(*) AS total_users FROM users;
SELECT AVG(price) AS avg_price FROM products;

-- 排序 + 限制
SELECT name, price FROM products ORDER BY price DESC LIMIT 5;

-- === UPDATE ===
UPDATE products SET stock = stock - 1 WHERE id = 1 AND stock > 0;

-- === DELETE ===
DELETE FROM products WHERE id = 999;  -- 删除无效商品

5. 多表连接

-- 创建一条订单
INSERT INTO orders (user_id, product_id, quantity, total_amount)
VALUES (1, 1, 2, 598.00);

-- INNER JOIN：查看订单详情
SELECT
    orders.id AS order_id,
    users.username,
    products.name AS product_name,
    orders.quantity,
    orders.total_amount,
    orders.status
FROM orders
INNER JOIN users ON orders.user_id = users.id
INNER JOIN products ON orders.product_id = products.id;

6. 索引优化

-- 为经常查询的列创建索引
CREATE INDEX idx_products_price ON products(price);
CREATE INDEX idx_orders_user_id ON orders(user_id);
CREATE INDEX idx_orders_status ON orders(status);

-- 查看查询是否使用索引
EXPLAIN SELECT * FROM orders WHERE user_id = 1;

7. 实战：查询"哪些商品卖得最好"

SELECT
    p.name,
    SUM(o.quantity) AS total_sold,
    SUM(o.total_amount) AS revenue
FROM orders o
JOIN products p ON o.product_id = p.id
WHERE o.status IN ('paid', 'shipped', 'done')
GROUP BY p.id
ORDER BY total_sold DESC;

思考题

1. 为什么 password_hash 用 VARCHAR(255) 而非直接在数据库存明文密码？
2. 订单中为什么要冗余 total_amount 而不每次都从 product.price × quantity 计算？
3. INDEX 能加速查询，为什么不能给所有列都加索引？