MySQL Knowledge Learning-Day Thirteen: Grouping Data

Preface

It's another new week, keep working hard~

Data grouping

How to understand data grouping? As a simple example, a school has a list of students in the third grade, and there are three classes in the entire grade, so that we can group the data by class.

If you personally understand, grouping is to separate data with a certain value.

Create group

So how to group? Grouping is established in the GROUP BY clause of the SELECT statement.

Look at the following example, for the following table (student)

Insert picture description here


we group the above table.

Insert picture description here


The group by clause must be followed by the column name, it will combine the records with the same value in the column into a group.

Insert picture description here


The group by clause can contain any number of columns. It will group all the following columns with the same value. For example, look at the following example

Insert picture description here


Insert picture description here


because only the first and second records have the class column and name value They are all equal, all of them are in the same group, and the rest of the records are in one group.

Insert picture description here

Filter group

In the above example, if we group by class, we will get three sets of data, so what should I do if I only want to get the total number of people in class one and class two? At this time, the grouping needs to be filtered. That is to exclude certain groups according to a certain standard.

At first glance, it is relatively simple. Can I just add a WHERE clause directly? The answer is no, why? Because WHERE filtering specifies rows instead of grouping . In fact, WHERE has no concept of grouping .

We use the HAVING clause to filter the grouped data, which means that HAVING filters the grouping , and its usage is similar to that of the WHERE clause.

In fact, all types of WHERE clauses learned so far can be replaced by HAVING. The only difference is that WHERE filters rows, while HAVING filters groups

Insert picture description here


So how to filter packets? Let's look at the following example. The

Insert picture description here


above example filters out the group of class 3.

Insert picture description here


Earlier we mentioned that the HAVING clause is after the WHERE clause. For MySQL, the WHERE clause is executed first, and the HAVING clause

Insert picture description here


is executed. Why is the result like this? The reason is that the WHERE clause is executed first, and the table after execution is actually

Insert picture description here


grouping the above tables, so the final result is only one.

Grouping and sorting

We now know that grouping uses the GROUP BY clause, and sorting is the ORDER BY clause.

Although GROUP BY and ORDER BY often accomplish the same job, they are very different.

Insert picture description here


Insert picture description here

SELECT clause order

Up to this point, we have learned more clauses, today we will roughly summarize the order of clauses

Insert picture description here


Insert picture description here

Today’s content is relatively simple, but very important, you can practice more by yourself~