MySQL learning 16: Multi-table join

Connection Overview (1) connecting to the MySQL database supports the JOIN operation in SELECT statements, multi-table updates, and multi-Table deletion. Syntax structure of multi-table JOIN: table_reference {[INNER | CROSS] JOIN} | {LEFT | RIGHT} [OUTER] JOIN} table_referenceONcondtional_expr; (2) Data Table reference table_reference

Connection Overview (1) connecting to the MySQL database supports the JOIN operation in SELECT statements, multi-table updates, and multi-Table deletion. Syntax structure of multi-table JOIN: table_reference {[INNER | CROSS] JOIN} | {LEFT | RIGHT} [OUTER] JOIN} table_reference ON condtional_expr; (2) Data Table reference table_reference

1. Connection Overview (1) Connection

MySQL databases support JOIN Operations in SELECT statements, multi-Table update, and multi-Table deletion. The syntax structure of multi-table join is:

Table_reference {[INNER | CROSS] JOIN} | {LEFT | RIGHT} [OUTER] JOIN} table_reference ON

Condtional_expr;

(2) Data Table reference

Table_reference table_name [[AS] alias] | table_subquery [AS] alias

You can use table_name AS alias_name or table_name alias_name to assign an alias to a data table. Table_subquery can be used as a subquery

In the FROM clause, such a subquery must be assigned its alias.

We may have fields with the same name in the two data tables. to distinguish the fields in each table, we alias the data table name.

Name.

(3) Connection Type

Inner join: Internal JOIN. in MySQL, JOIN, cross join and inner join are equivalent.

LEFT [OUTER] JOIN, left outer join.

RIGHT [OUTER] JOIN, right outer join.
(4) connection conditions

Use the ON keyword to set the connection conditions, or use WHERE instead.

Generally, the ON keyword is used to set the connection condition, and the WHERE keyword is used to filter the result set records.

2. JOIN method (1) INNER JOIN)

Display the records of the left and right tables that meet the connection conditions:

Instance:

Use the internal connection to connect the data table tdb_goods and the data table tdb_goods_cates for joint query.

SELECT goods_id, goods_name, cate_name FROM tdb_goods inner join tdb_goods_cates ON

Tdb_goods.cate_id = tdb_goods_cates.cate_id;

We can see that the query results only find 22 records. The newly added 23rd records are not queried because they do not match the query results.

Condition.

(2) left outer join (LEFT [OUTER] JOIN)

Display all records in the left table and records that meet the connection conditions in the right table

Instance:

Display all records in the tdb_goods data table and matching records in the tdb_goods_cates data table

SELECT goods_id, goods_name, cate_name FROM tdb_goods left join tdb_goods_cates ON

Tdb_goods.cate_id = tdb_goods_cates.cate_id;

(3) right outer join (RIGHT [OUTER] JOIN)

Display all records in the left table and records that meet the connection conditions in the right table

Instance:

Display all records in the tdb_goods_cates data table and matching records in the tdb_goods data table

SELECT goods_id, goods_name, cate_name FROM tdb_goods right join tdb_goods_cates ON

Tdb_goods.cate_id = tdb_goods_cates.cate_id \ G;

Example of Multi-table join

Here we use the inner join of the three data tables as a description:

SELECT goods_id, goods_name, cate_name, brand_name, goods_price FROM tdb_goods AS g INNER JOIN

Tdb_goods_cates AS c ON g. cate_id = c. cate_id inner join tdb_goods_brands AS B ON

G. brand_id = B. brand_id \ G;

We get the first 22 records added.

(2) Description of connections external JOIN: a left join B join_condition

1) The result set of Table B depends on Table.

2) The result set of datagram A depends on all data tables according to the left join condition (except table B)

3) The left Outer Join condition determines how to retrieve data table B (without specifying the WHERE condition)

4) if A record of data table A meets the WHERE condition, but data table B does not have A record that meets the connection condition, all columns are generated.

Empty Additional B rows.

That is, the result shown below:

SELECT goods_id, goods_name, cate_name FROM tdb_goods left join tdb_goods_cates ON

