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:

##1Alice20Female2Bob22Male3Cindy18Female4David21Male

id	name	age	gender

To delete all student records under the age of 20 in this table, you can use the following command:

DELETE FROM students WHERE age < 20;

After performing the delete operation, the contents of the table will become:

idnameagegender##124Use the TRUNCATE statement to quickly delete all data in the table

Alice	20	Female
Bob	22	Male
David	21	Male

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:

id ##12020-01- 01Alice10022020-01-02Bob20032020-01-03Cindy15042020-01-04David30052020- 01-05Elizabeth250To delete the three orders with the highest amounts in the "orders" table, you can use the following command :

DELETE FROM orders ORDER BY amount DESC LIMIT 3;

order_date	customer	amount

After execution, the table will become:

idorder_datecustomeramount##12020-01-012020-01-03Use the CASCADE option to delete subtable recordsIn MySQL, foreign keys are often used to establish connections between relational databases. Foreign keys allow us to implement joint queries and deletions between tables by establishing connections between tables. If we want to delete certain records in one table and also need to delete related records in other tables associated with it, we can use the CASCADE option.

Alice	100		3
Cindy	150

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!