Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
How to add column using alter in MySQL?
Following is the syntax to add column using alter in MySQL:
alter table yourTableName add column yourColumnName yourDataType default yourValue;
Let us first create a table:
mysql> create table alterTableDemo -> ( -> Id int, -> Name varchar(10) -> ); Query OK, 0 rows affected (0.69 sec)
Let us check the description of the table using DESC command. This displays Field, Type, Key, etc. of the table:
mysql> desc alterTableDemo;
This will produce the following output
+-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | Id | int(11) | YES | | NULL | | | Name | varchar(10) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ 2 rows in set (0.01 sec)
Now, add column Age with default value 18. If user won’t supply value for column Age then MySQL will use the default value for Age column. Following is the query to add column using alter command.
mysql> alter table alterTableDemo add column Age int default 18; Query OK, 0 rows affected (0.67 sec) Records: 0 Duplicates: 0 Warnings: 0
Let us check the table description once again:
mysql> desc alterTableDemo;
This will produce the following output
+-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | Id | int(11) | YES | | NULL | | | Name | varchar(10) | YES | | NULL | | | Age | int(11) | YES | | 18 | | +-------+-------------+------+-----+---------+-------+ 3 rows in set (0.00 sec)
Let us insert record in the table using insert command.
Following is the query
mysql> insert into alterTableDemo(Id,Name,Age) values(100,'Chris',24); Query OK, 1 row affected (0.16 sec) mysql> insert into alterTableDemo(Id,Name) values(101,'Robert'); Query OK, 1 row affected (0.25 sec)
Following is the query to display all records from the table using select statement:
mysql> select *from alterTableDemo;
Following is the output. Since we haven’t set age for ‘Robert’, therefore the default 18 would be set for Age:
+------+--------+------+ | Id | Name | Age | +------+--------+------+ | 100 | Chris | 24 | | 101 | Robert | 18 | +------+--------+------+ 2 rows in set (0.00 sec)
124 Views
