Basic SQL - Learning MySQL - page 169

share your music database with someone else and they don't want your played data.
You do this with:
mysql> DELETE FROM played;
Query OK, 19 rows affected (0.07 sec)
This removes all rows, including those we just added in “The INSERT Statement”; you
can see that 19 rows have been affected.
The DELETE syntax doesn't include column names, since it's used to remove whole rows
and not just values from a row. To reset or modify a value in a row, you use the
UPDATE statement, described later in this chapter in “The UPDATE Statement.” The
DELETE statement doesn't remove the table itself. For example, having deleted all rows
in the played table, you can still query the table:
mysql> SELECT * FROM played;
Empty set (0.00 sec)
Of course, you can also continue to explore its structure using DESCRIBE or SHOW CREATE
TABLE , and insert new rows using INSERT . To remove a table, you use the DROP statement
described in Chapter 6.
Using WHERE, ORDER BY, and LIMIT
If you've deleted rows in the previous section, reload your music database now. You
need the rows in the played table restored for the examples in this section.
To remove one or more rows, but not all rows in a table, you use a WHERE clause. This
works in the same way as it does for SELECT . For example, suppose you want to remove
all rows from the played table with played dates and times earlier than August 15, 2006.
You do this with:
mysql> DELETE FROM played WHERE played < "2006-08-15";
Query OK, 8 rows affected (0.00 sec)
The result is that the eight played rows that match the criteria are removed. Note that
the date is enclosed in quotes and that the date format is year , month , day , separated
by hyphens. MySQL supports several different ways of specifying times and dates but
saves dates in this internationally friendly, easy-to-sort format (it's actually an ISO
standard). MySQL can also reasonably interpret two-digit years, but we recommend
against using them; remember all the work required to avoid the Y2K problem?
Suppose you want to remove an artist, his albums, and his album tracks. For example,
let's remove everything by Miles Davis. Begin by finding out the artist_id from the
artist table, which we'll use to remove data from all four tables:
mysql> SELECT artist_id FROM artist WHERE artist_name = "Miles Davis";
+-----------+
| artist_id |
+-----------+
| 3 |