MySQL copy tables in several ways

Source: Internet
Author: User

MySQL Copy table operation we will often use, the following will give you a detailed description of several MySQL copy tables, I hope you learn the MySQL copy table can be helpful.

If we have one of the following tables:

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

    1. CREATE TABLE IF not EXISTS ' admin ' (
    2. ' ID ' int (6) unsigned not NULL auto_increment,
    3. ' username ' varchar (not NULL default ' '),
    4. ' Password ' varchar default NULL,
    5. PRIMARY KEY (' id ')
    6. ) 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)

    1. 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.

    1. CREATE TABLE Newadmin as
    2. (
    3. SELECT *
    4. From admin
    5. )

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

    1. CREATE TABLE newadmin like admin;
    2. INSERT into Newadmin SELECT * from admin;

4. We can operate different databases.

    1. CREATE TABLE newadmin like shop.admin;
    2. CREATE TABLE newshop.newadmin like shop.admin;

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

    1. CREATE TABLE Newadmin as
    2. (
    3. SELECT username, password from admin
    4. )

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

    1. CREATE TABLE Newadmin as
    2. (
    3. SELECT ID, username as uname, password as pass from admin
    4. )

7. We can also copy part of the data.

    1. CREATE TABLE Newadmin as
    2. (
    3. SELECT * FROM admin WHERE Left (username,1) = ' s '
    4. )

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

      1. CREATE TABLE Newadmin
      2. (
      3. ID INTEGER not NULL auto_increment PRIMARY KEY
      4. )
      5. As
      6. (
      7. SELECT * from admin
      8. )

MySQL copy tables in several ways

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.