How to add column in table in SQL ?
Introduction
- To add a column in already created table, one needs to use the ALTER command along with the ADD clause.
- If in the query, it is not specified where the new column is to be added, then by default it will be added as a last column.
- One can also add a new column at the first or even after a specific column of an already created table.
- You can add one column or more than one columns at a time using a single SQL query.
(A). Adding a new column at the last of an existing table
Syntax:
1
2
3
ALTER TABLE tablename ADD (ColumnName datatype);
where,
- Tablename is the name of an already existing table to which you have to add a new column.
- Column_name is the name of the column which is to be added to an already existing table.
Example:
First, we will create a database with name “studentdb”. Then in that database we will create a table “student” and insert records into the table. We will consider the same database and also the same table for subsequent examples.
Now, we will add a new column ‘City’ to an existing table.
mysql> USE studentdb;
Database changed
mysql> SELECT *FROM student;
+ — — — — -+ — — — — — -+ — — — — — -+ — — — — — — -+
| Stud_ID | Stud_Name | Course_ID | Course_Name |
+ — — — — -+ — — — — — -+ — — — — — -+ — — — — — — -+
| 1 | Prajakta | 101 | DBMS |
| 2 | Shweta | 102 | CN |
| 3 | Nikita | 103 | OS |
| 4 | Ankita | 104 | C |
+ — — — — -+ — — — — — -+ — — — — — -+ — — — — — — -+
4 rows in set (0.00 sec)
mysql> ALTER TABLE student ADD (City VARCHAR(20));
Query OK, 4 rows affected (0.29 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> SELECT *FROM student;
+ — — — — -+ — — — — — -+ — — — — — -+ — — — — — — -+ — — — +
| Stud_ID | Stud_Name | Course_ID | Course_Name | City |
+ — — — — -+ — — — — — -+ — — — — — -+ — — — — — — -+ — — — +
| 1 | Prajakta | 101 | DBMS | NULL |
| 2 | Shweta | 102 | CN | NULL |
| 3 | Nikita | 103 | OS | NULL |
| 4 | Ankita | 104 | C | NULL |
+ — — — — -+ — — — — — -+ — — — — — -+ — — — — — — -+ — — — +
4 rows in set (0.00 sec)
