Multi-table operation of the front-end learning Database

Previous words

Previous blog post introduced the related content of sub-query, and finally we stored the query results in a new data table. Below we will follow the example of a subquery, detailing multiple table operations in the database

Preparatory work

In the previous blog post, we stored the detailed data in the Tdb_goods data table, storing the category information from the detailed data in the Tdb_goods_cates data table

Next, we'll look at how to update the Tdb_goods table with the Tdb_goods_cates data table

Multiple table Updates

Multiple-table updates are similar to single-table updates

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

The reference relationship for the table is as follows:

Table_reference{[inner | Cross] JOIN | {left| Right} [OUTER] Join}table_referenceon conditional_expr

As seen from the results, the values in the Goods_cate column in the Tdb_goods data table have been updated to the corresponding cate_id values in the Tdb_goods_cates data table. In this way, replacing strings with numbers greatly saves storage space

Two-Step update

In the above multi-table update operation, in fact, we went through two steps, first created an empty table, the original data table query results are written to the empty table, and then use the table to write the results back to update the original data table

If you use the Create SELECT statement, you can implement a two-step update and write the query results to the datasheet (merge Create and insert ...) in the data table. Select two steps), and then reverse-update the original data table with the table that writes the results

CREATE TABLE [IF not EXISTS] tbl_name[(create_definition,...)] Select_statement

Below to deal with the brand information in the original data table Tdb_goods, first query the "brand" of the Tdb_goods table, and group

SELECT brand_name from Tdb_goods GROUP by Brand_Name;

Put brand information into 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;

Re-reference the brand table, update the original product data sheet

Note here that the Brand_Name field exists in both tables. To differentiate them, you need to give them different aliases or add table names before the fields

Looking at the column structure of the product data table, we found that although the data was modified for the number, the data type is still the character type

The following changes the column names and column types of goods_cate and Brand_Name in the Product data table

In this way, we have divided a large data table into small data tables for storage. Now, insert several new records in the Tdb_goods_cates and Tdb_goods_brands tables, respectively.

Insert Tdb_goods_cates (Cate_name) VALUES (' Router '), (' Switch '), (' Network card '), insert Tdb_goods_brands (brand_name) VALUES (' Haier '), (' Tsinghua Tongfang '), (' Shenzhou ');

New records are also written in the Tdb_goods data table

INSERT tdb_goods (Goods_name,cate_id,brand_id,goods_price) VALUES (' LaserJet Pro p1606dn monochrome laser printer ', ' 12 ', ' 4 ', ' 1849 ');

Connection

Through the above operation, the duplicated data has been distributed to different data tables for storage, as much as possible to save storage space. However, when displayed, the original data needs to be displayed, which requires the use of the concepts described below-connection

Grammatical structure

MySQL supports connection (join) Operations in SELECT statements, multiple table updates, and multiple table DELETE statements

Table_reference{[inner | Cross] JOIN | {left| Right} [OUTER] Join}table_referenceon conditional_expr

Data table Reference (table_reference), the data table can be aliased using Tbl_name as Alias_name or Tbl_name alias_name

Table_subquery can be used as a subquery in the FROM clause, such that a subquery must give it an alias

Tbl_name[[as] alias | Table_subquery [as] Alias

Connection type

Connection types mainly include inner join (INNER join), left outer join (Ieft [OUTER] join), right outer join (R [OUTER] join)

In MySQL, join, cross join, and INNER join are equivalent

Connection conditions

Use the ON keyword to set connection conditions, or you can use where instead. In general, use the ON keyword to set the join condition and use the Where keyword to filter the result set records

Internal connection

Inner joins show records of the left and right tables that meet the join criteria

Below through the internal connection to query all the product details, the original product list has 24 items, but only show 23 pieces, because that one does not meet the connection conditions

　　

For inner joins, note that the records found using an inner join do not exist in the connection data table, and try the operation in the WHERE clause: column_name is NULL. If COLUMN_NAME is specified as not null,mysql stops searching for more rows after it finds a record that matches the condition connected (find conflicts)

Left outer connection

Left outer join refers to all records showing the left table and the right table matching the join criteria.

Below through the left Outer connection to query all the product details, the original product table has 24 items, now also shows 24 pieces, but the last item is classified as NULL, because this classification of the right table does not meet the criteria, so the display is null

Right outer connection

Right outer join refers to all records showing the right table and the record of the left table matching the join condition.

Below through the right outside connection to query all the product details, the original product list has 24 items, now shows 26 pieces, more out of the right table but does not conform to the left table records

For outer joins, the following points are noted, taking the left outer join as an example

A left JOIN B join_condition

The result set of data table B depends on the data table A, and the result set of data table A depends on all data tables (except for table B) According to the left join condition.

Left OUTER JOIN condition determines how data table B is retrieved (without specifying a where condition)

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, an additional B row is generated for all columns that are empty

Multi-table Connection

Three tables above the connection is called a multi-table connection, the principle of the same as the connection of two tables

The following is an internal connection that enables you to query all product details

Infinite Level table

is a record of the Tdb_goods_cates table. But the actual classification is not the 10 category, but the infinite classification. The following is an introduction to the implementation of an infinitely classified data table

An infinite table needs at least three columns, one is type ID, one type name, and one is the parent ID

CREATE TABLE Tdb_goods_types (type_id   

Then, write the given data

Self-Connection

A self-connection refers to the same data table that connects itself to itself. To make a distinction, you need to add an alias. The Word table alias is defined as S, and the parent table alias is defined as P

Below to find all the classifications and their parent classes

Below to find all the categories and their subclasses

Below to find the number of all categories and their subclasses

Delete duplicate items

From the record, you can see that there are duplicates in the 24 records, now to find a way to delete the duplicates

First, find the duplicate items first

Then, you need to use multiple table deletions to implement the delete operation

DELETE tbl_name[.*][,tbl_name[.*]] ... From Table_references[where Where_condition]

Multi-table operation of the front-end learning Database