转载自：

 /  / MySQL DELETE

MySQL DELETE

 

Summary: in this tutorial, you will learn how to use the MySQL DELETE statement to delete data from a single table.

Introduction to MySQL DELETE statement

To delete data from a table, you use the MySQL DELETE statement. The following illustrates the syntax of the DELETE statement:

 

1 2	DELETE FROM table_name WHERE condition;

In this statement:

• First, specify the table from which you delete data.
• Second, use a condition to specify which rows to delete in the WHERE clause. If the row matches the condition, it will be deleted.

Notice that the WHERE clause is optional. If you omit the WHERE clause, the DELETE statement will delete all rows in the table.

Besides deleting data from a table, the DELETE statement returns the number of rows deleted.

To delete data from multiple tables using a single DELETE statement, you use the  statement which we will cover in the .

To delete all rows in a table without the need of knowing how many rows deleted, you should use the  statement to get a better performance.

For a table that has a  constraint, when you delete rows from the parent table, the rows in the child table will be deleted automatically by using the  option.

MySQL DELETE examples

We will use the employees table in the  for the demonstration.

Note that once you delete data, it is gone. Therefore, you should   before performing the 

DELETE statements in the next section.

Suppose you want to delete employees whose officeNumber is 4, you use the DELETE statement with the  clause as the following query:

 

1 2 3

DELETE FROM employees WHERE     officeCode = 4;

To delete all rows from the employees table, you use the DELETE statement without the WHERE clause as follows:

 

1	DELETE FROM employees;

All rows in the employees table deleted.

MySQL DELETE and LIMIT clause

If you want to limit the number of rows to be deleted, you use the  clause as follows:

 

1 2	DELETE FROM table LIMIT row_count;

Note that the order of rows in a table is unspecified, therefore, when you use the LIMIT clause, you should always use the  clause.

 

1 2 3

DELETE FROM table_name ORDER BY c1, c2, ... LIMIT row_count;

Consider the following customers table in the :

For example, the following statement sorts customers by customer’s names alphabetically and deletes the first 10 customers:

 

1 2 3

DELETE FROM customers ORDER BY customerName LIMIT 10;

Similarly, the following DELETE statement selects customers in France, sorts them by credit limit in ascending order, and deletes the first 5 customers:

 

1 2 3 4

DELETE FROM customers WHERE country = 'France' ORDER BY creditLimit LIMIT 5;

In this tutorial, you have learned how to use the MySQL DELETE statement to delete data from a table.

Related Tutorials