Summary of commonly used statements for MySQL copy table or table data

MySQL copy table or table data is commonly used to summarize the statements.

If we have one of the following tables:

ID Username password-----------------------------------1 Admin ************* 2 Sameer ************* 3 Stewart #SQLCREATETABLEIFNotEXISTS' Admin ' (' ID 'int (6) unsigned NOT null auto_increment,  ' username ' varchar (50) not null default  ",  ' password ' varchar (100) default null, primary KEY (") 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)

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.

CREATE TABLE newadmin AS   (   SELECT * FROM admin ) 

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

CREATE TABLE newadmin LIKE admin;   INSERT INTO newadmin SELECT * FROM admin; 

4. We can operate different databases.

CREATE TABLE newadmin LIKE shop.admin;   CREATE TABLE newshop.newadmin LIKE shop.admin; 

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

CREATE TABLE newadmin AS   (   SELECT username, password FROM admin ) 

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

CREATE TABLE newadmin AS   (   SELECT id, username AS uname, password AS pass FROM admin )

7. We can also copy part of the data.

CREATE TABLE newadmin AS   (   SELECT * FROM admin WHERE LEFT(username,1) = ‘s‘ ) 

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

CREATE TABLE newadmin   (   id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY ) AS ( SELECT * FROM admin )

9.MySQL copy table structure and data to a new table

CREATE TABLE 新表  SELECT * FROM 旧表 

10.Mysql Copy only table structure does not replicate data

CREATE TABLE 新表SELECT * FROM 旧表 WHERE 1=2即:让WHERE条件不成立.

11. Duplicating tables with different structures

create table 新表(字段1,字段2,,,) SELECT 字段1,字段2... FROM 旧表

Reference:

Summary of commonly used statements for MySQL copy table or table data