Learning MySQL

Creating Tables with Queries

You can create a table or easily create a copy of a table using a query. This is useful when you want to build a new database using existing data—for example, you might want to copy across a list of countries—or when you want to reorganize data for some reason. Data reorganization is common for producing reports, merging data from two or more tables, and redesigning on the fly. This short section shows you how it’s done.

From MySQL 4.1 onward, you can easily duplicate the structure of a table using a variant of the CREATE TABLE syntax:

mysql> CREATE TABLE artist_2 LIKE artist;
Query OK, 0 rows affected (0.24 sec)

mysql> DESCRIBE artist_2;
+-------------+-------------+------+-----+---------+-------+
| Field       | Type        | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+-------+
| artist_id   | smallint(5) |      | PRI | 0       |       |
| artist_name | char(128)   | YES  |     | NULL    |       |
+-------------+-------------+------+-----+---------+-------+
2 rows in set (0.09 sec)

mysql> SELECT * FROM artist_2;
Empty set (0.30 sec)

The LIKE syntax allows you to create a new table with exactly the same structure as another, including keys. You can see that it doesn’t copy the data across. You can also use the IF NOT EXISTS and TEMPORARY features with this syntax.

If you want to create a table and copy some data, you can do that with a combination of the CREATE TABLE and SELECT statements. Let’s remove the artist_2 table and re-create it using this new approach: ...