mysql中SELECT INTO 和 replace into SELECT 两种表复制语句-阿里云开发者社区

开发者社区> 数据库> 正文
登录阅读全文

mysql中SELECT INTO 和 replace 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语句实现。具体情形是:有三张表abc,现在需要从表b和表c中分别查几个字段的值插入到表a中对应的字段。对于这种情况,我们可以使用如下的语句来实现:

1.

      当然,上面的语句比较适合两个表的数据互插,如果多个表就不适应了。对于多个表,我们可以先将需要查询的字段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 (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(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不会在从属服务器中产生与主服务器不一样的数据。










本文转自 chengxuyonghu 51CTO博客,原文链接:http://blog.51cto.com/6226001001/1680042,如需转载请自行联系原作者

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

分享: