MySQl subquery, left and right connection, multi-table connection learning notes

Source: Internet
Author: User
Tags joins lenovo

1. A subquery refers to 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[] (or outer Statement),


SELECT Column1 from T2 is called Sub query[subquery].


So, we say that the subquery is nested inside the outside of the query. In fact, it is possible to embed subqueries inside subqueries.


The subquery must appear between the parentheses.



Row-level sub-query


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.


Refine subqueries






--Create a data table


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) UNSIGNED not NULL DEFAULT 0,
Is_show BOOLEAN not NULL for 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 Laptop ', ' notebook ', ' 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 Ben ', ' Game Ben ' , ' 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-inch ultra-polar ', ' 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 ", ' 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 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 ', ' 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 version) ', ' tablet ', ' apple ', ' 3388 ', default,default;

INSERT tdb_goods (Goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES (' IPad mini me279ch/a equipped with Retina display 7.9 inch Tablet PC (16G WiFi version) ', ' tablet ', ' 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 PC ', ' desktops ', ' 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 ', ' Desktops ', ' 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 One PC ', ' Desktop ', ' 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 single-Display DVD-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-grade desktop PC ', ' Server/workstation ', ' apple ', ' 28888 ', default,default);

INSERT tdb_goods (Goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES (' hmz-t3w ', ' 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-mount Server 2583i14 ', ' Server/Workstation ', ' IBM ', ' 6888 ', default,default);

INSERT tdb_goods (Goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES (' Xuan Dragon Elite Edition 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 ', ' 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);






--average price for all computer products, and retain two decimal places, avg,max,min, COUNT, sum as aggregate function


SELECT ROUND (AVG (Goods_price), 2) as Avg_price from Tdb_goods;


--Query all items with prices 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;

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




--The query type is "super-book" The Price of the commodity



SELECT Goods_price from tdb_goods WHERE goods_cate = ' Super Ben ';


--Query for items with prices greater than or equal to "super" prices, and sorted by price descending



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 "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 of the Tdb_goods table and GROUP by category


SELECT goods_cate from Tdb_goods GROUP by Goods_cate;


--Write the grouped results 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 with the Tdb_goods_cates data sheet


UPDATE tdb_goods INNER JOIN tdb_goods_cates on goods_cate = Cate_name


SET goods_cate = cate_id;


--Through 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 (+) not NULL


) SELECT Brand_Name from Tdb_goods GROUP by Brand_Name;




--Update Tdb_goods data table (Error) by Tdb_goods_brands data sheet


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 the Tdb_goods


DESC Tdb_goods;


--Modify the data table structure with 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 in tdb_goods_cates and Tdb_goods_brands tables, respectively


INSERT tdb_goods_cates (cate_name) VALUES (' Router '), (' Switch '), (' network card ');


INSERT tdb_goods_brands (brand_name) VALUES (' Haier '), (' Tsinghua Tongfang '), (' Shenzhou ');


--write arbitrary records in the Tdb_goods data table


INSERT tdb_goods (Goods_name,cate_id,brand_id,goods_price) VALUES (' LaserJet Pro p1606dn monochrome laser printer ', ' 12 ', ' 4 ', ' 1849 ');


--Query the details of all items (implemented via an internal connection) as follows:





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 via left outer connection)




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 joins Tdb_goods_brands as B on g.brand_id = B.brand_id\g;


--Query all product details (implemented via right outer connection)




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 () not NULL,
parent_id SMALLINT UNSIGNED not NULL DEFAULT 0
);


INSERT tdb_goods_types (type_name,parent_id) VALUES (' Home appliance ', DEFAULT);
INSERT tdb_goods_types (type_name,parent_id) VALUES (' Computer, Office ', DEFAULT);
INSERT tdb_goods_types (type_name,parent_id) VALUES (' Everyone electricity ', 1);
INSERT tdb_goods_types (type_name,parent_id) VALUES (' Living electrical ', 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 (' 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 Ben ', 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;

--Find all classes and their subclasses


SELECT p.type_id,p.type_name,s.type_name from Tdb_goods_types as P left joins Tdb_goods_types as s on s.parent_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 Tdb_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 for 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 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_good S_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 the two records numbered 12,20


SELECT * from Tdb_goods WHERE goods_id in (19,20);




--INSERT ... Select for 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 have 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 have count (goods _name) >= 2) as t2 on t1.goods_name = T2.goods_name WHERE t1.goods_id > t2.goods_id;

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.