Learning Mysql

| 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 inset 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) | YES | | NULL | |
+-------------+-------------+------+-----+---------+-------+
2 rows inset sec)
mysql> SHOW CREATE TABLE artist_2;
+----------+---------------------------------------------------+
| Table | Create Table |
+----------+---------------------------------------------------+
| artist | CREATE TABLE artist (
`artist_id` smallint(5) NOT NULL default '0',
`artist_name` char) default NULL)
ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+----------+---------------------------------------------------+
1 row inset 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 described 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.

Download 4.24 Mb.

Share with your friends: