MySQL DROP

Last update on August 19 2022 21:51:22 (UTC/GMT +8 hours)

DROP TABLE

In MySQL, DROP TABLE command removes one or more tables from an existing database.

The user who is using the DROP command, must have DROP privilege for each table(s) he wants to drop. The command removes all the data and table definition from the database.

Syntax:

DROP [TEMPORARY] TABLE [IF EXISTS]
    table_name [, table_name] ...
    [RESTRICT | CASCADE]

Argument:

Name	Description
table name	Table to be removed.
IF EXIST	The IF EXIST optional clause can be used to avoid the error message occurred, when a specified table in the table list passed as argument, does not exist in the database.
RESTRICT and CASCADE	The RESTRICT and CASCADE options can be used to make porting easier.
TEMPORARY	This clause with statement drops only TEMPORARY tables. A TEMPORARY table is visible only to the session that created by the user. Using TEMPORARY is a good practice to ensure that accidentally you do not drop such a table which is important to you.

Example:

If you want to drop the table newauthor, the following sql can be used.


DROP TABLE IF EXISTS  newauthor;

The above MySQL statement above will remove the 'newauthor' table with all data from the existing database.

MySQL DROP all TABLES or MySQL DROP DATABASE

If you want to drop all the tables from a database you need to use DROP DATABASE sql command to drops all tables in the database or empty the database. But you need to be very careful with this statement, and to use DROP DATABASE, you need the DROP privilege on the database

Syntax:

DROP {DATABASE | SCHEMA} [IF EXISTS] db_name

The clause IF EXISTS is used to prevent the display of an error message when the specific database does not exist which is going to be dropped.

The DROP DATABASE statement removes those files and directories that created by MySQL itself during the creation of database. The extension of files are - .BAK, .DAT, .HSH, .MRG, .MYD, .MYI, .TRG, .TRN, .db, .frm, .ibd, .ndb and .par .

Here are our databases.

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| bmcnetbank         |
| bupf               |
| employee           |
| empoloyee          |
| mucemppf           |
| mysql              |
| performance_schema |
| sakila             |
| tempdatabase       |
| test               |
| world              |
+--------------------+
12 rows in set (0.00 sec)

We want to drop all the tables from the database tempdatabase.

Here the tables of the database tempdatabase.

mysql> SHOW TABLES;
+------------------------+
| Tables_in_tempdatabase |
+------------------------+
| table1                 |
| table2                 |
+------------------------+
2 rows in set (0.00 sec)

Now you can DROP all the tables in a database as well as the database. You can recreate the database again. Before you drop the database, start the MySQL server, then go the command prompt, then entering the password connect the server, in the following 'mysql>' prompt enter the statement.

mysql> DROP DATABASE tempdatabase;
Query OK, 2 rows affected (0.27 sec)

After DROP the database here is the remaining databases.

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| bmcnetbank         |
| bupf               |
| employee           |
| empoloyee          |
| mucemppf           |
| mysql              |
| performance_schema |
| sakila             |
| test               |
| world              |
+--------------------+
12 rows in set (0.00 sec)

MySQL DROP multiple TABLES

Here the tables of the database tempdatabase.

mysql> SHOW tables;
+------------------------+
| Tables_in_tempdatabase |
+------------------------+
| table1                 |
| table2                 |
| table3                 |
| table4                 |
| table5                 |
+------------------------+
5 rows in set (0.10 sec)

If you want to delete the table table2,table4 and table5 from the database tempdatabase, the following sql can be used.

mysql> DROP TABLE table2,table4,table5;
Query OK, 0 rows affected (0.24 sec)

mysql> SHOW TABLES;
+------------------------+
| Tables_in_tempdatabase |
+------------------------+
| table1                 |
| table3                 |
+------------------------+
2 rows in set (0.00 sec)

MySQL DROP column

If MySQL ALTER command is used with DROP following the table column name, that column will be deleted from the table.

Example:

If we want to remove cate_descrip column from newcate table, the following statement can be used.


ALTER TABLE  newcate DROP cate_descrip;

MySQL DROP multiple columns

Here is the table table1.

mysql> DESCRIBE table1;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| col2  | varchar(15) | YES  |     | NULL    |       |
| col4  | int(5)      | YES  |     | NULL    |       |
| col1  | int(5)      | YES  |     | NULL    |       |
| col3  | int(10)     | YES  |     | NULL    |       |
| col5  | int(5)      | YES  |     | NULL    |       |
| col6  | int(5)      | YES  |     | NULL    |       |
| col7  | int(5)      | YES  |     | NULL    |       |
| col8  | int(5)      | YES  |     | NULL    |       |
| col9  | int(10)     | YES  |     | NULL    |       |
| col10 | int(5)      | YES  |     | NULL    |       |
| col11 | int(5)      | YES  |     | NULL    |       |
| col12 | int(5)      | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
12 rows in set (0.01 sec)

If you want to remove the column col1, col11 and col12 from the table table1, the following sql statement can be used.

mysql> ALTER TABLE table1 drop col1, drop col11, drop col12;
Query OK, 0 rows affected (0.20 sec)
Records: 0  Duplicates: 0  Warnings: 0

Here is the structure of the table after removing columns.

mysql> describe table1;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| col2  | varchar(15) | YES  |     | NULL    |       |
| col4  | int(5)      | YES  |     | NULL    |       |
| col3  | int(10)     | YES  |     | NULL    |       |
| col5  | int(5)      | YES  |     | NULL    |       |
| col6  | int(5)      | YES  |     | NULL    |       |
| col7  | int(5)      | YES  |     | NULL    |       |
| col8  | int(5)      | YES  |     | NULL    |       |
| col9  | int(10)     | YES  |     | NULL    |       |
| col10 | int(5)      | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
9 rows in set (0.01 sec)

MySQL DROP VIEW

MySQL DROP VIEW command is used to drop a view. You must have the DROP privilege for each view. If the view name specified into the DROP VIEW statement does not exists MySQL returns an error indicating by name.

Syntax:

DROP VIEW [IF EXISTS]
    view_name [, view_name] ...
    [RESTRICT | CASCADE]

Argument:

Name	Description
view name	View to be deleted.
IF EXISTS	The clause IF EXISTS prevents to display an error message from the operation happening for views does not exist.
RESTRICT, CASCADE	RESTRICT and CASCADE, if given, are parsed and ignored.

If you want to drop the view view_bookmast, the following sql statement can be used.

DROP VIEW view_bookmast;

MySQL DROP INDEX

MySQL DROP INDEX command removes an index from a table. This statement is mapped to an ALTER TABLE statement to drop the index.

Syntax:

DROP INDEX index_name ON table_name
    [algorithm_option | lock_option] ...

algorithm_option:
    ALGORITHM [=] {DEFAULT|INPLACE|COPY}

lock_option:
    LOCK [=] {DEFAULT|NONE|SHARED|EXCLUSIVE}

Arguments:

Name	Description
index name	Index to be removed.
table name	Table the index belongs to.
ALGORITHM [=] {DEFAULT\|INPLACE\|COPY}	DEFAULT - You needed this when you are applying ALTER TABLE in earlier versions of MySQL (< 5.6) while altering a table online. This method was used to use a temporary table while altering a table. The ALGORITHM=INPLACE continue the operation inside the InnoDB storage engines by using the in-place technique, and fail which are not support this features with an error. COPY - is the same a specifying no ALGORITHM clause at all.
LOCK [=] {DEFAULT\|NONE\|SHARED\|EXCLUSIVE}	DEFAULT - Permit a series of coincident events i.e. reads and writes when supported. Otherwise permit concurrent reads when supported else enforce exclusive access. NONE - When supported, permit concurrent reads and writes else return an error message. SHARED - When supported, allow concurrent reads but restrict writes. Remember that writes will be blocked even if concurrent writes are supported by the storage engine for the given ALGORITHM clause (if any) and ALTER TABLE operation. When concurrent reads are not supported an error message will be returned. EXCLUSIVE - This enforce exclusive access. It happens even if concurrent reads/writes are supported by the storage engine for the given ALGORITHM clause (if any) and ALTER TABLE operation.

Example:

If you want to drop the index newautid of newauthor table, the following sql statement can be used.


DROP INDEX newautid ON newauthor;

MySQL DROP multiple INDEX

Here are the indexes for the table table1.

