mysql SELECT查询_MySQL-mysql教程-PHP中文网

一、单表查询

1、一般查询。2、聚合函数、排序 3、别名。4、分组。5、分组过滤。6、限制显示条目。7、杂项。

二、多表查询

1、联结查询。2、子查询。3、联合查询。

数据库版本：5.5.46-MariaDB

说明一下这几张表，这是在上马哥课程的时候给的生成表的sql备份文件。

在文章最后我把它放到附件中。

注意：在linux上表名是区分大小写的。

如果搞不清语句顺序请看：help select

一、单表查询

1、一般查询

MariaDB [hellodb]> SELECT * FROM students;

+-------+---------------+-----+--------+---------+-----------+

+-------+---------------+-----+--------+---------+-----------+

| 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.05 sec)

MariaDB [hellodb]> SELECT StuID,Name,Age FROM students WHERE Age > 25;

+-------+--------------+-----+

| StuID | Name | Age |

+-------+--------------+-----+

| 3 | Xie Yanke | 53 |

| 4 | Ding Dian | 32 |

| 5 | Yu Yutong | 26 |

| 6 | Shi Qing | 46 |

| 13 | Tian Boguang | 33 |

| 24 | Xu Xian | 27 |

| 25 | Sun Dasheng | 100 |

+-------+--------------+-----+

7 rows in set (0.02 sec)

SELECT中的WHERE子句就是一个布尔条件表达式，来判断行是否区配表达式。只要返回的为真，也就是不为0，则WHERE子句就为真，就会显示匹配的行。

布尔条件表达式操作符：

= 等于，用于数值或字符都可以。

也是等值比较，不过不会跟空产生意外情况。是跟空值比较的安全方式。

不等于，这个就只能用于数值了。

空字符跟空是不一样的。空字符也是一种字符串，也是有自己的ASCII码和值的。

IS NULL 判断是否为空

IS NOT NULL 判断是否为不空

模糊匹配，支持通配符，% 百分号表示任意个任意字符。_ 下划线任意单个字符。在能用等值比较或不等值比较的情况下不要用LIKE，性能差的多。

RLIKE，REGEXP

支持使用正则表达式。性能更低。LIKE, RLIKE只能用来做字符的比较。

也可以完整的匹配数值，不过也没有意义。

判断指定的字段的值是否在给定的列表中， IN （‘abc','cc')

BETWEEN AND

判断指定的字段是否在给定的范围之间。

如 x>=20 AND x

组合条件：

NOT ，!

AND ,，&&

OR，||

注意：在mysql中只要是字符型的在使用的时候都要加引号，而如果是数值型的，一定不能加引号。

例1：IS NULL，判断ClassID字段为空的记录。只显示Name,Age,ClassID.

MariaDB [hellodb]> SELECT Name,Age,ClassID FROM students WHERE ClassID IS NULL;

+-------------+-----+---------+

| Name | Age | ClassID |

+-------------+-----+---------+

| Xu Xian | 27 | NULL |

| Sun Dasheng | 100 | NULL |

+-------------+-----+---------+

2 rows in set (0.00 sec)

例2：用LIKE来模糊匹配Name字段所有以X开头的行。%通配任意个任意字符。

MariaDB [hellodb]> SELECT Name FROM students WHERE Name LIKE 'X%';

+-------------+

| Name |

+-------------+

| Xie Yanke |

| Xi Ren |

| Xu Zhu |

| Xue Baochai |

| Xiao Qiao |

| Xu Xian |

+-------------+

6 rows in set (0.00 sec)

下面的效果跟上面的相同。这里是用正则表达式匹配的。

MariaDB [hellodb]> SELECT Name FROM students WHERE Name RLIKE '^X.*';

例3：IN。下面是查找ClassID是1或3或5的记录。只显示Name和ClassID字段。

MariaDB [hellodb]> SELECT Name,ClassID FROM students WHERE ClassID IN (1,3,5);

+--------------+---------+

| Name | ClassID |

+--------------+---------+

| Shi Potian | 1 |

| Yu Yutong | 3 |

| Shi Qing | 5 |

| Xi Ren | 3 |

| Yue Lingshan | 3 |

| Wen Qingqing | 1 |

| Lu Wushuang | 3 |

| Xu Zhu | 1 |

| Xiao Qiao | 1 |

+--------------+---------+

9 rows in set (0.00 sec)

