SQL DELETE Statement - Real World Examples:
To understand the DELETE statement, first let us create a database and a table in it; populate the table (fill the table with data) and then operate on it.
1) Create a database with any database name. In this example, the database is named hub. Write the following code in any SQL console. I am using a MySQL console.
CREATE DATABASE hub;
2) In order to create a table in that database, first we have to use the database. To use the database enter the following code.
3) Now create a table called 'friends' with four fields namely id, first_name, last_name and age. Enter the following code to create the table.
CREATE TABLE `hub`.`friends` (`id` INT( 10 ) NOT NULL AUTO_INCREMENT PRIMARY KEY ,`first_name` VARCHAR( 30 ) NOT NULL ,`last_name` VARCHAR( 30 ) NOT NULL ,`age` INT( 3 ) NOT NULL) ENGINE = MYISAM ;
4) Populate the table using the INSERT INTO statement.
INSERT INTO `hub`.`friends` (`id`, `first_name`, `last_name`, `age`) VALUES (NULL, 'harry', 'potter', '20'), (NULL, 'ron', 'weasley', '20'), (NULL, 'hermione', 'granger', '20'), (NULL, 'albus', 'dumbledore', '70');
5) Now use the DELETE statement and perform a few operations.
DELETE * FROM friends;
DELETE FROM friends WHERE last_name = weasley;
DELETE * FROM friends WHERE id > 2;
How the DELETE Statement is Different from TRUNCATE and DROP Commands:
DELETE statement deletes specific data that we want to delete.
TRUNCATE empties all the data but keeps the columns for future use.
DROP statement is used to delete the data permanently along with the column names. One should be careful while using the DROP statement.
Common Mistakes that Should be Avoided:
1) Don't forget the semicolon (;) to end the statement.
2) Make sure that the spaces are used properly.
3) The DELETE statement should be used with caution as no backup is created when the data is deleted.