MySQL Replication table structure table data SQL statement Summary

Copy an entire table

The code is as follows	Copy Code
CREATE TABLE new_table select * from Old_table;

Copy tables, do not copy data

The code is as follows	Copy Code
CREATE TABLE new_table SELECT * from old_table where 0;

Other additional features, such as primary keys, indexes, and so on, are not brought to the new table. This is similar to other database products.

It's the core of the two sentences.

1, MySQL copy table structure and data to a new table

The code is as follows	Copy Code
CREATE table ' new table ' SELECT * from ' old table '

2. Copy table structure to new table only

The code is as follows	Copy Code
CREATE table ' new table ' SELECT * from ' old table ' WHERE 1=2 Or CREATE table ' new table ' like ' Old table '

3, copy the old table data to the new table (assuming two table structure)

The code is as follows	Copy Code
INSERT into ' new table ' SELECT * from ' old table '

4, copy the old table data to the new table (assuming that two table structure is not the same)

The code is as follows	Copy Code
INSERT into ' New table ' (Field 1, Field 2,.......) SELECT field 1, Field 2,...... From ' Old table '

5, show create table old tables;

This lists the creation commands for the old table. We just need to copy the command, change the name of the table, and we can create a table that's exactly the same.

When these MySQL interface tools will be the error, this time with the command line better. For example, to copy a table field data to a different list of fields, you can write this:

The code is as follows	Copy Code
UPDATE tb_1 INNER JOIN tb_2 on tb_1.tid = Tb_2.tid SET tb_1.tcontent = tb_2.tcontent

The following is a practical example of writing a link to a phpcms generated static page to the URL field in the Phpcms_content table:

This is the first way to cobble together the required URL field columns.

The code is as follows	Copy Code
SELECT CONCAT (From_unixtime (inputtime, '%y/%m%d '), '/', ContentID, '. html ') as DT from Phpcms_content ORDER by ContentID DE Sc

Then, in the Query Editor (navicat), copy the entire segment to the URL column in the Phpcms_content table