MariaDB [hellodb]>

例4：BETWEEN AND ，匹配一个范围。年龄在30到50之间。

MariaDB [hellodb]> SELECT Name,Age FROM students WHERE Age BETWEEN 30 AND 50;

+--------------+-----+

| Name | Age |

+--------------+-----+

| Ding Dian | 32 |

| Shi Qing | 46 |

| Tian Boguang | 33 |

+--------------+-----+

3 rows in set (0.00 sec)

MariaDB [hellodb]>

例5：组合AND，gender为m，并且，Age大于30或等于20。这个括号是一定要有的，不然就变成“gender为M并且Age大于30，或者Age等于20。

MariaDB [hellodb]> SELECT * FROM students WHERE gender='M' AND (Age > 30 OR Age = 20);

+-------+--------------+-----+--------+---------+-----------+

+-------+--------------+-----+--------+---------+-----------+

| 3 | Xie Yanke | 53 | M | 2 | 16 |

| 4 | Ding Dian | 32 | M | 4 | 4 |

| 6 | Shi Qing | 46 | M | 5 | NULL |

| 13 | Tian Boguang | 33 | M | 2 | NULL |

| 25 | Sun Dasheng | 100 | M | NULL | NULL |

+-------+--------------+-----+--------+---------+-----------+

5 rows in set (0.01 sec)

想以年龄排序。可以用

ORDER BY [ASC|DESC|字段]

ASC表示升序， DESC表示降序。默认是ASC

以年龄降序排列。

MariaDB [hellodb]> SELECT * FROM students WHERE gender='M' AND (Age > 30 OR Age = 20) ORDER BY Age DESC;

+-------+--------------+-----+--------+---------+-----------+

+-------+--------------+-----+--------+---------+-----------+

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

+-------+--------------+-----+--------+---------+-----------+

5 rows in set (0.01 sec)

MariaDB [hellodb]>

这些只是一般的查询，如果要统计数据，就要用聚合函数了。

2、聚合函数。

话说想统计下一共有多少人，或者女的有多少，男的有多少，平均年龄，最小最大年龄。

SUM(), AVG(), MAX(), MIN(), COUNT()

分别是求和、平均值、最大、最小、统计个数。这几个是常用到的。

例6：SUM()，全体同学年龄总和。可以用WHERE加上条件，如男同学的年龄总和。

MariaDB [hellodb]> SELECT SUM(Age) FROM students;

+----------+

| SUM(Age) |

+----------+

| 685 |

+----------+

1 row in set (0.00 sec)

也可以显示其它字段，不过也只是一行。

例7：AVG()，全体同学的年龄平均值。

MariaDB [hellodb]> SELECT AVG(Age) FROM students;

+----------+

| AVG(Age) |

+----------+

| 27.4000 |

+----------+

1 row in set (0.00 sec)

例8：COUNT(), 统计一共多少学生。COUNT后面有的会使用*。COUNT(*)，这样也可以，不过性能差点。

MariaDB [hellodb]> SELECT COUNT(Name) FROM students;

+-------------+

| COUNT(Name) |

+-------------+

| 25 |

+-------------+

1 row in set (0.00 sec)

我们也可以不让它显示上面的字段名称，给它换一个名称。

3、AS 别名。

MariaDB [hellodb]> SELECT COUNT(Name) AS CC FROM students;

+----+

| CC |

+----+

| 25 |

+----+

1 row in set (0.00 sec)

还有表也可以有别名，在多表查询的时候再来说说。

下面男同学的最小年龄，并用别名显示。

MariaDB [hellodb]> SELECT MIN(Age) AS Min_M FROM students WHERE gender = 'M';

+-------+

| Min_M |

+-------+

| 19 |

+-------+

1 row in set (0.00 sec)

可不可以一次性男女分开显示各自的最小年龄。那就要用分组了。可以按性别gender来分组。这样函数就会分别计算各组的数据。

4、分组。

GROUP BY 字段名

以字段的值分组。同一个值一个组。然后再通过用聚合函数来统计不同组中的信息。

现在以gender分组，也就是两组。函数分别计算两个组。

不过下面这个有点缺陷，不知道哪是女的，哪个是男的。

MariaDB [hellodb]> SELECT MIN(Age) FROM students GROUP BY gender;

+----------+

| MIN(Age) |

+----------+

| 17 |

