Mysql copies the table structure and data instance code, mysql and

Mysql copies the table structure and data instance code, mysql and

In mysql database development, we sometimes need to copy or copy a table structure and data to an exception table. In this case, we can use create... select... from statement. This article introduces a simple mysql copy table structure and data instance,

For example, if you want to copy a table for future use, how can we use mysql statements? In fact, we can use the create... select... from statement directly. For the specific implementation method, see the following example.

First, create a Topic table. The SQL statement for creating a Topic table is as follows:

mysql> CREATE TABLE Topic(  ->  TopicID   SMALLINT NOT NULL AUTO_INCREMENT PRIMARY KEY,  ->  Name    VARCHAR(50) NOT NULL,  ->  InStock   SMALLINT UNSIGNED NOT NULL,  ->  OnOrder   SMALLINT UNSIGNED NOT NULL,  ->  Reserved  SMALLINT UNSIGNED NOT NULL,  ->  Department ENUM('Classical', 'Popular') NOT NULL,  ->  Category  VARCHAR(20) NOT NULL,  ->  RowUpdate  TIMESTAMP NOT NULL  -> );

Insert data to the Topic table:

mysql> INSERT INTO Topic (Name,     InStock, OnOrder, Reserved, Department,  Category) VALUES  ->          ('Java',     10,   5,    3,    'Popular',  'Rock'),  ->          ('JavaScript',  10,   5,    3,    'Classical', 'Opera'),  ->          ('C Sharp',    17,   4,    1,    'Popular',  'Jazz'),  ->          ('C',       9,    4,    2,    'Classical', 'Dance'),  ->          ('C++',      24,   2,    5,    'Classical', 'General'),  ->          ('Perl',     16,   6,    8,    'Classical', 'Vocal'),  ->          ('Python',    2,    25,   6,    'Popular',  'Blues'),  ->          ('Php',      32,   3,    10,    'Popular',  'Jazz'),  ->          ('ASP.net',    12,   15,   13,    'Popular',  'Country'),  ->          ('VB.net',    5,    20,   10,    'Popular',  'New Age'),  ->          ('VC.net',    24,   11,   14,    'Popular',  'New Age'),  ->          ('UML',      42,   17,   17,    'Classical', 'General'),  ->          ('www.java2s.com',25,   44,   28,    'Classical', 'Dance'),  ->          ('Oracle',    32,   15,   12,    'Classical', 'General'),  ->          ('Pl/SQL',    20,   10,   5,    'Classical', 'Opera'),  ->          ('Sql Server',  23,   12,   8,    'Classical', 'General');Query OK, 16 rows affected (0.00 sec)Records: 16 Duplicates: 0 Warnings: 0

Copy the table as follows:

mysql> CREATE TABLE Topic2  -> (  ->  TopicID   SMALLINT NOT NULL AUTO_INCREMENT PRIMARY KEY,  ->  Name    VARCHAR(50) NOT NULL,  ->  InStock   SMALLINT UNSIGNED NOT NULL,  ->  OnOrder   SMALLINT UNSIGNED NOT NULL,  ->  Reserved  SMALLINT UNSIGNED NOT NULL,  ->  Department ENUM('Classical', 'Popular') NOT NULL,  ->  Category  VARCHAR(20) NOT NULL,  ->  RowUpdate  TIMESTAMP NOT NULL  -> )  -> SELECT *  -> FROM Topic

In this way, Topic2 and Topic tables not only have the same table structure, but also have the same table data.

With the exception, if you only need to copy the table structure and do not need to copy data, you can also use create like:

Create table a like users;

Thank you for reading this article and hope to help you. Thank you for your support for this site!