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

This article will explain in detail how to achieve related queries in MySQL. The content of the article is of high quality, so the editor shares it for you as a reference. I hope you will have a certain understanding of the relevant knowledge after reading this article.

MySQL

Association query

Previously, we introduced single-table queries (that is, only getting data from one table), but in practical application, we will query multiple tables at the same time. Here, we will introduce the use of multi-table association queries.

SQL join is used to query data from two or more tables based on the relationship between the columns in these tables

Pre-knowledge

Primary key (Primary Key): a field that can uniquely identify a record, such as the student ID in the student table and our ID number in our life.

Foreign key (Foreign Key): the primary key that points to another table, such as class ID in the student table. Class ID is the primary key in the class table, but it is the foreign key in the student table.

Primary keys and foreign keys can be specified when creating tables, and they can control the integrity and consistency of your data at the database level.

Test data reference: http://yuguiyang.github.io/2017/09/09/mysql-handbook-01/

Inner join

Inner join can be abbreviated to join, and the result set is an intersection of records that exist in both tables. Refer to the picture above for details.

For example, in the student table, there is a class ID. We want to find the class information in the class table according to the class ID.

Select

*

From

T_student a

-- the table to associate with the query

Join

T_class b

What fields are used to associate the two tables

On

A.c_id = b.c_id

Left join

Left association, with the left table as the primary table, no matter whether the foreign key exists in the right table or not, the data of the left table will exist.

For example, in the student table, there is a record that his class ID is 904, but there is no class information of 904 in the class table.

So, you can't find this record if you use join.

-2. Left join

-- the student table is the main table and contains all student information

Select

*

From

T_student a

Left join

T_class b

On

A.c_id = b.c_id

Right join

Right association, which is similar to making an association, but the right table is the primary table

-3. Right join

-- the class table is the main table, regardless of whether the class has student information or not.

Select

*

From

T_student a

Right join

T_class b

On

A.c_id = b.c_id

Full outer join

Full relevance, mysql does not have full join syntax, we can achieve it by using union

Select

*

From

T_student a

Left join

T_class b

On

A.c_id = b.c_id

UNION

Select

*

From

T_student a

Right join

T_class b

On

A.c_id = b.c_id

Cross join

Cross join is a Cartesian product of two tables.

Select * from t_class a

Cross join t_class b

Order by a.c_id,b.c_id

On how to achieve the association query in MySQL to share here, I hope that the above content can be of some help to you, can learn more knowledge. If you think the article is good, you can share it for more people to see.

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.