Blog

MySQL alter command is used to modify an existing database, table, view or other database objects that might need to change during the life cycle of a database. Or you can used when you want to change the name of your table or any table field. It is also used to add or delete an existing column in a table.

The ALTER statement is always used with “ADD“, “DROP” and “MODIFY” commands according to the situation.

Table of content

1. Add/Alter Columns
   • 1.1 Add column in table command
   • 1.2 Alter add column at first
   • 1.3 Alter add column after column
   • 1.4 Alter add multiple columns after column or default
   • 1.5 Modify Auto Increment Value
   • 1.6 Modify varchar() datatype.
2. Delete/Drop Columns
   • 2.1 Alter drop column in table command
   • 2.2 Alter drop multiple column

1. Add column in table

Syntax

ALTER TABLE table_name ADD new_column_name column_definition  [ FIRST | AFTER column_name ];

1.1 Add column in table command

ALTER TABLE student ADD age int(2);

1.2 Alter add column at first

ALTER TABLE student ADD age int(2) FIRST;

1.3 Alter add column after column

ALTER TABLE student ADD telephone_number VARCHAR(20) AFTER name;

alter table add column MySQL

1.4 Alter add multiple columns after column or default

 ALTER TABLE student ADD username VARCHAR(255),add password VARCHAR(255) AFTER mobile;

1.5 Modify Auto Increment Value

 ALTER TABLE student AUTO_INCREMENT = 1000;

1.6 MySQL alter table modify column

 ALTER TABLE student MODIFY address VARCHAR(500);

2. Alter drop column in table

Syntax

ALTER TABLE table_name DROP existing_column_name;

2.1 Alter drop column in table command

ALTER TABLE student DROP age;

2.2 Alter drop multiple column

ALTER TABLE student drop age,drop id;