MySQL 15: subquery (2)

Subquery 3 the syntax structure of the subquery caused by [NOT] INEXISTS using [NOT] IN: operandcomparsion_operator [NOT] IN (subquery ). The ANY operator is equivalent to the IN operator ;! The ALL or ALL operator is equivalent to the NOTIN operator. Example: 1) query all items whose prices are equal to the super price (any one)

Subquery 3 the syntax structure of the subquery caused by [NOT] IN/EXISTS using [NOT] IN: operand comparsion_operator [NOT] IN (subquery ). The = ANY operator is equivalent to the IN operator ;! = ALL or ALL operators are equivalent to not in. Example: 1) query all items whose prices are equal to the super price (any one)

Subquery 3 subquery caused by [NOT] IN/EXISTS

The syntax structure of the subquery caused by [NOT] IN: operand comparsion_operator [NOT] IN (subquery ). Where, = ANY operation

Equivalent to IN ;! The = ALL or <> ALL operator is equivalent to the not in operator.

Example:

1) query all commodities whose prices are equal to the super price (any one)

SELECT goods_id, goods_name, goods_price FROM tdb_goods WHERE goods_price IN (SELECT

Goods_price FROM tdb_goods WHERE goods_cate = 'superscript ');

2) query all products whose prices are not equal to the super price (any one)

SELECT goods_id, goods_name, goods_price FROM tdb_goods WHERE goods_price not in (SELECT

Goods_price FROM tdb_goods WHERE goods_cate = 'superscript ');

The syntax structure of the subquery caused by [NOT] EXISTS: operand comparsion_operator [NOT] EXISTS (subquery); If the subquery

If the query returns any row, EXISTS returns TRUE; otherwise, FALSE. In this case, we use less.

Three subqueries (1) insert select command

Use INSERT... SELECT to INSERT the record syntax structure: INSERT [INTO] table_name [(col_name,...)] SELECT ...;

1) create a data table tdb_goods_cates first.

Create table if not exists tdb_goods_cates (

Cate_id smallint unsigned primary key AUTO_INCREMENT,

Cate_name VARCHAR (40) NOT NULL

);

2) record types in the tdb_goods table.

SELECT goods_cate FROM tdb_goods group by goods_cate;

3) write the query result to the data table tdb_goods_cates.

INSERT tdb_goods_cates (cate_name) SELECT goods_cate FROm tdb_goods group by goods_cate;

SELECT * FROM tdb_goods_cates;

4) Update the product type of the tdb_goods_goods table by referring to the product type cate_id in the product category table tdb_goods_cates (Multi-Table update and

Connection)

UPDATE tdb_goodsINNER JOIN tdb_goods_catesON goods_cate = cate_name SET goods_cate = cate_id;

SELECT goods_id, goods_cate FROM tdb_goods;

(2) Update multiple tables

Syntax structure of multi-Table update:

UPDATE table_references SET col_name1 = {expr1 | DEFAULT} [, col_name2 = {expr2 | DEFAULT}]... [WHERE

Where_condition];

The above example is multi-Table update.

1. Update multiple tables in one step

We need several steps to update multiple tables above. We can combine these steps, that is, CREATE SELECT

When a data table is created, the query results are directly written to the specified data table.

Create a data table and write the query results to the syntax structure of the data table:

Create table [if not exists] table_name [(create_definition,...)] select_statement;

Example:

1) Find the product brand in the data table tdb_goods (not a step, but a simple query)

SELECT brand_name FROM tdb_goods group by brand_name;

2) Write records when creating a data table:

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;

Show tables;

SELECT * FROM tdb_goods_brands;

3) Update the brand type of brand_name in tdb_goods_brands by referring to the brand_id in tdb_goods_brands.

UPDATE tdb_goods AS gINNER JOIN tdb_goods_brands AS bON g. brand_name = B. brand_name SET

G. brand_name = B. brand_id;

SELECT goods_id, brand_name FROM tdb_goods;

(3) problems caused by multi-Table update

Here, we have another problem: we have updated the goods_cate field and brand_name field in the tdb_goods data table.

It is a numeric type, but it is a string type. Therefore, you need to modify the names of the two fields and the data type in the tdb_goods data table.

1) view the table structure of the tdb_goods data table

DESC tdb_goods;

2) modify the data type and field name of the specified Column

Alter table tdb_goods

CHANGE goods_cate cate_id smallint unsigned not null,

CHANGE brand_name brand_id smallint unsigned not null;

DESC tdb_goods;

Check after modification

3) 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'), ('shenzhou ');

4) 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/white laser printing

Host ', 1849 );

We can see that the cate_id in the write record is 12, while the cate_id in the tdb_goods_cates table does not exist 12, but it can still be inserted

This is the result of no foreign key constraints.