5.1 DELETE FROM statement
What's easy to do in SQL is delete data. You can delete absolutely everything very quickly, and no one will even ask you for any confirmation.
Let's start with the simplest scenario: how to delete one row in a table .
This is the scenario you will see most often, it is usually the deletion of a specific record, and the standard query usually looks like:
DELETE FROM table
WHERE id = 133;
This is the only query where you do not need to specify the names of the columns: after all, the data is deleted immediately in rows.
The second scenario is deleting the rows that are specified by the id list , everything is also quite simple here:
DELETE FROM table
WHERE id IN (1, 2, 3, …);
The third scenario is to remove rows that match a certain condition:
DELETE FROM table
WHERE condition;
Let's say we want to fire all our programmers, then we need to write a request like:
DELETE FROM employee
WHERE occupation = 'Programmer';
And finally, if you want to delete all records, you can write a query like this:
DELETE FROM table
This simple query is enough to remove all records from a table. By the way, there will be no Ctrl + Z in this case. Recordings are simply deleted without the possibility of recovery and that's it. So make backups, more often .
5.2 Removing everything
For quick removal (to add headaches to users), SQL has a few more commands.
How to quickly delete all data in a table? Use the operator TRUNCATE
:
TRUNCATE TABLE table
One typo in the name of the table - and a couple of days of data recovery are provided to you. Be glad you're not a database admin.
If you need to delete not just the data in the table, but the table itself, then there is an operator DROP
for this :
DROP TABLE table
By the way, there are similar options with database schemas . If you want to delete the database itself, then:
DROP SCHEME database
or:
DROP DATABASE database
You can also use DROP to delete:
- EVENT
- FUNCTION
- PROCEDURE
- INDEX
- VIEW
- TRIGGER
And here are a couple of interesting stories related to data deletion:
Break of the day. GitLab deleted 300 GB of customer data due to sysadmin error
sudo rm -rf, or Chronicle of the GitLab.com database incident from 2017/01/31