复制表

复制表

作用: 1.备份表; 2.快速建表

格式:
create table 表名 select * from 表名;

mysql> show tables;
+---------------+
| Tables_in_db3 |
+---------------+
| t1 |
| t2 |
| t3 |
| t4 |
| user |
+---------------+

mysql> create database db4;
mysql> create table db4.user2 select * from db3.user;
mysql> use db4;
mysql> select database();
+------------+
| database() |
+------------+
| db4 |
+------------+

mysql> show tables;
+---------------+
| Tables_in_db4 |
+---------------+
| user2 |
+---------------+

mysql> select * from user2;

源表的表结构中，字段的键值不会复制给新表
mysql> desc db3.user;
+----------+-----------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+-----------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment | 源表有这个
| name | char(30) | YES | | NULL | |
| age | int(11) | YES | | 18 | |
| password | char(1) | YES | | NULL | |
| uid | int(11) | YES | | NULL | |
| gid | int(11) | YES | | NULL | |
| comment | char(150) | YES | | NULL | |
| homedir | char(150) | YES | | NULL | |
| shell | char(50) | YES | | NULL | |
+----------+-----------+------+-----+---------+----------------+

mysql> desc user2;
+----------+-----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-----------+------+-----+---------+-------+
| id | int(11) | NO | | 0 | | 新表没有这个
| name | char(30) | YES | | NULL | |
| age | int(11) | YES | | 18 | |
| password | char(1) | YES | | NULL | |
| uid | int(11) | YES | | NULL | |
| gid | int(11) | YES | | NULL | |
| comment | char(150) | YES | | NULL | |
| homedir | char(150) | YES | | NULL | |
| shell | char(50) | YES | | NULL | |
+----------+-----------+------+-----+---------+-------+

mysql> create table db4.user select * from db3.user
-> where 1=2; 查询时添加一个不成立的条件，就可以只复制表结构，而不要里面的值

mysql> use db4;
mysql> show tables;
+---------------+
| Tables_in_db4 |
+---------------+
| user |
| user2 |
+---------------+

mysql> select * from user; 新表里没有内容
Empty set (0.00 sec)

mysql> desc user; 新表里有表结构
+----------+-----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-----------+------+-----+---------+-------+
| id | int(11) | NO | | 0 | |
| name | char(30) | YES | | NULL | |
| age | int(11) | YES | | 18 | |
| password | char(1) | YES | | NULL | |
| uid | int(11) | YES | | NULL | |
| gid | int(11) | YES | | NULL | |
| comment | char(150) | YES | | NULL | |
| homedir | char(150) | YES | | NULL | |
| shell | char(50) | YES | | NULL | |
+----------+-----------+------+-----+---------+-------+

mysql> create table db4.t1 select name,uid,gid from db3.user 复制表db3.user里面的name,uid,gid字段，并且只要前3行
-> limit 3;

mysql> select * from db4.t1;
+--------+------+------+
| name | uid | gid |
+--------+------+------+
| root | 1 | -1 |
| bin | 2 | 0 |
| daemon | 3 | 1 |
+--------+------+------+