拆为多个表_拆表insert-优快云博客

本文链接：https://blog.youkuaiyun.com/oliver3455/article/details/119979405

首先在实际开发中，一般都是在开发前，尽可能的先设计好表。最后没办法才拆表

在学习拆表前，先准备数据。

数据准备

-- 创建 "京东" 数据库
create database jing_dong charset=utf8;

-- 使用 "京东" 数据库
use jing_dong;

-- 创建一个商品goods数据表
create table goods(
    id int unsigned primary key auto_increment not null,
    name varchar(150) not null,
    cate_name varchar(40) not null,
    brand_name varchar(40) not null,
    price decimal(10,3) not null default 0,
    is_show bit not null default 1,
    is_saleoff bit not null default 0
);


-- 向goods表中插入数据

insert into goods values(0,'r510vc 15.6英寸笔记本','笔记本','华硕','3399',default,default); 
insert into goods values(0,'y400n 14.0英寸笔记本电脑','笔记本','联想','4999',default,default);
insert into goods values(0,'g150th 15.6英寸游戏本','游戏本','雷神','8499',default,default); 
insert into goods values(0,'x550cc 15.6英寸笔记本','笔记本','华硕','2799',default,default); 
insert into goods values(0,'x240 超极本','超级本','联想','4880',default,default); 
insert into goods values(0,'u330p 13.3英寸超极本','超级本','联想','4299',default,default); 
insert into goods values(0,'svp13226scb 触控超极本','超级本','索尼','7999',default,default); 
insert into goods values(0,'ipad mini 7.9英寸平板电脑','平板电脑','苹果','1998',default,default);
insert into goods values(0,'ipad air 9.7英寸平板电脑','平板电脑','苹果','3388',default,default); 
insert into goods values(0,'ipad mini 配备 retina 显示屏','平板电脑','苹果','2788',default,default); 
insert into goods values(0,'ideacentre c340 20英寸一体电脑 ','台式机','联想','3499',default,default); 
insert into goods values(0,'vostro 3800-r1206 台式电脑','台式机','戴尔','2899',default,default); 
insert into goods values(0,'imac me086ch/a 21.5英寸一体电脑','台式机','苹果','9188',default,default); 
insert into goods values(0,'at7-7414lp 台式电脑 linux ）','台式机','宏碁','3699',default,default); 
insert into goods values(0,'z220sff f4f06pa工作站','服务器/工作站','惠普','4288',default,default); 
insert into goods values(0,'poweredge ii服务器','服务器/工作站','戴尔','5388',default,default); 
insert into goods values(0,'mac pro专业级台式电脑','服务器/工作站','苹果','28888',default,default); 
insert into goods values(0,'hmz-t3w 头戴显示设备','笔记本配件','索尼','6999',default,default); 
insert into goods values(0,'商务双肩背包','笔记本配件','索尼','99',default,default); 
insert into goods values(0,'x3250 m4机架式服务器','服务器/工作站','ibm','6888',default,default); 
insert into goods values(0,'商务双肩背包','笔记本配件','索尼','99',default,default);

SQL语句的强化

查询类型cate_name为 '超极本' 的商品名称、价格

select name,price from goods where cate_name = '超级本';

显示商品的种类

select cate_name from goods group by cate_name;

求所有电脑产品的平均价格,并且保留两位小数

select round(avg(price),2) as avg_price from goods;

显示每种商品的平均价格

select cate_name,avg(price) from goods group by cate_name;

查询每种类型的商品中最贵、最便宜、平均价、数量

select cate_name,max(price),min(price),avg(price),count(*) from goods group by cate_name;

查询所有价格大于平均价格的商品，并且按价格降序排序

select id,name,price from goods 
where price > (select round(avg(price),2) as avg_price from goods) 
order by price desc;

查询每种类型中最贵的电脑信息

思路：
1.现在能做到的是，找到每个类别中价格最贵的
select cata_name,max(price) from goods group by cate_name;

