Learning MySQL

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 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 ...