MySQL数据库的GROUP BY语句是一种用于对数据进行分组和汇总的强大工具。它通常与SELECT语句一起使用,以根据一个或多个列对结果进行分组。以下是关于MySQL数据库GROUP BY语句的详细介绍,包括其语法、案例以及注意事项。
GROUP BY语句的基本语法如下:
SELECT 列1, 列2, ... FROM 表名 GROUP BY 列1, 列2, ...
在上述语法中,你需要将"列1, 列2, ..."替换为你希望根据其进行分组的列名。你可以指定一个或多个列,以便根据这些列的值将数据分组。
让我们通过一个具体的案例来进一步了解GROUP BY语句的用法。假设我们有一个名为"student"的表,其中包含以下列:name(学生姓名)、sex(学生性别)和score(学生成绩)。
这是详细信息得内容。
-- ---------------------------- -- Table structure for student -- ---------------------------- DROP TABLE IF EXISTS `student`; CREATE TABLE `student` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL, `sex` varchar(11) COLLATE utf8_unicode_ci DEFAULT NULL, `score` int(11) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM AUTO_INCREMENT=6 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; -- ---------------------------- -- Records of student -- ---------------------------- INSERT INTO `student` VALUES ('1', '张三名', '男', '80'); INSERT INTO `student` VALUES ('2', '李四浩', '男', '90'); INSERT INTO `student` VALUES ('3', '王五期', '女', '70'); INSERT INTO `student` VALUES ('4', '赵六三', '男', '90'); INSERT INTO `student` VALUES ('5', '三七熊', '女', '60');
原始数据
mysql> select * from student; +----+-----------+------+-------+ | id | name | sex | score | +----+-----------+------+-------+ | 1 | 张三名 | 男 | 80 | | 2 | 李四浩 | 男 | 90 | | 3 | 王五期 | 女 | 70 | | 4 | 赵六三 | 男 | 90 | | 5 | 三七熊 | 女 | 60 | +----+-----------+------+-------+
单独使用,我们想对性别进行分组
mysql> SELECT sex from student GROUP BY sex; +------+ | sex | +------+ | 女 | | 男 | +------+
如果想显示其他字段(非聚集函数字段和分组字段)的话则需要去除sql_mode的ONLY_FULL_GROUP_BY配置。否则报错。
mysql> SELECT name,sex from student GROUP BY sex; ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'test.student.name' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
查询sql_mode是否开启ONLY_FULL_GROUP_BY
显示有ONLY_FULL_GROUP_BY是开启
mysql> SELECT @@global.sql_mode; +-------------------------------------------------------------------------------------------------------------------------------------------+ | @@global.sql_mode | +-------------------------------------------------------------------------------------------------------------------------------------------+ | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION | +-------------------------------------------------------------------------------------------------------------------------------------------+
修改my.ini文件添加sql_mode配置
sql_mode="STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
使用 GROUP BY 关键字时,查询结果会只显示每个分组的第一条记录。
mysql> SELECT name,sex from student GROUP BY sex; +-----------+------+ | name | sex | +-----------+------+ | 王五期 | 女 | | 张三名 | 男 | +-----------+------+
GROUP BY 关键字可以和 GROUP_CONCAT() 函数一起使用。GROUP_CONCAT() 函数会把每个分组的字段值都显示出来。
mysql> SELECT GROUP_CONCAT(name),sex from student GROUP BY sex; +-------------------------------+------+ | GROUP_CONCAT(name) | sex | +-------------------------------+------+ | 王五期,三七熊 | 女 | | 张三名,李四浩,赵六三 | 男 | +-------------------------------+------+
mysql> SELECT id,GROUP_CONCAT(name),sex,score from student GROUP BY sex,score; +----+---------------------+------+-------+ | id | GROUP_CONCAT(name) | sex | score | +----+---------------------+------+-------+ | 5 | 三七熊 | 女 | 60 | | 3 | 王五期 | 女 | 70 | | 1 | 张三名 | 男 | 80 | | 2 | 李四浩,赵六三 | 男 | 90 | +----+---------------------+------+-------+
上面实例在分组过程中,先按照 sex字段进行分组,当 sex字段值相等时,再把 sex字段值相等的记录按照 score字段进行分组。
多个字段分组查询时,会先按照第一个字段进行分组。如果第一个字段中有相同的值,MySQL 才会按照第二个字段进行分组。如果第一个字段中的数据都是唯一的,那么 MySQL 将不再对第二个字段进行分组。
在数据统计时,GROUP BY 关键字经常和聚合函数一起使用。聚合函数包括 COUNT(),SUM(),AVG(),MAX() 和 MIN()等。其中,COUNT() 用来统计记录的条数。
mysql> SELECT sex,COUNT(sex) as sexcount from student GROUP BY sex; +------+----------+ | sex | sexcount | +------+----------+ | 女 | 2 | | 男 | 3 | +------+----------+
WITH POLLUP 关键字用来在所有记录的最后加上一条记录,这条记录是上面所有记录的总和,即统计记录数量。
mysql> SELECT sex,COUNT(sex) as sexcount from student GROUP BY sex WITH ROLLUP; +------+----------+ | sex | sexcount | +------+----------+ | 女 | 2 | | 男 | 3 | | NULL | 5 | +------+----------+
在使用GROUP BY语句时,有几个关键的注意事项需要考虑:
列的顺序:在SELECT语句中,除了聚合函数之外,其他列必须出现在GROUP BY子句中。这意味着,如果你在查询中使用了多个列,那么这些列的顺序必须与GROUP BY子句中的顺序一致。
NULL值处理:当使用GROUP BY时,聚合函数(如SUM、COUNT等)将忽略NULL值。这意味着,如果某个客户的订单总金额为NULL,则该客户不会被包含在结果集中。
聚合函数的使用:在SELECT语句中,你可以使用各种聚合函数来计算分组后的数据。这些函数包括SUM、COUNT、AVG、MAX和MIN等。根据你的需求选择合适的函数。
HAVING子句:HAVING子句允许你在GROUP BY之后对分组结果进行筛选。它类似于WHERE子句,但用于对分组结果进行筛选。在使用HAVING子句时,要注意与GROUP BY子句的结合使用。
ORDER BY子句:如果你需要在结果中指定排序顺序,可以使用ORDER BY子句。但是,请注意,ORDER BY子句应在GROUP BY子句之后使用,以确保先进行分组计算,然后再进行排序。
子查询和JOIN:有时,你可能会在GROUP BY语句中使用子查询或JOIN来获取更复杂的结果。在这种情况下,确保正确地组合和使用这些技术以达到你的目标。
总之,MySQL数据库的GROUP BY语句是一种强大的工具,可用于对数据进行分组和汇总。通过了解其语法、案例以及注意事项,你可以有效地利用GROUP BY语句来解决各种数据问题。希望这篇文章能对你有所帮助!