MySQL ALTER TABLE command is used to modify the structure of MySQL table. ALTER TABLE ADD COLUMN will only add the new column at the end, as the last one. MySQL ALTER TABLE statement can do following actions on a table:
- Add column to Table
- Drop column from table
- Change the data type and its length
- Add or drop constraints
- Rename column name
- Change Storage Engine Type
……….
MySQL ALTER TABLE Syntax:
ALTER TABLE table_name [alter_specification [, alter_specification] ...] [partition_options]
Permissions required to execute MySQL ALTER TABLE:
To use ALTER TABLE, you need ALTER, CREATE, and INSERT privileges for the table.
Examples:
Create a table.
CREATE TABLE student(SNO int,sname varchar(90), dob date,class int,gender varchar(1));
1. How to add column to MySQL Table
ALTER TABLE student ADD email varchar(90);
To verify run the command “describe table_name;”
mysql> describe student; +--------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------+-------------+------+-----+---------+-------+ | SNO | int(11) | YES | | NULL | | | sname | varchar(90) | YES | | NULL | | | dob | date | YES | | NULL | | | class | int(11) | YES | | NULL | | | gender | varchar(1) | YES | | NULL | | | email | varchar(90) | YES | | NULL | | +--------+-------------+------+-----+---------+-------+ 6 rows in set (0.27 sec)
2. How to delete or remove column from MySQL Table
ALTER TABLE student drop column email;
3. How to modify MySQL Table Column
Change the sname column length to 100
ALTER TABLE student ALTER COLUMN sname varchar(100);
4. How to add constraint on MySQL Table
ALTER TABLE student ADD CONSTRAINT PRIMARY KEY(SNO);
