mysql 多表查询练习题_mysql多表查询练习

导入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