MySQL Date TypesMySQL LAST_INSERT_IDMySQL InsertMySQL SelectMySQL WhereMySQL Group ByMySQL Order ByMySQL LimitMySQL LikeMySQL ISNULLMySQL BetweenMySQL JoinMySQL Left JoinMySQL Right JoinMySQL Cross JoinMySQL Self JoinMySQL Sub QueryMySQL UpdateMySQL DeleteMySQL ExplainMySQL IfMySQL Full-Text SearchMySQL INFORMATION_SCHEMAMySQL ConnectionMySQL Server Helpful FunctionsMySQL ViewMySQL Stored ProcedureMySQL FunctionsMySQL TransactionMySQL TriggerMySQL EventMySQL DeadlockMySQL DumpMySQL Reset Root PasswordMySQL interview – Advanced QueriesMySQL JsonMySQL BinlogMySQL Closure Table
MySQL Delete
To delete data from a table, DELETE statement is used. Once data is deleted from your tables you can’t recover it. It is gone. It is recommended to back up your database regularly so that if data is gone you can still recover it from your backups.
DELETE FROM table_name WHERE condition;
The WHERE clause is optional but if it’s omitted then all rows will be deleted.
Here is an example of DELETE where we are deleting customer with id 21.
As you can see row 21st is gone. The DELETE statement returns the number of rows deleted.
To delete all rows within a table, use the TRUNCATE command which has better performance.
DELETE with LIMIT
When deleting data it is recommended to turn off SQL_SAFE_UPDATES.
DELETE FROM table_name ORDER BY column_name LIMIT count;
DELETE with ON CASCADE
ON DELETE CASCADE is used to delete rows or data in a child table when a row is deleted in the parent table.
You have to create your child table with it.
CREATE TABLE table_name (
column_names...
FOREIGN KEY (FK)
REFERENCES parent_table_name (FK)
ON DELETE CASCADE
);
Here is the query to show which tables use ON DELETE CASCADE.
USE information_schema;
SELECT
table_name
FROM
referential_constraints
WHERE
constraint_schema = 'database_name'
AND referenced_table_name = 'parent_table'
AND delete_rule = 'CASCADE'
MySQL Date TypesMySQL LAST_INSERT_IDMySQL InsertMySQL SelectMySQL WhereMySQL Group ByMySQL Order ByMySQL LimitMySQL LikeMySQL ISNULLMySQL BetweenMySQL JoinMySQL Left JoinMySQL Right JoinMySQL Cross JoinMySQL Self JoinMySQL Sub QueryMySQL UpdateMySQL DeleteMySQL ExplainMySQL IfMySQL Full-Text SearchMySQL INFORMATION_SCHEMAMySQL ConnectionMySQL Server Helpful FunctionsMySQL ViewMySQL Stored ProcedureMySQL FunctionsMySQL TransactionMySQL TriggerMySQL EventMySQL DeadlockMySQL DumpMySQL Reset Root PasswordMySQL interview – Advanced QueriesMySQL JsonMySQL BinlogMySQL Closure Table