MySQL数据库学习【第八篇】多表查询
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了MySQL数据库学习【第八篇】多表查询,小编现在分享给大家,供广大互联网技能从业者学习和参考。文章包含5224字,纯文字阅读大概需要8分钟。
内容图文
![MySQL数据库学习【第八篇】多表查询](/upload/InfoBanner/zyjiaocheng/500/7bc925b6a2b84c1b9a7576596660ca93.jpg)
答案
1、查询所有的课程的名称以及对应的任课老师姓名 select cname 课程名称,tname 老师姓名 from course left join teacher on course.teacher_id = teacher.tid; 2、查询学生表中男女生各有多少人 select gender ,count(gender) from student group by gender; 3、查询物理成绩等于100的学生的姓名 select sname from student inner join score on student.sid = score.student_id join course on score.course_id = course.cid where cname = ‘物理‘ and num = 100; 4、查询平均成绩大于八十分的同学的姓名和平均成绩 (1): select sname 姓名,avg(num) 平均成绩 from student inner join score on student.sid = score.student_id group by student_id having avg(num)>80; (2): select sname,avg(num) from student inner join score on student.sid = score.student_id group by student_id having avg(num)>80 5、查询所有学生的学号,姓名,选课数,总成绩 select student.sid 学号,sname 姓名,count(course_id) 选课数,sum(num) 总成绩 from student left join score on student.sid = score.student_id group by student.sid ; 6、 查询姓李老师的个数 select count(tid) from teacher where tname like ‘李%‘; 7、 查询没有报李平老师课的学生姓名 select sname 姓名 from student where sid not in ( select 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 inner join course on score.course_id=course.cid where cname=‘物理‘ )as t1 inner join (select student_id , num from score inner join course on score.course_id=course.cid where cname = ‘生物‘) as t2 on t1.student_id = t2.student_id where t1.num>t2.num; 9、 查询没有同时选修物理课程和体育课程的学生姓名 (1): select sname from student inner join score on student.sid = score.student_id join course on course.cid=score.course_id and cname in (‘物理‘,‘体育‘) group by student_id having count(course_id)!=2; (2): select sname from student join score on student.sid = score.student_id join course on course.cid=score.course_id where cname =‘物理‘ or cname= ‘体育‘ group by student_id having count(course_id) !=2; 10、查询挂科超过两门(包括两门)的学生姓名和班级 select sname 姓名,caption 班级 from student inner join score on student.sid = score.course_id join class on class.cid = score.course_id where num<60 group by student_id having count(course_id)>=2; 11、查询选修了所有课程的学生姓名 (1) select sname 姓名 ,所有的课程数 from student inner join (select student_id,count(course_id) 所有的课程数 from score group by student_id having count(course_id) = ( select count(cid) from course)) as t1 on t1.student_id = student.sid; (2) select sname,count(course_id) from student inner join score on student.sid = score.student_id group by student_id having count(course_id)=(select count(cid) from course); 12、查询李平老师教的课程的所有成绩记录 (1): select num from score inner join course on course.cid=score.course_id join teacher on teacher.tid=course.teacher_id where tname = ‘李平老师‘; (2): select num from score where course_id in(select cid from course where teacher_id= (select tid from teacher where tname=‘李平老师‘)); 13、查询全部学生都选修了的课程号和课程名 select cid 课程号,cname 课程名 from course select ; 14、查询每门课程被选修的次数 (1) select course.cname,count(student_id) 选课人数 from score inner join course on score.course_id=course.cid group by course_id; (2):也可以按照name分组 select course.cname,count(student_id) 选课人数 from score inner join course on score.course_id=course.cid group by cname; 15、查询之选修了一门课程的学生姓名和学号 select sname 姓名,student_id 学号 from student inner join score on student.sid = score.student_id group by student_id having count(course_id)=1; 16、查询所有学生考出的成绩并按从高到低排序(成绩去重) select distinct num from score order by num desc; 17、查询平均成绩大于85的学生姓名和平均成绩 (1): select sname 姓名,avg(num) 平均成绩 from student inner join score on student.sid = score.student_id group by student_id having avg(num)>85; (2): select student.sname,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、查询生物成绩不及格的学生姓名和对应生物分数 (1): select student.sname ,num 生物成绩 from student inner join score on student.sid = score.student_id join course on course.cid=score.course_id where cname=‘生物‘ and num<60; (2): select student.sname,t1.num from student inner join ( select student_id,num from score where course_id=(select cid from course where cname=‘生物‘) and num < 60 ) t1 on t1.student_id=student.sid ; 19、查询在所有选修了李平老师课程的学生中,这些课程(李平老师的课程,不是所有课程)平均成绩最高的学生姓名 select sname from student where sid=( select student_id from score where course_id in ( select cid from course where teacher_id=(select tid from teacher where tname=‘李平老师‘) ) group by student_id order by avg(num) desc limit 1 )
MySQL数据库学习【第八篇】多表查询
标签:mil tar 文件内容 structure value commit 必须 记录 host
本文系统来源:https://www.cnblogs.com/xiaohema/p/8454928.html
内容总结
以上是互联网集市为您收集整理的MySQL数据库学习【第八篇】多表查询全部内容,希望文章能够帮你解决MySQL数据库学习【第八篇】多表查询所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错,欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明:本文内容由互联网用户自发贡献,该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 gblab@vip.qq.com 举报,一经查实,本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。