Learning MySQL

Inserting Data Using Queries

Much of the time, you’ll create tables using data from another source. The examples you’ve seen so far in Chapter 5 therefore illustrate only part of the problem: they show you how to insert data that’s already in the form you want—that is, formatted as an SQL INSERT statement. The other ways to insert data include using SQL SELECT statements on other tables or databases, and reading in files from other sources. This section shows you how to tackle the former method of inserting data; you’ll learn how to insert data from a file of comma-separated values in the next section, Loading Data from Comma-Delimited Files.”

Suppose you’ve decided to create a new table in the music database. It’s going to store a shuffle list, tracks that are randomly selected from your music collection, put into a list, and played to you in that order. It’s a way of tasting part of the collection, rediscovering some old favorites and learning about hidden treasures in those albums you haven’t explored. We’ve decided to structure the table as follows:

mysql> CREATE TABLE shuffle (
    -> artist_id SMALLINT(5) NOT NULL DEFAULT 0,
    -> album_id SMALLINT(4) NOT NULL DEFAULT 0,
    -> track_id SMALLINT(3) NOT NULL DEFAULT 0,
    -> sequence_id SMALLINT(3) AUTO_INCREMENT NOT NULL,
    -> PRIMARY KEY (sequence_id));
Query OK, 0 rows affected (0.01 sec)

You can download these instructions from the the file shuffle.sql on the book’s web site. This table stores the details of the track, allowing you ...