MySQL Learning 15: Sub-query (ii)

Two sub-query 3 Child queries raised by [not] in/exists

syntax structure of a subquery raised with [not] in:operand comparsion_operator [not] in (subquery). Where the=any operation

the character and in are equivalent; the!=all or <>all operator is equivalent to not.

Example:

1) Check the prices of all products equal to the super price (any one) of the goods

Select Goods_id,goods_name,goods_price from Tdb_goods WHERE goods_price in (select

Goods_price from tdb_goods WHERE goods_cate = ' super Ben ');

2) Check the prices of all products are not equal to the super-price (any one) of the goods

Select Goods_id,goods_name,goods_price from Tdb_goods WHERE goods_price not in (select

Goods_price from tdb_goods WHERE goods_cate = ' super Ben ');

syntax structure for subqueries raised with [NOT] exists: operand comparsion_operator [NOT] EXISTS (subquery); If the child

The query returns any rows, exists returns TRUE, otherwise false is returned. We use less in this situation.

Three sub-query application (1)INSERT Select command

Use Insert ... The syntax structure of the Select Insert Record :Insert [into] table_name [(Col_name,...)] SELECT ...;

1) First to create a data table Tdb_goods_cates

CREATE TABLE IF not EXISTS tdb_goods_cates (

cate_id SMALLINT UNSIGNED PRIMARY KEY auto_increment,

Cate_name VARCHAR (+) not NULL

);

2) Type classification of the records in the data table Tdb_goods

SELECT goods_cate from Tdb_goods GROUP by Goods_cate;

3) Write the results of the query to the datasheet 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) Refer to the product type of commodity classification table Tdb_goods_cates cate_id to update the product type of the tdb_goods of the commodity table (this is used for multiple table updates and

Connection

UPDATE tdb_goods INNER JOIN tdb_goods_cates on goods_cate = cate_name SET goods_cate = cate_id;

SELECT goods_id,goods_cate from Tdb_goods;

(2) Multi-table update

Syntax structure for multiple table updates:

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

Where_condition];

The above example is a multi-table update.

1 Multi-table update one step in place

We have several steps to make a multi-table update above, and we can use several of them together, that is, create select Life

Allows you to create a data table and write the query results directly to the specified data table.

The syntax structure that creates a data table that writes query results to a data table at the same time:

CREATE TABLE [IF not EXISTS] table_name [(create_definition,...)] select_statement;

Example:

1) Find the product brand in the datasheet tdb_goods (not part of the step, just a simple query)

SELECT brand_name from Tdb_goods GROUP by Brand_Name;

2) write the record while creating the data table:

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;

SHOW TABLES;

SELECT * from Tdb_goods_brands;

3) refer to the brand_id in datasheet tdb_goods_brands to update the Brand_Name brand type in the data table Tdb_goods

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;

SELECT goods_id,brand_name from Tdb_goods;

(3) problems caused by multi-table updating

Here we have another problem, is to update the Tdb_goods data table in the Goods_cate field and the Brand_Name field, the updated

is a numeric type, and the original is a string type, so you need to modify the two field names and data types 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 operation after modification

3) Insert records in Tdb_goods_cates and Tdb_goods_brands tables, respectively

INSERT tdb_goods_cates (cate_name) VALUES (' Router '), (' Switch '), (' network card ');

INSERT tdb_goods_brands (brand_name) VALUES (' Haier '), (' Tsinghua Tongfang '), (' Shenzhou ');

4) write any record in the Tdb_goods data table

INSERT tdb_goods (goods_name,cate_id,brand_id,goods_price) VALUES (' LaserJet Pro p1606dn monochrome laser printing

Machine ', 12,4,1849);

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

Into the record, which is not the result of using a FOREIGN key constraint.

        

MySQL Learning 15: Sub-query (ii)