Introduction
In this tutorial, I am going to explain the delete, truncate, and drop statements of MySQL with various examples. Without wasting time, let’s start.
In a MySQL table, if I have to modify and delete, then we have to use some statements on the table. But these commands are different from each other also in case of working. Thus, I have to explain the differences between the DELETE, DROP, and TRUNCATE statements of MySQL.
DELETE Statement in MySQL
If you want to delete a record(s) from a table, then you can use the MySQL DELETE statement.
Syntax
DELETE FROM <table_name>
WHERE <condition> ;
Note. If you don’t specify the WHERE condition in the DELETE statement, then it will delete all rows in the table.
For Example
Use the following table 'entrepreneur' for the demonstration purpose and then apply the DELETE statement.
First, create a new table named 'entrepreneur'.
CREATE TABLE Entrepreneur(
E_id int AUTO_INCREMENT PRIMARY KEY,
Company_Name varchar(100) NOT NULL,
Specialization varchar(100) NOT NULL
);
Now, insert some dummy data into it.
INSERT INTO Entrepreneur VALUES
(1, ‘Vatsa’, ‘IT’),
(2, ‘OEnterprise, ‘Marketing’),
(3, ‘RBanqut’, ‘Hotel’),
(4, ‘JMahal’, ‘Hotel’),
(5, ‘SMall’, ‘Marketing’),
(6, ‘LShop’, ‘Marketing’),
(7, ‘DCompany’, ‘IT’);
Check the data of the Entrepreneur Table.
SELECT * FROM Entrepreneur;
Finally, use the DELETE statement to delete the rows.
1. If you want to delete the company's name whose “Specialization” is “Hotel”. Then use.
DELETE FROM Entrepreneur
WHERE Specialization = ‘Hotel’;
2. To delete all the rows from the Entrepreneur table, use.
DELETE FROM Entrepreneur;
DELETE statement features
The DELETE statement deletes table rows and returns the number of rows deleted. We can delete a selected no of records from the table using a DELETE statement. DELETE statement has two modifiers, namely LOW_PRIORITY and QUICK.
- When we have to use DELETE Command, then the data deleted can be retrieved when you ROLLBACK.
- The QUICK modifier is specified, then the table handler does not merge index leaves during delete; this may cause to speed up certain kinds of deletes.
- The LIMIT clause can also be used to set a limit on the number of rows to be deleted.
- A DELETE is issued with no WHERE clause; all rows are deleted.
- ORDER BY clause can be used in the DELETE statement. In this case, the rows are deleted in the specified order.
Note. With the LOW_PRIORITY keyword, DELETE execution is delayed until no other clients are reading from the table, and If the WHERE clause is specified, then rows are deleted satisfying the given conditions and finally returns the number of rows deleted. With the nowhere clause in auto-commit mode, DELETE works as TRUNCATE and does not return affected rows.
For Example
DELETE FROM Entrepreneur
WHERE E_id < 4;
TRUNCATE Statement in MySQL
In MySQL, the TRUNCATE statement is used to delete all the rows/data in a table. You can say a TRUNCATE statement is like a DELETE statement without a WHERE condition.
Syntax
TRUNCATE TABLE <table_name>;
First, create a new table named 'entrepreneur'.
CREATE TABLE Company(
E_id int AUTO_INCREMENT PRIMARY KEY,
Company_Name varchar(100) NOT NULL,
Field varchar(100) NOT NULL
);
Now, insert some dummy data into it.
INSERT INTO Company VALUES
(1, ‘Vatsa’, ‘IT’),
(2, ‘OEnterprise, ‘Marketing’),
(3, ‘RBanqut’, ‘Hotel’),
(4, ‘JMahal’, ‘Hotel’),
(5, ‘SMall’, ‘Marketing’),
(6, ‘LShop’, ‘Marketing’),
(7, ‘DCompany’, ‘IT’);
Check the data of the Company Table.
SELECT * FROM Company;
Now, use the TRUNCATE statement to delete all the rows from a table named “Company”.
TRUNCATE TABLE Company;
TRUNCATE statement features
TRUNCATE TABLE differs from DELETE in the following ways.
- TRUNCATE drops the table and re-create it. It is much faster than deleting rows one by one.
- Once you use the TRUNCATE command, then u cannot retrieve the data again from the table.
- TRUNCATE is not transaction-safe; an error occurs when attempting one in the course of an active transaction or active table lock.
- TRUNCATE removes all the rows from the Table.
- TRUNCATE does not return the number of deleted rows.
- TRUNCATE functionality is an Oracle SQL extension adopted in MySQL.
- TRUNCATE is probably better thought of as a shortcut for the DROP TABLE/CREATE TABLE rather than a quirky kind of DELETE.
When TRUNCATE is not transaction-safe, an error occurs when attempting one in the course of an active transaction or active table lock. With TRUNCATE TABLE, a table can be re-created (as an empty table) when only the table format file tbl_name.frm is valid and its data or index files have become corrupted. With TRUNCATE TABLE, the table handler reset the AUTO_INCREMENT value starting from the beginning
Note. In the case of partitioned tables, TRUNCATE TABLE drops the data and index files and re-creates but preserves the partition definitions (.par) file. TRUNCATE TABLE works similarly to DELETE if there are foreign key constraints that reference the table; otherwise, it simply drops and re-creates the table. The AUTO_INCREMENT value is reset by TRUNCATE TABLE, even ignoring foreign key constraints if there are.
Note. After truncating, this table does not contain any fields, but it shows in our database by using the following command.
SHOW TABLES;
DROP Statement in MySQL
DROP statement is used to delete the table structure with all the data. DROP statement is used to delete a table and its data permanently.
Syntax
DROP TABLE <table_name>;
Note. Users can drop multiple tables by using a comma-separated list.
Syntax
DROP TABLE (table_name1, table_name2);
First, create a new table named 'entrepreneur'.
CREATE TABLE Entrepreneur(
E_id int AUTO_INCREMENT PRIMARY KEY,
Company_Name varchar(100) NOT NULL,
Field varchar(100) NOT NULL,
Address varchar(100),
Asset DEC(10, 2) NOT NULL,
Cost DEC(10, 2) NOT NULL
);
Ow, drop a table by using the following command.
DROP TABLE Entrepreneur
DROP statement features
The DROP statement deletes the data as well as the structure.
Difference between DROP and DELETE in MySQL
The difference between the DROP and DELETE tables is that, after executing the DELETE statement, the contents of the table are removed, but the structure remains the same, but in the case of the DROP statement, both the contents and structure are removed.
Conclusion
In this article, I have discussed the concept of delete, truncate, and drop statements of MySQL with various examples.
I hope you enjoyed this article. Follow C# Corner to learn more new and amazing things about MySQL.
Thanks for reading this article!