[MySQL] subqueries and connections

1, subquery (subquery) refers to the SELECT clause that appears within other SQL statements

For example:

SELECT * from t1 WHERE col1 = (select col2 from T2);

where select * from T1, called Outer query/outer Statement

Select col2 from T2, called subquery.

2, a subquery is nested inside a query and always appears inside parentheses

Subqueries have multiple keywords or conditions, such as distinct, group BY, order by, limit, function, etc.

The outer query of a subquery can be: SELECT, INSERT, UPDATE, set, or do.

3, the subquery can return a scalar, row, column, or subquery 4, a subquery raised by the comparison operator (=,>,<,>=,<=,<>,!=,<=>)

Grammatical structure: operand comparison_operator subquery

Child Query Instance

Ask for the average price of all goods

Select Round (AVG (Goods_price), 2) from Tdb_goods;

List all items that are above the average price

Select Goods_id,goods_name,goods_price from Tdb_goods

where Goods_price >= (select round (AVG (Goods_price), 2) from Tdb_goods);

With SOME (any one), any (arbitrary), all modifier comparison operators

5, query raised by [not] in, [NOT] exists

= any operator is equivalent to in,!=all or <>all is equivalent to not in;

If the subquery returns any rows, exist returns True, otherwise false is returned;

6, use INSERT ... SELECT Insert record (easy to use foreign key implementation)

In the classification of a data table, there may be a large number of duplicate data, this time can be awarded to establish two data table association, improve query efficiency.

Instance

Writing query results to a data table

Insert tdb_goods_cates (cate_name) Select Goods_cate from Tdb_goods Group by Goods_cate;

7, Multi-table update

Multiple-table updates are basically similar to a single-table update.

Connection type

INNER joins (equivalent to Join,cross join),

Left[outer] Join LEFT OUTER JOIN

Right[outer] Join right outer join

SQL statement Instance

Update Tdb_goods INNER JOIN tdb_goods_cates on goods_cate = Cate_name set goods_cate = cate_id;

8, Multi-table update one step –create ... SELECT

SQL statement Instance

(1)

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;

(2) When the field names of two tables are the same, you can have an alias for two tables, such as the following G and B:

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;

(3) Modify the structure of the table

ALTER TABLE tdb_goods change goods_cate cate_id smallint unsigned not null,change brand_name brand_id smallint unsigned No T null;

9, connection syntax structure 10, INNER join INNER join

You typically use the ON keyword to set connection conditions and use the Where keyword to filter the result set records.

Inner joins: Displays the record (intersection) of the left and right tables that match the criteria.

SQL statement Instance

Select Goods_id,goods_name,cate_name from Tdb_goods inner joins tdb_goods_cates on tdb_goods.cate_id =tdb_goods_ cates.cate_id;

11, outer connection OUTER join

(1) LEFT OUTER join: Displays all and right parts of the left table that meet the criteria.

Select Goods_id,goods_name,cate_name from tdb_goods left join tdb_goods_cates on tdb_goods.cate_id = Tdb_goods_ cates.cate_id;

(2) Right outer join: Displays all and the eligible parts of the right table in all and left tables.

Select Goods_id,goods_name,cate_name from Tdb_goods right join tdb_goods_cates on tdb_goods.cate_id =tdb_goods_ cates.cate_id;

12, multi-table connection

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;

[MySQL] subqueries and connections