JIYIK CN >

This article aims to understand how to delete multiple tables in MySQL.

Most businesses and organizations that use MySQL to visualize and analyze data have multiple tables to work with. Sometimes, these tables become redundant. In such cases, you need to get rid of these tables to make room for newer tables or records.

Furthermore, it becomes crucial to understand how to get rid of multiple tables at once to avoid messy queries that run for hours individually. Removing multiple queries at once also helps improve the readability of the entire code base.

Let us try to understand this a little deeper.

However, before we get started, we'll create two virtual tables to work with. Here, we create a table student_datesand a few rows.

-- create the table student_dates
CREATE TABLE student_dates(
stu_id int,
stu_firstName varchar(255) DEFAULT NULL,
stu_date date,
primary key(stu_id)
);

Similarly, we can create a table student_detailsand some defined rows using the following query.

-- create the table student_details
CREATE TABLE student_details(
stu_id int,
stu_firstName varchar(255) DEFAULT NULL,
stu_lastName varchar(255) DEFAULT NULL,
primary key(stu_id)
);

Use INSERTthe statement to insert entries into the MySQL table

The previous query creates a student_datestable named . INSERTWith the help of add statement, let us add data for some students. The operation can be performed as follows.

-- insert rows to the table student_dates
INSERT INTO student_dates(stu_firstName,stu_date)
VALUES("Preet",STR_TO_DATE('24-May-2005', '%d-%M-%Y')),
("Dhruv",STR_TO_DATE('14-June-2001', '%d-%M-%Y')),
("Mathew",STR_TO_DATE('13-December-2020', '%d-%M-%Y')),
("Jeet",STR_TO_DATE('14-May-2003', '%d-%M-%Y')),
("Steyn",STR_TO_DATE('19-July-2002', '%d-%M-%Y'));

This code will student_datesenter the student data in the table . We can visualize this table using the following command.

SELECT * from student_dates;

The code block will generate the following output.

stu_id	stu_firstName	stu_date
1		Preet			2005-05-24
2		Dhruv			2001-06-14
3		Mathew			2020-12-13
4		Jeet			2003-05-14
5		Steyn			2002-07-19

Similarly, let us student_detailsinsert values ​​into the table. We can do that using the following query.

-- insert rows to the table student_details
INSERT INTO student_details(stu_id,stu_firstName,stu_lastName)
VALUES(1,"Preet","Sanghavi"),
(2,"Rich","John"),
(3,"Veron","Brow"),
(4,"Geo","Jos"),
(5,"Hash","Shah"),
(6,"Sachin","Parker"),
(7,"David","Miller");

student_detailsThe table can be visualized with the help of the following query.

SELECT * from student_details;

Output:

stu_id	stu_firstName	stu_lastName
1	      Preet	        Sanghavi
2	      Rich	        John
3	      Veron	        Brow
4	      Geo	        Jos
5	      Hash	        Shah
6	      Sachin	    Parker
7	      David	        Miller

Use DROP TABLEthe statement to delete multiple tables in MySQL

To delete a table in MySQL, you can use DROP TABLEthe statement. The syntax of the statement is as follows.

DROP TABLE name_of_the_table;

A table named name_of_the_tablewill be placed in the query.

Now let us aim to delete multiple tables i.e. student_datesand student_details. We can do that using the following query.

DROP TABLE IF EXISTS student_dates, student_details;

The query eliminates the tables student_datesand student_details. These names can now be used to name the table with the newer records.

Therefore, with the help DROP TABLEof statement, we can delete multiple tables quickly and efficiently in MySQL with high code readability.