MySQl subquery, left-right connection, multi-table connection learning NOTE _ MySQL

MySQl subquery, left-right join, and multi-table join learning Notes 1. subquery refers to the SELECT clause in another query statement.

Example:

SELECT * FROM t1 WHERE column1 = (SELECT column1 FROM t2 );

SELECT * FROM t1... is called Outer Query [external Query] (or Outer Statement ),

SELECT column1 FROM t2 is called Sub Query [subquery].

Therefore, subqueries are nested inside external queries. In fact, it is possible to nest the subquery within the subquery.

The subquery must appear between parentheses.

Row-level subquery

SELECT * FROM t1 WHERE (col1, col2) = (SELECT col3, col4 FROM t2 WHERE id = 10 );

SELECT * FROM t1 where row (col1, col2) = (SELECT col3, col4 FROM t2 WHERE id = 10 );

A row-level subquery returns a maximum of one row.

Optimize subqueries

-- Create a data table

Create table if not exists tdb_goods (

Goods_id smallint unsigned primary key AUTO_INCREMENT,

Goods_name VARCHAR (150) not null,

Goods_cate VARCHAR (40) not null,

Brand_name VARCHAR (40) not null,

Goods_price DECIMAL (15, 3) unsigned not null default 0,

Is_show boolean not null default 1,

Is_saleoff boolean not null default 0

);

-- Write records

INSERT tdb_goods (goods_name, goods_cate, brand_name, goods_price, is_show, is_saleoff) VALUES ('r510vc 15.6 inch notebooks ', 'notebooks', 'asuste', '123', DEFAULT, DEFAULT );

INSERT tdb_goods (goods_name, goods_cate, brand_name, goods_price, is_show, is_saleoff) VALUES ('y400n 14.0 inch laptops ', 'notebooks', 'Lenovo ', '123', DEFAULT, DEFAULT );

INSERT tdb_goods (goods_name, goods_cate, brand_name, goods_price, is_show, is_saleoff) VALUES ('g150th 15.6 inch gamepy', 'gamepy', 'raytheon ', '123', DEFAULT, DEFAULT );

INSERT tdb_goods (goods_name, goods_cate, brand_name, goods_price, is_show, is_saleoff) VALUES ('x550cc 15.6 inch notebooks ', 'notebooks', 'asuste', '123', DEFAULT, DEFAULT );

INSERT tdb_goods (goods_name, goods_cate, brand_name, goods_price, is_show, is_saleoff) VALUES ('x240 (20ALA0EYCD) 12.5 inch ultrabode', 'superice', 'Lenovo ', '2016', DEFAULT, DEFAULT );

INSERT tdb_goods (goods_name, goods_cate, brand_name, goods_price, is_show, is_saleoff) VALUES );

INSERT tdb_goods (goods_name, goods_cate, brand_name, goods_price, is_show, is_saleoff) VALUES, DEFAULT );

INSERT tdb_goods (goods_name, goods_cate, brand_name, goods_price, is_show, is_saleoff) VALUES ('iPad mini MD531CH/A 7.9 inch tablet ', 'tablet', 'apple ', '123', DEFAULT, DEFAULT );

INSERT tdb_goods (goods_name, goods_cate, brand_name, goods_price, is_show, is_saleoff) VALUES ('iPad Air MD788CH/A 9.7 inch tablet (16G WiFi) ', 'tablet ', 'Apple', '123', DEFAULT, DEFAULT );

INSERT tdb_goods (goods_name, goods_cate, brand_name, goods_price, is_show, is_saleoff) VALUES ('iPad mini ME279CH/A with Retina display 7.9 inch tablet (16G WiFi )', 'tablet ID', 'apple', '123', DEFAULT, DEFAULT );

INSERT tdb_goods (goods_name, goods_cate, brand_name, goods_price, is_show, is_saleoff) VALUES ('ideacentre C340 20 inch one', 'desktops ', 'Lenovo', '123', DEFAULT, DEFAULT );

