练习：导入hellodb.sql生成数据库

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

MariaDB [hellodb]> select Name,Age from students where Age>25 and Gender='M';

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

MariaDB [hellodb]> select ClassID,avg(age) as avg_age from students group by ClassID;

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

MariaDB [hellodb]> select ClassID,avg(age) as avg_age from students group by ClassID having avg_age>30;

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

MariaDB [hellodb]> select * from students where Name like 'L%';

MariaDB [hellodb]> select * from students where Name rlike '^L';

（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;

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



练习：导入hellodb.sql，以下操作在students表上执行

1、以ClassID分组，显示每班的同学的人数；

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

2、以Gender分组，显示其年龄之和；

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

3、以ClassID分组，显示其平均年龄大于25的班级；

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

4、以Gender分组，显示各组中年龄大于25的学员的年龄之和；

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

练习：导入hellodb.sql，完成以下题目：

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

MariaDB [hellodb]> select name,course,score from (select * from students limit 5) as t,courses,scores where t.stuid=scores.stuid and scores.courseid=courses.courseid;

2、显示其成绩高于80的同学的名称及课程；

select name,course,score from students,(select * from scores where score > 80) as s,courses where students.stuid=s.stuid and s.courseid=courses.courseid;

3、求前8位同学每位同学自己两门课的平均成绩，并按降序排列；

select name,a from (select * from students limit 8) as s,(select stuid,avg(score) as a from scores group by stuid) as c where s.stuid=c.stuid order by a desc;

4、显示每门课程课程名称及学习了这门课的同学的个数；

select course,count(name) from (select name,course from students,courses,scores where students.stuid=scores.stuid and scores.courseid=courses.courseid) as a group by course;

select courses.course,count(stuid) from scores left join courses on scores.courseid=courses.courseid group by scores.courseid;

思考：

1、如何显示其年龄大于平均年龄的同学的名字？

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

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

MariaDB [hellodb]> select name from students,(select distinct stuid from (select * from scores where courseid in (1,2,4,7)) as a) as b where b.stuid=students.stuid;

select students.name from students,(select distinct stuid from scores where courseid in (1,2,4,7))as s where s.stuid=students.stuid;

3、如何显示其成员数最少为3个的班级  的同学中年龄大于同班同学平均年龄的同学？

MariaDB [hellodb]> select student.name,student.age,student.classid,second.avg_age from (select students.name as name ,students.age as age,students.classid as classid from students left join (select count(name) as num,classid as classid from students group by classid having num>=3) as first on first.classid=students.classid) as student,(select avg(age) as avg_age,classid as classid from students group by classid) as  second where student.age>second.avg_age and student.classid=second.classid;

4、统计各班级中年龄大于全校同学平均年龄的同学。

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