mysql查询course_MySQL查询练习

1、自行创建测试数据；

详见"db5_结构+数据.sql"2、查询学生总人数；select count(sid) fromstudent ;3、查询“生物”课程和“物理”课程成绩都及格的学生id和姓名；selectsid,

snamefromstudentwheresidin(selectscore.student_idfromscoreinner join course on score.course_id=course.cidwherecourse.cnamein('生物','物理')and score.score >= 60

group byscore.student_idhaving

count(course_id)=2);4、查询每个年级的班级数，取出班级数最多的前三个年级；selectgnamefromclass_gradewheregidin(selectgrade_idfromclassgroup bygrade_idhaving

count(cid) in(selectt1.count_cidfrom(select distinct

count(cid) ascount_cidfromclassgroup bygrade_idorder bycount_ciddesclimit3)ast1

)

);5、查询平均成绩最高和最低的学生的id和姓名以及平均成绩；selectstudent.sid,

student.sname,

t1.avg_scorefromstudentinner join(selectstudent_id,avg(score) asavg_scorefromscoregroup bystudent_idhaving

avg(score) in(

(select

avg(score) asmax_avg_scorefromscoregroup bystudent_idorder by

avg(score) desclimit1),

(select

avg(score) asmin_avg_scorefromscoregroup bystudent_idorder by

avg(score) asclimit1)

)

)as t1 on student.sid =t1.student_id;6、查询每个年级的学生人数；selectt1.grade_id,count(t1.sid) ascount_studentfrom(selectstudent.sid,

class.grade_idfromstudentinner join class on student.class_id=class.cid

)ast1group byt1.grade_id;7、查询每位学生的学号，姓名，选课数，平均成绩；selectstudent.sid,

student.sname,

t1.count_course,

t1.avg_scorefromstudentleft join(selectstudent_id,count(course_id) ascount_course,avg(score) asavg_scorefromscoregroup bystudent_id

)as t1 on student.sid =t1.student_id;8、查询学生编号为“2”的学生的姓名、该学生成绩最高的课程名、成绩最低的课程名及分数；selectstudent.sname,

course.cname,

t1.scorefrom(selectstudent_id,

course_id,

scorefromscorewherestudent_id=2

and score in(

(select

min(score)fromscorewherestudent_id=2),

(select

max(score)fromscorewherestudent_id=2)

)

)ast1inner join student on t1.student_id=student.sidinner join course on t1.course_id=course.cid;9、查询姓“李”的老师的个数和所带班级数；selectteacher.tid,

teacher.tname,

t1.count_cidfromteacherleft join(selecttid,count(cid) ascount_cidfromteach2clswheretidin(selecttidfromteacherwheretnamelike '李%')group bytid

)as t1 on teacher.tid =t1.tidwhereteacher.tnamelike '李%';10、查询班级数小于5的年级id和年级名；selectgid,

gnamefromclass_gradewheregidin(selectgrade_idfromclassgroup bygrade_idhaving

count(caption)<5);11、查询班级信息，包括班级id、班级名称、年级、年级级别(12为低年级，34为中年级，56为高年级)，示例结果如下；

班级id 班级名称 年级 年级级别1一年一班 一年级 低

#case when ... then

selectclass.cid 班级id,

class.caption 班级名称,

class_grade.gname 年级,case when class_grade.gid between 1 and 2 then '低'

when class_grade.gid between 3 and 4 then '中'

when class_grade.gid between 5 and 6 then '高' else 0 end as '年级级别'

fromclass,

class_gradewhereclass.grade_id=class_grade.gid;12、查询学过“张三”老师2门课以上的同学的学号、姓名；selectsid,

snamefromstudentwheresidin(selectstudent_idfromscorewherecourse_idin(selectcourse.cidfromteacher,

coursewhereteacher.tid=course.teacher_idand teacher.tname = '张三')group bystudent_idhaving

count(course_id) > 2);13、查询教授课程超过2门的老师的id和姓名；selecttid,

tnamefromteacherwheretidin(selectteacher_idfromcoursegroup byteacher_idhaving

count(cid) > 2);14、查询学过编号“1”课程和编号“2”课程的同学的学号、姓名；selectsid,

snamefromstudentwheresidin(select distinctstudent_idfromscorewherecourse_idin(1,2)

);15、查询没有带过高年级的老师id和姓名；selecttid,

tnamefromteacherwheretidnot in(selecttidfromteach2clswherecidin(selectcidfromclasswheregrade_idin(5,6)

)

);16、查询学过“张三”老师所教的所有课的同学的学号、姓名；selectsid,

snamefromstudentwheresidin(selectstudent_idfromscoreinner join course on score.course_id=course.cidwhere teacher_id in(selectsidfromteacherwheresname='张三')

)17、查询带过超过2个班级的老师的id和姓名；selecttid,

tnamefromteacherwheretidin(selectteacher_idfromcoursegroup byteacher_idhaving

count(cid) > 2);18、查询课程编号“2”的成绩比课程编号“1”课程低的所有同学的学号、姓名；selectsid,

snamefromstudentwheresidin(selectt1.student_idfrom(selectstudent_id,

scorefromscorewherecourse_id= 2

group bystudent_id

)ast1,

(selectstudent_id,

scorefromscorewherecourse_id= 1

group bystudent_id

)ast2wheret1.student_id=t2.student_idand t1.score

);19、查询所带班级数最多的老师id和姓名；

# 考虑班级数并列最多的情况selecttid,

tnamefromteacherwheretidin(selecttidfromteach2clsgroup bytidhaving

count(cid) =(select

count(cid)fromteach2clsgroup bytidorder by

count(cid) desclimit1)

);20、查询有课程成绩小于60分的同学的学号、姓名；selectsid,

snamefromstudentwheresidin(select distinctstudent_idfromscorewherescore< 60);21、查询没有学全所有课的同学的学号、姓名；selectsid,

snamefromstudentwheresidnot in(selectstudent_idfromscoregroup bystudent_idhaving

count(course_id) =(select

count(cid)fromcourse

)

);22、查询至少有一门课与学号为“1”的同学所学相同的同学的学号和姓名；selectsid,

snamefromstudentwheresidin(selectstudent_idfromscorewherecourse_idin(selectcourse_idfromscorewherestudent_id= 1)group bystudent_id

);23、查询至少学过学号为“1”同学所选课程中任意一门课的其他同学学号和姓名；selectsid,

snamefromstudentwheresidin(selectstudent_idfromscorewherecourse_idin(selectcourse_idfromscorewherestudent_id= 1)group bystudent_id

)and sid != 1;24、查询和“2”号同学学习的课程完全相同的其他同学的学号和姓名；selectsid,

snamefromstudentwheresidin(selectscore.student_idfromscore,

(selectcourse_idfromscorewherestudent_id= 2)ast1wherescore.course_id=t1.course_idand score.student_id != 2

group byscore.student_idhaving

count(score.course_id) =(select

count(course_id)fromscorewherestudent_id= 2)

);25、删除学习“张三”老师课的score表记录；delete fromscorewherecourse_idin(selectcourse.cidfromcourse,

teacherwherecourse.teacher_id=teacher.tidand teacher.tname = '张三');26、向score表中插入一些记录，这些记录要求符合以下条件：①没有上过编号“2”课程的同学学号；②插入“2”号课程的平均成绩；

# 实际结果会和上面一题有冲突，张三老师所教的2号课程insert intoscore(student_id,course_id,score)selectt1.sid,2,

t2.avg_scorefrom(selectsidfromstudentwheresidnot in(selectstudent_idfromscorewherecourse_id= 2)

)ast1,

(select

avg(score) asavg_scorefromscorewherecourse_id= 2)ast2;27、按平均成绩从低到高显示所有学生的“语文”、“数学”、“英语”三门的课程成绩，按如下形式显示： 学生ID,语文,数学,英语,有效课程数,有效平均分；

# 有效成绩含所有课程，包括语数英selectt1.student_idas学生ID,

(select score.score from score left join course on score.course_id = course.cid where course.cname = '语文' and score.student_id = t1.student_id) as语文,

(select score.score from score left join course on score.course_id = course.cid where course.cname = '数学' and score.student_id = t1.student_id) as数学,

(select score.score from score left join course on score.course_id = course.cid where course.cname = '音乐' and score.student_id = t1.student_id) as英语,count(t1.course_id) as有效课程数,avg(t1.score) as有效平均分fromscoreast1group byt1.student_idorder by

avg(t1.score) asc;28、查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分；selectcourse.cidas课程ID,max(score.score) as最高分,min(score.score) as最低分fromcourseleft join score on course.cid =score.course_idgroup byscore.course_id;29、按各科平均成绩从低到高和及格率的百分数从高到低顺序；selectcourse_id,avg(score) asavg_score,sum(case when score.score > 60 then 1 else 0 end) / count(1) * 100 as及格率fromscoregroup bycourse_idorder by

avg(score) desc,

及格率asc;30、课程平均分从高到低显示(显示任课老师)；selectcourse.cname,

t1.avg_score,

teacher.tnamefromcourse,

teacher,

(selectcourse_id,avg(score) asavg_scorefromscoregroup bycourse_idorder byavg_scoredesc)ast1wherecourse.cid=t1.course_idand course.teacher_id =teacher.tidorder byt1.avg_scoredesc;31、查询各科成绩前三名的记录(不考虑成绩并列情况)selectscore.sid,

score.student_id,

score.course_id,

score.sid,

t1.first_score,

t1.second_score,

t1.third_scorefromscoreinner join(selects1.sid,

(select score from score as s2 where s1.course_id = s2.course_id order by score desc limit 0,1) asfirst_score,

(select score from score as s3 where s1.course_id = s3.course_id order by score desc limit 1,1) assecond_score,

(select score from score as s4 where s1.course_id = s4.course_id order by score desc limit 2,1) asthird_scorefromscoreass1

)as t1 on score.sid =t1.sidwherescore.scorein(

t1.first_score,

t1.second_score,

t1.third_score

);32、查询每门课程被选修的学生数；

# 包含了没有被选修的课程显示0selectcourse.cid,

ifnull(t1.count_students,0) ascount_studentfromcourseleft join(selectcourse_id,count(student_id) ascount_studentsfromscoregroup bycourse_id

)as t1 on course.cid =t1.course_id;33、查询选修了2门以上课程的全部学生的学号和姓名；selectsid,

snamefromstudentwheresidin(selectstudent_idfromscoregroup bystudent_idhaving

count(course_id) > 2);34、查询男生、女生的人数，按倒序排列；selectgender,count(sid) ascount_studentfromstudentgroup bygenderorder bycount_studentdesc;35、查询姓“张”的学生名单；selectstudent.sid,

student.sname,

student.gender,

class.captionfromstudentinner join class on student.class_id =class.cidwherestudent.snamelike '张%';36、查询同名同姓学生名单，并统计同名人数；selectsname,count(sname) ascount_snamefromstudentgroup bysnamehaving

count(sname) > 1;37、查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按课程号降序排列；selectcourse_id,avg(score) asavg_scorefromscoregroup bycourse_idorder byavg_score,

course_iddesc;38、查询课程名称为“数学”，且分数低于60的学生姓名和分数；selectstudent.sname,

t1.scorefromstudentinner join(selectscore.student_id,

score.scorefromscoreinner join course on score.course_id =course.cidwherecourse.cname= '数学'

and score.score < 60)as t1 on student.sid =t1.student_id;39、查询课程编号为“3”且课程成绩在80分以上的学生的学号和姓名；selectsid,

snamefromstudentwhere sid in(selectstudent_idfromscorewherecourse_id= 3

and score > 80);40、求选修了课程的学生人数selectcourse_id,count(student_id) ascount_studentfromscoregroup bycourse_id;41、查询选修“王五”老师所授课程的学生中，成绩最高和最低的学生姓名及其成绩；

