In Focus

DROP in MySQL

This article represents how to work the drop statement in MySQL 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 very easy to drop an existing MySQL table. But we need to be very careful while deleting any existing table because when data lost will not be recovered after deleting a table.
Here we have to drop a database on the server.

Synatx
Drop Database Database name

For Example
mysql> drop database world;

img 1.gif
 

The DROP INDEX Statement

The DROP INDEX statement is used to delete an index in a table.

Syntax
ALTER TABLE table_name DROP INDEX index_name

For Example
mysql> Alter table HeadOfState ADD PRIMARY KEY (ID),ADD INDEX (LastName,FirstName);

mysql> ALTER TABLE HeadOfState DROP INDEX LastName;

img alter index.gif


The DROP TABLE Statement
DROP TABLE removes one or more tables. 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 delete a table.

Synatx
DROP TABLE table_name

Example
mysql> DROP table Employee;

drop table.gif

Altering and Dropping Tables
Example
Drop table Stock;

CREATE TABLE Stock
(
ID SMALLINT
);

mysql> Describe Stock;

mysql> ALTER TABLE Stock
      -> ADD COLUMN Quantity SMALLINT UNSIGNED NOT NULL,
     -> MODIFY ID SMALLINT UNSIGNED NOT NULL,
     -> ADD PRIMARY KEY (ID);

mysql> Describe Stock;

img alter and drop table.gif


mysql>ALTER TABLE Stock;

mysql>DROP COLUMN Quantity,
      ->DROP PRIMARY KEY;

mysql> Describe Stock;

img alter and drop table2.gif


Note
All table data and the table definition are removed, so be careful with this statement!

Resources 

Here are some useful related resources: