It's another new week, keep working hard~
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.
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)
we group the above table.
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.
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
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.
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
So how to filter packets? Let's look at the following example. The
above example filters out the group of class 3.
Earlier we mentioned that the HAVING clause is after the WHERE clause. For MySQL, the WHERE clause is executed first, and the HAVING clause
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
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.
SELECT clause order
Up to this point, we have learned more clauses, today we will roughly summarize the order of clauses
Today’s content is relatively simple, but very important, you can practice more by yourself~