在mysql数据库开发中,我们有时候需要复制或拷贝一张表结构和数据到例外一张表,这个时候我们可以使用create ... select ... from语句来实现,本文章向大家介绍mysql复制表结构和数据一个简单实例,
比如现在有一张表,我们要将该表复制一份,以备以后使用,那么如何使用mysql语句来实现呢?其实我们可以直接使用create ... select ... from语句来实现,具体实现方法请看下面实例。
我们先来创建一张Topic表,创建Topic表的SQL语句如下:
1
2
3
4
5
6
7
8
9
10
|
mysql> CREATE TABLE Topic(
-> TopicID SMALLINT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
-> Name VARCHAR (50) NOT NULL ,
-> InStock SMALLINT UNSIGNED NOT NULL ,
-> OnOrder SMALLINT UNSIGNED NOT NULL ,
-> Reserved SMALLINT UNSIGNED NOT NULL ,
-> Department ENUM( 'Classical' , 'Popular' ) NOT NULL ,
-> Category VARCHAR (20) NOT NULL ,
-> RowUpdate TIMESTAMP NOT NULL
-> );
|
向Topic表中插入数据:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
|
mysql> INSERT INTO Topic ( Name , InStock, OnOrder, Reserved, Department, Category) VALUES
-> ( 'Java' , 10, 5, 3, 'Popular' , 'Rock' ),
-> ( 'JavaScript' , 10, 5, 3, 'Classical' , 'Opera' ),
-> ( 'C Sharp' , 17, 4, 1, 'Popular' , 'Jazz' ),
-> ( 'C' , 9, 4, 2, 'Classical' , 'Dance' ),
-> ( 'C++' , 24, 2, 5, 'Classical' , 'General' ),
-> ( 'Perl' , 16, 6, 8, 'Classical' , 'Vocal' ),
-> ( 'Python' , 2, 25, 6, 'Popular' , 'Blues' ),
-> ( 'Php' , 32, 3, 10, 'Popular' , 'Jazz' ),
-> ( 'ASP.net' , 12, 15, 13, 'Popular' , 'Country' ),
-> ( 'VB.net' , 5, 20, 10, 'Popular' , 'New Age' ),
-> ( 'VC.net' , 24, 11, 14, 'Popular' , 'New Age' ),
-> ( 'UML' , 42, 17, 17, 'Classical' , 'General' ),
-> ( 'www.java2s.com' ,25, 44, 28, 'Classical' , 'Dance' ),
-> ( 'Oracle' , 32, 15, 12, 'Classical' , 'General' ),
-> ( 'Pl/SQL' , 20, 10, 5, 'Classical' , 'Opera' ),
-> ( 'Sql Server' , 23, 12, 8, 'Classical' , 'General' );
Query OK, 16 rows affected (0.00 sec)
Records: 16 Duplicates: 0 Warnings: 0
|
现在我们要将这张表复制一份,具体操作如下:
1
2
3
4
5
6
7
8
9
10
11
12
13
|
mysql> CREATE TABLE Topic2
-> (
-> TopicID SMALLINT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
-> Name VARCHAR (50) NOT NULL ,
-> InStock SMALLINT UNSIGNED NOT NULL ,
-> OnOrder SMALLINT UNSIGNED NOT NULL ,
-> Reserved SMALLINT UNSIGNED NOT NULL ,
-> Department ENUM( 'Classical' , 'Popular' ) NOT NULL ,
-> Category VARCHAR (20) NOT NULL ,
-> RowUpdate TIMESTAMP NOT NULL
-> )
-> SELECT *
-> FROM Topic
|
这样表Topic2和Topic表不仅拥有相同的表结构,表数据也是一样的了。
例外,如果我们只需要复制表结构,不需要复制数据,也可以使用create like来实现:
create table a like users;
感谢阅读此文,希望能帮助到大家,谢谢大家对本站的支持!