Using _mysql for neutron queries in Mysql database

Nonsense not to say, directly to everyone paste MySQL database The use of the total subquery.

The code is described as follows:

</pre><pre name= "code" class= "SQL" > 1. A subquery refers to a SELECT clause in another query statement. 
Example: SELECT * from t1 WHERE column1 = (select Column1 from T2); Where the SELECT * from T1 ... 
Called the outer query[query] (or outer Statement), SELECT column1 from T2 is called Sub query[subquery]. So, we say the subquery is nested inside the outer query. 
In fact it is possible to embed subqueries inside subqueries. 
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); 
Row-level subqueries return results that are up to one row.  Optimizing subqueries--Creating a datasheet 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) UNS 
igned NOT NULL default 0, is_show boolean NOT null 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 laptops ', ' 
Notebooks ', ' 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 book ', ' Game book ' 
, ' 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 "ultra-Extreme", ' 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-inch ultra-extreme ', ' 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 ultra-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 PC ', ' 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 edition) ', ' Tablet PC ', ' apple ', ' 3388 ', 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 PC (16G WiFi edition) ', ' Tablet PC ', ' 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 computer 
', ' Desktop ', ' 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 ', 
' Desktop ', ' 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 all-in-one computer ', ' Desktop ', ' apple ', ' 9188 ', default,default; INSERT tdb_goods (Goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES (' AT7-7414LP desktop computer (i5-3450 four nuclear 4G 500G2G DVD key 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-level desktop computers ', ' Servers/workstations ', ' apple ', ' 28888 ', default,default; INSERT tdb_goods (Goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES (' hmz-t3w display device ', ' 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 Server 2583i14 ', 
' Server/Workstation ', ' IBM ', ' 6888 ', default,default; INSERT Tdb_goods (Goods_name,goods_cAte,brand_name,goods_price,is_show,is_saleoff) VALUES (' Hyun Long elite version of the 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 display device ', ' 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); 
--ask for the average price of all computer products and keep two decimal digits, avg,max,min, COUNT, sum to aggregate function SELECT ROUND (avg (Goods_price), 2) as Avg_price from Tdb_goods; --Check all items that have a price greater than the average price and sort the SELECT goods_id,goods_name,goods_price from Tdb_goods WHERE goods_price > 5845.10 order B by Price descending 
Y 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 Type "super book" Commodity price SELECT Goods_price from tdb_goods WHERE goods_cate = ' Super Ben '; --Query for items that are more than or equal to the "super" price, and in descending order of prices 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 catalog table created table IF not EXISTS tdb_goods_cates (cate_id SMALLINT UNSIGNED PRIMARY KEY auto_increment, Cate_nam 
e VARCHAR (40)); 
--Queries all records of the Tdb_goods table, and selects Goods_cate from Tdb_goods Group by goods_cate, grouped by category; 
-Writes grouped results to tdb_goods_cates datasheet INSERT tdb_goods_cates (cate_name) SELECT goods_cate from Tdb_goods Group by Goods_cate; --Update tdb_goods table update tdb_goods INNER JOIN tdb_goods_cates on goods_cate = Cate_name SET goods_cat by tdb_goods_cates Datasheet 
e = cate_id; --Through the Create ... 
Select to create the data table and write the record 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 the Tdb_goods datasheet (error) by tdb_goods_brands the data table (errors) Update tdb_goods INNER JOIN tdb_goods_brands on brand_name = Brand_Name SET 
Brand_Name = brand_id; ---Column ' brand_name ' in the 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 Tdb_goods data table structure DESC tdb_goods;  --Modifying the datasheet structure with the ALTER TABLE statement tdb_goods change goods_cate cate_id SMALLINT UNSIGNED not NULL, changing brand_name 
brand_id SMALLINT UNSIGNED not NULL; 
--Inserts records insert Tdb_goods_cates (cate_name) VALUES (' Routers '), (' Switches '), (' Tdb_goods_cates ') in the table and Tdb_goods_brands tables respectively; 
INSERT tdb_goods_brands (brand_name) VALUES (' Haier '), (' Tsinghua Tongfang '), (' Shenzhou '); --write arbitrary records in tdb_goods datasheet INSERT tdb_goods (goods_name,cate_id,brand_id,goods_price) VALUES (' LaserJet Pro p1606dn 
Black and white laser printers ', ' 12 ', ' 4 ', ' 1849 '); --Query for details of all items (implemented via 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.bra 
Nd_id\g; --Query for details of all items (implemented via left OUTER join) SELECT Goods_id,goods_name,cate_name,brand_name,goods_price from Tdb_goods as G-Right JOIN Tdb_g 
Oods_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 for details of all items (implemented via right outer join) SELECT Goods_id,goods_name,cate_name,brand_name,goods_price from Tdb_goods as G 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;  --Infinite classification of datasheet 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 (' Home appliances ', DEFAULT); 
INSERT tdb_goods_types (type_name,parent_id) VALUES (' Computer, Office ', DEFAULT); 
INSERT tdb_goods_types (type_name,parent_id) VALUES (' Everyone power ', 1); INSERT tdb_goods_types (Type_name,parent_id) VALUES (' Life 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 conditioner ', 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 book ', 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 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.pare 
nt_id = p.type_id; --Find all categories 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.pare nt_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 t 
Db_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 to 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 datasheet 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 Implements 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 has 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 has  Count (Goods_name) >= 2) as t2 on t1.goods_name = T2.goods_name WHERE t1.goods_id > t2.goods_id;

OK, about the use of the MySQL neutron query to introduce so many people, I hope to help you!