Databases Reference
In-Depth Information
| New Order | RETRO - MIRANDA SAWYER POP |
| New Order | Retro - New Order / Bobby Gillespie LIVE |
| New Order | Power, Corruption & Lies |
| New Order | Substance 1987 (Disc 1) |
| New Order | Brotherhood |
| Nick Cave and The Bad Seeds | Let Love In |
| Miles Dewey Davis | LIVE AROUND THE WORLD |
| Miles Dewey Davis | In A Silent Way |
| The Rolling Stones | Exile On Main Street |
| The Stone Roses | Second Coming |
| Kylie Minogue | Light Years |
+-----------------------------+------------------------------------------+
13 rows in set (0.00 sec)
So, in this example, the
artist_name
and
album_name
values from the
SELECT
statement
are used to populate the new
artist_name
and
album_name
columns in the
report
table.
Creating tables with a query has a major caveat that you need to be careful about. It
doesn't copy the indexes (or foreign keys, if you use them); this is a feature, since it
gives you a lot of flexibility, but it can be a catch if you forget. Have a look at our
artist_2
example:
mysql>
DESCRIBE artist_2;
+-------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+-------+
| artist_id | smallint(5) | | | 0 | |
| artist_name | char(128) | YES | | NULL | |
+-------------+-------------+------+-----+---------+-------+
2 rows in set (0.31 sec)
mysql>
SHOW CREATE TABLE artist_2;
+----------+---------------------------------------------------+
| Table | Create Table |
+----------+---------------------------------------------------+
| artist_2 | CREATE TABLE `artist_2` (
`artist_id` smallint(5) NOT NULL default '0',
`artist_name` char(128) default NULL)
ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+----------+---------------------------------------------------+
1 row in set (0.33 sec)
You can see that there's no primary key; if there had been other keys, they'd be missing
too.
To copy indexes across to the new table, there are at least three things you can do. The
first is to use the
LIKE
statement to create the empty table with the indexes, as described
earlier and then copy the data across using an
INSERT
with a
SELECT
statement as de-
scribed earlier in this chapter in “Inserting Data Using Queries.”
The second thing you can do is to use
CREATE TABLE
with a
SELECT
statement, and then
add indexes using
ALTER
TABLE
as described in Chapter 6.
