Databases Reference
In-Depth Information
A new row is created—MySQL reports that one row has been affected—and the value
7 is inserted as the
artist_id
and
Barry Adamson
as the
artist_name
. You can check with
a query:
mysql>
SELECT * FROM artist WHERE artist_id = 7;
+-----------+---------------+
| artist_id | artist_name |
+-----------+---------------+
| 7 | Barry Adamson |
+-----------+---------------+
1 row in set (0.01 sec)
You might be tempted to try out something like this:
mysql>
INSERT INTO artist
VALUES((SELECT 1+MAX(artist_id) FROM artist), "Barry Adamson");
However, this won't work because you can't modify a table while you're reading from
it. The query would work if you wanted to
INSERT INTO
a different table (here, a table
other than
artist
).
To continue our example, and illustrate the bulk-loading approach, let's now insert
Barry Adamson's album
The Taming of the Shrewd
and its tracks. First, check the
structure of the
album
table:
mysql>
SHOW COLUMNS FROM album;
+------------+-----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+-----------+------+-----+---------+-------+
| artist_id | int(5) | | PRI | 0 | |
| album_id | int(4) | | PRI | 0 | |
| album_name | char(128) | YES | | NULL | |
+------------+-----------+------+-----+---------+-------+
3 rows in set (0.00 sec)
Second, insert the album using the approach we used previously:
mysql>
INSERT INTO album VALUES (7, 1, "The Taming of the Shrewd");
Query OK, 1 row affected (0.00 sec)
The first value is the
artist_id
, the value of which we know from creating the artist,
and the second value is the
album_id
, which must be 1 because this is the first album
we've added for Barry Adamson.
Third, check the
track
table structure:
mysql>
SHOW COLUMNS FROM track;
+------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+-------+
| track_id | int(3) | | PRI | 0 | |
| track_name | char(128) | YES | | NULL | |
| artist_id | int(5) | | PRI | 0 | |
| album_id | int(4) | | PRI | 0 | |
| time | decimal(5,2) | YES | | NULL | |
