【就业班作业】【第十四周】 导入hellodb.sql生成数据库，查询年龄大于25岁，且为男性的同学的名字和年龄等
【第十四周】 导入hellodb.sql生成数据库
mysql -uroot -pubuntu < ~/data/hellodb.sql
MariaDB [(none)]> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | hellodb | | mysql | | performance_schema | +--------------------+ 4 rows in set (0.00 sec) MariaDB [(none)]>
(1) 在students表中，查询年龄大于25岁，且为男性的同学的名字和年龄
MariaDB [(none)]> use hellodb; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed MariaDB [hellodb]> show tables; +-------------------+ | Tables_in_hellodb | +-------------------+ | classes | | coc | | courses | | scores | | students | | teachers | | toc | +-------------------+ 7 rows in set (0.00 sec) MariaDB [hellodb]> select * from students; +-------+---------------+-----+--------+---------+-----------+ | 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 | | 6 | Shi Qing | 46 | M | 5 | NULL | | 7 | Xi Ren | 19 | F | 3 | NULL | | 8 | Lin Daiyu | 17 | F | 7 | NULL | | 9 | Ren Yingying | 20 | F | 6 | NULL | | 10 | Yue Lingshan | 19 | F | 3 | NULL | | 11 | Yuan Chengzhi | 23 | M | 6 | NULL | | 12 | Wen Qingqing | 19 | F | 1 | NULL | | 13 | Tian Boguang | 33 | M | 2 | NULL | | 14 | Lu Wushuang | 17 | F | 3 | NULL | | 15 | Duan Yu | 19 | M | 4 | NULL | | 16 | Xu Zhu | 21 | M | 1 | NULL | | 17 | Lin Chong | 25 | M | 4 | NULL | | 18 | Hua Rong | 23 | M | 7 | NULL | | 19 | Xue Baochai | 18 | F | 6 | NULL | | 20 | Diao Chan | 19 | F | 7 | NULL | | 21 | Huang Yueying | 22 | F | 6 | NULL | | 22 | Xiao Qiao | 20 | F | 1 | NULL | | 23 | Ma Chao | 23 | M | 4 | NULL | | 24 | Xu Xian | 27 | M | NULL | NULL | | 25 | Sun Dasheng | 100 | M | NULL | NULL | +-------+---------------+-----+--------+---------+-----------+ 25 rows in set (0.00 sec) MariaDB [hellodb]> select Name as 名字,Age as 年龄 from students where Age >25 and Gender = 'M'; +--------------+--------+ | 名字 | 年龄 | +--------------+--------+ | Xie Yanke | 53 | | Ding Dian | 32 | | Yu Yutong | 26 | | Shi Qing | 46 | | Tian Boguang | 33 | | Xu Xian | 27 | | Sun Dasheng | 100 | +--------------+--------+ 7 rows in set (0.00 sec) MariaDB [hellodb]>
(2) 以ClassID为分组依据，显示每组的平均年龄
MariaDB [hellodb]> select ClassID as 班级号,avg(Age) as 平均年龄 from students group by Classid; +-----------+--------------+ | 班级号 | 平均年龄 | +-----------+--------------+ | NULL | 63.5000 | | 1 | 20.5000 | | 2 | 36.0000 | | 3 | 20.2500 | | 4 | 24.7500 | | 5 | 46.0000 | | 6 | 20.7500 | | 7 | 19.6667 | +-----------+--------------+ 8 rows in set (0.00 sec) MariaDB [hellodb]>
(3) 显示第2题中平均年龄大于30的分组及平均年龄
MariaDB [hellodb]> select ClassID as 班级号,avg(Age) as 平均年龄 from students group by Classid; +-----------+--------------+ | 班级号 | 平均年龄 | +-----------+--------------+ | NULL | 63.5000 | | 1 | 20.5000 | | 2 | 36.0000 | | 3 | 20.2500 | | 4 | 24.7500 | | 5 | 46.0000 | | 6 | 20.7500 | | 7 | 19.6667 | +-----------+--------------+ 8 rows in set (0.00 sec) MariaDB [hellodb]> MariaDB [hellodb]> select ClassID as 班级号,avg(Age) as 平均年龄 from students group by Classid having 平均年龄 > 30; +-----------+--------------+ | 班级号 | 平均年龄 | +-----------+--------------+ | NULL | 63.5000 | | 2 | 36.0000 | | 5 | 46.0000 | +-----------+--------------+ 3 rows in set (0.00 sec) MariaDB [hellodb]>
(4) 显示以L开头的名字的同学的信息
MariaDB [hellodb]> select * from students; +-------+---------------+-----+--------+---------+-----------+ | 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 | | 6 | Shi Qing | 46 | M | 5 | NULL | | 7 | Xi Ren | 19 | F | 3 | NULL | | 8 | Lin Daiyu | 17 | F | 7 | NULL | | 9 | Ren Yingying | 20 | F | 6 | NULL | | 10 | Yue Lingshan | 19 | F | 3 | NULL | | 11 | Yuan Chengzhi | 23 | M | 6 | NULL | | 12 | Wen Qingqing | 19 | F | 1 | NULL | | 13 | Tian Boguang | 33 | M | 2 | NULL | | 14 | Lu Wushuang | 17 | F | 3 | NULL | | 15 | Duan Yu | 19 | M | 4 | NULL | | 16 | Xu Zhu | 21 | M | 1 | NULL | | 17 | Lin Chong | 25 | M | 4 | NULL | | 18 | Hua Rong | 23 | M | 7 | NULL | | 19 | Xue Baochai | 18 | F | 6 | NULL | | 20 | Diao Chan | 19 | F | 7 | NULL | | 21 | Huang Yueying | 22 | F | 6 | NULL | | 22 | Xiao Qiao | 20 | F | 1 | NULL | | 23 | Ma Chao | 23 | M | 4 | NULL | | 24 | Xu Xian | 27 | M | NULL | NULL | | 25 | Sun Dasheng | 100 | M | NULL | NULL | +-------+---------------+-----+--------+---------+-----------+ 25 rows in set (0.00 sec) MariaDB [hellodb]> 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 | +-------+-------------+-----+--------+---------+-----------+ 3 rows in set (0.00 sec) MariaDB [hellodb]>
（结束）
mysql -uroot -pubuntu < ~/data/hellodb.sql
