Multiple tables associated query of MySQL

Time:2019-11-25

1. internal connection

Rule: return common records of two tables

Syntax:

-- grammar 1
Select * from table 1 inner table 2 on table 1. Public field = Table 2. Public field
-- grammar two
Select * from table 1, table 2 where table 1. Public field = Table 2. Public field

          Supplement:

--How to realize three table query
Select * from table 1 inner table 2 on table 1. Common field = Table 2. Common field inner table 3 on table 2. Common field = Table 3. Common field

2. Left outer join
Rule: Based on the table on the left, if there is no corresponding record on the right, it will be displayed with null

Syntax:

Select * from table 1 left table 2 on table 1. Public field = Table 2. Public field

3. Right outer connection

Rule: the table on the right will prevail. If there is no corresponding record on the left, null will be used

Syntax:

Select * from table 1 right table 2 on table 1. Public field = Table 2. Public field

4. Cross connect

Rules: return Cartesian product

Syntax:

Select * from table 1 cross table 2

5. Natural connection  

                  Rule: automatically judge the condition connection based on the field with the same name

Syntax:

1. Natural internal connection
> select * from stuinfo natural  stumarks;
2. Natural left connection
> select * from stuinfo natural left  stumarks;
3. Natural right
> select * from stuinfo natural right  stumarks;

                     Summary:

1. Tables are connected by fields with the same name

2. Return Cartesian product if there is no field with the same name

3. Only one connection field with the same name will be displayed, and the field will be placed at the front

6.using

                    Using to specify connection fields

> select * from stuinfo inner  stumarks using(stuno);