How to Delete All Rows in MySQL Database Using phpMyAdmin

1. Delete Rows in MySQL Database Using phpMyAdmin
2. Conclusion

We will learn the best way to delete all rows in the MySQL database using phpMyAdmin. We will explore the DELETE command to remove row(s) from a given table.

We will also learn about the differences between DELETE, DROP, and TRUNCATE commands. We will see how to DELETE rows in MySQL if we have an associated foreign key in the table.

We are using MySQL version 8.0.27, and you can download the latest one from here.

Delete Rows in MySQL Database Using phpMyAdmin

In phpMyAdmin, you can simply delete rows in two ways. You can do it by using Graphical User Interface (GUI) Option and SQL query.

Let’s understand each of them below.

Delete Rows in phpMyAdmin Without SQL Query

There are some scenarios where you have to delete all rows from one table in one go. Click on Check all and then press Delete to do this.

If there is a situation where you want to delete only one row from the selected table, then click on the Delete button to remove that particular row. See the following image to follow.

If you want to remove one row or more but not all, you can check them first and then press the Delete. You can follow the instruction by looking at the following screenshot.

In case if you want to delete rows from different tables, you have to go to each table and then delete as mentioned above. It takes time and effort.

Here, SQL queries come into play and make the job very easy.

Delete Rows in phpMyAdmin With SQL Query

You can use three different ways to accomplish your goal as per your project requirements. Let’s understand them one by one to use them.

You might be thinking about where to write the SQL query while using phpMyAdmin. You can write your SQL queries in phpMyAdmin under the SQL tab (see the screenshot given below).

Press the Go button in the bottom-right corner or press the Ctrl+Enter key to execute the query.

DROP

DROP is one of the Data Definition Language Command (DDL).

This command is used to DROP the whole table in one go. It removes permissions, triggers, indexes, constraints, definition, and all data for that table.

Syntax

DROP TABLE your_table_name;

DELETE

The DELETE command removes one or multiple rows from the given table. It is one of the Data Manipulation Language (DML) commands.

DELETE command is used with the WHERE clause if you want to DELETE one row. The WHERE clause will delete all rows from the table by omitting the WHERE clause.

The DELETE command is slower than the TRUNCATE command because it keeps track of every deleted row in the transaction log. The DELETE command only removes the row(s) and not the table from the database.

Syntax

#delete all rows from the table
DELETE FROM your_table_name;

#delete one row from the table
DELETE FROM your_table_name WHERE your_table_name.id = 1;

TRUNCATE

TRUNCATE command works similar to the DELETE command, but it is one of the Data Definition Language (DDL) commands. It is specifically used to delete all rows from the table because it does not allow to use WHERE clause.

Similar to DELETE, TRUNCATE also keeps the table in the database but removes rows. As it logs only once in the transaction log thus, it is faster than the DELETE command.

Syntax

TRUNCATE TABLE your_table_name;

What if you have a relational database in which one table is associated with the other using a foreign key. How can you delete it now? In this case ON DELETE CASCADE option is added.

Whenever one row is deleted in the main table, the same row will also be deleted in the other table with a foreign key. Let’s understand with the example code given below. We created two tables named tb_student and tb_course.

CREATE TABLE tb_student
(
     id INT PRIMARY KEY,
     firstname varchar(8),
     lastname varchar(8),
     gender varchar(8)
);

CREATE TABLE tb_course
(
  course_id INT PRIMARY KEY,
  course_name varchar(8),
  student_id INT,
 FOREIGN KEY(student_id) 
 REFERENCES tb_student(id) 
 ON DELETE CASCADE
);

Populate these tables with data. Once it is done, you can delete rows from tb_student very easily.

It is because whenever you will delete from tb_student, the respective row from tb_course will also be deleted due to the ON DELETE CASCADE option on the foreign key.

Conclusion

We explored different ways to remove one and all rows from the table. We also learned to delete data only and table and its data. This tutorial also highlights the safe deletion in case of association with another table as a foreign key.