删除异常
删除异常
当只存在商品信息一张表时,我们要删除一条记录,会导致商品分类,品牌信息被删除,比如:
此时应考虑把商品分类、品牌信息独立建表,并建立各表之间的依赖关系。
信息表优化
- 创建商品种类表
- 同步数据到商品种类表
- 更新商品信息表数据
- 修改商品信息表表结构
代码实现:
-- 1. 创建商品种类表
CREATE TABLE
IF
NOT EXISTS goods_cates ( id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, NAME VARCHAR ( 40 ) NOT NULL );
-- 2. 同步商品分类表 数据 将商品的所有(种类信息) 写入到商品种类表中
-- 按照分组的方式查询goods表中的所有种类(cate_name)
SELECT
cate_name
FROM
goods
GROUP BY
cate_name;
INSERT INTO goods_cates ( NAME )(
SELECT
cate_name
FROM
goods
GROUP BY
cate_name
); -- 不需要加VALUES, 因为是语句插入
SELECT
*
FROM
goods_cates;
-- 3.通过goods_cates表更新goods表中的商品分类信息
SELECT
*
FROM
goods
INNER JOIN goods_cates ON goods.cate_name = goods_cates.NAME;
UPDATE ( goods INNER JOIN goods_cates ON goods.cate_name = goods_cates.NAME )
SET goods.cate_name = goods_cates.id;
SELECT
*
FROM
goods;-- 4. 修改表结构
-- 查看表结构
DESC goods;-- 修改表结构, 将cate_name改为cate_id,并更改数据类型为INT
ALTER TABLE goods CHANGE cate_name cate_id INT UNSIGNED NOT NULL;
品牌信息进行类似修改。
CREATE TABLE
IF
NOT EXISTS goods_brands ( id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, NAME VARCHAR ( 40 ) NOT NULL );
SELECT
*
FROM
goods;
SELECT
brand_name
FROM
goods
GROUP BY
brand_name;
INSERT INTO goods_brands ( NAME )(
SELECT
brand_name
FROM
goods
GROUP BY
brand_name
);
SELECT
*
FROM
goods_brands;
SELECT
*
FROM
goods
INNER JOIN goods_brands ON goods.brand_name = goods_brands.NAME;
UPDATE ( goods INNER JOIN goods_brands ON goods.brand_name = goods_brands.NAME )
SET goods.brand_name = goods_brands.id;
SELECT
*
FROM
goods;
DESC goods;
ALTER TABLE goods CHANGE brand_name brand_id INT UNSIGNED NOT NULL;