ALTER TABLE operation performs on exists table, through ALTER TABLE command we can ADD COLUMN, UPDATE COLUMN, and DROP COLUMN.
ALTER TABLE is also used to add INDEX, drop INDEX, ADD CONSTRAINT and DROP CONSTRAINT like (ADD UNIQUE/DROP UNIQUE/ADD FOREIGN KEY/DROP FOREIGN KEY), etc.
Add COLUMN INTO TABLE
ALTER TABLE tableName ADD columnName datatype
If you want to add multiple columns into the table
ALTER TABLE tableName ADD (columnName1 datatype, columnName2 datatype, columnName3 datatype……columnNameN datatype)
Suppose We have an employees table
+----+------------+-----------+----------------+------------+
| id | first_name | last_name | email | address |
+----+------------+-----------+----------------+------------+
| 1 | John | Tailor | john@abc.com | California |
| 2 | Rom | Tailor | rom@abc.com | California |
| 3 | Andrew | Symonds | andrew@abc.com | Sydney |
| 4 | Miacle | clerk | miacle@abc.com | sydney |
| 5 | Sachin | Tendulkar | sachin@abc.com | Mumbai |
+----+------------+-----------+----------------+------------+
Now Add column mobile_number into employees table
ALTER TABLE employees ADD mobile_number varchar(10)
Now, check employees table
+----+------------+-----------+----------------+------------+---------------+
| id | first_name | last_name | email | address | mobile_number |
+----+------------+-----------+----------------+------------+---------------+
| 1 | John | Tailor | john@abc.com | California | NULL |
| 2 | Rom | Tailor | rom@abc.com | California | NULL |
| 3 | Andrew | Symonds | andrew@abc.com | Sydney | NULL |
| 4 | Miacle | clerk | miacle@abc.com | sydney | NULL |
| 5 | Sachin | Tendulkar | sachin@abc.com | Mumbai | NULL |
+----+------------+-----------+----------------+------------+---------------+
DROP COLUMN FROM TABLE
ALTER TABLE tableName DROP COLUMN columnName
Example:-
Now, we are going to drop mobile_number column from the employees table
ALTER TABLE employees DROP COLUMN mobile_number
Now, check employees table
+----+------------+-----------+----------------+------------+
| id | first_name | last_name | email | address |
+----+------------+-----------+----------------+------------+
| 1 | John | Tailor | john@abc.com | California |
| 2 | Rom | Tailor | rom@abc.com | California |
| 3 | Andrew | Symonds | andrew@abc.com | Sydney |
| 4 | Miacle | clerk | miacle@abc.com | sydney |
| 5 | Sachin | Tendulkar | sachin@abc.com | Mumbai |
+----+------------+-----------+----------------+------------+