EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Free Courses
  • Certification Courses
  • 360+ Courses All in One Bundle
  • Login
Home Data Science Data Science Tutorials MySQL Tutorial MySQL DROP TABLE
Secondary Sidebar
MySQL Tutorial
  • Advanced
    • MySQL Formatter
    • MySQL TINYINT
    • MySQL Grant All Privileges
    • MySQL DROP TABLE
    • MySQL rename database
    • MySQL Flush Privileges
    • MySQL super Privilege
    • MySQL Character Set
    • MySQL Log File
    • MySQL Flush Log
    • Grant Privileges MySQL
    • MySQL WHILE LOOP
    • IF Statement in MySQL
    • MySQL CASE Statement
    • MySQL IF Function
    • MySQL IF EXISTS
    • MySQL UUID
    • Views in MySQL
    • MySQL Replication
    • MySQL Partition
    • Toad for MySQL
    • Navicat for MySQL
    • MySQL AES_Encrypt
    • MySQL Performance Tuning
    • MySQL Transaction
    • MySQL? sort_buffer_size
    • MySQL? Sync
    • MySQL? Query Cache
    • MySQL Collation
    • MySQL ODBC Driver
    • MySQL Partitioning
    • MySQL InnoDB
    • MySQL Float vs Decimal
    • MySQL Union vs Union All
  • Basic
    • MySQL floor
    • MySQL DESCRIBE table
    • MySQL encryption
    • Introduction to MySQL
    • Is SQL Server a Database?
    • What is MySQL
    • Is MySQL Programming Language
    • MySQL Server
    • MySQL AB
    • MySQL Community Server
    • How To Install MySQL
    • MySQL Versions
    • MySQL OpenSource
    • MySQL GUI Tools
    • MySQL Grant
    • MySQL Error 1064
    • MySQL Drop Database
    • MySQL not equal
    • MySQL SELECT INTO Variable
    • MySQL Commands
    • MySQL Operators
    • What is MySQL Schema
    • Wildcards in MySQL
    • MySQL Constraints
    • MySQL Administration
    • MySQL Data Type
    • MYSQL COMMIT
    • MySQL FORMAT
    • Timestamp to Date in MySQL
    • MySQL DATEDIFF
    • MySQL?Incremental Backup
    • MySQL JSON Data Type
    • MySQL ENUM
    • MySQL Default Port
    • Cheat Sheet MySQL
  • Queries
    • MySQL Queries
    • MySQL Query Commands
    • SELECT in MySQL
    • MySQL INSERT IGNORE
    • MySQL having
    • ORDER BY in MySQL
    • MySQL Cheat Sheet
    • MySQL ORDER BY Random
    • MySQL ORDER BY DESC
    • MySQL GROUP BY
    • MySQL GROUP BY Count
    • MySQL GROUP BY month
    • MySQL WHERE Clause
    • MySQL WITH
    • MySQL FETCH
    • MySQL DDL
    • MySQL DML
    • MySQL WHERE IN Array
    • MySQL Fetch Array
    • MySQL ISNULL
    • MySQL Index Types
    • Mysql? Export Schema
    • Amazon RDS for MySQL
    • MySQL greatest
  • Database
    • What is Data Modeling
    • What is Data Processing
    • DBMS Architecture
    • DBMS Keys
    • Careers in Database Administration
    • What is MySQL Database
    • MySQL Relational Database
    • How to Connect Database to MySQL
    • MySQL Database Repair
    • RDBMS Interview Questions
    • DBMS Interview Questions
  • Functions
    • MySQL Function
    • MySQL Aggregate Function
    • MySQL String functions
    • MySQL Date Functions
    • MySQL Window Functions
    • MySQL Math Functions
    • MySQL Boolean
    • Cursor in MySQL
    • Condition in MySQL
    • MySQL BETWEEN
    • Insert in MySQL
    • MySQL IFNULL()
    • MySQL TIMESTAMPDIFF()
    • MySQL COALESCE()
    • MySQL count()
    • MIN() in MySQL
    • MySQL Numeric
    • MySQL field()
    • MySQL FIND_IN_SET()
    • MySQL avg()
    • MySQL MAX() Function
    • MySQL BIN()
    • MySQL Concat
    • MySQL DECODE()
    • MySQL REGEXP_REPLACE()
    • MySQL Asynchronous
    • MySQL innodb_buffer_pool_size
    • MySQL key_buffer_size
    • MySQL TRUNCATE()
    • MySQL ROW_NUMBER()
    • NOT in MySQL
    • MySQL IN Operator
    • LIKE in MySQL
    • ANY in MySQL
    • MySQL NOT IN
    • MySQL CHECK Constraint
    • MySQL DISTINCT
    • MySQL ALL
    • MySQL Union
    • MySQL UNION ALL
    • MySQL EXISTS
    • MySQL ON DELETE CASCADE
    • MySQL REGEXP
    • MySQL Index
    • MySQL Add Index
    • MySQL REINDEX
    • MySQL UNIQUE INDEX
    • MySQL Clustered Index
    • MySQL? InnoDB Cluster
    • Table in MySQL
    • ALTER TABLE MySQL
    • MySQL Temporary Table
    • MySQL Clone Table
    • MySQL Repair Table
    • MySQL Lock Table
    • MySQL Optimize Table
    • TRUNCATE TABLE MySQL
    • MySQL Table Dump
    • MySQL Update Set
    • MySQL ALTER TABLE Add Column
    • MySQL RANK()
    • MySQL CTE
    • MySQL LAG()
    • MySQL GROUP_CONCAT()
    • MySQL EXTRACT()
    • MySQL REPLACE
    • MySQL AUTO_INCREMENT
    • MySQL SYSDATE()
    • MySQL NULLIF()
    • MySQL Substring
    • MySQL SUBSTRING_INDEX()
    • MySQL LOWERCASE
    • MySQL Row
    • MySQL NOW
    • MySQL CEIL
    • MySQL Alias
    • MySQL Trigger
    • MySQL SHOW Triggers
    • MySQL UPDATE Trigger
    • MySQL DELETE Trigger
    • MySQL AFTER UPDATE Trigger
    • MySQL Stored Procedure
    • ROLLUP in MySQL
    • MySQL? INSTR()
    • MySQL Subquery
    • MySQL Timestamp
    • MySQL? Hour()
    • MySQL MOD()
    • MySQL DATE_FORMAT()
    • ALTER Column in MySQL
    • MySQL Rename Column
    • MySQL Interval
    • MySQL CURDATE
    • MySQL BIT
    • MySQL Binlog
    • MySQL Average
    • MySQL TEXT
    • MySQL SHOW
    • MySQL Offset
    • MySQL Timezone
    • mysql_real_escape_string
    • MySQL Datetime
    • MySQL DATE_SUB()
    • MySQL FULLTEXT
    • MySQL DATE_ADD()
    • MySQL sum()
    • MySQL Merge
    • MySQL BigInt
    • MySQL ROUND
    • MySQL VARCHAR
    • MySQL Decimal
    • MySQL Limit
    • MySQL today()
    • MySQL WEEKDAY
    • MySQL Split
    • MySQL Create Function
    • MySQL BLOB
    • MySQL encode()
    • MySQL Primary Key
    • MySQL Foreign Key
    • Unique Key in MySQL
    • MySQL Drop Foreign Key
    • MySQL DROP TRIGGER
    • MYSQL Database
    • Delete Database MySQL
    • MySQL Root
    • MySQL Root Password
    • MySQL Client
    • MySQL Users
    • MySQL?User Permissions
    • MySQL add user
    • MySQL List User
    • MySQL Show Users
    • MySQL User Password
    • MySQL?Cardinality
    • MySQL Workbench
    • MySQL Backup
    • MySQL REVOKE
    • MySQL Dump
    • MySQL Cluster
    • MySQL Partitioning
    • MySQL Full Text Search
    • MySQL Admin Tool
    • MySQL Export Database
    • MySQL Export to CSV
  • Joins
    • Joins in MySQL
    • MySQL Outer Join
    • Left Outer Join in MySQL
    • MySQL Self Join
    • Natural Join in MySQL
    • MySQL DELETE JOIN
    • MySQL Update Join
    • MySQL Cross Join
  • Interview Questions
    • MySQL Interview Questions

