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

This article mainly gives you a brief introduction to the usage of the group by command of the introduction to MySQL. You can look up the relevant professional terms on the Internet or find some related books to supplement it. We will not dabble here, let's go straight to the topic, and hope that the detailed introduction of the usage of the group by command of the introduction to MySQL can bring you some practical help.

1.group by command usage

The conventional usage of group by is to cooperate with aggregate function and use grouping information for statistics. It is common to filter data after analysis with aggregate functions such as max, and filter with having.

Common aggregate functions are as follows:

Suppose the existing database tables are as follows: table user_info,id primary key, user_id unique key

CREATE TABLE `user_ info` (

`id`INT (11) NOT NULL AUTO_INCREMENT COMMENT 'primary key id'

`user_ id`VARCHAR (50) NOT NULL DEFAULT''COMMENT' user number'

`grade`VARCHAR (50) NOT NULL DEFAULT''COMMENT' Grade'

`class`VARCHAR (50) NOT NULL DEFAULT''COMMENT' Class'

PRIMARY KEY (`id`), UNIQUE INDEX `uniq_user_ id` (`user_ id`)

)

ENGINE=InnoDB

data

INSERT INTO `grade` (`id`, `user_ id`, `grade`, `class`) VALUES (10, '10230,' user_,'B')

INSERT INTO `grade` (`id`, `user_ id`, `grade`, `class`) VALUES (9, '102299,' user_,'a')

INSERT INTO `grade` (`id`, `user_ id`, `grade`, `class`) VALUES (8, '10228,' user_,'b')

INSERT INTO `grade` (`id`, `user_ id`, `grade`, `class`) VALUES (7, '10227,' Bread,'b')

INSERT INTO `grade` (`id`, `user_ id`, `grade`, `class`) VALUES (6, '10226,' baked,'a')

INSERT INTO `grade` (`id`, `user_ id`, `grade`, `class`) VALUES (5, '10225,' baked,'a')

INSERT INTO `grade` (`id`, `user_ id`, `grade`, `class`) VALUES (4, '10224,' Aids,'b')

INSERT INTO `grade` (`id`, `user_ id`, `grade`, `class`) VALUES (3, '10223,' Aids,'b')

INSERT INTO `grade` (`id`, `user_ id`, `grade`, `class`) VALUES (2, '10222,' Aids,'a`)

INSERT INTO `grade` (`id`, `user_ id`, `grade`, `class`) VALUES (1, '10221,' A','a')

Aggregate function max

Select max (user_id), grade from user_info group by grade

The meaning of this sql is clear. It groups the data according to the grade field and queries the largest user_id of each group as well as the current group content. Note that the grouping condition here is grade, and the non-aggregate condition of the query is also grade. There is no conflict here.

Results:

Having

Select max (user_id), grade from user_info group by grade having grade >'A'

# this sql is basically the same as in the example above, but it is followed by having filtering conditions. Filter out those whose grade does not satisfy'> A'. Note that the grouping condition here is grade, and the non-aggregate condition of the query is also grade. There is no conflict here.

Results:

The difference between Having and Where

The function of the where clause is to remove the rows that do not meet the where condition before grouping the query results, that is, to filter the data before grouping, the where condition cannot contain aggregate functions, and the where condition is used to filter out specific rows.

The role of the having clause is to filter groups that meet the criteria, that is, to filter data after grouping, which often contains aggregate functions, to filter out specific groups using having conditions, or to use multiple grouping criteria for grouping.

Unconventional usage of 2.group by

Select max (user_id), id,grade from user_info group by grade

# shows the user_id with the largest value in the grade group, displays id, displays grade, and is extracted from the user_ infon table.

Results:

The result of this sql is worth discussing. Unlike the above example, the query condition has an additional id column. After the data is grouped according to grade, the grade column is the same, and the max (user_id) is unique according to the data. How is the value of the id column taken? Look at the results of the above data

Corollary: id is the first match of physical memory.

Whether or not it is necessary to continue to discuss.

Modify data

Modify id according to the above data results, change id=1 to id=99, and execute sql. The conclusion is:

Obviously, the result is different from that of the above example. The first data id becomes 99, and as a result, the id of the first data has changed from 1 to 2. It shows that the value of the non-aggregate condition field id has nothing to do with the time when the data is written, because the record of id=1 exists before id=2, and the modified data only modifies the content of the data. Combined with mysql's data storage theory, because id is the primary key, the data is filtered after the retrieval is sorted by the primary key.

Corollary: the selection of the id field is the first item that matches the retrieval data stored in mysql.

After changing id to 1, the original result is restored, and the above inference can not be overturned.

Change the query criteria:

Select max (user_id), user_id,id,grade from user_info group by grade

# display the user_id with the largest value in the grade group, display user_id, display id, display grade, and extract from the user_ infon table

The result is:

After changing the data user_id to 10999

Execution

Select max (user_id), user_id,id,grade from user_info group by grade

The result is:

After modifying the user_id, the query data entry is not changed, so it is concluded that the modification of the unique key can not affect the query matching entry rules, so the entry rule is still the first matching item, namely id=1.

This is the detailed introduction to the usage of the group by command for the introduction to MySQL. If you want to know about other related issues, you can continue to pay attention to our industry information. Our section will capture some industry news and professional knowledge to share with you every day.

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.