MySQL group and link usage, mysql group link

MySQL group and link usage, mysql group link

I. Deep Learning about group

Group by, group. As the name suggests, grouping data by what has the characteristics of each group.

1. Let's start with the simplest one:

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

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

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

When grouping data, most of the fields in the data will lose its meaning. You can think about it. Only one value is displayed in the same column of multiple data records, is this value useful?

Through thinking, it is not difficult to find that only the columns of by can be put in, and then the function operations of SQL, such as count (), sum ()...... (Include after by as the basis for grouping, include in aggregate function as the result)

Example: query the number of students in each school: (the value of the school is the id of the school)

SELECT a. college as college, COUNT (*) AS students FROM base_alumni a group by a. COLLEGE;

3. where, having, and group

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

Reading is not serious. In fact, they can all be used together, except that where can only be in front of group by and having can only be in the back of group.

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

Having is also the keyword of the filter condition. It serves the same purpose as where, but it filters the grouped data, that is, filtering the result set obtained after grouping.

Having is actually used to solve the problem of two where statements and separate them.

Example:

Query the number of students in each major of the 30100 School.

SELECT a. major as major, COUNT (*) AS students FROM base_alumni a WHERE a. COLLEGE = 30100 group by a. MAJOR;

　　　　　　

Query the number of students in each school, as long as the number of students is greater than 3.

SELECT a. college as college, COUNT (*) AS students FROM base_alumni a group by a. college having count (*)> 3;

Filtering execution sequence: ① filter the results first, ② group the results, and ③ filter the results of the grouping.

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

In fact, after the preceding where clause, the result to be grouped shows data that does not conform to the where clause. Of course, if no operation is performed, the operation result is expressed as 0 or null. This syntax is useless, no application scenarios can be found ~

2. Deep Learning connection

There are four types of connections: inner connection, full connection, left outer connection, and right outer connection.

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

1. Connection location

① Join tables and tables 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, which can be connected to other tables and further filtered.

2. Explanation of connection types

Think of tables as a set, and join as ing, then their results

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

Keywords:

Internal join: inner join; full join: cross join; left Outer join; right outer join.

Syntax:

Table a left join table B on a. Column 1 = B. Column 2

3. Connection usage

In the previous example of Learning group by, the result set has bugs.

For example, to query the number of students in each school: (the value of the school is the id of the school.) When there is no connection, the number of students with 0 cannot be displayed, because the current table does not have information about this school

Here we will perform the left join (left Outer Join ):

SELECT c. ID,. COLLEGE, COUNT (. COLLEGE) FROM (select id from dic_college) c left join (select college from base_alumni) a ON c. ID =. college group by c. ID

Here is a complete statement. I encountered many bumps before writing this statement.

Interpret it:

First, we connect the school Table to the alumni information table (student table) to the left.

Because we want a school, the school acts as the main table and puts the front of the left join c left join a ON...

Then we found that there are many fields, so we removed the extra fields, which not only facilitates our observation, but also improves the SQL Execution efficiency.

① Change the school table to only one field (select id from dic_college) c

② Change the student table to only one field (select college from base_alumni)

The query result is as follows:

SELECT * FROM (select id from dic_college) c left join (select college from base_alumni) a ON c. ID = a. COLLEGE

　　　　　　　　

In this case, the result set is grouped by group by c. ID and the query field must be modified.

In the result set above, c. ID and. COLLEGE is a one-to-one correspondence. At this time, the count (*) data is the total number of rows, because our primary table is a school table, so this data and count (c. ID.

But. the value of the row whose COLLEGE is empty is 1, which is not what we want, so we change count (*) to count (. COLLEGE.

　　　　　　　　

This is the correct answer to query the number of students in each school! Of course, the above is only the first few rows of data, followed by data

　　　　　　

4. I tested it ......

Left and right connections ......

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 these two statements are the same, and no difference is found between them.

A full connection is a cross connection ......

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.