Front-end databases support many table operations and front-end databases

Front-end databases support many table operations and front-end databases
* Directory [1] Preparation [2] multi-Table update [3] Two-Step update [4] Before connecting to [5] infinitely-level tables

In the previous blog, we introduced subqueries and stored the query results in a new data table. Next we will go on to the subquery case to introduce the multi-Table operations in the database in detail.

 

Preparations

In the previous blog, we stored the detailed data in the tdb_goods data table, and stored the category information in the detailed data in the tdb_goods_cates data table.

Next, we will study how to update the tdb_goods_cates table through the tdb_goods_cates data table.

 

Multi-Table update

Multi-Table update is similar to single-Table update.

UPDATE table_references SET col_name1={expr1|DEFAULT}[,col_name2={expr2|DEFAULT}]...[WHERE where_condition]

The table reference relationships are as follows:

table_reference{[INNER | CROSS] JOIN |{LEFT|RIGHT} [OUTER] JOIN}table_referenceON conditional_expr

The results show that the value in the goods_cate column of the tdb_goods data table has been updated to the value of cate_id corresponding to the tdb_goods_cates data table. In this way, strings are replaced by numbers, which greatly saves storage space.

 

Two-step update

In the above multi-Table update operation, we have actually created an empty table and written the query results of the original data table into the empty table, refresh the original data table using the table with the write result.

If you use the create select statement, you can perform two-step update. When creating a data table, you can write the query results to the data table (combining the CREATE and INSERT... SELECT two operation steps), and then use the table that writes the results to reverse update the original data table.

CREATE TABLE [IF NOT EXISTS] tbl_name[(create_definition,...)]select_statement

The following describes how to process the brand information in the tdb_goods table. First, query the "brand" in the tdb_goods table and group

SELECT brand_name FROM tdb_goods GROUP BY brand_name;

Add the brand information to the new table tdb_goods_brands.

  CREATE TABLE tdb_goods_brands (    brand_id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,    brand_name VARCHAR(40) NOT NULL  ) SELECT brand_name FROM tdb_goods GROUP BY brand_name;

Refer to the brand table to update the original product data table.

Note that the brand_name field exists in both tables. To distinguish them, you need to give them different aliases or add the table name before the field

After viewing the Column Structure of the product data table, we found that although the data is changed to a number, the data type is still numeric.

Modify the column names and column types of goods_cate and brand_name in the product data table below

In this way, we have stored a large data table into a small data table. Now, insert several new records in the tdb_goods_cates and tdb_goods_brands tables respectively.

INSERT tdb_goods_cates (cate_name) VALUES ('router'), ('vswitch '), ('nics'); INSERT tdb_goods_brands (brand_name) VALUES ('haier '), ('tsinghua Tongfang '), ('shenzhen ');

Write new records in the tdb_goods data table

INSERT tdb_goods (goods_name, cate_id, brand_id, goods_price) VALUES ('laserjet Pro P1606dn black and white laser printer ', '12', '4', '123 ');

 

Connection

Through the above operations, repeated data has been distributed to different data tables for storage, saving storage space as much as possible. However, the original data needs to be displayed. You need to use the following concept-connection.

Syntax structure

MySQL supports JOIN Operations in SELECT statements, update multiple tables, and delete multiple tables.

table_reference{[INNER | CROSS] JOIN |{LEFT|RIGHT} [OUTER] JOIN}table_referenceON conditional_expr

When a data table is referenced (table_reference), you can use tbl_name AS alias_name or tbl_name alias_name to assign an alias to the data table.

Table_subquery can be used as a subquery in the from clause. Such a subquery must be assigned an alias.

tbl_name[[AS] alias] | table_subquery [AS] alias

Connection Type

The connection types include inner join, left outer join, and right outer join)

In mysql, JOIN, cross join, and inner join are equivalent.

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 records in the result set.

Internal Connection

The left table and right table matching the connection conditions are displayed for internal connections.

The following describes how to query the detailed information of all commodities through an inner connection. The original commodity table contains 24 commodities, but only 23 are displayed, because the specified commodity does not meet the connection conditions.

　　

For inner join, note that the record used for inner join query does not exist in the join data table, and you can try the operation in the WHERE clause: column_name is null. If column_name is specified as not null, MySQL will stop searching for more rows (search for conflicts) after finding the record that meets the connection conditions)

Left Outer Join

The left Outer Join Operation displays all records in the left table and records that meet the connection conditions in the right table.

The following queries the details of all commodities through the left outer link. The original commodity table contains 24 commodities, and now 24 are displayed, but the last commodity is classified as NULL, this is because this category of the right table does not meet the condition, so it is displayed as NULL

Outer right connection

Right Outer Join refers to the display of all records in the right table and records that meet the connection conditions in the left table

The following uses the right outer join to query the detailed information of all commodities. The original commodity table contains 24 commodities, and 26 items are displayed. The exclusive items are the records that match the right but not the left table.

Take the left Outer Join as an example.

A LEFT JOIN B join_condition

The result set of data table B depends on data table A. The result set of data table A depends on all data tables based on the left join conditions (except table B)

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

If A record of Table A meets the WHERE condition, but there is no record that meets the connection condition in Table B, an additional B row with all columns being empty will be generated.

Multi-table join

A join with more than three tables is called a multi-table join. The connection principle is the same as that of the two tables.

The following describes how to query the details of all products through an internal connection.

 

Unlimited table

Is the record of the tdb_goods_cates table. But the actual classification is not these 10 categories, but unlimited classification. The following describes how to implement an infinitely classified data table.

An infinite table requires at least three columns, one being type id, one being type name, and the other being parent id.

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

Then, write the given data

Self-connection

The same data table connects itself. Aliases must be added for differentiation. The word table alias is defined as s, and the parent table alias is defined as p

The following describes all categories and their parent classes.

Find all categories and their subclasses

Next we will look for the number of all categories and their subclasses

Delete duplicate items

From the record, we can see that there are repeated items in the 24 records. Now we need to delete the repeated items.

First, check the duplicate items.

Then, you need to delete multiple tables.

DELETE tbl_name[.*][,tbl_name[.*]]...FROM table_references[WHERE where_condition]