create table xxx as select 与 create table xxx like
create table xxx as select xxx，创建新表，没有原表的完整约束，会把原表的数据拷贝一份，如下：
mysql> desc stu;
+------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+----------------+
| Id | int(9) | NO | PRI | NULL | auto_increment |
| Name | varchar(100) | NO | | NULL | |
| Age | int(9) | NO | | 0 | |
| updatetime | datetime | YES | | NULL | |
+------------+--------------+------+-----+---------+----------------+
4 rows in set
mysql> select * from stu;
+----+------+-----+---------------------+
| Id | Name | Age | updatetime |
+----+------+-----+---------------------+
| 1 | Andy | 28 | 2015-03-19 15:42:09 |
+----+------+-----+---------------------+
1 row in set
mysql> create table stu2 as select * from stu;
Query OK, 1 row affected
Records: 1 Duplicates: 0 Warnings: 0
mysql> desc stu2;
+------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+-------+
| Id | int(9) | NO | | 0 | |
| Name | varchar(100) | NO | | NULL | |
| Age | int(9) | NO | | 0 | |
| updatetime | datetime | YES | | NULL | |
+------------+--------------+------+-----+---------+-------+
4 rows in set
mysql> select * from stu2;
+----+------+-----+---------------------+
| Id | Name | Age | updatetime |
+----+------+-----+---------------------+
| 1 | Andy | 28 | 2015-03-19 15:42:09 |
+----+------+-----+---------------------+
1 row in set
create table xxx like xxx，创建新表，约束和原表相同，只拷贝表结构，没有拷贝表的数据，如下：
mysql> desc stu;
+------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+----------------+
| Id | int(9) | NO | PRI | NULL | auto_increment |
| Name | varchar(100) | NO | | NULL | |
| Age | int(9) | NO | | 0 | |
| updatetime | datetime | YES | | NULL | |
+------------+--------------+------+-----+---------+----------------+
4 rows in set
mysql> select * from stu;
+----+------+-----+---------------------+
| Id | Name | Age | updatetime |
+----+------+-----+---------------------+
| 1 | Andy | 28 | 2015-03-19 15:42:09 |
+----+------+-----+---------------------+
1 row in set
mysql> create table stu3 like stu;
Query OK, 0 rows affected
mysql> desc stu3;
+------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+----------------+
| Id | int(9) | NO | PRI | NULL | auto_increment |
| Name | varchar(100) | NO | | NULL | |
| Age | int(9) | NO | | 0 | |
| updatetime | datetime | YES | | NULL | |
+------------+--------------+------+-----+---------+----------------+
4 rows in set
mysql> select * from stu3;
Empty set
如果我想拷贝表的结构（约束和原表相同），同时拷贝表的数据，怎么办？
先create table xxx like xxx，创建表结构，再insert into xxx select xxx 拷贝数据。注意：这里没有as
mysql> desc stu;
+------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+----------------+
| Id | int(9) | NO | PRI | NULL | auto_increment |
| Name | varchar(100) | NO | | NULL | |
| Age | int(9) | NO | | 0 | |
| updatetime | datetime | YES | | NULL | |
+------------+--------------+------+-----+---------+----------------+
4 rows in set
mysql> select * from stu;
+----+------+-----+---------------------+
| Id | Name | Age | updatetime |
+----+------+-----+---------------------+
| 1 | Andy | 28 | 2015-03-19 15:42:09 |
+----+------+-----+---------------------+
1 row in set
mysql> create table stu4 like stu;
Query OK, 0 rows affected
mysql> desc stu4;
+------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+----------------+
| Id | int(9) | NO | PRI | NULL | auto_increment |
| Name | varchar(100) | NO | | NULL | |
| Age | int(9) | NO | | 0 | |
| updatetime | datetime | YES | | NULL | |
+------------+--------------+------+-----+---------+----------------+
4 rows in set
mysql> select * from stu4;
Empty set
mysql> insert into stu4(name,age,updatetime) select name,age,updatetime from stu;
Query OK, 1 row affected
Records: 1 Duplicates: 0 Warnings: 0
mysql> select * from stu4;
+----+------+-----+---------------------+
| Id | Name | Age | updatetime |
+----+------+-----+---------------------+
| 1 | Andy | 28 | 2015-03-19 15:42:09 |
+----+------+-----+---------------------+
1 row in set
mysql> desc stu;
+------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+----------------+
| Id | int(9) | NO | PRI | NULL | auto_increment |
| Name | varchar(100) | NO | | NULL | |
| Age | int(9) | NO | | 0 | |
| updatetime | datetime | YES | | NULL | |
+------------+--------------+------+-----+---------+----------------+
4 rows in set
mysql> select * from stu;
+----+------+-----+---------------------+
| Id | Name | Age | updatetime |
+----+------+-----+---------------------+
| 1 | Andy | 28 | 2015-03-19 15:42:09 |
+----+------+-----+---------------------+
1 row in set
mysql> create table stu2 as select * from stu;
Query OK, 1 row affected
Records: 1 Duplicates: 0 Warnings: 0
mysql> desc stu2;
+------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+-------+
| Id | int(9) | NO | | 0 | |
| Name | varchar(100) | NO | | NULL | |
| Age | int(9) | NO | | 0 | |
| updatetime | datetime | YES | | NULL | |
+------------+--------------+------+-----+---------+-------+
4 rows in set
mysql> select * from stu2;
+----+------+-----+---------------------+
| Id | Name | Age | updatetime |
+----+------+-----+---------------------+
| 1 | Andy | 28 | 2015-03-19 15:42:09 |
+----+------+-----+---------------------+
1 row in set
create table xxx like xxx，创建新表，约束和原表相同，只拷贝表结构，没有拷贝表的数据，如下：
mysql> desc stu;
+------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+----------------+
| Id | int(9) | NO | PRI | NULL | auto_increment |
| Name | varchar(100) | NO | | NULL | |
| Age | int(9) | NO | | 0 | |
| updatetime | datetime | YES | | NULL | |
+------------+--------------+------+-----+---------+----------------+
4 rows in set
mysql> select * from stu;
+----+------+-----+---------------------+
| Id | Name | Age | updatetime |
+----+------+-----+---------------------+
| 1 | Andy | 28 | 2015-03-19 15:42:09 |
+----+------+-----+---------------------+
1 row in set
mysql> create table stu3 like stu;
Query OK, 0 rows affected
mysql> desc stu3;
+------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+----------------+
| Id | int(9) | NO | PRI | NULL | auto_increment |
| Name | varchar(100) | NO | | NULL | |
| Age | int(9) | NO | | 0 | |
| updatetime | datetime | YES | | NULL | |
+------------+--------------+------+-----+---------+----------------+
4 rows in set
mysql> select * from stu3;
Empty set
如果我想拷贝表的结构（约束和原表相同），同时拷贝表的数据，怎么办？
先create table xxx like xxx，创建表结构，再insert into xxx select xxx 拷贝数据。注意：这里没有as
mysql> desc stu;
+------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+----------------+
| Id | int(9) | NO | PRI | NULL | auto_increment |
| Name | varchar(100) | NO | | NULL | |
| Age | int(9) | NO | | 0 | |
| updatetime | datetime | YES | | NULL | |
+------------+--------------+------+-----+---------+----------------+
4 rows in set
mysql> select * from stu;
+----+------+-----+---------------------+
| Id | Name | Age | updatetime |
+----+------+-----+---------------------+
| 1 | Andy | 28 | 2015-03-19 15:42:09 |
+----+------+-----+---------------------+
1 row in set
mysql> create table stu4 like stu;
Query OK, 0 rows affected
mysql> desc stu4;
+------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+----------------+
| Id | int(9) | NO | PRI | NULL | auto_increment |
| Name | varchar(100) | NO | | NULL | |
| Age | int(9) | NO | | 0 | |
| updatetime | datetime | YES | | NULL | |
+------------+--------------+------+-----+---------+----------------+
4 rows in set
mysql> select * from stu4;
Empty set
mysql> insert into stu4(name,age,updatetime) select name,age,updatetime from stu;
Query OK, 1 row affected
Records: 1 Duplicates: 0 Warnings: 0
mysql> select * from stu4;
+----+------+-----+---------------------+
| Id | Name | Age | updatetime |
+----+------+-----+---------------------+
| 1 | Andy | 28 | 2015-03-19 15:42:09 |
+----+------+-----+---------------------+
1 row in set
