MySQL – Drop Tables
Table of content
- The MySQL DROP TABLE statement
- Dropping Tables from a database
- The IF EXISTS clause
- Dropping Table Using a Client Program
”;
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.
Tables_in_tutorials |
---|
customers |
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 −
To drop a table from MySQL database through a PHP program, we need to execute the Drop statement using the mysqli function query() as
−
$sql="DROP TABLE Table_name"; $mysqli->query($sql);
To drop a table from MySQL database through a Node.js program, we need to execute the Drop statement using the query() function of the mysql2 library as −
sql = "DROP TABLE Table_name"; con.query(sql);
To drop a table from MySQL database through a Java program, we need to execute the Drop statement using the JDBC function executeUpdate() as −
String sql="DROP TABLE Table_name"; statement.execute(sql);
To drop a table from MySQL database through a Python program, we need to execute the Drop statement using the execute() function of the MySQL Connector/Python as −
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();
Output
The output obtained is as follows −
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); }); });
Output
The output produced is as follows −
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); } } }
Output
The output obtained is as shown below −
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()
Output
Following is the output of the above code −
Table ''tutorials_tbl_cloned'' is dropped successfully.
”;