Basic SQL - Learning MySQL - page 166

Query OK, 0 rows affected (0.01 sec)
Records: 4 Duplicates: 4 Warnings: 0
We discuss causes of warnings—shown as the third entry on the final line—in Chap-
ter 6.
Alternative Syntaxes
There are several alternatives to the VALUES syntax we've shown you so far. This section
shows you these and explains the advantages and drawbacks of each. If you're happy
with the basic syntax we've described so far, and want to move on to a new topic, feel
free to skip ahead to “The DELETE Statement.”
There are three disadvantages of the VALUES syntax we've shown you. First, you need
to remember the order of the columns. Second, you need to provide a value for each
column. Last, it's closely tied to the underlying table structure: if you change the table's
structure, you need to change the INSERT statements, and the function of the INSERT
statement isn't obvious unless you have the table structure at hand. However, the three
advantages of the approach are that it works for both single and bulk inserts, you get
an error message if you forget to supply values for all columns, and you don't have to
type in column names. Fortunately, the disadvantages are easily avoided by varying the
syntax.
Suppose you know that the album table has three columns and you recall their names,
but you forget their order. You can insert using the following approach:
mysql> INSERT INTO album (artist_id, album_id, album_name)
-> VALUES (7, 2, "Oedipus Schmoedipus");
Query OK, 1 row affected (0.00 sec)
The column names are included in parentheses after the table name, and the values
stored in those columns are listed in parentheses after the VALUES keyword. So, in this
example, a new row is created and the value 7 is stored as the artist_id , 2 is stored as
the album_id , and Oedipus Schmoedipus is stored as the album_name . The advantages of
this syntax are that it's readable and flexible (addressing the third disadvantage we
described) and order-independent (addressing the first disadvantage). The disadvant-
age is that you need to know the column names and type them in.
This new syntax can also address the second disadvantage of the simpler approach—
that is, it can allow you to insert values for only some columns. To understand how
this might be useful, let's explore the played table:
mysql> SHOW COLUMNS FROM played;
+-----------+-----------+------+-----+-------------------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-----------+------+-----+-------------------+-------+
| artist_id | int(5) | | PRI | 0 | |
| album_id | int(4) | | PRI | 0 | |
| track_id | int(3) | | PRI | 0 | |
| played | timestamp | YES | PRI | CURRENT_TIMESTAMP | |