Shulou(Shulou.com)05/31 Report--

Editor to share with you how to write the MySQL sub-query sentence. I hope you will get something after reading this article. Let's discuss it together.

The MySQL subquery statement refers to the SELECT clause in another query statement, such as "SELECT * FROM T1 WHERE column1 = (SELECT column1 FROM T2);".

This article operating environment: Windows7 system, mysql5.7 version, Dell G3 computer.

Mysql subquery statement:

1. A subquery is 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 [external query] (or Outer Statement)

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

Therefore, we say that the subquery is nested inside the outer query. In fact, it is possible to nest subqueries within the subquery.

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

The return result of a row-level subquery is at most one row.

Optimized subquery

-- 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 (15pr 3) UNSIGNED NOT NULL DEFAULT 0, is_show BOOLEAN NOT NULL DEFAULT 1, is_saleoff BOOLEAN NOT NULL DEFAULT 0)

-- write record

INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES ('R510VC 15.6in Notebook', 'Notebook', 'Asustek', '33990inch Notebook'); INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES ('Y400N 14.0inch Notebook', 'Notebook', 'Lenovo', '4899PhoneDeFAULTMagneDeFAULTS') INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES ('G150TH 15.6in game book', 'game book', 'Raytheon', '8499mm notebook'); INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES ('X550CC 15.6in notebook', 'notebook', 'Asustek', '2799'' memorialDEFAULTS 'default) INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES ('X240 (20ALA0EYCD) 12.5-inch ultrabook', 'superbook', 'Lenovo', '4999 ultrabook'); INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES ('U330P 13.3-inch ultrabook', 'ultrabook', 'Lenovo', '4299 ultrabook') INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES ('SVP13226SCB 13.3in Touch Ultrabook', 'ultrabook', 'Sony', '7999DFULT.DEFAULT) INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES ('iPad mini MD531CH/A 7.9in tablet', 'tablet', 'Apple', '1998 calendar defaults) INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES ('iPad Air MD788CH/A 9.7in tablet (16G WiFi version)', 'tablet', 'Apple', '3388') INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES ('iPad mini ME279CH/An equipped with Retina display 7.9in tablet (16G WiFi version)', 'tablet', 'Apple', '2788' INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES ('IdeaCentre C340 20-inch all-in-one computer', 'desktop computer', 'Lenovo', '3499 desktop computer'); INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES ('Vostro 3800-R1206 desktop computer', 'desktop computer', 'Dell', '2899 desktop computer', '2899 desktop computer') INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES ('iMac ME086CH/A 21. 5-inch all-in-one computer', 'desktops', 'Apple', '9188 calendar defaults) 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 DVD mouse Linux)', 'desktop computer', 'Acer', '3699 memory defaults INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES ('Z220SFF F4F06PA Workstation', 'Server / Workstation', 'Hewlett-Packard', '4288 VALUES defaults); INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES (' PowerEdge T110 Server', 'Server / Workstation', 'Dell', '5388 Default Magazine 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 professional desktop computer defaults) INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES ('HMZ-T3W head-mounted display device', 'Notebook Accessories', 'Sony', '6999DFAULTMagi default); INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES (' Business backpack', 'Notebook Accessories', 'Sony','99 'DFULTMAG defaults) INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES ('X3250 M4 Rack 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 Notebook Radiator', 'Notebook Accessories', 'Kyushu 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', 'Notebook Accessories', 'Sony', '6999DFAULTMagi default); INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES (' Business backpack', 'Notebook Accessories', 'Sony','99 'DFULTMAG defaults)

Find the average price of all computer products and keep two decimal places. AVG,MAX,MIN, COUNT and SUM are 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 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

-- query the price of goods with the type of "super diary"

SELECT goods_price FROM tdb_goods WHERE goods_cate = 'ultrabook'

-- query for goods whose price is greater than or equal to the price of "ultrabook" and arrange them in descending order of price

SELECT goods_id,goods_name,goods_price FROM tdb_goods WHERE goods_price = ANY (SELECT goods_price FROM tdb_goods WHERE goods_cate = 'ultrabook') 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 = 'ultrabook') 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 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 table through the tdb_goods_cates data table

UPDATE tdb_goods INNER JOIN tdb_goods_cates ON goods_cate = cate_name SET goods_cate = cate_id

Create a data table through CREATE...SELECT 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

-- updating the tdb_goods data Table through the tdb_goods_brands 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 data table structure of tdb_goods

DESC tdb_goods

Modify the structure of the data table 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 INSERT tdb_goods_cates (cate_name) VALUES ('router'), ('switch'), ('network card') in tdb_goods_cates and tdb_goods_brands tables respectively; INSERT tdb_goods_brands (brand_name) VALUES ('Haier'), ('Tsinghua Tongfang'), ('Shenzhou')

-- write any record 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')

-- query the details of all goods (via 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 the details of all items (via the left outer link)

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 goods (via the right outer link)

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 (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 ('Home Appliances', 1) INSERT tdb_goods_types (type_name,parent_id) VALUES ('household 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 ('electric fan', 4) INSERT tdb_goods_types (type_name,parent_id) VALUES ('drinking fountain', 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 ('ultrabook', 9); INSERT tdb_goods_types (type_name,parent_id) VALUES ('game book', 9); INSERT tdb_goods_types (type_name,parent_id) VALUES ('CPU',10); INSERT tdb_goods_types (type_name,parent_id) VALUES (' mainframe', 10)

-- find all categories and their parents

SELECT s.typewritten 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.typeaccounidpr p.typeroomnamegamers.typeroomname FROM tdb_goods_types AS p LEFT JOIN tdb_goods_types AS s ON s.parent_id = p.type_id

-- find the number of all categories and their subclasses

SELECT p.typewriter _

-- add a 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 just queried to the tdb_goods_types data table

UPDATE tdb_goods_types AS T1 INNER JOIN (SELECT p.typeaccounidjournal p.typewriter 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 125.20

SELECT * FROM tdb_goods WHERE goods_id IN (195.20)

-INSERT... Replication by SELECT

INSERT tdb_goods (goods_name,cate_id,brand_id) SELECT goods_name,cate_id,brand_id FROM tdb_goods WHERE goods_id IN

-- 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.goodsreaders; after reading this article, I believe you have a certain understanding of "how to write MySQL sub-query sentences". If you want to know more about it, welcome to follow the industry information channel, thank you for reading!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.