Kenhuang Academy
  • English

      • 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

      OperationDROPTRUNCATEDELETE
      RemovesStructure+DataData onlyData only
      SpeedFastVery fastSlow
      RollbackNoNoYes
      WHERENoNoYes
      TriggersNoNoYes

      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