MySQL DELETE COLUMN Statement

MySQL DELETE COLUMN Statement

The MySQL DELETE COLUMN statement is used to remove a column from an existing table. This statement is essential for modifying the structure of a table to remove unnecessary or obsolete data.

Syntax

ALTER TABLE table_name
DROP COLUMN column_name;

The DELETE COLUMN statement has the following components:

  • table_name: The name of the table to be altered.
  • column_name: The name of the column to be deleted.

Example MySQL DELETE COLUMN Statement

Let's look at an example of the MySQL DELETE COLUMN statement and how to use it:

Step 1: Using the Database

USE mydatabase;

This query sets the context to the database named mydatabase.

MySQL USE DATABASE

Step 2: Creating a Table

Create a table to work with:

CREATE TABLE employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE,
    date_of_birth DATE
);

This query creates a table named employees with columns for id, first_name, last_name, email, and date_of_birth.

MySQL CREATE TABLE

Step 3: Deleting a Column

Delete a column from the table:

ALTER TABLE employees
DROP COLUMN date_of_birth;

This query removes the date_of_birth column from the employees table.

MySQL DELETE COLUMN

Step 4: Verifying the Column Deletion

To verify that the column has been deleted, you can describe the table structure:

DESCRIBE employees;

This query provides a detailed description of the employees table structure, confirming the deletion of the column.

MySQL DESCRIBE TABLE

Conclusion

The MySQL DELETE COLUMN statement is a powerful tool for modifying the structure of existing tables to remove unnecessary or obsolete data. Understanding how to use the DELETE COLUMN statement is essential for effective database management in MySQL.