sql文件
https://pan.baidu.com/s/1_pE_LeACLbeDY722b-Q4-A
提取码：y8yw
导入数据库

mysql < hellodb_innodb.sql

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 平均年龄>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 limit 10;

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

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

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

select classid 班级,count(name) 人数 from students group by classid;

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

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

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

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

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

select gender 性别,sum(age) 年龄和 from students where age>25 group by gender;

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

select name 姓名,c.course 课程,s.score 成绩 from students as st,courses as c,scores as s where st.StuID=s.StuID and c.courseid=s.courseid limit 5;

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

select name 姓名,c.course 课程,s.score 成绩 from students as st,courses as c,scores as s where st.StuID=s.StuID and c.courseid=s.courseid having 成绩>80;

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

select st.name 姓名,avg(s.score) 平均成绩 from students as st,scores as s where st.StuID=s.StuID  group by 姓名 order by 平均成绩 desc limit 3;

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

select c.course 课程,count(s.courseid) 学习人数 from  scores as s,courses as c where c.courseid=s.courseid group by 课程;

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

select st.name 姓名,s.courseid 课程id from students st inner join scores s on s.stuid=st.stuid having 课程id in (1,2,4,7);

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

select s.name 姓名,s.age 年龄,s.classid 班级,平均年龄 from (select name,age,classid from students  where classid in (select classid from students group by classid having count(classid)>=3)) as s,(select avg(age) as 平均年龄,classid from students group by classid) as st where s.classid=st.classid;

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

select name 姓名,age 年龄,(select avg(age) from students) 平均年龄 from students  where age>(selece>(select avg(age) from students);