Creating Tables with Queries | 285

The
LIKE
syntax allows you to create anew 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 recreate it using this new approach:
mysql> DROP TABLE artist_2;
Query OK, 0 rows affected (0.08 sec)
mysql> CREATE TABLE artist 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 inset 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, 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 You can see that the syntax is a little different from the previous example. In this example, 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) |

Download 4.24 Mb.

Share with your friends: