–
此处使用hellodb.sql文件作为演示
练习:导入hellodb.sql生成数据库 (1) 在students表中,查询年龄大于25岁,且为男性的同学的名字和年龄; (2) 以ClassID为分组依据,显示每组的平均年龄; (3) 显示第2题中平均年龄大于30的分组及平均年龄; (4) 显示以L开头的名字的同学的信息; (5) 显示TeacherID非空的同学的相关信息; (6) 以年龄排序后,显示年龄最大的前10位同学的信息; (7) 查询年龄大于等于20岁,小于等于25岁的同学的信息;用三种方法; MariaDB [hellodb]> 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 | +--------------+-----+ 7 rows in set (0.00 sec) MariaDB [hellodb]> select classid,avg(age) from students group by classid; +---------+----------+ | classid | avg(age) | +---------+----------+ | NULL | 63.5000 | | 1 | 20.5000 | | 2 | 36.0000 | | 3 | 20.2500 | | 4 | 24.2000 | | 5 | 46.0000 | | 6 | 20.7500 | | 7 | 19.6667 | +---------+----------+ 8 rows in set (0.00 sec) MariaDB [hellodb]> select classid,avg(age) from students group by classid having avg(age)>30; +---------+----------+ | classid | avg(age) | +---------+----------+ | NULL | 63.5000 | | 2 | 36.0000 | | 5 | 46.0000 | +---------+----------+ 3 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]> 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 | | 26 | shi | 22 | M | 4 | 23 | +-------+-------------+-----+--------+---------+-----------+ 6 rows in set (0.00 sec) MariaDB [hellodb]> select name,age from students order by age desc limit 10; +--------------+-----+ | name | age | +--------------+-----+ | Sun Dasheng | 100 | | Xie Yanke | 53 | | Shi Qing | 46 | | Tian Boguang | 33 | | Ding Dian | 32 | | Xu Xian | 27 | | Yu Yutong | 26 | | Lin Chong | 25 | | Ma Chao | 23 | | Hua Rong | 23 | +--------------+-----+ 10 rows in set (0.00 sec) MariaDB [hellodb]> select name,age from students where age>=20 and age<25; +---------------+-----+ | name | age | +---------------+-----+ | Shi Zhongyu | 22 | | Shi Potian | 22 | | Ren Yingying | 20 | | Yuan Chengzhi | 23 | | Xu Zhu | 21 | | Hua Rong | 23 | | Huang Yueying | 22 | | Xiao Qiao | 20 | | Ma Chao | 23 | | shi | 22 | +---------------+-----+ 10 rows in set (0.00 sec) 练习:导入hellodb.sql,以下操作在students表上执行 1、以ClassID分组,显示每班的同学的人数; 2、以Gender分组,显示其年龄之和; 3、以ClassID分组,显示其平均年龄大于25的班级; 4、以Gender分组,显示各组中年龄大于25的学员的年龄之和; MariaDB [hellodb]> select classid,count(classid) as count from students group by classid; +---------+-------+ | classid | count | +---------+-------+ | NULL | 0 | | 1 | 4 | | 2 | 3 | | 3 | 4 | | 4 | 5 | | 5 | 1 | | 6 | 4 | | 7 | 3 | +---------+-------+ 8 rows in set (0.00 sec) MariaDB [hellodb]> select gender,sum(age) from students group by gender; +--------+----------+ | gender | sum(age) | +--------+----------+ | F | 190 | | M | 517 | +--------+----------+ 2 rows in set (0.00 sec) MariaDB [hellodb]> select classid,avg(age) as avg from students group by classid having avg>25; +---------+---------+ | classid | avg | +---------+---------+ | NULL | 63.5000 | | 2 | 36.0000 | | 5 | 46.0000 | +---------+---------+ 3 rows in set (0.00 sec) MariaDB [hellodb]> select gender,sum(age) from students where age>25 group by gender; +--------+----------+ | gender | sum(age) | +--------+----------+ | M | 317 | +--------+----------+ 1 row in set (0.00 sec) 练习:导入hellodb.sql,完成以下题目: 1、显示前5位同学的姓名、课程及成绩; 2、显示其成绩高于80的同学的名称及课程; 3、求前8位同学每位同学自己两门课的平均成绩,并按降序排列; 4、显示每门课程课程名称及学习了这门课的同学的个数; MariaDB [hellodb]> select students.name,courses.course,scores.score from students,courses,scores where \ students.stuid=scores.stuid and scores.courseid=courses.courseid and students.stuid<=5; +-------------+----------------+-------+ | name | course | score | +-------------+----------------+-------+ | Shi Zhongyu | Kuihua Baodian | 77 | | Shi Zhongyu | Weituo Zhang | 93 | | Shi Potian | Kuihua Baodian | 47 | | Shi Potian | Daiyu Zanghua | 97 | | Xie Yanke | Kuihua Baodian | 88 | | Xie Yanke | Weituo Zhang | 75 | | Ding Dian | Daiyu Zanghua | 71 | | Ding Dian | Kuihua Baodian | 89 | | Yu Yutong | Hamo Gong | 39 | | Yu Yutong | Dagou Bangfa | 63 | +-------------+----------------+-------+ 10 rows in set (0.00 sec) MariaDB [hellodb]> select students.name,courses.course,scores.score from students,courses,scores where \ students.stuid=scores.stuid and scores.courseid=courses.courseid and scores.score>80; +-------------+----------------+-------+ | name | course | score | +-------------+----------------+-------+ | Shi Zhongyu | Weituo Zhang | 93 | | Shi Potian | Daiyu Zanghua | 97 | | Xie Yanke | Kuihua Baodian | 88 | | Ding Dian | Kuihua Baodian | 89 | | Shi Qing | Hamo Gong | 96 | | Xi Ren | Hamo Gong | 86 | | Xi Ren | Dagou Bangfa | 83 | | Lin Daiyu | Jinshe Jianfa | 93 | +-------------+----------------+-------+ 8 rows in set (0.00 sec) MariaDB [hellodb]> select students.name,avg(scores.score) as avg from students,scores where \ students.stuid=scores.stuid and students.stuid<=8 group by students.stuid order by avg desc; +-------------+---------+ | name | avg | +-------------+---------+ | Shi Qing | 96.0000 | | Shi Zhongyu | 85.0000 | | Xi Ren | 84.5000 | | Xie Yanke | 81.5000 | | Ding Dian | 80.0000 | | Lin Daiyu | 75.0000 | | Shi Potian | 72.0000 | | Yu Yutong | 51.0000 | +-------------+---------+ 8 rows in set (0.00 sec) MariaDB [hellodb]> select courses.course,count(courses.course) as count from students,courses,coc where \ students.classid=coc.classid and coc.classid=courses.courseid group by courses.course; +----------------+-------+ | course | count | +----------------+-------+ | Dagou Bangfa | 6 | | Daiyu Zanghua | 2 | | Hamo Gong | 8 | | Jinshe Jianfa | 8 | | Kuihua Baodian | 6 | | Taiji Quan | 10 | | Weituo Zhang | 8 | +----------------+-------+ 7 rows in set (0.01 sec)
–
–
–
评论前必须登录!
注册