Databases Reference
In-Depth Information
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:
mysql>
DROP TABLE artist_2;
Query OK, 0 rows affected (0.08 sec)
mysql>
CREATE TABLE artist_2 SELECT * from artist;
Query OK, 7 rows affected (0.02 sec)
Records: 7 Duplicates: 0 Warnings: 0
mysql>
SELECT * FROM artist_2;
+-----------+-----------------------------+
| artist_id | artist_name |
+-----------+-----------------------------+
| 1 | New Order |
| 2 | Nick Cave and The Bad Seeds |
| 3 | Miles Dewey Davis |
| 4 | The Rolling Stones |
| 5 | The Stone Roses |
| 6 | Kylie Minogue |
| 10 | Jane's Addiction |
+-----------+-----------------------------+
7 rows in set (0.01 sec)
An identical table
artist_2
is created, and all of the data is copied across by the
SELECT
statement.
This technique is powerful. You can create new tables with new structures and use
powerful queries to populate them with data. For example, here's a
report
table that's
created to contain the names of artists and albums in our database:
mysql>
CREATE TABLE report (artist_name CHAR(128), album_name CHAR(128))
-> SELECT artist_name, album_name FROM artist INNER JOIN album
-> USING (artist_id);
Query OK, 13 rows affected (0.45 sec)
Records: 13 Duplicates: 0 Warnings: 0
You can see that the syntax is a little different from the previous example. In this ex-
ample, the new table name,
report
, is followed by a list of column names and types in
parentheses; this is necessary because we're not duplicating the structure of an existing
table. Then, the
SELECT
statement follows, with its output matching the new columns
in the new table. You can check the contents of the new table:
mysql>
SELECT * FROM report;
+-----------------------------+------------------------------------------+
| artist_name | album_name |
+-----------------------------+------------------------------------------+
| New Order | Retro - John McCready FAN |
| New Order | Substance (Disc 2) |
