MySQL Group query tutorial

1. Grouping

Grouping means dividing a "dataset" into several "small areas" and then processing data for several small regions.

2. Characteristics of the Grouping

1.) The meaning of GROUP by: Group The results of the query into 1 or more fields with the same field values as a group

2.) Group by can be used for a single field grouping or for multiple field groupings

3 usage of groupings

Select field name from Data table Group by Group field name

4 The use of grouping is equivalent to a field in accordance with a requirement to overlap, this time using the normal method can not view the contents of each group, need to Group_concat (Group field name) to query

Select Group field name group_concat (field name) from data table Group by Group field name

5. When you need a query for some requirements, you need to add a constraint to the group query, you cannot use where to filter the condition, you should use having to filter the condition

Select Gender,count (*) from students group by gender have Count (*) >2;

　　

　

6. When you need to summarize the grouping of data tables, you can use the WITH Rollup method (the full data table is summarized by default when not in use with grouping)

Select Gender,count (*) from students group by gender with rollup;

Summarize:

1. The GROUP BY keyword can group data based on 1 or more fields
2. The Group_concat function is to stitch the specified fields of each member in each group into one row
3. When used in conjunction with group BY, the aggregate function is the object of each grouping
4. Having is the conditional filtering of the grouped results
5. With rollup the end of the grouping result is a new line to complete the summary display.

MySQL Group query tutorial