MySQL大作业
一、题目
1、查询所有的课程的名称以及对应的任课老师姓名 2、查询学生表中男女生各有多少人 3、查询物理成绩等于100的学生的姓名 4、查询平均成绩大于八十分的同学的姓名和平均成绩 5、查询所有学生的学号,姓名,选课数,总成绩 6、 查询姓李老师的个数 7、 查询没有报李平老师课的学生姓名 8、 查询物理课程的分数比生物课程的分数高的学生的学号 9、 查询没有同时选修物理课程和体育课程的学生姓名 10、查询挂科超过两门(包括两门)的学生姓名和班级 11、查询选修了所有课程的学生姓名 12、查询李平老师教的课程的所有成绩记录 13、查询全部学生都选修了的课程号和课程名 14、查询每门课程被选修的次数 15、查询只选修了一门课程的学生学号和姓名 16、查询所有学生考出的成绩并按从高到低排序(成绩去重) 17、查询平均成绩大于85的学生姓名和平均成绩 18、查询生物成绩不及格的学生姓名和对应生物分数 19、查询在所有选修了李平老师课程的学生中,这些课程(李平老师的课程,不是所有课程)平均成绩最高的学生姓名 20、查询每门课程成绩最好的课程id、学生姓名和分数
21、查询不同课程但成绩相同的课程号、学生号、成绩
22、查询没学过“李平”老师课程的学生姓名以及选修的课程名称
23、查询所有选修了学号为2的同学选修过的一门或者多门课程的同学学号和姓名
24、任课最多的老师中学生单科成绩最高的课程id、学生姓名和分数
二、答案
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
1、查询所有的课程的名称以及对应的任课老师姓名 SELECT course.cname, teacher.tname FROM course INNER JOIN teacher ON course.teacher_id = teacher.tid; 2、查询学生表中男女生各有多少人 SELECT gender, count(sid) FROM student GROUP BY gender; 3、查询物理成绩等于100的学生的姓名 SELECT sname FROM student WHERE sid IN ( SELECT score.student_id FROM score LEFT JOIN course ON score.course_id = course.cid WHERE score.num = 100 AND course.cname = "物理" ); 4、查询平均成绩大于八十分的同学的姓名和平均成绩 SELECT t1.sname, t2.avg_num FROM student t1 INNER JOIN ( SELECT student_id, avg(num) avg_num FROM score GROUP BY student_id HAVING avg(num) > 80 ) t2 ON t1.sid = t2.student_id; 5、查询所有学生的学号,姓名,选课数,总成绩(注意:对于那些没有选修任何课程的学生也算在内) SELECT sid, sname, t1.count_course, t1.sum_num FROM student LEFT JOIN ( SELECT student_id, count(course_id) count_course, sum(num) sum_num FROM score GROUP BY student_id ) t1 ON student.sid = t1.student_id; 6、 查询姓李老师的个数 SELECT count(tid) FROM teacher WHERE tname LIKE '李%'; 7、 查询没有报李平老师课的学生姓名(找出报名李平老师课程的学生,然后取反就可以) SELECT sname FROM student WHERE sid NOT IN ( SELECT DISTINCT student_id FROM score WHERE course_id IN ( SELECT cid FROM course WHERE teacher_id = ( SELECT tid FROM teacher WHERE tname = '李平' ) ) ); 8、 查询物理课程的分数比生物课程的分数高的学生的学号 SELECT t1.student_id FROM ( SELECT student_id, num FROM score LEFT JOIN course ON score.course_id = course.cid WHERE cname = '物理' ) t1 LEFT JOIN ( SELECT student_id, num FROM score LEFT JOIN course ON score.course_id = course.cid WHERE cname = '生物' ) t2 ON t1.student_id = t2.student_id WHERE t1.num > t2.num; 9、 查询没有同时选修物理课程和体育课程的学生姓名(没有同时选修指的是选修了一门的,思路是得到物理+体育课程的学生信息表,然后基于学生分组,统计count(课程)=1) SELECT sname FROM student WHERE sid IN ( SELECT student_id FROM score WHERE course_id IN ( SELECT cid FROM course WHERE cname IN ('物理', '体育') ) GROUP BY student_id HAVING count(student_id) = 1 ); 10、查询挂科超过两门(包括两门)的学生姓名和班级(求出<60的表,然后对学生进行分组,统计课程数目>=2) # 方式一: SELECT t1.sname, t2.caption FROM ( SELECT class_id, sname FROM student WHERE sid IN ( SELECT student_id FROM score WHERE num < 60 GROUP BY student_id HAVING count(course_id) > 1 ) ) t1 INNER JOIN (SELECT cid, caption FROM class) t2 ON t1.class_id = t2.cid; # 方式二: SELECT student.sname, class.caption FROM student INNER JOIN ( SELECT student_id FROM score WHERE num < 60 GROUP BY student_id HAVING count(course_id) >= 2 ) AS t1 INNER JOIN class ON student.sid = t1.student_id AND student.class_id = class.cid; 11、查询选修了所有课程的学生姓名(先从course表统计课程的总数,然后基于score表按照student_id分组,统计课程数据等于课程总数即可) SELECT sname FROM student WHERE sid IN ( SELECT student_id FROM score GROUP BY student_id HAVING count(course_id) = ( SELECT count(cid) AS course_total FROM course ) ); 12、查询李平老师教的课程的所有成绩记录 # 方式一: SELECT * FROM score WHERE course_id IN ( SELECT cid FROM course WHERE teacher_id = ( SELECT tid FROM teacher WHERE tname = '李平' ) ); # 方式二: SELECT * FROM score WHERE course_id IN ( SELECT cid FROM course INNER JOIN teacher ON course.teacher_id = teacher.tid WHERE teacher.tname = '李平' ); 13、查询全部学生都选修了的课程号和课程名(先统计出有课程的学生总数,再以课程分组统计出有多少学生学,如果有课程跟学生总数相等,就说明这就是都选修的课程) SELECT cid, cname FROM course WHERE cid IN ( SELECT course_id FROM score GROUP BY course_id HAVING count(student_id) = ( SELECT count(DISTINCT student_id) FROM score ) ) 14、查询每门课程被选修的次数 SELECT course_id, count(student_id) FROM score GROUP BY course_id; 15、查询只选修了一门课程的学生学号和姓名 SELECT sid, sname FROM student WHERE sid IN ( SELECT student_id FROM score GROUP BY student_id HAVING count(course_id) = 1 ); 16、查询所有学生考出的成绩并按从高到低排序(成绩去重) SELECT DISTINCT num FROM score ORDER BY num DESC; 17、查询平均成绩大于85的学生姓名和平均成绩 SELECT sname, t1.avg_num FROM student INNER JOIN ( SELECT student_id, avg(num) AS avg_num FROM score GROUP BY student_id HAVING avg(num) > 85 ) t1 ON student.sid = t1.student_id; 18、查询生物成绩不及格的学生姓名和对应生物分数 SELECT sname, t1.num FROM student INNER JOIN ( SELECT student_id, num FROM score WHERE course_id = ( SELECT cid FROM course WHERE cname = '生物' ) HAVING num < 60 ) t1 ON student.sid = t1.student_id; 19、查询在所有选修了李平老师课程的学生中,这些课程(李平老师的课程,不是所有课程)平均成绩最高的学生姓名 SELECT sname FROM student WHERE sid = ( SELECT student_id FROM score WHERE course_id IN ( SELECT course.cid FROM teacher INNER JOIN course ON teacher.tid = course.teacher_id WHERE teacher.tname = '李平' ) GROUP BY student_id ORDER BY avg(num) DESC LIMIT 1 ); 20、查询每门课程成绩最好的课程id、学生姓名和分数 SELECT course_id, sname, num FROM student INNER JOIN ( SELECT t1.course_id, student_id, num FROM score INNER JOIN ( SELECT course_id, max(num) AS max_num FROM score GROUP BY course_id ) t1 ON score.course_id = t1.course_id WHERE score.num = t1.max_num ) t2 ON student.sid = t2.student_id; 21、查询不同课程但成绩相同的课程号、学生号、成绩 SELECT DISTINCT s1.course_id, s2.course_id, s1.student_id, s2.student_id, s1.num, s2.num FROM score AS s1, score AS s2 WHERE s1.num = s2.num AND s1.course_id != s2.course_id; 22、查询没学过“李平”老师课程的学生姓名以及选修的课程名称 SELECT student.sname, course.cname FROM student INNER JOIN ( SELECT student_id, course_id FROM score WHERE student_id IN ( SELECT sid FROM student WHERE sid NOT IN ( SELECT student_id FROM score WHERE course_id IN ( SELECT cid FROM course INNER JOIN teacher ON course.teacher_id = teacher.tid WHERE tname = '李平' ) ) ) ) t1 ON student.sid = t1.student_id INNER JOIN course ON course.cid = t1.course_id; 23、查询所有选修了学号为2的同学选修过的一门或者多门课程的同学学号和姓名 SELECT sid, sname FROM student WHERE sid IN ( SELECT DISTINCT student_id FROM score WHERE course_id IN ( SELECT course_id FROM score WHERE student_id = 2 ) ) AND sid != 2; 24、任课最多的老师中学生单科成绩最高的课程id、学生姓名和分数 SELECT t1.course_id, student.sname, num FROM score INNER JOIN ( SELECT course_id, max(num) AS max_num FROM score WHERE course_id IN ( SELECT cid FROM course WHERE teacher_id = ( SELECT teacher_id FROM course GROUP BY teacher_id ORDER BY count(teacher_id) DESC LIMIT 1 ) ) GROUP BY course_id ) t1 ON score.course_id = t1.course_id INNER JOIN student ON score.student_id = student.sid WHERE score.course_id = t1.course_id AND num = t1.max_num;