13 - MySQL Drop

Drop in MySQL allow us to drop the table, database, Triggers, Indexes etc. It needs to have a drop privilege to do so.

Drop table or Database

It completely removes the table or database from the system that can’t be recoverafter dropping.Basic syntax of drop a table or database is:

DROP TABLE TableName;

The above DROP command removes the table in the working database selected by use or \u command.

DROP DATABASE DatabaseName;

DROP SCHEMA DatabaseName;

The above two commands are same for dropping the.Schema is a synonym for database. This DROP command can be run with or without selecting the working database.The Drop command shows 0 rows affected as a result of the command:

mysql> drop table t1;
Query OK, 0 rows affected (0.18 sec)

mysql> drop database aman;
Query OK, 0 rows affected (0.01 sec)
We can also drop the database through mysqladmin command without login into the mysql. The mysqladmin command to drop the database is as:
[aman@localhost]# mysqladmin -u root -p DROP DBNAME
Enter password:*****

This mysqladmin command prompts for the root password.

There is a keyword IF EXISTS which is used in DROP command to make the query runnable only if the database or the table exists. If this keyword is absent and the database or table that we are trying to drop is not available then the command gives an error:

mysql>
mysql> drop database good_works;
ERROR 1008 (HY000): Can't drop database 'good_works'; database doesn't exist
mysql>
mysql> drop database if exists good_works;
Query OK, 0 rows affected, 1 warning (0.00 sec)
Drop command is auto commit command, so use it carefully form the command line as well as from the code. In general only the root or admin user has the drop privilegeto remove the table or database from the MySQL server to avoid worst conditions.

Drop database command will remove all the data, temporary, trigger etc. files present in the data directory at the physical level present in the disk. Files include bak, dat, opt, trg, ibd, frm, trn, ndb etc.

NOTE:Drop database will not remove any temporary tables present in that database, although that tables are automatically deleted on the session expire.

DROP Index

Drop index drop the existing index from the table, the basic syntax of dropping the index from the table is:

DROP INDEX columnidx ON TableCompany;

Here columnidx is the index on the table TableCompany;

DROP Trigger

Drop trigger removes the existing trigger on any table, will discuss about the triggers in later chapters. The basic syntax is:

DROP TriggerTRIGGERNAME;

DROP Views

Drop views removes the existing views on any table, will discuss about the triggers in later chapters. The basic syntax is:

DROP view VIEWNAME;

DROP Procedure / Functions

Drop Procedure / Function removes the existing Procedure / Function in the database, will discuss about the procedures/functions in later chapters. The basic syntax is:

DROP ProcedurePROCEDURENAME;
OR
DROP Function FUNCTIONNAME;

All the drop commands run with the IF EXISTS keyword to avoid the error.

Like us on Facebook