Detailed explanation of the use of MySQL group links

Grouping and linking in MYSQL are the two most commonly used functions when operating databases and data interaction. If these two items are handled well, the execution efficiency of MYSQL will be very high.

1. group by

As the name suggests, the data is grouped according to what principles and what characteristics each group has.

1. Let’s start with the simplest one:

select count(*) from tb1 group by tb1.sex;

Query the number of all data items and group them by gender. The result set obtained by this query has only one column, count(*).

2. Then let's analyze this grouping. What can we put between select and from?

After the data is grouped, most of the data fields will lose their meaning. Think about it, if the same column of multiple data only displays one value, whose value is it displayed for? Is this value useful?

By thinking about it, it is not difficult to find that only the columns of by can be put in, and then there are sql function operations, such as count(), sum()... (included after by as the basis for grouping, included in the aggregate function as the result)

Example: Query the number of students in each college: (the value of college is the college id)

SELECT a.COLLEGE AS college,COUNT(*) AS number of students FROM base_alumni a GROUP BY a.COLLEGE;

3. Where, having, and group by are used together

When I first learned group by, I fell into a misunderstanding, that is, group by cannot be used with where, only having can be used...

You didn't read the book carefully. In fact, they can be used together, but where can only be before group by, and having can only be after group by.

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

Having is also a keyword for filtering conditions. Its function is the same as where, but it filters the grouped data, that is, it filters the result set obtained after grouping.

I think the purpose of having is to solve the problem of two where clauses in one statement and distinguish them.

example:

Query how many students are there in each major of 30100 college.

SELECT a.MAJOR AS major, COUNT(*) AS number of students FROM base_alumni a WHERE a.COLLEGE = 30100 GROUP BY a.MAJOR;

Query how many students there 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 HAVING COUNT(*)>3;

Filter execution order: ① Filter * first, ② Group the filtered results, ③ Filter the grouped results

4. The use of Group By All, hahahaha, I often check online, and I decided to eliminate this syntax~

In fact, after the previous where, you want the grouped results to display the data that does not meet the where. Of course, no calculation is performed, and the calculation result is represented by 0 or null. I feel that this syntax is useless and I can't think of an application scenario~

2. Deep Learning Connections

There are 4 types of connections: inner connection, full connection, left outer connection, right outer connection

1. Where the connection appears

① Between from and where, connect tables

② Between where and having, having is to filter the result set of group by, that is, to treat the result set of group by as a table, and then connect it with other tables for further filtering

2. Interpretation of connection type

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

Inner join: one-to-one mapping; full join: Cartesian product; left outer join: one-to-one mapping + left table corresponds to null in the right table; right outer join: one-to-one mapping + right table corresponds to null in the left table.

Keywords:

Inner join: inner join; full join: cross join; left outer join: left join; right outer join: right join.

grammar:

Table a left join table b on a.column 1 = b.column 2

3. Use of connection

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

Example: Query the number of students in each college: (the value of the college is the college ID). When there is no connection, the college with a student number of 0 will not be displayed because there is no information about this college in the current table.

So let's do a left join (left outer join) here:

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 have a complete sentence here. I had a lot of trouble writing this sentence.

Read it:

Let's first do a left join of the college table and the alumni information table (student table)

Because we want the college, so the college is the main table and is placed before the left join c LEFT JOIN a ON ...

Then we found that there were many fields, so we removed the redundant fields, which not only made it easier for us to observe, but also improved the execution efficiency of SQL.

①Change the college table to have only one field (SELECT ID FROM dic_college)

②Change the student table to have only one field (SELECT COLLEGE FROM base_alumni) a

At this time, the query result is 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 time, group the result set: GROUP BY c.ID, and change the query field

In the result set above, c.ID and a.COLLEGE correspond one to one. At this time, the data of count(*) is the total number of rows. Because our main table is the college table, this data is the same as the data of count(c.ID).

However, the values ​​in the rows where a.COLLEGE is empty are all 1, which is not what we want, so we change count(*) to count(a.COLLEGE), and the data will come out.

This is the correct answer to query the number of students in each college among all colleges! Of course, the screenshot above is only the first few rows of data, and there is more data behind it.

4. After I tested it

Left and right joins...

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 two statements produce the same result; it detects no other difference between them.

Full connection is a cross connection, and no connection is used...

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 these two statements.

The above is all the content of this article on the in-depth explanation of MYSQL grouping and linking. If you still have any questions, you can discuss them in the comment area below.