| 19 |

+----------+

2 rows in set (0.00 sec)

MariaDB [hellodb]>

下面再显示出来性别字段。

MariaDB [hellodb]> SELECT MIN(Age),gender FROM students GROUP BY gender;

+----------+--------+

| MIN(Age) | gender |

+----------+--------+

| 17 | F |

| 19 | M |

+----------+--------+

2 rows in set (0.00 sec)

MariaDB [hellodb]>

例：显示不同班级的学生个数

MariaDB [hellodb]> SELECT Count(Name),ClassID FROM students GROUP BY classID;

+-------------+---------+

| Count(Name) | ClassID |

+-------------+---------+

| 2 | NULL |

| 4 | 1 |

| 3 | 2 |

| 4 | 3 |

| 4 | 4 |

| 1 | 5 |

| 4 | 6 |

| 3 | 7 |

+-------------+---------+

8 rows in set (0.00 sec)

MariaDB [hellodb]>

不显示没有班级的。WHERE在分组之前先进行过滤，然后把数据再给GROUP BY来进行分组。

MariaDB [hellodb]> SELECT Count(Name),ClassID FROM students WHERE ClassID IS NOT NULL GROUP BY classID;

+-------------+---------+

| Count(Name) | ClassID |

+-------------+---------+

| 4 | 1 |

| 3 | 2 |

| 4 | 3 |

| 4 | 4 |

| 1 | 5 |

| 4 | 6 |

| 3 | 7 |

+-------------+---------+

7 rows in set (0.00 sec)

MariaDB [hellodb]>

例9：各个班级的平均年龄。

MariaDB [hellodb]> SELECT AVG(age),ClassID FROM students GROUP BY ClassID;

+----------+---------+

| AVG(age) | ClassID |

+----------+---------+

| 63.5000 | NULL |

| 20.5000 | 1 |

| 36.0000 | 2 |

| 20.2500 | 3 |

| 24.7500 | 4 |

| 46.0000 | 5 |

| 20.7500 | 6 |

| 19.6667 | 7 |

+----------+---------+

8 rows in set (0.00 sec)

MariaDB [hellodb]>

加上排序呢：

MariaDB [hellodb]> SELECT AVG(age),ClassID FROM students GROUP BY ClassID ORDER BY AVG(age);

+----------+---------+

| AVG(age) | ClassID |

+----------+---------+

| 19.6667 | 7 |

| 20.2500 | 3 |

| 20.5000 | 1 |

| 20.7500 | 6 |

| 24.7500 | 4 |

| 36.0000 | 2 |

| 46.0000 | 5 |

| 63.5000 | NULL |

+----------+---------+

8 rows in set (0.00 sec)

意思就是在分组之后，把各个分组重新排序了。以各个组的age字段的平均值来排序。

回来看分组：如果不想显示平均年龄小于等于25的，怎么办呢。

5、分组过滤。

HAVING 用于对分组做条件过滤。

普及：WHERE是对表中的每一行做过滤，单位是行。而HAVING是对每一个组做过滤，单位是组。

如：

MariaDB [hellodb]> 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 |

+----------+---------+

3 rows in set (0.00 sec)

MariaDB [hellodb]>

HAVING拿到手的都是一组一组的数据，所以也要求下平均值。然后不匹配的组，就刷掉。到了SELECT那里，它求一下平均值是为了显示。这是两个不同的部分。

如果想找age小于AVG(age)之类的结果，这里是查不出来的。在子查询部分。

例10：显示最少有3个同学的班级和该班级的人数。

MariaDB [hellodb]> SELECT ClassID,Count(Name) FROM students GROUP BY ClassID HAVING Count(Name) >= 3;

+---------+-------------+

| ClassID | Count(Name) |

+---------+-------------+

| 1 | 4 |

| 2 | 3 |

| 3 | 4 |

| 4 | 4 |

| 6 | 4 |

| 7 | 3 |

+---------+-------------+

6 rows in set (0.00 sec)

MariaDB [hellodb]>

这个表小，这样显示还可以，但是如果有上千上万个的条目，一下子显示出来就有点夸张了，占网络带宽不说，一下子出来这么多，也看不完啊。

6、限制显示条目的数量。

LIMIT

只显示3行。在最后加上limit 3就可以了。

MariaDB [hellodb]> SELECT * FROM students LIMIT 3;

