MySQl subquery, left-right connection, multi-Table connection learning notes, mysql learning notes

MySQl subquery, left-right connection, multi-Table connection learning notes, mysql 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;


Which of the following is the efficiency of table link queries and subqueries in databases (such as MYSQL? Why

Table connection query is high. Generally, subqueries are not recommended.

SQL statement for connecting multiple tables to left join in a table in mysql40:

SQL statement for connecting multiple tables to left join in a table in mysql4.0:
Select m. zsbt, br. brand_name, B. bname from merchant m left join brand br on m. probrand = br. brand_id left join base_source B on m. zsdz = B. bid;