MySQL DROP TABLE

Definition of MySQL DROP TABLE

MySQL DROP TABLE is a MySQL query statement that is responsible to drop a MySQL table existing in the database server. This is a MySQL command which is required to be used if we want to delete any unwanted table or for any reason, a certain table is built up in the database.

Suppose, sometimes to manage and maintain the database servers we need to repair or recover the database tables and records from time to time to make the proper and effective access of databases. For this, it might be essential to remove any table and adjust the records in the database. We will use the DROP table statement on the server and execute it to make proper running and control administrative functions on the MySQL server.

Syntax:

We have the following elementary syntax code for the DROP Table query in MySQL:

DROP TABLE [IF EXISTS] TableName [,TableName1, TableName2,….,] [CASCADE | RESTRICT];

This query statement will permanently remove the database table and its information from the MySQL server.

• Here, IF EXISTS keyword denotes a condition if a table is available in the database or not.
• This is because if you remove a table that is not present in the database using IF EXISTS option then, the server will produce a NOTE that can be accessed by the statement SHOW WARNINGS.
• You can also use a TEMPORARY keyword to make sure you remove the temporary database table, not the non-temporary one.
• Again, note that the DROP table command will just delete tables but it will not drop the particular user privileges linked with the specific table. Suppose if new create a new table has the identical name as that of the removed one then, the present privileges will be provided to it by MySQL that may position a security threat to the server.
• Further, if we want to delete more than one table implemented with a DROP table query just once then, we need to use other table names which are parted by comma(,). Also, CASCADE or RESTRICT options in the syntax are provided that do not add any impact on the query and are specially reserved for future uses of MySQL versions.
• In order to delete any table and execute the DROP query, the user must have DROP rights for the database table that we want to drop.

How does MySQL Drop Table Work?

Suppose, we have a table in our database named Persons and we want to implement the MySQL DROP TABLE command on this table. We have written the following SQL query to be executed and perform deletion on the table Persons:

DROP TABLE Persons;

Let us consider if we want to delete all the database tables then, in MySQL we apply the DROP DATABASE command that will remove all tables and make it empty. But if we want to remove any database table then we use the DROP TABLE statement that permanently drops the table with its records.

The MySQL statements DROP and TRUNCATE have the same functionality that is used for the deletion process. But they are different in the form of working, syntax, and result.

For truncating a table we have the succeeding syntax structure as follows:

TRUNCATE TABLE TableName;

The DROP Table query deletes the whole table along with its records but the TRUNCATE Table query will delete only the data inside the database table, not the whole table. Remember this difference while using the MySQL queries DROP TABLE and TRUNCATE TABLE properly.

Examples of Drop Table in MySQL

Let us elaborate on some examples using the MySQL DROP TABLE query statement:

Example #1

Example to use DROP TABLE query to remove a single database table: For implementing the MySQL statement first we need to create a table in the database using the CREATE TABLE statement in the MySQL server. So, let us build a table for sample:

CREATE TABLE Employees (EmpID INT PRIMARY KEY AUTO_INCREMENT, EmpName VARCHAR (255) NOT NULL, EmpAddress VARCHAR(255) NOT NULL, Phone INT NOT NULL, JoinDate DATETIME NOT NULL);

Also, let us insert some records in the table created using the INSERT command in the MySQL database:

INSERT INTO Employees(EmpID,EmpName,EmpAddress,Phone,JoinDate) VALUES (‘10’,’Rita Shah’,’Rampur’,’897678908’,’2020-05-25 10:04:30’);

To view the table records we will apply the SELECT statement below:

SELECT * FROM Employees;

Output:

Now, for dropping the table we will use the following query:

DROP TABLE Employees;

Output:

Example #2

Example to use DROP TABLE query to remove more than one database table: For implementing this MySQL statement, again let us create another table on the database server. So, let us build other tables for showing the dropping of multiple tables:

CREATE TABLE Persons (PersonID INT PRIMARY KEY AUTO_INCREMENT, LastName VARCHAR (255) NOT NULL, FirstName VARCHAR (255) NOT NULL, Address VARCHAR(255) NOT NULL, City VARCHAR(255) NOT NULL, JoinDate DATETIME NOT NULL);

Also, let us insert some records in the table created using the INSERT command in the MySQL database:

INSERT INTO Persons (PersonID,LastName,FirstName,Address,City,JoinDate) VALUES (‘1’,’Sharma’,‘Nikhil’,’Dhanbad,Jharkhand’,’Dhanbad’,’2020-01-09 10:05:00’);

To view the table records we will apply the SELECT statement below:

SELECT * FROM Persons;

Output:

Now, for dropping the two tables separated by comma we will use the following query and also recreate the previous table Employees:

DROP TABLE Persons,Employees;

Output:

Example #3

Example to use DROP TABLE query to remove a non-existing database table: We are considering the statement in MySQL to attempt executing DROP TABLE query for the table that is not available in the database server.

DROP TABLE Products;

When the above statement is queried and MySQL runs the code then, it will generate the following error.

Output:

Thus, if we use the MySQL option IF EXISTS with the above query like the below statement:

DROP TABLE IF EXISTS Products;

Then, MySQL will issue a warning instead of an error on processing.

Output:

To view the warning or read the error we can use the SHOW WARNINGS statement in MySQL:

SHOW WARNINGS;

Output:

Conclusion

In MySQL, the DROP Table statement helps to remove the table present in the database server that will also delete the information related to it. Therefore, we should be careful before deleting any existing table because it may result in dropping any important data or info that is kept in the database server.

Recommended Articles

This is a guide to MySQL DROP TABLE. Here we also discuss the definition and how does mysql drop table works? along with different examples and its code implementation. You may also have a look at the following articles to learn more –

1. MySQL TRUNCATE()
2. MySQL INSTR()
3. MySQL Constraints
4. MySQL Root