MySQL最经典50道练习题

表名和字段

学生表

Student(s_id,s_name,s_birth,s_sex):学生编号、学生姓名、出生年月、学生性别。

课程表

Course(c_id,c_name,t_id):课程编号、课程名称、教师编号。

教师表

Teacher(t_id,t_name):教师编号、教师姓名。

成绩表

Score(s_id,c_id,s_score):学生编号、课程编号、分数。

建表语句

# 建表 # 学生表 CREATE TABLE `student`( `s_id` VARCHAR(20), `s_name` VARCHAR(20) NOT NULL DEFAULT '', `s_birth` VARCHAR(20) NOT NULL DEFAULT '', `s_sex` VARCHAR(10) NOT NULL DEFAULT '', PRIMARY KEY(`s_id`) ); # 课程表 CREATE TABLE `course`( `c_id` VARCHAR(20), `c_name` VARCHAR(20) NOT NULL DEFAULT '', `t_id` VARCHAR(20) NOT NULL, PRIMARY KEY(`c_id`) ); # 教师表 CREATE TABLE `teacher`( `t_id` VARCHAR(20), `t_name` VARCHAR(20) NOT NULL DEFAULT '', PRIMARY KEY(`t_id`) ); # 成绩表 CREATE TABLE `score`( `s_id` VARCHAR(20), `c_id` VARCHAR(20), `s_score` INT(3), PRIMARY KEY(`s_id`,`c_id`) ); # 插入数据 # 插入学生表测试数据 insert into Student values('1', '赵雷', '1990-01-01', '男'); insert into Student values('2', '钱电', '1990-12-21', '男'); insert into Student values('3', '孙风', '1990-05-20', '男'); insert into Student values('4', '李云', '1990-08-06', '男'); insert into Student values('5', '周梅', '1991-12-01', '女'); insert into Student values('6', '吴兰', '1992-03-01', '女'); insert into Student values('7', '郑竹', '1989-07-01', '女'); insert into Student values('8', '王菊', '1990-01-20', '女'); # 插入课程表测试数据 insert into Course values('1', '语文', '2'); insert into Course values('2', '数学', '1'); insert into Course values('3', '英语', '3'); # 插入教师表测试数据 insert into Teacher values('1', '张三'); insert into Teacher values('2', '李四'); insert into Teacher values('3', '王五'); # 插入成绩表测试数据 insert into Score values('1', '1', 80); insert into Score values('1', '2', 90); insert into Score values('1', '3', 99); insert into Score values('2', '1', 70); insert into Score values('2', '2', 60); insert into Score values('2', '3', 80); insert into Score values('3', '1', 80); insert into Score values('3', '2', 80); insert into Score values('3', '3', 80); insert into Score values('4', '1', 50); insert into Score values('4', '2', 30); insert into Score values('4', '3', 20); insert into Score values('5', '1', 76); insert into Score values('5', '2', 87); insert into Score values('6', '1', 31); insert into Score values('6', '3', 34); insert into Score values('7', '2', 89); insert into Score values('7', '3', 98);

题目及答案

