MariaDB之SELECT语法练习

[toc]

一、导入hellodb.sql生成数据库后实现以下操作

 # mysql -uroot -h172.16.23.23 -pcento.123 < hellodb.sql

 mysql> SHOW DATABASES; #可以列出已存在的数据库 
+--------------------+
| Database           |
+--------------------+
| information_schema |
| NODE1              |
| RJYY               |
| hellodb            |
| mysql              |
| performance_schema |
| test               |
+--------------------+

 mysql> USE hellodb;
mysql> SHOW TABLES; 
+-------------------+
| Tables_in_hellodb |
+-------------------+
| classes           |
| coc               |
| courses           |
| scores            |
| students          |
| teachers          |
| toc               |
+-------------------+

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 avg(age),ClassID FROM students WHERE ClassID IS NOT NULL GROUP BY ClassID ;
+----------+---------+
| avg(age) | ClassID |
+----------+---------+
|  20.5000 |       1 |
|  36.0000 |       2 |
|  20.2500 |       3 |
|  24.7500 |       4 |
|  46.0000 |       5 |
|  20.7500 |       6 |
|  19.6667 |       7 |
+----------+---------+

3、显示第2题中平均年龄大于30的分组及平均年龄；

 mysql> SELECT avg(Age),ClassID FROM students WHERE ClassID IS NOT NULL  GROUP BY ClassID HAVING avg(Age) > 30;
+----------+---------+
| avg(Age) | ClassID |
+----------+---------+
|  36.0000 |       2 |
|  46.0000 |       5 |
+----------+---------+

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 |
|    23 | Ma Chao      |  23 | M      |       4 |      NULL |
|    18 | Hua Rong     |  23 | M      |       7 |      NULL |
+-------+--------------+-----+--------+---------+-----------+

7、查询年龄大于等于20岁，小于等于25岁的同学的信息；用三种方法；

 mysql> SELECT * FROM students WHERE Age >=20 AND Age <=25;    