+-------+-------------+-----+--------+---------+-----------+

+-------+-------------+-----+--------+---------+-----------+

| 1 | Shi Zhongyu | 22 | M | 2 | 3 |

| 2 | Shi Potian | 22 | M | 1 | 7 |

| 3 | Xie Yanke | 53 | M | 2 | 16 |

+-------+-------------+-----+--------+---------+-----------+

3 rows in set (0.00 sec)

这是从头开始，显示3行。如果想从中间开始。下面这个是从第5行开始，显示3行。

MariaDB [hellodb]> SELECT * FROM students LIMIT 5,3;

+-------+-----------+-----+--------+---------+-----------+

+-------+-----------+-----+--------+---------+-----------+

| 6 | Shi Qing | 46 | M | 5 | NULL |

| 7 | Xi Ren | 19 | F | 3 | NULL |

| 8 | Lin Daiyu | 17 | F | 7 | NULL |

+-------+-----------+-----+--------+---------+-----------+

3 rows in set (0.00 sec)

7、杂项。

DISTINCT : 指定的结果相同的只显示一次。在SELECT 语句后面。

SQL_CACHE : 缓存此条语句至查询缓存中。

SQL_NO_CACHE: 说明不缓存此条语句。

简单的例子说明下第一个吧。如果显示都有哪些年龄的同学，除了用分组外。还可以用DISTINCT。只不过只能显示一个字段。

age字段重复的就不显示，并且排序。不排序也没有关系。

MariaDB [hellodb]> SELECT DISTINCT age FROM students ORDER BY age;

+-----+

| age |

+-----+

| 17 |

| 18 |

| 19 |

| 20 |

| 21 |

| 22 |

| 23 |

| 25 |

| 26 |

| 27 |

| 32 |

| 33 |

| 46 |

| 53 |

| 100 |

+-----+

15 rows in set (0.00 sec)

到这里单表查询就完了。我们来看看这么多语句它的执行流程。

SELECT语句的执行流程：

FROM --> WHERE --> GROUP BY --> HAVING --> ORDER BY --> SELECT --> LIMIT

首先是FROM获取表数据，然后WHERE筛选，再然后GROUP BY来分组，再然后HAVING给组再来一下过滤，再然后就是ORDER BY给剩下的组或是整张表的行排序，再然后才是SELECT把最终整理好的数据计算或者直接显示出来，当然到达客户端还要经过LIMIT限制。

二、多表查询。

我们知道关系型数据库就是为了降低冗余，所以都是把内容记录到多张表中，我们在查询的时候要把多张表连起来才能查到所有数据。

说明一下，因为表的内容都贴出来的话就太多了，所以这里就只举例子了，具体的表内容，朋友们自己下载看吧。

MariaDB [hellodb]> SHOW TABLES;

+-------------------+

| Tables_in_hellodb |

+-------------------+

| classes |

| coc |

| courses |

| scores |

| students |

| teachers |

| toc |

+-------------------+

7 rows in set (0.00 sec)

1、联结查询

联结查询：先将几张表join起来，然后再根据join以后所产生的表，来进行查询。

有：

交叉联结、自然联结、外联结、自联结。

交叉联结：

就是各个表的各字段的值相乘的关系。各种连结，各种交叉。这里也只是提一下。

直接FROM表就是了。

MariaDB [hellodb]> SELECT * FROM students,coc,classes;

自然联结：

又叫内联结或等值联结，两张表要有相同的字段可以建立联结。用WHERE 联结条件。一般情况下都是使用自然联结。

举例子先，上面的students表中有学生信息，而classes表中有班级的名称。现在想显示学生的名子和所对应的班级名称。

那么就需要student和classes建立结结。正好它们都有classID班级编号。所以：

MariaDB [hellodb]> SELECT students.Name,classes.Class FROM students,classes WHERE students.ClassID = classes.ClassID;

+---------------+----------------+

| Name | Class |

+---------------+----------------+

| Shi Zhongyu | Emei Pai |

| Shi Potian | Shaolin Pai |

| Xie Yanke | Emei Pai |

| Ding Dian | Wudang Pai |

23 rows in set (0.00 sec)

中间省略了，不然太多。上面用WHERE来做两个表的等值条件。

把字段全部显示出来看看：

MariaDB [hellodb]> SELECT * FROM students,classes WHERE students.ClassID = classes.ClassID;

