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) 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 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 (' Living electrical ', 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 (' Notebooks ', 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 ', 10);
--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_good S_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;
