mysql练习

 

 

 

1. 在students表中，查询年龄大于25岁，且为男性的同学的名字和年龄

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

 

 

 

 

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

 

select classid,avg(age),count(*) from students where classid is not null group by classid;

 

 

 

 

 

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

 select classid,avg(age),count(*) from students where classid is not null and age>30 group by classid;

 

 

 

 

4. 显示以L开头的名字的同学的信息
like：对列进行模糊收缩
%：任意长度字符

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;

 

 

 

 

 

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

 select classid,count(*) from students where classid is not null  group by classid;

 

 

 

 

 

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

 

 

 

elect Gender,sum(age),count(*) from students where classid is not null group by Gender;

 

 

 

 

 

10. 以ClassID分组，显示其平均年龄大于25的班级
having：用于对分组运算后进行条件过滤。

 select classid,avg(age),count(*) from students where classid is not null group by classid having avg(age)>25;

 

 

 

 

 

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

 select gender,sum(age),count(*) from students where age>25 and classid is not null group by gender;

 

 

 

 

 

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

select st.name,sc.score,co.course from scores sc  inner join students st on sc.StuID=st.StuID inner join courses co on sc.CourseID=co.CourseID order by sc.score desc limit 5

 

 

 

 

 

13. 显示其成绩高于80的同学的名称及课程
进行多表查询时where要置于最后方以及添加条件过滤时要加上表名或表的别名。

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

 

 

 

 

14. 取每位同学各门课的平均成绩，显示成绩前三名的同学的姓名和平均成绩
PS：因为学生不管靠几课有多少门成绩都是利用一个StuID进行考试，因此拿StuID为依据进行分组就可以将不同成绩纳入同一个StuID组中，以此通过avg来取成绩的平均分。

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

 

 

15. 显示每门课程课程名称及学习了这门课的同学的个数
PS：这里也是用到了分组的概念，从成绩表可以看到每名学生有多科成绩，因此通过成绩表的CourseID对科目进行去重分组，再通过count()即可计算出每个组中有多少名学生考了这个科目。

 select co.Course,count(*) from courses co inner join scores sc on co.CourseID=sc.CourseID group by sc.CourseID ;

 

16. 显示其年龄大于平均年龄的同学的名字
PS：先计算出所有同学的平均年龄，再将平均年龄的结果作为子查询进行比较,即可得出结果，这里平均年龄是27因此拿这个结果设置过滤条件以作验证。

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

 

 

 

 

 

17. 显示其学习的课程为第1、2，4或第7门课的同学的名字
解题思路：这里通过左外连接的方式保留了scores表中的classid和成绩的对应关系(因为有些同学有成绩有些没有)，因此在交集之处就会将没有成绩的同学排除掉，再通过in去不连续查询课程。

 select st.name,sc.CourseID,co.Course   from scores sc left join  students st on sc.StuID=st.StuID left join courses co on sc.CourseID=co.CourseID  where sc.CourseIDD in(1,2,4,7);

 

 

 

 

 

 

 

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


19. 统计各班级中年龄大于全校同学平均年龄的同学
解题思路：通过子查询使用avg函数计算出全校学生的平均年龄然后调用子查询结果与age进行比较筛选。

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