删除异常

当只存在商品信息一张表时，我们要删除一条记录，会导致商品分类，品牌信息被删除，比如：

此时应考虑把商品分类、品牌信息独立建表，并建立各表之间的依赖关系。

信息表优化

1. 创建商品种类表
2. 同步数据到商品种类表
3. 更新商品信息表数据
4. 修改商品信息表表结构

代码实现：

-- 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;