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
上一篇:create table repo_folder_operate_log_bak as select * from repo_folder_operate_log;


下一篇:create table:使用SELECT语句创建表