2.4 成绩表
insert into t_mysql_score values('01' , '01' , 80); insert into t_mysql_score values('01' , '02' , 90); insert into t_mysql_score values('01' , '03' , 99); insert into t_mysql_score values('02' , '01' , 70); insert into t_mysql_score values('02' , '02' , 60); insert into t_mysql_score values('02' , '03' , 80); insert into t_mysql_score values('03' , '01' , 80); insert into t_mysql_score values('03' , '02' , 80); insert into t_mysql_score values('03' , '03' , 80); insert into t_mysql_score values('04' , '01' , 50); insert into t_mysql_score values('04' , '02' , 30); insert into t_mysql_score values('04' , '03' , 20); insert into t_mysql_score values('05' , '01' , 76); insert into t_mysql_score values('05' , '02' , 87); insert into t_mysql_score values('06' , '01' , 31); insert into t_mysql_score values('06' , '03' , 34); insert into t_mysql_score values('07' , '02' , 89); insert into t_mysql_score values('07' , '03' , 98);
3. 题目
01）查询" 01 "课程比" 02 "课程成绩高的学生的信息及课程分数
SELECT t3.*, t1.score 语文, t2.score 数学 FROM ( SELECT * FROM t_mysql_score sc WHERE sc.cid = '01' ) t1, ( SELECT * FROM t_mysql_score sc WHERE sc.cid = '02' ) t2, t_mysql_student t3 WHERE t1.sid = t2.sid AND t1.sid = t3.sid AND t1.score > t2.score;
02）查询同时存在" 01 "课程和" 02 "课程的情况
SELECT t3.*, ( CASE WHEN t1.cid = '01' THEN t1.score END ) 语文, ( CASE WHEN t2.cid = '02' THEN t1.score END ) 数学 FROM ( SELECT * FROM t_mysql_score sc WHERE sc.cid = '01' ) t1, ( SELECT * FROM t_mysql_score sc WHERE sc.cid = '02' ) t2, t_mysql_student t3 WHERE t1.sid = t2.sid AND t1.sid = t3.sid
03）查询存在" 01 "课程但可能不存在" 02 "课程的情况(不存在时显示为 null )
SELECT t1.*, t2.score FROM ( SELECT * FROM t_mysql_score sc WHERE sc.cid = '01' ) t1 LEFT JOIN ( SELECT * FROM t_mysql_score sc2 WHERE sc2.cid = '02' ) t2 ON t1.sid = t2.sid
04）查询不存在" 01 "课程但存在" 02 "课程的情况
SELECT * FROM t_mysql_score sc WHERE sc.sid NOT IN ( SELECT sid FROM t_mysql_score WHERE cid = '01' ) AND sc.cid = '02'
05）查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩
SELECT s.sid, s.sname, ROUND( AVG( sc.score ), 2 ) 平均成绩 FROM t_mysql_score sc, t_mysql_student s WHERE sc.sid = s.sid GROUP BY s.sid, s.sname HAVING AVG( sc.score ) >= 60
06）查询在t_mysql_score表存在成绩的学生信息
SELECT * FROM t_mysql_student WHERE sid IN ( SELECT sc.sid FROM t_mysql_score sc GROUP BY sc.sid )
07）查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩(没成绩的显示为 null )
SELECT s.sid, s.sname, COUNT( sc.cid ) 选课总数, SUM( sc.score ) 总成绩 FROM t_mysql_score sc, t_mysql_student s WHERE sc.sid = s.sid GROUP BY s.sid, s.sname
08）查询「李」姓老师的数量
select COUNT(*) from t_mysql_teacher where tname like '李%'
09）查询学过「张三」老师授课的同学的信息
SELECT s.* FROM t_mysql_student s, t_mysql_teacher t, t_mysql_course c, t_mysql_score sc WHERE s.sid = sc.sid AND c.cid = sc.cid AND c.tid = t.tid AND t.tname = '张三';
10）查询没有学全所有课程的同学的信息
SELECT s.sid, s.sname, COUNT( sc.score ) n FROM t_mysql_student s, t_mysql_score sc WHERE s.sid = sc.sid GROUP BY s.sid, s.sname HAVING n < ( SELECT count( 1 ) FROM t_mysql_course )
11）查询没学过"张三"老师讲授的任一门课程的学生姓名
SELECT * FROM t_mysql_student WHERE sid NOT IN ( SELECT sc.sid FROM t_mysql_score sc, t_mysql_course c, t_mysql_teacher t WHERE sc.cid = c.cid AND c.tid = t.tid AND t.tname = '张三' )
12）查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩
SELECT s.sid, s.sname, ROUND( AVG( sc.score ), 2 ) FROM t_mysql_student s, t_mysql_score sc WHERE s.sid = sc.sid AND sc.score < 60 GROUP BY sc.sid HAVING count( * ) > 1
13）检索" 01 "课程分数小于 60，按分数降序排列的学生信息
SELECT s.*, sc.score FROM t_mysql_student s, t_mysql_score sc WHERE s.sid = sc.sid AND sc.cid = '01' AND sc.score < 60 ORDER BY sc.score DESC
14）按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
SELECT s.*, ROUND( AVG( sc.score ), 2 ) descNum, IFNULL(max(CASE WHEN sc.cid='01' then sc.score END),0) 语文, IFNULL(max(CASE WHEN sc.cid='02' then sc.score END),0) 数学, IFNULL(max(CASE WHEN sc.cid='03' then sc.score END),0) 英语 FROM t_mysql_student s, t_mysql_score sc WHERE s.sid = sc.sid GROUP BY s.sid ORDER BY descNum DESC
15）查询各科成绩最高分、最低分和平均分：
以如下形式显示：课程 ID，课程 name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90 ，要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列
SELECT sc.cid, max( sc.score ) AS 最高分, min( sc.score ) AS 最低分, ROUND( AVG( sc.score ), 2 ) 平均分, count( * ) AS 选修人数, CONCAT(ROUND(SUM(if(sc.score>=60,1,0))/COUNT(*)*100,2),'%') 及格率, CONCAT(ROUND(SUM(if(sc.score>=70 and sc.score<=80,1,0))/COUNT(*)*100,2),'%') 中等率, CONCAT(ROUND(SUM(if(sc.score>=80 and sc.score<=90,1,0))/COUNT(*)*100,2),'%') 优良率, CONCAT(ROUND(SUM(if(sc.score>=90,1,0))/COUNT(*)*100,2),'%') 优秀率 FROM t_mysql_score sc GROUP BY sc.cid ORDER BY count( * ) DESC, sc.cid ASC
尾记
学到这里...恭喜你的冒险之路就要结束了。在途中 (学习中) 我们可能会遇到许多困难，或许汗水也有泪水。但少年！你的路还远呢，人生不止这段旅程，你还有许多未知（本篇还没提及到的知识点）和精彩等着你去探索。每一次挑战都是一次成长的机会，每一次摔倒都是一次站起的力量。不要畏惧失败，因为失败是通向成功的必经之路。勇敢地面对困难，坚持不懈地追逐梦想，你将收获意想不到的成果。
记住，成功不是一蹴而就的，它需要时间、努力和毅力。尽管前方可能布满荆棘和曲折，但请相信自己的能力，坚定地向前迈进。当你感到疲惫时，想一想自己为什么开始，回想起过去的努力和奋斗，这将给你重新点燃激情的力量。无论你面对怎样的挑战，都要保持积极的心态和坚定的信念。内心的力量是无穷的，它能够驱使你克服一切困难。相信自己的实力，相信自己的潜力，你就能战胜任何艰难险阻。
同时，不要忘记身边的伙伴和支持者。他们是你成功的助推器，他们会在你最需要的时候给予你鼓励和支持。与志同道合的人一起前行，分享彼此的成长和喜悦，让友情和团队精神成为你前进的动力。
最后，永远保持对未知世界的好奇心和探索欲望。人生的旅程并不止于此，世界上还有无数的奇迹等待着你去发现。披荆斩棘的冒险之路只是人生中的一个起点，你可以用智慧和勇气开启更加辉煌的篇章。
因此，少年，继续前行吧！即使面临挫折，也要坚定地相信自己的实力和潜力。每一步都是人生的财富，每一次经历都是一次宝贵的成长。相信自己，勇敢地追逐梦想，你将创造属于自己的辉煌！向前走吧，少年！不要回头！
