| Another step you can easily take on existing data is to entirely remove it from the database. To do this, you use the DELETE command. DELETE FROM tablename WHERE  Note that once you have deleted a record, there is no way of retrieving it, so you may want to back up your database before performing any deletes. Also, you should get in the habit of using WHERE when deleting data, or else you will delete all of the data in a table. The query DELETE FROM tablename will empty out a table, while still retaining its structure. Similarly, the command trUNCATE TABLE tablename will delete an entire table (both the records and the structure) and then re-create the structure. The end result is the same, but this method is faster and safer. To delete data 1. | Determine which record will be deleted (Figure 4.29).
SELECT user_id FROM users WHERE first_name='Peter AND last_name='Tork;
Figure 4.29. The user_id will be used to refer to this record in my DELETE query. 
Just as in the UPDATE example, I first need to determine which primary key to use for the delete.
| 2. | Preview what will happen when the delete is made (Figure 4.30).
SELECT * FROM users WHERE  user_id = 7;
Figure 4.30. To preview the effect of my DELETE query, I run a syntactically similar SELECT query. 
A really good trick for safeguarding against errant deletions is to first run the query using SELECT * instead of DELETE. The results of this query will represent which row(s) will be affected by the deletion.
| 3. | Delete the record (Figure 4.31).
DELETE FROM users WHERE user_id = 7;
Figure 4.31. Deleting one record from the table. 
As with the update, MySQL will report on the successful execution of the query and how many rows were affected. At this point, there is no way of reinstating the deleted records unless you backed up the database beforehand (or are using transactions, see Chapter 11, "Extended Topics").
| 4. | Confirm that the change was made (Figure 4.32).
SELECT user_id, first_name, last_name FROM users ORDER BY user_id ASC;
Figure 4.32. The record whose user_id was 7 is no longer part of this table. 
|
 Tips
To delete all of the data in a table, as well as the table itself, use DROP TABLE: DROP TABLE tablename
To delete an entire database, including every table therein and all of its data, use DROP DATABASE databasename
Beginning with MySQL version 4.0, you can run a DELETE query across multiple tables at the same time. For extra security when deleting records, you can add a LIMIT clause (assuming you want to delete only a set number of records): DELETE FROM tablename WHERE id=4 LIMIT 1
|
