Mysql SQL statements that are completely copied between two tables

In MySql4.0.12, after using the "create table bak_name as select * from original_name" statement

The generated bak table does not have a primary key and an index. When using it, you must note that you can first create one and the table structure to be backed up.

Example: back_name. Use the following method to import data.

Insert into bak_table select * from original_name

In the mysql tutorial, you can directly copy an existing table as another table using commands.

Method 1: create table mmm select * from bbb; Note: This Command requires the mmm table to be in the database tutorial.

Does not exist
This command creates a new table mmm And the table structure and data in bbb are exactly the same as those in mmm. You can also export some fields.

Create table mmm select Field 1, Field 2 from bbb;

Method 2: insert into mmm select * bbb; this statement implements the same function as the previous statement, but only requires

The mmm table must exist in the database.

 

Mysql>
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
-> );
Query OK, 0 rows affected (0.03 sec)

Mysql>
Mysql>
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,

'Classic', '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,

'Classic', '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

Mysql>
Mysql> select * from Topic;
+ --------- + ---------------- + --------- + ---------- + ------------ + --------

-- + --------------------- +
| TopicID | Name | InStock | OnOrder | Reserved | Department | Category

| RowUpdate |
+ --------- + ---------------- + --------- + ---------- + ------------ + --------

-- + --------------------- +
| 1 | Java | 10 | 5 | 3 | Popular | Rock

| 19:09:52 |
| 2 | JavaScript | 10 | 5 | 3 | Classical | Opera

| 19:09:52 |
| 3 | C Sharp | 17 | 4 | 1 | Popular | Jazz

| 19:09:52 |
| 4 | C | 9 | 4 | 2 | Classical | Dance

| 19:09:52 |
| 5 | C ++ | 24 | 2 | 5 | Classical | General

| 19:09:52 |
| 6 | Perl | 16 | 6 | 8 | Classical | Vocal

| 19:09:52 |
| 7 | Python | 2 | 25 | 6 | Popular | Blues

| 19:09:52 |
| 8 | Php | 32 | 3 | 10 | Popular | Jazz

| 19:09:52 |
| 9 | ASP.net | 12 | 15 | 13 | Popular | Country

| 19:09:52 |
| 10 | VB.net | 5 | 20 | 10 | Popular | New Age

| 19:09:52 |
| 11 | VC.net | 24 | 11 | 14 | Popular | New Age

| 19:09:52 |
| 12 | UML | 42 | 17 | 17 | Classical | General

| 19:09:52 |
| 13 | www.java2s.com | 25 | 44 | 28 | Classical | Dance

| 19:09:52 |
| 14 | Oracle | 32 | 15 | 12 | Classical | General

| 19:09:52 |
| 15 | Pl/SQL | 20 | 10 | 5 | Classical | Opera

| 19:09:52 |
| 16 | SQL Server | 23 | 12 | 8 | Classical | General

| 19:09:52 |
+ --------- + ---------------- + --------- + ---------- + ------------ + --------

-- + --------------------- +
16 rows in set (0.00 sec)

Mysql>
Mysql> create table Topic2
-> (
-> TopicID smallint not null primary key,
-> Name VARCHAR (50) not null,
-> InStock SMALLINT UNSIGNED NOT NULL
->)
-> SELECT TopicID, Name, InStock
-> FROM Topic
-> WHERE Category = 'Blues' OR Category = 'jazz ';
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0

Mysql>
Mysql> select * from Topic2;
+ --------- +
| TopicID | Name | InStock |
+ --------- +
| 3 | C Sharp | 17 |
| 7 | Python | 2 |
| 8 | Php | 32 |
+ --------- +
3 rows in set (0.00 sec)