How to use delete statement

DELETE statement is used to delete records from database tables. The basic syntax is "DELETE FROM table_name WHERE condition;".

#DELETE statement is used to delete records from a database table. The following is the basic usage of the DELETE statement and some common examples:

Basic syntax

sql

DELETE FROM table_name
WHERE condition;

Examples

Delete all records in the table:

sql

DELETE FROM table_name;

Note: This will delete all records in the table, but not the table itself.

2. Delete records based on conditions:

sql

DELETE FROM table_name WHERE condition;

For example, if you have a table named students and want to delete students whose age is 20:

sql

DELETE FROM students WHERE age = 20;

Delete multiple conditions:

You can use AND and OR to set multiple conditions:

sql

DELETE FROM table_name WHERE condition1 AND condition2;
DELETE FROM table_name WHERE condition1 OR condition2;

For example , if you want to delete all students whose age is 20 or whose last name is "Smith":

sql

DELETE FROM students WHERE age = 20 OR last_name = 'Smith';

Use subquery:

You can use subquery to based on other Data deletion records in the table:

sql

DELETE FROM table_name WHERE column_name IN (SELECT column_name FROM another_table WHERE condition);

For example, if you want to delete all students from the students table that are related to the dropped_courses table:

sql

DELETE FROM students WHERE student_id IN (SELECT student_id FROM dropped_courses);

Delete records related to foreign key constraints:

If you want to delete records related to foreign key constraints, you need to use cascading delete or set ON DELETE CASCADE:

sql

ALTER TABLE child_table DROP CONSTRAINT foreign_key_name CASCADE;  -- 删除外键约束并级联删除相关记录

Or:

sql

ALTER TABLE child_table ADD CONSTRAINT foreign_key_name FOREIGN KEY (column_name) REFERENCES parent_table(column_name) ON DELETE CASCADE;  -- 设置外键约束并级联删除相关记录

Note:

When using the DELETE statement, be careful to ensure that you set the correct conditions to avoid accidentally deleting important data. Before performing a delete operation, it's a good idea to back up your data or test it in a secure environment.

In some database systems, if a DELETE statement without a WHERE clause is executed, the entire table may be deleted. Therefore, always make sure to use appropriate conditions when performing deletion operations.

The above is the detailed content of How to use delete statement. For more information, please follow other related articles on the PHP Chinese website!