多表更新
Update table_references set col_name1 = {expr1|default}[,col_name2 = {expr2|default}].....[where where_condition]
语法结构
Table_reference { [ inner | cross ] join | { left | right } [ outer ] jion }
table_reference on conditional_expr
连接类型
Inner join，内连接
在mysql中，join、cross join 和inner join是等价的。
Left【outer】join，左外连接
Right 【outer】join，右外连接
UPDATE tdb_goods INNER JOIN tdb_goods_cates ON goods_cate = cate_name SET goods_cate = cate_id;
update tdb_goods as a inner join tdb_goods_brand as b on a.brand_name=b.brand_name set a.brand_name=b.brand_id; #两个表都有brand_name,要用 as 别名
Create....select
创建数据表的同时将查询结果写入到数据表
Create table 【if not exists】表名 【（create_definition,......）】select_statement
CREATE SELECT
Eg: 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 GOURP BY brand_name;
修改tdb_goods,将goods_cate改名为cate_id,brand_name改名为brand_id ,同时,数据类型变成了SMALLINT类型,完成了对数据表进行的减肥操作,因为之前的值字符型,占用字节要比数字的大,改成SMALLINT类型以后,占用的字节变少
ALTER TABLE tdb_goods
CHANGE goods_cate cate_id SMALLINT UNSIGNED NOT NULL,
CHANGE brand_name brand_id SMALLINT UNSIGNED NOT NULL;