/    /  MySQL – Alter Table

MySQL – Alter Table

 

MySQL - Alter Table

Using the ALTER TABLE statement in MySQL, you can add, modify, or delete columns from a table. It is also possible to rename a table with the ALTER TABLE statement in MySQL.

Add column in table

Syntax

Adding a column to a table in MySQL (using ALTER TABLE) is as follows:

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

table_name – To modify, enter the table name.

new_column_name – To add a new column to the table, give it a name.

column_definition – Defining the datatype and definition of the column (NULL or NOT NULL, etc).

FIRST | AFTER column_name – This is optional. Specifies where the column should be created in the table. By default, the new column will be added at the end of the table if this parameter is not specified.

Example

The following query can be used to add a column named “address” to the “employees” table:

ALTER TABLE employees
ADD COLUMN address VARCHAR(100);

MySQL - Alter Table

Add multiple columns in table

Syntax

Adding multiple columns to a MySQL table (with the ALTER TABLE statement) requires the following syntax:

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

table_name – To modify, enter the table name.

new_column_name – To add a new column to the table, give it a name.

column_definition – Defining the datatype and definition of the column (NULL or NOT NULL, etc).

FIRST | AFTER column_name – This is optional. Specifies where the column should be created in the table. By default, the new column will be added at the end of the table if this parameter is not specified.

Example

Here is an example of how to add multiple columns to a MySQL table using the ALTER TABLE command

. ALTER TABLE employees
  ADD city varchar(40) NOT NULL
    AFTER last_name,
  ADD country varchar(35) NULL
    AFTER city;

MySQL - Alter Table

 

In the ALTER TABLE example, the city and country columns are added to the employee table.

The city field will be created as a varchar(40) NOT NULL column and will appear after the last_name column in the table. The country column will be created as a varchar(35) NULL column and will appear after the city column in the table.

Modify column in table

Syntax

Modifying a column in a MySQL table (using the ALTER TABLE statement) is as follows:

ALTER TABLE table_name
  MODIFY column_name column_definition
    [ FIRST | AFTER column_name ];

table_name – To modify, enter the table name.

new_column_name – To add a new column to the table, give it a name.

column_definition – Defining the datatype and definition of the column (NULL or NOT NULL, etc).

FIRST | AFTER column_name – This is optional. Specifies where the column should be created in the table. By default, the new column will be added at the end of the table if this parameter is not specified.

Example

Here’s an example of how to use the ALTER TABLE statement to modify a column in a MySQL table.

ALTER TABLE employees
MODIFY COLUMN salary INT;

MySQL - Alter Table
This ALTER TABLE example will modify the column called last_name to be a data type of varchar(50) and force the column to allow NULL values.

Modify Multiple columns in table

Using the ALTER TABLE statement, you can modify multiple columns in a table in MySQL:

Syntax

ALTER TABLE table_name
  MODIFY column_name column_definition
    [ FIRST | AFTER column_name ],
  MODIFY column_name column_definition
    [ FIRST | AFTER column_name ],
  ...
;

table_name – To modify, enter the table name.

new_column_name – To add a new column to the table, give it a name.

column_definition – Defining the datatype and definition of the column (NULL or NOT NULL, etc).

FIRST | AFTER column_name – This is optional. Specifies where the column should be created in the table. By default, the new column will be added at the end of the table if this parameter is not specified.

Example

Using the ALTER TABLE statement, we can modify multiple columns in a MySQL table.

 ALTER TABLE employees
  MODIFY last_name varchar(55) NULL
    AFTER first_name,
  MODIFY first_name varchar(30) NOT NULL;

MySQL - Alter Table

Drop column in table:

Using the ALTER TABLE statement in MySQL, you can drop a column in a table as follows:

Syntax

ALTER TABLE table_name
  DROP COLUMN column_name;

table_name – Modify the name of the table.

column_name – Deletes the column from the table by its name.

Example

Here is an example of using the ALTER TABLE statement to remove a column from a MySQL table.

ALTER TABLE employees
DROP COLUMN address;

MySQL - Alter Table

Rename column in table

Syntax

In MySQL, a column can be renamed by using the ALTER TABLE statement:

ALTER TABLE table_name
  CHANGE COLUMN old_name new_name 
    column_definition
    [ FIRST | AFTER column_name ]

Example

 ALTER TABLE employees
  CHANGE COLUMN city city_name
    varchar(20) NOT NULL;

MySQL - Alter Table

Rename table

Syntax

In MySQL, you can rename a table by using the following syntax:

ALTER TABLE table_name
  RENAME TO new_table_name;

table_name – This is the table that needs to be renamed.

new_table_name – Use this new table name.

Example

ALTER TABLE employees
  RENAME TO staff;

MySQL - Alter Table