MySQL Learning 16: multi-table connection

A connection overview (1) connection

The MySQL database supports join operations in SELECT statements, multiple table updates, and multiple table deletions. The syntax structure for a multi-table connection is:

table_reference {[INNER | Cross] JOIN} | {left| Right} [OUTER] joins} table_reference on

condtional_expr;

(2) Data Sheet reference

table_reference table_name [[as] alias] | table_subquery [as] alias

The data table can be aliased using table_name as ALIAS_NAME or table_name alias_name. Table_subquery can be used as a sub-search

The query is used in the FROM clause, such a subquery must be give it an alias.

We may have fields of the same name in two data tables, in order to differentiate the fields in each table, we alias their data table names, use the

Distinguished by name.

(3) connection type

INNER join, inner connection; in MySQL, the Join,cross join and the inner join are equivalent.

Left [OUTER] join, outer join.

right [OUTER] join, left OUTER join.
(4) connection conditions

Use the ON keyword to set connection conditions, or you can use where instead.

You typically use the ON keyword to set connection conditions and use the Where keyword to filter the result set records.

Two connection mode (1) Inner connection (INNER join)

Displays the left and right table records that match the join criteria:

Instance:

Connecting data tables Tdb_goods and datasheets tdb_goods_cates two tables using an internal connection for federated queries

SELECT goods_id,goods_name,cate_name from Tdb_goods INNER joins Tdb_goods_cates on

tdb_goods.cate_id = tdb_goods_cates.cate_id;

We see that the result of the query is only to find 22 records, our newly added 23rd record is not queried because the connection that does not conform to the query

Conditions.

(2) Left outer connection (Ieft [OUTER] join)

Shows all records of the left table and the right table matches the join criteria

Instance:

Displays all records in the Tdb_goods data table and the qualifying 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 connection (R [OUTER] join)

Shows all records of the left table and the right table matches the join criteria

Instance:

Displays all records in the Tdb_goods_cates data table and the qualifying 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;

three multi-table connection (1) Multi-table Connection example

Here we use an inner connection of three data sheets 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 have to get the first 22 records added.

(2) A few notes about the connection External connection:A left JOIN B join_condition

1) data Table B's result set dependent data table A

2) The result set of the datagram a depends on all data tables (except for table B) based on the left join connection condition

3) LEFT outer join condition determines how data table B is retrieved (without specifying a where condition)

4) If a record of table a conforms to the where condition, but there is no record in data table B that matches the join condition, it will generate a list of all columns

Empty the extra B-line.

This 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;

We already know this result in the example above.

If the records that are found by using an inner join do not exist in the connection data table, and the following action is attempted in the WHERE clause: Col_name is NULL

, if col_name is defined as not Null,mysql will stop searching for more rows after it finds records that meet the connection criteria.

Four design of infinite class classification table (1) Design steps : 1) First create a data table

CREATE TABLE Tdb_goods_types (

type_id SMALLINT UNSIGNED PRIMARY KEY auto_increment,

Type_name VARCHAR () not NULL,

parent_id SMALLINT UNSIGNED not NULL DEFAULT 0

);

2) Insert record

INSERT tdb_goods_types (type_name,parent_id) VALUES (' Home appliance ', DEFAULT);

INSERT tdb_goods_types (type_name,parent_id) VALUES (' Computer Office ', DEFAULT);

INSERT tdb_goods_types (type_name,parent_id) VALUES (' Everyone electricity ', 1);

INSERT tdb_goods_types (type_name,parent_id) VALUES (' Living electrical ', 1);

INSERT tdb_goods_types (type_name,parent_id) VALUES (' Flat screen TV ', 3);

INSERT tdb_goods_types (type_name,parent_id) VALUES (' Air conditioning ', 3);

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

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

INSERT tdb_goods_types (type_name,parent_id) VALUES (' Computer Machine ', 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 (' Super Ben ', 9);

INSERT tdb_goods_types (type_name,parent_id) VALUES (' Game Ben ', 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 the records in the datasheet

SELECT * from Tdb_goods_types;

The last column of the display results above means: 0 represents the top-level category, there is no Father node, and 1 to 10 represents the subclass.

(2) Self-connection self-connection refers to the same data table that is connected to 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) Find 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 as

s on s.parent_id = p.type_id;

3) Find the number of all classes 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;

Five multiple table deletions

The syntax structure for multiple table deletions is:

DELETE tabke_name[.*] [, table_name[.*]] ... From Table_references [WHERE where_condition];

SELECT * from Tdb_goods\g;

we looked for a duplicate record. So the next thing to do is to delete the duplicate records and keep the records with the smaller ID values.

(1) Finding duplicate records

SELECT goods_id,goods_name from Tdb_goods GROUP by Goods_name have 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 have count (goods_name) >= 2) as t2 on t1.goods_name = T2.goods_name WHERE

t1.goods_id > t2.goods_id;

(3) See if duplicate records exist for all records in the datasheet again

SELECT * from Tdb_goods\g;

SELECT goods_id,goods_name from Tdb_goods GROUP by Goods_name have count (goods_name) >=

2;

From the above results, we can see that there are no duplicate records in the data table, indicating that we have successfully deleted the duplicate records and retained the GOODS_ID value

A smaller record.

MySQL Learning 16: multi-table connection