MySQL Delete Column

Tutorials

Mysql

Topic

How to delete column or change column name?

Explanation

MySQL Delete column :

The DROP COLUMN is used to delete a column from the table.
The syntax is

ALTER TABLE tbl_name DROP col_name;

The following query drops the field marks.

mysql> ALTER TABLE student DROP COLUMN marks;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc student;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| studid  | int(10)     | YES  |     | NULL    |       |
| name    | varchar(20) | YES  |     | NULL    |       |
| address | varchar(40) | YES  |     | NULL    |       |
| phone   | int(10)     | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

Change a column name :

When we modify a column, we have to specify the attribute of the column again. The following example renames the name field to stud_name in the student table.

mysql> ALTER TABLE student CHANGE name stud_name VARCHAR(20);
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc student;
+-----------+-------------+------+-----+---------+-------+
| Field     | Type        | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| studid    | int(10)     | YES  |     | NULL    |       |
| stud_name | varchar(20) | YES  |     | NULL    |       |
| marks     | int(10)     | YES  |     | NULL    |       |
| address   | varchar(40) | YES  |     | NULL    |       |
| phone     | int(10)     | YES  |     | NULL    |       |
+-----------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

Change a column name :

If we want to change the attribute alone, we can use the same column as in the following example.


mysql> alter table student change name name varchar(40);
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc student;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| studid  | int(10)     | YES  |     | NULL    |       |
| name    | varchar(40) | YES  |     | NULL    |       |
| marks   | int(10)     | YES  |     | NULL    |       |
| address | varchar(40) | YES  |     | NULL    |       |
| phone   | int(10)     | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)