Creating Copies of a Table : Create Table « Table Index « SQL / MySQL

Home
SQL / MySQL
1.Aggregate Functions
2.Backup Load
3.Command MySQL
4.Cursor
5.Data Type
6.Database
7.Date Time
8.Engine
9.Event
10.Flow Control
11.FullText Search
12.Function
13.Geometric
14.I18N
15.Insert Delete Update
16.Join
17.Key
18.Math
19.Procedure Function
20.Regular Expression
21.Select Clause
22.String
23.Table Index
24.Transaction
25.Trigger
26.User Permission
27.View
28.Where Clause
29.XML
SQL / MySQL » Table Index » Create Table 
Creating Copies of a Table
        
mysql>
mysql>
mysql> CREATE TABLE employee (
    ->     id int unsigned not null auto_increment primary key,
    ->     firstname varchar(20),
    ->     lastname varchar(20),
    ->     title varchar(30),
    ->     age int,
    ->     yearofservice int,
    ->     salary int,
    ->     perks int,
    ->     email varchar(60)
    -> );
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("John", "Chen
", "Senior Programmer", 31, 3, 120000, 25000, "[email protected]");
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Jan", "Pilla
i", "Senior Programmer", 32, 4, 110000, 20000, "[email protected]");
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Ane", "Pandi
t", "Web Designer", 24, 3, 90000, 15000, "[email protected]");
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Mary", "Anch
or", "Web Designer", 27, 2, 85000, 15000, "[email protected]");
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Fred", "King
", "Programmer", 32, 3, 75000, 15000, "[email protected]");
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("John", "Mac"
, "Programmer", 32, 4, 80000, 16000, "[email protected]");
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Arthur", "Sa
m", "Programmer", 28, 2, 75000, 14000, "[email protected]");
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Alok", "Nand
a", "Programmer", 32, 3, 70000, 10000, "[email protected]");
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Susan", "Ra"
, "Multimedia Programmer", 32, 4, 90000, 15000, "[email protected]");
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Paul", "Simo
n", "Multimedia Programmer", 23, 1, 85000, 12000, "[email protected]");
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Edward", "Pa
rhar", "Multimedia Programmer", 30, 2, 75000, 15000, "[email protected]");
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Kim", "Hunte
r", "Senior Web Designer", 32, 4, 110000, 20000, "[email protected]");
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Roger", "Lew
is", "System Administrator", 32, 3, 100000, 13000, "[email protected]");
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Danny", "Gib
son", "System Administrator", 31, 2, 90000, 12000, "[email protected]");
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Mike", "Harp
er", "Senior Marketing Executive", 36, 1, 120000, 28000, "[email protected]");
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Mary", "Sund
ay", "Marketing Executive", 31, 5, 90000, 25000, "[email protected]");
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Jack", "Sim"
, "Marketing Executive", 27, 1, 70000, 18000, "[email protected]");
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Joe", "Irvin
e", "Marketing Executive", 27, 1, 72000, 18000, "[email protected]");
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Henry", "Ali
", "Customer Service Manager", 32, 3, 70000, 9000, "[email protected]");
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Peter", "Cha
mpion", "Finance Manager", 32, 2, 120000, 25000, "[email protected]");
Query OK, 1 row affected (0.00 sec)

mysql>
mysql> select * from employee;
+----+-----------+----------+----------------------------+------+---------------+--------+-------+---------------------+

| id | firstname | lastname | title                      | age  | yearofservice | salary | perks | email               |

+----+-----------+----------+----------------------------+------+---------------+--------+-------+---------------------+

|  1 | John      | Chen     | Senior Programmer          |   31 |             3 | 120000 | 25000 | [email protected]       |

|  2 | Jan       | Pillai   | Senior Programmer          |   32 |             4 | 110000 | 20000 | [email protected]         |

|  3 | Ane       | Pandit   | Web Designer               |   24 |             3 |  90000 | 15000 | [email protected]         |

|  4 | Mary      | Anchor   | Web Designer               |   27 |             2 |  85000 | 15000 | [email protected]          |

|  5 | Fred      | King     | Programmer                 |   32 |             3 |  75000 | 15000 | [email protected]           |

|  6 | John      | Mac      | Programmer                 |   32 |             4 |  80000 | 16000 | [email protected]       |

|  7 | Arthur    | Sam      | Programmer                 |   28 |             2 |  75000 | 14000 | [email protected]         |

|  8 | Alok      | Nanda    | Programmer                 |   32 |             3 |  70000 | 10000 | [email protected]         |

|  9 | Susan     | Ra       | Multimedia Programmer      |   32 |             4 |  90000 | 15000 | [email protected]         |

| 10 | Paul      | Simon    | Multimedia Programmer      |   23 |             1 |  85000 | 12000 | [email protected]        |

| 11 | Edward    | Parhar   | Multimedia Programmer      |   30 |             2 |  75000 | 15000 | [email protected]       |

| 12 | Kim       | Hunter   | Senior Web Designer        |   32 |             4 | 110000 | 20000 | [email protected]    |

| 13 | Roger     | Lewis    | System Administrator       |   32 |             3 | 100000 | 13000 | [email protected]      |

| 14 | Danny     | Gibson   | System Administrator       |   31 |             2 |  90000 | 12000 | [email protected]   |

| 15 | Mike      | Harper   | Senior Marketing Executive |   36 |             1 | 120000 | 28000 | [email protected]         |

| 16 | Mary      | Sunday   | Marketing Executive        |   31 |             5 |  90000 | 25000 | [email protected]  |

| 17 | Jack      | Sim      | Marketing Executive        |   27 |             1 |  70000 | 18000 | [email protected]       |

| 18 | Joe       | Irvine   | Marketing Executive        |   27 |             1 |  72000 | 18000 | [email protected]  |

| 19 | Henry     | Ali      | Customer Service Manager   |   32 |             3 |  70000 |  9000 | [email protected] |

| 20 | Peter     | Champion | Finance Manager            |   32 |             2 | 120000 | 25000 | [email protected]     |

+----+-----------+----------+----------------------------+------+---------------+--------+-------+---------------------+

20 rows in set (0.00 sec)

mysql>
mysql>
mysql> CREATE TABLE newtable SELECT * FROM employee;
Query OK, 20 rows affected (0.01 sec)
Records: 20  Duplicates: 0  Warnings: 0

mysql>
mysql> select * from newtable;
+----+-----------+----------+----------------------------+------+---------------+--------+-------+---------------------+

| id | firstname | lastname | title                      | age  | yearofservice | salary | perks | email               |

+----+-----------+----------+----------------------------+------+---------------+--------+-------+---------------------+

|  1 | John      | Chen     | Senior Programmer          |   31 |             3 | 120000 | 25000 | [email protected]       |

|  2 | Jan       | Pillai   | Senior Programmer          |   32 |             4 | 110000 | 20000 | [email protected]         |

|  3 | Ane       | Pandit   | Web Designer               |   24 |             3 |  90000 | 15000 | [email protected]         |

|  4 | Mary      | Anchor   | Web Designer               |   27 |             2 |  85000 | 15000 | [email protected]          |

|  5 | Fred      | King     | Programmer                 |   32 |             3 |  75000 | 15000 | [email protected]           |

|  6 | John      | Mac      | Programmer                 |   32 |             4 |  80000 | 16000 | [email protected]       |

|  7 | Arthur    | Sam      | Programmer                 |   28 |             2 |  75000 | 14000 | [email protected]         |

|  8 | Alok      | Nanda    | Programmer                 |   32 |             3 |  70000 | 10000 | [email protected]         |

|  9 | Susan     | Ra       | Multimedia Programmer      |   32 |             4 |  90000 | 15000 | [email protected]         |

| 10 | Paul      | Simon    | Multimedia Programmer      |   23 |             1 |  85000 | 12000 | [email protected]        |

| 11 | Edward    | Parhar   | Multimedia Programmer      |   30 |             2 |  75000 | 15000 | [email protected]       |

| 12 | Kim       | Hunter   | Senior Web Designer        |   32 |             4 | 110000 | 20000 | [email protected]    |

| 13 | Roger     | Lewis    | System Administrator       |   32 |             3 | 100000 | 13000 | [email protected]      |

| 14 | Danny     | Gibson   | System Administrator       |   31 |             2 |  90000 | 12000 | [email protected]   |

| 15 | Mike      | Harper   | Senior Marketing Executive |   36 |             1 | 120000 | 28000 | [email protected]         |

| 16 | Mary      | Sunday   | Marketing Executive        |   31 |             5 |  90000 | 25000 | [email protected]  |

| 17 | Jack      | Sim      | Marketing Executive        |   27 |             1 |  70000 | 18000 | [email protected]       |

| 18 | Joe       | Irvine   | Marketing Executive        |   27 |             1 |  72000 | 18000 | [email protected]  |

| 19 | Henry     | Ali      | Customer Service Manager   |   32 |             3 |  70000 |  9000 | [email protected] |

| 20 | Peter     | Champion | Finance Manager            |   32 |             2 | 120000 | 25000 | [email protected]     |

+----+-----------+----------+----------------------------+------+---------------+--------+-------+---------------------+

20 rows in set (0.00 sec)

mysql>
mysql>
mysql> drop table employee;
Query OK, 0 rows affected (0.00 sec)

mysql> drop table newtable;
Query OK, 0 rows affected (0.00 sec)

mysql>

   
    
    
    
    
    
    
    
  
Related examples in the same category
1.Create Simple Tables
2.Create table: default value for column
3.Create table: small int and unsigned int
4.Create table: smallint, decimal and float
5.Create table: char, varchar
6.Create table: BLOB
7.Create table: char set and language
8.Create table: ENUM and set
9.Create table: YEAR and TIMESTAMP
10.Create table: null and not null
11.Create table: not null and default value
12.Create table: default int value
13.Create table: primary key
14.Create table: primary key 2
15.Create table: two columns for primary key
16.Create table: auto increment primary key
17.Create table: REFERENCES
18.Create table: foreign key
19.Create table: engine type INNODB
20.Set average row length and max, min rows
21.Syntax for Creating Tables (CREATE TABLE)
22.Create an empty table, use a WHERE clause that is always false
23.To produce a summary that includes all hours of the day, create a reference table that lists each hour
24.Creates table-level privileges.
25.Create a copy of the product table:
26.Create a table called cust_names using the table definitions and data from the product table.
27.CREATE TABLE IF NOT EXISTS
28.CREATE TABLE TEAMS_COPY1 LIKE TEAMS
29.Creating a New Table by Copying
30.Creating a Table
31.Add an IF NOT EXISTS clause to the statement:
java2s.com  |  | Contact Us | Privacy Policy
Copyright 2009 - 12 Demo Source and Support. All rights reserved.
All other trademarks are property of their respective owners.