5.5 实践练习：学生信息表CRUD操作

     恭喜你！你已经掌握了MySQL数据操作的四大核心语句：Create（插入）、Read（查询）、Update（更新）、Delete（删除），合称CRUD。本节将通过一个完整的“学生信息管理系统”案例，串联所有知识，完成从建表到数据维护的全程实战。

项目目标：学生信息管理

我们将通过以下完整的CRUD流程，管理一个 students 表：

1. Create：创建表结构，并向表中插入初始学生数据。

2. Read：执行各种条件查询，检索所需的学生信息。

3. Update：修改和更新学生的信息。

4. Delete：删除特定的学生记录。

第一步：创建表（CREATE TABLE）

首先，我们需要设计并创建一张学生表。请在你的数据库环境中执行以下SQL语句。

-- 1. 创建一个新的数据库（如果不存在）
CREATE DATABASE IF NOT EXISTS school_db;
USE school_db;

-- 2. 创建学生信息表 `students`
DROP TABLE IF EXISTS students; -- 如果表已存在，先删除（方便练习）

CREATE TABLE students (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY COMMENT '学生主键ID',
    student_no CHAR(10) NOT NULL UNIQUE COMMENT '学号，要求唯一',
    name VARCHAR(50) NOT NULL COMMENT '学生姓名',
    gender ENUM('男', '女') DEFAULT '男' COMMENT '性别',
    age TINYINT UNSIGNED COMMENT '年龄',
    major VARCHAR(100) COMMENT '专业',
    enrollment_date DATE NOT NULL COMMENT '入学日期',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '记录创建时间'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='学生基本信息表';

-- 3. 查看表结构，确认创建成功
DESC students;

表结构解析：

• id：主键，用于唯一标识每条记录，且自动增长。

• student_no：学号，具有 UNIQUE 约束，确保不会重复。

• gender：使用 ENUM 类型，限定只能填入‘男’或‘女’。

• enrollment_date：入学日期，使用 DATE 类型。

• created_at：记录创建时间，由数据库自动生成。

第二步：插入数据（INSERT）

现在，向空表中添加一些初始的学生记录。

-- 1. 插入单条学生记录
INSERT INTO students (student_no, name, gender, age, major, enrollment_date)
VALUES ('20240001', '张三', '男', 20, '计算机科学', '2024-09-01');

-- 2. 批量插入多条学生记录（高效方式）
INSERT INTO students (student_no, name, gender, age, major, enrollment_date)
VALUES
    ('20240002', '李四', '女', 19, '软件工程', '2024-09-01'),
    ('20240003', '王五', '男', 21, '数据科学', '2024-09-01'),
    ('20240004', '赵六', '女', 20, '网络工程', '2024-09-01'),
    ('20240005', '钱七', '男', 22, '人工智能', '2024-09-01'),
    ('20240006', '孙八', '女', 19, '计算机科学', '2024-09-01');

-- 3. 插入一条部分字段的数据（age和major允许为NULL）
INSERT INTO students (student_no, name, gender, enrollment_date)
VALUES ('20240007', '周九', '女', '2024-09-01');

-- 4. 验证数据插入
SELECT * FROM students;

执行后，你的表中应该有7条记录。

第三步：查询数据（SELECT）

根据不同的业务需求，进行多种查询。

-- 1. 基础查询：查看所有学生的所有信息
SELECT * FROM students;

-- 2. 投影查询：只查看学生的学号、姓名和专业
SELECT student_no, name, major FROM students;

-- 3. 条件查询（WHERE）: 查找所有‘计算机科学’专业的学生
SELECT student_no, name, age 
FROM students 
WHERE major = '计算机科学';

-- 4. 组合条件查询：查找年龄大于等于20岁的女生
SELECT * 
FROM students 
WHERE gender = '女' AND age >= 20;

-- 5. 模糊查询（LIKE）: 查找姓‘王’的学生
SELECT * 
FROM students 
WHERE name LIKE '王%';

-- 6. 排序查询（ORDER BY）: 按年龄从大到小排序
SELECT name, age, major 
FROM students 
ORDER BY age DESC;

-- 7. 去重查询（DISTINCT）: 查看学校有哪些不同的专业
SELECT DISTINCT major FROM students;

-- 8. 聚合查询：统计学生总数、平均年龄
SELECT 
    COUNT(*) AS '学生总数',
    AVG(age) AS '平均年龄',
    MAX(age) AS '最大年龄',
    MIN(age) AS '最小年龄'
FROM students;

第四步：更新数据（UPDATE）

当学生信息发生变化时，我们需要更新记录。

-- 【安全习惯】更新前，先用SELECT确认要更新的记录
SELECT * FROM students WHERE name = '周九';

-- 1. 更新单条记录：为‘周九’同学补全专业和年龄信息
UPDATE students
SET major = '信息安全', age = 20
WHERE name = '周九'; -- 关键：WHERE子句确保只更新目标行

-- 2. 批量更新记录：将所有‘计算机科学’专业更名为‘计算机科学与技术’
UPDATE students
SET major = '计算机科学与技术'
WHERE major = '计算机科学';

-- 3. 基于表达式的更新：将所有学生的年龄增加1岁（模拟新学年）
UPDATE students
SET age = age + 1
WHERE age IS NOT NULL; -- 避免NULL值参与运算

-- 验证更新结果
SELECT student_no, name, major, age FROM students ORDER BY id;

第五步：删除数据（DELETE）

对于离校或其他原因需要移除的记录，执行删除操作。

-- 【极端重要】删除前，务必先SELECT确认！
SELECT * FROM students WHERE name LIKE '测试%'; -- 假设没有符合条件的记录

-- 1. 删除特定记录：学号为‘20240007’的学生转学离开
DELETE FROM students
WHERE student_no = '20240007';

-- 2. 谨慎的批量删除：删除年龄大于22岁的学生记录（假设已毕业）
--    为了安全，我们先用事务
START TRANSACTION;
SELECT * FROM students WHERE age > 22; -- 先查看会删除谁
-- 如果确认结果正确，再执行
DELETE FROM students WHERE age > 22;
-- 如果发现删错了，立即执行 ROLLBACK; 如果正确，执行 COMMIT;
COMMIT;

-- 3. 【危险示范】绝对要避免的语句：没有WHERE条件的DELETE
-- DELETE FROM students; -- 这将清空整个表！切勿在练习中执行。

-- 查看删除后的最终数据
SELECT COUNT(*) AS '当前学生人数' FROM students;
SELECT * FROM students;

综合挑战：完整的事务处理场景

场景：新学期开始，你需要同时完成以下操作：

1. 新增一名转学生。

2. 为所有“软件工程”专业的学生年龄增加1岁。

3. 删除一名已退学的学生（学号：20240005）。

这些操作必须作为一个整体，要么全部成功，要么全部失败。

-- 开启事务，确保数据一致性
START TRANSACTION;

-- 1. 新增转学生
INSERT INTO students (student_no, name, gender, age, major, enrollment_date)
VALUES ('20240008', '吴十', '男', 21, '软件工程', CURDATE()); -- CURDATE()获取当前日期

-- 2. 更新软件工程专业学生年龄
UPDATE students 
SET age = age + 1 
WHERE major = '软件工程' AND age IS NOT NULL;

-- 3. 删除退学学生
DELETE FROM students 
WHERE student_no = '20240005';

-- 在提交前，可以查看一下即将发生的变化
-- SELECT * FROM students WHERE major = '软件工程' OR student_no = '20240008';

-- 如果所有操作确认无误，提交事务
COMMIT;

-- 如果任何一步有问题，可以回滚事务（取消所有更改）
-- ROLLBACK;

-- 查看最终结果
SELECT student_no, name, major, age FROM students ORDER BY id;

练习与总结

独立练习任务

请你独立完成以下任务，以巩固CRUD技能：

1. 查询：找出年龄在19到21岁之间（含）的所有男生，按学号排序。

2. 更新：将“人工智能”专业更名为“人工智能与机器学习”。

3. 插入：再插入2名你自己的虚拟同学信息。

4. 删除：删除所有专业为NULL的学生记录（如果存在）。

CRUD操作核心要点回顾

通过这个完整的练习，你已经将零散的知识点串联起来，体验了数据管理的真实流程。记住，对数据的任何修改操作（U和D），都必须怀有敬畏之心，养成“先验证，后操作”和“使用事务”的职业习惯。