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.
The simplest use of
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 share your
music database with someone else
and they don’t want your
data. You do this with:
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.
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
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
SELECT * FROM played;Empty set (0.00 sec)
Of course, you can also continue to explore its structure using ...