导入hellodb.sql生成数据库 ，完成下列操作
目录- 导入hellodb.sql生成数据库 ，完成下列操作
- 导入hellodb.sql
- 查询
- 1. 在students表中，查询年龄大于25岁，且为男性的同学的名字和年龄
- 2.以ClassID为分组依据，显示每组的平均年龄
- 3.显示第2题中平均年龄大于30的分组及平均年龄
- 4.显示以L开头的名字的同学的信息
- 5.显示TeacherID非空的同学的相关信息
- 6.以年龄排序后，显示年龄最大的前10位同学的信息
- 7.查询年龄大于等于20岁，小于等于25岁的同学的信息
- 8.以ClassID分组，显示每班的同学的人数
- 9.以Gender分组，显示其年龄之和
- 10.以ClassID分组，显示其平均年龄大于25的班级
- 11.以Gender分组，显示各组中年龄大于25的学员的年龄之和
- 12.显示前5位同学的姓名、课程及成绩
- 13.显示其成绩高于80的同学的名称及课程
- 14.取每位同学各门课的平均成绩，显示成绩前三名的同学的姓名和平均成绩
- 15.显示每门课程课程名称及学习了这门课的同学的个数
- 16.显示其年龄大于平均年龄的同学的名字
- 17.显示其学习的课程为第1、2，4或第7门课的同学的名字
- 18.显示其成员数最少为3个的班级的同学中年龄大于同班同学平均年龄的同学
- 19.统计各班级中年龄大于全校同学平均年龄的同学
导入hellodb.sql
下载链接：https://wwi.lanzoup.com/ieK0q0htj9mh
[root@node1 ~]# mysql -uroot -p'Mysql@123' < /root/hellodb.sql
[root@node1 ~]# mysql -uroot -p'Mysql@123'
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| hellodb |
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)
mysql> use hellodb;
mysql> show tables;
+-------------------+
| Tables_in_hellodb |
+-------------------+
| classes |
| coc |
| courses |
| scores |
| students |
| teachers |
| toc |
+-------------------+
7 rows in set (0.00 sec)
查询
1. 在students表中，查询年龄大于25岁，且为男性的同学的名字和年龄
mysql> 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 |
+--------------+-----+
2.以ClassID为分组依据，显示每组的平均年龄
mysql> SELECT ClassID,AVG(Age) FROM students WHERE ClassID IS NOT NULL GROUP BY ClassID;
+---------+----------+
| ClassID | AVG(Age) |
+---------+----------+
| 2 | 36.0000 |
| 1 | 20.5000 |
| 4 | 24.7500 |
| 3 | 20.2500 |
| 5 | 46.0000 |
| 7 | 19.6667 |
| 6 | 20.7500 |
+---------+----------+
3.显示第2题中平均年龄大于30的分组及平均年龄
mysql> SELECT ClassID,AVG(Age) FROM students WHERE ClassID IS NOT NULL GROUP BY ClassID HAVING AVG(Age)>30;
+---------+----------+
| ClassID | AVG(Age) |
+---------+----------+
| 2 | 36.0000 |
| 5 | 46.0000 |
+---------+----------+
4.显示以L开头的名字的同学的信息
mysql> 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 |
+-------+-------------+-----+--------+---------+-----------+
5.显示TeacherID非空的同学的相关信息
mysql> 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 |
+-------+-------------+-----+--------+---------+-----------+
6.以年龄排序后，显示年龄最大的前10位同学的信息
mysql> SELECT * FROM students ORDER BY age DESC LIMIT 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 |
| 11 | Yuan Chengzhi | 23 | M | 6 | NULL |
| 18 | Hua Rong | 23 | M | 7 | NULL |
+-------+---------------+-----+--------+---------+-----------+
7.查询年龄大于等于20岁，小于等于25岁的同学的信息
mysql> 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 |
+-------+---------------+-----+--------+---------+-----------+
或
mysql> 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 |
+-------+---------------+-----+--------+---------+-----------+
8.以ClassID分组，显示每班的同学的人数
mysql> SELECT ClassID,COUNT(StuID) FROM students GROUP BY ClassID;
+---------+--------------+
| ClassID | COUNT(StuID) |
+---------+--------------+
| 2 | 3 |
| 1 | 4 |
| 4 | 4 |
| 3 | 4 |
| 5 | 1 |
| 7 | 3 |
| 6 | 4 |
| NULL | 2 |
+---------+--------------+
9.以Gender分组，显示其年龄之和
mysql> SELECT Gender,SUM(Age) FROM students GROUP BY Gender ;
+--------+----------+
| Gender | SUM(Age) |
+--------+----------+
| M | 495 |
| F | 190 |
+--------+----------+
10.以ClassID分组，显示其平均年龄大于25的班级
mysql> SELECT ClassID,AVG(Age) FROM students GROUP BY ClassID HAVING AVG(Age)>25;
+---------+----------+
| ClassID | AVG(Age) |
+---------+----------+
| 2 | 36.0000 |
| 5 | 46.0000 |
| NULL | 63.5000 |
+---------+----------+
11.以Gender分组，显示各组中年龄大于25的学员的年龄之和
mysql> SELECT Gender,SUM(Age) FROM students WHERE Age>25 GROUP BY Gender;
+--------+----------+
| Gender | SUM(Age) |
+--------+----------+
| M | 317 |
+--------+----------+
12.显示前5位同学的姓名、课程及成绩
mysql> SELECT name,Course,Score FROM students st INNER JOIN scores sc ON st.StuID=sc.StuID INNER JOIN courses co ON sc.CourseID=co.CourseID LIMIT 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 |
+-------------+----------------+-------+
13.显示其成绩高于80的同学的名称及课程
mysql> SELECT st.`Name`,sc.Score,cl.Class FROM students st INNER JOIN scores sc ON sc.StuID=st.StuID INNER JOIN classes cl ON st.ClassID=cl.ClassID WHERE sc.Score>80;
+-------------+-------+----------------+
| Name | Score | Class |
+-------------+-------+----------------+
| Shi Zhongyu | 93 | Emei Pai |
| Shi Potian | 97 | Shaolin Pai |
| Xie Yanke | 88 | Emei Pai |
| Ding Dian | 89 | Wudang Pai |
| Shi Qing | 96 | Riyue Shenjiao |
| Xi Ren | 86 | QingCheng Pai |
| Xi Ren | 83 | QingCheng Pai |
| Lin Daiyu | 93 | Ming Jiao |
+-------------+-------+----------------+
14.取每位同学各门课的平均成绩，显示成绩前三名的同学的姓名和平均成绩
mysql> SELECT st.name,avg(score) 平均成绩 FROM students st INNER JOIN scores sc on st.StuID=sc.StuID GROUP BY st.`Name` ORDER BY 平均成绩 DESC LIMIT 3;
+-------------+--------------+
| name | 平均成绩 |
+-------------+--------------+
| Shi Qing | 96.0000 |
| Shi Zhongyu | 85.0000 |
| Xi Ren | 84.5000 |
+-------------+--------------+
15.显示每门课程课程名称及学习了这门课的同学的个数
mysql> SELECT Course,COUNT(StuID) FROM scores sc INNER JOIN courses co ON sc.CourseID=co.CourseID GROUP BY Course;
+----------------+--------------+
| Course | COUNT(StuID) |
+----------------+--------------+
| Kuihua Baodian | 4 |
| Weituo Zhang | 2 |
| Daiyu Zanghua | 2 |
| Hamo Gong | 3 |
| Dagou Bangfa | 2 |
| Taiji Quan | 1 |
| Jinshe Jianfa | 1 |
+----------------+--------------+
16.显示其年龄大于平均年龄的同学的名字
mysql> SELECT `Name` FROM students st WHERE st.Age>(SELECT AVG(Age) FROM students);
+--------------+
| Name |
+--------------+
| Xie Yanke |
| Ding Dian |
| Shi Qing |
| Tian Boguang |
| Sun Dasheng |
+--------------+
17.显示其学习的课程为第1、2，4或第7门课的同学的名字
mysql> SELECT`Name`,CourseID FROM students st INNER JOIN scores sc ON st.StuID=sc.StuID WHERE sc.CourseID IN (1,2,4) OR sc.CourseID = 7;
+-------------+----------+
| Name | CourseID |
+-------------+----------+
| Shi Zhongyu | 2 |
| Shi Potian | 2 |
| Xie Yanke | 2 |
| Ding Dian | 2 |
| Yu Yutong | 1 |
| Yu Yutong | 7 |
| Shi Qing | 1 |
| Xi Ren | 1 |
| Xi Ren | 7 |
| Lin Daiyu | 4 |
+-------------+----------+
18.显示其成员数最少为3个的班级的同学中年龄大于同班同学平均年龄的同学
mysql> SELECT ClassID,COUNT(StuID),AVG(Age) FROM students GROUP BY ClassID HAVING COUNT(StuID)>=3;
+---------+--------------+----------+
| ClassID | COUNT(StuID) | AVG(Age) |
+---------+--------------+----------+
| 2 | 3 | 36.0000 |
| 1 | 4 | 20.5000 |
| 4 | 4 | 24.7500 |
| 3 | 4 | 20.2500 |
| 7 | 3 | 19.6667 |
| 6 | 4 | 20.7500 |
+---------+--------------+----------+
19.统计各班级中年龄大于全校同学平均年龄的同学
mysql> SELECT * FROM students WHERE Age > (SELECT AVG(Age) FROM students WHERE ClassID IS NOT NULL) AND ClassID IS NOT NULL;
+-------+--------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+--------------+-----+--------+---------+-----------+
| 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 |
| 13 | Tian Boguang | 33 | M | 2 | NULL |
| 17 | Lin Chong | 25 | M | 4 | NULL |
+-------+--------------+-----+--------+---------+-----------+