While managing a database, a database administrator needs to alter the tables’ structure to stay up to date. In this article, we will learn some different types of tasks that we can do using MySQL’s provided ALTER command. So, let’s get started.
ALTER command alters or changes the structure of the table. For example,
- Addition of a column
- Deletion of a column
- Renaming of a column
- Modification of a column
In this article, we will try examples of ALTER commands to add, delete, rename, and modify the columns of a table in MySQL.
Add a column
We can add a column into an already existing table using the following syntax of the ALTER command.
In this syntax, make sure to replace table_name and column_name with the name you want to give.
We can also mention the position of the newly created column by using the FIRST and AFTER clause and referencing the already existing column in the table. For example
If we want to add a column before an existing column, we can use the FIRST clause. Similarly, if we want to add a column after the existing column, we can use the AFTER clause.
We can add multiple columns as well, using the same ALTER TABLE and ADD column syntax. For example
Drop/Delete a column
Deleting a column in MySQL is as easy as saying. We can remove an existing column in a table by using the ALTER TABLE command and DROP. The syntax for deleting a column is
This is how simple it is to delete a column of a table in MySQL.
Rename a Column
We can rename a column by using the CHANGE clause along with the ALTER TABLE command. In which, we first provide the existing name of the column and then the new name along with the data type, followed by the CHANGE clause. To change the name of the column, run the following command
If we can change the name of the column, we can change the name of the table and use the RENAME clause with the ALTER TABLE command. The syntax for renaming the table is
Modify a column
Suppose we want to change the column definition or data type of a column in MySQL. The MODIFY clause comes in handy to help us in this case. We can use the MODIFY clause to alter the data type of a column. Like this
Here is a protip for using the ALTER command.
Set default value to a column
If we want to provide or set some default value to a column of a table. WE can do so by running the following command
In this syntax, make sure to replace the table_name, column_name, and value according to your requirement.
If we can set the default values of a column. There must be a way to unset or drop the default values of a column.
Drop default value of a column
Well, MySQL provides the DROP DEFAULT clause as well in the ALTER TABLE command to drop the default values of a column.
So, these are some of the different ways to manage and alter the table in MySQL.
This article includes various ways to use the ALTER command to perform some different operations. In this article, we learned to add, delete, rename the table and columns, modify the column definition, set and unset the default values of a column using the ALTER TABLE command in MySQL. If this article helped you in having a better understanding of ALTER Command in MySQL and you want to learn more about the concepts of MySQL. Keep visiting our website linuxhint.com.