+-------+---------------+-----+--------+---------+-----------+---------+----------------+----------+

+-------+---------------+-----+--------+---------+-----------+---------+----------------+----------+

| 1 | Shi Zhongyu | 22 | M | 2 | 3 | 2 | Emei Pai | 7 |

| 2 | Shi Potian | 22 | M | 1 | 7 | 1 | Shaolin Pai | 10 |

| 3 | Xie Yanke | 53 | M | 2 | 16 | 2 | Emei Pai | 7 |

| 4 | Ding Dian | 32 | M | 4 | 4 | 4 | Wudang Pai | 12 |

| 5 | Yu Yutong | 26 | M | 3 | 1 | 3 | QingCheng Pai | 11 |

| 6 | Shi Qing | 46 | M | 5 | NULL | 5 | Riyue Shenjiao | 31 |

| 7 | Xi Ren | 19 | F | 3 | NULL | 3 | QingCheng Pai | 11 |

ClassID都是相等的。这里还有一个问题就是，在多表连结的时候会有多个字段一样的，所以在写的时候要把表名也给写上，就是这种格式students.ClassID之类的。但是有的表名又很长，这个时候就可以用别名了。在FROM后面的表名后面使用AS。FROM 表名 AS 别名

MariaDB [hellodb]> SELECT * FROM students AS STU,classes AS CLA WHERE STU.ClassID = CLA.ClassID;

+-------+---------------+-----+--------+---------+-----------+---------+----------------+----------+

+-------+---------------+-----+--------+---------+-----------+---------+----------------+----------+

| 1 | Shi Zhongyu | 22 | M | 2 | 3 | 2 | Emei Pai | 7 |

| 2 | Shi Potian | 22 | M | 1 | 7 | 1 | Shaolin Pai | 10 |

| 3 | Xie Yanke | 53 | M | 2 | 16 | 2 | Emei Pai | 7 |

| 4 | Ding Dian | 32 | M | 4 | 4 | 4 | Wudang Pai | 12 |

| 5 | Yu Yutong | 26 | M | 3 | 1 | 3 | QingCheng Pai | 11 |

| 6 | Shi Qing | 46 | M | 5 | NULL | 5 | Riyue Shenjiao | 31 |

多表连结也简单，就是表多了以后会晕乎。所以主要问题就是要熟悉自己的各种表。

有内连结，自然就有外连结。内连结把表的字段的数值与另一张表连接起来，但是并不是所有记录都可以连接起来，比如上面的students表中还有两个人没有显示出来，因为他们没有班级。

+-------+---------------+-----+--------+---------+-----------+

+-------+---------------+-----+--------+---------+-----------+

| 24 | Xu Xian | 27 | M | NULL | NULL |

| 25 | Sun Dasheng | 100 | M | NULL | NULL |

+-------+---------------+-----+--------+---------+-----------+

如这两位仁兄。那么如果我也想把这两个显示出来。就要用到外连接了。

外联结：外联结又分为“左外连结和右外连结”。其实意思都一样，就是以哪个为主，主表所有的都显示出来，别一张表如果对不上就为NULL。

左外联结以左表为基准，右表没有的为NULL.

left_tb LEFT JOIN right_tb ON 连接条件

右外联结以右表为基准，左表没有的为NULL.

left_tb RIGHT JOIN right_tb ON 连接条件

全外联结以两个表为基准，哪个没有哪个为NULL. mysql中没有。

MariaDB [hellodb]> SELECT * FROM students LEFT JOIN classes ON students.ClassID=classes.ClassID;

+-------+---------------+-----+--------+---------+-----------+---------+----------------+----------+

+-------+---------------+-----+--------+---------+-----------+---------+----------------+----------+

| 18 | Hua Rong | 23 | M | 7 | NULL | 7 | Ming Jiao | 27 |

| 19 | Xue Baochai | 18 | F | 6 | NULL | 6 | Lianshan Pai | 27 |

| 20 | Diao Chan | 19 | F | 7 | NULL | 7 | Ming Jiao | 27 |

| 21 | Huang Yueying | 22 | F | 6 | NULL | 6 | Lianshan Pai | 27 |

| 22 | Xiao Qiao | 20 | F | 1 | NULL | 1 | Shaolin Pai | 10 |

| 23 | Ma Chao | 23 | M | 4 | NULL | 4 | Wudang Pai | 12 |

