The MySQL ALTER TABLE
statement is used to modify an existing table. This statement is essential for adding, deleting, or modifying columns and constraints in a table.
ALTER TABLE table_name
ADD column_name datatype [constraints],
MODIFY column_name datatype [constraints],
DROP COLUMN column_name;
The ALTER TABLE
statement has the following components:
table_name
: The name of the table to be altered.ADD column_name datatype [constraints]
: Adds a new column to the table with the specified data type and optional constraints.MODIFY column_name datatype [constraints]
: Modifies an existing column's data type and optional constraints.DROP COLUMN column_name
: Deletes a column from the table.Let's look at an example of the MySQL ALTER TABLE
statement and how to use it:
USE mydatabase;
This query sets the context to the database named mydatabase
.
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
);
This query creates a table named employees
with columns for id
, first_name
, last_name
, and email
.
Next, add a new column to the table:
ALTER TABLE employees
ADD date_of_birth DATE;
This query adds a new column named date_of_birth
to the employees
table.
Modify an existing column's data type:
ALTER TABLE employees
MODIFY email VARCHAR(150);
This query changes the data type of the email
column in the employees
table to VARCHAR(150)
.
Drop an existing column from the table:
ALTER TABLE employees
DROP COLUMN date_of_birth;
This query deletes the date_of_birth
column from the employees
table.
The MySQL ALTER TABLE
statement is a powerful tool for modifying the structure of existing tables. Understanding how to use the ALTER TABLE
statement and its various options is essential for effective database management in MySQL.