Updated June 1, 2023
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 particular 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 proper and effective access to databases. For this, removing any table and adjusting the records in the database might be essential. 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 permanently removes the database table and its information from the MySQL server.
- Here, the IF EXISTS keyword denotes a condition if a table is available in the database or not.
- If you remove a table not present in the database using the IF EXISTS option, the server will produce a NOTE that can be accessed by the statement SHOW WARNINGS.
- You can also use a TEMPORARY keyword to remove the temporary database table, not the non-temporary one.
- Again, note that the DROP table command will delete tables but not drop the particular user privileges linked with the specific table. Suppose if new create a new table has an identical name to that of the removed one, then the present privileges will be provided to it by MySQL, which 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, we must use other table names parted by a 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.
- To delete any table and execute the DROP query, the user must have DROP rights for the database table we want to drop.
How Does MySQL Drop Table Work?
Suppose we have a table in our database named Persons and 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 to remove all tables and make hemthem empty. But if we want to remove any database table, we use the DROP TABLE statement that permanently drops the table with its records.
The MySQL statements DROP and TRUNCATE have the same functionality used for the deletion process. But they are different in working, syntax, and results.
For truncating a table, we have the succeeding syntax structure as follows:
TRUNCATE TABLE TableName;The DROP Table query deletes the whole table and its records, but the TRUNCATE Table query will delete only the data inside the database table, not the whole table. Remember this difference while properly using the MySQL queries DROP TABLE and TRUNCATE TABLE.
Examples of Drop Table in MySQL
Let us elaborate on some examples using the MySQL DROP TABLE query statement:
Example #1
Example of using 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 a 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 of using 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 a comma, we will use the following query and also recreate the previous table Employees:
DROP TABLE Persons,Employees;Output:
Example #3
Example of using DROP TABLE query to remove a non-existing database table: We are considering the statement in MySQL to attempt to execute the DROP TABLE query for the table unavailable in the database server.
DROP TABLE Products;When the above statement is queried, and MySQL runs the code, 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 remove the table in the database server, which will also delete the related information. Therefore, we should be careful before deleting any existing table because it may result in dropping any important data or info kept in the database server.
Recommended Articles
We hope that this EDUCBA information on “MySQL DROP TABLE” was beneficial to you. You can view EDUCBA’s recommended articles for more information.
