insert into table 和create table as 例子

年龄大了，好多的东西记不住了，留个备忘吧！
真怀念20多岁读书的时候！！那个时候记性还可以

create table personbak as select * from person; 
mysql> use test;
Database changed
mysql> show tables;
Empty set (0.00 sec)

mysql> create table person(id int,name varchar(20)); 
Query OK, 0 rows affected (0.00 sec)

mysql> insert into person values(1,'zhang');
Query OK, 1 row affected (0.00 sec)

mysql> create table personbak as select * from person where 1=2; ##只创建表结构
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0


 
mysql> show create  table personbak; 
+-----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table     | Create Table                                                                                                                                                                   |
+-----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| personbak | CREATE TABLE `personbak` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci |
+-----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> create table personbak as select * from person;  ##表存在时就会报错
ERROR 1050 (42S01): Table 'personbak' already exists
mysql> drop table personbak; 
Query OK, 0 rows affected (0.00 sec)


mysql> create table personbak as select * from person; ##连记录也一起过去了!
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0





 insert into personbak select * from person;  ##personbak表必须存在
 
mysql> select  * from person;
+------+-------+
| id   | name  |
+------+-------+
|    1 | zhang |
+------+-------+
1 row in set (0.00 sec)


mysql> select  * from personbak;
+------+-------+
| id   | name  |
+------+-------+
|    1 | zhang |
+------+-------+
1 row in set (0.00 sec)


mysql> insert into person values(2,'li');
 

Query OK, 1 row affected (0.00 sec)


mysql> insert into person values(3,'ji');
Query OK, 1 row affected (0.00 sec)


mysql> insert into personbak select * from person;
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0


mysql> select * from personbak;
+------+-------+
| id   | name  |
+------+-------+
|    1 | zhang |
|    1 | zhang |
|    2 | li    |
|    3 | ji    |
+------+-------+
4 rows in set (0.00 sec)