MySQL copy tables in several ways

MySQL Copy table operation we will often use, the following will give you a detailed description of several MySQL copy tables, I hope you learn the MySQL copy table can be helpful.

If we have one of the following tables:

ID Username password
-----------------------------------
1 Admin *************
2 Sameer *************
3 Stewart *************

1. CREATE TABLE IF not EXISTS ' admin ' (
2. ' ID ' int (6) unsigned not NULL auto_increment,
3. ' username ' varchar (not NULL default ' '),
4. ' Password ' varchar default NULL,
5. PRIMARY KEY (' id ')
6. ) engine=MyISAM DEFAULT charset=latin1 auto_increment=4;

1. The following statement copies the table structure into the new table newadmin. (Data in the table is not copied)

1. CREATE TABLE newadmin like admin

2. The following statement copies the data to the new table. Note: This statement actually only constructs a table for the result of the SELECT statement. So newadmin This table will not have a primary key, index.

1. CREATE TABLE Newadmin as
2. (
3. SELECT *
4. From admin
5. )

3. If you want to actually copy a table. You can use the following statement.

1. CREATE TABLE newadmin like admin;
2. INSERT into Newadmin SELECT * from admin;

4. We can operate different databases.

1. CREATE TABLE newadmin like shop.admin;
2. CREATE TABLE newshop.newadmin like shop.admin;

5. We can also copy some of the fields in a table.

1. CREATE TABLE Newadmin as
2. (
3. SELECT username, password from admin
4. )

6. We can also rename the fields of the newly created table.

1. CREATE TABLE Newadmin as
2. (
3. SELECT ID, username as uname, password as pass from admin
4. )

7. We can also copy part of the data.

1. CREATE TABLE Newadmin as
2. (
3. SELECT * FROM admin WHERE Left (username,1) = ' s '
4. )

8. We can also define the field information in the table while creating the table.

1. CREATE TABLE Newadmin
2. (
3. ID INTEGER not NULL auto_increment PRIMARY KEY
4. )
5. As
6. (
7. SELECT * from admin
8. )

Transferred from: http://database.51cto.com/art/201011/234776.htm

MySQL copy tables in several ways