Mysql SQL statements that are completely copied between two tables

Source: Internet
Author: User
Tags mysql tutorial

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)

Related Article

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.