MySQL Add a Column to Existing Table

MySQL Database System is a highly scalable database service for creating cloud-native applications. Therefore we have to perform different operations while working on it. The ALTER TABLE declaration is being cast-off to add, remove, or alter columns while working on an already existing table in any schema of MySQL. We’ll teach you exactly how to declare a column to an existing table utilizing the MySQL ADD COLUMN expression in this guide.

Syntax:

>> ALTER TABLE table_name ADD new_column_name column_definition [FIRST | AFTER column_name ];

Here is the detail of this query:

• Table_name: is the existing table you wish to amend or add a new column.
• New_column_name: is the title for a new column to be added.
• Column_definition: It is the data type of a new column and its definition, e.g., Null, Not Null.
• FIRST | AFTER column_name: This clause specifies the location of a new column in the table. It is optional; that’s why if not used, the column will be implanted at the last of a table.

Add Column via MySQL Workbench

Open your newly installed MySQL workbench from the start button of your desktop. Make sure to connect your workbench with the database.

In the Navigation bar of the workbench under the schema, you can find the already created databases. We have created a database ‘data’ and added a table ‘student’ into it. The table ‘student’ has the following columns and records in it.

To add a new column in the existing table ‘student’, you have to navigate towards the Schemas under the Navigator. Within the database ‘data’, we have a list of tables, e.g., student and teacher. You have to expand the table, student. While hovering over it, you will find an icon of the setting, as highlighted below. Click on it to continue.

The below window will be opened in MySQL workbench. You can view a list of columns and their definitions. You can add a new column at the last of all the columns by double tapping at the last space and, after that, writing a column name on it.

It is clear from the below image that we have added the new column ‘age’ at the last of all columns, with its definition defined.

You will find a new window having a query listed to add a new column as below.  Click on Apply button to proceed.

The window will be opened. Hit on Finish to see changes.

Now, the restructured table is appended below.

After adding values, it will look like as underneath. You can also add the Alter query in the space above this table and under the navigation bar to add a column in a table.

Add Column via Command-Line Shell

To add a new column in an existing table while using the command-line, you have to open the MySQL command-line client from the taskbar. Enter your MySQL password when asked and press Enter.

Upon checking, we have found the below-given record in the table ‘student’. Let’s add a new column, ‘age’, at the end of the table.

>> SELECT * FROM data.student ORDER BY id;

Example 01: Add Single Column

If you want to add a single column to an existing table, then this example is meant for you. Now, we will add a new column named ‘age’ at the last place of the table, ‘student’. To add a new column ‘age’ after the column ‘class’, try the below query in the MySQL command-line shell.

>> ALTER TABLE data.student ADD age VARCHAR(20) NOT NULL AFTER class;

On checking the table, you will see that the table has created a new empty column ‘age’ at the last place, as shown in the image.

>> SELECT * FROM data.student ORDER BY id;

We will be updating a table while adding values to a newly created column ‘age’. We have tried the below three UPDATE queries to add values into a column ‘age’.

>> UPDATE data.student SET age= ‘25’ WHERE id > 0 and id < 3;

>> UPDATE data.student SET age= ‘17’ WHERE id > 3;

>> UPDATE data.student SET age= ‘18’ WHERE id = 3;

Let’s check the updated table ‘student’ using the below SELECT query in the shell as:

>> SELECT * FROM data.student ORDER BY id;

Now we have a full-fledge newly updated table as given below.

Example 02: Add More than One Columns

You can also add more than one column at different places of an existing table using ALTER query. Let’s try the below query to add two new columns, e.g., gender and city, at the last of all the table ‘student’ columns. We have used two ADD clauses in this query to add two columns.

>> ALTER TABLE data.student ADD COLUMN gender VARCHAR(20) NOT NULL AFTER age, ADD COLUMN city VARCHAR(20) NOT NULL AFTER gender;

You will find the below-updated table upon checking it with the SELECT query in the shell. You will find that the table has created two new columns with no values in them at all.

>> SELECT * FROM data.student ORDER BY id;

To avoid the emptiness of newly created columns, we will be adding values to new columns, e.g., gender and city. We have tried the below three UPDATE queries to add values into the columns’ gender’ and ‘city’. First of all, we have updated the column ‘gender’ using the below query as:

>> UPDATE data.student SET gender = ‘Female’ WHERE id < 6;

After that, we have updated the column ‘city’ by using the below two UPDATE commands:

>> UPDATE data.student SET city = ‘Islamabad’ WHERE id < 3;

>> UPDATE data.student SET city = ‘Rawalpindi’ WHERE id > 2;

Let’s check the updated table ‘student’ using the beneath SELECT query in the command line shell as:

>> SELECT * FROM data.student ORDER BY id;

Finally, we have got a newly restructured table as given below.

Conclusion

Perfect! We have efficiently tried all the queries to add a single column or more than one column in an existing table while working in MySQL workbench and Command-line client shell.

from Linux Hint https://ift.tt/3dRlo8h