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

转载于:https://www.cnblogs.com/nzbbody/p/4356254.html