mysql> SHOW INDEX FROM table1;
+--------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table  | Non_unique | Key_name     | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| table1 |          1 | index_col2   |            1 | col2        | A         |           0 |     NULL | NULL   | YES  | BTREE      |         |               |
| table1 |          1 | index_col5   |            1 | col5        | A         |           0 |     NULL | NULL   | YES  | BTREE      |         |               |
| table1 |          1 | index_col7   |            1 | col7        | A         |           0 |     NULL | NULL   | YES  | BTREE      |         |               |
| table1 |          1 | index_col910 |            1 | col9        | A         |           0 |     NULL | NULL   | YES  | BTREE      |         |               |
| table1 |          1 | index_col910 |            2 | col10       | A         |           0 |     NULL | NULL   | YES  | BTREE      |         |               |
+--------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
5 rows in set (0.00 sec)

If you want to drop the indexes index_col2 and index_col5, the following sql statement can be used.

mysql> ALTER TABLE table1 DROP INDEX index_col2, DROP INDEX index_col5;
Query OK, 0 rows affected (0.09 sec)
Records: 0  Duplicates: 0  Warnings: 0

After drop the indexes from the table table1, here is the indexes for the table1

mysql> show index from table1;
+--------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table  | Non_unique | Key_name     | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| table1 |          1 | index_col7   |            1 | col7        | A         |           0 |     NULL | NULL   | YES  | BTREE      |         |               |
| table1 |          1 | index_col910 |            1 | col9        | A         |           0 |     NULL | NULL   | YES  | BTREE      |         |               |
| table1 |          1 | index_col910 |            2 | col10       | A         |           0 |     NULL | NULL   | YES  | BTREE      |         |               |
+--------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set (0.00 sec)

MySQL DROP FUNCTION , PROCEDURE

Click here to see DROP FUNCTION, PROCEDURE.

MySQL DROP TRIGGER

Click here to see DROP TRIGGER.

MySQL DROP EVENT

The DROP EVENT drops the event.

Here we are creating an event.

mysql> CREATE EVENT testevent
    ->     ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 MINUTE
    ->     DO
    ->       UPDATE tempdatabase.table3 SET col1 = col1 + 1;
Query OK, 0 rows affected (0.01 sec)

The above statement creates an event named testevent and it executes once, after one minute following its creation, by running an SQL statement it will increments the value of the column col1 of tempdatabse.table3 by 1.

Syntax :

DROP EVENT [IF EXISTS] event_name

Arguments:

Name	Description
event_name	Name of the event
IF EXISTS	If the event does not exist, the error ERROR 1517 (HY000): Unknown event 'event_name' results will display. To prevent this error message for nonexistent events the IF EXISTS clause can be used.

I you want to remove the event testevent the following sql statement can be used.

mysql> DROP EVENT IF EXISTS testevent;
Query OK, 0 rows affected (0.00 sec)

MySQL DROP PRIMARY KEY

Here is the structure of the sample table.

mysql> DESCRIBE table1;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| col2  | varchar(15) | NO   | PRI |         |       |
| col4  | int(5)      | YES  | MUL | NULL    |       |
| col3  | int(10)     | YES  | MUL | NULL    |       |
| col5  | int(5)      | YES  | MUL | NULL    |       |
| col6  | int(5)      | YES  | MUL | NULL    |       |
| col7  | int(5)      | YES  | MUL | NULL    |       |
| col8  | int(5)      | YES  |     | NULL    |       |
| col9  | int(10)     | YES  | MUL | NULL    |       |
| col10 | int(5)      | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
9 rows in set (0.01 sec)

Here in the above table structure of table1 shows col2 is the PRIMARY KEY, which is indicated by red color.

If you want to drop the PRIMARY KEY on col2, the following sql can be used.

mysql> DROP INDEX `PRIMARY` ON table1;
Query OK, 0 rows affected (0.81 sec)
Records: 0  Duplicates: 0  Warnings: 0

Now look the structure of the table table1 again.

mysql> describe table1;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| col2  | varchar(15) | NO   | MUL |         |       |
| col4  | int(5)      | YES  | MUL | NULL    |       |
| col3  | int(10)     | YES  | MUL | NULL    |       |
| col5  | int(5)      | YES  | MUL | NULL    |       |
| col6  | int(5)      | YES  | MUL | NULL    |       |
| col7  | int(5)      | YES  | MUL | NULL    |       |
| col8  | int(5)      | YES  |     | NULL    |       |
| col9  | int(10)     | YES  | MUL | NULL    |       |
| col10 | int(5)      | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
9 rows in set (0.01 sec)

Previous: MySQL Show Commands
Next: Exporting and importing data between mysql and microsoft excel