Mysql 复制表

Mysql 复制表


> create table mark (id int auto_increment primary key,name char(10),math int,phy int);
> insert into mark values (null,"John",60,70),(null,"Sarah",90,88),(null,"Mike",70,80);
> select * from mark;
+----+-------+------+------+
| id | name  | math | phy  |
+----+-------+------+------+
|  1 | John  |   60 |   70 |
|  2 | Sarah |   90 |   88 |
|  3 | Mike  |   70 |   80 |
+----+-------+------+------+


创建副本（复制整个表，但不能复制键）
> create table grade select * from mark;
> select * from grade;
+----+-------+------+------+
| id | name  | math | phy  |
+----+-------+------+------+
|  1 | John  |   60 |   70 |
|  2 | Sarah |   90 |   88 |
|  3 | Mike  |   70 |   80 |
+----+-------+------+------+


复制表的部分字段（不能复制键）
> create table student select name from mark;
> select * from student;
+-------+
| name  |
+-------+
| John  |
| Sarah |
| Mike  |
> create table id select id from mark;
> select * from id;
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
+----+
mysql> desc id;        // 不能复制键
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | NO   |     | 0       |       |
+-------+---------+------+-----+---------+-------+


创建已存在表的空副本（不能复制键）
> create table empty select * from mark where 0=1;


手动创建键
> alter table empty add primary key (id);
> alter table grade add primary key (name);