MySql multi-Table query, MySql

MySql multi-Table query, MySql

Multi-Table query must use multi-Table Association.

Multi-table join is divided into three types:

A: Internal join or inner join

B: external connections are classified into two types: left Outer join and right outer join.

C: cross join

The following example shows two tables.

A middle table of a student's teacher

 

 

 

I. Internal link application scenarios

 

 

In this case, the internal connection is called to obtain the data of a B that meets the AB condition at the same time.

The code is:

SELECT * FROM stu_tea st JOIN student s ON st. student = s. name

The result is:

 

 

It is clear from the table that the information in the studnet table will be spliced behind the association according to the specified rules ("student Field" = name field in the student table in the middle table, the two students, Chen Jian and Li Tao, are missing because they do not exist in both tables.

 

If you want to display only one row for the students and names in the result table repeatedly, you can perform the following operations on the Code:

SELECT st. 'studeny', st. 'instructor ', s. 'birth' FROM stu_tea st JOIN student s ON st. student = s. name

The result is as follows:

You can also process the s. birth culture, and add an alias to the end to display:

SELECT st. 'studeny', st. 'instructor ', s. 'birth'' birthday' FROM stu_tea st JOIN student s ON st. student = s. name

The result is as follows:

Note: The Chinese field in the above Code can be added without quotation marks. The system generally adds single quotation marks by default;

 

II. Application scenarios of external links

Left Outer Join

In this scenario, we obtain all data of A and some data that meets certain conditions of B.

 

Code: SELECT * FROM stu_tea st left join student s ON st. 'Students' = s. 'name'

Result:

From the table, we can see that the left Outer Join uses the left middle table as the template, and the student table is followed by the middle mark. If no data is filled with null, the data in the student table is not displayed, but not in the middle table.

You can use the where clause to further filter the above results.

For example, to remove data without student information: SELECT * FROM stu_tea st left join student s ON st. 'student '= s. 'name' WHERE s. 'name' IS NOT NULL

The result is:

We also query which student information IS removed: SELECT * FROM stu_tea st left join student s ON st. 'Students '= s. 'name' WHERE s. 'name' IS NULL

The result is:

Note: MySQL can only use is and cannot use "="

Outer right connection

In this scenario, we obtain all data of B and some data that A meets certain conditions.

The right external connection is the opposite of the left external connection.

 

 

Iii. Cross join

In practice, there is another situation where we want to get the arrangement and combination of A and B records, that is, the flute product, which is not easy to use the collection and metadata representation. Cross join is required

Code: SELECT * FROM stu_tea st cross join student

The result is:

 

It can be found that the student table has a total of five rows of data, and the stu_tea table has 12 rows of data, 60 = 12*5;

This code can also be written as SELECT * FROM stu_tea st JOIN student

 

Note:

1. The join table uses a comma, which is interpreted as a cross join;

That is, SELECT * FROM stu_tea st, student SELECT * FROM stu_tea st JOIN student SELECT * FROM stu_tea st cross join student is equivalent.

2. Generally, the on condition must be added for inner connections. If this condition is not added, it will be interpreted as a cross connection.

3. Generally, the where condition is added after a cross join (or cross join), and can be written as on