Shulou(Shulou.com)06/02 Report--

Today, I will talk to you about how to talk about group by in MySQL. Many people may not know much about it. In order to make you understand better, the editor has summarized the following contents for you. I hope you can get something from this article.

1. Preface

The group by of MySQL is used to group the data of the query; in addition, MySQL provides a having clause to filter the data within the packet.

MySQL provides many select clause keywords

The order of them in the statement is as follows:

Clause function must / when to use select query to return data or expression is from specify query table No where specify row level filter No group by packet No / use having packet filter No when a pair of grouped data filter use order by to return data specify collation No limit specify the number of rows returned 2, prepare the user table

Prepare a user table with DDL and table data as follows

SET NAMES utf8mb4;SET FOREIGN_KEY_CHECKS = 0Mutual-Table structure for user-- DROP TABLE IF EXISTS `user` CREATE TABLE `user` (`id` bigint (20) NOT NULL AUTO_INCREMENT COMMENT 'key', `name` varchar (255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT 'user name', `nation`varchar (255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT 'nationality', `age`int (11) NULL DEFAULT NULL COMMENT 'age', `height`double NULL DEFAULT NULL COMMENT 'height', `sex`smallint (6) NULL DEFAULT NULL COMMENT 'gender' PRIMARY KEY (`id`) USING BTREE) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic -Records of user-- INSERT INTO `user`VALUES (1, 'plum', 'Han', 18,180,1); INSERT INTO `user`VALUES (2, 'Zhang San', 'Hui', 20,175,1) INSERT INTO `user`VALUES (3,'Li Si', 'Uygur', 45,168,0); INSERT INTO `user`VALUES (4, 'Wang Wu', 'Mongolian', 18,177,1); INSERT INTO `user`VALUES (5, 'Zhao Liu', 'Han', 16,184,0); INSERT INTO `user`VALUES (6, 'Tianqi', 'Uygur', 27,192,1)

The data in the user table is as follows:

Mysql > select * from user +-+-+ | id | name | nation | age | height | sex | +-+-+ | 1 | Li Ziqi | Han nationality | | 18 | 180 | 1 | 2 | Zhang San | Hui | 20 | 175 | 1 | 3 | Li Si | Uygur | 45 | 168 | 0 | 4 | Wang Wu | Mongolia | 18 | 177 | 1 | 5 | Zhao Liu | Han | 16 | 184 | 0 | | 6 | Tian Qi | Uygur | 27 | 192 | 1 | + | -+ 6 rows in set (0.00 sec) 2.1 group by rules

Before using group by, you need to understand the relevant rules used by group by.

The group by clause is placed after the where and before the order by clause

The having clause is placed after the group by and before the order by clause

Each column in the group by clause must be a retrieval column or a valid expression of select, and aggregation functions cannot be used

Expressions used in select that must appear in the group by clause and cannot use aliases

The data grouped by group by contains null values, which are grouped into groups

Group by clauses can be nested, and the nested groups are summarized on the final grouping.

2.2 group by usage

Demand:

Statistics on the number of users of different nationalities

Statement:

Mysql > select nation, count (*) from user group by nation +-+-+ | nation | count (*) | +-+-+ | Han nationality | 2 | | Hui | 1 | Uygur | 2 | | Mongolian | 1 | +-+-+ 4 rows in set (0.00 sec)

Group by can be used with where, but where cannot be filtered after group by. After using the where clause, the grouped data is the dataset filtered by the where clause.

Mysql > select nation, count (*) as nation_num from user where sex = 0 group by nation +-+-+ | nation | nation_num | +-+-+ | Uygur | 1 | | Han nationality | 1 | +-+-+ 2 rows in set (0.00 sec) 2.3 having use

The data that has been grouped into group by needs to be filtered again, so you must use the having clause. Using the where clause after the group by clause the MySQL server throws an exception

Mysql > select nation, count (*) as nation_num from user group by nation where nation = 'Han'; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'where nation =' Han'at line 1

At this point, you only need to replace the above where clause with the having clause. The having clause supports all where operators. Generally speaking, where the where clause can be used, only replace it with having can be used after the group by clause.

Vmysql > select nation, count (*) as nation_num from user group by nation having nation = 'Han'; +-+-+ | nation | nation_num | +-+-+ | Han | 2 | +-+-+ 1 row in set (0.00 sec) 2.4 order by and limit

The grouped data needs to be sorted and can be sorted using the order by,order by clause after the having clause.

Mysql > select nation, count (*) as nation_num from user group by nation having nation! = 'Han' order by nation_num desc +-+-+ | nation | nation_num | +-+-+ | Uygur | 2 | | Hui | 1 | Mongolian | 1 | +-+-+ 3 rows in set (0.00 sec)

For the output result you need to specify the number of rows returned, you can use the limit,limit clause at the end of the entire statement.

Mysql > select nation, count (*) as nation_num from user group by nation having nation! = 'Han' order by nation_num desc limit 2 +-+-+ | nation | nation_num | +-+-+ | Uygur | 2 | | Hui | 1 | +-+-+ 2 rows in set (0.00 sec) 2.5 with rollup

In the group by clause, WITH ROLLUP can perform the same statistics on the basis of grouped statistics (SUM,AVG,COUNT. )

For example, max ():

Mysql > select nation, max (height) as nation_num from user group by nation with rollup +-+-+ | nation | nation_num | +-+-+ | Hui nationality | 175 | Han nationality | 184 | | Uygur nationality | 192 | | Mongolian nationality | 177 | | NULL | 192 | +-+ -+ 5 rows in set (0.00 sec)

For example, avg ():

Mysql > select nation, avg (height) as nation_num from user group by nation with rollup +-+-+ | nation | nation_num | +-+-+ | Hui nationality | 175 | | Han nationality | 182 | | Uygur | 180 | | Mongolia | Family | 177 | | NULL | 179.333333333334 | +-+-+ 5 rows in set (0.00 sec)

For example, count ():

Mysql > select nation, count (*) as nation_num from user group by nation with rollup +-+-+ | nation | nation_num | +-+-+ | Hui | 1 | | Han nationality | 2 | Uygur | 2 | | Mongolian | 1 | | NULL | 6 | +- +-+ 5 rows in set (0.00 sec) read the above content Do you have any further understanding of how to talk about group by in MySQL? If you want to know more knowledge or related content, please follow the industry information channel, thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.