假设存在以下Table：
mysql> select * from staff;
+----+----------+-------+
| id | name | slary |
+----+----------+-------+
| | haofugui | |
| | guoming | |
| | haotian | |
+----+----------+-------+
rows in set (0.00 sec) mysql> describe staff;
+-------+----------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+----------------+
| id | int() | NO | PRI | NULL | auto_increment |
| name | char() | YES | | NULL | |
| slary | int() | YES | | NULL | |
+-------+----------+------+-----+---------+----------------+
rows in set (0.00 sec)
1. 只复制表结构到新表
语句1：CREATE TABLE new_table_name SELECT [field1,field2... | *] FROM old_table_name WHERE 1=2;
语句2：CREATE TABLE new_table _name LIKE old_table_name;
示例：
mysql> create table staff_bak select id,name from staff where 1=2; //根据旧表的指定属性创建一个新的空表
Query OK, rows affected (0.03 sec)
Records: Duplicates: Warnings: mysql> select * from staff_bak; //新建的数据库为空表
Empty set (0.00 sec) mysql> describe staff_bak; //原表的主键和自动增长不能被复制
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id | int() | NO | | | |
| name | char() | YES | | NULL | |
+-------+----------+------+-----+---------+-------+
rows in set (0.00 sec)
mysql> create table staff_bak_1 like staff; //根据旧表创建一个新的空表，无法指定属性或属性组
Query OK, rows affected (0.03 sec) mysql> select * from staff_bak_1;
Empty set (0.00 sec) mysql> describe staff_bak_1; //所有数据类型和完整性约束条件都能被复制，包括主键和自动增长
+-------+----------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+----------------+
| id | int() | NO | PRI | NULL | auto_increment |
| name | char() | YES | | NULL | |
| slary | int() | YES | | NULL | |
+-------+----------+------+-----+---------+----------------+
rows in set (0.00 sec)
注意：语句1可指定复制的属性范围，但无法复制主键类型和自增方式；
语句2会把旧表的所有字段类型都复制到新表，但无法复制指定属性或属性组。
2. 复制表结构及数据到新表
语句：CREATE TABLE new_table_name SELECT [field1,field2... | *] FROM old_table_name;
mysql> create table staff_bak select id,name from staff; //根据旧表将指定属性及其数据创建新表
Query OK, rows affected (0.03 sec)
Records: Duplicates: Warnings: mysql> describe staff_bak; //新表结构展示
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id | int() | NO | | | |
| name | char() | YES | | NULL | |
+-------+----------+------+-----+---------+-------+
rows in set (0.00 sec) mysql> select * from staff_bak; //新表数据显示
+----+----------+
| id | name |
+----+----------+
| | haofugui |
| | guoming |
| | haotian |
+----+----------+
rows in set (0.00 sec)