技术饭

INSERT INTO SELECT FROM,从某个表复制到另外一个表语句的使用方法

copylian    0 评论    6095 浏览    2022.05.24

INSERT INTO SELECT FROM,从某个表复制到另外一个表语句的使用方法,数据脚本迁移原本看起来比较麻烦,问了下大佬,大佬随便在我屏幕上打上:insert into from,说用这个就好了,然后再加上小度就找到解决方法了,感谢大佬,每天教会小弟一点知识。

select into frominsert into select都是用来复制表

两者的主要区别为: select into from 要求目标表不存在,因为在插入时会自动创建,insert into select from 要求目标表存在

备份表数据: create table emp as select * from scott.emp

还原表数据:insert into emp select * from scott.emp

复制表结构及其数据:create table table_name_new as select * from table_name_old

只复制表结构:

create table table_name_new as select * from table_name_old where 1=2

或者

create table table_name_new like table_name_old


只复制表数据:

如果两个表结构一样

insert into table_name_new select * from table_name_old

如果两个表结构不一样

insert into table_name_new(column1,column2...) select column1,column2... from table_name_old pasting


实例:

INSERT INTO `user1` (

      openid,

      brand_id,

      brand_pid,

      start_date,

      end_date

SELECT 

      openid,

      id AS brand_id,

      pid AS brand_pid,

      group_start_date AS start_date,

      group_end_date AS end_date 

FROM

      `user` 

WHERE oepnids IN (

        'b2c8edd0f2a2496b9b6513a3dc10bd68',

        '28407233fe054bf09cfed6bc3b491a40',

        '3edc4bb7ab3247b98ff4e7760083916c',

        '2b09b3d6cf8f43c7a0aab4226de9d6f2',

        'a15f8b57907741cda11888b14212d329',

        '1ddcf80194ec4abd8fc75b937379516f',

  )

只袄早~~~
感谢你的支持,我会继续努力!
扫码打赏,感谢您的支持!
INSERT SELECT mysql 

文明上网理性发言!

  • 还没有评论,沙发等你来抢