JIYIK CN >

We will learn the best way to delete all rows in a database using DELETE. We will explore phpMyAdminthe command to delete rows from a given table.MySQLDELETE

We will also learn the difference between the DELETE, , DROPand TRUNCATEcommands. If we have associated foreign keys in the table, we will see how to row MySQLin DELETE.

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

phpMyAdminDelete MySQLrows from the database using

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

Let’s learn about each of them below.

Deleting rows in SQLa query withoutphpMyAdmin

In some cases, you must delete all rows in a table at once. Click 检查所有and then press 删除to do this.

If you want to delete only one row from the selected table, then click 删除the button to delete that particular row. Refer to the image below.

If you want to delete one or more rows but not all, you can check them first and then press Delete. You can follow the instructions by looking at the following screenshots.

If you want to delete rows from different tables, you have to go to each table and delete as above. This takes time and effort.

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

Use SQLthe query to delete phpMyAdminthe rows in

There are three different ways you can use to achieve your goal based on your project requirements. Let’s understand them one by one to use them.

You might be thinking phpMyAdminwhere to write SQLthe query while using . You can write the query in SQLunder the tab (see the screenshot given below).phpMyAdminSQL

Press the button in the lower right corner Goor press the Ctrl+ Enterkey to execute the query.

DROPIs Data Definition Languageone of the commands (DDL).

This command is used to DROPdelete an entire table at once. It will delete the table's permissions, triggers, indexes, constraints, definitions, and all data.

DROP TABLE your_table_name;

DELETEThe command deletes one or more rows from a given table. It is one of the Data Manipulation Language (DML) commands.

DELETEcommand WHEREis used with the -a clause if you want to DELETEremove a row. WHEREThe -b clause will WHEREremove all rows from the table by omitting the -c clause.

DELETEThe delete_row command is slower than TRUNCATEthe remove_row command because it tracks each deleted row in the transaction log. DELETEThe remove_row command only deletes rows from the database, not tables.

#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;

TRUNCATEThe command works like DELETEthe Delete command, but it is one of the Data Definition Language (DDL) commands. It is specifically used to delete all rows in a table because it does not allow the Delete WHEREclause.

Similar to DELETE, TRUNCATEalso keeps the table in the database but deletes the rows. Because it is recorded only once in the transaction log, it is DELETEfaster than the command.

TRUNCATE TABLE your_table_name;

What if you have a relational database where one table is related to another table using a foreign key. How do you delete it now? In this case, ON DELETE CASCADEthe option has been added.

Whenever a row is deleted in the primary table, the same row will also be deleted in the other table having a foreign key. Let us understand with the help of the sample code given below. We have created two tables named tb_studentand .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 you are done, you can delete rows from them very easily tb_student.

This is because whenever you delete from , the corresponding row in will also be deleted due to the option tb_studenton the foreign key .ON DELETE CASCADEtb_course

in conclusion

We explored different ways to delete a row and all rows from a table. We also learned to delete only data and table along with its data. This tutorial also emphasized on safe deletion in case of association with another table as a foreign key.