MySQL 8.0 多表查询实战学生-课程-成绩-教师4表12个经典业务场景解析教务管理系统是典型的多表关联应用场景涉及学生、课程、成绩和教师四个核心实体。本文将基于这四张表通过12个真实业务需求深入剖析MySQL 8.0的多表查询技术。每个案例不仅提供可执行的SQL解决方案还会拆解查询逻辑并给出性能优化建议。1. 数据库设计与初始化首先建立四张基础表并插入测试数据-- 学生表 CREATE TABLE student ( s_id INT PRIMARY KEY, sname VARCHAR(20) NOT NULL, sage INT, sgender VARCHAR(8) ); -- 教师表 CREATE TABLE teacher ( t_id INT PRIMARY KEY, tname VARCHAR(20) NOT NULL ); -- 课程表 CREATE TABLE course ( c_id INT PRIMARY KEY, cname VARCHAR(20) NOT NULL, t_id INT, FOREIGN KEY (t_id) REFERENCES teacher(t_id) ); -- 成绩表学生-课程关系表 CREATE TABLE student_course ( s_id INT, c_id INT, score INT, PRIMARY KEY (s_id, c_id), FOREIGN KEY (s_id) REFERENCES student(s_id), FOREIGN KEY (c_id) REFERENCES course(c_id) );插入测试数据-- 学生数据 INSERT INTO student VALUES (1,刘一,18,男),(2,钱二,19,女), (3,张三,17,男),(4,李四,18,女), (5,王五,17,男),(6,赵六,19,女); -- 教师数据 INSERT INTO teacher VALUES (1,叶平),(2,贺高),(3,杨艳),(4,周磊); -- 课程数据 INSERT INTO course VALUES (1,语文,1),(2,数学,2),(3,英语,3),(4,物理,4); -- 成绩数据 INSERT INTO student_course VALUES (1,1,56),(1,2,78),(1,3,67),(1,4,58), (2,1,79),(2,2,81),(2,3,92),(2,4,68), (3,1,91),(3,2,47),(3,3,88),(3,4,56), (4,2,88),(4,3,90),(4,4,93), (5,1,46),(5,3,78),(5,4,53), (6,1,35),(6,2,68),(6,4,71);2. 基础查询场景2.1 查询所有学生的选课情况SELECT s.s_id, s.sname, GROUP_CONCAT(c.cname ORDER BY c.c_id) AS courses, COUNT(c.c_id) AS course_count FROM student s LEFT JOIN student_course sc ON s.s_id sc.s_id LEFT JOIN course c ON sc.c_id c.c_id GROUP BY s.s_id, s.sname;执行计划分析使用LEFT JOIN确保未选课学生也能显示GROUP_CONCAT合并多行结果为单行添加ORDER BY保证课程名称有序显示优化建议在student_course表的s_id和c_id字段上建立联合索引对于大数据量考虑分页查询2.2 查询每门课程的平均分及选修人数SELECT c.c_id, c.cname, ROUND(AVG(sc.score), 2) AS avg_score, COUNT(sc.s_id) AS student_count FROM course c LEFT JOIN student_course sc ON c.c_id sc.c_id GROUP BY c.c_id, c.cname ORDER BY avg_score DESC;关键点ROUND函数保留两位小数LEFT JOIN确保没有学生选修的课程也能显示ORDER BY按平均分降序排列3. 复杂业务场景3.1 查询未选修某老师课程的学生假设查询未选修叶平老师课程的学生SELECT s.s_id, s.sname FROM student s WHERE s.s_id NOT IN ( SELECT sc.s_id FROM student_course sc JOIN course c ON sc.c_id c.c_id JOIN teacher t ON c.t_id t.t_id WHERE t.tname 叶平 );替代方案使用LEFT JOINSELECT s.s_id, s.sname FROM student s LEFT JOIN ( SELECT DISTINCT sc.s_id FROM student_course sc JOIN course c ON sc.c_id c.c_id JOIN teacher t ON c.t_id t.t_id WHERE t.tname 叶平 ) AS tmp ON s.s_id tmp.s_id WHERE tmp.s_id IS NULL;性能对比NOT IN在子查询结果集小时效率高LEFT JOIN方案在大数据量时通常更优3.2 查询课程最高分学生信息查询每门课程的最高分学生SELECT c.c_id, c.cname, s.s_id, s.sname, max_scores.max_score FROM course c JOIN ( SELECT c_id, MAX(score) AS max_score FROM student_course GROUP BY c_id ) AS max_scores ON c.c_id max_scores.c_id JOIN student_course sc ON max_scores.c_id sc.c_id AND max_scores.max_score sc.score JOIN student s ON sc.s_id s.s_id;注意点使用子查询先找出每门课程的最高分再关联回原表获取学生信息如果有多人并列最高分会返回多条记录3.3 查询同时选修多门课程的学生查询同时选修了语文和数学的学生SELECT s.s_id, s.sname FROM student s WHERE s.s_id IN ( SELECT sc.s_id FROM student_course sc JOIN course c ON sc.c_id c.c_id WHERE c.cname 语文 ) AND s.s_id IN ( SELECT sc.s_id FROM student_course sc JOIN course c ON sc.c_id c.c_id WHERE c.cname 数学 );替代方案使用GROUP BYSELECT sc.s_id, s.sname FROM student_course sc JOIN course c ON sc.c_id c.c_id JOIN student s ON sc.s_id s.s_id WHERE c.cname IN (语文, 数学) GROUP BY sc.s_id, s.sname HAVING COUNT(DISTINCT c.c_id) 2;4. 统计分析场景4.1 学生成绩综合统计SELECT s.s_id, s.sname, COUNT(sc.c_id) AS course_count, SUM(sc.score) AS total_score, ROUND(AVG(sc.score), 2) AS avg_score, MAX(sc.score) AS max_score, MIN(sc.score) AS min_score FROM student s LEFT JOIN student_course sc ON s.s_id sc.s_id GROUP BY s.s_id, s.sname ORDER BY avg_score DESC;可视化建议使用CASE WHEN添加成绩等级CASE WHEN AVG(sc.score) 90 THEN A WHEN AVG(sc.score) 80 THEN B WHEN AVG(sc.score) 70 THEN C WHEN AVG(sc.score) 60 THEN D ELSE E END AS grade4.2 教师授课成绩分析SELECT t.t_id, t.tname, COUNT(DISTINCT c.c_id) AS course_count, COUNT(sc.s_id) AS student_count, ROUND(AVG(sc.score), 2) AS avg_score FROM teacher t LEFT JOIN course c ON t.t_id c.t_id LEFT JOIN student_course sc ON c.c_id sc.c_id GROUP BY t.t_id, t.tname ORDER BY avg_score DESC;5. 高级查询技巧5.1 使用窗口函数排名MySQL 8.0支持窗口函数可以更高效地实现排名SELECT s.s_id, s.sname, c.cname, sc.score, RANK() OVER (PARTITION BY c.c_id ORDER BY sc.score DESC) AS course_rank, DENSE_RANK() OVER (ORDER BY sc.score DESC) AS overall_rank FROM student s JOIN student_course sc ON s.s_id sc.s_id JOIN course c ON sc.c_id c.c_id;窗口函数说明RANK(): 相同分数会有相同排名后续排名会跳过DENSE_RANK(): 相同分数有相同排名但后续排名不跳过ROW_NUMBER(): 相同分数也会分配不同排名5.2 使用CTE(Common Table Expression)WITH course_avg AS ( SELECT c_id, AVG(score) AS avg_score FROM student_course GROUP BY c_id ) SELECT c.c_id, c.cname, ca.avg_score, RANK() OVER (ORDER BY ca.avg_score DESC) AS rank FROM course c JOIN course_avg ca ON c.c_id ca.c_id;CTE优势提高SQL可读性可以递归查询避免重复子查询6. 性能优化实践6.1 索引策略-- 基础索引 ALTER TABLE student_course ADD INDEX idx_sid (s_id); ALTER TABLE student_course ADD INDEX idx_cid (c_id); ALTER TABLE student_course ADD INDEX idx_score (score); -- 复合索引 ALTER TABLE student_course ADD INDEX idx_sid_cid (s_id, c_id); ALTER TABLE student_course ADD INDEX idx_cid_score (c_id, score);6.2 查询重写优化将相关子查询改为JOIN-- 优化前相关子查询 SELECT s.s_id, s.sname FROM student s WHERE EXISTS ( SELECT 1 FROM student_course sc WHERE sc.s_id s.s_id AND sc.score 60 ); -- 优化后使用JOIN SELECT DISTINCT s.s_id, s.sname FROM student s JOIN student_course sc ON s.s_id sc.s_id WHERE sc.score 60;6.3 使用EXPLAIN分析EXPLAIN SELECT s.s_id, s.sname FROM student s WHERE s.s_id IN ( SELECT sc.s_id FROM student_course sc WHERE sc.score 90 );关键指标type: 最好达到ref或eq_refpossible_keys: 可能使用的索引key: 实际使用的索引rows: 预估需要检查的行数7. 事务与并发控制教务系统需要处理并发选课场景START TRANSACTION; -- 检查课程容量 SELECT COUNT(*) INTO student_count FROM student_course WHERE c_id 3; -- 假设课程容量为50 IF student_count 50 THEN -- 插入选课记录 INSERT INTO student_course VALUES (6, 3, NULL); COMMIT; ELSE ROLLBACK; SELECT 选课人数已满 AS message; END IF;并发问题解决方案使用SELECT ... FOR UPDATE锁定记录设置合适的隔离级别考虑乐观锁机制8. 视图与存储过程8.1 创建常用视图CREATE VIEW v_student_score AS SELECT s.s_id, s.sname, c.cname, sc.score, t.tname AS teacher_name FROM student s JOIN student_course sc ON s.s_id sc.s_id JOIN course c ON sc.c_id c.c_id JOIN teacher t ON c.t_id t.t_id;8.2 创建成绩分析存储过程DELIMITER // CREATE PROCEDURE sp_analyze_course(IN course_id INT) BEGIN SELECT c.cname, COUNT(sc.s_id) AS student_count, AVG(sc.score) AS avg_score, MAX(sc.score) AS max_score, MIN(sc.score) AS min_score, SUM(CASE WHEN sc.score 60 THEN 1 ELSE 0 END) / COUNT(*) AS pass_rate FROM course c LEFT JOIN student_course sc ON c.c_id sc.c_id WHERE c.c_id course_id GROUP BY c.cname; END // DELIMITER ;9. JSON功能应用MySQL 8.0增强的JSON功能-- 将学生信息转为JSON SELECT JSON_OBJECT( id, s.s_id, name, s.sname, age, s.sage, gender, s.sgender, courses, ( SELECT JSON_ARRAYAGG( JSON_OBJECT( course_id, c.c_id, course_name, c.cname, score, sc.score ) ) FROM student_course sc JOIN course c ON sc.c_id c.c_id WHERE sc.s_id s.s_id ) ) AS student_json FROM student s LIMIT 3;10. 全文检索应用为课程名称添加全文索引ALTER TABLE course ADD FULLTEXT INDEX ft_cname (cname); -- 使用全文检索查询 SELECT * FROM course WHERE MATCH(cname) AGAINST(数学 物理 IN NATURAL LANGUAGE MODE);11. 数据安全与权限创建只读视图用户CREATE USER report_user% IDENTIFIED BY secure_password; GRANT SELECT ON school_db.* TO report_user%;12. 备份与恢复策略使用mysqldump备份特定表mysqldump -u root -p school_db student course teacher student_course school_data.sql恢复数据mysql -u root -p school_db school_data.sql
MySQL 8.0 多表查询实战学生-课程-成绩-教师4表12个经典业务场景解析教务管理系统是典型的多表关联应用场景涉及学生、课程、成绩和教师四个核心实体。本文将基于这四张表通过12个真实业务需求深入剖析MySQL 8.0的多表查询技术。每个案例不仅提供可执行的SQL解决方案还会拆解查询逻辑并给出性能优化建议。1. 数据库设计与初始化首先建立四张基础表并插入测试数据-- 学生表 CREATE TABLE student ( s_id INT PRIMARY KEY, sname VARCHAR(20) NOT NULL, sage INT, sgender VARCHAR(8) ); -- 教师表 CREATE TABLE teacher ( t_id INT PRIMARY KEY, tname VARCHAR(20) NOT NULL ); -- 课程表 CREATE TABLE course ( c_id INT PRIMARY KEY, cname VARCHAR(20) NOT NULL, t_id INT, FOREIGN KEY (t_id) REFERENCES teacher(t_id) ); -- 成绩表学生-课程关系表 CREATE TABLE student_course ( s_id INT, c_id INT, score INT, PRIMARY KEY (s_id, c_id), FOREIGN KEY (s_id) REFERENCES student(s_id), FOREIGN KEY (c_id) REFERENCES course(c_id) );插入测试数据-- 学生数据 INSERT INTO student VALUES (1,刘一,18,男),(2,钱二,19,女), (3,张三,17,男),(4,李四,18,女), (5,王五,17,男),(6,赵六,19,女); -- 教师数据 INSERT INTO teacher VALUES (1,叶平),(2,贺高),(3,杨艳),(4,周磊); -- 课程数据 INSERT INTO course VALUES (1,语文,1),(2,数学,2),(3,英语,3),(4,物理,4); -- 成绩数据 INSERT INTO student_course VALUES (1,1,56),(1,2,78),(1,3,67),(1,4,58), (2,1,79),(2,2,81),(2,3,92),(2,4,68), (3,1,91),(3,2,47),(3,3,88),(3,4,56), (4,2,88),(4,3,90),(4,4,93), (5,1,46),(5,3,78),(5,4,53), (6,1,35),(6,2,68),(6,4,71);2. 基础查询场景2.1 查询所有学生的选课情况SELECT s.s_id, s.sname, GROUP_CONCAT(c.cname ORDER BY c.c_id) AS courses, COUNT(c.c_id) AS course_count FROM student s LEFT JOIN student_course sc ON s.s_id sc.s_id LEFT JOIN course c ON sc.c_id c.c_id GROUP BY s.s_id, s.sname;执行计划分析使用LEFT JOIN确保未选课学生也能显示GROUP_CONCAT合并多行结果为单行添加ORDER BY保证课程名称有序显示优化建议在student_course表的s_id和c_id字段上建立联合索引对于大数据量考虑分页查询2.2 查询每门课程的平均分及选修人数SELECT c.c_id, c.cname, ROUND(AVG(sc.score), 2) AS avg_score, COUNT(sc.s_id) AS student_count FROM course c LEFT JOIN student_course sc ON c.c_id sc.c_id GROUP BY c.c_id, c.cname ORDER BY avg_score DESC;关键点ROUND函数保留两位小数LEFT JOIN确保没有学生选修的课程也能显示ORDER BY按平均分降序排列3. 复杂业务场景3.1 查询未选修某老师课程的学生假设查询未选修叶平老师课程的学生SELECT s.s_id, s.sname FROM student s WHERE s.s_id NOT IN ( SELECT sc.s_id FROM student_course sc JOIN course c ON sc.c_id c.c_id JOIN teacher t ON c.t_id t.t_id WHERE t.tname 叶平 );替代方案使用LEFT JOINSELECT s.s_id, s.sname FROM student s LEFT JOIN ( SELECT DISTINCT sc.s_id FROM student_course sc JOIN course c ON sc.c_id c.c_id JOIN teacher t ON c.t_id t.t_id WHERE t.tname 叶平 ) AS tmp ON s.s_id tmp.s_id WHERE tmp.s_id IS NULL;性能对比NOT IN在子查询结果集小时效率高LEFT JOIN方案在大数据量时通常更优3.2 查询课程最高分学生信息查询每门课程的最高分学生SELECT c.c_id, c.cname, s.s_id, s.sname, max_scores.max_score FROM course c JOIN ( SELECT c_id, MAX(score) AS max_score FROM student_course GROUP BY c_id ) AS max_scores ON c.c_id max_scores.c_id JOIN student_course sc ON max_scores.c_id sc.c_id AND max_scores.max_score sc.score JOIN student s ON sc.s_id s.s_id;注意点使用子查询先找出每门课程的最高分再关联回原表获取学生信息如果有多人并列最高分会返回多条记录3.3 查询同时选修多门课程的学生查询同时选修了语文和数学的学生SELECT s.s_id, s.sname FROM student s WHERE s.s_id IN ( SELECT sc.s_id FROM student_course sc JOIN course c ON sc.c_id c.c_id WHERE c.cname 语文 ) AND s.s_id IN ( SELECT sc.s_id FROM student_course sc JOIN course c ON sc.c_id c.c_id WHERE c.cname 数学 );替代方案使用GROUP BYSELECT sc.s_id, s.sname FROM student_course sc JOIN course c ON sc.c_id c.c_id JOIN student s ON sc.s_id s.s_id WHERE c.cname IN (语文, 数学) GROUP BY sc.s_id, s.sname HAVING COUNT(DISTINCT c.c_id) 2;4. 统计分析场景4.1 学生成绩综合统计SELECT s.s_id, s.sname, COUNT(sc.c_id) AS course_count, SUM(sc.score) AS total_score, ROUND(AVG(sc.score), 2) AS avg_score, MAX(sc.score) AS max_score, MIN(sc.score) AS min_score FROM student s LEFT JOIN student_course sc ON s.s_id sc.s_id GROUP BY s.s_id, s.sname ORDER BY avg_score DESC;可视化建议使用CASE WHEN添加成绩等级CASE WHEN AVG(sc.score) 90 THEN A WHEN AVG(sc.score) 80 THEN B WHEN AVG(sc.score) 70 THEN C WHEN AVG(sc.score) 60 THEN D ELSE E END AS grade4.2 教师授课成绩分析SELECT t.t_id, t.tname, COUNT(DISTINCT c.c_id) AS course_count, COUNT(sc.s_id) AS student_count, ROUND(AVG(sc.score), 2) AS avg_score FROM teacher t LEFT JOIN course c ON t.t_id c.t_id LEFT JOIN student_course sc ON c.c_id sc.c_id GROUP BY t.t_id, t.tname ORDER BY avg_score DESC;5. 高级查询技巧5.1 使用窗口函数排名MySQL 8.0支持窗口函数可以更高效地实现排名SELECT s.s_id, s.sname, c.cname, sc.score, RANK() OVER (PARTITION BY c.c_id ORDER BY sc.score DESC) AS course_rank, DENSE_RANK() OVER (ORDER BY sc.score DESC) AS overall_rank FROM student s JOIN student_course sc ON s.s_id sc.s_id JOIN course c ON sc.c_id c.c_id;窗口函数说明RANK(): 相同分数会有相同排名后续排名会跳过DENSE_RANK(): 相同分数有相同排名但后续排名不跳过ROW_NUMBER(): 相同分数也会分配不同排名5.2 使用CTE(Common Table Expression)WITH course_avg AS ( SELECT c_id, AVG(score) AS avg_score FROM student_course GROUP BY c_id ) SELECT c.c_id, c.cname, ca.avg_score, RANK() OVER (ORDER BY ca.avg_score DESC) AS rank FROM course c JOIN course_avg ca ON c.c_id ca.c_id;CTE优势提高SQL可读性可以递归查询避免重复子查询6. 性能优化实践6.1 索引策略-- 基础索引 ALTER TABLE student_course ADD INDEX idx_sid (s_id); ALTER TABLE student_course ADD INDEX idx_cid (c_id); ALTER TABLE student_course ADD INDEX idx_score (score); -- 复合索引 ALTER TABLE student_course ADD INDEX idx_sid_cid (s_id, c_id); ALTER TABLE student_course ADD INDEX idx_cid_score (c_id, score);6.2 查询重写优化将相关子查询改为JOIN-- 优化前相关子查询 SELECT s.s_id, s.sname FROM student s WHERE EXISTS ( SELECT 1 FROM student_course sc WHERE sc.s_id s.s_id AND sc.score 60 ); -- 优化后使用JOIN SELECT DISTINCT s.s_id, s.sname FROM student s JOIN student_course sc ON s.s_id sc.s_id WHERE sc.score 60;6.3 使用EXPLAIN分析EXPLAIN SELECT s.s_id, s.sname FROM student s WHERE s.s_id IN ( SELECT sc.s_id FROM student_course sc WHERE sc.score 90 );关键指标type: 最好达到ref或eq_refpossible_keys: 可能使用的索引key: 实际使用的索引rows: 预估需要检查的行数7. 事务与并发控制教务系统需要处理并发选课场景START TRANSACTION; -- 检查课程容量 SELECT COUNT(*) INTO student_count FROM student_course WHERE c_id 3; -- 假设课程容量为50 IF student_count 50 THEN -- 插入选课记录 INSERT INTO student_course VALUES (6, 3, NULL); COMMIT; ELSE ROLLBACK; SELECT 选课人数已满 AS message; END IF;并发问题解决方案使用SELECT ... FOR UPDATE锁定记录设置合适的隔离级别考虑乐观锁机制8. 视图与存储过程8.1 创建常用视图CREATE VIEW v_student_score AS SELECT s.s_id, s.sname, c.cname, sc.score, t.tname AS teacher_name FROM student s JOIN student_course sc ON s.s_id sc.s_id JOIN course c ON sc.c_id c.c_id JOIN teacher t ON c.t_id t.t_id;8.2 创建成绩分析存储过程DELIMITER // CREATE PROCEDURE sp_analyze_course(IN course_id INT) BEGIN SELECT c.cname, COUNT(sc.s_id) AS student_count, AVG(sc.score) AS avg_score, MAX(sc.score) AS max_score, MIN(sc.score) AS min_score, SUM(CASE WHEN sc.score 60 THEN 1 ELSE 0 END) / COUNT(*) AS pass_rate FROM course c LEFT JOIN student_course sc ON c.c_id sc.c_id WHERE c.c_id course_id GROUP BY c.cname; END // DELIMITER ;9. JSON功能应用MySQL 8.0增强的JSON功能-- 将学生信息转为JSON SELECT JSON_OBJECT( id, s.s_id, name, s.sname, age, s.sage, gender, s.sgender, courses, ( SELECT JSON_ARRAYAGG( JSON_OBJECT( course_id, c.c_id, course_name, c.cname, score, sc.score ) ) FROM student_course sc JOIN course c ON sc.c_id c.c_id WHERE sc.s_id s.s_id ) ) AS student_json FROM student s LIMIT 3;10. 全文检索应用为课程名称添加全文索引ALTER TABLE course ADD FULLTEXT INDEX ft_cname (cname); -- 使用全文检索查询 SELECT * FROM course WHERE MATCH(cname) AGAINST(数学 物理 IN NATURAL LANGUAGE MODE);11. 数据安全与权限创建只读视图用户CREATE USER report_user% IDENTIFIED BY secure_password; GRANT SELECT ON school_db.* TO report_user%;12. 备份与恢复策略使用mysqldump备份特定表mysqldump -u root -p school_db student course teacher student_course school_data.sql恢复数据mysql -u root -p school_db school_data.sql
![[C++]内存管理：串顺序存储的内存回收](http://pic.xiahunao.cn/yaotu/[C++]内存管理：串顺序存储的内存回收)
