Subquery

Subqueries refer to SELECT clauses that appear in other SQL statements.
For example:
SELECT * FROM t1 WHERE col1 = (SELECT col2 FROM t2);
Among them, SELECT * FROM t1 is called Outer Query/ Outer Statement (that is, the outer query)
SELECT col2 FROM t2, called SubQuery

Subqueries are nested within queries and must always appear in parentheses.
Subqueries can contain multiple keywords or conditions.
Such as distinct, GROUP BY, ORDER BY, LIMIT, functions, etc.
The outer queries of sub-queries can be: SELECT INSERT UPDATE, SET or DO.
Query is not a search, but a generic term for all SQL commands.

Subquery return value
Scalar, row, column or subquery can be returned.

Here the AVG and ROUND functions are aggregate functions, and the parameters passed behind the ROUND functions are the digits of decimal numbers that are rounded and reserved.

Subqueries using [NOT] IN

Grammatical structure
operand comparison_operator [NOT]IN (subquery)
= ANY Operator and IN Equivalent
! = ALL or <> ALL Operator Equivalent to NOT IN

Multi-table update

Table Reference: A table passes through JOIN
table_reference
{[INNER| CROSS] JOIN | {LEFT|RIGHT} [OUTER] JOIN}

mysql> SELECT goods_id, goods_name,cate_name,brand_name,goods_price
    -> FROM tdb_goods AS g
    -> INNER JOIN tdb_goods_cate AS c ON g.cate_id = c.cate_id
    -> INNER JOIN tdb_goods_brands AS b ON g.brand_id = b.brand_id\G;

Essential: Multiple tables are stored by foreign keys, and then linked by multiple tables.

line type
INNER JOIN
Remember UPDATE must be capitalized!!!

CREATE...SELECT
Create a data table and write the query results to the data table
CREATE TABLE [IF NOT EXISTS] tbl_name
[(create_definition,...)]
select_statement

In the above code, multi-table updates are implemented and sub-table queries are used.

After modifying the fields of the table, you also need to modify the structure of the table.
Through ALTER... CHANGE statement modified

Connect
MySQL supports JOIN operations in SELECT statements, multi-table updates, and multi-table deletion statements.
The connection here refers to the connection of individual operation tables.

Grammatical structure:
table_reference
{[INNER | CROSS] JOIN | {LEFT |RIGHT} [OUTER] JOIN}
table_reference
ON conditional_expr

ON is followed by the condition of connection.

Internal links (equivalent to union)

INNER JOIN...ON...
Use ON keywords to set connection conditions, or WHERE instead.

The ON keyword is usually used to set the connection conditions.
Use WHERE keyword to filter the result collection record.

External connection

Left outer connection
A LEFT JOIN B join_condition
The results of Data Table B depend on Data Table A
The result set of data table A depends on all data tables according to the left join condition (except B)
The left join condition determines how to retrieve table B (without specifying the WHERE condition).
If a record in Datasheet A meets the WHERE criteria, but there is no record in Datasheet B that meets the join criteria, all additional B rows listed as null will be generated.

Design of Infinite Classification Table

Example: Among the library books, books can be divided into four major masterpieces, and the four major masterpieces can be indefinitely classified.
So you can connect by yourself: the same data table joins itself

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

  INSERT tdb_goods_types(type_name,parent_id) VALUES('Household Electric Appliances',DEFAULT);
  INSERT tdb_goods_types(type_name,parent_id) VALUES('Computer, Office',DEFAULT);
  INSERT tdb_goods_types(type_name,parent_id) VALUES('Telephone',1);
  INSERT tdb_goods_types(type_name,parent_id) VALUES('Household appliances',1);
  INSERT tdb_goods_types(type_name,parent_id) VALUES('Flat panel TV',3);
  INSERT tdb_goods_types(type_name,parent_id) VALUES('Air conditioner',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 machine',2);
  INSERT tdb_goods_types(type_name,parent_id) VALUES('Computer accessories',2);
  INSERT tdb_goods_types(type_name,parent_id) VALUES('Notebook',9);
  INSERT tdb_goods_types(type_name,parent_id) VALUES('Super Ben',9);
  INSERT tdb_goods_types(type_name,parent_id) VALUES('Gamebook',9);
  INSERT tdb_goods_types(type_name,parent_id) VALUES('CPU',10);
  INSERT tdb_goods_types(type_name,parent_id) VALUES('Host',10);

 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;

The first way of self-joining is to treat one's own table as a parent class, and the other table on the right as a subclass. The third column of the temporary table is actually the type_name of the parent class.

 SELECT p.type_id ,p.type_name,s.type_name FROM tdb_goods_types p LEFT JOIN
  tdb_goods_types s ON s.parent_id = p.type_id;

The second way is to look at the table on your left as a table of subclasses, and the table on your right as a table of the parent class. The last column of the temporary table is the type_name of the subclass, which queries the subclasses of the parent class.

Multi-table deletion

SELECT goods_id ,goods_name FROM tdb_goods GROUP BY goods_name
    -> HAVING count(goods_name) >= 2;

1. First query records with multiple duplicate records through HAVING
2. Delete by DELETE statement

 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;

It should be noted here that the use of sub-query "()" is to carry out sub-query, it is worth mentioning that
For left join, no matter what condition is followed by on, all the data in the left table is found, so you want to filter the condition behind where.