The ALTER TABLE statement is used to add, delete, or modify columns in an existing table. It is also used to add and drop various constraints on an existing table.

Current state

CREATE TABLE student(
student_id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
first_name VARCHAR(30) NOT NULL,
last_name VARCHAR(30) NOT NULL);
SHOW TABLES
[('student',)]
DESCRIBE student
Field Type Null Key Default Extra
0 student_id int(10) unsigned NO PRI None auto_increment
1 first_name varchar(30) NO None
2 last_name varchar(30) NO None

Add column

Add column after all columns

ALTER TABLE student
ADD birth_date DATE NOT NULL;
DESCRIBE student
Field Type Null Key Default Extra
0 student_id int(10) unsigned NO PRI None auto_increment
1 first_name varchar(30) NO None
2 last_name varchar(30) NO None
3 birth_date date NO None

Using AFTER we can add column after a specified column

--- ALTER TABLE table_name
--- ADD new_column DATATYPE CONSTRAINT AFTER this_column 
ALTER TABLE student
ADD email VARCHAR(60) NULL AFTER last_name;
DESCRIBE student
Field Type Null Key Default Extra
0 student_id int(10) unsigned NO PRI None auto_increment
1 first_name varchar(30) NO None
2 last_name varchar(30) NO None
3 email varchar(60) YES None
4 birth_date date NO None

Drop column

-- ALTER TABLE table_name
-- DROP COLUMN column_name;
ALTER TABLE student
DROP COLUMN email;
DESCRIBE student;
Field Type Null Key Default Extra
0 student_id int(10) unsigned NO PRI None auto_increment
1 first_name varchar(30) NO None
2 last_name varchar(30) NO None
3 birth_date date NO None

Modify column datatype

-- ALTER TABLE table_name
-- MODIFY COLUMN column_name DATATYPE;
ALTER TABLE student
MODIFY COLUMN birth_date YEAR;
DESCRIBE student;
Field Type Null Key Default Extra
0 student_id int(10) unsigned NO PRI None auto_increment
1 first_name varchar(30) NO None
2 last_name varchar(30) NO None
3 birth_date year(4) YES None

Change column name

-- ALTER TABLE table_name
-- CHANGE current_name new_name DATATYPE;
ALTER TABLE student
CHANGE birth_date birth_year YEAR NOT NULL;
DESCRIBE student;
Field Type Null Key Default Extra
0 student_id int(10) unsigned NO PRI None auto_increment
1 first_name varchar(30) NO None
2 last_name varchar(30) NO None
3 birth_year year(4) NO None

Rename table name

RENAME TABLE 
student to student_data
SHOW TABLES
[('student_data',)]

References