复制表

创建测试表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: 0



mysql> 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: 0



mysql> 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: 0



mysql> 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: 0



mysql> 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: 0



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