DROP Tables

The DROP statement is used to delete database objects such as tables, databases, indexes, etc. This chapter introduces how to use the DROP statement.

Drop Tables

Basic Syntax

DROP TABLE table_name;

Drop Single Table

-- Drop table
DROP TABLE users;

-- Drop if exists
DROP TABLE IF EXISTS users;

Drop Multiple Tables

-- Drop multiple tables at once
DROP TABLE users, orders, products;

Drop Database

-- Drop database
DROP DATABASE mydb;

-- Drop if exists
DROP DATABASE IF EXISTS mydb;

DROP vs TRUNCATE vs DELETE

Operation	DROP	TRUNCATE	DELETE
Removes	Structure+Data	Data only	Data only
Speed	Fast	Very fast	Slow
Rollback	No	No	Yes
WHERE	No	No	Yes
Triggers	No	No	Yes

Drop Index

-- Drop index
DROP INDEX idx_name ON users;

-- MySQL syntax
ALTER TABLE users DROP INDEX idx_name;

Drop View

-- Drop view
DROP VIEW view_name;

-- Drop if exists
DROP VIEW IF EXISTS view_name;

Practical Examples

Clean Temporary Tables

-- Drop temporary table
DROP TEMPORARY TABLE IF EXISTS temp_users;

Rebuild Table

-- Drop and recreate table
DROP TABLE IF EXISTS users;
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50)
);

Precautions

1. DROP is irreversible
2. Always backup before dropping
3. Check foreign key dependencies
4. Use IF EXISTS to avoid errors

Best Practices

-- 1. Check if table exists
SHOW TABLES LIKE 'users';

-- 2. Backup data
CREATE TABLE users_backup AS SELECT * FROM users;

-- 3. Drop table
DROP TABLE IF EXISTS users;

Summary

• DROP TABLE: Delete table
• DROP DATABASE: Delete database
• DROP INDEX: Delete index
• IF EXISTS: Avoid errors
• Irreversible, use with caution

Next Step: Learn CONSTRAINTS