Shulou(Shulou.com)05/31 Report--

This article mainly introduces the example analysis of multi-table connection in MySQL, which is very detailed and has certain reference value. Friends who are interested must finish it!

Joins can be used to query, update, and establish factual foreign keys (refers to the artificial correspondence between two tables, on the other hand, FORGIEN KEY is also called physical foreign keys)

The join of a table is essentially the reverse constraint of a foreign key.

Connection condition

Use ON to set connection conditions, or you can use WHERE instead.

Normally

ON: setting connection condition

WHERE: filtering result set records

An unconditional JOIN inner join is essentially a Cartesian product.

[INNER] connection within JOIN

In MySQL, JOIN, CROSS JOIN and INNER JOIN are equivalent.

The inner join indicates the intersection, and only the records of An and B tables that meet the join conditions are displayed. Records that do not meet the connection conditions are not displayed.

SELECT goods_id,goods_name,cate_name FROM tdb_goods INNER JOIN tdb_goods_cate ON tdb_goods.cate_id = tdb_goods_cate.cate_id

Use internal joins for multi-table update operations:

-- modify the goods_cate stored in Chinese in the tdb_ goods table to the corresponding cate_id in the tdb_goods_ tables to save space on the table name that UPDATE tdb_goods INNER JOIN tdb_goods_catesON goods_cate=cate_name SET goods_cate=cate_id;--tdb_goods wants to change-- the schedule associated with tdb_goods_cates-- the relationship between the corresponding columns of the two tables-- goods_cate=cate_id; setting value

External connection

More internal connections are used than external connections.

If only one table exists in a field, the inner field of the other table returns NULL

LEFT [OUTER] JOIN left outer connection

Displays all records of the left table and records of the right table that meet the connection conditions.

If you use LEFT JOIN, there is a record An in the left table, and no corresponding record is found in the right table, then the returned result will show a record with only the corresponding fields in record An and all other fields as NULL (similar to RIGHT JOIN).

RIGHT [OUTER] JOIN right outer connection

Displays all the records of the right table and the records of the left table that meet the connection conditions.

Multi-table connection

Similar to the connection between two tables

Such as the connection of three tables:

SELECT goods_id,goods_name,b.cate_name,c.brand_name,goods_priceFROM products AS an INNER JOIN products_cate AS b ON a.goods_cate = b.cate_idINNER JOIN products_brand AS c ON a.brand_name = c.brand_id

Self-connection

Design an infinite classification data table

There are both parent and subclasses in the same table, which is essentially a tree:

You can query by joining yourself to the same data table:

-- find the name that shows the parent class id, SELECT s.typeaccounidreports.typeroomnamememp.typeroomnamestatement s.parentroomidstation.typeaccounidprinidplay.typeaccouniditlybetweenthe name corresponding to the subclass id, SELECT p.typeroomidstations.typeroomnamename AS child_nameFROM tdb_goods_types AS pLEFT JOIN tdb_goods_types AS sON p.type_id=s.parent_id. -- find out how many children there are in SELECT p.typewriter _

Query and delete multiple tables

Here, we use self-join to simulate two tables, delete the duplicates in the table and keep the record with smaller goods_id.

DELETE T1 FROM tdb_goods AS T1 LEFT JOIN (--Select goods_name duplicate record SELECT goods_id,goods_name FROM tdb_goods GROUP BY goods_name-- MySQL version 5.7.5 and above enables only_full_group_by SQL mode The column of select must be in group, or it must be an aggregate column (SUM,AVG,MAX,MIN). HAVING COUNT (goods_name) > = 2) AS T2 is not enabled here-- left join between T1 and T2. In fact, inner join and right join also work here-- join condition WHERE t1.goods_id > t2.goods_id of T1 and T2. -- in the LEFT JOIN result set, select the records that satisfy t1.goods_id > t2.goods_id. These are all the contents of the article "sample Analysis of Multi-table joins in MySQL". Thank you for reading! Hope to share the content to help you, more related knowledge, welcome to follow the industry information channel!

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.