Basic SQL Tutorial

SQL Join (Join)

Advanced SQL Tutorial

SQL Reference Manual

SQL Truncate Table (TRUNCATE TABLE)SQL Update Data (UPDATE)

SQL Delete Data (DELETE Statement)

In this tutorial, you will learn how to use SQL to delete records from a database table.

delete data from the table

like usingINSERTstatements insert records into a table, you can also use statements to delete records from a table DELETE.

Syntax

The DELETE statement is used to delete one or more rows from a table.

DELETE FROM table_name WHERE condition;

Warning:in the DELETE statementWHEREclause specifies which records should be deleted. However, it is optional, and if you omit or forget the WHERE clause, all records will be permanently deleted from the table.

Let's start with theCreate a tablein a chapterCreate'spersonssome records from the table.

Suppose ourPerson (persons)The table currently has the following records:

+----+--------------------+------------+-------------+
| id | name | birth_date | phone |
+----+--------------------+------------+-------------+
|　　1　| Peter Wilson |　1990-07-15　| 0711-020361　|
|　　2　| Carrie Simpson |　1995-05-01　| 0251-031259　|
|　　3　| Victoria Ashworth |　1996-10-17　| 0695-346721　|
|　　4　| George Bailey |　1993-03-05　| 0897-034214　|
|　　5　| Norman Bates |　1999-08-25　| 0522-556721　|
+----+--------------------+------------+-------------+

delete records based on the conditions

The following statement will delete records fromidgreater than3'sperson(personsrows will be deleted from the table.

Example

DELETE FROM persons WHERE id >　3;

After executing the query,Person (persons)The table will be as follows:

+----+--------------------+------------+-------------+
| id | name | birth_date | phone |
+----+--------------------+------------+-------------+
|　　1　| Peter Wilson |　1990-07-15　| 0711-020361　|
|　　2　| Carrie Simpson |　1995-05-01　| 0251-031259　|
|　　3　| Victoria Ashworth |　1996-10-17　| 0695-346721　|
+----+--------------------+------------+-------------+

Note:Please refer toSQL WHERE clausetutorials to learn how to form complex queries based on multiple conditions when deleting records from a table.

Delete all data

Similarly, as mentioned above, if you do not specify a clause in the WHERE statement, all rows in the table will be deleted. However, the target table itself will not be deleted, which means that the table structure, properties, and indexes will remain unchanged, but the data in the table will be cleared.

The following statement will deletePerson (persons)All records in the table:

Example

DELETE FROM persons;

Now, if you try to delete fromPerson (persons)in the tableSelect or Query RecordsIf so, you will get an empty result set.

SQL Truncate Table (TRUNCATE TABLE)SQL Update Data (UPDATE)