MySQL grouping, use of links

Group BY, grouping, as the name implies, the data according to what to group, each group has what characteristics.

1, we start from the simplest:

Select COUNT (*) from TB1 GROUP by Tb1.sex;

Query the number of bars for all data, grouped by gender. This results in a single column count (*) for the result set that is queried.

2, then we analyze, this group, we can put something between select and from?

When the data is grouped, most fields of the data will lose its meaning, and if you think about the same column of multiple data, only one value is displayed, then who is it, and is this value useful?

By thinking, it is not difficult to find that only by the columns can be put in, and then the SQL function operation, such as count (), SUM () ... (included as the basis for grouping after by, included in the aggregate function as the result)

Example: How many people are queried for each college student: (The college's value is the college's ID)

SELECT A.college as Academy, COUNT (*) as the number of students from Base_alumni a GROUP by A.college;

3, Where,having, and group by joint use

When I first learned group BY, I was caught in the misconception that group by cannot be used with where, only with having ...

Reading is not serious ah, in fact, they can be used together, but where only in the group by the front, having only after group by.

Where, filter the keyword of the condition, but it can only filter the data before group by;

Having, is also the filter of the keyword function and where is the same, but it filters the data after grouping, that is, the result set after grouping to filter filtering.

Having a having had in fact I think that is to solve a statement two where problem, distinguish them

Cases:

Find out how many people are in each of the 30100 college students.

SELECT A.major as Professional, COUNT (*) as the number of students from Base_alumni a WHERE A.college = 30100 GROUP by a.major;

　　　　　　

Find out how many students are in each college, and as long as the number of students is greater than 3.

SELECT A.college as College, COUNT (*) as number of students from Base_alumni a GROUP by A.college have COUNT (*) >3;

Filter execution Order: ① Filter the filter first, ② the filtered results, ③ Filter The results of the grouping

4, Group by the use of all, haha haha, often online access, I decided to eliminate this grammar ~

In fact, the previous where you want to group the results of the display does not conform to where the data, of course, do not do the operation, the results of the operation with 0 or NULL, the feeling that this syntax is useless, I can't think of the application scenario ~

Second, in-depth learning connection

4 kinds of connection, inner connection, full connection, left outer connection, right outer connection

Https://www.cnblogs.com/afirefly/archive/2010/10/08/1845906.html

1. Where the connection appears

Between ①from and where, make table and table connection

Between ②where and having, the having is to filter the result set of group by, that is, the result set of group by as a table, then can be connected with other tables, and then further filter

2. Interpretation of connection type

Consider a table as a set, and the connection as a map, then their results

Inner connection: one by one mapping; Full connection: Cartesian product, left OUTER join: one by one map + left table corresponding to right table null; right outer join: one by one map + null for right table for left table.

Key words:

Inner connection: inner join; Full connection: Cross join, LEFT OUTER join: Right join;

Grammar:

Table A LEFT JOIN table B on a. Column 1 = B. Column 2

3, the use of the connection

In the previous example of group BY, there was a bug in the result set.

Example: Query the number of students in each college: (The Institute's value is the Institute's ID), when there is no connection, the number of the college is 0 is not displayed, because the current table does not have the information of the Institute

So let's do this here. Left connection (left outer connection):

Select C.id, A.college, COUNT (A.college) from (select ID from Dic_college) C left JOIN (select COLLEGE from Base_alumni) A on c.id = A.college GROUP by c.id

I am here to be a complete statement. I met a lot of bumps before I wrote this statement.

Read it:

Let's start by connecting the college and alumni information sheets (student tables) to the left.

Because we want the college, so the college as the main table, put the left join in front of the C to join a on ...

Then we found that there were a lot of fields, so we removed the extra fields so that we could both observe and improve the efficiency of SQL execution.

① the college table into only one field (SELECT ID from Dic_college) c

② The student table into only one field (SELECT COLLEGE from Base_alumni) a

At this point, the query results are like this

SELECT * FROM (select ID from Dic_college) C left JOIN (select College from Base_alumni) A on c.id = A.college

　　　　　　　　

At this point, the result set is grouped: Group by c.id, and the query fields are changed

In the result set above, c.id and A.college are one by one corresponding, at this point, the data of Count (*) is the total number of rows, because our main table is the college table, so this data and count (c.id) data is the same.

But a. The value in the data for COLLEGE is 1, which is not what we want, so we change the count (*) to count (A.college) so that the data comes out.

　　　　　　　　

This is the correct answer to the number of students in every college in all colleges! Of course, the top is just the first few lines of data, followed by data

　　　　　　

4, after I tested a bit ...

Left JOIN and right connection ...

SELECT * from a left JOIN b on b.id = a.fk_id;

SELECT * from B right JOIN a on b.id = a.fk_id;

The results of the two statements are the same, and neither of them finds any other difference.

All connections are cross-connected, and do not use connections ...

SELECT * from c,a WHERE c.id = a.fk_id;

SELECT * from C cross JOIN A on c.id = a.fk_id;

There is no difference between the two statements.

MySQL grouping, use of links