MySQL replicated table Data Operations SQL statements

MySQL Copy table Data operation I believe that we will not be too unfamiliar, the following for you to detail the MySQL copy table data to the new table steps, I hope you will be enlightened.

1.MySQL copy table structure and data to 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

That is, let the Where condition not be tenable.

Method Two: (Low version of MySQL does not support, mysql4.0.25 does not support, MYSQL5 has been supported)

The code is as follows	Copy Code
CREATE Table New Table Like old table

3. Copy old table data to new table (assuming two table structure)

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

4. Copy old table data to new table (assuming two table structures are different)

The code is as follows	Copy Code
INSERT into new Table (Field 1, Field 2,.......) SELECT Field 1, Field 2,...... From old table

Examples are as follows:

The MYTBL table in the production database is quickly copied to the mytbl_new,2 command as follows:

The code is as follows	Copy Code
CREATE TABLE mytbl_new like production.mytbl; INSERT mytbl_new SELECT * from PRODUCTION.MYTBL;

The first command is to create a new datasheet mytbl_new and copy the MYTBL datasheet structure.

The second command is to copy the data from the datasheet mytbl to the new table mytbl_new.

Note: PRODUCTION.MYTBL is the name of the database that specifies the table to be replicated production. It is optional.

If there is no production. , the MySQL database will assume mytbl in the current operation of the database.

Other methods:

Programme 1:

Copy an entire table
CREATE TABLE new_table SELECT * from old_table;

Copy, do not copy data
CREATE TABLE new_table SELECT * from old_table where 0;

Note: This scenario simply builds a table of the results of the SELECT statement. So new_table This table will not have a primary key, index.

Programme 2:

If we have one of these tables:

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

CREATE TABLE IF not EXISTS ' admin ' (
' ID ' int (6) unsigned not NULL auto_increment,
' username ' varchar not NULL default ',
' Password ' varchar default NULL,
PRIMARY KEY (' id ')
) Engine=myisam DEFAULT charset=latin1 auto_increment=4;

1. The following statement copies the table structure to the new table Newadmin. (Data in the table will not be copied)

CREATE TABLE newadmin like admin

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

The code is as follows	Copy Code
CREATE TABLE Newadmin ( SELECT * From admin )

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

The code is as follows	Copy Code
CREATE TABLE newadmin like admin; INSERT into Newadmin SELECT * from admin;

4. We can operate a different database.

The code is as follows	Copy Code
CREATE TABLE newadmin like shop.admin; CREATE TABLE newshop.newadmin like shop.admin;

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

The code is as follows	Copy Code
CREATE TABLE Newadmin ( SELECT username, password from admin )

6. We can also change the name of the field of the newly created table.

The code is as follows	Copy Code
CREATE TABLE Newadmin ( SELECT ID, username as uname, password as pass from admin )

7. We can also copy part of the data.

The code is as follows	Copy Code
CREATE TABLE Newadmin ( SELECT * FROM admin WHERE Left (username,1) = ' s ' )

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

code is as follows	copy code
create TABLE Newadmin ( ID INTEGER not NULL auto_increment PRIMARY KEY ) ( SELECT * from admin )