mysql DELETE FROM and TRUNCATE With MySQL

[Linux Hint]

MySQL databases are known as system databases, which contain tables for storing the information required while running the MySQL server. MySQL categorizes tables into “Grant System Tables” and “Object Information System Tables”. Additionally, database table records can be removed or deleted wherever required. SQL queries, such as “DELETE” and “TRUNCATE” can be utilized for this corresponding purpose.

The outcomes of this write-up are:

• What is MySQL’s “DELETE FROM” Statement?
• How to Run the “DELETE FROM” Statement in MySQL?
• What is MySQL’s “TRUNCATE” Statement?
• How to Run the “TRUNCATE” Statement in MySQL?

What is MySQL’s “DELETE FROM” Statement?

When a user wants to remove or delete particular records(row) from the MySQL database tables, the “DELETE” statement can be used. It is a Data Manipulation Language command which contains the “WHERE” clause. If the condition is specified, it will delete or remove only the particular records otherwise, the whole table records will be deleted. It can save the removed logs of the deleted record.

Syntax

The general syntax of the “DELETE” statement is given below:

DELETE FROM <table-name> WHERE <condition>;

How to Run the “DELETE FROM” Statement in MySQL?

Follow the provided steps to use the DELETE FROM“ statement to delete the records in MySQL.

Step 1: Launch Command Prompt

Initially, search for the “Command Prompt” and launch it:

Step 2: Access MySQL Server

Then, connect the terminal to the MySQL server by executing the “mysql” command:

mysql -u root -p

As you can see, we have been successfully connected with the MySQL server:

Step 3: List Databases

Execute the provided command to list all databases:

SHOW DATABASES;

From the below given displayed list of databases, we chose the highlighted database:

Step 4: Change Database

Change the database through the “USE” command:

USE mynewdb;

Step 5: View Database Tables

Now, list all the tables by utilizing the “SHOW” statement:

SHOW TABLES;

From the provided tables list, we want to delete the records from the “std1” table:

Step 6: List Table Content

To show the content of the databases tables, execute the “SELECT” command:

SELECT * FROM Std1;

As you can see, the specified table contains two records, and we want to remove the second row:

Step 7: Delete Record

Next, execute the “DELETE” statement with the “WHERE” clause to remove the particular record:

DELETE FROM Std1 WHERE FirstName='Fatima';

Step 8: Verification

Run the “SELECT” statement to ensure the record is deleted:

SELECT * FROM Std1;

It can be observed that the row has been deleted from the table:

Let’s check out the next section and learn about the “TRUNCATE” statement.

What is MySQL’s “TRUNCATE” Statement?

To delete all the existing rows in the table, the “TRUNCATE” statement can be used. It is the “Data Definition Language” command, which removes all the table’s records. However, it cannot be used with the “WHERE” clause. Unlike the “DELETE” statement, the transaction log for every removed record is not stored. Once the delete is removed through the “TRUNCATE” command, it cannot be rolled back.

Syntax

The general syntax of the TRUNCATE“ command is:

TRUNCATE TABLE <table-name>;

How to Run the “TRUNCATE” Statement in MySQL?

To remove the entire records of the MySQL databases table by utilizing “TRUNCATE“, check out the provided instructions.

At first, execute the “SELECT” statement to show the table content:

SELECT * FROM std1;

To delete all the records, run the provided command:

TRUNCATE TABLE Std1;

Here, the “Std1” is the target table name:

To ensure the whole table record is removed or not, use the provided statement:

SELECT * FROM std1;

It can be observed that the table is empty:

That’s all! We have briefly explained the MySQL “DELETE” and “TRUNCATE” commands.

Conclusion

The “DELETE FROM <table-name> WHERE <condition>;” is the Data Manipulation Language command used for deleting specific records, which can contain the WHERE clause and save the deleted logs of removed records. On the other hand, the “TRUNCATE TABLE <table-name>;” is the Data Definition Language command which removes all the records from the table, and it does not contain the “WHERE” clause. This write-up discussed the MySQL “DELETE” and “TRUNCATE” commands.

View the full article