MySQL remove
We can use MySQL Delete statement to implement the removal on the database. If you want to remove any record from the MySQL database table then you can use MySQL Delete From. Here in this section we will describe how you can use Delete From in your MySQL query to remove records. Syntax is as given below :
Syntax :
| DELETE from table [ WHERE clause ]; |
WHERE clause is used when you want to perform query on some specific data records. If we don't use WHERE it will remove all records from the database table. In WHERE clause we can provide some specific condition, based on which query would be resulted.
Explanation :
Before firing the query we must have one or more table in any database. For our example we have created a table "users", for this table structure is as given below :
| Query |
CREATE TABLE `users` ( `id` int(11) NOT NULL, `username` varchar(50) default NULL, `password` varchar(50) default NULL, `email` varchar(50) default NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1; /*Data for the table `users` */ insert into `users`(`id`,`username`,`password`,`email`) values (1,'Ankur','ankur','[email protected]'), (2,'Vinay','vinay','[email protected]'); mysql> select * from users; |
| Output |
+----+----------+----------+-----------------+ | id | username | password | email | +----+----------+----------+-----------------+ | 1 | Ankur | ankur | [email protected] | | 2 | Vinay | vinay | [email protected] | +----+----------+----------+-----------------+ 2 rows in set (0.00 sec) |
Now on this table we can execute query for delete . Here we will remove all information of the user with the id value 2.
| Query |
mysql> DELETE from users where users.id = 2; Query OK, 1 row affected (0.01 sec) mysql> select * from users; |
| Output |
+----+----------+----------+-----------------+ | id | username | password | email | +----+----------+----------+-----------------+ | 1 | Ankur | ankur | [email protected] | +----+----------+----------+-----------------+ 1 row in set (0.00 sec) |
As you can see in output that it has removed the record with the id value 2.