mysql> SELECT * FROM students WHERE Age  BETWEEN 20 AND 25; 
mysql> SELECT * FROM students WHERE Age  IN (20,21,22,23,24,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 |
+-------+---------------+-----+--------+---------+-----------+

二、导入hellodb.sql，以下操作在students表上执行

1、以ClassID分组，显示每班的同学的人数；

 mysql> SELECT count(StuID),ClassID FROM students GROUP BY ClassID ;  
+--------------+---------+
 | count(StuID) | ClassID |
+--------------+---------+
|            2 |    NULL |
|            4 |       1 |
|            3 |       2 |
|            4 |       3 |
|            4 |       4 |
|            1 |       5 |
|            4 |       6 |
|            3 |       7 |
+--------------+---------+

2、以Gender分组，显示其年龄之和；

 mysql> SELECT sum(Age),Gender FROM students GROUP BY Gender ;
+----------+--------+
| sum(Age) | Gender |
+----------+--------+
|      190 | F      |
|      495 | M      |
+----------+--------+

3、以ClassID分组，显示其平均年龄大于25的班级；

 mysql> SELECT avg(Age),ClassID FROM students GROUP BY ClassID HAVING avg(Age) > 25;
+----------+---------+
| avg(Age) | ClassID |
+----------+---------+
|  63.5000 |    NULL |
|  36.0000 |       2 |
|  46.0000 |       5 |
+----------+---------+

4、以Gender分组，显示各组中年龄大于25的学员的年龄之和；

 mysql> SELECT sum(Age),Gender FROM students WHERE Age > 25 GROUP BY Gender ;
+----------+--------+
| sum(Age) | Gender |
+----------+--------+
|      317 | M      |
+----------+--------+

三、导入hellodb.sql，完成以下题目：

1、显示前5位同学的姓名、课程及成绩；

 mysql> SELECT s.Name,courses.Course,scores.Score FROM (select * from students limit 5) 
AS s  LEFT JOIN scores ON scores.StuID = s.StuID LEFT JOIN courses ON scores.CourseID =courses.CourseID;
mysql> SELECT s.name,sc.course,sc.score FROM (SELECT * FROM students LIMIT 5 ) 
AS s LEFT JOIN (SELECT scores.stuid,courses.course,scores.score FROM scores LEFT JOIN courses ON 
courses.CourseID=scores.CourseID)AS sc ON s.StuId=sc.StuID;
+-------------+----------------+-------+
| 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 |
+-------------+----------------+-------+

2、显示其成绩高于80的同学的名称及课程；

 mysql> SELECT Name,Course,Score FROM (students LEFT JOIN scores ON students.StuID=scores.StuID ) 
LEFT JOIN courses ON courses.CourseID=scores.CourseID WHERE 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 |
+-------------+----------------+-------+

3、求前8位同学每位同学自己两门课的平均成绩，并按降序排列；

 mysql> SELECT Name,avg(Score) FROM (SELECT  * FROM students LIMIT 8) AS rj LEFT JOIN scores AS
jr ON rj.StuID=jr.StuID GROUP BY Name ORDER BY avg(Score) DESC;
+-------------+------------+
| Name        | avg(Score) |
+-------------+------------+
| 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 |
+-------------+------------+

4、显示每门课程课程名称及学习了这门课的同学的个数；

 mysql> SELECT courses.Course,count(rj.StuID) FROM scores AS rj LEFT JOIN courses ON courses.CourseID=rj.CourseID GROUP BY rj.CourseID;
+----------------+-----------------+
| Course         | count(rj.StuID) |
+----------------+-----------------+
| Hamo Gong      |               3 |
| Kuihua Baodian |               4 |
| Jinshe Jianfa  |               1 |
| Taiji Quan     |               1 |
| Daiyu Zanghua  |               2 |
| Weituo Zhang   |               2 |
| Dagou Bangfa   |               2 |
+----------------+-----------------+

四、思考题

1、如何显示其年龄大于平均年龄的同学的名字？

 mysql> SELECT Name,Age FROM students WHERE Age > (SELECT avg(Age) FROM students);
+--------------+-----+
| Name         | Age |
+--------------+-----+
| Xie Yanke    |  53 |
| Ding Dian    |  32 |
| Shi Qing     |  46 |
| Tian Boguang |  33 |
| Sun Dasheng  | 100 |
+--------------+-----+

2、如何显示其学习的课程为第1、2，4或第7门课的同学的名字？

 mysql> SELECT rj.Name,scores.CourseID FROM students AS rj LEFT JOIN scores ON scores.StuID = rj.StuID WHERE scores.CourseID IN (1,2,4,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 |
+-------------+----------+

3、如何显示其成员数最少为3个的班级的同学中年龄大于同班同学平均年龄的同学？

 mysql> SELECT students.name,students.age,tp.classid,tp.vg FROM students,(SELECT classid,COUNT(stuid) 
AS cs,AVG(age) AS vg FROM students GROUP BY classid HAVING cs >=3) AS tp WHERE  students.age>tp.vg AND students.classid=tp.classid;
+---------------+-----+---------+---------+
| name          | age | classid | vg      |
+---------------+-----+---------+---------+
| Shi Potian    |  22 |       1 | 20.5000 |
| Xie Yanke     |  53 |       2 | 36.0000 |
| Ding Dian     |  32 |       4 | 24.7500 |
| Yu Yutong     |  26 |       3 | 20.2500 |
| Yuan Chengzhi |  23 |       6 | 20.7500 |
| Xu Zhu        |  21 |       1 | 20.5000 |
| Lin Chong     |  25 |       4 | 24.7500 |
| Hua Rong      |  23 |       7 | 19.6667 |
| Huang Yueying |  22 |       6 | 20.7500 |
+---------------+-----+---------+---------+

4、统计各班级中年龄大于全校同学平均年龄的同学。

 mysql> SELECT rj.Name,rj.Age FROM students AS rj LEFT JOIN classes AS jr ON  
rj.ClassID=jr.ClassID WHERE rj.ClassID=jr.ClassID AND Age > (SELECT AVG(Age) FROM students);
+--------------+-----+
| Name         | Age |
+--------------+-----+
| Xie Yanke    |  53 |
| Ding Dian    |  32 |
| Shi Qing     |  46 |
| Tian Boguang |  33 |
+--------------+-----+

	mysql> SHOW DATABASES; #可以列出已存在的数据库
	+--------------------+
	\| Database \|
	+--------------------+
	\| information_schema \|
	\| NODE1 \|
	\| RJYY \|
	\| hellodb \|
	\| mysql \|
	\| performance_schema \|
	\| test \|
	+--------------------+

	mysql> USE hellodb;
	mysql> SHOW TABLES;
	+-------------------+
	\| Tables_in_hellodb \|
	+-------------------+
	\| classes \|
	\| coc \|
	\| courses \|
	\| scores \|
	\| students \|
	\| teachers \|
	\| toc \|
	+-------------------+

	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 \|
	+--------------+-----+

	mysql> SELECT avg(age),ClassID FROM students WHERE ClassID IS NOT NULL GROUP BY ClassID ;
	+----------+---------+
	\| avg(age) \| ClassID \|
	+----------+---------+
	\| 20.5000 \| 1 \|
	\| 36.0000 \| 2 \|
	\| 20.2500 \| 3 \|
	\| 24.7500 \| 4 \|
	\| 46.0000 \| 5 \|
	\| 20.7500 \| 6 \|
	\| 19.6667 \| 7 \|
	+----------+---------+

	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 \|
	+-------+-------------+-----+--------+---------+-----------+

	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 \|
	+-------+-------------+-----+--------+---------+-----------+

	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 \|
	\| 23 \| Ma Chao \| 23 \| M \| 4 \| NULL \|
	\| 18 \| Hua Rong \| 23 \| M \| 7 \| NULL \|
	+-------+--------------+-----+--------+---------+-----------+

	mysql> SELECT * FROM students WHERE Age >=20 AND Age <=25;
	mysql> SELECT * FROM students WHERE Age BETWEEN 20 AND 25;
	mysql> SELECT * FROM students WHERE Age IN (20,21,22,23,24,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 count(StuID),ClassID FROM students GROUP BY ClassID ;
	+--------------+---------+
	\| count(StuID) \| ClassID \|
	+--------------+---------+
	\| 2 \| NULL \|
	\| 4 \| 1 \|
	\| 3 \| 2 \|
	\| 4 \| 3 \|
	\| 4 \| 4 \|
	\| 1 \| 5 \|
	\| 4 \| 6 \|
	\| 3 \| 7 \|
	+--------------+---------+

	mysql> SELECT sum(Age),Gender FROM students GROUP BY Gender ;
	+----------+--------+
	\| sum(Age) \| Gender \|
	+----------+--------+
	\| 190 \| F \|
	\| 495 \| M \|
	+----------+--------+

	mysql> SELECT s.Name,courses.Course,scores.Score FROM (select * from students limit 5)
	AS s LEFT JOIN scores ON scores.StuID = s.StuID LEFT JOIN courses ON scores.CourseID =courses.CourseID;
	mysql> SELECT s.name,sc.course,sc.score FROM (SELECT * FROM students LIMIT 5 )
	AS s LEFT JOIN (SELECT scores.stuid,courses.course,scores.score FROM scores LEFT JOIN courses ON
	courses.CourseID=scores.CourseID)AS sc ON s.StuId=sc.StuID;
	+-------------+----------------+-------+
	\| 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 \|
	+-------------+----------------+-------+

	mysql> SELECT Name,Course,Score FROM (students LEFT JOIN scores ON students.StuID=scores.StuID )
	LEFT JOIN courses ON courses.CourseID=scores.CourseID WHERE 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 \|
	+-------------+----------------+-------+

	mysql> SELECT Name,avg(Score) FROM (SELECT * FROM students LIMIT 8) AS rj LEFT JOIN scores AS
	jr ON rj.StuID=jr.StuID GROUP BY Name ORDER BY avg(Score) DESC;
	+-------------+------------+
	\| Name \| avg(Score) \|
	+-------------+------------+
	\| 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 \|
	+-------------+------------+

	mysql> SELECT courses.Course,count(rj.StuID) FROM scores AS rj LEFT JOIN courses ON courses.CourseID=rj.CourseID GROUP BY rj.CourseID;
	+----------------+-----------------+
	\| Course \| count(rj.StuID) \|
	+----------------+-----------------+
	\| Hamo Gong \| 3 \|
	\| Kuihua Baodian \| 4 \|
	\| Jinshe Jianfa \| 1 \|
	\| Taiji Quan \| 1 \|
	\| Daiyu Zanghua \| 2 \|
	\| Weituo Zhang \| 2 \|
	\| Dagou Bangfa \| 2 \|
	+----------------+-----------------+

	mysql> SELECT rj.Name,scores.CourseID FROM students AS rj LEFT JOIN scores ON scores.StuID = rj.StuID WHERE scores.CourseID IN (1,2,4,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 \|
	+-------------+----------+

	mysql> SELECT students.name,students.age,tp.classid,tp.vg FROM students,(SELECT classid,COUNT(stuid)
	AS cs,AVG(age) AS vg FROM students GROUP BY classid HAVING cs >=3) AS tp WHERE students.age>tp.vg AND students.classid=tp.classid;
	+---------------+-----+---------+---------+
	\| name \| age \| classid \| vg \|
	+---------------+-----+---------+---------+
	\| Shi Potian \| 22 \| 1 \| 20.5000 \|
	\| Xie Yanke \| 53 \| 2 \| 36.0000 \|
	\| Ding Dian \| 32 \| 4 \| 24.7500 \|
	\| Yu Yutong \| 26 \| 3 \| 20.2500 \|
	\| Yuan Chengzhi \| 23 \| 6 \| 20.7500 \|
	\| Xu Zhu \| 21 \| 1 \| 20.5000 \|
	\| Lin Chong \| 25 \| 4 \| 24.7500 \|
	\| Hua Rong \| 23 \| 7 \| 19.6667 \|
	\| Huang Yueying \| 22 \| 6 \| 20.7500 \|
	+---------------+-----+---------+---------+

	mysql> SELECT rj.Name,rj.Age FROM students AS rj LEFT JOIN classes AS jr ON
	rj.ClassID=jr.ClassID WHERE rj.ClassID=jr.ClassID AND Age > (SELECT AVG(Age) FROM students);
	+--------------+-----+
	\| Name \| Age \|
	+--------------+-----+
	\| Xie Yanke \| 53 \|
	\| Ding Dian \| 32 \|
	\| Shi Qing \| 46 \|
	\| Tian Boguang \| 33 \|
	+--------------+-----+

MariaDB之SELECT语法练习

一 、导入hellodb.sql生成数据库后实现以下操作

1、 在students表中，查询年龄大于25岁，且为男性的同学的名字和年龄；

2、 以ClassID为分组依据，显示每组的平均年龄；

3、 显示第2题中平均年龄大于30的分组及平均年龄；

4、 显示以L开头的名字的同学的信息；

5、 显示TeacherID非空的同学的相关信息；

6、 以年龄排序后，显示年龄最大的前10位同学的信息；

7、 查询年龄大于等于20岁，小于等于25岁的同学的信息；用三种方法；

二、 导入hellodb.sql，以下操作在students表上执行

1、以ClassID分组，显示每班的同学的人数；

2、以Gender分组，显示其年龄之和；

3、以ClassID分组，显示其平均年龄大于25的班级；

4、以Gender分组，显示各组中年龄大于25的学员的年龄之和；

三、 导入hellodb.sql，完成以下题目：

1、显示前5位同学的姓名、课程及成绩；

2、显示其成绩高于80的同学的名称及课程；

3、求前8位同学每位同学自己两门课的平均成绩，并按降序排列；

4、显示每门课程课程名称及学习了这门课的同学的个数；

四、思考题

1、如何显示其年龄大于平均年龄的同学的名字？

2、如何显示其学习的课程为第1、2，4或第7门课的同学的名字？

3、如何显示其成员数最少为3个的班级的同学中年龄大于同班同学平均年龄的同学？

4、统计各班级中年龄大于全校同学平均年龄的同学。

一、导入hellodb.sql生成数据库后实现以下操作

1、在students表中，查询年龄大于25岁，且为男性的同学的名字和年龄；

2、以ClassID为分组依据，显示每组的平均年龄；

3、显示第2题中平均年龄大于30的分组及平均年龄；

4、显示以L开头的名字的同学的信息；

5、显示TeacherID非空的同学的相关信息；

6、以年龄排序后，显示年龄最大的前10位同学的信息；

7、查询年龄大于等于20岁，小于等于25岁的同学的信息；用三种方法；

二、导入hellodb.sql，以下操作在students表上执行

三、导入hellodb.sql，完成以下题目：