17.mysql数据库练习

导入hellodb.sql生成数据库
1. 在students表中，查询年龄大于25岁，且为男性的同学的名字和年龄

select name,age from students where age > 25 and gender='M';

2. 以ClassID为分组依据，显示每组的平均年龄

select classid,avg(age) from students group by classid;

3. 显示第2题中平均年龄大于30的分组及平均年龄

select classid,avg(age)  from students group by classid having avg(age) > 30;

4. 显示以L开头的名字的同学的信息

select * from students where name like 'L%';

5. 显示TeacherID非空的同学的相关信息

select * from students where  teacherid is not null;

6. 以年龄排序后，显示年龄最大的前10位同学的信息

select * from students order by age desc limit 10;

7. 查询年龄大于等于20岁，小于等于25岁的同学的信息

select * from students where age >= 20 and age <=25;

select * from students where age between 20 and 25;

8. 以ClassID分组，显示每班的同学的人数

select classid,count(stuid) 人数 from students group by classid;

9. 以Gender分组，显示其年龄之和

select gender,sum(age) 年龄之和 from students group by gender;

10. 以ClassID分组，显示其平均年龄大于25的班级

select classid,avg(age) 平均年龄 from students group by classid having 平均年龄 > 25; 

11. 以Gender分组，显示各组中年龄大于25的学员的年龄之和

select gender,sum(age) from students where age > 25 group by gender;

12. 显示前5位同学的姓名、课程及成绩

select name,course,score from (select * from students limit 5) st 
inner join scores sc on st.stuid=sc.stuid
inner join courses co on co.courseid=sc.courseid limit 5;

13. 显示其成绩高于80的同学的名称及课程

select name,course,score from students st
inner join scores sc on st.stuid=sc.stuid
inner join courses co on co.courseid=sc.courseid where score > 80;

14. 取每位同学各门课的平均成绩，显示成绩前三名的同学的姓名和平均成绩

select name,avg(score) from students st 
inner join scores sc on st.stuid=sc.stuid 
inner join courses co on co.courseid=sc.courseid 
group by name order by avg(score) desc limit 3;

15. 显示每门课程课程名称及学习了这门课的同学的个数

select course,count(stuid) 个数 from courses co 
inner join students st on co.courseid=st.classid group by course;

16. 显示其年龄大于平均年龄的同学的名字

select name,age from students where age>(select avg(age) from students);

17. 显示其学习的课程为第1、2，4或第7门课的同学的名字

select name,classid from students where classid in (1,2,4,7);

18. 显示其成员数最少为3个的班级的同学中年龄大于同班同学平均年龄的同学

select name,age,avg.平均年龄 from students st 
inner join (select classid,count(classid),avg(age) 平均年龄 from students 
group by classid having count(classid)>=3) as avg 
on st.classid=avg.classid and st.age > avg.平均年龄;

19. 统计各班级中年龄大于全校同学平均年龄的同学

select * from students where age > (select avg(age) from students) and classid is not null;