MySQl subquery, left-right join, and multi-table join learning Notes
1. subquery refers to the SELECT clause in another query statement.
Example:
SELECT * FROM t1 WHERE column1 = (SELECT column1 FROM t2 );
SELECT * FROM t1... is called Outer Query [external Query] (or Outer Statement ),
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 subqueries
-- 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 (15, 3) unsigned 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', 'asuste', '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 ', '123', DEFAULT, DEFAULT );
INSERT tdb_goods (goods_name, goods_cate, brand_name, goods_price, is_show, is_saleoff) VALUES ('x550cc 15.6 inch notebooks ', 'notebooks', 'asuste', '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', 'superice', 'Lenovo ', '2016', DEFAULT, DEFAULT );
INSERT tdb_goods (goods_name, goods_cate, brand_name, goods_price, is_show, is_saleoff) VALUES );
INSERT tdb_goods (goods_name, goods_cate, brand_name, goods_price, is_show, is_saleoff) VALUES, 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 ', '123', 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 ID', 'apple', '123', DEFAULT, DEFAULT );
INSERT tdb_goods (goods_name, goods_cate, brand_name, goods_price, is_show, is_saleoff) VALUES ('ideacentre C340 20 inch one', 'desktops ', 'Lenovo', '123', 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 PC ', '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 )', 'desktops ', 'acer', '123', DEFAULT, DEFAULT );
INSERT tdb_goods (goods_name, goods_cate, brand_name, goods_price, is_show, is_saleoff) VALUES ('z220sff F4F06PA workws', 'server/workws', 'HP ', '2016', DEFAULT, DEFAULT );
INSERT tdb_goods (goods_name, goods_cate, brand_name, goods_price, is_show, is_saleoff) VALUES ('poweredge T110 II server ', 'server/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-level desktops ', 'Servers/workws', 'apple ', '123', DEFAULT, DEFAULT );
INSERT tdb_goods (goods_name, goods_cate, brand_name, goods_price, is_show, is_saleoff) VALUES, 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 );
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 );
INSERT tdb_goods (goods_name, goods_cate, brand_name, goods_price, is_show, is_saleoff) VALUES, 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 retain two decimal places, AVG, MAX, MIN, COUNT, and SUM as 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 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;
-- Implement with subquery
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 price of the item whose query type is "supernote"
SELECT goods_price FROM tdb_goods WHERE goods_cate = 'superscript ';
-- Query commodities whose prices are greater than or equal to the "super" price, and sort them by Price in descending order
SELECT goods_id, goods_name, goods_price FROM tdb_goods
WHERE goods_price = ANY (SELECT goods_price FROM tdb_goods WHERE goods_cate = 'superscript ')
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 = 'superscript ')
Order by goods_price DESC;
-- Create a "product category" 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_cates table
UPDATE tdb_goods inner join tdb_goods_cates ON goods_cate = cate_name
SET goods_cate = cate_id;
-- CREATE... SELECT is used 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;
-- Use the tdb_goods_brands data table to update the tdb_goods 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 table structure of tdb_goods
DESC tdb_goods;
-- 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 the tdb_goods_cates and tdb_goods_brands tables respectively.
INSERT tdb_goods_cates (cate_name) VALUES ('router'), ('vswitch '), ('nics ');
INSERT tdb_goods_brands (brand_name) VALUES ('Haier '), ('Tsinghua Tongfang'), ('shenzhen ');
-- Write arbitrary records 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', '123 ');
-- Query the details of all products (implemented through internal connections) 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 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;
-- Unlimited 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 ('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 electric appliance ', 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 ID', 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 ('superscript', 9 );
INSERT tdb_goods_types (type_name, parent_id) VALUES ('game Bene', 9 );
INSERT tdb_goods_types (type_name, parent_id) VALUES ('CPU ', 10 );
INSERT tdb_goods_types (type_name, parent_id) VALUES ('host', 10 );
-- Search for 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;
-- Search for 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. parent_id = p. type_id;
-- Search for 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 the 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 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 and 20
SELECT * FROM tdb_goods WHERE goods_id IN (19,20 );
-- INSERT... SELECT
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 );
-- Query 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.goods _ id;