DROP In MySQL

Introduction

In this tutorial, I am going to explain about MySQL DROP Statements with examples. This article covers the following topics.

1. Introduction to the Drop Statement
2. MySQL DROP Database
3. MySQL DROP Table
4. MySQL DROP Index Statement
5. MySQL ALTER Table Statement
6. Conclusion

MySQL Drop Database

In this section, I will explain how to use MySQL DROP DATABASE statement to delete a database.

Dropping or deleting a MySQL database is easy. The drop statement command is used when we have to no longer use one of the SQL databases on the server. It will remove permanently and is very easy to drop an existing MySQL table. But we need to be very careful while deleting any existing database because data lost will not be recovered after deleting a database.

Here, we use the following syntax to drop the database on the server.

Syntax

DROP DATABASE <Database_name>;

Example

DROP DATABASE CSharpCornerdb; 

Note. If you try to drop a database that does not exist in the server, then MySQL will issue an error. To prevent this, you can use the following syntax.

Syntax

DROP DATABASE [IF EXISTS] <Database_name>;

Example

DROP DATABASE IF EXISTS CSharpCornerdb; 

MySQL DROP TABLE Statement

In this section, I will explain how to use MySQL DROP TABLE statements to delete a table from a database.

DROP TABLE statement is used to remove one or more tables from the database. We must have the DROP advantages for each table. All table data and the table definition are removed. DROP TABLE permanently removes the table definition, all of its partitions, and all of the data which was stored in those partitions.

The DROP TABLE statement is used to drop the table permanently. Here, we use the following syntax to drop tables from the database.

Syntax

DROP TABLE [IF EXISTS] <table_name>;

Without wasting time, let’stake some examples.

1. DROP a Single Table

First, we have to create a “BollywoodStars” table.

CREATE TABLE BollywoodStars (  
  BSID int NOT NULL AUTO_INCREMENT,  
  BSName varchar(100) NOT NULL,  
  BSAwardForYear int NOT NULL,  
  BSAddress varchar(250) NOT NULL,  
  BSNCBStatus varchar(100) NOT NULL,  
  PRIMARY KEY (`BSID`)  
); 

Now, execute the DROP TABLE statement to drop a “BollywoodStars” table permanently from a database.

2. DROPTABLE BollywoodStars

3. DROP Multiple Tables

First, we have to create a few dummy tables for testing purposes.

CREATE TABLE GetResortsDetails(   
  R_ID INT PRIMARY KEY AUTO_INCREMENT,   
  R_Name VARCHAR(50),   
  R_Address VARCHAR(200)   
);   
  CREATE TABLE GetCompanyDetails(   
  C_ID INT,   
  C_Name VARCHAR(50),   
  C_Address VARCHAR(200),  
  C_Description VARCHAR(250),  
  C_BusinessModel VARCHAR(50)   
);

Now, execute the DROP TABLE statement to drop both the tables from the database.

DROP TABLE GetResortsDetails, GetCompanyDetails; 

4. DROP a Non-Existing Table