+-------+---------------+-----+--------+---------+-----------+---------+----------------+----------+

就是这样的了。打个比方，如果classes的表有额外的ClassID,如8，9，10之类的，而students表中没有，那么也是不会显示出来的。因为左外连接是以左表为准，管你右边的表有什么。而右外连接就是以右边的表为准了。当然在写表的时候把classes表写左边也是一样的。

这些表还可以作三个表甚至四个表连接的操作。比如加上成绩。大家就自己试试吧。

2、子查询：

在查询中嵌套的查询。

用于WHERE中的子查询

1、用于比较表达式中的子查询。子查询的返回值只能有一个

2、用于EXISTS中的子查询,判断存在与否。

3、用于IN中的子查询,判断存在于指定的列表中。

4、用于FROM中的子查询，SELECT * FROM (SELECT clause) AS alias。这里一定要用别名。

5、在SELECT中也可以用子语句的值来作为一个字段。

先解决查询age>AVG(age)的问题。为什么在上面那里不能用，因为这种写法就是错的。一行还是一组呢。

1、放到WHERE后，WHERE语句的数据是一行一行的，age是可以表示当前行的age值。但是AVG(age)就有问题了，它只能放在GROUP BY后面来计算组的平均值，或是SELECT后面全表的平均值。

2、放到HAVING后面，同样的问题。是一组数据。

如果要查询就要用子查询先计算平均值。

查询所有同学年龄大于平均年龄的。

MariaDB [hellodb]> 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 |

+--------------+-----+

5 rows in set (0.00 sec)

延伸一下：显示平均年龄：

MariaDB [hellodb]> SELECT Name,Age,(SELECT AVG(age) FROM students) AS avg_age FROM students WHERE age > (SELECT AVG(age) FROM students);

+--------------+-----+---------+

| Name | Age | avg_age |

+--------------+-----+---------+

| Xie Yanke | 53 | 27.4000 |

| Ding Dian | 32 | 27.4000 |

| Shi Qing | 46 | 27.4000 |

| Tian Boguang | 33 | 27.4000 |

| Sun Dasheng | 100 | 27.4000 |

+--------------+-----+---------+

5 rows in set (0.00 sec)

那么再延伸一下，显示在各个班级内同学，大于班级内年龄平均值的。

有点复杂，我这里是这样作的。

第一步：求出各个班内的平均年龄。

MariaDB [hellodb]> SELECT AVG(age),ClassID FROM students GROUP BY ClassID;

+----------+---------+

| AVG(age) | ClassID |

+----------+---------+

| 63.5000 | NULL |

| 20.5000 | 1 |

| 36.0000 | 2 |

| 20.2500 | 3 |

| 24.7500 | 4 |

| 46.0000 | 5 |

| 20.7500 | 6 |

| 19.6667 | 7 |

+----------+---------+

8 rows in set (0.00 sec)

第二步：以上面这个结果与students表建立连接。

MariaDB [hellodb]> SELECT * FROM students,(SELECT AVG(age),ClassID FROM students GROUP BY ClassID) AS avg_age WHERE students.ClassID=avg_age.ClassID;

+-------+---------------+-----+--------+---------+-----------+----------+---------+

+-------+---------------+-----+--------+---------+-----------+----------+---------+

| 1 | Shi Zhongyu | 22 | M | 2 | 3 | 36.0000 | 2 |

| 2 | Shi Potian | 22 | M | 1 | 7 | 20.5000 | 1 |

| 3 | Xie Yanke | 53 | M | 2 | 16 | 36.0000 | 2 |

| 4 | Ding Dian | 32 | M | 4 | 4 | 24.7500 | 4 |

| 5 | Yu Yutong | 26 | M | 3 | 1 | 20.2500 | 3 |

| 6 | Shi Qing | 46 | M | 5 | NULL | 46.0000 | 5 |

| 7 | Xi Ren | 19 | F | 3 | NULL | 20.2500 | 3 |

| 8 | Lin Daiyu | 17 | F | 7 | NULL | 19.6667 | 7 |

| 9 | Ren Yingying | 20 | F | 6 | NULL | 20.7500 | 6 |

| 10 | Yue Lingshan | 19 | F | 3 | NULL | 20.2500 | 3 |

| 11 | Yuan Chengzhi | 23 | M | 6 | NULL | 20.7500 | 6 |

