**

1、 导入hellodb.sql生成数据库

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

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

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

(4) 显示以L开头的名字的同学的信息**
**

2、数据库授权magedu用户，允许192.168.1.0/24网段可以连接mysql

**

查询练习

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

MariaDB [hellodb]> select * from students where teacherid is not null;

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

MariaDB [hellodb]> select * from students order by age desc limit 10;

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

MariaDB [hellodb]> select * from students where age > 20 and age < 25;

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

MariaDB [hellodb]> select classid,count(stuid) from students group by classid;

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

MariaDB [hellodb]> select gender,sum(age) from students group by gender;

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

MariaDB [hellodb]> select classid,avg(age) as a from students group by classid having a > 25;

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

MariaDB [hellodb]> select gender,sum(age) from students where age>25 group by gender;

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

MariaDB [hellodb]> select name,course,score from students st inner join scores sc inner join courses co on st.stuid=sc.stuid and sc.courseid=co.courseid order by score desc limit 5;

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

MariaDB [hellodb]> select name,course,score from students st inner join scores sc inner join courses co on st.stuid=sc.stuid and sc.courseid=co.courseid where score>80 order by score desc;

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

MariaDB [hellodb]> select name,avg(score) from scores inner join students on scores.stuid=students.stuid group by scores.stuid order by avg(score) desc limit 3;

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

MariaDB [hellodb]> select course,sum(stuid) from scores inner join courses on scores.courseid=courses.courseid group by scores.courseid;

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

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

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

MariaDB [hellodb]> select name,courseid from students inner join scores on students.stuid=scores.stuid where courseid in (1,2,4,7);

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

MariaDB [hellodb]> select * from students as a
    -> inner join (select classid,count(stuid),avg(age) as aage from students where classid >0 group by classid having count(stuid)>=3 ) as b on a.classid=b.classid
    -> where a.age>b.aage;

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

MariaDB [hellodb]> select name from students where age > (select avg(age) from students);