INSERT tdb_goods (goods_name, goods_cate, brand_name, goods_price, is_show, is_saleoff) VALUES ('vostro 3800-R1206 desktops ', 'desktops', 'Dell ', '123', DEFAULT, DEFAULT );

INSERT tdb_goods (goods_name, goods_cate, brand_name, goods_price, is_show, is_saleoff) VALUES ('imac ME086CH/A 21.5 inch PC ', 'desktops', 'apple', '123 ', DEFAULT, DEFAULT );

INSERT tdb_goods (goods_name, goods_cate, brand_name, goods_price, is_show, is_saleoff) VALUES ('at7-7414LP desktop computer (i5-3450 Quad Core 4G 500G 2G dedicated display DVD mouse Linux )', 'desktops ', 'acer', '123', DEFAULT, DEFAULT );

INSERT tdb_goods (goods_name, goods_cate, brand_name, goods_price, is_show, is_saleoff) VALUES ('z220sff F4F06PA workws', 'server/workws', 'HP ', '2016', DEFAULT, DEFAULT );

INSERT tdb_goods (goods_name, goods_cate, brand_name, goods_price, is_show, is_saleoff) VALUES ('poweredge T110 II server ', 'server/workws', 'Dell', '123', DEFAULT, DEFAULT );

INSERT tdb_goods (goods_name, goods_cate, brand_name, goods_price, is_show, is_saleoff) VALUES ('Mac Pro MD878CH/A professional-level desktops ', 'Servers/workws', 'apple ', '123', DEFAULT, DEFAULT );

INSERT tdb_goods (goods_name, goods_cate, brand_name, goods_price, is_show, is_saleoff) VALUES, DEFAULT );

INSERT tdb_goods (goods_name, goods_cate, brand_name, goods_price, is_show, is_saleoff) VALUES ('business backpack ', 'laptop accessories', 'Sony ', '99', DEFAULT, DEFAULT );

INSERT tdb_goods (goods_name, goods_cate, brand_name, goods_price, is_show, is_saleoff) VALUES ('x3250 M4 rack server 2583i14 ', 'server/workws', 'IBM', '123456 ', DEFAULT, DEFAULT );

INSERT tdb_goods (goods_name, goods_cate, brand_name, goods_price, is_show, is_saleoff) VALUES, DEFAULT );

INSERT tdb_goods (goods_name, goods_cate, brand_name, goods_price, is_show, is_saleoff) VALUES, DEFAULT );

INSERT tdb_goods (goods_name, goods_cate, brand_name, goods_price, is_show, is_saleoff) VALUES ('business backpack ', 'laptop accessories', 'Sony ', '99', DEFAULT, DEFAULT );

-- Calculate the average price of all computer products, and retain two decimal places, AVG, MAX, MIN, COUNT, and SUM as aggregate functions

Select round (AVG (goods_price), 2) AS avg_price FROM tdb_goods;

-- Query all goods whose prices are higher than the average price and sort them by Price in descending order

SELECT goods_id, goods_name, goods_price FROM tdb_goods WHERE goods_price> 5845.10 order by goods_price DESC;

-- Implement with subquery

SELECT goods_id, goods_name, goods_price FROM tdb_goods

WHERE goods_price> (select round (AVG (goods_price), 2) AS avg_price FROM tdb_goods)

Order by goods_price DESC;

-- The price of the item whose query type is "supernote"

SELECT goods_price FROM tdb_goods WHERE goods_cate = 'superscript ';

-- Query commodities whose prices are greater than or equal to the "super" price, and sort them by Price in descending order

SELECT goods_id, goods_name, goods_price FROM tdb_goods

WHERE goods_price = ANY (SELECT goods_price FROM tdb_goods WHERE goods_cate = 'superscript ')

Order by goods_price DESC;

-- = Any or = SOME is equivalent to IN

SELECT goods_id, goods_name, goods_price FROM tdb_goods

WHERE goods_price IN (SELECT goods_price FROM tdb_goods WHERE goods_cate = 'superscript ')

Order by goods_price DESC;

