ALTER TABLE

MySQL

In MySQL ALTER command command is very useful when you want to change any table field or if you want to add or delete an existing column in a table. The MySQL ALTER TABLE statement is also used to rename a table.

Syntax:

ALTER TABLE table_name ADD new_column_namecolumn_definition

[ FIRST | AFTER column_name ]:
table_name -The name of the table to modify.

new_column _name  -The name of the new column to add to the table.

Column_definition_name  - The datatype and definition of the column (Null or NOT NULL, etc). 

FIRST | AFTER column_name- 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:

Let’s look at an example that shows how to add a column in a MySQL table using the ALTER TABLE statement.

ALTER TABLE customer ADD gendervarchar (6) AFTER customer name

This will add a column gender to the customer table after the customer name field in the table. Now the check the CUSTOMER table,

Add multiple columns in table

Syntax:

ALTER TABLE table_name

ADD new_column_namecolumn_defination

     [FIRST | AFTER column_name],

ADD new_column_namecolumn_definition

     [First | AFTER column_name],
  • table_name – The name of the table to modify.
  • new_column_name – The name of the new column to added.
  • column_definition – The datatype and definition of the column
  • First | AFTER column_name – 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.

Modify columns in table

Syntax:

ALTER TABLE table_name

        MODIFY column_name column_definitilon
[FIRST|AFTER column_name],
        MODIFY column_name column_definition.
[FIRST|AFTER column_name],
table_name  - The name of the table to modify.
column_name  - The modified datatype

Let’s look at an example that shows how to modify a column in a MySQL table using the ALTER TABLE statement.

ALTER TABLE customer MODIFY customername varchar(25)

This ALTER TABLE example will modify the column customer name to be a data type of varchar(25).

Modify Multiple columns in table

Syntax:

ALTER TABLE table_name

        MODIFY column_name column_definitilon
[FIRST|AFTER column_name],
        MODIFY column_name column_definitilon
[FIRST|AFTER column_name],

  • table_name – The name of the table to modify.
  • new_column_name – The name of the new column to added.
  • column_definition – The datatype and definition of the column
  • First | AFTER column_name – 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

Let’s look at an example that shows how to modify multiple columns in a MySQL table using the ALTER TABLE statement.

For example:

ALTER TABLE contacts
MODIFY last_name varchar(55) NULL
AFTER contacts_type,
MODIFY first_name varchar(30) NOT NULL:
This ALTER TABLE example will modify two columns to the contacts table - last_name and first_name.

The last name field will be changed to a varchar(55) NULL column and will appear after the contact type column in the table. The first_name column will be modified to a varchar(30) NOT NULL coumn (and will not change position in the contacts table definition, as thereis no FIRST | AFTERspecified).

Drop column in table

Syntax:

The syntax to drop a column in a table in MySQL (using the ALTER TABLE statement) is:

ALTER TABLE table_name
DROP COLUMN column_name;
table_name
The name of the table to modify.
column_name
The name of the column to delete from the table.

Example

Let’s look at an example that shows how to drop a column in a MySQL table using the ALTER TABLE statement.

For example:

ALTER TABLE table_name
DROP COLUMN column_name;

This ALTER TABLE example will drop the column called contact_type from the table called contacts.

Rename column in table

Syntax:

The syntax to rename a column in a table in MySQL (using the ALTER TABLE statement is:

ALTER TABLE table_name
CHANGE COLUMN old_name new_name
column_definition
[FIRST | AFTER column_name ]
table_name
The name of the table to modify.
old_name
The column to rename.
new_name
The new name for the column.
column_definition

The datatype and definition of the column (NULL or NOT NULL, etc). You must specify the column definition when renaming the column, even if it does not change.

FIRST AFTER column_name

Optional. It tells MySQL where in the table to position the column, if you wish to change its position.

Example

Let’s look at an example that shows how to rename a column in a MySQL table using the ALTER TABLE statement.

For example:

ALTER TABLE contacts
CHANGE COLUMN contact_typectype
varchar(20) NOT NULL;

This MySQL ALTER TABLE example will rename the column called contact_type to ctype. The column will be defined as a varchar(20) NOT NULL column.

Rename table

Syntax:

The syntax to rename a table in MySQL is:

ALTER TABLE table_name
RENAME TO new_table_name;
table_name
The table to rename.
new_table_name
The new table name to use.

Example

Let’s look at an example that shows how to rename a table in MySQL using the ALTER TABLE statement.

For example:

ALTER TABLE contacts
RENAME TO people:
This ALTER TABLE example will rename the contacts table to people.
Create a table named sales for the below structure,
sales id INT UNSIGNED, productid INT UNSIGNED, quantity INT UNSIGNED,
price DECIMAL(7.2),
amount DECIMAL(10,2));


Create a table named sale summary for the below structure,
Store the following details into the sales table

sales idproduct idquantitypriceamount
110101550550
110021325325
21007168006800
21008160006000
21004222004400
310101600600
4100225501100
51007170007000