Post
Ask Question

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;

dropdtabase

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;

existstable

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

droptable

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;

droptablesql

4. 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

DROP TABLE GetCompanyDetails;

Following error issued by MySQL.

isuedtable

To prevent this, use the following syntax when dropping the table.

Syntax

DROP TABLE IF EXISTS <table_name>;

Example

DROP TABLE IF EXISTS GetCompanyDetails;

exsitstable

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.

CREATE TABLE GetCompanyDetails(   
  C_ID INT,   
  C_Name VARCHAR(50),   
  C_Address VARCHAR(200),  
  C_Description VARCHAR(250),  
  C_BusinessModel VARCHAR(50)   
);

Now, by alter table statement, add index to the above table.

ALTER TABLE GetCompanyDetails  
ADD PRIMARY KEY (C_ID),  
ADD INDEX (C_Name,C_BusinessModel);

Now, drop the INDEX by using the following command.

ALTER TABLE GetCompanyDetails  
DROP INDEX C_Name;

Now, you can check the result by typing the following command.

DESCRIBE GetCompanyDetails;

typingcommand

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.

DROP TABLE IF EXISTS GetCompanyDetails;  
   CREATE TABLE GetCompanyDetails(   
  C_ID INT PRIMARY KEY auto_increment,   
  C_Name VARCHAR(50),   
  C_Address VARCHAR(200),  
  C_Description VARCHAR(250),  
  C_BusinessModel VARCHAR(50),  
  C_Revenue BIGINT  
);

1. Drop a Single Column

Now, drop a single column by using the following statement.

ALTER TABLE GetCompanyDetails  
DROP COLUMN C_Description;

Now, check the result by typing the following command.

DESCRIBE GetCompanyDetails

commandinsql

2. Drop Multiple Columns

You can drop more than one column simultaneously by using the following statement.

ALTER TABLE GetCompanyDetails  
DROP COLUMN C_BusinessModel,  
DROP COLUMN C_Address;

Check the result by typing the following command.

DESCRIBE GetCompanyDetails;

companydetailsin sql

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!

Resources

Here are some useful related resources.


Similar Articles