复制表

创建测试表test：mysql> create table test(

    -> id int primary key auto_increment,    -> name varchar(32)    -> );Query OK, 0 rows affected (0.16 sec)

?插入几条数据后复制test到test2中mysql> create table test2 select * from test;

Query OK, 2 rows affected (0.08 sec)Records: 2  Duplicates: 0  Warnings: 0mysql> select * from test2;+----+-------+| id | name  |+----+-------+|  1 | test1 ||  2 | test2 |+----+-------+2 rows in set (0.00 sec)

?上面是复制了整张表，我们下面看复制表的一部分字段，相信一这样说就应该想到怎么做了

创建测试表test3mysql> create table test3(

    -> id int primary key auto_increment,    -> name varchar(32),    -> age int,    -> birthday timestamp default now()    -> );Query OK, 0 rows affected (0.06 sec)

?插入几条数据后开始复制test3到test4中mysql> create table test4 select id,name,birthday from test3;

Query OK, 7 rows affected (0.06 sec)Records: 7  Duplicates: 0  Warnings: 0mysql> select * from test4;+----+------+---------------------+| id | name | birthday            |+----+------+---------------------+|  1 | test | 2010-12-30 23:16:08 ||  2 | test | 2010-12-30 23:16:11 ||  3 | test | 2010-12-30 23:16:12 ||  4 | test | 2010-12-30 23:16:13 ||  5 | test | 2010-12-30 23:16:14 ||  6 | test | 2010-12-30 23:16:16 ||  7 | test | 2010-12-30 23:16:19 |+----+------+---------------------+7 rows in set (0.00 sec)

?从上面可以看出，如果是只创建含有约束条件的值的表副本就简单了直接where就Ok了mysql> create table test5 select * from test4 where id<3;

Query OK, 2 rows affected (0.11 sec)Records: 2  Duplicates: 0  Warnings: 0mysql> select * from test5;+----+------+---------------------+| id | name | birthday            |+----+------+---------------------+|  1 | test | 2010-12-30 23:16:08 ||  2 | test | 2010-12-30 23:16:11 |+----+------+---------------------+2 rows in set (0.00 sec)

?只复制表结构，不复制表数据

一：(取巧方法)mysql> create table test6 select * from test3 where 0=1;

Query OK, 0 rows affected (0.08 sec)Records: 0  Duplicates: 0  Warnings: 0mysql> select * from test6;Empty set (0.00 sec)

?二：(MySQL提供了一个现成的语法)mysql> create table test7 like test6;

Query OK, 0 rows affected (0.06 sec)mysql> select * from test7;Empty set (0.00 sec)mysql> desc test7;+----------+-------------+------+-----+---------------------+-------+| Field    | Type        | Null | Key | Default             | Extra |+----------+-------------+------+-----+---------------------+-------+| id       | int(11)     | NO   |     | 0                   |       || name     | varchar(32) | YES  |     | NULL                |       || age      | int(11)     | YES  |     | NULL                |       || birthday | timestamp   | NO   |     | 0000-00-00 00:00:00 |       |+----------+-------------+------+-----+---------------------+-------+4 rows in set (0.00 sec)

?三：(复制旧表部分结构，同时增加新字段)mysql> create table test8 (location varchar(32),position varchar(32)) select id,name,birthday from test7;

Query OK, 0 rows affected (0.08 sec)Records: 0  Duplicates: 0  Warnings: 0mysql> desc test8;+----------+-------------+------+-----+---------------------+-------+| Field    | Type        | Null | Key | Default             | Extra |+----------+-------------+------+-----+---------------------+-------+| location | varchar(32) | YES  |     | NULL                |       || position | varchar(32) | YES  |     | NULL                |       || id       | int(11)     | NO   |     | 0                   |       || name     | varchar(32) | YES  |     | NULL                |       || birthday | timestamp   | NO   |     | 0000-00-00 00:00:00 |       |+----------+-------------+------+-----+---------------------+-------+5 rows in set (0.00 sec)