黑马程序员---数据库拆表操作

清疚 2022-11-28 10:47 118阅读 0赞

一：问题：目前只有一个goods表，我们想要增加一个商品分类信息，比如：移动设备这个分类信息，只通过goods表无法完成商品分类的添加，那么如何实现添加商品分类信息的操作?  
答案：创建一个商品分类表，把goods表中的商品分类信息添加到该表中。  
将goods表中的分类名称更改成商品分类表中对应的分类id

mysql> select * from goods;
    +----+---------------------------------------+---------------------+------------+-----------+---------+------------+
    | id | name                                  | cate_name           | brand_name | price     | is_show | is_saleoff |
    +----+---------------------------------------+---------------------+------------+-----------+---------+------------+
    |  1 | r510vc 15.6英寸笔记本                 | 笔记本              | 华硕       |  3399.000 |        |            |
    |  2 | y400n 14.0英寸笔记本电脑              | 笔记本              | 联想       |  4999.000 |        |            |
    |  3 | g150th 15.6英寸游戏本                 | 游戏本              | 雷神       |  8499.000 |        |            |
    |  4 | x550cc 15.6英寸笔记本                 | 笔记本              | 华硕       |  2799.000 |        |            |
    |  5 | x240 超极本                           | 超级本              | 联想       |  4880.000 |        |            |
    |  6 | u330p 13.3英寸超极本                  | 超级本              | 联想       |  4299.000 |        |            |
    |  7 | svp13226scb 触控超极本                | 超级本              | 索尼       |  7999.000 |        |            |
    |  8 | ipad mini 7.9英寸平板电脑             | 平板电脑            | 苹果       |  1998.000 |        |            |
    |  9 | ipad air 9.7英寸平板电脑              | 平板电脑            | 苹果       |  3388.000 |        |            |
    | 10 | ipad mini 配备 retina 显示屏          | 平板电脑            | 苹果       |  2788.000 |        |            |
    | 11 | ideacentre c340 20英寸一体电脑        | 台式机              | 联想       |  3499.000 |        |            |
    | 12 | vostro 3800-r1206 台式电脑            | 台式机              | 戴尔       |  2899.000 |        |            |
    | 13 | imac me086ch/a 21.5英寸一体电脑       | 台式机              | 苹果       |  9188.000 |        |            |
    | 14 | at7-7414lp 台式电脑 linux ）          | 台式机              | 宏碁       |  3699.000 |        |            |
    | 15 | z220sff f4f06pa工作站                 | 服务器/工作站       | 惠普       |  4288.000 |        |            |
    | 16 | poweredge ii服务器                    | 服务器/工作站       | 戴尔       |  5388.000 |        |            |
    | 17 | mac pro专业级台式电脑                 | 服务器/工作站       | 苹果       | 28888.000 |        |            |
    | 18 | hmz-t3w 头戴显示设备                  | 笔记本配件          | 索尼       |  6999.000 |        |            |
    | 19 | 商务双肩背包                          | 笔记本配件          | 索尼       |    99.000 |        |            |
    | 20 | x3250 m4机架式服务器                  | 服务器/工作站       | ibm        |  6888.000 |        |            |
    | 21 | 商务双肩背包                          | 笔记本配件          | 索尼       |    99.000 |        |            |
    +----+---------------------------------------+---------------------+------------+-----------+---------+------------+
    21 rows in set (0.00 sec)

二：拆表的过程：  
1：创建新表：

mysql> create table goods_cates(id int primary key auto_increment not null,name varchar(50) not null);
    Query OK, 0 rows affected (0.06 sec)

2：把goods表中的商品分类添加到商品分类表:

mysql> insert into goods_cates(name) select cate_name from goods group by cate_name;
    Query OK, 7 rows affected (0.00 sec)
    Records: 7  Duplicates: 0  Warnings: 0
    
    mysql> select * from goods_cates;
    +----+---------------------+
    | id | name                |
    +----+---------------------+
    |  1 | 台式机              |
    |  2 | 平板电脑            |
    |  3 | 服务器/工作站       |
    |  4 | 游戏本              |
    |  5 | 笔记本              |
    |  6 | 笔记本配件          |
    |  7 | 超级本              |
    +----+---------------------+
    7 rows in set (0.00 sec)

3：更新goods表中的商品分类信息：将goods表中的分类名称更改成商品分类表中对应的分类id。（连接更新）

mysql> update goods inner join goods_cates on goods.cate_name = goods_cates.name set goods.cate_name = goods_cates.id;
    Query OK, 21 rows affected (0.00 sec)
    Rows matched: 21  Changed: 21  Warnings: 0
    
    mysql> select * from goods;
    +----+---------------------------------------+-----------+------------+-----------+---------+------------+
    | id | name                                  | cate_name | brand_name | price     | is_show | is_saleoff |
    +----+---------------------------------------+-----------+------------+-----------+---------+------------+
    |  1 | r510vc 15.6英寸笔记本                 | 5         | 华硕       |  3399.000 |        |            |
    |  2 | y400n 14.0英寸笔记本电脑              | 5         | 联想       |  4999.000 |        |            |
    |  3 | g150th 15.6英寸游戏本                 | 4         | 雷神       |  8499.000 |        |            |
    |  4 | x550cc 15.6英寸笔记本                 | 5         | 华硕       |  2799.000 |        |            |
    |  5 | x240 超极本                           | 7         | 联想       |  4880.000 |        |            |
    |  6 | u330p 13.3英寸超极本                  | 7         | 联想       |  4299.000 |        |            |
    |  7 | svp13226scb 触控超极本                | 7         | 索尼       |  7999.000 |        |            |
    |  8 | ipad mini 7.9英寸平板电脑             | 2         | 苹果       |  1998.000 |        |            |
    |  9 | ipad air 9.7英寸平板电脑              | 2         | 苹果       |  3388.000 |        |            |
    | 10 | ipad mini 配备 retina 显示屏          | 2         | 苹果       |  2788.000 |        |            |
    | 11 | ideacentre c340 20英寸一体电脑        | 1         | 联想       |  3499.000 |        |            |
    | 12 | vostro 3800-r1206 台式电脑            | 1         | 戴尔       |  2899.000 |        |            |
    | 13 | imac me086ch/a 21.5英寸一体电脑       | 1         | 苹果       |  9188.000 |        |            |
    | 14 | at7-7414lp 台式电脑 linux ）          | 1         | 宏碁       |  3699.000 |        |            |
    | 15 | z220sff f4f06pa工作站                 | 3         | 惠普       |  4288.000 |        |            |
    | 16 | poweredge ii服务器                    | 3         | 戴尔       |  5388.000 |        |            |
    | 17 | mac pro专业级台式电脑                 | 3         | 苹果       | 28888.000 |        |            |
    | 18 | hmz-t3w 头戴显示设备                  | 6         | 索尼       |  6999.000 |        |            |
    | 19 | 商务双肩背包                          | 6         | 索尼       |    99.000 |        |            |
    | 20 | x3250 m4机架式服务器                  | 3         | ibm        |  6888.000 |        |            |
    | 21 | 商务双肩背包                          | 6         | 索尼       |    99.000 |        |            |
    +----+---------------------------------------+-----------+------------+-----------+---------+------------+
    21 rows in set (0.00 sec)

4：能不能创建表的同时插入数据呢？  
假如我们想要添加一个品牌，比如：双飞燕这个品牌信息，只通过goods表无法完成品牌信息的添加，那么如何实现添加品牌信息的操作?  
答案：创建一个品牌表，把goods表中的品牌信息添加到该表中。  
将goods表中的品牌名称更改成品牌表中对应的品牌id。

mysql> create table good_brands (     
        -> id int unsigned primary key auto_increment,     
        -> name varchar(40) not null) select brand_name as name from goods group by brand_name;
    Query OK, 9 rows affected (0.08 sec)
    Records: 9  Duplicates: 0  Warnings: 0
    
    mysql> select * from good_brands;
    +----+--------+
    | id | name   |
    +----+--------+
    |  1 | ibm    |
    |  2 | 华硕   |
    |  3 | 宏碁   |
    |  4 | 惠普   |
    |  5 | 戴尔   |
    |  6 | 索尼   |
    |  7 | 联想   |
    |  8 | 苹果   |
    |  9 | 雷神   |
    +----+--------+
    9 rows in set (0.00 sec)