| 12 | Wen Qingqing | 19 | F | 1 | NULL | 20.5000 | 1 |

| 13 | Tian Boguang | 33 | M | 2 | NULL | 36.0000 | 2 |

| 14 | Lu Wushuang | 17 | F | 3 | NULL | 20.2500 | 3 |

| 15 | Duan Yu | 19 | M | 4 | NULL | 24.7500 | 4 |

| 16 | Xu Zhu | 21 | M | 1 | NULL | 20.5000 | 1 |

| 17 | Lin Chong | 25 | M | 4 | NULL | 24.7500 | 4 |

| 18 | Hua Rong | 23 | M | 7 | NULL | 19.6667 | 7 |

| 19 | Xue Baochai | 18 | F | 6 | NULL | 20.7500 | 6 |

| 20 | Diao Chan | 19 | F | 7 | NULL | 19.6667 | 7 |

| 21 | Huang Yueying | 22 | F | 6 | NULL | 20.7500 | 6 |

| 22 | Xiao Qiao | 20 | F | 1 | NULL | 20.5000 | 1 |

| 23 | Ma Chao | 23 | M | 4 | NULL | 24.7500 | 4 |

+-------+---------------+-----+--------+---------+-----------+----------+---------+

23 rows in set (0.00 sec)

第三步：这里就直接作判断就可以了。

MariaDB [hellodb]> SELECT * FROM students,(SELECT AVG(age) AS avg_age_col,ClassID FROM students GROUP BY ClassID) AS avg_age_tab WHERE students.ClassID=avg_age_tab.ClassID AND Age > avg_age_col ORDER BY students.ClassID;

+-------+---------------+-----+--------+---------+-----------+-------------+---------+

+-------+---------------+-----+--------+---------+-----------+-------------+---------+

| 2 | Shi Potian | 22 | M | 1 | 7 | 20.5000 | 1 |

| 16 | Xu Zhu | 21 | M | 1 | NULL | 20.5000 | 1 |

| 3 | Xie Yanke | 53 | M | 2 | 16 | 36.0000 | 2 |

| 5 | Yu Yutong | 26 | M | 3 | 1 | 20.2500 | 3 |

| 17 | Lin Chong | 25 | M | 4 | NULL | 24.7500 | 4 |

| 4 | Ding Dian | 32 | M | 4 | 4 | 24.7500 | 4 |

| 11 | Yuan Chengzhi | 23 | M | 6 | NULL | 20.7500 | 6 |

| 21 | Huang Yueying | 22 | F | 6 | NULL | 20.7500 | 6 |

| 18 | Hua Rong | 23 | M | 7 | NULL | 19.6667 | 7 |

+-------+---------------+-----+--------+---------+-----------+-------------+---------+

9 rows in set (0.00 sec)

MariaDB [hellodb]>

再来一个：要疯了。这个我这样写总觉得有点复杂了。不知道大家有没有简略点的。

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

MariaDB [hellodb]> SELECT SQL_NO_CACHE * FROM (SELECT AVG(age) AS A,ClassID FROM students WHERE ClassID IN (SELEct ClassID FROM students GROUP BY ClassID HAVING COUNT(*) >= 3) GROUP BY ClassID) AS s,students WHERE students.ClassID=s.ClassID AND age > A;

+---------+---------+-------+---------------+-----+--------+---------+-----------+

+---------+---------+-------+---------------+-----+--------+---------+-----------+

| 20.5000 | 1 | 2 | Shi Potian | 22 | M | 1 | 7 |

| 36.0000 | 2 | 3 | Xie Yanke | 53 | M | 2 | 16 |

| 24.7500 | 4 | 4 | Ding Dian | 32 | M | 4 | 4 |

| 20.2500 | 3 | 5 | Yu Yutong | 26 | M | 3 | 1 |

| 20.7500 | 6 | 11 | Yuan Chengzhi | 23 | M | 6 | NULL |

| 20.5000 | 1 | 16 | Xu Zhu | 21 | M | 1 | NULL |

| 24.7500 | 4 | 17 | Lin Chong | 25 | M | 4 | NULL |

| 19.6667 | 7 | 18 | Hua Rong | 23 | M | 7 | NULL |

| 20.7500 | 6 | 21 | Huang Yueying | 22 | F | 6 | NULL |

+---------+---------+-------+---------------+-----+--------+---------+-----------+