MySQL Alter Table Statement

In this MySQL tutorial, you will learn everything about the MySQL Alter Table statement to add a column, rename a column, modify a column, drop a column and add multiple columns into the database table. In the previous tutorial, we have seen all about MySQL Create Table statement to create a new table into the MYSQL database.

MySQL Alter Table Introduction

Here, we will create a simple MYSQL Table using MySQL CREATE TABLE statement, so that you don’t have any confusion. This Table is only for testing purposes.

Example: Create Table in MySQL

CREATE TABLE students 
(
	st_id INT PRIMARY KEY,
	name VARCHAR(100) NOT NULL,
	roll_no INT,
	UNIQUE(roll_no)
);

Learn more about the MySQL CREATE TABLE statement.

Use DESC students command to see the structure of students table. The table looks like this.

+---------+--------------+------+-----+---------+-------+
| Field   | Type         | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| st_id   | int(11)      | NO   | PRI | NULL    |       |
| name    | varchar(100) | NO   |     | NULL    |       |
| roll_no | int(11)      | YES  | UNI | NULL    |       |
+---------+--------------+------+-----+---------+-------+
3 rows in set (0.11 sec)

Now let move on to the topic where we can see all about MySQL ALTER TABLE statement.

MySQL ALTER TABLE ADD COLUMN

The MySQL ALTER TABLE ADD allows users to add a new column to the existing database table.
let’s add a column named course to the students table.

ALTER TABLE students ADD course VARCHAR(50) NOT NULL;

Use DESC students command to see the new structure of students table.

+---------+--------------+------+-----+---------+-------+
| Field   | Type         | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| st_id   | int(11)      | NO   | PRI | NULL    |       |
| name    | varchar(100) | NO   |     | NULL    |       |
| roll_no | int(11)      | YES  | UNI | NULL    |       |
| course  | varchar(50)  | NO   |     | NULL    |       |
+---------+--------------+------+-----+---------+-------+
4 rows in set (0.01 sec)

As you can see, a new column course has been added to the students table.

MySQL ALTER TABLE MODIFY COLUMN

The ALTER TABLE MODIFY command allows us to modify the existing database table column.

Syntax:

The basic syntax of the ALTER TABLE MODIFY is.

ALTER TABLE table_name 
MODIFY col_name column_definition
[FIRST | AFTER col_name];

Example: Modify Column in MySQL

Here we will modify the course column with a maximum of 100 characters.

ALTER TABLE students MODIFY course VARCHAR(100) NOT NULL;

Now you can use the DESC stduents command to show the column name list to verify the changes.

MySQL ALTER TABLE RENAME COLUMN

ALTER TABLE RENAME statement is used to rename the table column name.

Syntax

The basic syntax of ALTER TABLE RENAME column statement is:

ALTER TABLE table_name 
CHANGE [COLUMN] old_col_name new_col_name column_definition
[FIRST|AFTER col_name]

Example: Rename column name in MySQL

In this example, we will rename the course column to courses.

ALTER TABLE students CHANGE COLUMN course courses VARCHAR(100) NOT NULL;

you can use desc students to verify the changes.

+---------+--------------+------+-----+---------+-------+
| Field   | Type         | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| st_id   | int(11)      | NO   | PRI | NULL    |       |
| name    | varchar(100) | NO   |     | NULL    |       |
| roll_no | int(11)      | YES  | UNI | NULL    |       |
| courses | varchar(100) | NO   |     | NULL    |       |
+---------+--------------+------+-----+---------+-------+
4 rows in set (0.01 sec)

MySQL ALTER TABLE DROP COLUMN

The ALTER TABLE DROP statement is used to drop the existing column of the table.

Syntax

The basic syntax of MySQL alter table drop column is:

ALTER TABLE table_name DROP col_name;

Example: Drop column name in MySQL

Here we will drop the courses column from the students table which we have created in the above example.

ALTER TABLE students DROP courses;

You can use DESCRIBE students command to verify the changes.

MYSQL ALTER TABLE ADD MULTIPLE COLUMNS:

The alter table add multiple columns statement allows us to add multiple columns in the existing database table.

Syntax
The basic syntax of altering the table add multiple columns is as follows.

ALTER TABLE table_name
ADD [COLUMN] col_name column_definition
        [FIRST | AFTER col_name],
ADD [COLUMN] col_name column_definition
        [FIRST | AFTER col_name]
......;

Example: Add Multiple Columns in MySQL

In this example, we will add courses and address to the students table.

ALTER TABLE students 
ADD courses VARCHAR(100) NOT NULL,
ADD address VARCHAR(100) NOT NULL;

You can use DESCRIBE students command to verify the changes.

+---------+--------------+------+-----+---------+-------+
| Field   | Type         | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| st_id   | int(11)      | NO   | PRI | NULL    |       |
| name    | varchar(100) | NO   |     | NULL    |       |
| roll_no | int(11)      | YES  | UNI | NULL    |       |
| courses | varchar(100) | NO   |     | NULL    |       |
| address | varchar(100) | NO   |     | NULL    |       |
+---------+--------------+------+-----+---------+-------+
5 rows in set (0.01 sec)

Conclusion

In this article, you have been seen all about MySQL ALTER TABLE statements to add, rename, drop and modify columns into the database table.MySQL ALTER TABLE statement is one of the most important statements, especially when you want to work with existing database table columns.

I hope this article will help you. if you like this article, share and keep visiting for further MySQL tutorials.

Thanks for your valuable time …

Reference:- Click Here