SQL ALTER TABLE
The
SQL ALTER TABLE command is used to add, delete or modify columns in an existing
table.
You
would also use ALTER TABLE command to add and drop various constraints on an
existing table.
Syntax:
ALTER
TABLE table_name {ADD|DROP|MODIFY} column_name {data_ype};
Example:
Consider
the CUSTOMERS table having the following records:
+----+-------------+------+-----------------+---------------+
|
ID | NAME | AGE
| ADDRESS | SALARY |
+----+-------------+------+-----------------+---------------+
|
1 | Ramesh | 32
| Ahmedabad | 2000.00 |
|
2 | Ramesh | 25
| Delhi |
1500.00 |
|
3 | kaushik | 23
| Kota |
2000.00 |
|
4 | Chaitali | 25
| Mumbai | 6500.00 |
|
5 | Chaitali | 27
| Bhopal | 8500.00 |
|
6 | Komal | 22
| MP |
4500.00 |
|
7 | Muffy | 24
| Indore |
10000.00 |
+----+-------------+------+-----------------+---------------+
Following is the
example to
ADD a new column in an existing table:
ALTER
TABLE CUSTOMERS ADD GENDER char(1);
Now,
CUSTOMERS table is changed and following would be output from SELECT *FROM
CUSTOMERS statement:
+----+-------------+------+-----------------+---------------+--------------+
|
ID | NAME | AGE
| ADDRESS | SALARY | GENDER |
+----+-------------+------+-----------------+---------------+--------------+
|
1 | Ramesh | 32
| Ahmedabad | 2000.00 | NULL |
|
2 | Ramesh | 25
| Delhi |
1500.00 | NULL |
| 3 | kaushik
| 23 | Kota | 2000.00 | NULL |
|
4 | Chaitali | 25
| Mumbai | 6500.00 | NULL |
|
5 | Chaitali | 27
| Bhopal | 8500.00 | NULL |
|
6 | Komal | 22
| MP | 4500.00 | NULL |
|
7 | Muffy | 24
| Indore |
10000.00 | NULL |
+----+-------------+------+-----------------+---------------+--------------+
Following is the
example to
DROP sex column from existing table:
ALTER
TABLE CUSTOMERS DROP GENDER;
Now,
CUSTOMERS table is changed and following would be output from SELECT statement:
+----+-------------+------+-----------------+---------------+
|
ID | NAME | AGE
| ADDRESS | SALARY |
+----+-------------+------+-----------------+---------------+
|
1 | Ramesh | 32
| Ahmedabad | 2000.00 |
|
2 | Ramesh | 25
| Delhi |
1500.00 |
|
3 | kaushik | 23
| Kota |
2000.00 |
|
4 | Chaitali | 25
| Mumbai | 6500.00 |
|
5 | Chaitali | 27
| Bhopal | 8500.00 |
|
6 | Komal | 22
| MP |
4500.00 |
|
7 | Muffy | 24
| Indore |
10000.00 |
+----+-------------+------+-----------------+---------------+
0 Comments