English | 简体中文 | 繁體中文 | Русский язык | Français | Español | Português | Deutsch | 日本語 | 한국어 | Italiano | بالعربية
No more chatter, let's directly post the usage of total subquery in MySQL database to everyone.
The code is described as follows:
</pre><pre name="code" class="sql">1.A subquery refers to the SELECT clause within another query statement.
Example sentence:
SELECT * FROM t1 WHERE column1 = (SELECT column1 FROM t2);
Among which, SELECT * FROM t1 ...Called Outer Query[Outer Query](or Outer Statement),
SELECT column1 FROM t2 Called Sub Query[Subquery].
Therefore, we say that the subquery is nested within the outer query. In fact, it may be nested within another subquery.
The subquery must appear within 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);
The return result of a row-level subquery is at most one row.
Optimize Subquery
-- Create 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
);
-- Record Writing
INSERT INTO tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('R510VC 15.6Inch Notebook3399',DEFAULT,DEFAULT);
INSERT INTO tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('Y400N 14.0 Inch Laptop4899',DEFAULT,DEFAULT);
INSERT INTO tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('G150TH 15.6Inch Gaming Notebook8499',DEFAULT,DEFAULT);
INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('X550CC 15.6Inch Notebook2799',DEFAULT,DEFAULT);
INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('X240(2Inch Tablet PC 12.5A Equipped with Retina Display4999',DEFAULT,DEFAULT);
INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('iPad Air MD33INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('iPad mini ME 13.3A Equipped with Retina Display4299',DEFAULT,DEFAULT);
Inch Tablet PC (13226 13.3Apple7999',DEFAULT,DEFAULT);
Tablet PC531CH/A 7.9G WiFi Edition)1998',DEFAULT,DEFAULT);
INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('IdeaCentre C788CH/A 9.7Desktop Computer160-inch All-in-One Computer3388',DEFAULT,DEFAULT);
279CH/Lenovo 7.9Desktop Computer160-inch All-in-One Computer2788',DEFAULT,DEFAULT);
INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('Vostro340 2003499',DEFAULT,DEFAULT);
R 38-Dell1206 Desktop Computer2899',DEFAULT,DEFAULT);
INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('iMac ME086CH/A 21.5inch all-in-one computer','Desktop PC','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 discrete graphics DVD keyboard and mouse Linux ）','Desktop PC','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 Desktop Computer','Server/Workstation','Apple','28888',DEFAULT,DEFAULT);
INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES(' HMZ-T3W Head-Mounted Display Device','Laptop Accessories','Sony','6999',DEFAULT,DEFAULT);
INSERT INTO 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 M4Rackmount Server 2583i14','Server/Workstation','IBM','6888',DEFAULT,DEFAULT);
INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('Xuanlong Elite Edition Laptop Cooling Pad','Laptop Accessories','Jiuzhou Fengshen','',DEFAULT,DEFAULT);
INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES(' HMZ-T3W Head-Mounted Display Device','Laptop Accessories','Sony','6999',DEFAULT,DEFAULT);
INSERT INTO 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 keep two decimal places, AVG, MAX, MIN, COUNT, SUM are aggregate functions
SELECT ROUND(AVG(goods_price),2) AS avg_price FROM tdb_goods;
-- Query all products with prices greater than the average price, and sorted in descending order by price
SELECT goods_id,goods_name,goods_price FROM tdb_goods WHERE goods_price > 5845.10 ORDER BY goods_price DESC;
-- Use 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;
-- Query the price of products of type 'Super Notebook'
SELECT goods_price FROM tdb_goods WHERE goods_cate = 'Super Notebook';
-- Query products with prices greater than or equal to 'Super Notebook' prices, and sorted in descending order by price
SELECT goods_id,goods_name,goods_price FROM tdb_goods
WHERE goods_price = ANY(SELECT goods_price FROM tdb_goods WHERE goods_cate = 'Super Notebook')
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 = 'Super Notebook')
ORDER BY goods_price DESC;
-- Create the '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 by 'category'
SELECT goods_cate FROM tdb_goods GROUP BY goods_cate;
-- Write the grouped results into the tdb_goods_cates table
INSERT tdb_goods_cates (cate_name) SELECT goods_cate FROM tdb_goods GROUP BY goods_cate;
-- Update the tdb_goods table through the tdb_goods_cates table
UPDATE tdb_goods INNER JOIN tdb_goods_cates ON goods_cate = cate_name
SET goods_cate = cate_id ;
-- Create the table and write records at the same time through CREATE...SELECT
-- 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;
-- Update the tdb_goods table through the tdb_goods_brands 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 ambiguous
-- 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;
-- Check the data structure of the tdb_goods table
DESC tdb_goods;
-- Modify the table structure through 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 into the tdb_goods_cates and tdb_goods_brands tables separately
INSERT INTO tdb_goods_cates(cate_name) VALUES('Router'),('Switch'),('Network Card');
INSERT INTO tdb_goods_brands(brand_name) VALUES('Haier'),('Tsinghua Tongfang'),('Hasee');
-- Insert any record into the tdb_goods table
INSERT INTO tdb_goods(goods_name,cate_id,brand_id,goods_price) VALUES('LaserJet Pro P'1606dn Black and White Laser Printer','12','4','1849);
-- Query all product details (implemented through an inner join)
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 through a 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 all product details (implemented through a 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;
-- Design of an infinite classification data table
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('Home Appliances',DEFAULT);
INSERT tdb_goods_types(type_name,parent_id) VALUES('Computers & Office',DEFAULT);
INSERT tdb_goods_types(type_name,parent_id) VALUES('Major Appliances',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('Complete Computer',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('Ultra-thin Laptop',9);
INSERT tdb_goods_types(type_name,parent_id) VALUES('Game Laptop',9);
INSERT INTO tdb_goods_types(type_name,parent_id) VALUES('CPU',10);
INSERT INTO tdb_goods_types(type_name,parent_id) VALUES('主机',10);
-- Find all categories and their parent categories
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 categories and their subcategories
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;
-- Find the number of subcategories for all categories
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 to tdb_goods_types
ALTER TABLE tdb_goods_types ADD child_count MEDIUMINT UNSIGNED NOT NULL DEFAULT 0;
-- Update the number of child categories 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;
-- Duplicate number of12,2Two records of 0
SELECT * FROM tdb_goods WHERE goods_id IN (19,20);
-- INSERT ... SELECT for duplication
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 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;
That's all for the introduction of subquery usage in MySQL, I hope it will be helpful to everyone!