第四章 第七节：多表更新之一步到位（CREATE....SELECT）

创建数据表的同时将查询到的数据写入到表中

CREATE TABLE [IF NOT EXISTS] tbl_name

[(create_definition,...)]

select_statement

1、两步实现tdb_goods_brands表的创建和更新 

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;

2、参照tdb_goods_brands表更新tdb_goods表：使用AS起别名，用于区分brand_name

UPDATE tdb_goods AS g INNRE JOIN tdb_goods_brands AS b ON  g.brand_name = b.brand_name 

SET g.brand_name =  b.brand_id;

由于这节中修改了tdb_goods表中brand_name和上节的goods_cate  里面的内容，但是，brand_name和goods_cate的类型依然是VARCHAR,所以还要修改字段的类型：

ALTER TABLE tdb_goods

CHANGE goods_cate cate_id SMALLINT UNSIGNED NOT NULL,

CHANGE brand_name brand_id SMALLINT UNSIGNED NOT NULL;