MySQL练习题及答案
程序员文章站
2022-05-18 12:26:14
一 题目 二 答案 1、查询所有的课程的名称以及对应的任课老师姓名 SELECT course.cname, teacher.tname FROM course INNER JOIN teacher ON course.teacher_id = teacher.tid; 2、查询学生表中男女生各有多 ......
一 题目
1、查询所有的课程的名称以及对应的任课老师姓名 2、查询学生表中男女生各有多少人 3、查询物理成绩等于100的学生的姓名 4、查询平均成绩大于八十分的同学的姓名和平均成绩 5、查询所有学生的学号,姓名,选课数,总成绩 6、 查询姓李老师的个数 7、 查询没有报李平老师课的学生姓名 8、 查询物理课程比生物课程高的学生的学号 9、 查询没有同时选修物理课程和体育课程的学生姓名 10、查询挂科超过两门(包括两门)的学生姓名和班级 、查询选修了所有课程的学生姓名 12、查询李平老师教的课程的所有成绩记录 13、查询全部学生都选修了的课程号和课程名 14、查询每门课程被选修的次数 15、查询之选修了一门课程的学生姓名和学号 16、查询所有学生考出的成绩并按从高到低排序(成绩去重) 17、查询平均成绩大于85的学生姓名和平均成绩 18、查询生物成绩不及格的学生姓名和对应生物分数 19、查询在所有选修了李平老师课程的学生中,这些课程(李平老师的课程,不是所有课程)平均成绩最高的学生姓名 20、查询每门课程成绩最好的前两名学生姓名 21、查询不同课程但成绩相同的学号,课程号,成绩 22、查询没学过“叶平”老师课程的学生姓名以及选修的课程名称; 23、查询所有选修了学号为1的同学选修过的一门或者多门课程的同学学号和姓名; 24、任课最多的老师中学生单科成绩最高的学生姓名
二 答案
#1、查询所有的课程的名称以及对应的任课老师姓名 select course.cname, teacher.tname from course inner join teacher on course.teacher_id = teacher.tid; #2、查询学生表中男女生各有多少人 select gender 性别, count(1) 人数 from student group by gender; #3、查询物理成绩等于100的学生的姓名 select student.sname from student where sid in ( select student_id from score inner join course on score.course_id = course.cid where course.cname = '物理' and score.num = 100 ); #4、查询平均成绩大于八十分的同学的姓名和平均成绩 select student.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) > 80 ) as t1 on student.sid = t1.student_id; #5、查询所有学生的学号,姓名,选课数,总成绩(注意:对于那些没有选修任何课程的学生也算在内) select student.sid, student.sname, t1.course_num, t1.total_num from student left join ( select student_id, count(course_id) course_num, sum(num) total_num from score group by student_id ) as t1 on student.sid = t1.student_id; #6、 查询姓李老师的个数 select count(tid) from teacher where tname like '李%'; #7、 查询没有报李平老师课的学生姓名(找出报名李平老师课程的学生,然后取反就可以) select student.sname from student where sid not in ( select distinct student_id from score where course_id in ( select course.cid from course inner join teacher on course.teacher_id = teacher.tid where teacher.tname = '李平老师' ) ); #8、 查询物理课程比生物课程高的学生的学号(分别得到物理成绩表与生物成绩表,然后连表即可) select t1.student_id from ( select student_id, num from score where course_id = ( select cid from course where cname = '物理' ) ) as t1 inner join ( select student_id, num from score where course_id = ( select cid from course where cname = '生物' ) ) as t2 on t1.student_id = t2.student_id where t1.num > t2.num; #9、 查询没有同时选修物理课程和体育课程的学生姓名(没有同时选修指的是选修了一门的,思路是得到物理+体育课程的学生信息表,然后基于学生分组,统计count(课程)=1) select student.sname from student where sid in ( select student_id from score where course_id in ( select cid from course where cname = '物理' or cname = '体育' ) group by student_id having count(course_id) = 1 ); #10、查询挂科超过两门(包括两门)的学生姓名和班级(求出<60的表,然后对学生进行分组,统计课程数目>=2) 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 student.sname from student where sid in ( select student_id from score group by student_id having count(course_id) = (select count(cid) from course) ); #12、查询李平老师教的课程的所有成绩记录 select * from score where course_id in ( select cid from course inner join teacher on course.teacher_id = teacher.tid where teacher.tname = '李平老师' ); #13、查询全部学生都选修了的课程号和课程名(取所有学生数,然后基于score表的课程分组,找出count(student_id)等于学生数即可) select cid, cname from course where cid in ( select course_id from score group by course_id having count(student_id) = ( select count(sid) from student ) ); #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) avg_num from score group by student_id having avg(num) > 85 ) t1 on student.sid = t1.student_id; #18、查询生物成绩不及格的学生姓名和对应生物分数 select sname 姓名, num 生物成绩 from score left join course on score.course_id = course.cid left join student on score.student_id = student.sid where course.cname = '生物' and score.num < 60; #19、查询在所有选修了李平老师课程的学生中,这些课程(李平老师的课程,不是所有课程)平均成绩最高的学生姓名 select sname from student where sid = ( select student_id from score where course_id in ( select course.cid from course inner join teacher on course.teacher_id = teacher.tid where teacher.tname = '李平老师' ) group by student_id order by avg(num) desc limit 1 ); #20、查询每门课程成绩最好的前两名学生姓名 #查看每门课程按照分数排序的信息,为下列查找正确与否提供依据 select * from score order by course_id, num desc; #表1:求出每门课程的课程course_id,与最高分数first_num select course_id, max(num) first_num from score group by course_id; #表2:去掉最高分,再按照课程分组,取得的最高分,就是第二高的分数second_num select score.course_id, max(num) second_num from score inner join ( select course_id, max(num) first_num from score group by course_id ) as t on score.course_id = t.course_id where score.num < t.first_num group by course_id; #将表1和表2联合到一起,得到一张表t3,包含课程course_id与该们课程的first_num与second_num select t1.course_id, t1.first_num, t2.second_num from ( select course_id, max(num) first_num from score group by course_id ) as t1 inner join ( select score.course_id, max(num) second_num from score inner join ( select course_id, max(num) first_num from score group by course_id ) as t on score.course_id = t.course_id where score.num < t.first_num group by course_id ) as t2 on t1.course_id = t2.course_id; #查询前两名的学生(有可能出现并列第一或者并列第二的情况) select score.student_id, t3.course_id, t3.first_num, t3.second_num from score inner join ( select t1.course_id, t1.first_num, t2.second_num from ( select course_id, max(num) first_num from score group by course_id ) as t1 inner join ( select score.course_id, max(num) second_num from score inner join ( select course_id, max(num) first_num from score group by course_id ) as t on score.course_id = t.course_id where score.num < t.first_num group by course_id ) as t2 on t1.course_id = t2.course_id ) as t3 on score.course_id = t3.course_id where score.num >= t3.second_num and score.num <= t3.first_num; #排序后可以看的明显点 select score.student_id, t3.course_id, t3.first_num, t3.second_num from score inner join ( select t1.course_id, t1.first_num, t2.second_num from ( select course_id, max(num) first_num from score group by course_id ) as t1 inner join ( select score.course_id, max(num) second_num from score inner join ( select course_id, max(num) first_num from score group by course_id ) as t on score.course_id = t.course_id where score.num < t.first_num group by course_id ) as t2 on t1.course_id = t2.course_id ) as t3 on score.course_id = t3.course_id where score.num >= t3.second_num and score.num <= t3.first_num order by course_id; #可以用以下命令验证上述查询的正确性 select * from score order by course_id, num desc; -- 21、查询不同课程但成绩相同的学号,课程号,成绩 -- 22、查询没学过“叶平”老师课程的学生姓名以及选修的课程名称; -- 23、查询所有选修了学号为1的同学选修过的一门或者多门课程的同学学号和姓名; -- 24、任课最多的老师中学生单科成绩最高的学生姓名
推荐阅读
-
Windows 64 位 mysql 5.7以上版本包解压中没有data目录和my-default.ini及服务无法启动的快速解决办法(问题小结)
-
MySQL 服务器参数说明及查看 设置方法
-
MySQL 数据库设计复习笔记及项目实战
-
MySQL主从原理及配置详解
-
CentOS7.x卸载与安装MySQL5.7的操作过程及编码格式的修改方法
-
centos编译安装mysql 5.6及安装多个mysql实例详解
-
CentOS安装mysql5.7 及简单配置教程详解
-
MAC下MYSQL5.7.17连接不上的问题及解决办法
-
mysql中find_in_set()函数的使用及in()用法详解
-
MySQL PHP 语法详解及实例代码