• 中文
    • English
  • 注册
  • 赞助本站

    • 支付宝
    • 微信
    • QQ

    感谢一直支持本站的所有人!

    • 查看作者
    • 5-7:多表更新之一步到位

      一.  前言

      我们除了可以使用INSERT SELECT外,还可以在创建表的同时直接将记录插入到该表中,本节我们将讲解如何用CREATE...SELECT在创建数据表同时将查询结果写入到数据表

      二.  CREATE...SELECT

      CREAET..SELECT的语法结构:

      CREATE TABLE [IF NOT EXISTS] tbl_name
      [(create_definition,...)]
      select_statement

      前面我们已经创建了商品分类表并完成了更新操作,接下来我们创建商品品牌表,首先查看一下有哪些品牌,直接分组查看即可:

      mysql> SELECT brand_name FROM tdb_goods GROUP BY brand_name;
      +------------+
      | brand_name |
      +------------+
      | IBM        |
      | 华硕          |
      | 宏碁         |
      | 惠普           |
      | 戴尔           |
      | 索尼           |
      | 联想           |
      | 苹果          |
      | 雷神           |
      +------------+
      9 rows in set (0.01 sec)

      接来下再创建该表的同时完成记录插入操作:

      mysql> CREATE TABLE tdb_goods_brands (
          -> brand_id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
          -> brand_name VARCHAR(40) NOT NULL)
          -> SELECT brand_name FROM tdb_goods GROUP BY brand_name;
      Query OK, 9 rows affected (0.02 sec)
      Records: 9  Duplicates: 0  Warnings: 0
      
      mysql> SELECT * FROM tdb_goods_brands;
      +----------+------------+
      | brand_id | brand_name |
      +----------+------------+
      |        1 | IBM        |
      |        2 | 华硕          |
      |        3 | 宏碁         |
      |        4 | 惠普           |
      |        5 | 戴尔           |
      |        6 | 索尼           |
      |        7 | 联想           |
      |        8 | 苹果          |
      |        9 | 雷神           |
      +----------+------------+
      9 rows in set (0.00 sec)

      可以看到数据表创建成功,插入记录也成功,接来下进行多表的更新,参照品牌表更新商品表

      mysql> UPDATE tdb_goods  INNER JOIN tdb_goods_brands ON brand_name = brand_name SET brand_name = brand_id;
      ERROR 1052 (23000): Column 'brand_name' in field list is ambiguous

      为什么报错呢?这是因为品牌表和商品表中都存在brand_name字段,所以系统并不知道连接条件brand_name = brand_name的这两个字段分别隶属哪个表,解决此问题有两个方法,起别名或者给字段前面加上表名,这里常用起别名的方法解决,将商品表中的brand_name起别名为g,品牌表中的brand_name起别名为b:

      mysql> UPDATE tdb_goods AS  g  INNER JOIN tdb_goods_brands AS b ON g.brand_name = b.brand_name SET g.brand_name = b.brand_id;
      Query OK, 22 rows affected (0.01 sec)
      Rows matched: 22  Changed: 22  Warnings: 0

      可以看到22条记录被影响,更新成功, 再检查一下:

      mysql> SELECT * FROM tdb_goods\G
      *************************** 1. row ***************************
         goods_id: 1
       goods_name: R510VC 15.6英寸笔记本
       goods_cate: 5
       brand_name: 2
      goods_price: 3399.000
          is_show: 1
       is_saleoff: 0
      *************************** 2. row ***************************
         goods_id: 2
       goods_name: Y400N 14.0英寸笔记本电脑
       goods_cate: 5
       brand_name: 7
      goods_price: 4899.000
          is_show: 1
       is_saleoff: 0
      *************************** 3. row ***************************
         goods_id: 3
       goods_name: G150TH 15.6英寸游戏本
       goods_cate: 4
       brand_name: 9
      goods_price: 8499.000
          is_show: 1
       is_saleoff: 0
      *************************** 4. row ***************************
         goods_id: 4
       goods_name: X550CC 15.6英寸笔记本
       goods_cate: 5
       brand_name: 2
      goods_price: 2799.000
          is_show: 1
       is_saleoff: 0
      *************************** 5. row ***************************
         goods_id: 5
       goods_name: X240(20ALA0EYCD) 12.5英寸超极本
       goods_cate: 7
       brand_name: 7
      goods_price: 4999.000
          is_show: 1
       is_saleoff: 0
      *************************** 6. row ***************************
         goods_id: 6
       goods_name: U330P 13.3英寸超极本
       goods_cate: 7
       brand_name: 7
      goods_price: 4299.000
          is_show: 1
       is_saleoff: 0
      *************************** 7. row ***************************
         goods_id: 7
       goods_name: SVP13226SCB 13.3英寸触控超极本
       goods_cate: 7
       brand_name: 6
      goods_price: 7999.000
          is_show: 1
       is_saleoff: 0
      *************************** 8. row ***************************
         goods_id: 8
       goods_name: iPad mini MD531CH/A 7.9英寸平板电脑
       goods_cate: 2
       brand_name: 8
      goods_price: 1998.000
          is_show: 1
       is_saleoff: 0
      *************************** 9. row ***************************
         goods_id: 9
       goods_name: iPad Air MD788CH/A 9.7英寸平板电脑 (16G WiFi版)
       goods_cate: 2
       brand_name: 8
      goods_price: 3388.000
          is_show: 1
       is_saleoff: 0
      *************************** 10. row ***************************
         goods_id: 10
       goods_name:  iPad mini ME279CH/A 配备 Retina 显示屏 7.9英寸平板电脑 (16G WiFi版)
       goods_cate: 2
       brand_name: 8
      goods_price: 2788.000
          is_show: 1
       is_saleoff: 0
      *************************** 11. row ***************************
         goods_id: 11
       goods_name: IdeaCentre C340 20英寸一体电脑
       goods_cate: 1
       brand_name: 7
      goods_price: 3499.000
          is_show: 1
       is_saleoff: 0
      *************************** 12. row ***************************
         goods_id: 12
       goods_name: Vostro 3800-R1206 台式电脑
       goods_cate: 1
       brand_name: 5
      goods_price: 2899.000
          is_show: 1
       is_saleoff: 0
      *************************** 13. row ***************************
         goods_id: 13
       goods_name: iMac ME086CH/A 21.5英寸一体电脑
       goods_cate: 1
       brand_name: 8
      goods_price: 9188.000
          is_show: 1
       is_saleoff: 0
      *************************** 14. row ***************************
         goods_id: 14
       goods_name: AT7-7414LP 台式电脑 (i5-3450四核 4G 500G 2G独显 DVD 键鼠 Linux )
       goods_cate: 1
       brand_name: 3
      goods_price: 3699.000
          is_show: 1
       is_saleoff: 0
      *************************** 15. row ***************************
         goods_id: 15
       goods_name: Z220SFF F4F06PA工作站
       goods_cate: 3
       brand_name: 4
      goods_price: 4288.000
          is_show: 1
       is_saleoff: 0
      *************************** 16. row ***************************
         goods_id: 16
       goods_name: PowerEdge T110 II服务器
       goods_cate: 3
       brand_name: 5
      goods_price: 5388.000
          is_show: 1
       is_saleoff: 0
      *************************** 17. row ***************************
         goods_id: 17
       goods_name: Mac Pro MD878CH/A 专业级台式电脑
       goods_cate: 3
       brand_name: 8
      goods_price: 28888.000
          is_show: 1
       is_saleoff: 0
      *************************** 18. row ***************************
         goods_id: 18
       goods_name:  HMZ-T3W 头戴显示设备
       goods_cate: 6
       brand_name: 6
      goods_price: 6999.000
          is_show: 1
       is_saleoff: 0
      *************************** 19. row ***************************
         goods_id: 19
       goods_name: 商务双肩背包
       goods_cate: 6
       brand_name: 6
      goods_price: 99.000
          is_show: 1
       is_saleoff: 0
      *************************** 20. row ***************************
         goods_id: 20
       goods_name: X3250 M4机架式服务器 2583i14
       goods_cate: 3
       brand_name: 1
      goods_price: 6888.000
          is_show: 1
       is_saleoff: 0
      *************************** 21. row ***************************
         goods_id: 21
       goods_name:  HMZ-T3W 头戴显示设备
       goods_cate: 6
       brand_name: 6
      goods_price: 6999.000
          is_show: 1
       is_saleoff: 0
      *************************** 22. row ***************************
         goods_id: 22
       goods_name: 商务双肩背包
       goods_cate: 6
       brand_name: 6
      goods_price: 99.000
          is_show: 1
       is_saleoff: 0
      22 rows in set (0.00 sec)

      可以看到brand_name 字段也更新成功,但是此时我们查看一下表结构:

      mysql> SHOW COLUMNS FROM tdb_goods;
      +-------------+------------------------+------+-----+---------+----------------+
      | Field       | Type                   | Null | Key | Default | Extra          |
      +-------------+------------------------+------+-----+---------+----------------+
      | goods_id    | smallint(5) unsigned   | NO   | PRI | NULL    | auto_increment |
      | goods_name  | varchar(150)           | NO   |     | NULL    |                |
      | goods_cate  | varchar(40)            | NO   |     | NULL    |                |
      | brand_name  | varchar(40)            | NO   |     | NULL    |                |
      | goods_price | decimal(15,3) unsigned | NO   |     | 0.000   |                |
      | is_show     | tinyint(1)             | NO   |     | 1       |                |
      | is_saleoff  | tinyint(1)             | NO   |     | 0       |                |
      +-------------+------------------------+------+-----+---------+----------------+
      7 rows in set (0.02 sec)

      可以看到goods_cate和brand_name依旧是字符型,同时goods_cate和brand_name也建议修改成cate_id和brand_id。我们可以修改一下数据表结构:

      mysql> ALTER TABLE tdb_goods
          -> CHANGE goods_cate cate_id SMALLINT UNSIGNED NOT NULL,
          -> CHANGE brand_name brand_id SMALLINT UNSIGNED NOT NULL;
      Query OK, 22 rows affected (0.03 sec)
      Records: 22  Duplicates: 0  Warnings: 0
      
      mysql> DESC tdb_goods;
      +-------------+------------------------+------+-----+---------+----------------+
      | Field       | Type                   | Null | Key | Default | Extra          |
      +-------------+------------------------+------+-----+---------+----------------+
      | goods_id    | smallint(5) unsigned   | NO   | PRI | NULL    | auto_increment |
      | goods_name  | varchar(150)           | NO   |     | NULL    |                |
      | cate_id     | smallint(5) unsigned   | NO   |     | NULL    |                |
      | brand_id    | smallint(5) unsigned   | NO   |     | NULL    |                |
      | goods_price | decimal(15,3) unsigned | NO   |     | 0.000   |                |
      | is_show     | tinyint(1)             | NO   |     | 1       |                |
      | is_saleoff  | tinyint(1)             | NO   |     | 0       |                |
      +-------------+------------------------+------+-----+---------+----------------+
      7 rows in set (0.01 sec)

      可以看到修改成功。另外在上一节中,我一直有一个疑问就是goods_cate为什么没有加FOREIGN KEY外键呢?这一节老师给出了解释:

      其实外键不一定需要物理的外键,就是说不一定必须加FOREIGN KEY,我们也可以使用像本节和上节这种逻辑外键的方式,也就是3-3节中最后提到的知识点。

    • 0
    • 0
    • 0
    • 2.6k
    • 请登录之后再进行评论

      登录
    • 做任务
    • 实时动态
    • 偏好设置
    • 返回顶部
    • 单栏布局 侧栏位置: