TechBeamersTechBeamers
  • Learn ProgrammingLearn Programming
    • Python Programming
      • Python Basic
      • Python OOP
      • Python Pandas
      • Python PIP
      • Python Advanced
      • Python Selenium
    • Python Examples
    • Selenium Tutorials
      • Selenium with Java
      • Selenium with Python
    • Software Testing Tutorials
    • Java Programming
      • Java Basic
      • Java Flow Control
      • Java OOP
    • C Programming
    • Linux Commands
    • MySQL Commands
    • Agile in Software
    • AngularJS Guides
    • Android Tutorials
  • Interview PrepInterview Prep
    • SQL Interview Questions
    • Testing Interview Q&A
    • Python Interview Q&A
    • Selenium Interview Q&A
    • C Sharp Interview Q&A
    • PHP Interview Questions
    • Java Interview Questions
    • Web Development Q&A
  • Self AssessmentSelf Assessment
    • Python Test
    • Java Online Test
    • Selenium Quiz
    • Testing Quiz
    • HTML CSS Quiz
    • Shell Script Test
    • C/C++ Coding Test
Search
  • Python Multiline String
  • Python Multiline Comment
  • Python Iterate String
  • Python Dictionary
  • Python Lists
  • Python List Contains
  • Page Object Model
  • TestNG Annotations
  • Python Function Quiz
  • Python String Quiz
  • Python OOP Test
  • Java Spring Test
  • Java Collection Quiz
  • JavaScript Skill Test
  • Selenium Skill Test
  • Selenium Python Quiz
  • Shell Scripting Test
  • Latest Python Q&A
  • CSharp Coding Q&A
  • SQL Query Question
  • Top Selenium Q&A
  • Top QA Questions
  • Latest Testing Q&A
  • REST API Questions
  • Linux Interview Q&A
  • Shell Script Questions
© 2024 TechBeamers. All Rights Reserved.
Reading: MySQL DROP TABLE with Simple Examples
Font ResizerAa
TechBeamersTechBeamers
Font ResizerAa
  • Python
  • SQL
  • C
  • Java
  • Testing
  • Selenium
  • Agile Concepts Simplified
  • Linux
  • MySQL
  • Python Quizzes
  • Java Quiz
  • Testing Quiz
  • Shell Script Quiz
  • WebDev Interview
  • Python Basic
  • Python Examples
  • Python Advanced
  • Python OOP
  • Python Selenium
  • General Tech
Search
  • Programming Tutorials
    • Python Tutorial
    • Python Examples
    • Java Tutorial
    • C Tutorial
    • MySQL Tutorial
    • Selenium Tutorial
    • Testing Tutorial
  • Top Interview Q&A
    • SQL Interview
    • Web Dev Interview
  • Best Coding Quiz
    • Python Quizzes
    • Java Quiz
    • Testing Quiz
    • ShellScript Quiz
Follow US
© 2024 TechBeamers. All Rights Reserved.
MySQL Tutorial

MySQL DROP TABLE with Simple Examples

Last updated: May 22, 2023 12:52 pm
By Meenakshi Agarwal
Share
8 Min Read
MySQL DROP TABLE with Simple Examples
SHARE

This tutorial explains MySQL DROP TABLE statement which deletes the specified tables from the in-use database. We’ll describe several usages of this method with the help of simple examples.

Contents
SyntaxMySQL DROP TABLE Examples1. DROP TABLE IF EXIST2. DROP TABLE matching a pattern3. Full Example

1. DROP TABLE Syntax
2. DROP TABLE If Exist
3. DROP TABLE Matching Pattern
4. DROP TABLE Full Example

Let’s now read and understand each of the section one by one.

MySQL DROP TABLE statement

As stated initially, the DROP TABLE is a built-in MySQL statement which removes a specified table from the database. So, let’s first check the details and see how to use the DROP command. But first, create a sample table which we’ll use in our example.

CREATE TABLE BLOGS( blog_name VARCHAR(30) NOT NULL,
                    topic VARCHAR(10) NOT NULL);

Syntax

Below is the signature of this command:

DROP [TEMPORARY] TABLE [IF EXISTS] table1 [, table2] ...
[RESTRICT | CASCADE];

