复制表
创建测试表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)
