Mysql: copying table structure and table data _ MySQL

Mysql copies the table structure, table data bitsCN. com1, and copies the table structure and data to the new table.
Create table new table select * FROM old TABLE
This method will copy all content in oldtable. of course, we can use delete from newtable; to delete it.
However, the worst thing about this method is that the new table does not have the primary key, Extra (auto_increment) and other attributes of the old table. You need to use "alter" to add it yourself, and it is easy to make a mistake.


2. only copy the table structure to the new table
Create table new table select * FROM old table where 1 = 2
Or create table new table like old TABLE


3. copy the data from the old table to the new table (assuming the two tables have the same structure)
Insert into new table SELECT * FROM old table


4. copy the data from the old table to the new table (assuming the two tables have different structures)
Insert into new table (Field 1, field 2,...) SELECT field 1, field 2,... FROM old table


5. copy the Table 1 structure to Table 2.
SELECT * INTO table 2 FROM Table 1 WHERE 1 = 2


6. you can copy all contents of Table 1 to Table 2.
SELECT * INTO table 2 FROM Table 1


7. show create table old table;
This will list the creation commands of the old table. We only need to copy the command and change the table name to create a table with the same name.


8. mysqldump
Use mysqldump to dump the table, change its name, and export it back, or directly run bitsCN.com in the command line.