Basic SQL - Learning MySQL - page 170

+-----------+
1 row in set (0.00 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 8.
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 a field 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 re-creating 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