Related Courses

MS SQL Certification Courses

Oracle Certification Courses

PL/SQL Certification Courses

MySQL DROP TABLE

MySQL DROP TABLE

Definition of MySQL DROP TABLE

MySQL DROP TABLE is a MySQL query statement that is responsible to drop a MySQL table existing in the database server. This is a MySQL command which is required to be used if we want to delete any unwanted table or for any reason, a certain table is built up in the database.

Suppose, sometimes to manage and maintain the database servers we need to repair or recover the database tables and records from time to time to make the proper and effective access of databases. For this, it might be essential to remove any table and adjust the records in the database. We will use the DROP table statement on the server and execute it to make proper running and control administrative functions on the MySQL server.

Syntax:

We have the following elementary syntax code for the DROP Table query in MySQL:

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

DROP TABLE [IF EXISTS] TableName [,TableName1, TableName2,….,] [CASCADE | RESTRICT];

This query statement will permanently remove the database table and its information from the MySQL server.

  • Here, IF EXISTS keyword denotes a condition if a table is available in the database or not.
  • This is because if you remove a table that is not present in the database using IF EXISTS option then, the server will produce a NOTE that can be accessed by the statement SHOW WARNINGS.
  • You can also use a TEMPORARY keyword to make sure you remove the temporary database table, not the non-temporary one.
  • Again, note that the DROP table command will just delete tables but it will not drop the particular user privileges linked with the specific table. Suppose if new create a new table has the identical name as that of the removed one then, the present privileges will be provided to it by MySQL that may position a security threat to the server.
  • Further, if we want to delete more than one table implemented with a DROP table query just once then, we need to use other table names which are parted by comma(,). Also, CASCADE or RESTRICT options in the syntax are provided that do not add any impact on the query and are specially reserved for future uses of MySQL versions.
  • In order to delete any table and execute the DROP query, the user must have DROP rights for the database table that we want to drop.

How does MySQL Drop Table Work?

Suppose, we have a table in our database named Persons and we want to implement the MySQL DROP TABLE command on this table. We have written the following SQL query to be executed and perform deletion on the table Persons:

DROP TABLE Persons;

Let us consider if we want to delete all the database tables then, in MySQL we apply the DROP DATABASE command that will remove all tables and make it empty. But if we want to remove any database table then we use the DROP TABLE statement that permanently drops the table with its records.

The MySQL statements DROP and TRUNCATE have the same functionality that is used for the deletion process. But they are different in the form of working, syntax, and result.

For truncating a table we have the succeeding syntax structure as follows:

TRUNCATE TABLE TableName;

The DROP Table query deletes the whole table along with its records but the TRUNCATE Table query will delete only the data inside the database table, not the whole table. Remember this difference while using the MySQL queries DROP TABLE and TRUNCATE TABLE properly.

Examples of Drop Table in MySQL

Let us elaborate on some examples using the MySQL DROP TABLE query statement:

Example #1

Example to use DROP TABLE query to remove a single database table: For implementing the MySQL statement first we need to create a table in the database using the CREATE TABLE statement in the MySQL server. So, let us build a table for sample:

CREATE TABLE Employees (EmpID INT PRIMARY KEY AUTO_INCREMENT, EmpName VARCHAR (255) NOT NULL, EmpAddress VARCHAR(255) NOT NULL, Phone INT NOT NULL, JoinDate DATETIME NOT NULL);

Also, let us insert some records in the table created using the INSERT command in the MySQL database:

INSERT INTO Employees(EmpID,EmpName,EmpAddress,Phone,JoinDate) VALUES (‘10’,’Rita Shah’,’Rampur’,’897678908’,’2020-05-25 10:04:30’);

To view the table records we will apply the SELECT statement below:

SELECT * FROM Employees;

Output:

MySQL DROP TABLE-1.1

Now, for dropping the table we will use the following query:

DROP TABLE Employees;

Output:

MySQL DROP TABLE-1.2

Example #2

Example to use DROP TABLE query to remove more than one database table: For implementing this MySQL statement, again let us create another table on the database server. So, let us build other tables for showing the dropping of multiple tables:

CREATE TABLE Persons (PersonID INT PRIMARY KEY AUTO_INCREMENT, LastName VARCHAR (255) NOT NULL, FirstName VARCHAR (255) NOT NULL, Address VARCHAR(255) NOT NULL, City VARCHAR(255) NOT NULL, JoinDate DATETIME NOT NULL);

Also, let us insert some records in the table created using the INSERT command in the MySQL database:

INSERT INTO Persons (PersonID,LastName,FirstName,Address,City,JoinDate) VALUES (‘1’,’Sharma’,‘Nikhil’,’Dhanbad,Jharkhand’,’Dhanbad’,’2020-01-09 10:05:00’);

All in One Data Science Bundle(360+ Courses, 50+ projects)
Python TutorialMachine LearningAWSArtificial Intelligence
TableauR ProgrammingPowerBIDeep Learning
Price
View Courses
360+ Online Courses | 50+ projects | 1500+ Hours | Verifiable Certificates | Lifetime Access
4.7 (86,408 ratings)

To view the table records we will apply the SELECT statement below:

SELECT * FROM Persons;

Output:

MySQL DROP TABLE-2.1

Now, for dropping the two tables separated by comma we will use the following query and also recreate the previous table Employees:

DROP TABLE Persons,Employees;

Output:

MySQL DROP TABLE-2.2

Example #3

Example to use DROP TABLE query to remove a non-existing database table: We are considering the statement in MySQL to attempt executing DROP TABLE query for the table that is not available in the database server.

DROP TABLE Products;

When the above statement is queried and MySQL runs the code then, it will generate the following error.

Output:

Output-3.1

Thus, if we use the MySQL option IF EXISTS with the above query like the below statement:

DROP TABLE IF EXISTS Products;

Then, MySQL will issue a warning instead of an error on processing.

Output:

Output-3.2

To view the warning or read the error we can use the SHOW WARNINGS statement in MySQL:

SHOW WARNINGS;

Output:

Output-3.3

Conclusion

In MySQL, the DROP Table statement helps to remove the table present in the database server that will also delete the information related to it. Therefore, we should be careful before deleting any existing table because it may result in dropping any important data or info that is kept in the database server.

Recommended Articles

This is a guide to MySQL DROP TABLE. Here we also discuss the definition and how does mysql drop table works? along with different examples and its code implementation. You may also have a look at the following articles to learn more –

  1. MySQL TRUNCATE()
  2. MySQL INSTR()
  3. MySQL Constraints
  4. MySQL Root
Popular Course in this category
MySQL Training Program (12 Courses, 10 Projects)
  12 Online Courses |  10 Hands-on Projects |  92+ Hours |  Verifiable Certificate of Completion
4.5
Price

View Course

Related Courses

MS SQL Training (16 Courses, 11+ Projects)4.9
Oracle Training (14 Courses, 8+ Projects)4.8
PL SQL Training (4 Courses, 2+ Projects)4.7
0 Shares
Share
Tweet
Share
Primary Sidebar
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Database Management
  • Machine Learning
  • All Tutorials
Certification Courses
  • All Courses
  • Data Science Course - All in One Bundle
  • Machine Learning Course
  • Hadoop Certification Training
  • Cloud Computing Training Course
  • R Programming Course
  • AWS Training Course
  • SAS Training Course

ISO 10004:2018 & ISO 9001:2015 Certified

© 2022 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA
Free Data Science Course

SPSS, Data visualization with Python, Matplotlib Library, Seaborn Package

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more