Heim > Artikel > Datenbank > mysql SELECT查询_MySQL

mysql SELECT查询_MySQL

WBOYOriginal: 2016-05-30 17:09:571047Durchsuche

一、单表查询

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 |

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

9 rows in set (0.00 sec)

MariaDB [hellodb]>

有人说mysql中对子查询的优化不好，所以子查询也要少用。

3、联合查询：

把两个或多个查询语句的结果合并起来。UNION

这个简单，就是一个结果附加在了另一个结果的下面。叠加起来了。

SELECT Name,Age FROM teachers UNION SELECT Name,Age FROM students;

把后面的语句结果连接在前面结果的下面。

UNION 可以有多个，可以连接多个查询结果。

各个查询结果的字段数要相同。

Stellungnahme：

Der Inhalt dieses Artikels wird freiwillig von Internetnutzern beigesteuert und das Urheberrecht liegt beim ursprünglichen Autor. Diese Website übernimmt keine entsprechende rechtliche Verantwortung. Wenn Sie Inhalte finden, bei denen der Verdacht eines Plagiats oder einer Rechtsverletzung besteht, wenden Sie sich bitte an admin@php.cn

Vorheriger Artikel：SQL Server字段类型简介_MySQLNächster Artikel：SQLServer索引维护（1）系统常见的索引问题_MySQL

In Verbindung stehende Artikel

Mehr sehen