Learning Mysql

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, 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 (its 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 |

Download 4.24 Mb.

Share with your friends: