mysql多表的查询的简单示例

下面讲讲关于MySQL多表的查询的简单示例，文字的奥妙在于贴近主题相关。所以，闲话就不谈了，我们直接看下文吧，相信看完mysql多表的查询的简单示例这篇文章你一定会有所受益。

创新互联专业为企业提供芜湖网站建设、芜湖做网站、芜湖网站设计、芜湖网站制作等企业网站建设、网页设计与制作、芜湖企业网站模板建站服务，10多年芜湖做网站经验，不只是建网站，更提供有价值的思路和整体网络服务。

导入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 CourseIDin ('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

对于以上mysql多表的查询的简单示例相关内容，大家还有什么不明白的地方吗？或者想要了解更多相关，可以继续关注我们的行业资讯板块。

网页名称：mysql多表的查询的简单示例
标题来源：http://cdxtjz.cn/article/ghddgi.html