mysql中SELECT INTO 和 INSERT INTO SELECT 两种表复制语句

1.INSERT INTO SELECT语句

      语句形式为：Insert into Table2(field1,field2,...) select value1,value2,... from Table1

      要求目标表Table2必须存在，由于目标表Table2已经存在，所以我们除了插入源表Table1的字段外，

      还可以插入常量

2.SELECT INTO FROM语句

      语句形式为：SELECT vale1, value2 into Table2 from Table1

      要求目标表Table2不存在，因为在插入时会自动创建表Table2，

       并将Table1中指定字段数据复制到Table2中

如何在mysql从多个表中组合字段然后插入到一个新表中，通过一条sql语句实现。具体情形是：有三张表a、b、c，现在需要从表b和表c中分别查几个字段的值插入到表a中对应的字段。对于这种情况，我们可以使用如下的语句来实现：

1.             INSERT INTO db1_name(field1,field2) SELECT field1,field2 FROM db2_name 

      当然，上面的语句比较适合两个表的数据互插，如果多个表就不适应了。对于多个表，我们可以先将需要查询的字段join起来，然后组成一个视图后再select from就可以了：

1.             INSERT INTO a(field1,field2) SELECT * FROM(SELECT f1,f2 FROM b JOIN c) AS tb

      其中f1是表b的字段，f2是表c的字段，通过join查询就将分别来自表b和表c的字段进行了组合，然后再通过select嵌套查询插入到表a中，这样就满足了我们这个场景了，如果需要不止2个表，那么可以多个join的形式来组合字段。需要注意的是嵌套查询部分最后一定要有设置表别名，如下：

1.             SELECT * FROM(SELECT f1,f2 FROM b JOIN c) AS tb

      即最后的as tb是必须的（当然tb这个名称可以随意取），即指定一个别名，否则在mysql中会报如下错误：

如果列清单和VALUES清单均为空清单，则INSERT会创建一个行，每个列都被设置为默认值：
 INSERT INTO tbl_name () VALUES();
假设worker表只有name和email,插入一条数据 insert into worker values(“tom”,”[e-mail]”); 批量插入多条数据 insert into worker values(‘tom’,’[e-mail]’),(‘paul’,’[e-mail]’); 给出要赋值的那个列，然后再列出值的插入数据
insert into worker (name) values (‘tom’);
insert into worker (name) values (‘tom’), (‘paul’);
使用set插入数据
insert into worker set name=’tom’;

在 SET 子句中未命名的行都赋予一个缺省值，使用这种形式的 INSERT 语句不能插入多行。

1、复制表结构以及数据

CREATE TABLE 新表  

SELECT * FROM 旧表

2、只复制表结构

      a、      CREATE TABLE 新表

                 SELECT * FROM 旧表 WHERE 1=2

                 即:让WHERE条件不成立.
      b、:(低版本的mysql不支持，mysql4.0.25 不支持，mysql5已经支持了)
                CREATE TABLE 新表
                LIKE 旧表

3、复制旧表数据到新表（两表结构一样）

    INSERT INTO 新表

    SELECT * FROM 旧表

4、复制旧表数据到新表（两表结构不一样）

     INSERT INTO 新表(字段1,字段2,…….)
     SELECT 字段1,字段2,…… FROM 旧表

一个expression可以引用在一个值表先前设置的任何列，例如

INSERT INTO tbl_name (col1,col2) VALUES(15,col1*2); --但不能这样
INSERT INTO tbl_name (col1,col2) VALUES(col2*2,15);

 

使用INSERT…SELECT语句插入从其他表选择的行

insert into tbl_name1(col1,col2) select col3,col4 from tbl_name2;
--如果每一列都有数据

insert into tbl_name1 select col3,col4 from tbl_name2;

查询不能包含一个ORDER BY子句,而且INSERT语句的目的表不能出现在SELECT查询部分的FROM子句.

ON DUPLICATE KEY UPDATE

如果您指定了ON DUPLICATE KEY UPDATE，并且插入行后会导致在一个UNIQUE索引或PRIMARY KEY中出现重复值，则执行旧行UPDATE。

 

--假设a,b为唯一索引,表table没有1,2这样的行是正常插入数据，冲突时，更新c列的值INSERT INTO table (a,b,c) VALUES (1,2,3) ON DUPLICATE KEY UPDATE c=3;--或者是INSERT INTO table (a,b,c) VALUES (1,2,3) ON DUPLICATE KEY UPDATE c=values(c);--引用其他列更新冲突的行INSERT INTO table (a,b,c) VALUES (1,2,3),(4,5,6) ON DUPLICATE KEY UPDATE c=VALUES(a)+VALUES(b);

 向一个已定义为NOT NULL的列中插入NULL。对于一个多行INSERT语句或INSERT INTO...SELECT语句，根据列数据的类型，列被设置为隐含的默认值。对于数字类型，默认值为0；对于字符串类型，默认值为空字符串('')；对于日期和时间类型，默认值为“zero”值。

INSERT INTO...SELECT的ON DUPLICATE KEY UPDATE

insert into tbl_name1(a,b,c) 
  select col1,col2,col3 from tbl_name2 
ON DUPLICATE KEY UPDATE c=values(c);

 

INSERT DELAYED

如果您的客户端不能等待INSERT完成，则这个选项是非常有用的，当一个客户端使用INSERT DELAYED时，会立刻从服务器处得到一个确定。并且行被排入队列，当表没有被其它线程使用时，此行被插入。

使用INSERT DELAYED的另一个重要的好处是，来自许多客户端的插入被集中在一起，并被编写入一个块。这比执行许多独立的插入要快很多。

INSERT DELAYED INTO worker (name) values (‘tom’), (‘paul’);

使用DELAYED时有一些限制： 

• INSERT DELAYED仅适用于MyISAM, MEMORY和ARCHIVE表。对于MyISAM表，如果在数据文件的中间没有空闲的块，则支持同时采用SELECT和INSERT语句。在这些情况下，基本不需要对MyISAM使用INSERT DELAYED。

• INSERT DELAYED应该仅用于指定值清单的INSERT语句。服务器忽略用于INSERT DELAYED...SELECT语句的DELAYED和INSERT DELAYED...ON DUPLICATE UPDATE语句的DELAYED。

• 因为在行被插入前，语句立刻返回，所以您不能使用LAST_INSERT_ID()来获取AUTO_INCREMENT值。AUTO_INCREMENT值可能由语句生成。

• 对于SELECT语句，DELAYED行不可见，直到这些行确实被插入了为止。

• DELAYED在从属复制服务器中被忽略了，因为DELAYED不会在从属服务器中产生与主服务器不一样的数据。