第13周作业
1、如何将 hellodb_innodb.sql导入到数据库中
mysql < hellodb_innodb.sql
2、在学生表中，查询年龄大于25岁，且为男性的同学的名字和年龄
select name,age from students where age>25 and gender='m'; +--------------+-----+ | name | age | +--------------+-----+ | Xie Yanke | 53 | | Ding Dian | 32 | | Yu Yutong | 26 | | Shi Qing | 46 | | Tian Boguang | 33 | | Xu Xian | 27 | | Sun Dasheng | 100 | +--------------+-----+
3、在学生表中，以ClassID为分组依据，查询显示每组的平均年龄
select classid,avg(age) from students where classid is not null group by classid; +---------+----------+ | classid | avg(age) | +---------+----------+ | 1 | 20.5000 | | 2 | 36.0000 | | 3 | 20.2500 | | 4 | 24.7500 | | 5 | 46.0000 | | 6 | 20.7500 | | 7 | 19.6667 | +---------+----------+
4、显示第2题中平均年龄大于30的分组及平均年龄
select classid,avg(age) as 平均年龄 from (select * from students where age>25 and gender='m') as new where classid is not null group by classid having 平均年龄>30; +---------+--------------+ | ClassID | 平均年龄 | +---------+--------------+ | 2 | 43.0000 | | 4 | 32.0000 | | 5 | 46.0000 | +---------+--------------+
5、显示以L开头的名字的同学的信息
第一种: select * from students where name like 'l%'; +-------+-------------+-----+--------+---------+-----------+ | StuID | Name | Age | Gender | ClassID | TeacherID | +-------+-------------+-----+--------+---------+-----------+ | 8 | Lin Daiyu | 17 | F | 7 | NULL | | 14 | Lu Wushuang | 17 | F | 3 | NULL | | 17 | Lin Chong | 25 | M | 4 | NULL | +-------+-------------+-----+--------+---------+-----------+ 第二种: select * from students where substr(name,1,1)='l'; +-------+-------------+-----+--------+---------+-----------+ | StuID | Name | Age | Gender | ClassID | TeacherID | +-------+-------------+-----+--------+---------+-----------+ | 8 | Lin Daiyu | 17 | F | 7 | NULL | | 14 | Lu Wushuang | 17 | F | 3 | NULL | | 17 | Lin Chong | 25 | M | 4 | NULL | +-------+-------------+-----+--------+---------+-----------+
6、显示老师ID非空的同学的相关信息
select * from students where teacherid is not null; +-------+-------------+-----+--------+---------+-----------+ | StuID | Name | Age | Gender | ClassID | TeacherID | +-------+-------------+-----+--------+---------+-----------+ | 1 | Shi Zhongyu | 22 | M | 2 | 3 | | 2 | Shi Potian | 22 | M | 1 | 7 | | 3 | Xie Yanke | 53 | M | 2 | 16 | | 4 | Ding Dian | 32 | M | 4 | 4 | | 5 | Yu Yutong | 26 | M | 3 | 1 | +-------+-------------+-----+--------+---------+-----------+
7、students表中，查询以年龄排序后的数据，并且显示年龄最大的前10位同学的信息
select * from students order by age desc limit 0,10; +-------+--------------+-----+--------+---------+-----------+ | StuID | Name | Age | Gender | ClassID | TeacherID | +-------+--------------+-----+--------+---------+-----------+ | 25 | Sun Dasheng | 100 | M | NULL | NULL | | 3 | Xie Yanke | 53 | M | 2 | 16 | | 6 | Shi Qing | 46 | M | 5 | NULL | | 13 | Tian Boguang | 33 | M | 2 | NULL | | 4 | Ding Dian | 32 | M | 4 | 4 | | 24 | Xu Xian | 27 | M | NULL | NULL | | 5 | Yu Yutong | 26 | M | 3 | 1 | | 17 | Lin Chong | 25 | M | 4 | NULL | | 23 | Ma Chao | 23 | M | 4 | NULL | | 18 | Hua Rong | 23 | M | 7 | NULL | +-------+--------------+-----+--------+---------+-----------+
8、students表中，查询年龄大于等于20岁，小于等于25岁的同学的信息
select * from students where age between 20 and 25; +-------+---------------+-----+--------+---------+-----------+ | StuID | Name | Age | Gender | ClassID | TeacherID | +-------+---------------+-----+--------+---------+-----------+ | 1 | Shi Zhongyu | 22 | M | 2 | 3 | | 2 | Shi Potian | 22 | M | 1 | 7 | | 9 | Ren Yingying | 20 | F | 6 | NULL | | 11 | Yuan Chengzhi | 23 | M | 6 | NULL | | 16 | Xu Zhu | 21 | M | 1 | NULL | | 17 | Lin Chong | 25 | M | 4 | NULL | | 18 | Hua Rong | 23 | M | 7 | NULL | | 21 | Huang Yueying | 22 | F | 6 | NULL | | 22 | Xiao Qiao | 20 | F | 1 | NULL | | 23 | Ma Chao | 23 | M | 4 | NULL | +-------+---------------+-----+--------+---------+-----------+ 第二种 select * from students where age>=20 and age<=25; +-------+---------------+-----+--------+---------+-----------+ | StuID | Name | Age | Gender | ClassID | TeacherID | +-------+---------------+-----+--------+---------+-----------+ | 1 | Shi Zhongyu | 22 | M | 2 | 3 | | 2 | Shi Potian | 22 | M | 1 | 7 | | 9 | Ren Yingying | 20 | F | 6 | NULL | | 11 | Yuan Chengzhi | 23 | M | 6 | NULL | | 16 | Xu Zhu | 21 | M | 1 | NULL | | 17 | Lin Chong | 25 | M | 4 | NULL | | 18 | Hua Rong | 23 | M | 7 | NULL | | 21 | Huang Yueying | 22 | F | 6 | NULL | | 22 | Xiao Qiao | 20 | F | 1 | NULL | | 23 | Ma Chao | 23 | M | 4 | NULL | +-------+---------------+-----+--------+---------+-----------+
9、以ClassID分组，显示每班的同学的人数
select classid as 班级,count(stuid) as 班级人数 from students where classid is not null group by classid; +--------+--------------+ | 班级 | 班级人数 | +--------+--------------+ | 1 | 4 | | 2 | 3 | | 3 | 4 | | 4 | 4 | | 5 | 1 | | 6 | 4 | | 7 | 3 | +--------+--------------+
10、以ClassID分组，显示其平均年龄大于25的班级
select classid as 班级,avg(age) as 平均年龄 from students where classid is not null group by classid having 平均年龄>25; +--------+--------------+ | 班级 | 平均年龄 | +--------+--------------+ | 2 | 36.0000 | | 5 | 46.0000 | +--------+--------------+
