MySQL Learning Notes - Subqueries and Connections
Enter client into gbk encoding mode to display:
mysql> SET NAMES gbk;
1. Subquery
Definition of subqueries:
Subquery refers to the SELECT clause that appears in other SQL statements.
For example:
SELECT * FROM t1 WHERE col1 = (SELECT col2 FROM t2);
Where SELECT * FROM t1 is called Outer Query / Outer Statement (external query)
SELECT col2 FROM t2, known as SubQuery
Subquery conditions:
Subqueries are nested within queries and must always appear in parentheses.
Subqueries can contain multiple keywords or conditions.
For example: DISTINCT, GROUP BY, ORDER BY, LIMIT functions, etc.
External queries for subqueries can be: SELECT, INSERT, UPDATE, SET or DO
The return value of the subquery:
Subqueries can return scalars, rows, columns, or subqueries
2. Subqueries using comparison operators
Subqueries using comparison operators:
=,>,<,>=,<=,<>,!=, <=>
Grammatical structure:
Opnd (operand), comparison_operator (comparison operator), subquery (subquery)
COMPARISON OPERATORS MODIFIED WITH ANY, SOME OR ALL
operand comparison_operator ANY (subquery)
operand comparison_operator SOME (subquery)
operand comparison_operator ALL (subquery)
1. Syntax structure, operand, comparison operator any (subquery)
Operator comparison operator some (subquery)
Operator comparison operator all (subquery)
2. Suitable for sub-queries with multiple results
3.any and some results are consistent. all is contrary to any and some.
For example, the average price is queried from the tdb_goods table, and the decimal point is reserved for 2 bits.
mysql> SELECT ROUND(avg(goods_price),2) AS avg_price FROM tdb_goods; +-----------+ | avg_price | +-----------+ | 5391.30 | +-----------+
Query for goods above average price:
mysql> SELECT * FROM tdb_goods WHERE goods_price > 5391.30;
mysql> SELECT * FROM tdb_goods WHERE goods_price > (SELECT ROUND(AVG(goods_price),2) FROM tdb_goods);
Query the list of superbooks, in ascending order of prices:
mysql> SELECT * FROM tdb_goods WHERE goods_cate = 'Super Ben' ORDER BY goods_price ASC;
- Query the list with the lowest price higher than the super-prime price, and arrange it in descending order:
mysql> SELECT * FROM tdb_goods WHERE goods_price > ANY(SELECT goods_price FROM tdb_goods WHERE goods_cate = 'Super Ben') ORDER BY goods_price DESC;
- Query the list with the highest price than the Superbook, in descending order:
mysql> SELECT * FROM tdb_goods WHERE goods_price > ALL(SELECT goods_price FROM tdb_goods WHERE goods_cate = 'Super Ben') ORDER BY goods_price DESC;
3. Subqueries triggered by [NOT]IN/EXISTS
Subquery form:
1. Subqueries using IN
2. Subqueries using [NOT]IN
Grammatical structure: operand comparison_operator [NOT] IN (subquery)
= ANY operator is equivalent to IN,!= ALL or <> ALL operator is equivalent to NOT IN.
3. Subqueries using [NOT]EXISTS (relatively few)
If the subquery returns any rows, EXISTS will return TRUE, otherwise FALSE
For example, look for lists of goods that are not super-basic:
mysql> SELECT * FROM tdb_goods WHERE goods_cate NOT IN(SELECT goods_cate FROM tdb_goods WHERE goods_cate = 'Super Ben');
4. Insert records using INSERT...SELECT
Example: Insert goods_cate classification in tdb_goods_cates table;
mysql> INSERT tdb_goods_cates(cate_name) SELECT goods_cate FROM tdb_goods GROUP BY goods_cate; mysql> SELECT * FROM tdb_goods_cates; +---------+---------------------+ | cate_id | cate_name | +---------+---------------------+ | 1 | Desktop computer | | 2 | Tablet PC | | 3 | The server/Workstation | | 4 | Gamebook | | 5 | Notebook | | 6 | Notebook Accessories | | 7 | Super Ben | +---------+---------------------+
5. Multi-table update
Multi-table update:
UPDATE table_references SET col_name1={expr1|DEFAULT} [,col_name2={expr2|DEFAULT}]... [WHERE where_condition]
Where table_references is the syntactic structure of the join
Connection type:
INNER JOIN, Internal Connection
JOIN, CROSS JOIN and INNER JOIN are equivalent in MySQL
LEFT [OUTER] JOIN, Left Outside Connection
RIGHT [OUTER] JOIN, Right External Connection
Connection-Grammatical Structure
table_reference {[INNER | CROSS] JOIN |{LEFT|RIGHT} [OUTER] JOIN} table_reference ON conditional_expr
For example, goods_cate in tdb_goods is updated to the corresponding cate_id in the tdb_goods_cate table.
mysql> UPDATE tdb_goods INNER JOIN tdb_goods_cates ON goods_cate = cate_name SET goods_cate = cate_id;
6. One step of multi-table updates is in place
CREATE...SELECT
Create the data table and write the query results to the data table at the same time (combining the two steps of CREATE and INSERT...SELECT)
CREATE TABLE [IF NOT EXISTS] tbl_name [(create_definition,...)] select_statement
Example: Create a brand classification data table tdb_goods_brand and write brand_name in the tdb_good table
mysql> CREATE TABLE tdb_goods_brand( -> brand_id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT, -> brand_name VARCHAR(40) NOT NULL -> ) -> SELECT brand_name FROM tdb_goods GROUP BY brand_name;
- For example, update brand_name in tdb_goods to the corresponding brand_id in the tdb_goods_brand table. Wrong Writing (The program can't tell which brand_name belongs to which table):
mysql> UPDATE tdb_goods INNER JOIN tdb_goods_brand ON brand_name = brand_name SET brand_name = brand_id; ERROR 1052 (23000): Column 'brand_name' in field list is ambiguous
The solution is to alias the table:
mysql> UPDATE tdb_goods AS a INNER JOIN tdb_goods_brand AS b ON a.brand_name = b.brand_name SET a.brand_name = b.brand_id;
- Query the data table structure of tdb_goods
mysql> DESC tdb_goods; +-------------+------------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------------+------------------------+------+-----+---------+----------------+ | goods_id | smallint(5) unsigned | NO | PRI | NULL | auto_increment | | goods_name | varchar(150) | NO | | NULL | | | goods_cate | varchar(40) | NO | | NULL | | | brand_name | varchar(40) | NO | | NULL | | | goods_price | decimal(15,3) unsigned | NO | | 0.000 | | | is_show | tinyint(1) | NO | | 1 | | | is_saleoff | tinyint(1) | NO | | 0 | | +-------------+------------------------+------+-----+---------+----------------+
Goods_cate and brand_name are still varchar. Now we change the field name goods_cate to cate_id and brand_name to brand_id. To save space, we change the data type to smallant.
mysql> ALTER TABLE tdb_goods； -> CHANGE goods_cate cate_id SMALLINT UNSIGNED NOT NULL, -> CHANGE brand_name brand_id SMALLINT UNSIGNED NOT NULL; mysql> DESC tdb_goods； +-------------+------------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------------+------------------------+------+-----+---------+----------------+ | goods_id | smallint(5) unsigned | NO | PRI | NULL | auto_increment | | goods_name | varchar(150) | NO | | NULL | | | cate_id | smallint(5) unsigned | NO | | NULL | | | brand_id | smallint(5) unsigned | NO | | NULL | | | goods_price | decimal(15,3) unsigned | NO | | 0.000 | | | is_show | tinyint(1) | NO | | 1 | | | is_saleoff | tinyint(1) | NO | | 0 | | +-------------+------------------------+------+-----+---------+----------------+
7. Grammatical Structure of Connections
Connections support JOIN operations in SELECT statements, multi-table updates, and multi-table deletion statements of MySQL.
table_reference {[INNER|CROSS] JOIN|{LEFT|RIGHT}[OUTER]JOIN} table_reference ON conditional_expr
Data Table Reference
table_reference
tbl_name [[AS] alias] | table_subquery [AS] alias
Data tables can use tbl_name AS alias_name
Or tbl_name alias_name gives an alias
table_subquery can be used as a subquery in the FROM clause.
Such subqueries must be aliased.
8. Internal connection INNER JOIN
INNER JOIN, Internal Connection
In MySQL, JOIN, CROSS JOIN and INNER JOIN are equivalent
LEFT [OUTER] JOIN, Left Outside Connection
RIGHT [OUTER] JOIN, Right External Connection
Connection conditions:
Use ON keywords to set connection conditions, or WHERE instead.
Connection conditions are usually set using ON keywords
Use WHERE keyword to filter result set records
The Difference between Internal Connection and External Connection
Internal connection, showing records of left and right tables that meet the connection criteria, i.e. intersection
For example, insert several records
Insert 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');
Write any notes 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','1849');
For example, look at the records that conform to the internal connection. Only the records that both the commodity table and the brand table have appeared in the table. This is the internal connection (here the commodity table is the left table and the classification table is the right table):
mysql> SELECT goods_id,goods_name,cate_name,goods_price FROM tdb_goods -> INNER JOIN tdb_goods_cates ON tdb_goods.cate_id = tdb_goods_cates.cate_id;
9. Outer Connection OUTER JOIN
Left outer connection
- Display all the records of the left table and the records of the right table meeting the connection criteria
- For example, look at the records that conform to the left outer join. All the records that appear in the table and those in the table 2 are the left outer join (here the commodity table is the left table and the classification table is the right table).
mysql> SELECT goods_id,cate_name,goods_price FROM tdb_goods LEFT JOIN tdb_goods_cates ON tdb_goods.cate_id = tdb_goods_cates.cate_id;
- Right External Connection
- Display all the records of the right table and the records of the left table meeting the connection criteria
- For example, look at the records that conform to the right outer join. All the records that appear in the brand table and those in the two tables are the right outer join (here the commodity table is left table and the classification table is right table).
mysql> SELECT goods_id,goods_name,cate_name,goods_price FROM tdb_goods RIGHT JOIN tdb_goods_cates ON tdb_goods.cate_id = tdb_goods_cates.cate_id;
10. Multi-table connection
/**
Multi-table joins are the same as the joins of two tables
The join of tables is essentially the reverse constraint of foreign keys
*/
For example, to check the records that conform to the internal connection, there are only records in the list of goods and brands. This is the multi-table connection of the internal connection (here the commodity table is left table, the brand table and the classification table is right table):
mysql> SELECT goods_id,goods_name,cate_name,brand_name,goods_price FROM tdb_goods -> INNER JOIN tdb_goods_cates ON tdb_goods.cate_id = tdb_goods_cates.cate_id -> INNER JOIN tdb_goods_brand ON tdb_goods.brand_id = tdb_goods_brand.brand_id;
11. Design of Infinite Classification Table
Self-connection
- For example, find all classifications and their parent classes:
mysql> 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; +---------+-----------------+-----------------+ | type_id | type_name | type_name | +---------+-----------------+-----------------+ | 1 | Household Electric Appliances | NULL | | 2 | Computer, Office | NULL | | 3 | Telephone | Household Electric Appliances | | 4 | Household appliances | Household Electric Appliances | | 5 | Flat panel TV | Telephone | | 6 | Air conditioner | Telephone | | 7 | Electric fan | Household appliances | | 8 | Water dispenser | Household appliances | | 9 | Computer machine | Computer, Office | | 10 | Computer accessories | Computer, Office | | 11 | Notebook | Computer machine | | 12 | Super Ben | Computer machine | | 13 | Gamebook | Computer machine | | 14 | CPU | Computer accessories | | 15 | Host | Computer accessories | +---------+-----------------+-----------------+
- For example, find all classifications and their subclasses:
mysql> 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; +---------+-----------------+--------------+ | type_id | type_name | type_name | +---------+-----------------+--------------+ | 1 | Household Electric Appliances | Telephone | | 1 | Household Electric Appliances | Household appliances | | 2 | Computer, Office | Computer machine | | 2 | Computer, Office | Computer accessories | | 3 | Telephone | Flat panel TV | | 3 | Telephone | Air conditioner | | 4 | Household appliances | Electric fan | | 4 | Household appliances | Water dispenser | | 5 | Flat panel TV | NULL | | 6 | Air conditioner | NULL | | 7 | Electric fan | NULL | | 8 | Water dispenser | NULL | | 9 | Computer machine | Notebook | | 9 | Computer machine | Super Ben | | 9 | Computer machine | Gamebook | | 10 | Computer accessories | CPU | | 10 | Computer accessories | Host | | 11 | Notebook | NULL | | 12 | Super Ben | NULL | | 13 | Gamebook | NULL | | 14 | CPU | NULL | | 15 | Host | NULL | +---------+-----------------+--------------+
- For example, find the number of all classes and their subclasses
mysql> SELECT p.type_id,p.type_name,COUNT(s.type_name) 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; +---------+-----------------+--------------------+ | type_id | type_name | COUNT(s.type_name) | +---------+-----------------+--------------------+ | 1 | Household Electric Appliances | 2 | | 2 | Computer, Office | 2 | | 3 | Telephone | 2 | | 4 | Household appliances | 2 | | 5 | Flat panel TV | 0 | | 6 | Air conditioner | 0 | | 7 | Electric fan | 0 | | 8 | Water dispenser | 0 | | 9 | Computer machine | 3 | | 10 | Computer accessories | 2 | | 11 | Notebook | 0 | | 12 | Super Ben | 0 | | 13 | Gamebook | 0 | | 14 | CPU | 0 | | 15 | Host | 0 | +---------+-----------------+--------------------+
- Add child_count field for tdb_goods_types
mysql> UPDATE tdb_goods_types AS t1 INNER JOIN -> (SELECT p.type_id,p.type_name,count(s.type_name) AS child_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)AS t2 -> ON t1.type_id = t2.type_id -> SET t1.child_count = t2.child_count； mysql> SELECT * FROM tdb_goods_types; +---------+-----------------+-----------+-------------+ | type_id | type_name | parent_id | child_count | +---------+-----------------+-----------+-------------+ | 1 | Household Electric Appliances | 0 | 2 | | 2 | Computer, Office | 0 | 2 | | 3 | Telephone | 1 | 2 | | 4 | Household appliances | 1 | 2 | | 5 | Flat panel TV | 3 | 0 | | 6 | Air conditioner | 3 | 0 | | 7 | Electric fan | 4 | 0 | | 8 | Water dispenser | 4 | 0 | | 9 | Computer machine | 2 | 3 | | 10 | Computer accessories | 2 | 2 | | 11 | Notebook | 9 | 0 | | 12 | Super Ben | 9 | 0 | | 13 | Gamebook | 9 | 0 | | 14 | CPU | 10 | 0 | | 15 | Host | 10 | 0 | +---------+-----------------+-----------+-------------+
11. Multi-table deletion
DELETE tbl_name[.*][,tbl_name[.*]]... FROM table_references [WHERE where_condition]
For example, find duplicate records:
mysql> SELECT goods_id,goods_name FROM tdb_goods GROUP BY goods_name having count(goods_name) >= 2; +----------+-----------------------------+ | goods_id | goods_name | +----------+-----------------------------+ | 18 | HMZ-T3W Head-mounted display | | 19 | Business Backpack | +----------+-----------------------------+
Delete duplicate records
mysql> 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;
12. Reproductive Records
Two records with duplication number 19,20
mysql> SELECT goods_id,goods_name,cate_id,brand_id FROM tdb_goods WHERE goods_id IN (19,20); +----------+------------------------------------+---------+----------+ | goods_id | goods_name | cate_id | brand_id | +----------+------------------------------------+---------+----------+ | 19 | Business Backpack | 6 | 7 | | 20 | X3250 M4 Rack Server 2583 i14 | 3 | 1 | +----------+------------------------------------+---------+----------+
mysql> INSERT tdb_goods(goods_name,cate_id,brand_id) SELECT goods_name,cate_id,brand_id FROM tdb_goods WHERE goods_id IN(19,20);
