Basic SQL - Learning MySQL - page 168

| 0 | 0 | 0 | 2006-08-09 12:20:40 |
+-----------+----------+----------+---------------------+
1 row in set (0.00 sec)
The process worked, but the row doesn't make any sense. We'll discuss default values
further in Chapter 6.
You can set defaults and still use the original INSERT syntax with MySQL 4.0.3 or later
by using the DEFAULT keyword. Here's an example that adds a played row:
mysql> INSERT INTO played VALUES (7, 1, 2, DEFAULT);
Query OK, 1 row affected (0.00 sec)
The keyword DEFAULT tells MySQL to use the default value for that column, and so the
current date and time are inserted in our example. The advantages of this approach are
that you can use the bulk-insert feature with default values, and you can never acci-
dentally omit a column.
There's another alternative INSERT syntax. In this approach, you list the column name
and value together, giving the advantage that you don't have to mentally map the list
of values to the earlier list of columns. Here's an example that adds a new row to the
played table:
mysql> INSERT INTO played
-> SET artist_id = 7, album_id = 1, track_id = 1;
Query OK, 1 row affected (0.00 sec)
The syntax requires you list a table name, the keyword SET , and then column-equals-
value pairs, separated by commas. Columns that aren't supplied are set to their default
values. The disadvantages are again that you can accidentally omit values for columns,
and that you need to remember and type in column names. A significant additional
disadvantage is that you can't use this method for bulk insertion.
You can also insert using values returned from a query. We discuss this in Chapter 8.
The DELETE Statement
The DELETE statement is used to remove one or more rows from a database. We explain
single-table deletes here, and discuss multi-table deletes—which remove data from two
or more tables through one statement—in Chapter 8.
If you want to try out the steps in this section on your MySQL server, you'll need to
reload your music database afterwards so that you can follow the examples in later
sections. To do this, follow the steps you used in “Loading the Sample Databases” in
Chapter 3 to load it in the first place.
DELETE Basics
The simplest use of DELETE is to remove all rows in a table. Suppose you want to empty
your played table, perhaps because it's taking too much space or because you want to