Mega Code Archive
Creating Copies of a Table
<![CDATA[
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>
]]>