MySQL ALTER TABLE

We can modify the structure of a Table by using MySQL ALTER TABLE Statement. With ALTER TABLE statement we can ADD a column to the existing table, DROP a column from the table, change the DataType of the column, change the name of the Table, Add Constraints like Primary Key, ForeignKey etc

ALTER TABLE SYNTAX

Below is the Basic syntax of MySQL ALTER TABLE statement:

WHERE:

TableName: Is the Name of the table whose structure/specification you are modifying
ALTER_EXPRESSION: can be an expression to add a column, drop a column, add a constaraint etc.

ALTER TABLE EXAMPLES

To demonstrate the ALTER TABLE examples, let us first create a demo database MySQLTutorial and set it as an contextual database by executing the following script:

Also create a simple Customer Table with sample data by executing the following script:

EXAMPLE 1: ADD COLUMN

We can add a new column DateOfBirth of type DATETIME to the Customer table by executing the following statement

Let us execute the following statement to verify whether this new column is added to the Customer table or not.

RESULT:
mysql-add-column

From the above result we can see that the new column DateOfBirth is added to the customer Table.

EXAMPLE 2: DROP COLUMN

We can remove/drop the column DateOfBirth from the Customer table by executing the following statement

Let us execute the following statement to verify whether the DateOfBirth column is removed/dropped from the Customer table or not.

RESULT:
mysql-drop-column

From the above result we can see that the DateOfBirth column is dropped from the customer Table.

EXAMPLE 3: MODIFY COLUMN DATA TYPE

We can change the CustomerId column DataType from INT to BIGINT by executing the following statement

Let us execute the following statement to verify whether the CustomerId column DataType is changed from INT to BIGINT type.

RESULT:
mysql-modify-column-data-type

From the above result we can see that now the CustomerId column is of type BIGINT.

Note: In MySQL we can use DESCRIBE statement to see the Table definition.

EXAMPLE 4: RENAME TABLE

We can change the Table name Customer to CustomerInfo by executing the following statement

Let us execute the following statement to verify whether the table name is changed from Customer to CustomerInfo:

RESULT:
mysql-rename-table

EXAMPLE 5: CHANGE COLUMN NAME

We can change the name of the column PhoneNumber to MobileNumber by executing the following statement

Let us execute the following statement to verify whether the column name PhoneNumber is changed to MobileNumber or not?:

RESULT:
mysql-change-column-name

From the above result we can see that the column name PhoneNumber is changed to MobileNumber.

Leave a Reply

Your email address will not be published. Required fields are marked *