拆表
先創(chuàng)建表格
create table if not exists goods_cates(
id int unsigned primary key auto_increment,
name varchar(40) not null,
);
查詢goods表中商品的種類
select cate_name from goods group by cate_name;
將分組結(jié)果寫(xiě)入到goods_cates數(shù)據(jù)表
insert into goods_cates (name) select cate_name from goods group by cate_name;
拆表.png
將第一張表和豎著的第二張的關(guān)聯(lián)
通過(guò)alter table語(yǔ)句修改表結(jié)構(gòu)
alter table goods
change cate_name cate_id int unsigned not null役耕；
（先將數(shù)據(jù)類型改成一致的）
然后再通過(guò)外鍵將他們關(guān)聯(lián)起來(lái)
alter table goods add foreign key (cate_id) references goods_cates(id);
作業(yè)：第一張表的與第三張表的拆分及關(guān)聯(lián)
拆分
先創(chuàng)建表格
create table if not exists goods_brand(
id int unsigned primary key auto_increment,
name varchar(40) not null
);
查詢goods表中商品品牌
select brand_name from goods group by brand_name;
將分組結(jié)果寫(xiě)入到goods_brand數(shù)據(jù)表
insert into goods_brand (name) select brand_name from goods group by brand_name;
關(guān)聯(lián)
alter table goods
change brand_name brand_id int unsigned not null；
alter table goods add foreign key (brand_id) references goods_bramd(id);
