What is a mysql subquery statement?-Mysql Tutorial-php.cn

Mysql subquery statement refers to the SELECT clause in another query statement, for example, "SELECT * FROM t1 WHERE column1 = (SELECT column1 FROM t2);".

What is a mysql subquery statement?

The operating environment of this article: Windows 7 system, mysql version 5.7, Dell G3 computer.

What is mysql subquery statement?

mysql Subquery statement:

1. Subquery refers to the SELECT clause in another query statement.

Example:

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

Copy after login

Among them, SELECT * FROM t1 ... is called Outer 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 subqueries.

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

Copy after login

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

Copy after login

--Write records

INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('R510VC 15.6英寸笔记本','笔记本','华硕','3399',DEFAULT,DEFAULT); INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('Y400N 14.0英寸笔记本电脑','笔记本','联想','4899',DEFAULT,DEFAULT); INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('G150TH 15.6英寸游戏本','游戏本','雷神','8499',DEFAULT,DEFAULT); INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('X550CC 15.6英寸笔记本','笔记本','华硕','2799',DEFAULT,DEFAULT); INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('X240(20ALA0EYCD) 12.5英寸超极本','超级本','联想','4999',DEFAULT,DEFAULT); INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('U330P 13.3英寸超极本','超级本','联想','4299',DEFAULT,DEFAULT); INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('SVP13226SCB 13.3英寸触控超极本','超级本','索尼','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英寸平板电脑','平板电脑','苹果','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英寸平板电脑 （16G WiFi版）','平板电脑','苹果','3388',DEFAULT,DEFAULT); INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES(' iPad mini ME279CH/A 配备 Retina 显示屏 7.9英寸平板电脑 （16G WiFi版）','平板电脑','苹果','2788',DEFAULT,DEFAULT); INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('IdeaCentre C340 20英寸一体电脑 ','台式机','联想','3499',DEFAULT,DEFAULT); INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('Vostro 3800-R1206 台式电脑','台式机','戴尔','2899',DEFAULT,DEFAULT); INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('iMac ME086CH/A 21.5英寸一体电脑','台式机','苹果','9188',DEFAULT,DEFAULT); INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('AT7-7414LP 台式电脑 （i5-3450四核 4G 500G 2G独显 DVD 键鼠 Linux ）','台式机','宏碁','3699',DEFAULT,DEFAULT); INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('Z220SFF F4F06PA工作站','服务器/工作站','惠普','4288',DEFAULT,DEFAULT); INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('PowerEdge T110 II服务器','服务器/工作站','戴尔','5388',DEFAULT,DEFAULT); INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('Mac Pro MD878CH/A 专业级台式电脑','服务器/工作站','苹果','28888',DEFAULT,DEFAULT); INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES(' HMZ-T3W 头戴显示设备','笔记本配件','索尼','6999',DEFAULT,DEFAULT); INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('商务双肩背包','笔记本配件','索尼','99',DEFAULT,DEFAULT); INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('X3250 M4机架式服务器 2583i14','服务器/工作站','IBM','6888',DEFAULT,DEFAULT); INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('玄龙精英版 笔记本散热器','笔记本配件','九州风神','',DEFAULT,DEFAULT); INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES(' HMZ-T3W 头戴显示设备','笔记本配件','索尼','6999',DEFAULT,DEFAULT); INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('商务双肩背包','笔记本配件','索尼','99',DEFAULT,DEFAULT);

Copy after login

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

Copy after login

--Query all products whose price is 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;

Copy after login

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

Copy after login

-- Query the price of products with the type "Super Notebook"

SELECT goods_price FROM tdb_goods WHERE goods_cate = '超级本';

Copy after login

-- Query the price is greater than or equal to Products priced at "Ultrabook", 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 = '超级本') ORDER BY goods_price DESC;

Copy after login

-- = 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 = '超级本') ORDER BY goods_price DESC;

Copy after login

--Create the "goods classification" table

CREATE TABLE IF NOT EXISTS tdb_goods_cates( cate_id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT, cate_name VARCHAR(40) );

Copy after login

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

SELECT goods_cate FROM tdb_goods GROUP BY goods_cate;

Copy after login

--Write the grouping results to the tdb_goods_cates data table

INSERT tdb_goods_cates (cate_name) SELECT goods_cate FROM tdb_goods GROUP BY goods_cate;

Copy after login

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

Copy after login

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

Copy after login

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

Copy after login

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

Copy after login

-- View the data table structure of tdb_goods

DESC tdb_goods;

Copy after login

-- Modify the data 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; -- 分别在tdb_goods_cates和tdb_goods_brands表插入记录 INSERT tdb_goods_cates(cate_name) VALUES('路由器'),('交换机'),('网卡'); INSERT tdb_goods_brands(brand_name) VALUES('海尔'),('清华同方'),('神舟');

Copy after login

-- Write any record in the tdb_goods data table

INSERT tdb_goods(goods_name,cate_id,brand_id,goods_price) VALUES(' LaserJet Pro P1606dn 黑白激光打印机','12','4','1849');

Copy after login

-- Query the detailed information of all products (implemented through inner joins)

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;

Copy after login

-- Query the detailed information 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;

Copy after login

-- Query the detailed information of all products (implemented through 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;

Copy after login

-- Unlimited Classified 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('家用电器',DEFAULT); INSERT tdb_goods_types(type_name,parent_id) VALUES('电脑、办公',DEFAULT); INSERT tdb_goods_types(type_name,parent_id) VALUES('大家电',1); INSERT tdb_goods_types(type_name,parent_id) VALUES('生活电器',1); INSERT tdb_goods_types(type_name,parent_id) VALUES('平板电视',3); INSERT tdb_goods_types(type_name,parent_id) VALUES('空调',3); INSERT tdb_goods_types(type_name,parent_id) VALUES('电风扇',4); INSERT tdb_goods_types(type_name,parent_id) VALUES('饮水机',4); INSERT tdb_goods_types(type_name,parent_id) VALUES('电脑整机',2); INSERT tdb_goods_types(type_name,parent_id) VALUES('电脑配件',2); INSERT tdb_goods_types(type_name,parent_id) VALUES('笔记本',9); INSERT tdb_goods_types(type_name,parent_id) VALUES('超级本',9); INSERT tdb_goods_types(type_name,parent_id) VALUES('游戏本',9); INSERT tdb_goods_types(type_name,parent_id) VALUES('CPU',10); INSERT tdb_goods_types(type_name,parent_id) VALUES('主机',10);

Copy after login

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

Copy after login

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

Copy after login

-- Find the number of all categories and their subcategories

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;

Copy after login

-- Add the child_count field to tdb_goods_types

ALTER TABLE tdb_goods_types ADD child_count MEDIUMINT UNSIGNED NOT NULL DEFAULT 0;

Copy after login

-- Update the number of subcategories 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 after login

-- Copy two records numbered 12 and 20

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

Copy after login

-- INSERT ... SELECT to achieve 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);

Copy after login

-- Find duplicate records

SELECT goods_id,goods_name FROM tdb_goods GROUP BY goods_name HAVING count(goods_name) >= 2;

Copy after login

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

Copy after login

[Related recommendations:mysql video tutorial]

The above is the detailed content of What is a mysql subquery statement?. For more information, please follow other related articles on the PHP Chinese website!