Tdb_goods.cate_id = tdb_goods_cates.cate_id;

The result is known in the preceding example.

If the record used for inner join query does not exist in the connected data table, and the WHERE clause tries the following operations: col_name IS NULL

If col_name is defined as not null, MySQL will stop searching for more rows after finding a record that meets the connection conditions.

Design steps: 1) create a data table

Create table tdb_goods_types (

Type_id smallint unsigned primary key AUTO_INCREMENT,

Type_name VARCHAR (20) not null,

Parent_id smallint unsigned not null default 0

);

2) insert records

INSERT tdb_goods_types (type_name, parent_id) VALUES ('household appliances ', DEFAULT );

INSERT tdb_goods_types (type_name, parent_id) VALUES ('computer office ', DEFAULT );

INSERT tdb_goods_types (type_name, parent_id) VALUES ('household appliances ', 1 );

INSERT tdb_goods_types (type_name, parent_id) VALUES ('household Electric Appliance ', 1 );

INSERT tdb_goods_types (type_name, parent_id) VALUES ('flat panel TV ', 3 );

INSERT tdb_goods_types (type_name, parent_id) VALUES ('airconditioner ', 3 );

INSERT tdb_goods_types (type_name, parent_id) VALUES ('electric fan ', 4 );

INSERT tdb_goods_types (type_name, parent_id) VALUES ('water dispenser ', 4 );

INSERT tdb_goods_types (type_name, parent_id) VALUES ('computer id', 2 );

INSERT tdb_goods_types (type_name, parent_id) VALUES ('computer accessories ', 2 );

INSERT tdb_goods_types (type_name, parent_id) VALUES ('notebooks ', 9 );

INSERT tdb_goods_types (type_name, parent_id) VALUES ('superscript', 9 );

INSERT tdb_goods_types (type_name, parent_id) VALUES ('game bene', 9 );

INSERT tdb_goods_types (type_name, parent_id) VALUES ('cpu ', 10 );

INSERT tdb_goods_types (type_name, parent_id) VALUES ('host', 10 );

3) view records in the data table

SELECT * FROM tdb_goods_types;

The last column in The result shown above indicates that 0 represents the top-level category without Father's Day; 1 to 10 represents the Child class.

(2) Self-connection means that the same data table connects itself.

Instance:

1) view all categories and their parent classes

SELECT s. type_id, s. type_name, p. type_name FROM tdb_goods_types AS s left join tdb_goods_types AS p

ON s. parent_id = p. type_id;

2) Search for all categories and their subclasses

SELECT p. type_id, p. type_name, s. type_name FROM tdb_goods_types AS p left join tdb_goods_types

S ON s. parent_id = p. type_id;

3) Search for the number of all categories and their subclasses

SELECT p. type_id, p. type_name, count (s. type_name) AS children_count FROM tdb_goods_types AS p LEFT

JOIN tdb_goods_types AS s ON s. parent_id = p. type_id group by p. type_name order by p. type_id;

Delete more than five tables

The syntax structure for deleting multiple tables is:

DELETE tabke_name [. *] [, table_name [. *]... FROM table_references [WHERE where_condition];

SELECT * FROM tdb_goods \ G;

We found Repeated Records. The following is to delete duplicate records and keep records with a smaller id value.

(1) Search for duplicate records

SELECT goods_id, goods_name FROM tdb_goods group by goods_name HAVING count (goods_name)> =

2;

(2) Delete duplicate records

DELETE t1 FROM tdb_goods AS t1 left join (SELECT goods_id, goods_name FROM tdb_goods GROUP

BY goods_name HAVING count (goods_name)> = 2) AS t2 ON t1.goods _ name = t2.goods _ name WHERE

T1.goods _ id> t2.goods _ id;

(3) check whether all records in the data table have repeated records again.

SELECT * FROM tdb_goods \ G;

SELECT goods_id, goods_name FROM tdb_goods group by goods_name HAVING count (goods_name)> =

2;

From the above results, we can see that there are no repeated records in the data table, indicating that we have successfully deleted the repeated records and kept the goods_id value.

Small records.