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:

ALTER TABLE employee_table ADD age varchar(40) NOT NULL;

Output:

Table Altered Successfully.

2. Add multiple columns in the table

Syntax :

ALTER TABLE tablename

ADD 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:

select * from employee_table;

Output:

fieldsresult

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.

ALTER TABLE employee_table MODIFY name varchar(50) NULL;

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"

ALTER TABLE employee_table DROP COLUMN address;

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".

ALTER TABLE employee_table CHANGE COLUMN name fullname varchar(20) NOT NULL;

Output:

Table Altered Successfully.