MySQL 实战 50 题：从零构建学生选课系统，掌握核心查询技巧

发布时间：2026/7/14 20:32:23

1. 从零搭建学生选课系统数据库很多同学在学习MySQL时总感觉SQL语句抽象难懂其实最好的学习方法就是通过真实项目实战。今天我们就用学生选课系统这个经典案例带你从零开始构建完整的数据库并通过50个实战查询掌握核心SQL技能。先来看看我们要构建的系统包含哪些数据学生信息学号、姓名、性别、出生日期课程信息课程编号、课程名称、授课教师教师信息教师编号、姓名成绩信息学号、课程编号、分数打开你的MySQL客户端我用的MySQL 8.0跟着我一步步操作-- 创建数据库 CREATE DATABASE course_selection_system DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; USE course_selection_system;建表时要注意字段类型选择和约束条件设置。比如学生表的学号应该设为主键成绩表需要设置联合主键防止重复记录-- 学生表 CREATE TABLE student ( s_id VARCHAR(10) PRIMARY KEY, s_name VARCHAR(20) NOT NULL, s_sex ENUM(男,女) DEFAULT 男, s_birth DATE NOT NULL ) ENGINEInnoDB; -- 教师表 CREATE TABLE teacher ( t_id VARCHAR(10) PRIMARY KEY, t_name VARCHAR(20) NOT NULL ); -- 课程表注意外键关联教师 CREATE TABLE course ( c_id VARCHAR(10) PRIMARY KEY, c_name VARCHAR(30) NOT NULL, t_id VARCHAR(10), FOREIGN KEY (t_id) REFERENCES teacher(t_id) ); -- 成绩表联合主键外键约束 CREATE TABLE score ( s_id VARCHAR(10), c_id VARCHAR(10), s_score DECIMAL(5,2) CHECK (s_score BETWEEN 0 AND 100), PRIMARY KEY (s_id, c_id), FOREIGN KEY (s_id) REFERENCES student(s_id), FOREIGN KEY (c_id) REFERENCES course(c_id) );实际项目中我强烈建议使用InnoDB引擎它支持事务和外键约束。曾经有个项目用了MyISAM引擎结果数据一致性出了问题排查到凌晨3点...2. 填充测试数据的技巧有了表结构后我们需要插入合理的测试数据。这里分享几个实用技巧批量插入比单条INSERT效率高10倍以上日期字段使用标准格式YYYY-MM-DD注意外键约束顺序先插教师再课程-- 插入教师数据 INSERT INTO teacher VALUES (t01, 张教授), (t02, 李副教授), (t03, 王讲师); -- 插入课程数据 INSERT INTO course VALUES (c01, 数据库原理, t01), (c02, 数据结构, t02), (c03, 操作系统, t03); -- 插入学生数据 INSERT INTO student VALUES (s1001, 张三, 男, 2000-05-15), (s1002, 李四, 女, 2001-08-22), (s1003, 王五, 男, 1999-11-03); -- 插入成绩数据 INSERT INTO score VALUES (s1001, c01, 85.5), (s1001, c02, 92.0), (s1002, c01, 78.0), (s1003, c03, 88.5);验证数据是否完整SELECT * FROM student; SELECT * FROM course WHERE t_id t01;3. 基础查询实战3.1 单表查询要点最基本的SELECT语句也藏着不少学问。比如查询所有女生的信息-- 使用枚举类型更规范 SELECT * FROM student WHERE s_sex 女; -- 模糊查询姓张的学生 SELECT s_id, s_name FROM student WHERE s_name LIKE 张%;注意LIKE %三会导致全表扫描数据量大时要慎用3.2 多表连接查询这是实际业务中最常用的操作。内连接、左连接的区别一定要掌握-- 查询所有学生选课情况内连接 SELECT s.s_name, c.c_name, sc.s_score FROM student s JOIN score sc ON s.s_id sc.s_id JOIN course c ON sc.c_id c.c_id; -- 查询所有学生选课情况包含未选课学生 SELECT s.s_name, c.c_name, sc.s_score FROM student s LEFT JOIN score sc ON s.s_id sc.s_id LEFT JOIN course c ON sc.c_id c.c_id;4. 高级查询技巧4.1 聚合函数与分组统计每门课程的平均分和选修人数SELECT c.c_name, AVG(sc.s_score) AS avg_score, COUNT(sc.s_id) AS student_count FROM course c LEFT JOIN score sc ON c.c_id sc.c_id GROUP BY c.c_id HAVING avg_score 75; -- 筛选平均分大于75的课程4.2 子查询实战查询比数据库原理平均分高的课程SELECT c.c_name, AVG(sc.s_score) as avg_score FROM course c JOIN score sc ON c.c_id sc.c_id GROUP BY c.c_id HAVING avg_score ( SELECT AVG(s_score) FROM score WHERE c_id c01 );4.3 窗口函数应用MySQL 8.0 支持的窗口函数特别适合排名场景-- 按课程成绩排名并列名次处理 SELECT s.s_name, c.c_name, sc.s_score, DENSE_RANK() OVER (PARTITION BY sc.c_id ORDER BY sc.s_score DESC) AS rank FROM score sc JOIN student s ON sc.s_id s.s_id JOIN course c ON sc.c_id c.c_id;5. 综合实战案例5.1 查询各科成绩分布SELECT c.c_name, SUM(CASE WHEN sc.s_score 90 THEN 1 ELSE 0 END) AS 优秀, SUM(CASE WHEN sc.s_score 80 AND sc.s_score 90 THEN 1 ELSE 0 END) AS 良好, SUM(CASE WHEN sc.s_score 60 AND sc.s_score 80 THEN 1 ELSE 0 END) AS 及格, SUM(CASE WHEN sc.s_score 60 THEN 1 ELSE 0 END) AS 不及格 FROM course c LEFT JOIN score sc ON c.c_id sc.c_id GROUP BY c.c_id;5.2 查询学生年龄SELECT s_name, s_birth, TIMESTAMPDIFF(YEAR, s_birth, CURDATE()) AS age FROM student;5.3 查询没有选全课的学生SELECT s.s_name FROM student s WHERE ( SELECT COUNT(DISTINCT c_id) FROM score WHERE s_id s.s_id ) (SELECT COUNT(*) FROM course);这些实战题目基本覆盖了SQL的90%日常使用场景。建议每个例子都亲手执行一遍遇到报错不要慌仔细看错误信息这才是真正的学习过程。我在刚学MySQL时一个外键约束报错能卡半天但解决后印象特别深刻。