Several ways to copy a table in mysql

Mysql copy table operations are often used. The following describes how to copy tables from mysql in detail. It is helpful for you to learn how to copy tables from mysql.

Suppose we have the following table:

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(50) NOT NULL default '',   
  
  
`password` varchar(100) default NULL,   
  
  
PRIMARY KEY (`id`)   
  
  
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;  
 
 

1. The following statement copies the table structure to newadmin. Will not copy the data in the table)

 
 

  
  
CREATE TABLE newadmin LIKE admin  
  
  
 
 
 

2. The following statement copies the data to the new table. Note: This statement only creates a table for the results of the select statement. Therefore, the newadmin table does not have a primary key and an index.

 
 

  
  
CREATE TABLE newadmin AS   
  
  
(   
  
  
SELECT *   
  
  
FROM admin   
  
  
)  
  
  
 
 
 

3. If you want to 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 fields in a table.

 
 

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

6. We can also change the name of the field in the new 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. You can also define field information in the table while creating the table.

 
 

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

In-depth study on MySQL's data deletion from multiple tables

Simple MySQL large table backup method

Analysis of MySQL Chinese Table creation Problems

MySQL authorization table usage example

Disadvantages of MySQL memory tables