How to Delete From a MySQL Database

There are multiple ways to delete data from a database in MySQL. At times it may even be necessary to delete entire tables, since some tables are temporary--created to be the target of a SELECT INTO query-- and need to be deleted after you're finished using them. MySQL allows for you to delete individual rows, whole tables or even complete databases.

Instructions

Delete Individual Rows From a MySQL Database

1. Query the rows you want to delete. You need to use SELECT to query the rows you wish to delete before you actually delete them.

2. Use the SELECT command to avoid making a mistake in the WHERE clause. Using SELECT first verifies that you have the data you really want to delete. Otherwise, the DELETE command could be unrecoverable and you could lose data. This example deletes all customers with a balance of more than 0 from the customers database. Example:
SELECT * FROM customers WHERE balance > 0.0;


3. Delete the rows. This query is almost identical to the SELECT query. This will drop these rows from the table, so make sure you really want to do it! Example:
DELETE FROM customers WHERE balance > 0.0;

Delete Tables From a MySQL Database

1. Delete the table. Deleting a table is called "dropping" it and is a very simple command. In this first form, it'll give an error if the table doesn't exist. Example:
DROP TABLE customers;

2. Delete the table if it exists. It's easier to use this form instead of checking manually to see if the table exists and then dropping it, or trying to drop a table that doesn't exist and having to deal with the error. Example:
DROP TABLE IF EXISTS customers;

3. Drop a temporary table. There is a constrict of the DROP command to drop only the tables that are marked temporary. It's safer way to drop temporary tables. Example:
DROP TEMPORARY TABLE customers;

Delete MySQL Databases

1. Drop the database using one of the two forms of the DROP command. This form will drop the entire database and all tables in the database. Example:
DROP DATABASE customers_db;

2. Drop a database only if it exists. This command will suppress an error message if the database doesn't exist. Example:
DROP DATABASE IF EXISTS customers_db;

Tips & Warnings

• To delete the database, you need to be root or a user with equivalent permissions. Typical users won't have permission to create or drop databases.