第13章：基础实战（新手必练，逐个突破）

本章通过三个基础实战项目，帮助你巩固所学的 MySQL 知识，从基础操作到多表操作再到事务实战，逐步提升你的实战能力。

实战1：学生信息管理系统（基础操作实战）

13.1 需求分析

功能需求：

创建学生表，存储学生基本信息
实现学生信息的增、删、改、查操作
支持按班级、成绩等条件查询
保证数据的完整性和一致性

数据结构：

学生表（students）：id、姓名、年龄、性别、班级ID、成绩、创建时间
班级表（classes）：id、班级名称

13.2 核心实现

13.2.1 创建数据表

sql

-- 创建班级表
CREATE TABLE classes (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 创建学生表
CREATE TABLE students (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    age INT,
    gender ENUM('男', '女'),
    class_id INT,
    score DECIMAL(5,2),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (class_id) REFERENCES classes(id)
);

13.2.2 基础操作

1. 插入数据

sql

-- 插入班级数据
INSERT INTO classes (name) VALUES 
('一班'),
('二班'),
('三班');

-- 插入学生数据
INSERT INTO students (name, age, gender, class_id, score) VALUES
('张三', 18, '男', 1, 85.5),
('李四', 19, '女', 1, 92.0),
('王五', 18, '男', 2, 78.5),
('赵六', 19, '女', 2, 88.0),
('钱七', 18, '男', 3, 95.5);

2. 查询数据

sql

-- 查询所有学生
SELECT * FROM students;

-- 查询一班的学生
SELECT s.*, c.name AS class_name 
FROM students s
JOIN classes c ON s.class_id = c.id
WHERE c.name = '一班';

-- 查询成绩大于85分的学生
SELECT * FROM students WHERE score > 85;

-- 按成绩降序排序
SELECT * FROM students ORDER BY score DESC;

-- 统计每个班级的平均成绩
SELECT c.name AS class_name, AVG(s.score) AS avg_score
FROM students s
JOIN classes c ON s.class_id = c.id
GROUP BY c.id, c.name;

3. 更新数据

sql

-- 更新学生信息
UPDATE students 
SET age = 20, score = 90.0 
WHERE id = 1;

-- 更新班级名称
UPDATE classes 
SET name = '高一1班' 
WHERE id = 1;

4. 删除数据

sql

-- 删除指定学生
DELETE FROM students WHERE id = 5;

-- 删除没有学生的班级（安全删除）
DELETE FROM classes 
WHERE id NOT IN (SELECT DISTINCT class_id FROM students WHERE class_id IS NOT NULL);

13.3 实操

完整操作流程：

创建数据库

sql

CREATE DATABASE student_management;
USE student_management;

创建表结构
- 执行上面的 CREATE TABLE 语句
插入测试数据
- 执行上面的 INSERT 语句

验证数据

sql

SELECT * FROM classes;
SELECT * FROM students;

执行各种查询
- 尝试不同的查询语句
执行更新和删除操作
- 测试数据修改功能
验证数据一致性
- 确保外键约束正常工作
- 确保数据修改后状态正确

实战2：商品与订单管理（多表操作实战）

13.4 需求分析

功能需求：

创建商品表和订单表
实现商品信息管理
实现订单管理
支持多表连接查询
支持销售数据统计

数据结构：

商品表（products）：id、名称、价格、库存、描述
订单表（orders）：id、订单号、商品ID、数量、订单日期、总金额

13.5 核心实现

13.5.1 创建数据表

sql

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

-- 创建订单表
CREATE TABLE orders (
    id INT PRIMARY KEY AUTO_INCREMENT,
    order_no VARCHAR(20) NOT NULL UNIQUE,
    product_id INT,
    quantity INT NOT NULL,
    order_date DATE NOT NULL,
    total_amount DECIMAL(10,2) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (product_id) REFERENCES products(id)
);

13.5.2 多表操作

1. 插入数据

sql

-- 插入商品数据
INSERT INTO products (name, price, stock, description) VALUES
('iPhone 13', 5999.00, 100, '苹果手机'),
('iPad Pro', 6799.00, 50, '苹果平板'),
('MacBook Air', 7999.00, 30, '苹果笔记本'),
('AirPods Pro', 1999.00, 200, '苹果耳机');

-- 插入订单数据
INSERT INTO orders (order_no, product_id, quantity, order_date, total_amount) VALUES
('ORD20230101001', 1, 2, '2023-01-01', 11998.00),
('ORD20230101002', 1, 1, '2023-01-02', 5999.00),
('ORD20230103001', 2, 3, '2023-01-03', 20397.00),
('ORD20230104001', 3, 1, '2023-01-04', 7999.00),
('ORD20230105001', 4, 2, '2023-01-05', 3998.00);

2. 多表连接查询

sql

-- 查询订单详情（包含商品信息）
SELECT 
    o.id AS 订单ID,
    o.order_no AS 订单号,
    p.name AS 商品名称,
    p.price AS 单价,
    o.quantity AS 数量,
    o.total_amount AS 总金额,
    o.order_date AS 订单日期
FROM orders o
JOIN products p ON o.product_id = p.id;

-- 查询每个商品的销售数量和销售总额
SELECT 
    p.id AS 商品ID,
    p.name AS 商品名称,
    SUM(o.quantity) AS 销售数量,
    SUM(o.total_amount) AS 销售总额
FROM orders o
JOIN products p ON o.product_id = p.id
GROUP BY p.id, p.name
ORDER BY 销售总额 DESC;

-- 查询每天的销售情况
SELECT 
    o.order_date AS 日期,
    COUNT(*) AS 订单数,
    SUM(o.total_amount) AS 销售总额
FROM orders o
GROUP BY o.order_date
ORDER BY o.order_date;

3. 库存管理

sql

-- 更新商品库存（订单生成后）
UPDATE products p
JOIN (
    SELECT product_id, SUM(quantity) AS sold_quantity
    FROM orders
    GROUP BY product_id
) o ON p.id = o.product_id
SET p.stock = p.stock - o.sold_quantity;

-- 查询商品库存状态
SELECT 
    name AS 商品名称,
    stock AS 库存数量,
    CASE 
        WHEN stock > 50 THEN '充足'
        WHEN stock > 0 THEN '紧张'
        ELSE '缺货'
    END AS 库存状态
FROM products;

13.6 实操

完整操作流程：

创建数据库

sql

CREATE DATABASE product_management;
USE product_management;

创建表结构
- 执行上面的 CREATE TABLE 语句
插入测试数据
- 执行上面的 INSERT 语句
执行多表查询
- 测试订单详情查询
- 测试销售统计查询
更新库存
- 执行库存更新操作
- 验证库存状态
分析销售数据
- 生成销售报表
- 分析销售趋势

实战3：用户登录注册数据存储（事务实战）

13.7 需求分析

功能需求：

创建用户表，存储用户注册信息
实现用户注册功能
实现用户登录验证
使用事务保证数据安全
添加约束保证数据完整性

数据结构：

用户表（users）：id、用户名、密码、邮箱、注册时间、最后登录时间
用户详情表（user_profiles）：id、用户ID、真实姓名、手机号、地址

13.8 核心实现

13.8.1 创建数据表

sql

-- 创建用户表
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL UNIQUE,
    password VARCHAR(100) NOT NULL,
    email VARCHAR(100) NOT NULL UNIQUE,
    registered_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    last_login_at TIMESTAMP NULL
);

-- 创建用户详情表
CREATE TABLE user_profiles (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL UNIQUE,
    real_name VARCHAR(50),
    phone VARCHAR(20),
    address VARCHAR(200),
    FOREIGN KEY (user_id) REFERENCES users(id)
);

13.8.2 事务操作

1. 用户注册（使用事务）

sql

-- 开始事务
START TRANSACTION;

-- 插入用户基本信息
INSERT INTO users (username, password, email) VALUES
('zhangsan', '123456', 'zhangsan@example.com');

-- 获取刚插入的用户ID
SET @user_id = LAST_INSERT_ID();

-- 插入用户详情
INSERT INTO user_profiles (user_id, real_name, phone, address) VALUES
(@user_id, '张三', '13800138000', '北京市朝阳区');

-- 提交事务
COMMIT;

2. 用户登录

sql

-- 验证用户登录
SELECT * FROM users 
WHERE username = 'zhangsan' AND password = '123456';

-- 更新最后登录时间
UPDATE users 
SET last_login_at = CURRENT_TIMESTAMP 
WHERE username = 'zhangsan';

3. 安全注册（带验证）

sql

-- 安全的用户注册过程
START TRANSACTION;

-- 检查用户名是否已存在
SELECT COUNT(*) INTO @username_count FROM users WHERE username = 'zhangsan';

-- 检查邮箱是否已存在
SELECT COUNT(*) INTO @email_count FROM users WHERE email = 'zhangsan@example.com';

IF @username_count = 0 AND @email_count = 0 THEN
    -- 插入用户信息
    INSERT INTO users (username, password, email) VALUES
    ('zhangsan', '123456', 'zhangsan@example.com');
    
    SET @user_id = LAST_INSERT_ID();
    
    INSERT INTO user_profiles (user_id, real_name, phone, address) VALUES
    (@user_id, '张三', '13800138000', '北京市朝阳区');
    
    COMMIT;
    SELECT '注册成功' AS message;
ELSE
    ROLLBACK;
    SELECT '用户名或邮箱已存在' AS message;
END IF;

13.9 实操

完整操作流程：

创建数据库

sql

CREATE DATABASE user_management;
USE user_management;

创建表结构
- 执行上面的 CREATE TABLE 语句
测试用户注册
- 执行事务注册操作
- 验证数据是否正确插入
测试用户登录
- 执行登录验证
- 验证最后登录时间是否更新
测试注册失败场景
- 尝试注册已存在的用户名
- 验证事务是否正确回滚
数据查询
- 查询用户信息和详情
- 测试关联查询

13.10 实战总结

实战项目	核心知识点	技能收获
学生信息管理系统	基础 CRUD 操作、外键约束	掌握基本数据操作
商品与订单管理	多表连接、聚合函数、分组查询	掌握复杂查询技巧
用户登录注册	事务处理、数据验证、安全性	掌握数据安全保障

实战技巧：

数据完整性：合理使用约束确保数据正确性
查询优化：使用索引和合理的查询语句
事务安全：关键操作使用事务保证一致性
错误处理：考虑各种异常情况

下一步： 进入第14章，挑战更复杂的企业级实战项目！

第13章：基础实战（新手必练，逐个突破） ​

实战1：学生信息管理系统（基础操作实战） ​

13.1 需求分析 ​

13.2 核心实现 ​

13.2.1 创建数据表 ​

13.2.2 基础操作 ​

13.3 实操 ​

实战2：商品与订单管理（多表操作实战） ​

13.4 需求分析 ​

13.5 核心实现 ​

13.5.1 创建数据表 ​

13.5.2 多表操作 ​

13.6 实操 ​

实战3：用户登录注册数据存储（事务实战） ​

13.7 需求分析 ​

13.8 核心实现 ​

13.8.1 创建数据表 ​

13.8.2 事务操作 ​

13.9 实操 ​

13.10 实战总结 ​

第13章：基础实战（新手必练，逐个突破）

实战1：学生信息管理系统（基础操作实战）

13.1 需求分析

13.2 核心实现

13.2.1 创建数据表

13.2.2 基础操作

13.3 实操

实战2：商品与订单管理（多表操作实战）

13.4 需求分析

13.5 核心实现

13.5.1 创建数据表

13.5.2 多表操作

13.6 实操

实战3：用户登录注册数据存储（事务实战）

13.7 需求分析

13.8 核心实现

13.8.1 创建数据表

13.8.2 事务操作

13.9 实操

13.10 实战总结