练习题
1、导入hellodb.sql生成数据库
mysql -uroot < hellodb.sql,use hellodb
(1) 在students表中,查询年龄大于25岁,且为男性的同学的名字和年龄
select Name,Age from students where Age>25;
(2) 以ClassID为分组依据,显示每组的平均年龄
select ClassID,avg(Age) from students group by ClassID
(3)显示第2题中平均年龄大于30的分组及平均年龄
select ClassID,avg(Age) from students group by ClassID having avg(Age) > 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 desc limit 10;
(7) 查询年龄大于等于20岁,小于等于25岁的同学的信息
select * from students where age between 20 and 25;
2、导入hellodb.sql,以下操作在students表上执行
(1)以ClassID分组,显示每班的同学的人数
select classID,count(*) as total_num from students group by ClassID;
(2)以Gender分组,显示其年龄之和
select gender,sum(Age) from students group by gender;
(3)以ClassID分组,显示其平均年龄大于25的班级
select ClassID,avg(Age) from students group by ClassID having avg(Age) > 25;
(4)以Gender分组,显示各组中年龄大于25的学员的年龄之和
select gender,sum(Age) from students where age > 25 group by gender;
(5)显示前5位同学的姓名、课程及成绩
select s.stuid as stu_id,s.name as stu_name,sc.score,c.course from students as s inner join scores sc on s.stuid=sc.stuid inner join courses c on sc.courseid=c.courseid where s.stuid <= 5;
(6)显示其成绩高于80的同学的名称及课程;
select s.stuid as stu_id,s.name as stu_name,sc.score,c.course from students as s inner join scores sc on s.stuid=sc.stuid inner join courses c on sc.courseid=c.courseid where sc.score > 80;
(7)求前8位同学每位同学自己两门课的平均成绩,并按降序排列
select s.stuid,s.name,avg(sc.score) as avg_score from students as s inner join scores sc on s.stuid=sc.stuid group by stuid order by avg_score;
(8)取每位同学各门课的平均成绩,显示成绩前三名的同学的姓名和平均成绩
select s.name,avg(sc.score) from students as s inner join scores sc on s.stuid=sc.stuid group by s.name order by avg(sc.score) desc limit 3;
(9)显示每门课程课程名称及学习了这门课的同学的个数
第一步:经过筛选,决定使用courses表、coc表和students表做查询
第二步:通过联系将三个表联系在了一起,select count(s.stuid),c.course from students as s inner join coc on s.classid=coc.classid inner join courses c on coc.courseid=c.courseid;
第三步,分组,得到的这个表用哪个分组合适?毫无疑问,用course字段合适,将该字段加入分组,select count(s.stuid) as Stu_Num,c.course from students as s inner join coc on s.classid=coc.classid inner join courses c on coc.courseid=c.courseid group by c.course;
(10)显示其年龄大于平均年龄的同学的名字
有子查询和内连接两种写法,虽然子查询方式便于理解,但由于数据库性能不高在此推荐用内连接的写法
第一种,子查询:select name,age from students where age > (select avg(Age) from students);
第二种,内连接写法:slect s.name,age from students as s inner join (select avg(age) as age from students) as ss on s.age > ss.age;
(11)显示其学习的课程为第1、2,4或第7门课的同学的名字
select s.name,a.courseid from students as s inner join (select * from coc where courseid in ('1','2','4','7')) as a on s.classid=a.classid;
(12)显示其成员数最少为3个的班级的同学中年龄大于同班同学平均年龄的同学
先查询成员数量最少为3个的班级,且每个班的平均年龄,select classid,count(stuid),avg(age) from students group by classid having count(stuid)>=3;
再将学生表的name、age与生成的该表做对比,对比时候用where条件判断每个学生分别对应自己的classid进行比较
(13)统计各班级中年龄大于全校同学平均年龄的同学
select stuid,name,age,classid from students where age > (select avg(age) from students);
转载于:https://blog.51cto.com/13873498/2293831