mysql数据库练习

 

导入hellodb.sql生成数据库 mysql < hellodb.sql

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

   root@localhost [hellodb]>select name as 姓名 , age 年龄 from students where age>25 and gender = 'M';

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

            root@localhost [hellodb]>select classid ,avg(age) 平均年龄 from students group by classid;

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

   root@localhost [hellodb]>select classid ,avg(age) 平均年龄 from students group by classid having 平均年龄>30;

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

            root@localhost [hellodb]>select * from students where name like 'L%';

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

             root@localhost [hellodb]>select * from students where teacherid is not null;

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

   root@localhost [hellodb]>select * from students order by age desc limit 10;

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

   root@localhost [hellodb]>select * from students where age between 20 and 25;

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

   root@localhost [hellodb]>select classid 班级 , count(classid) 班级人数 from students group by classid;

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

   root@localhost [hellodb]>select gender 性别 ,sum(age) 年龄和 from students group by gender;

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

   root@localhost [hellodb]>select classid 班级 ,avg(age) 平均年龄 from students group by classid having 平均年龄>25;

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

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

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

   root@localhost [hellodb]>select name ,course,score from (select * from students limit 5 ) as st inner join scores sc on st.stuid=sc.stuid inner join courses co on sc.courseid=co.courseid;

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

   root@localhost [hellodb]>select name 姓名,course 课程 from (select *from scores where score>80 ) as sc inner join students st on sc.stuid=st.stuid inner join courses co on sc.courseid=co.courseid;

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

   root@localhost [hellodb]>select name ,avg(score) from students st, scores sc, courses co where st.stuid=sc.stuid and sc.courseid=co.courseid group by st.name order by avg(score) desc limit 3;

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

    root@localhost [hellodb]>select course,count(*) from courses co , scores sc where co.courseid=sc.courseid group by course;

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

    root@localhost [hellodb]>select name ,age from students where age> (select avg(age) from students);

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

    root@localhost [hellodb]>select name ,course,co.courseid from (select *from scores where courseid in (1,2,3,7)) as sc inner join students as st on sc.stuid=st.stuid inner join courses as co on co.courseid=sc.courseid;

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

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

15.  root@localhost [hellodb]> select name ,classid from students where age>(select avg(age) from students) order by classid;

16. 1