Delete statement mysql

In MySQL, the delete statement is a command used to delete records from the database. It can help us clean up useless records in the database to manage data more effectively. MySQL's delete statement is very flexible and can use a variety of different syntaxes and options to complete different delete operations. In this article, we'll take a closer look at these options and how to use delete statements correctly.

Basic syntax

The basic syntax of MySQL's delete statement is as follows:

DELETE FROM table_name WHERE condition;

Among them:

• table_name Specify the table name from which data is to be deleted.
• condition is an optional parameter used to specify the records to be deleted. If this parameter is omitted, all records in the table will be deleted.

Example:

Suppose we have a table named "students" containing the following data:

DELETE FROM students WHERE age < 20;

In some cases, we may need Quickly clear all data in a table instead of deleting only part of it. For this purpose, MySQL provides a statement called TRUNCATE that can completely delete all data in the table in one operation. TRUNCATE is faster than the DELETE statement because it deletes data in the table directly without logging and therefore without the need for rollback.

The syntax of the TRUNCATE statement is as follows:

TRUNCATE table_name;

Example:

To quickly clear all the data in the "students" table mentioned earlier, you can use the following command:

TRUNCATE students;

After execution, the table will no longer contain any data.

Use the LIMIT statement to control the number of deleted rows

When deleting data, sometimes we only want to delete a certain number of rows in the table, not all of them. At this time, you can use the LIMIT statement to specify the number of rows to be deleted. The LIMIT statement is similar to the SELECT statement and can be added to the DELETE statement to control the number of rows deleted.

The syntax of the LIMIT statement is as follows:

DELETE FROM table_name WHERE condition LIMIT number;

Among them:

table_name
• and condition are the same as those introduced previously The same.
number
• Specifies the number of rows to delete.
Example:

Suppose we have a table called "orders" that contains the following data:

DELETE FROM orders ORDER BY amount DESC LIMIT 3;

The CASCADE option is a commonly used cascade delete operation in MySQL. It automatically finds other tables related to records in the specified table and deletes all related records in these tables. The CASCADE option can only be used in combination with foreign keys to specify the action that should be taken when performing a delete operation.

For the following table structure:

CREATE TABLE customers (
  id INT PRIMARY KEY,
  name VARCHAR(50)
);

CREATE TABLE orders (
  id INT PRIMARY KEY,
  customer_id INT,
  order_date DATE,
  amount DECIMAL(10, 2),
  FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE
);

To delete the record with ID 3 in the "customers" table and all the records in the related "orders" table, you can use the following command:

DELETE FROM customers WHERE id = 3;

The deletion operation will delete the customer record with ID 3 and automatically delete all related order records.

Summary

As mentioned above, you can easily delete records in the database using MySQL's delete statement. The basic syntax of delete statements includes DELETE, TRUNCATE and LIMIT statements. By using these options flexibly, data operations can be handled quickly and securely. Note: When using delete statements, carefully check the conditions and options to avoid accidentally deleting or losing important data.

The above is the detailed content of Delete statement mysql. For more information, please follow other related articles on the PHP Chinese website!