1. runoob_id  runoob_title  runoob_author  
  2. ---------  ------------  ---------------
  3.         1  php           baidu.com      
  4.         2  PHP           baidu.com      
  5.         3  Java          qq.com         
  6.         4  Python        qq.com         
  7.         5  C             pass           
  8. -- runoob 为主键
  1. run_id_bp  run_title_bp  run_author_bp 
  2. ---------  ------------  ---------------
  3.         6  JavaScript    csdn.com         
  4.         7  SQL           baidu.com        
  5.         8  R             weixin.com 
  6.         9  Objective-C   qq.com           
  7. --  runoob 为主键

1.插入一行数据

  1. INSERT INTO table_name ( field1, field2,...fieldN )
  2.                        VALUES
  3.                        ( value1, value2,...valueN );
  1. INSERT INTO  `runoob` (`runoob_id`,`runoob_title`,`runoob_author`) VALUES(6,'C++','weixin.com')
  1. unoob_id  runoob_title  runoob_author  
  2. ---------  ------------  ---------------
  3.         1  php           baidu.com      
  4.         2  PHP           baidu.com      
  5.         3  Java          qq.com         
  6.         4  Python        qq.com         
  7.         5  C             pass           
  8.         6  C++           weixin.com

2.插入多行数据

  1. INSERT INTO table_name  (field1, field2,...fieldN)  
  2.                                                 VALUES  
  3.                                                 (valueA1,valueA2,...valueAN),(valueB1,valueB2,...valueBN),(valueC1,valueC2,...valueCN)......;
  1. INSERT INTO  `runoob` (`runoob_id`,`runoob_title`,`runoob_author`) VALUES(6,'C++','weixin.com'),(7,'go','weixin.com')
  1. runoob_id  runoob_title  runoob_author  
  2. ---------  ------------  ---------------
  3.         1  php           baidu.com      
  4.         2  PHP           baidu.com      
  5.         3  Java          qq.com         
  6.         4  Python        qq.com         
  7.         5  C             pass           
  8.         6  C++           weixin.com     
  9.         7  go            weixin.com

3.删除原数据，插入最新数据

  1. -- 注意：却分是否存在是通过主键来确定的
  2. REPLACE  INTO table_name ( field1, field2,...fieldN )
  3.                        VALUES
  4.                        ( value1, value2,...valueN );
  1. REPLACE INTO `runoob` (`runoob_id`,`runoob_title`,`runoob_author`) VALUES(5,'js',"weixin.com")
  1. runoob_id  runoob_title  runoob_author  
  2. ---------  ------------  ---------------
  3.         1  php           baidu.com      
  4.         2  PHP           baidu.com      
  5.         3  Java          qq.com         
  6.         4  Python        qq.com         
  7.         5  js            weixin.com

4.忽略已有，插入差异

  1. -- 注意：是否存在是通过主键来确定的
  2. -- 如果插入的数据：已存在 忽略插入的数据,不改变原来的数据,
  3. -- 如果插入的数据：不存在 则插入新的数据。
  5. INSERT IGNORE INTO table_name ( field1, field2,...fieldN )
  6.                        VALUES
  7.                        ( value1, value2,...valueN );
  1. INSERT IGNORE INTO `runoob` (`runoob_id`,`runoob_title`,`runoob_author`) VALUES(5,'C',"pass"),(6,'C++','weixin.com')
  1. runoob_id  runoob_title  runoob_author  
  2. ---------  ------------  ---------------
  3.         1  php           baidu.com      
  4.         2  PHP           baidu.com      
  5.         3  Java          qq.com         
  6.         4  Python        qq.com         
  7.         5  C             pass           
  8.         6  C++           weixin.com

5.将查询结果插入

  1. INSERT INTO table_name ( field1, field2,...fieldN )
  2.                          VALUES
  3.                                         SELECT value1,value2,...valueN
  4.                         From table
  1. INSERT INTO `runoob`(`runoob_id`,`runoob_title`,`runoob_author`) SELECT `runoob_id_backup`,`runoob_title_backup`,`runoob_author_backup` FROM `runoob_backup`
  1. runoob_id  runoob_title  runoob_author  
  2. ---------  ------------  ---------------
  3.         1  php           baidu.com      
  4.         2  PHP           baidu.com      
  5.         3  Java          qq.com         
  6.         4  Python        qq.com         
  7.         5  (NULL)        (NULL)         
  8.         6  JavaScript    csdn.com       
  9.         7  SQL           baidu.com      
  10.         8  R             weixin.com     
  11.         9  Objective-C   qq.com