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






  1. SQL / MySQL
  2. Table Index
  3. 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, "j@hotmail.com");
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, "g@yahoo.com");
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, "a@gmail.com");
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, "m@mail.com");
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, "f@net.com");
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, "j@hotmail.com");
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, "e@yahoo.com");
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, "a@yahoo.com");
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, "h@gmail.com");
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, "ps@gmail.com");
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, "a@hotmail.com");
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, "kim@coolmail.com");
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, "roger@mail.com");
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, "danny@hotmail.com");
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, "m@gmail.com");
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, "monica@bigmail.com");
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, "hal@gmail.com");
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, "joseph@hotmail.com");
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, "shahida@hotmail.com");
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, "peter@yahoo.com");
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 | j@hotmail.com       |

|  2 | Jan       | Pillai   | Senior Programmer          |   32 |             4 | 110000 | 20000 | g@yahoo.com         |

|  3 | Ane       | Pandit   | Web Designer               |   24 |             3 |  90000 | 15000 | a@gmail.com         |

|  4 | Mary      | Anchor   | Web Designer               |   27 |             2 |  85000 | 15000 | m@mail.com          |

|  5 | Fred      | King     | Programmer                 |   32 |             3 |  75000 | 15000 | f@net.com           |

|  6 | John      | Mac      | Programmer                 |   32 |             4 |  80000 | 16000 | j@hotmail.com       |

|  7 | Arthur    | Sam      | Programmer                 |   28 |             2 |  75000 | 14000 | e@yahoo.com         |

|  8 | Alok      | Nanda    | Programmer                 |   32 |             3 |  70000 | 10000 | a@yahoo.com         |

|  9 | Susan     | Ra       | Multimedia Programmer      |   32 |             4 |  90000 | 15000 | h@gmail.com         |

| 10 | Paul      | Simon    | Multimedia Programmer      |   23 |             1 |  85000 | 12000 | ps@gmail.com        |

| 11 | Edward    | Parhar   | Multimedia Programmer      |   30 |             2 |  75000 | 15000 | a@hotmail.com       |

| 12 | Kim       | Hunter   | Senior Web Designer        |   32 |             4 | 110000 | 20000 | kim@coolmail.com    |

| 13 | Roger     | Lewis    | System Administrator       |   32 |             3 | 100000 | 13000 | roger@mail.com      |

| 14 | Danny     | Gibson   | System Administrator       |   31 |             2 |  90000 | 12000 | danny@hotmail.com   |

| 15 | Mike      | Harper   | Senior Marketing Executive |   36 |             1 | 120000 | 28000 | m@gmail.com         |

| 16 | Mary      | Sunday   | Marketing Executive        |   31 |             5 |  90000 | 25000 | monica@bigmail.com  |

| 17 | Jack      | Sim      | Marketing Executive        |   27 |             1 |  70000 | 18000 | hal@gmail.com       |

| 18 | Joe       | Irvine   | Marketing Executive        |   27 |             1 |  72000 | 18000 | joseph@hotmail.com  |

| 19 | Henry     | Ali      | Customer Service Manager   |   32 |             3 |  70000 |  9000 | shahida@hotmail.com |

| 20 | Peter     | Champion | Finance Manager            |   32 |             2 | 120000 | 25000 | peter@yahoo.com     |

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

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 | j@hotmail.com       |

|  2 | Jan       | Pillai   | Senior Programmer          |   32 |             4 | 110000 | 20000 | g@yahoo.com         |

|  3 | Ane       | Pandit   | Web Designer               |   24 |             3 |  90000 | 15000 | a@gmail.com         |

|  4 | Mary      | Anchor   | Web Designer               |   27 |             2 |  85000 | 15000 | m@mail.com          |

|  5 | Fred      | King     | Programmer                 |   32 |             3 |  75000 | 15000 | f@net.com           |

|  6 | John      | Mac      | Programmer                 |   32 |             4 |  80000 | 16000 | j@hotmail.com       |

|  7 | Arthur    | Sam      | Programmer                 |   28 |             2 |  75000 | 14000 | e@yahoo.com         |

|  8 | Alok      | Nanda    | Programmer                 |   32 |             3 |  70000 | 10000 | a@yahoo.com         |

|  9 | Susan     | Ra       | Multimedia Programmer      |   32 |             4 |  90000 | 15000 | h@gmail.com         |

| 10 | Paul      | Simon    | Multimedia Programmer      |   23 |             1 |  85000 | 12000 | ps@gmail.com        |

| 11 | Edward    | Parhar   | Multimedia Programmer      |   30 |             2 |  75000 | 15000 | a@hotmail.com       |

| 12 | Kim       | Hunter   | Senior Web Designer        |   32 |             4 | 110000 | 20000 | kim@coolmail.com    |

| 13 | Roger     | Lewis    | System Administrator       |   32 |             3 | 100000 | 13000 | roger@mail.com      |

| 14 | Danny     | Gibson   | System Administrator       |   31 |             2 |  90000 | 12000 | danny@hotmail.com   |

| 15 | Mike      | Harper   | Senior Marketing Executive |   36 |             1 | 120000 | 28000 | m@gmail.com         |

| 16 | Mary      | Sunday   | Marketing Executive        |   31 |             5 |  90000 | 25000 | monica@bigmail.com  |

| 17 | Jack      | Sim      | Marketing Executive        |   27 |             1 |  70000 | 18000 | hal@gmail.com       |

| 18 | Joe       | Irvine   | Marketing Executive        |   27 |             1 |  72000 | 18000 | joseph@hotmail.com  |

| 19 | Henry     | Ali      | Customer Service Manager   |   32 |             3 |  70000 |  9000 | shahida@hotmail.com |

| 20 | Peter     | Champion | Finance Manager            |   32 |             2 | 120000 | 25000 | peter@yahoo.com     |

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

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: