Quick Copy Data table method summary in MySQL

This article will focus on a combination of two MySQL commands that will create a new data table of the same structure and data based on the original data table.

This helps you to quickly copy tables as test data during development without risking direct manipulation of the running data tables.

Examples are as follows:

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

CREATE TABLE  like production.mytbl; INSERT SELECT *  from Production.mytbl;

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

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

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

If there is no production. , the MySQL database will assume that MYTBL is in the current operational database.

Other methods:
Scenario 1:
Copy entire table

CREATE TABLE SELECT *  from Old_table;

Copy, do not copy data

CREATE TABLE SELECT *  from 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.

Scenario 2:
If we have one of the following tables:

ID Username password

1. The following statement copies the table structure into the new table newadmin. (Data in the table is not copied)

CREATE TABLE  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  as  SELECT* from Admin)

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

CREATE TABLE  like  INSERTintoSELECT* from admin;

4. We can operate different databases.

CREATE TABLE  like  CREATETABLE like shop.admin;

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

CREATE TABLE  as  SELECT from Admin)

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

CREATE TABLE  as  SELECT as as from Admin)

7. We can also copy part of the data.

CREATE TABLE  as  SELECT*fromWHERELeft (username,1= ' s '  )

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

CREATE TABLE  INTEGER notNULLPRIMARYKEY as  SELECT *  from Admin)

Original: https://www.cnblogs.com/yaoyao1556/p/3813999.html

Quick Copy Data table method summary in MySQL