Use of Mysql database neutron query, mysql database neutron

Use of Mysql database neutron query, mysql database neutron

Let's talk a little bit about it. Let's talk about the use of mysql database subqueries.

The Code is as follows:

</Pre> <pre name = "code" class = "SQL"> limit 1. subquery refers to the SELECT clause in another query statement. Example: SELECT * FROM t1 WHERE column1 = (SELECT column1 FROM t2); WHERE, SELECT * FROM t1... it is called Outer Query [External Query] (or Outer Statement), and SELECT column1 FROM t2 is called Sub Query [subquery]. Therefore, subqueries are nested inside external queries. In fact, it is possible to nest the subquery within the subquery. The subquery 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); a row-level subquery returns a maximum of one row. Optimize subquery -- 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 ); -- INSERT tdb_goods (goods_name, goods_cate, brand_name, goods_price, is_show, is_saleoff) VALUES ('r510vc 15.6 inch notebooks ', 'notebooks', 'asus ', '123', DEFAULT, DEFAULT); INSERT tdb_goods (goods_name, goods_cate, brand_name, goods_price, is_show, is_saleoff) VALUES ('y400n 14.0 inch laptops ', 'notebooks', 'lenovo ', '123 ', DEFAULT, DEFAULT); INSERT tdb_goods (goods_name, goods_cate, brand_name, goods_price, is_show, is_saleoff) VALUES ('g150th 15.6 inch gamepy', 'gamepy', 'raytheon ', '2016 ', 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 ', '123 ', DEFAULT, DEFAULT); INSERT tdb_goods (goods_name, goods_cate, brand_name, goods_price, is_show, is_saleoff) VALUES ('x240 (20ALA0EYCD) 12.5 inch ultrabode', 'superscript', 'Association ', '20140901', DEFAULT, DEFAULT); INSERT tdb_goods (goods_name, goods_cate, brand_name, goods_price, is_show, is_saleoff) VALUES ('u330p 4999 Ultrabook ', 'superscript', 'lenovo ', '20140901', DEFAULT, DEFAULT); INSERT tdb_goods (goods_name, goods_cate, brand_name, goods_price, is_show, is_saleoff) VALUES ('svp13226scb 4299 touch ultrabode', 'superscript ', 'sony ', '000000', 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 ', 'tablet', 'apple', '000000', 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 (16G WiFi) ', 'tablet', 'apple', '123', DEFAULT, DEFAULT); INSERT tdb_goods (goods_name, goods_cate, brand_name, goods_price, is_show, is_saleoff) VALUES ('ipad mini ME279CH/A with Retina Display 7.9 inch tablet (16G WiFi )', 'tablet pcs', 'apple', '123', 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 id', 'desktops ', 'lenovo', '000000', DEFAULT, DEFAULT); INSERT tdb_goods (goods_name, goods_cate, brand_name, goods_price, is_show, is_saleoff) VALUES ('vostro 3800-R1206 desktops ', 'desktops', 'Dell ', '123', DEFAULT, DEFAULT); INSERT tdb_goods (goods_name, goods_cate, brand_name, goods_price, is_show, is_saleoff) VALUES ('imac ME086CH/A 21.5 inch-in-One IDCs ', 'desktops', 'apple', '123', DEFAULT, DEFAULT); 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 dedicated display DVD mouse Linux) ', 'desktop', 'acer ', '20140901', DEFAULT, DEFAULT); INSERT tdb_goods (goods_name, goods_cate, brand_name, goods_price, is_show, is_saleoff) VALUES ', 'hp ', '200', DEFAULT, DEFAULT); INSERT tdb_goods (goods_name, goods_cate, brand_name, goods_price, is_show, is_saleoff) VALUES ('poweredge T110 II server ', 'servers/workws', 'Dell ', '123', DEFAULT, DEFAULT); INSERT tdb_goods (goods_name, goods_cate, brand_name, goods_price, is_show, is_saleoff) VALUES ('mac Pro MD878CH/A professional desktops ', 'servers/workws', 'apple', '123', DEFAULT, DEFAULT); INSERT tdb_goods (goods_name, goods_cate, brand_name, goods_price, is_show, is_saleoff) VALUES, 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 M4 rack server 2583i14 ', 'server/workws', 'ibm', '123456 ', 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 head-mounted display device', 'laptop accessories ', 'sony ', '000000', DEFAULT, DEFAULT); INSERT 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, and SUM are Aggregate functions select round (AVG (goods_price), 2) AS avg_price FROM tdb_goods; -- Query all goods whose prices are 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; -- use subquery 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; -- SELECT goods_price FROM tdb_goods WHERE goods_cate = 'superscript' for the item whose Query type is "superscript"; -- query the item whose price is greater than or equal to the "superscript" Price, SELECT goods_id, goods_name, goods_price FROM tdb_goods WHERE goods_price = ANY (SELECT goods_price FROM tdb_goods WHERE goods_cate = 'superice') 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') order by goods_price DESC; -- create table if not exists tdb_goods_cates (cate_id smallint unsigned primary key AUTO_INCREMENT, cate_name VARCHAR (40); -- Query all records of TABLE tdb_goods, SELECT goods_cate FROM tdb_goods group by goods_cate BY category; -- write the grouping results to the values data table INSERT tdb_goods_cates (cate_name) SELECT goods_cate FROM tdb_goods group by goods_cate; -- use the tdb_goods_cates data table to UPDATE the tdb_goods table UPDATE tdb_goods inner join tdb_goods_cates ON goods_cate = cate_name SET goods_cate = cate_id; -- use 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 (40) not null) SELECT brand_name FROM tdb_goods group by brand_name; -- UPDATE 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 DESC tdb_goods of tdb_goods; -- use the alter table statement to modify the data TABLE structure alter table tdb_goods CHANGE goods_cate cate_id smallint unsigned not null, CHANGE brand_name brand_id SMALLINT; -- INSERT records in the tdb_goods_cates and tdb_goods_brands tables into the INSERT tdb_goods_cates (cate_name) VALUES) VALUES ('haier '), ('tsinghua Tongfang'), ('shenzhen'); -- write arbitrary records into 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', '000000'); -- query the details of all products (implemented through internal connections) 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 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; -- query the details of all products (implemented by 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; -- 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 ('household 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 ('airconditioner', 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 ('computer servers', 2 ); INSERT into (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 ('supericled', 9); INSERT tdb_goods_types (type_name, parent_id) VALUES ('gamely', 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; -- Query all types and their subclasses 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; -- 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 alter table tdb_goods_types ADD child_count mediumint unsigned not null default 0 for tdb_goods_types; -- UPDATE the number of subclass 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 two records numbered 12, 20 SELECT * FROM tdb_goods WHERE goods_id IN (19,20 ); -- INSERT... SELECT implement copy INSERT tdb_goods (goods_name, cate_id, brand_id) SELECT goods_name, cate_id, brand_id FROM tdb_goods WHERE goods_id IN (); -- Query duplicate records SELECT goods_id, goods_name FROM tdb_goods group by goods_name HAVING count (goods_name)> = 2; -- DELETE duplicate record 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;

Well, I will introduce you so much about the use of mysql subqueries. I hope it will be helpful to you!