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: ...
Get Learning MySQL now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.