# 1.查询"1"课程比"2"课程成绩高的学生的信息及课程分数。 SELECT student.*, s1.s_score AS "语文成绩", s2.s_score AS "数学成绩" FROM student LEFT JOIN score AS s1 ON student.s_id = s1.s_id AND s1.c_id = 1 LEFT JOIN score AS s2 ON student.s_id = s2.s_id AND s2.c_id = 2 WHERE s1.s_score > s2.s_score; # 2.查询"1"课程比"2"课程成绩低的学生的信息及课程分数。 SELECT student.*, s1.s_score AS "语文成绩", s2.s_score AS "数学成绩" FROM student LEFT JOIN score AS s1 ON s1.s_id = student.s_id AND s1.c_id = 1 LEFT JOIN score AS s2 ON s2.s_id = student.s_id AND s2.c_id = 2 WHERE s1.s_score < s2.s_score; # 3.查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩。 SELECT student.s_id AS "学生编号", student.s_name AS "学生姓名", ROUND(AVG(score.s_score),2) AS "平均成绩" FROM student LEFT JOIN score ON score.s_id = student.s_id GROUP BY student.s_id HAVING AVG(score.s_score) >= 60; # 4.查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩(包括有成绩的和无成绩的)。 SELECT student.s_id AS "学生编号", student.s_name AS "学生姓名", IFNULL(ROUND(AVG(score.s_score),2),0) AS "平均成绩" FROM student LEFT JOIN score ON score.s_id = student.s_id GROUP BY student.s_id HAVING AVG(score.s_score) < 60 OR AVG(score.s_score) IS NULL; # 5.查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩。 SELECT student.s_id AS "学生编号", student.s_name AS "学生姓名", COUNT(course.c_id) AS "选课总数", IFNULL(SUM(score.s_score),0) AS "所有课程总成绩" FROM student LEFT JOIN score ON student.s_id = score.s_id LEFT JOIN course ON course.c_id = score.c_id GROUP BY student.s_id; # 6.查询"李"姓老师的数量。 SELECT COUNT(t_id) FROM teacher WHERE t_name LIKE "李%"; # 7.查询学过"张三"老师授课的同学的信息。 SELECT student.* FROM student LEFT JOIN score ON student.s_id = score.s_id LEFT JOIN course ON score.c_id = course.c_id LEFT JOIN teacher ON course.t_id = teacher.t_id WHERE teacher.t_name = "张三"; # 8.查询没学过"张三"老师授课的同学的信息。 SELECT student.* FROM student WHERE student.s_id NOT IN ( SELECT student.s_id FROM student LEFT JOIN score ON score.s_id = student.s_id WHERE score.c_id IN ( SELECT course.c_id FROM course LEFT JOIN teacher ON course.t_id = teacher.t_id WHERE teacher.t_name = "张三" ) ) # 9.查询学过编号为"1"并且也学过编号为"2"的课程的同学的信息。 SELECT student.* FROM student INNER JOIN score ON score.s_id = student.s_id INNER JOIN course ON course.c_id = score.c_id GROUP BY student.s_id HAVING SUM(IF(course.c_id = 1 OR course.c_id = 2, 1, 0)) > 1; # 10.查询学过编号为"1"但是没有学过编号为"2"的课程的同学的信息。 SELECT student.* FROM student INNER JOIN score ON score.s_id = student.s_id AND score.c_id = 1 WHERE student.s_id NOT IN ( SELECT student.s_id FROM student INNER JOIN score ON score.s_id = student.s_id AND score.c_id = 2 ); # 11.查询没有学全所有课程的同学的信息。 SELECT student.* FROM student LEFT JOIN score ON score.s_id = student.s_id GROUP BY student.s_id HAVING COUNT(c_id) < ( SELECT COUNT(c_id) FROM course ); # 12.查询至少有一门课与学号为"1的同学所学相同的同学的信息。 SELECT student.* FROM student LEFT JOIN score ON score.s_id = student.s_id WHERE c_id IN ( SELECT c_id FROM score LEFT JOIN student ON score.s_id = student.s_id WHERE student.s_id = 1 ) GROUP BY student.s_id; # 13.查询和"1"号的同学学习的课程完全相同的其他同学的信息。 SELECT student.* FROM student LEFT JOIN score ON score.s_id = student.s_id GROUP BY student.s_id HAVING GROUP_CONCAT(c_id) = ( SELECT GROUP_CONCAT(c_id) FROM student LEFT JOIN score ON score.s_id = student.s_id GROUP BY student.s_id HAVING student.s_id = 1 ); # 14.查询没学过"张三"老师讲授的任一门课程的学生姓名。 SELECT s_name FROM student WHERE s_id NOT IN ( SELECT s_id FROM score INNER JOIN course ON course.c_id = score.c_id INNER JOIN teacher ON teacher.t_id = course.t_id AND t_name = "张三" ); # 15.查询两门及其以上不及格课程的同学的学号,姓名及其平均成绩。 SELECT student.s_id AS "学号", s_name AS "姓名", IFNULL(ROUND(AVG(s_score),2),0) AS "平均成绩" FROM student LEFT JOIN score ON score.s_id = student.s_id GROUP BY student.s_id HAVING SUM(IF(s_score < 60, 1, 0)) >= 2; SELECT st.s_id,st.s_name, sc.* FROM score AS sc LEFT JOIN student AS st ON sc.s_id = st.s_id WHERE sc.s_score < 60 GROUP BY sc.s_id; # 16.检索"1"课程分数小于60,按分数降序排列的学生信息。 SELECT student.*, s_score AS "分数" FROM student INNER JOIN score ON score.s_id = student.s_id AND c_id = 1 WHERE s_score < 60 ORDER BY s_score DESC; # 17.按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩。 SELECT s_name, IFNULL(s1.s_score, 0) AS "语文成绩", IFNULL(s2.s_score, 0) AS "数学成绩", IFNULL(s3.s_score, 0) AS "英语成绩", IFNULL(ROUND(AVG(s4.s_score), 2), 0) AS "平均成绩" FROM student LEFT JOIN score AS s1 ON s1.s_id = student.s_id AND s1.c_id = 1 LEFT JOIN score AS s2 ON s2.s_id = student.s_id AND s2.c_id = 2 LEFT JOIN score AS s3 ON s3.s_id = student.s_id AND s3.c_id = 3 LEFT JOIN score AS s4 ON s4.s_id = student.s_id GROUP BY student.s_id ORDER BY AVG(s4.s_score) DESC; # 18.查询各科成绩最高分、最低分和平均分:以如下形式显示:课程ID,课程name,最高分,最低分,平均分,及格率,中等率,优良率,优秀率。 # 及格为>=60,中等为:70-80,优良为:80-90,优秀为:>=90。 SELECT course.c_id AS "课程ID", c_name AS "课程名称", MAX(s_score) AS "最高分", MIN(s_score) AS "最低分", AVG(s_score) AS "平均分", (SUM(IF(s_score >= 60, 1, 0)) / COUNT(1)) AS "及格率", (SUM(IF(s_score >= 70 AND s_score < 80, 1, 0)) / COUNT(1)) AS "中等率", (SUM(IF(s_score >= 80 AND s_score < 90, 1, 0)) / COUNT(1)) AS "优良率", (SUM(IF(s_score >= 90, 1, 0)) / COUNT(1)) AS "优秀率" FROM course INNER JOIN score ON score.c_id = course.c_id GROUP BY course.c_id; # 19.按各科成绩进行排序,并显示排名。 SELECT c_name AS "课程名称", s_score AS "分数", ( CASE WHEN @p = s_score THEN @rank WHEN @p := s_score THEN @rank := @rank + 1 END ) AS "排名" FROM ( SELECT c_name, s_score FROM course INNER JOIN score ON score.c_id = course.c_id WHERE course.c_id = 1 ORDER BY s_score DESC ) AS tab, (SELECT @rank := 0, @p := NULL) AS r UNION ALL SELECT c_name AS "课程名称", s_score AS "分数", ( CASE WHEN @p2 = s_score THEN @rank2 WHEN @p2 := s_score THEN @rank2 := @rank2 + 1 END ) AS "排名" FROM ( SELECT c_name, s_score FROM course INNER JOIN score ON score.c_id = course.c_id WHERE course.c_id = 2 ORDER BY s_score DESC ) AS tab, (SELECT @rank2 := 0, @p2 := NULL) AS r UNION ALL SELECT c_name AS "课程名称", s_score AS "分数", ( CASE WHEN @p3 = s_score THEN @rank3 WHEN @p3 := s_score THEN @rank3 := @rank3 + 1 END ) AS "排名" FROM ( SELECT c_name, s_score FROM course INNER JOIN score ON score.c_id = course.c_id WHERE course.c_id = 3 ORDER BY s_score DESC ) AS tab, (SELECT @rank3 := 0, @p3 := NULL) AS r; # 20.查询学生的总成绩并进行排名。 SELECT s_name AS "学生姓名", IFNULL(SUM(s_score), 0) AS "总成绩" FROM student LEFT JOIN score ON score.s_id = student.s_id GROUP BY student.s_id ORDER BY SUM(s_score) DESC; # 21.查询不同老师所教不同课程平均分从高到低显示。 SELECT teacher.t_name AS "老师姓名", c_name AS "课程名称", AVG(s_score) AS "平均分" FROM teacher INNER JOIN course ON course.t_id = teacher.t_id INNER JOIN score ON score.c_id = course.c_id GROUP BY course.c_id ORDER BY AVG(s_score) DESC; # 22.查询所有课程的成绩第2名到第3名的学生信息及该课程成绩。 (SELECT c_name AS "课程名称", student.*, s_score AS "分数" FROM student INNER JOIN score ON student.s_id = score.s_id INNER JOIN course ON course.c_id = score.c_id AND course.c_id = 1 ORDER BY s_score DESC LIMIT 1, 2) UNION ALL (SELECT c_name AS "课程名称", student.*, s_score AS "分数" FROM student INNER JOIN score ON student.s_id = score.s_id INNER JOIN course ON course.c_id = score.c_id AND course.c_id = 2 ORDER BY s_score DESC LIMIT 1, 2) UNION ALL (SELECT c_name AS "课程名称", student.*, s_score AS "分数" FROM student INNER JOIN score ON student.s_id = score.s_id INNER JOIN course ON course.c_id = score.c_id AND course.c_id = 3 ORDER BY s_score DESC LIMIT 1, 2); # 23.统计各科成绩各分数段人数:课程编号,课程名称,[100-85],[85-70],[70-60],[0-60]及所占百分比。 SELECT course.c_id AS "课程编号", c_name AS "课程名称", SUM(IF(s_score > 85 AND s_score <= 100, 1, 0)) AS "[85-100]人数", (SUM(IF(s_score > 85 AND s_score <= 100, 1, 0)) / COUNT(1)) AS "所百分比", SUM(IF(s_score > 70 AND s_score <= 85, 1, 0)) AS "[70-85]人数", (SUM(IF(s_score > 70 AND s_score <= 85, 1, 0)) / COUNT(1)) AS "所百分比", SUM(IF(s_score > 60 AND s_score <= 70, 1, 0)) AS "[60-70]人数", (SUM(IF(s_score > 60 AND s_score <= 70, 1, 0)) / COUNT(1)) AS "所百分比", SUM(IF(s_score <= 60, 1, 0)) AS "[0-60]人数", (SUM(IF(s_score <= 60, 1, 0)) / COUNT(1)) AS "所百分比" FROM score INNER JOIN course ON score.c_id = course.c_id GROUP BY course.c_id; # 24.查询学生平均成绩及其名次。 SELECT s_name AS "学生姓名", avg AS "平均分", ( CASE WHEN @p = avg THEN @rank WHEN @p := avg THEN @rank := @rank + 1 END ) AS "排名" FROM ( SELECT s_name, ROUND(IFNULL(AVG(s_score), 0), 2) AS avg FROM student LEFT JOIN score ON score.s_id = student.s_id GROUP BY student.s_id ORDER BY AVG(s_score) DESC ) AS tab, (SELECT @rank := 0, @p := NULL) AS r; # 25.查询各科成绩前三名的记录。 (SELECT c_name AS "课程名称", s_score AS "分数" FROM score INNER JOIN course ON course.c_id = score.c_id AND course.c_id = 1 ORDER BY s_score DESC LIMIT 3) UNION ALL (SELECT c_name AS "课程名称", s_score AS "分数" FROM score INNER JOIN course ON course.c_id = score.c_id AND course.c_id = 2 ORDER BY s_score DESC LIMIT 3) UNION ALL (SELECT c_name AS "课程名称", s_score AS "分数" FROM score INNER JOIN course ON course.c_id = score.c_id AND course.c_id = 3 ORDER BY s_score DESC LIMIT 3) # 26.查询每门课程被选修的学生数。 SELECT c_name AS "课程名称", COUNT(s_id) AS "人数" FROM course INNER JOIN score ON score.c_id = course.c_id GROUP BY course.c_id; # 27.查询出只有两门课程的全部学生的学号和姓名。 SELECT student.s_id AS "学号", s_name AS "姓名" FROM student LEFT JOIN score ON score.s_id = student.s_id GROUP BY student.s_id HAVING COUNT(score.c_id) = 2; # 28.查询男生、女生人数。 SELECT s_sex AS "性别", COUNT(s_sex) AS "人数" FROM student GROUP BY s_sex; # 29.查询名字中含有"风"字的学生信息。 SELECT * FROM student WHERE s_name LIKE "%风%"; # 30.查询同名同性学生名单,并统计同名人数。 SELECT s_name AS "姓名", s_sex AS "性别", COUNT(s_name) AS "人数" FROM student GROUP BY s_name, s_sex HAVING COUNT(s_name) > 1; # 31.查询1990年出生的学生名单。 SELECT s_name AS "姓名" FROM student WHERE s_birth LIKE "1990%"; # 32.查询每门课程的平均成绩,结果按平均成绩降序排列,平均成绩相同时,按课程编号升序排列。 SELECT course.c_id AS "课程编号", c_name AS "课程名称", ROUND(AVG(s_score), 2) AS "平均成绩" FROM score INNER JOIN course ON course.c_id = score.c_id GROUP BY course.c_id ORDER BY AVG(s_score) DESC, course.c_id ASC; # 33.查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩。 SELECT student.s_id AS "编号", s_name AS "姓名", ROUND(AVG(s_score), 2) AS "平均成绩" FROM student LEFT JOIN score ON student.s_id = score.s_id GROUP BY student.s_id HAVING AVG(s_score) >= 85; # 34.查询课程名称为"数学",且分数低于60的学生姓名和分数。 SELECT s_name AS "学生姓名", s_score AS "分数" FROM student INNER JOIN score ON student.s_id = score.s_id INNER JOIN course ON course.c_id = score.c_id AND c_name = "数学" WHERE s_score < 60; # 35.查询所有学生的课程及分数情况。 SELECT student.s_id AS "学号", s_name AS "姓名", c_name AS "课程名称", IFNULL(s_score, 0) AS "分数" FROM student LEFT JOIN score ON score.s_id = student.s_id LEFT JOIN course ON course.c_id = score.c_id; # 36.查询任何一门课程成绩在70分以上的姓名、课程名称和分数。 SELECT s_name AS "姓名", c_name AS "课程名称", s_score AS "分数" FROM student LEFT JOIN score ON score.s_id = student.s_id LEFT JOIN course ON course.c_id = score.c_id WHERE s_score > 70; # 37.查询不及格的课程。 SELECT c_name AS "课程名称", s_score AS "分数" FROM course INNER JOIN score ON score.c_id = course.c_id WHERE s_score < 60; # 38.查询课程编号为"1程成绩在80分以上的学生的学号和姓名。 SELECT student.s_id AS "学号", s_name AS "姓名" FROM student INNER JOIN score ON score.s_id = student.s_id AND c_id = 1 AND s_score >= 80; # 39.求每门课程的学生人数。 SELECT c_name AS "课程名称", COUNT(s_id) AS "学生人数" FROM course INNER JOIN score ON score.c_id = course.c_id GROUP BY course.c_id; # 40.查询选修"张三"老师所授课程的学生中,成绩最高的学生信息及其成绩。 SELECT student.*, s_score AS "分数" FROM student INNER JOIN score ON score.s_id = student.s_id INNER JOIN course ON course.c_id = score.c_id INNER JOIN teacher ON teacher.t_id = course.t_id WHERE t_name = "张三" ORDER BY s_score DESC LIMIT 1; # 41.查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩。 SELECT student.s_id AS "学生学号", s_name AS "学生姓名", s1.c_id AS "课程编号", s1.s_score AS "分数" FROM student LEFT JOIN score AS s1 ON s1.s_id = student.s_id LEFT JOIN course AS c1 ON c1.c_id = s1.c_id WHERE ( SELECT COUNT(1) FROM student LEFT JOIN score AS s2 ON s2.s_id = student.s_id LEFT JOIN course AS c2 ON c2.c_id = s2.c_id WHERE s1.s_score = s2.s_score AND c1.c_id != c2.c_id ) > 1; # 42.查询每门功成绩最好的前两名。 ( SELECT c_name AS "课程名称", s_name AS "学生姓名", s_score AS "分数" FROM student LEFT JOIN score ON score.s_id = student.s_id LEFT JOIN course ON course.c_id = score.c_id WHERE course.c_id = 1 LIMIT 2 ) UNION ALL ( SELECT c_name AS "课程名称", s_name AS "学生姓名", s_score AS "分数" FROM student LEFT JOIN score ON score.s_id = student.s_id LEFT JOIN course ON course.c_id = score.c_id WHERE course.c_id = 2 LIMIT 2 ) UNION ALL ( SELECT c_name AS "课程名称", s_name AS "学生姓名", s_score AS "分数" FROM student LEFT JOIN score ON score.s_id = student.s_id LEFT JOIN course ON course.c_id = score.c_id WHERE course.c_id = 3 LIMIT 2 ); # 43.统计每门课程的学生选修人数(超过5人的课程才统计)。要求输出课程号和选修人数,查询结果按人数降序排列,若人数相同,按课程号升序排列。 SELECT c_id AS "课程编号", COUNT(s_score) AS "学生人数" FROM score GROUP BY c_id HAVING COUNT(s_score) > 5 ORDER BY COUNT(s_score) DESC, c_id ASC; # 44.检索至少选修两门课程的学生学号。 SELECT s_id AS "学号" FROM score GROUP BY s_id HAVING COUNT(c_id) >= 2; # 45.查询选修了全部课程的学生信息。 SELECT student.* FROM student LEFT JOIN score ON score.s_id = student.s_id GROUP BY student.s_id HAVING COUNT(c_id) = ( SELECT COUNT(1) FROM course ); # 46.查询各学生的年龄。 SELECT s_name AS "姓名", TIMESTAMPDIFF(YEAR, s_birth, NOW()) FROM student; # 47.查询本周过生日的学生。 SELECT student.* FROM student WHERE WEEK(NOW(), 1) = WEEK(CONCAT(YEAR(s_birth), '-', MONTH(s_birth), '-', DAY(s_birth)), 1); # 48.查询下周过生日的学生。 SELECT student.* FROM student WHERE WEEK(NOW(), 1) = WEEK(CONCAT(YEAR(s_birth), '-', MONTH(s_birth), '-', DAY(s_birth)), 1) - 1; # 49.查询本月过生日的学生。 SELECT student.* FROM student WHERE MONTH(NOW()) = MONTH(CONCAT(YEAR(s_birth), '-', MONTH(s_birth), '-', DAY(s_birth))); # 50.查询下月过生日的学生。 SELECT student.* FROM student WHERE MONTH(NOW()) = MONTH(CONCAT(YEAR(s_birth), '-', MONTH(s_birth), '-', DAY(s_birth))) - 1;

