首页 / MYSQL / mysql多表查询练习
mysql多表查询练习
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了mysql多表查询练习,小编现在分享给大家,供广大互联网技能从业者学习和参考。文章包含2349字,纯文字阅读大概需要4分钟。
内容图文
![mysql多表查询练习](/upload/InfoBanner/zyjiaocheng/885/9abf23f9d3a34a0a9c1e9aaa07c5db21.jpg)
导入hellodb.sql生成数据库,并进入到数据库之中
mysql -uroot < hellodb.sql
use mysql
(1)以ClassID分组,显示每班的同学的人数
`select classid,count(stuid) from students group by classid``
(2)以Gender分组,显示其年龄之和
select gender,sum(age) from students group by gender
(3)以ClassID分组,显示其平均年龄大于25的班级
select classid,avg(age) as new_age from students group by classid having new_age>25
(4)以Gender分组,显示各组中年龄大于25的学员的年龄之和
select gender,sum(age) from students group by gender
(5)显示前5位同学的姓名、课程及成绩
select name,course,score from (select name,score,courseid from (select * from students where stuid<=5) as s inner join scores on scores.stuid=s.stuid)as t inner join courses on courses.courseid=t.courseid
(6)显示其成绩高于80的同学的名称及课程
select name,course from (select name,score,courseid from (select from scores where Score>80) as t inner join students on students.stuid=t.stuid) as t inner join courses on courses.courseid=t.courseid
(7)求前8位同学每位同学自己两门课的平均成绩,并按降序排列
select t.stuid,avg(score) from (select stuid,courseid from (select from students where stuid<=8) as s inner join coc on s.classid=coc.classid) as t inner join scores on s cores.stuid=t.stuid group by t.stuid
(8)取每位同学各门课的平均成绩,显示成绩前三名的同学的姓名和平均成绩
select name,avg(score) as 平均分数 from (select name,courseid from students inner join coc on students.classid=coc.classid) as s inner join scores on s.courseid=scores.courseid group by stuid order by 平均分数 desc limit 3
(9)显示每门课程课程名称及学习了这门课的同学的个数
select courseid,count(CourseID) from students inner join coc on coc.classid=students.classid group by courseid
(10)显示其年龄大于平均年龄的同学的名字
select * from students where age>(select avg(age) from students)
(11)显示其学习的课程为第1、2,4或第7门课的同学的名字
select name,courseid from (select * from coc where CourseID
in ('1','2','4','7')) as new inner join students on students.classid=new.classid
(12)显示其成员数最少为3个的班级的同学中年龄大于同班同学平均年龄的同学
select * from (select name,classid,age from students) as s inner join (select new.classid,avg(age) as cc from (select classid from students group by ClassID having count(stuid) >= 3) as new inner join students on students.classid=new.classid group by new.classid) as a on a.classid=s.classid where cc<age;
内容总结
以上是互联网集市为您收集整理的mysql多表查询练习全部内容,希望文章能够帮你解决mysql多表查询练习所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错,欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明:本文内容由互联网用户自发贡献,该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 gblab@vip.qq.com 举报,一经查实,本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。