Below are the descriptions of the parameters in the above statement.

1. The DROP TABLE statement performs the delete operation on a table passed as the parameter. However, it can also remove the array of tables given as a comma-separated list.

2. The TEMPORARY option enables the deletion of temporary tables. This feature reduces the possibility of mistakes.

3. Another useful option is IF EXISTS, which prevents errors while trying to drop non-existing tables. MySQL will generate a NOTE after using it. We can check this by using SHOW WARNING.

4. Please note that the DROP TABLE command removes only tables along with their data. However, it does not revoke the user privileges from the deleted tables. Therefore, if you create another with the same name, it will use the existing rights. And it could be a security risk.

5. The options RESTRICT and CASCADE happen to be there for future use.

6. While removing a table, you need to make sure that it has DROP privileges on that.

MySQL DROP TABLE Examples

Let’s now check out how to use the DROP command with various examples.

1. DROP TABLE IF EXIST

We’ll remove the BLOGS table that we created at the beginning of this tutorial by using the CREATE TABLE command.

Besides, we’ll also delete a non-existent table and use the SHOW WARNING statement.

The below code to remove the BLOGS and non-existent tables are as follows:

DROP TABLE IF EXISTS BLOGS, nonexistent_table_name;
SHOW WARNINGS;

After running the command, if you access the database, then it will not show the deleted tables. Also, the SHOW WARNING statement will the following MySQL NOTE or warning:

Note	1051	Unknown table 'test.nonexistent_table_name'

Moreover, you can check if the deleted table exists or not by using the below MySQL statement.

SHOW TABLES LIKE 'BLOGS';

It will not show anything in the output if the table doesn’t exist.

2. DROP TABLE matching a pattern

Let’s assume we have multiple tables having names that begin with emp in our database. And we wish to remove all of them in a single shot quickly.

But, MySQL does not allow DROP TABLE LIKE statement to delete tables using the pattern matching.

DROP TABLE LIKE '%target_pattern%'

However, we can achieve the same a bit differently. Let’s discuss one such way here for your knowledge.

So, let’s create some emp* tables for the illustration purpose.

CREATE TABLE IF NOT EXISTS emp_a (
emp_id int(11) NOT NULL AUTO_INCREMENT,
PRIMARY KEY(emp_id)
);

CREATE TABLE IF NOT EXISTS emp_b LIKE emp_a;
CREATE TABLE IF NOT EXISTS emp_c LIKE emp_a;
CREATE TABLE IF NOT EXISTS emp_d LIKE emp_a;

So, after running the above commands, you will get four tables as emp_a, emp_b, emp_c, and emp_d with the same table schema.

Hence, we’ll now try to delete all emp* tables in a single shot. Check out the below steps:

Step-1)

Take two identifiers, one for database schema and one for the pattern to match:

-- Set table schema and pattern matching for tables
SET @db = 'emp_db';
SET @matcher = 'emp%';

Step-2)

Now, we have to form a DROP TABLE statement using the above two variables.

-- build a query (DROP TABLE table1, tables2...;)
SELECT CONCAT('DROP TABLE ',GROUP_CONCAT(CONCAT(@db,'.',table_name)),';')
INTO @droplike
FROM information_schema.tables
WHERE @db = database()
AND table_name LIKE @matcher;

Let’s simplify the above logic. The query notifies MySQL to access the information_schema. It has data for all tables and to combine all tables in the database @db matching the pattern @matcher. The GROUP_CONCAT() method produces a comma-delimited array of tables.

After all this, we can print the dynamic query to check if it works well:

-- display the dynamic query
SELECT @droplike;

We can confirm that it executes as intended. Next, use the prepared statement to run the query.

-- Run dynamic query
PREPARE dynst FROM @droplike;
EXECUTE dynst;
DEALLOCATE PREPARE dynst;

3. Full Example

Let’s assemble everything we’ve done so far.

USE test;

CREATE TABLE IF NOT EXISTS emp_a (
emp_id int(11) NOT NULL AUTO_INCREMENT,
PRIMARY KEY(emp_id)
);

