MySQl subquery, left-right connection, multi-table connection learning NOTE _ MySQL

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 )', 'desktop ', 'macro