有了上面的信息，那么我们就可以把上面的表作为一个新表，然后和原始做做连接查询，找出他们类别相同，价格相同的产品即可

select * from goods
inner join 
    (
        select
        cate_name, 
        max(price) as max_price, 
        from goods group by cate_name
    ) as goods_new_info 
on goods.cate_name=goods_new_info.cate_name and goods.price=goods_new_info.max_price;
或：select * from (select cate_name,max(price) as max_price from goods group by cate_name)
    as g_new left join good as g on g_new.cate_name=g.cate_name and g_new.max_price=g.price

上面还是联系在一张表，但这个表有些耦合，下面要进行拆表

拆为多表

下面把这个总的商品表，拆为商品分类表和商品品牌表，把cate_name改为cate_id，brand_name改为brand_id

第一步：创建一张新的表

-- 创建商品分类表
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;

第三步：将分组结果写入到goods_cates数据表

insert into goods_cates (name) select cate_name from goods group by cate_name;

第四步：同步更新goods表里cate_name字段数据为goods_cates表的id 号

通过goods_cates数据表来更新goods表

update goods as g inner join goods_cates as c on g.cate_name=c.name set g.cate_name=c.id;

这时可以看出，goods表里的cate_name,已经和goods_cates表里的id对应起来了

注意：但是这个时候有个问题，当你分别在goods_cates表里插入记录，和在goods表里插入记录时，goods表里的cate_name里标号对应不上goods_cates里的序号了，如：

goods_cates表插入数据

insert into goods_cates(name) values ('路由器'),('交换机'),('网卡');

goods表插数据

insert into goods (name,cate_id,brand_id,price)
values('LaserJet Pro P1606dn 黑白激光打印机', 12, 4,'1849');

从上图可以看出，goods表里的类别值有不在goods_cate表里的。

这是因为他们两的，存储字段的数据类型不一样，以前cate_name是名字，是字符型，而good_cates表里的id 是整型。

所以接下来，要把goods 表里的cate_name改为cate_id，同时要把存储的数据类型也要改了。

这里用change 方法修改，字段，因为他是重命名修改方法

alter table goods  
change cate_name cate_id int unsigned not null,

然后接下来要把这两个字段关联起来，把cate_id设为外键，但是在加外键之前，要先把上面的匹配不上的黑白激光打印机给删了

alter table goods add foreign key (cate_id) references goods_cates(id);

接下来就是把，品牌表从总表的字段里拆出来，思路和拆类别表一样

1.创建数据表：goods_brands

2.把总表里面按品牌分组，之后插入到新的数据表里

通过create...select来创建数据表并且同时插入数据,一步到位

-- select brand_name from goods group by brand_name;

-- 在创建数据表的时候一起插入数据
-- 注意: 需要对brand_name 用as起别名，否则name字段就没有值
create table goods_brands (
    id int unsigned primary key auto_increment,
    name varchar(40) not null) select brand_name as name from goods group by brand_name;

3.把总表的brand_name里的值更新为，新表goods_brands主键id的值

update goods as g inner join goods_brands as b on g.brand_name=b.name set g.brand_name=b.id;

4.把总表的brand_name改为和新表goods_brands类型，同时把外键加上

通过alter table语句修改表结构

alter table goods  
change brand_name brand_id int unsigned not null;
加外键
alter table goods add foreign key (brand_id) references goods_brands(id);

注：接下来我们看看，表里哪个是外键，因为外键比较影响性能，开发时一般不用，因为他会降低表查询的效率。

用 show create table goods; 查看

上面黄色框就是外键约束，那么为了提高性能，要把这两个约束去了

取消外键

#alter table goods drop foreign key 外键名称;
alter table goods drop foreign key goods_ibfk_1;
alter table goods drop foreign key goods_ibfk_2;

注：上面的所有操作都是把一张表拆为多个表，但是在实际开发中，前期都是花很多时间来设计表。