龙空技术网

MySQL:多表查询

十维教育 750

前言:

而今小伙伴们对“查询mysql中所有表”大概比较珍视,咱们都需要剖析一些“查询mysql中所有表”的相关内容。那么小编同时在网上收集了一些有关“查询mysql中所有表””的相关内容,希望看官们能喜欢,同学们快快来了解一下吧!

多表查询练习题

题目

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.tnameFROM courseINNER JOIN teacher ON course.teacher_id = teacher.tid;#2、查询学生表中男女生各有多少人SELECT gender 性别, count(1) 人数FROM studentGROUP BY gender;#3、查询物理成绩等于100的学生的姓名SELECT student.snameFROM studentWHERE 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_numFROM studentINNER 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_numFROM studentLEFT 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 teacherWHERE tname LIKE '李%';#7、 查询没有报李平老师课的学生姓名(找出报名李平老师课程的学生,然后取反就可以)SELECT student.snameFROM studentWHERE 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_idFROM ( SELECT student_id, num FROM score WHERE course_id = ( SELECT cid FROM course WHERE cname = '物理' ) ) AS t1INNER JOIN ( SELECT student_id, num FROM score WHERE course_id = ( SELECT cid FROM course WHERE cname = '生物' )) AS t2 ON t1.student_id = t2.student_idWHERE t1.num > t2.num;#9、 查询没有同时选修物理课程和体育课程的学生姓名(没有同时选修指的是选修了一门的,思路是得到物理+体育课程的学生信息表,然后基于学生分组,统计count(课程)=1)SELECT student.snameFROM studentWHERE 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.captionFROM studentINNER JOIN ( SELECT student_id FROM score WHERE num < 60 GROUP BY student_id HAVING count(course_id) >= 2) AS t1INNER JOIN class ON student.sid = t1.student_idAND student.class_id = class.cid;#11、查询选修了所有课程的学生姓名(先从course表统计课程的总数,然后基于score表按照student_id分组,统计课程数据等于课程总数即可)SELECT student.snameFROM studentWHERE sid IN ( SELECT student_id FROM score GROUP BY student_id HAVING COUNT(course_id) = (SELECT count(cid) FROM course) );#12、查询李平老师教的课程的所有成绩记录SELECT *FROM scoreWHERE 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, cnameFROM courseWHERE 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 scoreGROUP BY course_id;#15、查询之选修了一门课程的学生姓名和学号SELECT sid, snameFROM studentWHERE sid IN ( SELECT student_id FROM score GROUP BY student_id HAVING COUNT(course_id) = 1 );#16、查询所有学生考出的成绩并按从高到低排序(成绩去重)SELECT DISTINCT numFROM scoreORDER BY num DESC;#17、查询平均成绩大于85的学生姓名和平均成绩SELECT sname, t1.avg_numFROM studentINNER 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 scoreLEFT JOIN course ON score.course_id = course.cidLEFT JOIN student ON score.student_id = student.sidWHERE course.cname = '生物'AND score.num < 60;#19、查询在所有选修了李平老师课程的学生中,这些课程(李平老师的课程,不是所有课程)平均成绩最高的学生姓名SELECT snameFROM studentWHERE 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 scoreORDER BY course_id, num DESC;#表1:求出每门课程的课程course_id,与最高分数first_numSELECT course_id, max(num) first_numFROM scoreGROUP BY course_id;#表2:去掉最高分,再按照课程分组,取得的最高分,就是第二高的分数second_numSELECT score.course_id, max(num) second_numFROM scoreINNER JOIN ( SELECT course_id, max(num) first_num FROM score GROUP BY course_id) AS t ON score.course_id = t.course_idWHERE score.num < t.first_numGROUP BY course_id;#将表1和表2联合到一起,得到一张表t3,包含课程course_id与该们课程的first_num与second_numSELECT t1.course_id, t1.first_num, t2.second_numFROM ( SELECT course_id, max(num) first_num FROM score GROUP BY course_id ) AS t1INNER 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_numFROM scoreINNER 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_idWHERE score.num >= t3.second_numAND score.num <= t3.first_num;#排序后可以看的明显点SELECT score.student_id, t3.course_id, t3.first_num, t3.second_numFROM scoreINNER 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_idWHERE score.num >= t3.second_numAND score.num <= t3.first_numORDER BY course_id;#可以用以下命令验证上述查询的正确性SELECT *FROM scoreORDER BY course_id, num DESC;-- 21、查询不同课程但成绩相同的学号,课程号,成绩-- 22、查询没学过“叶平”老师课程的学生姓名以及选修的课程名称;-- 23、查询所有选修了学号为1的同学选修过的一门或者多门课程的同学学号和姓名;-- 24、任课最多的老师中学生单科成绩最高的学生姓名

标签: #查询mysql中所有表