SQL alter table program

Rajeev Ranjan Tiwari

SQL - ALTER TABLE

ALTER TABLE Statement का इस्तेमाल दिए गए table के column(s) को add, drop या modify करने के लिए किया जाता है |

Syntax for ADD Column in a Table Statement

For Single Column

ALTER TABLE table_name
ADD column_name data_type;

For Multiple Columns

ALTER TABLE table_name
ADD column_name1 data_type,
ADD column_name2 data_type,
ADD .......,
ADD column_nameN data_type;

Example for ADD Column in a Table in SQL

Before Adding Column

Table name : mytable
+------+------------+-----------+
| id   | first_name | last_name |
+------+------------+-----------+
|    1 | Rakesh     | Raman     |
+------+------------+-----------+

Example पर 'mytable' नाम के table पर 'marks' नाम का column add किया गया है |

Source Code :

ALTER TABLE mytable;
ADD marks INT(4);

After Adding Column

Table name : mytable
+------+------------+-----------+-------+
| id   | first_name | last_name | marks |
+------+------------+-----------+-------+
|    1 | Rakesh     | Raman     |  NULL |
+------+------------+-----------+-------+

Add More than One Columns in SQL

Before Adding Column

Table name : mytable
+------+------------+-----------+
| id   | first_name | last_name |
+------+------------+-----------+
|    1 | Rakesh     | Raman     |
+------+------------+-----------+

Example पर mytable इस table पर 'Physics', 'Chemistry' और 'Maths' इन तीन columns को add किया गया है |

Source Code :

ALTER TABLE mytable
ADD Physics INT(3),
ADD Chemistry INT(3),
ADD Maths INT(3);

After Adding Column

Table name : mytable
+------+------------+-----------+---------+-----------+-------+
| id   | first_name | last_name | Physics | Chemistry | Maths |
+------+------------+-----------+---------+-----------+-------+
|    1 | Rakesh     | Raman     |    NULL |      NULL |  NULL |
+------+------------+-----------+---------+-----------+-------+

---

DROP Column in a table in SQL

ALTER TABLE Statement का इस्तेमाल table से column DROP करने के लिए भी किया जाता है |

Syntax for DROP Column in a Table in SQL

for Single Column

ALTER TABLE table_name
DROP COLUMN column_name;

for Multiple Columns

ALTER TABLE table_name
DROP COLUMN column_name1,
DROP COLUMN column_name2,
........,
DROP COLUMN column_nameN;

Example for DROP Column in a table in SQL

Before Deleting Column

Table name : mytable
+------+------------+-----------+---------+-----------+-------+
| id   | first_name | last_name | Physics | Chemistry | Maths |
+------+------------+-----------+---------+-----------+-------+
|    1 | Rakesh     | Raman     |    NULL |      NULL |  NULL |
+------+------------+-----------+---------+-----------+-------+

Example पर mytable इस table के 'Maths' इस column को delete किया गया है |

Source Code :

ALTER TABLE mytable
DROP COLUMN Maths;

After Deleting Column

Table name : mytable
+------+------------+-----------+---------+-----------+
| id   | first_name | last_name | Physics | Chemistry |
+------+------------+-----------+---------+-----------+
|    1 | Rakesh     | Raman     |    NULL |      NULL |
+------+------------+-----------+---------+-----------+

Example for DROP More than One Columns in a table in SQL

Before Deleting Column

Table name : mytable
+------+------------+-----------+---------+-----------+
| id   | first_name | last_name | Physics | Chemistry |
+------+------------+-----------+---------+-----------+
|    1 | Rakesh     | Raman     |    NULL |      NULL |
+------+------------+-----------+---------+-----------+

Example पर mytable इस table के 'Physics' और 'Chemistry' इन columns को delete किया गया है |

Source Code :

ALTER TABLE mytable
DROP COLUMN Physics,
DROP COLUMN Chemistry;

After Deleting Column

Table name : mytable
+------+------------+-----------+
| id   | first_name | last_name |
+------+------------+-----------+
|    1 | Rakesh     | Raman     |
+------+------------+-----------+

---

MODIFY Column in a table in SQL

ALTER TABLE Statement का इस्तेमाल column को modify करने के लिए भी किया जाता है |

Syntax for MODIFY Column in a Table in SQL

for Single Column

ALTER TABLE table_name
MODIFY column_name column_type;

for Multiple Columns

ALTER TABLE table_name
MODIFY column_name1 data_type;
MODIFY column_name2 data_type;
........,
MODIFY column_nameN data_type;

Example पर mytable इस table के last_name इस column का data type modify किया गया है |

Source Code :

ALTER TABLE mytable
MODIFY last_name TEXT;

Thanks friend

Posted by

Rajeev Ranjan Tiwari