注意：创建的时候查询语句的别名必须要取，而且要和创建的某一个字段相同。比如这个name。  
同样更新goods表中的品牌信息。

mysql> update goods as g inner join good_brands gb on g.brand_name = gb.name set g.brand_name = gb.id;
    Query OK, 21 rows affected (0.00 sec)
    Rows matched: 21  Changed: 21  Warnings: 0
    
    mysql> select * from goods;
    +----+---------------------------------------+-----------+------------+-----------+---------+------------+
    | id | name                                  | cate_name | brand_name | price     | is_show | is_saleoff |
    +----+---------------------------------------+-----------+------------+-----------+---------+------------+
    |  1 | r510vc 15.6英寸笔记本                 | 5         | 2          |  3399.000 |        |            |
    |  2 | y400n 14.0英寸笔记本电脑              | 5         | 7          |  4999.000 |        |            |
    |  3 | g150th 15.6英寸游戏本                 | 4         | 9          |  8499.000 |        |            |
    |  4 | x550cc 15.6英寸笔记本                 | 5         | 2          |  2799.000 |        |            |
    |  5 | x240 超极本                           | 7         | 7          |  4880.000 |        |            |
    |  6 | u330p 13.3英寸超极本                  | 7         | 7          |  4299.000 |        |            |
    |  7 | svp13226scb 触控超极本                | 7         | 6          |  7999.000 |        |            |
    |  8 | ipad mini 7.9英寸平板电脑             | 2         | 8          |  1998.000 |        |            |
    |  9 | ipad air 9.7英寸平板电脑              | 2         | 8          |  3388.000 |        |            |
    | 10 | ipad mini 配备 retina 显示屏          | 2         | 8          |  2788.000 |        |            |
    | 11 | ideacentre c340 20英寸一体电脑        | 1         | 7          |  3499.000 |        |            |
    | 12 | vostro 3800-r1206 台式电脑            | 1         | 5          |  2899.000 |        |            |
    | 13 | imac me086ch/a 21.5英寸一体电脑       | 1         | 8          |  9188.000 |        |            |
    | 14 | at7-7414lp 台式电脑 linux ）          | 1         | 3          |  3699.000 |        |            |
    | 15 | z220sff f4f06pa工作站                 | 3         | 4          |  4288.000 |        |            |
    | 16 | poweredge ii服务器                    | 3         | 5          |  5388.000 |        |            |
    | 17 | mac pro专业级台式电脑                 | 3         | 8          | 28888.000 |        |            |
    | 18 | hmz-t3w 头戴显示设备                  | 6         | 6          |  6999.000 |        |            |
    | 19 | 商务双肩背包                          | 6         | 6          |    99.000 |        |            |
    | 20 | x3250 m4机架式服务器                  | 3         | 1          |  6888.000 |        |            |
    | 21 | 商务双肩背包                          | 6         | 6          |    99.000 |        |            |
    +----+---------------------------------------+-----------+------------+-----------+---------+------------+
    21 rows in set (0.00 sec)

5：最后要修改一下表结构。这是因为，goods里面的以前shivarchar()类型，而新表是int类型，最好将他们类型匹配起来，这里的cate\_name与brand\_name原来是字符串，现在要修改成cate\_id和brand\_id 此时的类型也要改。

mysql> show create table goods;
    +-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    | Table | Create Table                                                                                                                                                                                                                                                                                                                                                                              |
    +-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    | goods | CREATE TABLE `goods` (
      `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
      `name` varchar(150) NOT NULL,
      `cate_id` int(11) NOT NULL,
      `brand_id` int(11) NOT NULL,
      `price` decimal(10,3) NOT NULL DEFAULT '0.000',
      `is_show` bit(1) NOT NULL DEFAULT b'1',
      `is_saleoff` bit(1) NOT NULL DEFAULT b'0',
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=22 DEFAULT CHARSET=utf8 |
    +-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    1 row in set (0.00 sec)