MySQL subquery, connection, multi-table update

1. A subquery refers to a SELECT clause in another query statement.

Example:

SELECT * from t1 WHERE column1 = (select Column1 from T2);

Among them, SELECT * from T1 ... Called outer query[] (or outer Statement),

SELECT Column1 from T2 is called Sub query[subquery].

So, we say that the subquery is nested inside the outside of the query. In fact, it is possible to embed subqueries inside subqueries.

The subquery must appear between the parentheses.

Row-level sub-query

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.

Refine subqueries

--Create a data table

CREATE TABLE IF not EXISTS tdb_goods (
goods_id SMALLINT UNSIGNED PRIMARY KEY auto_increment,
Goods_name VARCHAR () not NULL,
Goods_cate VARCHAR (+) not NULL,
Brand_Name VARCHAR (+) not NULL,
Goods_price DECIMAL (15,3) UNSIGNED not NULL DEFAULT 0,
Is_show BOOLEAN not NULL for 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 ' , ' Asus ', ' 3399 ', default,default);

INSERT tdb_goods (Goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES (' y400n 14.0-inch Laptop ', ' notebook ', ' Lenovo ', ' 4899 ', default,default);

INSERT tdb_goods (Goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES (' g150th 15.6 inch game Ben ', ' Game Ben ' , ' Thor ', ' 8499 ', default,default);

INSERT tdb_goods (Goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES (' x550cc 15.6-inch notebooks ', ' notebooks ' , ' Asus ', ' 2799 ', default,default);

INSERT tdb_goods (Goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES (' X240 (20ALA0EYCD) 12.5-inch ultra-polar ', ' Super Ben ', ' Lenovo ', ' 4999 ', default,default;

INSERT tdb_goods (Goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES (' u330p 13.3 ", ' Super Ben ', ' Lenovo ', ' 4299 ', default,default);

INSERT tdb_goods (Goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES (' SVP13226SCB 13.3 inch Touch Extreme ', ' Super Ben ', ' Sony ', ' 7999 ', default,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 PC ', ' tablet ', ' Apple ', ' 1998 ', 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 PC (16G WiFi version) ', ' tablet ', ' apple ', ' 3388 ', default,default;

INSERT tdb_goods (Goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES (' IPad mini me279ch/a equipped with Retina display 7.9 inch Tablet PC (16G WiFi version) ', ' tablet ', ' apple ', ' 2788 ', default,default;

INSERT tdb_goods (Goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES (' Ideacentre C340 20 inch All-in-one PC ', ' desktops ', ' Lenovo ', ' 3499 ', default,default);

INSERT tdb_goods (Goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES (' Vostro 3800-r1206 desktop computer ', ' Desktops ', ' dell ', ' 2899 ', default,default);

INSERT tdb_goods (Goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES (' IMac me086ch/a 21.5 inch One PC ', ' Desktop ', ' apple ', ' 9188 ', default,default;

INSERT tdb_goods (Goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES (' AT7-7414LP desktop PC (i5-3450 four-core 4G 500G 2G single-Display DVD-mouse Linux) ', ' Desktop ', ' Acer ', ' 3699 ', default,default);

INSERT tdb_goods (Goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES (' z220sff f4f06pa workstation ', ' Server/Workstation ', ' hp ', ' 4288 ', default,default);

INSERT tdb_goods (Goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES (' PowerEdge T110 II server ', ' Server/Workstation ', ' dell ', ' 5388 ', default,default);

INSERT tdb_goods (Goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES (' Mac Pro md878ch/a Professional-grade desktop PC ', ' Server/workstation ', ' apple ', ' 28888 ', default,default);

INSERT tdb_goods (Goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES (' hmz-t3w ', ' Notebook accessories ' , ' Sony ', ' 6999 ', default,default);

INSERT tdb_goods (Goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES (' Business backpack ', ' notebook accessories ', ' Sony ', ' , Default,default);

INSERT tdb_goods (Goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES (' X3250 M4 rack-mount Server 2583i14 ', ' Server/Workstation ', ' IBM ', ' 6888 ', default,default);

INSERT tdb_goods (Goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES (' Xuan Dragon Elite Edition Notebook Radiator ', ' notebook accessories ', ' Kyushu winds ', ', default,default);

INSERT tdb_goods (Goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES (' hmz-t3w ', ' Notebook accessories ' , ' Sony ', ' 6999 ', default,default);

INSERT tdb_goods (Goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES (' Business backpack ', ' notebook accessories ', ' Sony ', ' , Default,default);

--average price for all computer products, and retain two decimal places, avg,max,min, COUNT, sum as aggregate function

SELECT ROUND (AVG (Goods_price), 2) as Avg_price from Tdb_goods;

--Query all items with prices greater than the average price and sort 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;

--Using subqueries to implement

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 query type is "super-book" The Price of the commodity

SELECT Goods_price from tdb_goods WHERE goods_cate = ' Super Ben ';

--Query for items with prices greater than or equal to "super" prices, and sorted by price descending

SELECT Goods_id,goods_name,goods_price from Tdb_goods

where Goods_price = any (SELECT goods_price from tdb_goods WHERE goods_cate = ' Super Ben ')

ORDER by Goods_price DESC;

--= any or = SOME 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 = ' Super Ben ')

ORDER by Goods_price DESC;

--Create a "commodity classification" table

CREATE TABLE IF not EXISTS tdb_goods_cates (

cate_id SMALLINT UNSIGNED PRIMARY KEY auto_increment,

Cate_name VARCHAR (40)

);

--Query all records of the Tdb_goods table and GROUP by category

SELECT goods_cate from Tdb_goods GROUP by Goods_cate;

--Write the grouped results 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 table with the Tdb_goods_cates data sheet

UPDATE tdb_goods INNER JOIN tdb_goods_cates on goods_cate = Cate_name

SET goods_cate = cate_id;

--Through Create ... Select 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 (+) not NULL

) SELECT Brand_Name from Tdb_goods GROUP by Brand_Name;

--Update Tdb_goods data table (Error) by Tdb_goods_brands data sheet

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 data table structure of the Tdb_goods

DESC Tdb_goods;

--Modify the data table structure with the 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 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 arbitrary records 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 ');

--Query the details of all items (implemented via an internal connection)

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 all product details (implemented via left outer connection)

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 joins Tdb_goods_brands as B on g.brand_id = B.brand_id\g;

--Query all product details (implemented via right outer connection)

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;

--data table design with infinite classification

CREATE TABLE Tdb_goods_types (
type_id SMALLINT UNSIGNED PRIMARY KEY auto_increment,
Type_name VARCHAR () not NULL,
parent_id SMALLINT UNSIGNED not NULL DEFAULT 0
);

INSERT tdb_goods_types (type_name,parent_id) VALUES (' Home appliance ', DEFAULT);
INSERT tdb_goods_types (type_name,parent_id) VALUES (' Computer, Office ', DEFAULT);
INSERT tdb_goods_types (type_name,parent_id) VALUES (' Everyone electricity ', 1);
INSERT tdb_goods_types (type_name,parent_id) VALUES (' Life appliances ', 1);
INSERT tdb_goods_types (type_name,parent_id) VALUES (' Flat screen TV ', 3);
INSERT tdb_goods_types (type_name,parent_id) VALUES (' Air conditioning ', 3);
INSERT tdb_goods_types (type_name,parent_id) VALUES (' 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 (' Game Ben ', 9);
INSERT tdb_goods_types (type_name,parent_id) VALUES (' CPU ', 10);
INSERT tdb_goods_types (type_name,parent_id) VALUES (' Host ', ten);

--Find 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

--Find all classes and their subclasses

SELECT p.type_id,p.type_name,s.type_name from Tdb_goods_types as P left joins Tdb_goods_types as s on s.parent_id = P.type_ Id

--Find 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 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 subclasses you just 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 the two records numbered 12,20

SELECT * from Tdb_goods WHERE goods_id in (19,20);

--INSERT ... Select for replication

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

--Find duplicate records

SELECT goods_id,goods_name from Tdb_goods GROUP by Goods_name have 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 have count (goods _name) >= 2) as t2 on t1.goods_name = T2.goods_name WHERE t1.goods_id > t2.goods_id;

MySQL subquery, connection, multi-table update