MySQL copy tables in several ways

1. Copy the table structure into the new table. (Data in the table is not copied)

CREATE table new table like old table;
Or
CREATE Table New Table SELECT * from old table WHERE 1=2;

　　

2. Copy the data into the new table. (The new table will not have a primary key, index)

CREATE table New Table as   (       SELECT * from       old table);

　　

3. Really copy a table. You can use the following statement.

CREATE table new table like old table;   INSERT into new table SELECT * from old table;  
Or
CREATE Table New Table SELECT * from old table;

　　

4. Operate a different database.

CREATE table new table like library 1. old table;   CREATE Table Library 2. New table like library 1. old table;  

　　

5. Copy some of the fields in a table.

CREATE table New Table   as (       SELECT field 1, Field 2,... From the old table);

We can also copy part of the data

CREATE Table New Table as
（
SELECT * from old table WHERE left (username,1) = ' s '
);

Copy the data from the old table to the new table
(assuming two table structures)
INSERT into new table SELECT * from old table;

(assuming two table structures are different)
INSERT into new table
(
Field 1, Field 2,.......


from the old table;

　　

6. Rename the field of the newly created table.

CREATE table New Table as   (       SELECT ID, username as uname, password as pass from old table);

　　

7. Copy table 1 structure to table 2

SELECT * into table 2 from table 1 WHERE 1=2;

Copy all table 1 contents to table 2
SELECT * into table 2 from table 1;

　　

8. Create a table to define the field information in the table at the same time.

CREATE Table New Table (    ID INTEGER not NULL auto_increment PRIMARY KEY) as (    SELECT * from old table   

9. Display the creation command for the old table

Show create table old table;

　　

MySQL copy tables in several ways