# 教的课可能包含多门，按课程id来分的！包含了最高，最低成绩相等的情况selectstudent.sname,

t2.course_id,

t2.score,

t2.max_score,

t2.min_scorefromstudentinner join(selectscore.student_id,

score.course_id,

score.score,

t1.max_score,

t1.min_scorefromscore,

(selectcourse_id,max(score) asmax_score ,min(score) asmin_scorefromscorewherecourse_idin(selectcidfromcourseinner join teacher on course.teacher_id =teacher.tidwhereteacher.tname= '王五')group bycourse_id

)ast1wherescore.course_id=t1.course_idand score.score in(

max_score,

min_score

)

)as t2 on student.sid =t2.student_id;42、查询各个课程及相应的选修人数；

# 包含了没有被选修的课程显示0selectcourse.cname,

ifnull(t1.count_student,0) ascount_studentfromcourseleft join(selectcourse_id,count(student_id) ascount_studentfromscoregroup bycourse_id

)as t1 on course.cid =t1.course_id;43、查询不同课程但成绩相同的学生的学号、课程号、学生成绩；

# 不同的学生之间，成绩相同 这里有问题！！select distincts1.student_id,

s2.student_id,

s1.course_idass1_course_id,

s2.course_idass2_course_id,

s1.score,

s2.scorefromscoreass1,

scoreass2wheres1.course_id!=s2.course_idand s1.score =s2.score;select distinct# 同一个学生，成绩相同

s1.student_id,

s2.student_id,

s1.course_idass1_course_id,

s2.course_idass2_course_id,

s1.score,

s2.scorefromscoreass1,

scoreass2wheres1.student_id=s2.student_idand s1.course_id !=s2.course_idand s1.score =s2.score;44、查询每门课程成绩最好的前两名学生id和姓名；

# 这个有排名 包括了成绩相同的！selectc.sname,

d.cname,

a.scorefromscore ainner join(selectcourse_id, score, rankfrom(select a.course_id, a.score, count(*) asrankfrom (selectcourse_id, scorefromscoregroup bycourse_id, scoreorder by course_id, score desc) ainner join (selectcourse_id, scorefromscoregroup bycourse_id, scoreorder by course_id, score desc) bon a.course_id =b.course_idand a.score <=b.scoregroup bycourse_id, score

) t1where rank in (1, 2)order bycourse_id, rank

) bon a.course_id =b.course_idand a.score =b.scoreinner joinstudent con a.student_id =c.sidinner joincourse don a.course_id =d.cid;45、检索至少选修两门课程的学生学号；selectstudent_idfromscoregroup bystudent_idhaving

count(course_id) >= 2;46、查询没有学生选修的课程的课程号和课程名；selectcid,

cnamefromcoursewherecidnot in(selectcourse_idfromscoregroup bycourse_id

);47、查询没带过任何班级的老师id和姓名；selecttid,

tnamefromteacherwheretidnot in(selecttidfromteach2clsgroup bytid

);48、查询有两门以上课程超过80分的学生id及其平均成绩；selectstudent_id,avg(score) asavg_scorefromscorewherestudent_idin(selectstudent_idfromscorewherescore> 80

group bystudent_idhaving

count(course_id) > 2)group bystudent_id;49、检索“3”课程分数小于60，按分数降序排列的同学学号；selectstudent_id,

scorefromscorewherecourse_id= 3

and score < 60

order byscoredesc;50、删除编号为“2”的同学的“1”课程的成绩；delete fromscorewheresid=(selectt1.sidfrom(selectsidfromscorewherestudent_id= 2

and course_id = 1)ast1

);51、查询同时选修了物理课和生物课的学生id和姓名；selectsid,

snamefromstudentwheresidin(selectstudent_idfromscorewherecourse_idin(selectcidfromcoursewherecourse.cnamein('物理','生物')

)group bystudent_idhaving

count(course_id) = 2);