-- Create a "product category" table

Create table if not exists tdb_goods_cates (

Cate_id smallint unsigned primary key AUTO_INCREMENT,

Cate_name VARCHAR (40)

);

-- Query all records in the tdb_goods table and group them by category

SELECT goods_cate FROM tdb_goods group by goods_cate;

-- Write the grouping result to the tdb_goods_cates data table

INSERT tdb_goods_cates (cate_name) SELECT goods_cate FROM tdb_goods group by goods_cate;

-- Update the tdb_goods_cates table

UPDATE tdb_goods inner join tdb_goods_cates ON goods_cate = cate_name

SET goods_cate = cate_id;

-- CREATE... SELECT is used to CREATE a data table and write records at the same time.

-- SELECT brand_name FROM tdb_goods group by brand_name;

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;

-- Use the tdb_goods_brands data table to update the tdb_goods data table (error)

UPDATE tdb_goods inner join tdb_goods_brands ON brand_name = brand_name

SET brand_name = brand_id;

-- Column 'brand _ name' in field list is ambigous

-- Correct

UPDATE tdb_goods AS g inner join tdb_goods_brands AS B ON g. brand_name = B. brand_name

SET g. brand_name = B. brand_id;

-- View the table structure of tdb_goods

DESC tdb_goods;

-- Alter table statement

Alter table tdb_goods

CHANGE goods_cate cate_id smallint unsigned not null,

CHANGE brand_name brand_id smallint unsigned not null;

-- Insert 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 arbitrary 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 ');

-- Query the details of all products (implemented through internal connections) as follows:

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;

-- Query the details of all products (implemented through left outer join)

SELECT goods_id, goods_name, cate_name, brand_name, goods_price FROM tdb_goods AS g

Left join tdb_goods_cates AS c ON g. cate_id = c. cate_id

Left join tdb_goods_brands AS B ON g. brand_id = B. brand_id/G;

-- Query the details of all products (implemented by right outer join)

SELECT goods_id, goods_name, cate_name, brand_name, goods_price FROM tdb_goods AS g

Right join tdb_goods_cates AS c ON g. cate_id = c. cate_id

Right join tdb_goods_brands AS B ON g. brand_id = B. brand_id/G;

-- Unlimited data table design

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 appliances ', DEFAULT );

INSERT tdb_goods_types (type_name, parent_id) VALUES ('computer, office ', DEFAULT );

INSERT tdb_goods_types (type_name, parent_id) VALUES ('household appliances ', 1 );

INSERT tdb_goods_types (type_name, parent_id) VALUES ('household electric appliance ', 1 );

INSERT tdb_goods_types (type_name, parent_id) VALUES ('flat panel TV ', 3 );

INSERT tdb_goods_types (type_name, parent_id) VALUES ('airconditioner ', 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 ID', 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 ('superscript', 9 );

INSERT tdb_goods_types (type_name, parent_id) VALUES ('game Bene', 9 );

INSERT tdb_goods_types (type_name, parent_id) VALUES ('CPU ', 10 );

INSERT tdb_goods_types (type_name, parent_id) VALUES ('host', 10 );

-- Search for 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;

-- Search for 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;

-- Search for the number of all categories 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;

-- Add the child_count field for tdb_goods_types

Alter table tdb_goods_types ADD child_count mediumint unsigned not null default 0;

-- Update the number of subclass queried to the tdb_goods_types data table.

UPDATE tdb_goods_types AS t1 inner join (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) AS t2

ON t1.type _ id = t2.type _ id

SET t1.child _ count = t2.children _ count;

-- Copy two records numbered 12 and 20

SELECT * FROM tdb_goods WHERE goods_id IN (19,20 );

-- INSERT... SELECT

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

-- Query duplicate records

SELECT goods_id, goods_name FROM tdb_goods group by goods_name HAVING count (goods_name)> = 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 HAVING count (goods_name)> = 2) AS t2 ON t1.goods _ name = t2.goods _ name WHERE t1.goods _ id> t2.goods _ id;