ALTER TABLE MySQL

Introduction to Alter Table Mysql

In MySQL, ALTER TABLE command is used to change the name of the table or rename one or more columns of the table, add new columns, remove existing ones, modify the datatype, length, index of one or more columns and we can also rename the name of the table. This command is most often used with ADD, DROP and MODIFY statements depending on the operation that you wish to perform for the table, its columns, or indexes. We can even change the sequence of the columns in the table using the ALTER TABLE command. In this article, we will learn the syntax and the usage of the ALTER TABLE command accompanied by ADD, DROP, or MODIFY statement with the help of examples.

Examples to Implement ALTER TABLE MySQL

Below are the examples mentioned:

1. ALTER TABLE statement with ADD command

We can add one or more columns to the existing table by using the ALTER TABLE statement with the ADD command. The syntax of adding the columns using the ALTER statement is as follows –

Syntax

ALTER TABLE name_of_table
ADD name_of_new_column details_of_column
[ FIRST | AFTER name_of_existing_column ];

Parameters

• name_of_table: This is the name of the existing table in which we wish to add a new column using the ALTER query.
• name_of_new_column: This is the name of the new column that we are adding to the table.
• details_of_column: This helps to specify the details and definition of the new column we are adding that includes the datatype of the column and other details such as NULL or NOT NULL, UNIQUE, etc.
• name_of_existing_column: We can specify the position of the column which we are adding with respect to the existing position of the columns in the table named name_of_table by using the FIRST and AFTER keywords. FIRST represents that the new column will be placed in the beginning while AFTER signifies the position of the new column after name_of_existing_column named column in the table. The default position where the new column is added is at the last.

Let us consider an existing table named developers. After executing the following SELECT command –

Code #1

SELECT * FROM `developers`;

Output:

Now, we need to add one more column named experience which is of integer datatype as it will store value in years. For this we can make the use of ALTER TABLE statement with ADD command and the query will be somewhat like this –

Code #2

ALTER TABLE developers
ADD experience INTEGER NOT NULL DEFAULT 0;

Output:

Let us check the records by selecting it using this query –

Code #3

SELECT * FROM `developers`;

Output:

We can even add multiple columns in single query statement. Suppose, we want to add three more columns named joiningDate, age, and address. We can do so, by executing the following ALTER TABLE command –

Code #4

ALTER TABLE developers
ADD joiningDate DATE
FIRST ,
ADD age INT NOT NULL
AFTER NAME,
ADD address VARCHAR(100) NOT NULL
AFTER experience;

Output:

Let us now, retrieve the records from developers table to view our changes by executing following select query –

Code #5

SELECT * FROM `developers`;

Output:

As we can see all the new columns are added at their respective positions that we mentioned and are initialized to its default value depending on their datatype. The default value of integer datatype column is 0, varchar i.e string is “” blank and for date is NULL.

2. ALTER TABLE statement with MODIFY command

We can modify the column by changing its definition and position by using the ALTER TABLE statement with MODIFY command. The syntax of modifying the column is similar to adding the column which is as follows –

ALTER TABLE name_of_table
MODIFY name_of_column_to_modify details_of_column
[ FIRST | AFTER name_of_existing_column ];

Where all the names used have the same as ADD command except  name_of_column_to_modify that stands for the name of the existing column that needs to modify.

Let us alter the joining date column by setting the default value to “2020-05-01”. For this, we will make the use of the following ALTER TABLE query with MODIFY clause in the following way –

Code #1

ALTER TABLE developers
MODIFY joiningDate DATE DEFAULT "2020-05-01";

Output:

Let us check the records of the developer’s table after inserting one record –

Code #2

INSERT INTO `developers` (`joiningDate`, `developer_id`, `team_id`, `name`, `age`, `position`, `technology`, `salary`, `experience`, `address`) VALUES(DEFAULT,'11','2','Suresh','0','Designer','MySQL','20000','0','');

Code #3

ALTER TABLE developers
MODIFY joiningDate DATE DEFAULT "2020-05-01";

And now, let us retrieve the records of developers table and check the joiningDate column of Suresh by using following query –

Code #4

SELECT * FROM `developers`;

Output:

In a similar fashion, we can modify multiple columns of a particular table using a single ALTER TABLE query with modify clause.

3. ALTER TABLE statement with DROPcommand

We can delete or drop the column by specifying its name by using the ALTER TABLE statement with DROP command. The syntax of droping the column is as follows –

ALTER TABLE name_of_table
DROP COLUMN name_of_column_to_drop;

Suppose, we want to drop address column of developers table. For that We can make the use of ALTER TABLE command with DROP clause in following way –

Code #1

ALTER TABLE  developers
DROP COLUMN address;

Output:

Now, let us verify by retrieving all the records of developers table by using the same select query that gives the following output –

So, we can conclude that our address column is deleted completely including its structure and values of column for all records.

4. ALTER TABLE statement with CHANGE command to rename column names

We can change the name of the column and rename it by using the ALTER TABLE statement with the CHANGE command. The syntax of renaming the column is similar to modifying the column which is as follows –

ALTER TABLE name_of_table
CHANGECOLUMN old_name_of_column new_name_of_column details_of_column
[ FIRST | AFTER name_of_existing_column ];

Where all the names used have the same as ADD command except  old_name_of_column that stands for the name of the existing column that needs to renamed and new_name_of_column that specifies the new name that column should possess.

Code #1

Let us change the name of column developer_id to id of developers table. Our alter command will be as follows –

ALTER TABLE developers
CHANGE COLUMN developer_id  id INTEGER NOT NULL ;

Output:

Output:

5. ALTER TABLE statement to rename table

We can even change the name of the table using ALTER TABLE command for which we need to follow following syntax –

Code #1

ALTER TABLE new_table_name
RENAME TO new_table_name;

Suppose, that in my example, I have to rename the table developers to workers. In that case, we will use the following ALTER query –

Code #2

ALTER TABLE developers
RENAME TO workers;

Output:

test is my database name and it says that developers tables don’t exist anymore.

Code #3

SELECT * FROM workers;

Output:

Conclusion

We can use the ALTER TABLE command to add, modify, and drop the columns of the table and also to rename the columns and the table names.

Recommended Articles

This is a guide to ALTER TABLE MySQL. Here we discuss an introduction to ALTER TABLE MySQL, syntax, how to use an alter table with different query examples. You can also go through our other related articles to learn more –

1. Natural Join in MySQL
2. Unique Key in MySQL
3. MySQL Self Join
4. Cursor in MySQL