Skip to main content

Altering and Deleting Tables in MySQL

Introduction

MySQL is a powerful relational database management system that provides a wide range of functionality to manage data. One of the fundamental tasks when working with MySQL is creating, altering, and deleting tables. This tutorial will guide you on how to alter and delete tables in MySQL.

Creating a Sample Table​

Before we delve into how to alter and delete tables, let's first create a simple table as a reference. The SQL command to create a new table in MySQL is the CREATE TABLE statement. For example:

CREATE TABLE Employees (
    ID INT,
    Name VARCHAR(50),
    Position VARCHAR(50),
    Salary INT
);

This will create a new table called Employees with four columns: ID, Name, Position, and Salary.

Altering Tables in MySQL​

The ALTER TABLE statement allows you to modify the structure of an existing table in MySQL. You can use it to add, delete, or modify columns in an existing table. You can also use the ALTER TABLE statement to add and drop various constraints on an existing table.

Adding a Column​

To add a new column to a table, you use the ADD COLUMN clause in the ALTER TABLE statement. The basic syntax is as follows:

ALTER TABLE table_name
ADD COLUMN new_column_name column_definition;

For example, to add a new column Department to the Employees table, you would do:

ALTER TABLE Employees
ADD COLUMN Department VARCHAR(50);

Modifying a Column​

If you want to change the data type of a column, you can use the MODIFY COLUMN clause in the ALTER TABLE statement. The syntax is as follows:

ALTER TABLE table_name
MODIFY COLUMN column_name new_column_type;

For example, if we want to modify the Salary column from INT to FLOAT in the Employees table, we can do:

ALTER TABLE Employees
MODIFY COLUMN Salary FLOAT;

Deleting a Column​

To delete a column from a table, we use the DROP COLUMN clause. Here is the syntax:

ALTER TABLE table_name
DROP COLUMN column_name;

For example, to delete the Department column from the Employees table, you would do:

ALTER TABLE Employees
DROP COLUMN Department;

Deleting Tables in MySQL​

In MySQL, you can easily delete (drop) a table from the database. The SQL command to delete a table is the DROP TABLE statement.

Here is the syntax:

DROP TABLE table_name;

For example, to delete the Employees table, you would do:

DROP TABLE Employees;

Please be careful when using the DROP TABLE command because once the table is deleted, all the information available in the table is lost.

Conclusion

Altering and deleting tables are common tasks in MySQL that allow you to manage your data effectively. This tutorial introduced you to the basic SQL commands to alter and delete tables in MySQL. Remember to be cautious when deleting tables to avoid losing important data. Happy coding!