【MySQL】select聚合查询用法（详解）

最新推荐文章于 2024-04-09 23:48:19 发布

zbossz

最新推荐文章于 2024-04-09 23:48:19 发布

阅读量432

点赞数

分类专栏： MYSQL 文章标签： mysql sql 数据库

本文链接：https://blog.csdn.net/weixin_54130714/article/details/123127420

版权

MYSQL 专栏收录该内容

13 篇文章 0 订阅

订阅专栏

本文介绍了如何在MySQL中使用聚合函数如max(), min(), avg(), count()处理数字数据，同时探讨了这些函数在处理字符串（如姓名）时的局限性。通过实例演示，读者将理解如何正确应用这些查询并避免常见问题。

摘要由CSDN通过智能技术生成

聚合查询

聚合查询有max，min，avg，count等方法。
其针对的必须是数字，如果是null的话，不会被统计的。
我们举个例子吧。

mysql> use z1;
Database changed
mysql> show tables;
+--------------+
| Tables_in_z1 |
+--------------+
| class        |
| customer     |
| exam         |
| gender       |
| goods        |
| purchase     |
| student      |
+--------------+
7 rows in set (0.01 sec)

mysql> select * from exam;
+------+-------+---------+------+---------+
| id   | name  | chinese | math | english |
+------+-------+---------+------+---------+
|    1 | z1    |    23.0 | 99.0 |    23.0 |
|    2 | z2    |    49.0 | 54.0 |    65.0 |
|    3 | z3    |    87.0 | 54.0 |    34.0 |
|    4 | z4    |    58.0 | 80.0 |    90.0 |
|    5 | z5    |    78.0 | 88.0 |    98.0 |
|    6 | z6    |    66.0 | 60.0 |   100.0 |
|    7 | z7    |    13.0 | 46.0 |    70.0 |
|    8 | z8    |    87.0 | 67.0 |    90.0 |
|    9 | z9    |    45.0 | 23.0 |    54.0 |
|   10 | q1    |    45.0 | 76.0 |    54.0 |
|   11 | q2    |    78.0 | 87.0 |    34.0 |
|   12 | q3    |    99.0 | 88.0 |    77.0 |
|   13 | r1    |    76.0 | 32.0 |    54.0 |
|   14 | r2    |    43.0 | 65.0 |    75.0 |
|   15 | v1    |    34.0 | 34.0 |    43.0 |
|   16 | v2    |    76.0 | 45.0 |    87.0 |
|   17 | g1    |    32.0 | 45.0 |    76.0 |
|   18 | g2434 |    21.0 | 34.0 |    45.0 |
|   19 | 中    |    55.0 | 32.0 |    99.0 |
|   20 | y1    |    56.0 | 76.0 |    76.0 |
|   21 | de    |    87.0 | 89.0 |    98.0 |
|   22 | fe    |    NULL | NULL |    NULL |
|   23 | deew  |    11.0 | NULL |    NULL |
|   24 | fret  |    87.0 | NULL |    87.0 |
+------+-------+---------+------+---------+
24 rows in set (0.00 sec)

mysql> select count(id) from exam;
+-----------+
| count(id) |
+-----------+
|        24 |
+-----------+
1 row in set (0.00 sec)

mysql> select count(math) from exam;
+-------------+
| count(math) |
+-------------+
|          21 |
+-------------+
1 row in set (0.00 sec)

mysql> select sum(math) from exam;
+-----------+
| sum(math) |
+-----------+
|    1274.0 |
+-----------+
1 row in set (0.00 sec)

mysql> select min(math) from exam;
+-----------+
| min(math) |
+-----------+
|      23.0 |
+-----------+
1 row in set (0.00 sec)

mysql> select max(math) from exam;
+-----------+
| max(math) |
+-----------+
|      99.0 |
+-----------+
1 row in set (0.00 sec)

mysql> select avg(math) from exam;
+-----------+
| avg(math) |
+-----------+
|  60.66667 |
+-----------+
1 row in set (0.00 sec)

最好是针对数字的，但是如果针对字符串也未尝不可，但是有的是不支持的，就比如说avg，更何况针对汉字来求min，max，avg是没有意义的。

mysql> select count(name) from exam;
+-------------+
| count(name) |
+-------------+
|          24 |
+-------------+
1 row in set (0.00 sec)

mysql> select min(name) from exam;
+-----------+
| min(name) |
+-----------+
| de        |
+-----------+
1 row in set (0.00 sec)

mysql> select max(name) from exam;
+-----------+
| max(name) |
+-----------+
| 中        |
+-----------+
1 row in set (0.00 sec)

mysql> select avg(name) from exam;
+-----------+
| avg(name) |
+-----------+
|         0 |
+-----------+
1 row in set, 4 warnings (0.00 sec)