The DELETE Statement | 169

+-----------+
1 row inset sec)
Next, remove the row from the artist table:
mysql> DELETE FROM artist WHERE artist_id = 3;
Query OK, 1 row affected (0.00 sec)
Then, do the same thing for the album, track, and played tables:
mysql> DELETE FROM album WHERE artist_id = 3;
Query OK, 2 rows affected (0.01 sec)
mysql> DELETE FROM track WHERE artist_id = 3;
Query OK, 13 rows affected (0.01 sec)
mysql> DELETE FROM played WHERE artist_id = 3;
Query OK, 3 rows affected (0.00 sec)
Since all four tables can be joined using the artist_id column, you can accomplish this whole deletion process in a single DELETE statement we show you how in Chapter You can use the ORDER BY
and
LIMIT
clauses with DELETE. You usually do this when you want to limit the number of rows deleted, either so that the statement doesn’t run for too long or because you want to keep a table to a specific size. Suppose your played table contains 10,528 rows, but you want to have at most 10,000 rows. In this situation,
it may make sense to remove the 528 oldest rows, and you can do this with the following statement:
mysql> DELETE FROM played ORDER BY played LIMIT 528;
Query OK, 528 rows affected (0.23 sec)
The query sorts the rows by ascending play date and then deletes at most 528 rows,
starting with the oldest. Typically, when you’re deleting, you use
LIMIT
and ORDER BY
together; it usually doesn’t make sense to use them separately. Note that sorting large numbers of entries on afield that doesn’t have an index can be quite slow. We discuss indexes in detail in Keys and Indexes in Chapter 6.
Removing All Rows with TRUNCATE
If you want to remove all rows in a table, there’s a faster method than removing them with DELETE. By using the
TRUNCATE
TABLE
statement, MySQL takes the shortcut of
dropping the table—that is, removing the table structures and then recreating them.
When there are many rows in a table, this is much faster. If you want to remove the data in the played table, you can write this:
mysql> TRUNCATE TABLE played;
Query OK, 0 rows affected (0.00 sec)
Notice that the number of rows affected is shown as zero to quickly delete all the data in the table, MySQL doesn’t count the number of rows that are deleted, so the number

Download 4.24 Mb.

Share with your friends: