Creating New Tables with SELECT Results

      
mysql>
mysql>
mysql> CREATE TABLE publishers (
    ->   publID int(11) NOT NULL auto_increment,
    ->   publName varchar(60) collate latin1_german1_ci NOT NULL default '',
    ->   ts timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
    ->   PRIMARY KEY  (publID),
    ->   KEY publName (publName)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_german1_ci;
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql>
mysql> INSERT INTO publishers VALUES (1,'A','2004-12-02 18:36:58'),
    ->                                 (2,'Apress','2004-12-02 18:36:58'),
    ->                                 (3,'New Riders','2004-12-02 18:36:58'),
    ->                                 (4,'O\'Reilly & Associates','2004-12-02 18:36:58'),
    ->                                 (5,'Hanser','2004-12-02 18:36:58');
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql>
mysql> CREATE TABLE computerbooks SELECT * FROM publishers WHERE publID=1;
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql>
mysql> select * from computerbooks;
+--------+----------+---------------------+
| publID | publName | ts                  |
+--------+----------+---------------------+
|      1 | A        | 2004-12-02 18:36:58 |
+--------+----------+---------------------+
1 row in set (0.00 sec)

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

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

mysql>
mysql>

Related examples in the same category

1.	Copy Table Demo
2.	Copy Table with Condition
3.	Copying a Table
4.	Use NULL in where clause
5.	Copy table with conditions
6.	Only copy records
7.	Using the INSERT Statement to Copy Data
8.	Using the REPLACE Statement to Copy Data
9.	Copy a table
10.	Only copy certain columns
11.	Copy one row of data
12.	Copy data to temporary table
13.	Copying Only Selected Data from a Table
14.	Copy table with calculation
15.	Copying Data into a New Table
16.	MySQL truncates the data during copying

Creating New Tables with SELECT Results : Copy Table « Table Index « SQL / MySQL

Related examples in the same category