mysql-8.0 insert命令

insert命令

1.简单插入

insert into tmp values(1,"22");

2.插入其他表中的数据.

insert into tmp2(id,str) select sid,sname from students;

3.插入数据的时候还可以进行插入数据的处理.

insert into tmp2(id,str) select sid,concat(sname,gender,brithday) from students;

4.这种数据的插入可以用在生产中的字段插入时使用.在大表进行字段插入的时候系统停机时间较短.

14,数据库中增加字段数据迁移

数据迁移,将原来表中的数据插入到新表中.

insert into students3(sid,sname,gender,dept_id,brithday,sname2) select * from students;

Query OK, 4 rows affected (0.12 sec)

Records: 4  Duplicates: 0  Warnings: 0

然后将原有表重命名成students_bak.

mysql> rename table students to students_bak;

Query OK, 0 rows affected (0.18 sec)

​

mysql> rename table students3 to students;

Query OK, 0 rows affected (0.18 sec)

​

mysql> select * from students;

+-----+-------+--------+---------+---------------------+--------+--------+

| sid | sname | gender | dept_id | brithday            | sname2 | sname3 |

+-----+-------+--------+---------+---------------------+--------+--------+

|   3 | Bob   | 0      |       1 | 1983-01-01 00:00:00 | NULL   | NULL   |

|   4 | Ruth  | 1      |       2 | 1983-01-01 00:00:00 | NULL   | NULL   |

|   5 | Mike  | 0      |       2 | 1986-01-01 00:00:00 | NULL   | NULL   |

|   6 | John  | 0      |       3 | 1986-01-01 00:00:00 | NULL   | NULL   |

+-----+-------+--------+---------+---------------------+--------+--------+

4 rows in set (0.01 sec)