In this tutorial, we will learn how to alter various table properties using the ALTER TABLE command in MySQL.
MySQL Alter Table Statement
The syntax of the ALTER TABLE command will vary depending on the action you wish to perform.
Therefore, to understand how we can work with this statement, let us cover some practical examples.
Setting up Sample Table
To start, let us set up a sample table for demonstration purposes.
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
title VARCHAR(255) NOT NULL,
content TEXT NOT NULL,
date_posted DATE NOT NULL,
author VARCHAR(255) NOT NULL
);
Next, let us add sample data:
VALUES ('My First Blog Post', 'This is the content of my first blog post.', '2022-12-09', 'Jane Doe');
INSERT INTO blogs (title, content, date_posted, author)
VALUES ('My Second Blog Post', 'This is the content of my second blog post.', '2022-12-10', 'Jane Doe');
INSERT INTO blogs (title, content, date_posted, author)
VALUES ('My Third Blog Post', 'This is the content of my third blog post.', '2022-12-11', 'Jane Doe');
Example 1 – MySQL Add Table Column
The first example is adding a column to an existing table. The syntax is as shown:
ADD column_name column_definition
[FIRST | AFTER column_name]
In this case, we start by specifying the table’s name to which we wish to add a new column.
Next, we define the name of the column we wish to create, followed by the column definition, such as data types, column constraints, etc.
The FIRST | AFTER column_name clause allows us to define the position of the column in the table. We can use the FIRST keyword to add the column at the first position. To add it to a specific position, you can use the AFTER keyword and the column’s name to wish the new column is created. This is an optional parameter; if not specified, MySQL will add the new column at the end of the table.
The following example shows how to add a table at the end,
ADD rating INT;
The query above should add a rating column of type int to the blogs table.
You can also add multiple columns to an existing table as shown in the syntax below:
ADD column_name column_definition
[FIRST | AFTER column_name]
ADD column_name column_definition
[FIRST | AFTER column_name]
ADD column_name column_definition
[FIRST | AFTER column_name]
...,
ADD column_name column_definition
[FIRST | AFTER column_name]
Example 2 – MySQL Alter Column
We can also use the ALTER TABLE command to change the properties of a column as shown in the syntax below:
MODIFY column_name column_definition
[FIRST | AFTER column_name];
For example, to modify the rating column data type to varchar, we can run:
modify rating varchar(10);
Example 3 – MySQL Rename Column
To rename a column, use the syntax:
CHANGE COLUMN old_name new_name column_definition
[FIRST | AFTER column_name];
Example:
change rating rt varchar(10);
The above should rename the rating column to rt.
Example 4 – MySQL Drop Column
To drop a column using the ALTER TABLE command, use the syntax:
DROP COLUMN column_name;
Example:
drop rt;
This should remove the rt column from the blogs table.
Example 5 – MySQL Rename Table
We can also use the ALTER TABLE statement to rename an existing table as shown in the syntax below:
RENAME TO new_table_name;
Example:
rename to post;
The query above should rename the table from blogs to posts.
Conclusion
In this post, you learned how to use the ALTER TABLE statement to alter various table and column properties such as dropping columns, renaming a table, and more.
