SQL练习题及答案与详细分析

数据表介绍--1.学生表Student(SId,Sname,Sage,Ssex)--SId 学生编号,Sname 学生姓名,Sage 出生年月,Ssex 学生性别--2.课程表Course(CId,Cname,TId)--CId 课程编号,Cname 课程名称,TId 教师编号--3.教师表Teacher(TId,Tname)--TId 教师编号,Tname 教师姓名--4.成绩表SC(SId,CId,score)--SId 学生编号,CId 课程编号,score 分数学生表 Studentcreate table Student(SId varchar(10),Sname varchar(10),Sage datetime,Ssex varchar(10)); insert into Student values(01 , 赵雷 , 1990-01-01 , 男); insert into Student values(02 , 钱电 , 1990-12-21 , 男); insert into Student values(03 , 孙风 , 1990-12-20 , 男); insert into Student values(04 , 李云 , 1990-12-06 , 男); insert into Student values(05 , 周梅 , 1991-12-01 , 女); insert into Student values(06 , 吴兰 , 1992-01-01 , 女); insert into Student values(07 , 郑竹 , 1989-01-01 , 女); insert into Student values(09 , 张三 , 2017-12-20 , 女); insert into Student values(10 , 李四 , 2017-12-25 , 女); insert into Student values(11 , 李四 , 2012-06-06 , 女); insert into Student values(12 , 赵六 , 2013-06-13 , 女); insert into Student values(13 , 孙七 , 2014-06-01 , 女);科目表 Coursecreate table Course(CId varchar(10),Cname nvarchar(10),TId varchar(10)); insert into Course values(01 , 语文 , 02); insert into Course values(02 , 数学 , 01); insert into Course values(03 , 英语 , 03);教师表 Teachercreate table Teacher(TId varchar(10),Tname varchar(10)); insert into Teacher values(01 , 张三); insert into Teacher values(02 , 李四); insert into Teacher values(03 , 王五);成绩表 SCcreate table SC(SId varchar(10),CId varchar(10),score decimal(18,1)); insert into SC values(01 , 01 , 80); insert into SC values(01 , 02 , 90); insert into SC values(01 , 03 , 99); insert into SC values(02 , 01 , 70); insert into SC values(02 , 02 , 60); insert into SC values(02 , 03 , 80); insert into SC values(03 , 01 , 80); insert into SC values(03 , 02 , 80); insert into SC values(03 , 03 , 80); insert into SC values(04 , 01 , 50); insert into SC values(04 , 02 , 30); insert into SC values(04 , 03 , 20); insert into SC values(05 , 01 , 76); insert into SC values(05 , 02 , 87); insert into SC values(06 , 01 , 31); insert into SC values(06 , 03 , 34); insert into SC values(07 , 02 , 89); insert into SC values(07 , 03 , 98);练习题目1、查询 01 课程比 02 课程成绩高的学生的信息及课程分数因为需要全部的学生信息则需要在sc表中得到符合条件的SId后与student表进行join可以left join 也可以 right joinselect * from Student RIGHT JOIN ( select t1.SId, class1, class2 from (select SId, score as class1 from sc where sc.CId 01)as t1, (select SId, score as class2 from sc where sc.CId 02)as t2 where t1.SId t2.SId AND t1.class1 t2.class2 )r on Student.SId r.SId; select * from ( select t1.SId, class1, class2 from (SELECT SId, score as class1 FROM sc WHERE sc.CId 01) AS t1, (SELECT SId, score as class2 FROM sc WHERE sc.CId 02) AS t2 where t1.SId t2.SId and t1.class1 t2.class2 ) r LEFT JOIN Student ON Student.SId r.SId;2、查询同时存在 01 课程和 02 课程的情况查询存在 01 课程但可能不存在 02 课程的情况(不存在时显示为 null )这一道就是明显需要使用join的情况了02可能不存在即为left join的右侧或right join 的左侧即可.select * from (select * from sc where sc.CId 01) as t1, (select * from sc where sc.CId 02) as t2 where t1.SId t2.SId; select * from (select * from sc where sc.CId 01) as t1 left join (select * from sc where sc.CId 02) as t2 on t1.SId t2.SId; select * from (select * from sc where sc.CId 02) as t2 right join (select * from sc where sc.CId 01) as t1 on t1.SId t2.SId;3 查询不存在 01 课程但存在 02 课程的情况select * from sc where sc.SId not in ( select SId from sc where sc.CId 01 ) AND sc.CId 02;4、查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩这里只用根据学生ID把成绩分组对分组中的score求平均值最后在选取结果中AVG大于60的即可. 注意这里必须要给计算得到的AVG结果一个alias.AS ss得到学生信息的时候既可以用join也可以用一般的联合搜索select student.SId,sname,ss from student,( select SId, AVG(score) as ss from sc GROUP BY SId HAVING AVG(score) 60 )r where student.sid r.sid; select Student.SId, Student.Sname, r.ss from Student right join( select SId, AVG(score) AS ss from sc GROUP BY SId HAVING AVG(score) 60 )r on Student.SId r.SId; select s.SId,ss,Sname from( select SId, AVG(score) as ss from sc GROUP BY SId HAVING AVG(score) 60 )r left join (select Student.SId, Student.Sname from Student)s on s.SId r.SId;5、查询在 SC 表存在成绩的学生信息select DISTINCT student.* from student,sc where student.SIdsc.SId;6.查询所有同学的学生编号、学生姓名、选课总数、所有课程的成绩总和联合查询不会显示没选课的学生select student.sid, student.sname,r.coursenumber,r.scoresum from student, (select sc.sid, sum(sc.score) as scoresum, count(sc.cid) as coursenumber from sc group by sc.sid)r where student.sid r.sid;如要显示没选课的学生(显示为NULL)需要使用join:select s.sid, s.sname,r.coursenumber,r.scoresum from ( (select student.sid,student.sname from student )s left join (select sc.sid, sum(sc.score) as scoresum, count(sc.cid) as coursenumber from sc group by sc.sid )r on s.sid r.sid );7.查有成绩的学生信息这一题涉及到in和exists的用法在这种小表中两种方法的效率都差不多但是请参考SQL查询中in和exists的区别分析当表2的记录数量非常大的时候选用exists比in要高效很多.EXISTS用于检查子查询是否至少会返回一行数据该子查询实际上并不返回任何数据而是返回值True或False.结论IN()适合B表比A表数据小的情况结论EXISTS()适合B表比A表数据大的情况select * from student where exists (select sc.sid from sc where student.sid sc.sid); select * from student where student.sid in (select sc.sid from sc);8.查询「李」姓老师的数量select count(*) from teacher where tname like 李%;9.查询学过「张三」老师授课的同学的信息多表联合查询select student.* from student,teacher,course,sc where student.sid sc.sid and course.cidsc.cid and course.tid teacher.tid and tname 张三;10.查询没有学全所有课程的同学的信息因为有学生什么课都没有选反向思考先查询选了所有课的学生再选择这些人之外的学生.select * from student where student.sid not in ( select sc.sid from sc group by sc.sid having count(sc.cid) (select count(cid) from course) );11.查询至少有一门课与学号为 01 的同学所学相同的同学的信息这个用联合查询也可以但是逻辑不清楚我觉得较为清楚的逻辑是这样的从sc表查询01同学的所有选课cid--从sc表查询所有同学的sid如果其cid在前面的结果中--从student表查询所有学生信息如果sid在前面的结果中select * from student where student.sid in ( select sc.sid from sc where sc.cid in( select sc.cid from sc where sc.sid 01 ) );12.查询和 01 号的同学学习的课程完全相同的其他同学的信息SELECT s.* FROM Student s WHERE s.SId 01 AND NOT EXISTS ( -- 01号选了但该同学没选的课一条都不能有 SELECT 1 FROM SC sc1 WHERE sc1.SId 01 AND NOT EXISTS ( SELECT 1 FROM SC sc2 WHERE sc2.SId s.SId AND sc2.CId sc1.CId ) ) AND NOT EXISTS ( -- 该同学选了但01号没选的课一条都不能有 SELECT 1 FROM SC sc2 WHERE sc2.SId s.SId AND NOT EXISTS ( SELECT 1 FROM SC sc1 WHERE sc1.SId 01 AND sc1.CId sc2.CId ) );13.查询没学过张三老师讲授的任一门课程的学生姓名仍然还是嵌套三层嵌套 或者多表联合查询select * from student where student.sid not in( select sc.sid from sc where sc.cid in( select course.cid from course where course.tid in( select teacher.tid from teacher where tname 张三 ) ) ); select * from student where student.sid not in( select sc.sid from sc,course,teacher where sc.cid course.cid and course.tid teacher.tid and teacher.tname 张三 );14.查询两门及其以上不及格课程的同学的学号姓名及其平均成绩从SC表中选取score小于60的并group by sidhaving count 大于1select student.SId, student.Sname,b.avg from student RIGHT JOIN (select sid, AVG(score) as avg from sc where sid in ( select sid from sc where score60 GROUP BY sid HAVING count(score)1) GROUP BY sid) b on student.sidb.sid;15.检索 01 课程分数小于 60按分数降序排列的学生信息双表联合查询在查询最后可以设置排序方式语法为ORDER BY ***** DESC\ASC;select student.*, sc.score from student, sc where student.sid sc.sid and sc.score 60 and cid 01 ORDER BY sc.score DESC;16.按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩select * from sc left join ( select sid,avg(score) as avscore from sc group by sid )r on sc.sid r.sid order by avscore desc;17.查询各科成绩最高分、最低分和平均分以如下形式显示课程 ID课程 name最高分最低分平均分及格率中等率优良率优秀率及格为60中等为70-80优良为80-90优秀为90要求输出课程号和选修人数查询结果按人数降序排列若人数相同按课程号升序排列select sc.CId , max(sc.score)as 最高分, min(sc.score)as 最低分, AVG(sc.score)as 平均分, count(*)as 选修人数, sum(case when sc.score60 then 1 else 0 end )/count(*)as 及格率, sum(case when sc.score70 and sc.score80 then 1 else 0 end )/count(*)as 中等率, sum(case when sc.score80 and sc.score90 then 1 else 0 end )/count(*)as 优良率, sum(case when sc.score90 then 1 else 0 end )/count(*)as 优秀率 from sc GROUP BY sc.CId ORDER BY count(*)DESC, sc.CId ASC;18.按各科成绩进行排序并显示排名 Score 重复时保留名次空缺这一道题有点tricky可以用变量但也有更为简单的方法即自交左交用sc中的score和自己进行对比来计算“比当前分数高的分数有几个”。select a.cid, a.sid, a.score, count(b.score) 1 as rank from sc as a left join sc as b on a.score b.score and a.cid b.cid group by a.cid, a.sid, a.score order by a.cid, rank asc;19.查询学生的总成绩并进行排名总分重复时不保留名次空缺这里主要学习一下使用变量。在SQL里面变量用来标识。-- 先初始化变量再查询 SET crank : 0; SELECT q.sid, q.total, crank : crank 1 AS rank FROM ( SELECT sc.sid, SUM(sc.score) AS total FROM sc GROUP BY sc.sid ORDER BY total DESC ) q ORDER BY q.total DESC;20.统计各科成绩各分数段人数课程编号课程名称[100-85][85-70][70-60][60-0] 及所占百分比group by以后的查询结果无法使用别名所以不要想着先单表group by计算出结果再从第二张表里添上课程信息而应该先将两张表join在一起得到所有想要的属性再对这张总表进行统计计算。这里就不算百分比了道理相同。注意一下用case when 返回1 以后的统计不是用count而是sumSELECT course.cname, course.cid, SUM(CASE WHEN sc.score 100 AND sc.score 85 THEN 1 ELSE 0 END) AS [100-85], SUM(CASE WHEN sc.score 85 AND sc.score 70 THEN 1 ELSE 0 END) AS [85-70], SUM(CASE WHEN sc.score 70 AND sc.score 60 THEN 1 ELSE 0 END) AS [70-60], SUM(CASE WHEN sc.score 60 AND sc.score 0 THEN 1 ELSE 0 END) AS [60-0] FROM sc LEFT JOIN course ON sc.cid course.cid GROUP BY course.cid, course.cname;21.查询各科成绩前三名的记录mysql不能group by 了以后取limit所以不要想着讨巧了。思路有两种第一种比较暴力计算比自己分数大的记录有几条如果小于3 就select因为对前三名来说不会有3个及以上的分数比自己大了最后再对所有select到的结果按照分数和课程编号排名即可。select * from sc where ( select count(*) from sc as a where sc.cid a.cid and sc.scorea.score ) 3 order by cid asc, sc.score desc;第二种比较灵巧一些用自身左交但是有点难以理解。先用自己交自己条件为a.cid b.cid and a.scoreb.score其实就是列出同一门课内所有分数比较的情况。想要查看完整的表可以select * from sc a left join sc b on a.cid b.cid and a.scoreb.score order by a.cid,a.score;结果查看发现结果是47行的一个表列出了类似 01号课里“30分小于50也小于70也小于80也小于90”“50分小于70小于80小于90”.....所以理论上对任何一门课来说分数最高的那三个记录在这张大表里通过a.sid和a.cid可以联合确定这个同学的这门课的这个分数究竟比多少个其他记录高/低如果这个特定的a.sid和a.cid组合出现在这张表里的次数少于3个那就意味着这个组合学号课号分数是这门课里排名前三的。所以下面这个计算中having count 部分其实count()或者任意其他列都可以这里制定了一个列只是因为比count()运行速度上更快。SELECT a.sid, a.cid, a.score FROM sc a LEFT JOIN sc b ON a.cid b.cid AND a.score b.score GROUP BY a.cid, a.sid, a.score HAVING COUNT(b.cid) 3 ORDER BY a.cid;22.查询每门课程被选修的学生数select cid, count(sid) from sc group by cid;23.查询出只选修两门课程的学生学号和姓名嵌套查询select student.sid, student.sname from student where student.sid in (select sc.sid from sc group by sc.sid having count(sc.cid)2 );联合查询SELECT student.SId, student.Sname FROM sc, student WHERE student.SId sc.SId GROUP BY sc.SId, student.Sname -- 补充 student.Sname 以兼容 ONLY_FULL_GROUP_BY HAVING COUNT(*) 2;24.查询男生、女生人数select ssex, count(*) from student group by ssex;25.查询名字中含有「风」字的学生信息select * from student where student.Sname like %风%26.查询同名学生名单并统计同名人数找到同名的名字并统计个数select sname, count(*) from student group by sname having count(*)1;嵌套查询列出同名的全部学生的信息select * from student where sname in ( select sname from student group by sname having count(*)1 );27.查询 1990 年出生的学生名单select * from student where YEAR(student.Sage)1990;28.查询每门课程的平均成绩结果按平均成绩降序排列平均成绩相同时按课程编号升序排列SELECT sc.cid, course.cname, AVG(sc.score) AS average FROM sc JOIN course ON sc.cid course.cid GROUP BY sc.cid, course.cname ORDER BY average DESC, sc.cid ASC;29.查询平均成绩大于等于 85 的所有学生的学号、姓名和平均成绩having也可以用来截取结果表在这里就先得到平均成绩总表再截取AVG大于85的即可.SET sql_mode(SELECT REPLACE(sql_mode,ONLY_FULL_GROUP_BY,)); select student.sid, student.sname, AVG(sc.score) as aver from student, sc where student.sid sc.sid group by sc.sid having aver 85;30.查询课程名称为「数学」且分数低于 60 的学生姓名和分数select student.sname, sc.score from student, sc, course where student.sid sc.sid and course.cid sc.cid and course.cname 数学 and sc.score 60;31.查询所有学生的课程及分数情况存在学生没成绩没选课的情况select student.sname, cid, score from student left join sc on student.sid sc.sid;32.查询任何一门课程成绩在 70 分以上的姓名、课程名称和分数select student.sname, course.cname,sc.score from student,course,sc where sc.score70 and student.sid sc.sid and sc.cid course.cid;33.查询存在不及格的课程可以用group by 来取唯一也可以用distinctselect cid from sc where score 60 group by cid; select DISTINCT sc.CId from sc where sc.score 60;34.查询课程编号为 01 且课程成绩在 80 分及以上的学生的学号和姓名select student.sid,student.sname from student,sc where cid01 and score80 and student.sid sc.sid;35.求每门课程的学生人数select sc.CId,count(*) as 学生人数 from sc GROUP BY sc.CId;36.成绩不重复查询选修「张三」老师所授课程的学生中成绩最高的学生信息及其成绩用having max()理论上也是对的但是下面那种按分数排序然后取limit 1的更直观可靠select student.*, sc.score, sc.cid from student, teacher, course,sc where teacher.tid course.tid and sc.sid student.sid and sc.cid course.cid and teacher.tname 张三 having max(sc.score); select student.*, sc.score, sc.cid from student, teacher, course,sc where teacher.tid course.tid and sc.sid student.sid and sc.cid course.cid and teacher.tname 张三 order by score desc limit 1;37.成绩有重复的情况下查询选修「张三」老师所授课程的学生中成绩最高的学生信息及其成绩UPDATE sc SET score90 where sid 07 and cid 02;select student.*, sc.score, sc.cid from student, teacher, course,sc where teacher.tid course.tid and sc.sid student.sid and sc.cid course.cid and teacher.tname 张三 and sc.score ( select Max(sc.score) from sc,student, teacher, course where teacher.tid course.tid and sc.sid student.sid and sc.cid course.cid and teacher.tname 张三 );38.查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩同上在这里用了inner join后会有概念是重复的记录“01 课与 03课”“03 课与 01 课”所以这里取唯一可以直接用group bySELECT a.cid, a.sid, a.score FROM sc AS a INNER JOIN sc AS b ON a.sid b.sid AND a.cid ! b.cid AND a.score b.score;39.查询每门功成绩最好的前两名select a.sid, a.cid, a.score from sc as a left join sc as b on a.cid b.cid and a.score b.score group by a.cid, a.sid, a.score -- 新增 a.score having count(b.cid) 2 order by a.cid;40.统计每门课程的学生选修人数超过 5 人的课程才统计select sc.cid, count(sid) as cc from sc group by cid having cc 5;41.检索至少选修两门课程的学生学号select sid, count(cid) as cc from sc group by sid having cc2;42.查询选修了全部课程的学生信息SELECT student.* FROM student WHERE student.SId IN ( SELECT sc.SId FROM sc GROUP BY sc.SId HAVING COUNT(*) (SELECT COUNT(*) FROM course) );43. 按照出生日期来算当前月日 出生年月的月日则年龄减一select student.SId as 学生编号,student.Sname as 学生姓名, TIMESTAMPDIFF(YEAR,student.Sage,CURDATE()) as 学生年龄 from student44.查询本周过生日的学生select * from student where WEEKOFYEAR(student.Sage)WEEKOFYEAR(CURDATE());45.查询下周过生日的学生select * from student where WEEKOFYEAR(student.Sage)WEEKOFYEAR(CURDATE())1;46.查询本月过生日的学生select * from student where MONTH(student.Sage)MONTH(CURDATE());47.查询下月过生日的学生select * from student where MONTH(student.Sage)MONTH(CURDATE())1;