MySQL - Drop Tables

The MySQL DROP TABLE statement

The MySQL DROP TABLE statement is a Data Definition Language (DDL) command that is used to remove a table's definition, and its data, indexes, triggers, constraints and permission specifications (if any). In simple terms, this statement will delete the entire table from the database.

However, while using DROP TABLE command, we need make note of the following −

• You should be very careful while using this command because once a table is deleted then all the information available in that table will also be lost forever.

• To drop a table in a database, one must require ALTER permission on the specified table and CONTROL permissions on the table schema.

• Even though it is a data definition language command, it is different from TRUNCATE TABLE statement as the DROP statement completely removes the table from the database.

Syntax

Following is the syntax of MySQL DROP TABLE statement −

DROP TABLE table_name ;

Dropping Tables from a database

To drop tables from a database, we need to use the MySQL DROP TABLE command. But we must make sure that the table exists in the database, before dropping it. If we try to drop a table that does not exist in the database, an error is raised.

Example

Let us start by creating a database TUTORIALS by executing below statement −

CREATE DATABASE TUTORIALS;

Using the following query, change the database to TUTORIALS −

USE TUTORIALS;

Create a a table CUSTOMERS using the following CREATE TABLE statement −

CREATE TABLE CUSTOMERS (
   ID INT NOT NULL,
   NAME VARCHAR(20) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS CHAR (25),
   SALARY DECIMAL (18, 2),
   PRIMARY KEY (ID)
);

To verify whether the above created is created in the TUTORIALS database or not, execute the following SHOW TABLES command −

SHOW TABLES IN TUTORIALS;

As we can see in the output below, the CUSTOMERS table is created in the TUTORIALS database.

Now, let us use the MySQL DROP TABLE statement to delete the above created CUSTOMERS table −

DROP TABLE CUSTOMERS;

Output

Executing the query above will produce the following output −

Query OK, 0 rows affected (0.02 sec)

Verification

Since we have removed the table CUSTOMERS, if we try to verify the list of tables again, using the "SHOW TABLES" query it will display an Empty Set as shown below −

Empty set (0.00 sec)

The IF EXISTS clause

Instead of constantly checking whether a table exists or not in a database before deleting it, we can use the IF EXISTS clause along with the DROP TABLE statement.

When we specify this clause in the DROP TABLE query, it will automatically verify if the table exists in the current database. If it exists, it will then delete the table. If the table doesn't exist, the query will be ignored.

Syntax

Following is the basic syntax of DROP TABLE IF EXISTS statement −

DROP TABLE [IF EXISTS] table_name;

Example

Here, we are using just DROP TABLE statement to drop the CUSTOMERS table which has been deleted already in the previous example.

DROP TABLE CUSTOMERS;

Output

Since, we are not using IF EXISTS with the DROP TABLE command, it will display an error as follows −

ERROR 1051 (42S02): Unknown table 'tutorials.customers'

Example

If we try to drop CUSTOMERS that does not exist in the database, using the IF EXISTS clause, the query will be ignored without issuing any error −

DROP TABLE IF EXISTS CUSTOMERS;

Executing the query above will produce the following output −

Query OK, 0 rows affected, 1 warning (0.01 sec)

Dropping Table Using a Client Program

In addition to dropping a table from MySQL Database using the MySQL query, we can also perform the DROP TABLE operation on a table using a client program.

Syntax

Following are the syntaxes to drop a table from MySQL in various programming languages −

$sql="DROP TABLE Table_name";
$mysqli->query($sql);

sql = "DROP TABLE Table_name";
con.query(sql);

String sql="DROP TABLE Table_name";
statement.execute(sql);

sql="DROP TABLE Table_name";
cursorObj.execute(sql);

Example

Following are the programs −

$dbhost = 'localhost';
$dbuser = 'root';
$dbpass = 'root@123';
$dbname = 'TUTORIALS';
$mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbname);

if($mysqli->connect_errno ) {
   printf("Connect failed: %s<br />", $mysqli->connect_error);
   exit();
}
printf('Connected successfully.<br />');

if ($mysqli->query("Drop Table tutorials_tbl")) {
   printf("Table tutorials_tbl dropped successfully.<br />");
}
if ($mysqli->errno) {
   printf("Could not drop table: %s<br />", $mysqli->error);
}

$mysqli->close();

Connected successfully.
Table tutorials_tbl dropped successfully.

var mysql = require('mysql2');
var con = mysql.createConnection({
    host: "localhost",
    user: "root",
    password: "Nr5a0204@123"
});

  //Connecting to MySQL
  con.connect(function (err) {
  if (err) throw err;
  console.log("Connected!");
  console.log("--------------------------");

  //Selecting a Database
  sql = "USE TUTORIALS"
  con.query(sql);

  sql = "DROP TABLE SalesSummary;"
  con.query(sql, function(err, result){
    if (err) throw err
    console.log(result);
  });
});

Connected!
--------------------------
ResultSetHeader {
  fieldCount: 0,
  affectedRows: 0,
  insertId: 0,
  info: '',
  serverStatus: 2,
  warningStatus: 0,
  changedRows: 0
}

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class DropTable {
    public static void main(String[] args) {
       String url = "jdbc:mysql://localhost:3306/TUTORIALS";
       String username = "root";
       String password = "password";
       try {
          Class.forName("com.mysql.cj.jdbc.Driver");
          Connection connection = DriverManager.getConnection(url, username, password);
          Statement statement = connection.createStatement();
          System.out.println("Connected successfully...!");

          //Drop a table....
          String sql = "DROP TABLE customer";
          statement.execute(sql);
          System.out.println("Table Dropped successfully...!");

          connection.close();
       } catch (Exception e) {
          System.out.println(e);
       }
    }
}

Connected successfully...!
Table Dropped successfully...!

import mysql.connector
#establishing the connection
connection = mysql.connector.connect(
    host='localhost',
    user='root',
    password='password',
    database='tut'
)
table_name = 'tutorials_tbl_cloned'
#Creating a cursor object 
cursorObj = connection.cursor()
drop_table_query = f"DROP TABLE {table_name}"
cursorObj.execute(drop_table_query)
print(f"Table '{table_name}' is dropped successfully.")
cursorObj.close()
connection.close()

Table 'tutorials_tbl_cloned' is dropped successfully.