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);