CREATE TABLE IF NOT EXISTS emp_b LIKE emp_a;
CREATE TABLE IF NOT EXISTS emp_c LIKE emp_a;
CREATE TABLE IF NOT EXISTS emp_d LIKE emp_a;

-- Set table schema and pattern matching for tables
SET @db = 'test';
SET @matcher = 'emp%';

-- Build dynamic query (DROP TABLE table1, table2...;)
SELECT CONCAT('DROP TABLE ',GROUP_CONCAT(CONCAT(@db,'.',table_name)),';')
INTO @droplike
FROM information_schema.tables
WHERE @db = database()
AND table_name LIKE @matcher;

-- Print the dynamic query statement
SELECT @droplike;

-- Run the dynamic query
PREPARE dynStmt FROM @dropStmt;
EXECUTE dynStmt;
DEALLOCATE PREPARE dynStmt;

Finally, we have the full script that can drop more than one tables. You can supply a pattern and the schema in @matcher and @db identifiers. If you wish to automate the whole thing, then better write a stored procedure using the above script.

We hope that after wrapping up this tutorial, you should feel comfortable in using the MySQL DROP TABLE statement. However, you may practice more with examples to gain confidence.

Also, to learn SQL from scratch to depth, do read our step by step MySQL tutorial.

You Might Also Like

MySQL vs MongoDB Comparison

Concatenate Strings in an SQL Query With Examples

The Difference between UPSERT & Insert

SQL Programming Test in 2024

SQL Table Creation: The Missing Manual

Meenakshi Agarwal Avatar
By Meenakshi Agarwal
Follow:
Hi, I'm Meenakshi Agarwal. I have a Bachelor's degree in Computer Science and a Master's degree in Computer Applications. After spending over a decade in large MNCs, I gained extensive experience in programming, coding, software development, testing, and automation. Now, I share my knowledge through tutorials, quizzes, and interview questions on Python, Java, Selenium, SQL, and C# on my blog, TechBeamers.com.
Previous Article MySQL ABS Function with Simple Examples MySQL ABS Function with Simple Examples
Next Article MySQL CONCAT to Concatenate Strings MySQL CONCAT to Concatenate Strings

Popular Tutorials

SQL Interview Questions List
50 SQL Practice Questions for Good Results in Interview
SQL Interview Nov 01, 2016
Demo Websites You Need to Practice Selenium
7 Sites to Practice Selenium for Free in 2024
Selenium Tutorial Feb 08, 2016
SQL Exercises with Sample Table and Demo Data
SQL Exercises – Complex Queries
SQL Interview May 10, 2020
Java Coding Questions for Software Testers
15 Java Coding Questions for Testers
Selenium Tutorial Jun 17, 2016
30 Quick Python Programming Questions On List, Tuple & Dictionary
30 Python Programming Questions On List, Tuple, and Dictionary
Python Basic Python Tutorials Oct 07, 2016
//
Our tutorials are written by real people who’ve put in the time to research and test thoroughly. Whether you’re a beginner or a pro, our tutorials will guide you through everything you need to learn a programming language.

Top Coding Tips

  • PYTHON TIPS
  • PANDAS TIPSNew
  • DATA ANALYSIS TIPS
  • SELENIUM TIPS
  • C CODING TIPS
  • GDB DEBUG TIPS
  • SQL TIPS & TRICKS

Top Tutorials

  • PYTHON TUTORIAL FOR BEGINNERS
  • SELENIUM WEBDRIVER TUTORIAL
  • SELENIUM PYTHON TUTORIAL
  • SELENIUM DEMO WEBSITESHot
  • TESTNG TUTORIALS FOR BEGINNERS
  • PYTHON MULTITHREADING TUTORIAL
  • JAVA MULTITHREADING TUTORIAL

Sign Up for Our Newsletter

Subscribe to our newsletter to get our newest articles instantly!

Loading
TechBeamersTechBeamers
Follow US
© 2024 TechBeamers. All Rights Reserved.
  • About
  • Contact
  • Disclaimer
  • Privacy Policy
  • Terms of Use
TechBeamers Newsletter - Subscribe for Latest Updates
Join Us!

Subscribe to our newsletter and never miss the latest tech tutorials, quizzes, and tips.

Loading
Zero spam, Unsubscribe at any time.
x