在mysql數(shù)據(jù)庫(kù)開(kāi)發(fā)中，我們有時(shí)候需要復(fù)制或拷貝一張表結(jié)構(gòu)和數(shù)據(jù)到例外一張表，這個(gè)時(shí)候我們可以使用create ... select ... from語(yǔ)句來(lái)實(shí)現(xiàn)，本文章向大家介紹mysql復(fù)制表結(jié)構(gòu)和數(shù)據(jù)一個(gè)簡(jiǎn)單實(shí)例，
比如現(xiàn)在有一張表，我們要將該表復(fù)制一份，以備以后使用，那么如何使用mysql語(yǔ)句來(lái)實(shí)現(xiàn)呢？其實(shí)我們可以直接使用create ... select ... from語(yǔ)句來(lái)實(shí)現(xiàn)，具體實(shí)現(xiàn)方法請(qǐng)看下面實(shí)例。
我們先來(lái)創(chuàng)建一張Topic表，創(chuàng)建Topic表的SQL語(yǔ)句如下：
|
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表中插入數(shù)據(jù)：
|
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 |
現(xiàn)在我們要將這張表復(fù)制一份，具體操作如下：
|
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表不僅擁有相同的表結(jié)構(gòu)，表數(shù)據(jù)也是一樣的了。
例外，如果我們只需要復(fù)制表結(jié)構(gòu)，不需要復(fù)制數(shù)據(jù)，也可以使用create like來(lái)實(shí)現(xiàn)：
create table a like users;
感謝閱讀此文，希望能幫助到大家，謝謝大家對(duì)本站的支持！
