The final thing we'll look at today is the DELETE statement. Quite simply, DELETE removes rows of data from a table.
Here is its syntax:
DELETE FROM table_name [WHERE where_definition] [ORDER BY column_list] [LIMIT num_rows]
You should see immediately several similarities with the SELECT query (which is why we're looking at it now!).
For a start, there's a WHERE clause, which restricts the delete action to just those rows meeting the condition. This works exactly the same as in a SELECT statement.
Occasionally, you may also use a LIMIT clause, and if so, you may also use an ORDER BY clause. (ORDER BY has no effect without LIMIT.) You may choose a limit of 1 for safety (so that you can't delete everything by mistake), or you may find a reason for deleting num_rows rows of data.
Here's a simple example:
mysql> DELETE FROM child_products WHERE id=105;
Query OK, 1 row affected (0.01 sec)
In this example, the product with id 105 is deleted.
Here's another example:
mysql> DELETE FROM child_products WHERE name LIKE 'toy%';
Query OK, 1 row affected (0.00 sec)
Now all products are deleted if their name starts with the word toy.
Finally, to empty a table just do this:
mysql> DELETE FROM child_products;
Query OK, 3 rows affected (0.00 sec)
In this case, every remaining row is deleted from the table. Take care with DELETE!
18.219.71.21