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

    • 支付宝
    • 微信
    • QQ

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

    • 查看作者
    • 5-14:多表删除

      一.  多表删除的语法结构

      DELETE tbl_name[.*][,tbl_name[.*]]...
      FROM table_references
      [WHERE where_condition]

      首先查看一下tdb_goods表中的所有记录:

      mysql> SELECT goods_id,goods_name FROM tdb_goods;
      +----------+---------------------------------------------------------------------
      | goods_id | goods_name                                                         
      +----------+--------------------------------------------------------------------
      |        1 | R510VC 15.6英寸笔记本                                                
      |        2 | Y400N 14.0英寸笔记本电脑                                                    
      |        3 | G150TH 15.6英寸游戏本                                                 
      |        4 | X550CC 15.6英寸笔记本                                                    
      |        5 | X240(20ALA0EYCD) 12.5英寸超极本                                            
      |        6 | U330P 13.3英寸超极本                                                     
      |        7 | SVP13226SCB 13.3英寸触控超极本                                                 
      |        8 | iPad mini MD531CH/A 7.9英寸平板电脑                                             
      |        9 | iPad Air MD788CH/A 9.7英寸平板电脑 (16G WiFi版)                                 
      |       10 |  iPad mini ME279CH/A 配备 Retina 显示屏 7.9英寸平板电脑 (16G WiFi版)                     
      |       11 | IdeaCentre C340 20英寸一体电脑                                                   
      |       12 | Vostro 3800-R1206 台式电脑                                                   
      |       13 | iMac ME086CH/A 21.5英寸一体电脑                                                 
      |       14 | AT7-7414LP 台式电脑 (i5-3450四核 4G 500G 2G独显 DVD 键鼠 Linux )                 
      |       15 | Z220SFF F4F06PA工作站                                                      
      |       16 | PowerEdge T110 II服务器                                                   
      |       17 | Mac Pro MD878CH/A 专业级台式电脑                                            
      |       18 |  HMZ-T3W 头戴显示设备                                                 
      |       19 | 商务双肩背包                                                        
      |       20 | X3250 M4机架式服务器 2583i14                                        
      |       21 |  HMZ-T3W 头戴显示设备                                               
      |       22 | 商务双肩背包                                                       
      |       23 |  LaserJet Pro P1606dn 黑白激光打印机                                
      +----------+-----------------------------------------------------------------------
      23 rows in set (0.00 sec)

      可以看到18和21还有19和22这几条记录是完全相同的,首先找出所有的重复记录:

      mysql> SELECT goods_id,goods_name FROM tdb_goods GROUP BY goods_name HAVING count(goods_name) >= 2;
      +----------+-----------------------+
      | goods_id | goods_name            |
      +----------+-----------------------+
      |       18 |  HMZ-T3W 头戴显示设备         |
      |       19 | 商务双肩背包                   |
      +----------+-----------------------+
      2 rows in set (0.00 sec)

      删除和18、19重复的记录:

      (感谢此处@ytcfighting的指正)

      DELETE t1 FROM tdb_goods AS t1 LEFT JOIN
      (SELECT goods_id, goods_name FROM tdb_goods 
      GROUP BY goods_name HAVING count(goods_name)>=2)
      AS t2 ON t1.goods_name=t2.goods_name WHERE t1.goods_id >t2.goods_id;

      再次查看记录可以看到已经删除成功

      mysql> SELECT * FROM tdb_goods\G
      *************************** 1. row ***************************
         goods_id: 1
       goods_name: R510VC 15.6英寸笔记本
          cate_id: 5
         brand_id: 2
      goods_price: 3399.000
          is_show: 1
       is_saleoff: 0
      *************************** 2. row ***************************
         goods_id: 2
       goods_name: Y400N 14.0英寸笔记本电脑
          cate_id: 5
         brand_id: 7
      goods_price: 4899.000
          is_show: 1
       is_saleoff: 0
      *************************** 3. row ***************************
         goods_id: 3
       goods_name: G150TH 15.6英寸游戏本
          cate_id: 4
         brand_id: 9
      goods_price: 8499.000
          is_show: 1
       is_saleoff: 0
      *************************** 4. row ***************************
         goods_id: 4
       goods_name: X550CC 15.6英寸笔记本
          cate_id: 5
         brand_id: 2
      goods_price: 2799.000
          is_show: 1
       is_saleoff: 0
      *************************** 5. row ***************************
         goods_id: 5
       goods_name: X240(20ALA0EYCD) 12.5英寸超极本
          cate_id: 7
         brand_id: 7
      goods_price: 4999.000
          is_show: 1
       is_saleoff: 0
      *************************** 6. row ***************************
         goods_id: 6
       goods_name: U330P 13.3英寸超极本
          cate_id: 7
         brand_id: 7
      goods_price: 4299.000
          is_show: 1
       is_saleoff: 0
      *************************** 7. row ***************************
         goods_id: 7
       goods_name: SVP13226SCB 13.3英寸触控超极本
          cate_id: 7
         brand_id: 6
      goods_price: 7999.000
          is_show: 1
       is_saleoff: 0
      *************************** 8. row ***************************
         goods_id: 8
       goods_name: iPad mini MD531CH/A 7.9英寸平板电脑
          cate_id: 2
         brand_id: 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版)
          cate_id: 2
         brand_id: 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版)
          cate_id: 2
         brand_id: 8
      goods_price: 2788.000
          is_show: 1
       is_saleoff: 0
      *************************** 11. row ***************************
         goods_id: 11
       goods_name: IdeaCentre C340 20英寸一体电脑
          cate_id: 1
         brand_id: 7
      goods_price: 3499.000
          is_show: 1
       is_saleoff: 0
      *************************** 12. row ***************************
         goods_id: 12
       goods_name: Vostro 3800-R1206 台式电脑
          cate_id: 1
         brand_id: 5
      goods_price: 2899.000
          is_show: 1
       is_saleoff: 0
      *************************** 13. row ***************************
         goods_id: 13
       goods_name: iMac ME086CH/A 21.5英寸一体电脑
          cate_id: 1
         brand_id: 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 )
          cate_id: 1
         brand_id: 3
      goods_price: 3699.000
          is_show: 1
       is_saleoff: 0
      *************************** 15. row ***************************
         goods_id: 15
       goods_name: Z220SFF F4F06PA工作站
          cate_id: 3
         brand_id: 4
      goods_price: 4288.000
          is_show: 1
       is_saleoff: 0
      *************************** 16. row ***************************
         goods_id: 16
       goods_name: PowerEdge T110 II服务器
          cate_id: 3
         brand_id: 5
      goods_price: 5388.000
          is_show: 1
       is_saleoff: 0
      *************************** 17. row ***************************
         goods_id: 17
       goods_name: Mac Pro MD878CH/A 专业级台式电脑
          cate_id: 3
         brand_id: 8
      goods_price: 28888.000
          is_show: 1
       is_saleoff: 0
      *************************** 18. row ***************************
         goods_id: 18
       goods_name:  HMZ-T3W 头戴显示设备
          cate_id: 6
         brand_id: 6
      goods_price: 6999.000
          is_show: 1
       is_saleoff: 0
      *************************** 19. row ***************************
         goods_id: 19
       goods_name: 商务双肩背包
          cate_id: 6
         brand_id: 6
      goods_price: 99.000
          is_show: 1
       is_saleoff: 0
      *************************** 20. row ***************************
         goods_id: 20
       goods_name: X3250 M4机架式服务器 2583i14
          cate_id: 3
         brand_id: 1
      goods_price: 6888.000
          is_show: 1
       is_saleoff: 0
      *************************** 21. row ***************************
         goods_id: 23
       goods_name:  LaserJet Pro P1606dn 黑白激光打印机
          cate_id: 12
         brand_id: 4
      goods_price: 1849.000
          is_show: 1
       is_saleoff: 0
      21 rows in set (0.00 sec)

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

      登录
    • 0
      张甲47站长
      @ytcfighting 十分感谢,已经更正!
    • 0
      DELETE t1FROM tdb_goods AS t1 LEFT JOIN(SELECT goods_id, goods_name FROM tdb_goods GROUP BY goods_name HAVING count(goods_name)>=2)AS t2 ON t1.goods_name=t2.goods_name WHERE t1.goods_id >t2.goods_id;
      忘写删除语句了
    • 做任务
    • 实时动态
    • 偏好设置
    • 返回顶部
    • 单栏布局 侧栏位置: