Alter
Definition :
ALTER statement is 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
1. ADD a column in the table
To add a new column into an existing table, we use ADD keyword with the ALTER TABLE command.
For Alter Table :
Syntax :
ALTER TABLE tablename ADD new_column_name column_definition FIRST | AFTER column_name ;Explanation:
The parameter descriptions of the above syntax are as follows:
1. table_name: It specifies the name of the table that you want to modify.
2. new_column_name: It specifies the name of the new column that you want to add to the table.
3. column_definition: It specifies the data type and definition of the column (NULL or NOT NULL, etc).
4.FIRST | AFTER column_name: It is optional. It tells MySQL where in the table to create the column. If this parameter is not specified, the new column will be added to the end of the table.
Example:
Here, we are going to create a table name "employee_table" in the database "CodersClash" using the following statement:
Output:
Table Altered Successfully.
2. Add multiple columns in the table
Syntax :
ALTER TABLE tablenameADD new_column_name column_definition FIRST | AFTER column_name,
ADD new_column_name column_definition FIRST | AFTER column_name,... ;
Example:
In this example, we add two new columns "address", and "salary" in the existing table "employee_table".address is added after name column and salary is added after age column.
ALTER TABLE employee_table
ADD address varchar(100) NOT NULL AFTER name,
ADD salary int NOT NULL AFTER age ;
Output:
Table Altered Successfully.
See the recently added columns:
Output:
3. MODIFY column in the table:
The MODIFY command is used to change the column definition of the table.
Syntax :
ALTER TABLE tablename MODIFY new_column_name column_definition FIRST | AFTER column_name ;Example:
In this example, we modify the column name to be a data type of varchar(50) and force the column to allow NULL values.
Output:
Table Altered Successfully.
4. DROP column in table
To delete a column in a table, use the following syntax :
Syntax :
ALTER TABLE tablename DROP COLUMN ;Example:
Let's take an example to drop the column name "address" from the table "employee_table"
Output:
Table Altered Successfully.
5. RENAME column in table
Syntax :
ALTER TABLE tablename CHANGE COLUMN old_columnname new_columnname column_definition;Example:
In this example, we will change the column name "name" to "Full name".
Output:
Table Altered Successfully.