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:

Note: That the value of the newly added experience column is set to 0 as we have mentioned it in the column definition.

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.

Popular Course in this category