如有问题或有更优写法,欢迎大家留言提建议!


__EOF__

本文作者Nyantocat
本文链接https://www.cnblogs.com/SunnyGao/p/13874078.html
关于博主:评论和私信会在第一时间回复。或者直接私信我。
版权声明:本博客所有文章除特别声明外,均采用 BY-NC-SA 许可协议。转载请注明出处!
声援博主:如果您觉得文章对您有帮助,可以点击文章右下角推荐一下。您的鼓励是博主的最大动力!
posted @   Nyantocat  阅读(833)  评论(0编辑  收藏  举报
编辑推荐:
· Java 中堆内存和栈内存上的数据分布和特点
· 开发中对象命名的一点思考
· .NET Core内存结构体系(Windows环境)底层原理浅谈
· C# 深度学习:对抗生成网络(GAN)训练头像生成模型
· .NET 适配 HarmonyOS 进展
阅读排行:
· 手把手教你更优雅的享受 DeepSeek
· 腾讯元宝接入 DeepSeek R1 模型,支持深度思考 + 联网搜索,好用不卡机!
· AI工具推荐:领先的开源 AI 代码助手——Continue
· 探秘Transformer系列之(2)---总体架构
· V-Control:一个基于 .NET MAUI 的开箱即用的UI组件库
点击右上角即可分享
微信分享提示