Delete statement
236
To delete one or multiple records from a table you use delete
statement.
delete from `user` where `id` = 1;
Query OK, 1 row affected (0.05 sec)
Let's look into the table:
select * from `user`;
+----+----------+-----------------------+--------------+---------------------+
| id | name | email | password | registered |
+----+----------+-----------------------+--------------+---------------------+
| 2 | Angel | angel@springville.com | 1PzIjSWmj3 | 2020-02-07 00:00:00 |
| 3 | Carolina | carolina@studio.org | funnybunny95 | 2020-06-12 00:00:00 |
| 4 | Michael | michael@mail.com | 84738257342 | 2020-09-24 00:00:00 |
+----+----------+-----------------------+--------------+---------------------+
3 rows in set (0.00 sec)
As you we can see, the first record has been deleted.
Delete statement without where
clause will delete all records:
delete from `user`;
The same result could be achieved with truncate table
statement:
truncate table `user`;
A note on auto_increment column
When you delete all data from a table with truncate table
command, auto_increment
column counter value is reset back to 1
. However when you do the same with delete
statement, it remains unchanged.
Rate this post:
Lesson 7
Share this page:
Learning plan
How to create one or multiple records in a table
How to query records from a table
How to update previously created data in a table
8. Delete statement
How to delete data from a table