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
  1. 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
  1. DROP DATABASE IF EXISTS CSharpCornerdb; 
 

MySQL DROP TABLE Statement

 
In this section, I will explain how to use MySQL DROP TABLE statement 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’s take some examples.
 

1) DROP a Single Table

 
First, we have to create a “BollywoodStars” table.
  1. CREATE TABLE BollywoodStars (  
  2.   BSID int NOT NULL AUTO_INCREMENT,  
  3.   BSName varchar(100) NOT NULL,  
  4.   BSAwardForYear int NOT NULL,  
  5.   BSAddress varchar(250) NOT NULL,  
  6.   BSNCBStatus varchar(100) NOT NULL,  
  7.   PRIMARY KEY (`BSID`)  
  8. ); 
Now, execute the DROP TABLE statement to drop a “BollywoodStars” table permanently from a database.
  1. DROP TABLE BollywoodStars; 
 

2) DROP Multiple Tables

 
First, we have to create a few dummy tables for testing purposes.
  1. CREATE TABLE GetResortsDetails(   
  2.   R_ID INT PRIMARY KEY AUTO_INCREMENT,   
  3.   R_Name VARCHAR(50),   
  4.   R_Address VARCHAR(200)   
  5. );   
  6.    
  7. CREATE TABLE GetCompanyDetails(   
  8.   C_ID INT,   
  9.   C_Name VARCHAR(50),   
  10.   C_Address VARCHAR(200),  
  11.   C_Description VARCHAR(250),  
  12.   C_BusinessModel VARCHAR(50)   
  13. ); 
Now, execute the DROP TABLE statement to drop both the tables from the database.
  1. DROP TABLE GetResortsDetails, GetCompanyDetails; 
 

3) Drop a Non-Existing Table

 
If you want to drop a table that does not exist in the database. In this case, MySQL will issue the following error.
 
Example
  1. DROP TABLE GetCompanyDetails; 
Following error issued by MySQL.
 
 
To prevent this, use the following syntax when dropping the table.
 
Syntax
DROP TABLE IF EXISTS <table_name>;
 
Example
  1. DROP TABLE IF EXISTS GetCompanyDetails; 
 

MySQL DROP INDEX Statement

 
The DROP INDEX Statement is used to delete an index in a table. Use the following below syntax to do this.
 
Syntax
ALTER TABLE <table_name>
DROP INDEX <index_name>;
 
Example
 
First, we have to create a dummy table without any index. Let’s see.
  1. CREATE TABLE GetCompanyDetails(   
  2.   C_ID INT,   
  3.   C_Name VARCHAR(50),   
  4.   C_Address VARCHAR(200),  
  5.   C_Description VARCHAR(250),  
  6.   C_BusinessModel VARCHAR(50)   
  7. ); 
Now, by alter table statement, add index to the above table.
  1. ALTER TABLE GetCompanyDetails  
  2. ADD PRIMARY KEY (C_ID),  
  3. ADD INDEX (C_Name,C_BusinessModel); 
Now, drop the INDEX by using the following command.
  1. ALTER TABLE GetCompanyDetails  
  2. DROP INDEX C_Name; 
Now, you can check the result by typing the following command.
  1. DESCRIBE GetCompanyDetails; 

MySQL ALTER Table Statement

 
In this section, I will explain you how to use MySQL DROP COLUMN statement to drop a column from a table.
 
Syntax
ALTER TABLE <table_name>
DROP COLUMN <column_name>;
 
Example
 
First, we have to create a dummy table for testing purposes. Now, let’s see.
  1. DROP TABLE IF EXISTS GetCompanyDetails;  
  2.    
  3. CREATE TABLE GetCompanyDetails(   
  4.   C_ID INT PRIMARY KEY auto_increment,   
  5.   C_Name VARCHAR(50),   
  6.   C_Address VARCHAR(200),  
  7.   C_Description VARCHAR(250),  
  8.   C_BusinessModel VARCHAR(50),  
  9.   C_Revenue BIGINT  
  10. ); 

1) Drop a Single Column

 
Now, drop a single column by using the following statement.
  1. ALTER TABLE GetCompanyDetails  
  2. DROP COLUMN C_Description; 
Now, check the result by typing the following command.
  1. DESCRIBE GetCompanyDetails; 
 

2) Drop Multiple Columns

 
You can drop more than one columns simultaneously by using the following statement.
  1. ALTER TABLE GetCompanyDetails  
  2. DROP COLUMN C_BusinessModel,  
  3. DROP COLUMN C_Address;  
Now, check the result by typing the following command.
  1. DESCRIBE GetCompanyDetails; 
 
Note
All table data and the table definition can be removed, so be careful with DROP statement.
 

CONCLUSION

 
In this article, I have discussed the concept of MySQL DROP Statements with various examples.
 
I hope you enjoyed this article. Follow C# Corner to learn more new and amazing things about MySQL.
 
Thanks for reading this article!
Author
Arjun Panwar
0 10.6k 5.9m
Next » SQL TOP Statement

©2024 C# Corner.