Shulou(Shulou.com)06/01 Report--

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

1. Group by, grouping

As the name implies, what are the characteristics of each group when you group the data into groups?

1. Let's start with the simplest one:

Select count (*) from tb1 group by tb1.sex

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

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

When the data is grouped, most of the fields of the data will lose the meaning of its existence. If you think about it, the same column of multiple data shows only one value. Who does it show? is this value useful?

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

Example: inquire how many students there are in each college: (the value of the college is the id of the college)

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

3. Where,having, used in conjunction with group by

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.

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

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

Having, which is also a filter condition, has the same keyword function as where, but it filters the grouped data, that is, the result set obtained by the grouping.

In fact, I think the reason for the emergence of having is to solve the problem of two where in one sentence and to distinguish them.

Example:

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

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

Find out 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, number of COUNT (*) AS students FROM base_alumni a GROUP BY a.COLLEGE HAVING COUNT (*) > 3

Filter execution order: ① first filters *, ② groups the screening results, and ③ filters the grouping results.

4. The use of Group By All, , I often look it up on the Internet, and I decide to eliminate this grammar.

In fact, after the previous where, you want the grouped results to display data that do not conform to the where. Of course, do not do the operation, and the operation results are expressed as 0 or null. I feel that this syntax is useless, and I can't think of an application scenario.

Second, in-depth study connection

There are four kinds of connections, inner connection, full connection, left outer connection, right outer connection.

1. Where the connection appears

Make the connection between ① from and where

Between ② where and having, having filters the result set of group by, that is, the result set of group by is used as a table, and then it can be joined with other tables for further filtering.

2. Interpretation of connection types.

If you think of a table as a collection and a join as a mapping, then their results

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

Keywords:

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

Syntax:

Table a left join. Table b on a. Column 1 = b. Column 2

3. The use of connection

In the previous example of learning group by, there is a bug in the result set.

Ex.: query the number of students in each college: (the value of the college is the id of the college). When there is no connection, the number of colleges with a number of 0 cannot be displayed, because there is no information about this college in the current table.

So let's do a left connection here (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) an ON c.ID = a.COLLEGE GROUP BY c.ID

I have a complete sentence here. I encountered a lot of bumps and bumps before I wrote this sentence.

Interpret it:

Let's first make a left link between the college table and the alumni information table (student table).

Because we want the college, so the college as the main table, put the c LEFT JOIN an ON in front of the left join.

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

① changes the college table to only one field (SELECT ID FROM dic_college) c

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

At this point, the query result looks like this

SELECT * FROM (SELECT ID FROM dic_college) c LEFT JOIN (SELECT COLLEGE FROM base_alumni) an ON c.ID = a.COLLEGE

At this point, group the result set: GROUP BY c.ID, and the query field needs to be changed.

In the above result set, 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, so this data is the same as the data of count (c.ID).

But the data median value for rows with empty a.COLLEGE is 1, which is not what we want, so we change count (*) to count (a.COLLEGE) so that the data comes out.

This is the correct answer to inquire about the number of students in each college in all colleges! Of course, the screenshot above is only the first few lines of the data, and there are data behind it.

4. I tested it.

Left connection and right connection.

SELECT * FROM a LEFT JOIN b ON b.ID = a.FK_ID

SELECT * FROM b RIGHT JOIN an ON b.ID = a.FK_ID

The result of the two statements is the same, and there is no difference between the two.

A full connection is a cross connection, and no connection is used.

SELECT * FROM cjora WHERE c.ID = a.FK_ID

SELECT * FROM c CROSS JOIN an ON c.ID = a.FK_ID

There is no difference between the two statements.

The above is the whole content of this article about MYSQL grouping and links in detail. If you have anything you don't understand, you can discuss it in the message area below.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.