Alter Command

Alter is the DDL command of SQL. It is use to update structure on "database object". To modify ,add , drop ,change ,rename etc and use after before clause.

ADD Command

Alter command ADD is use to add column in table with datatype or constraint.

Syntax

ALERT Table table_name ADD Column column_name datatype constraint;

Example

+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
1 row in set (0.02 sec)

mysql> ALTER Table personal ADD Column fname char(30) DEFAULT NULL;
Query OK, 1 row affected (0.17 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> EXPLAIN personal;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id    | int(11)  | YES  |     | NULL    |       |
| fname | char(30) | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
2 rows in set (0.01 sec)

mysql>

DROP Command

Alter command DROP is use to remove column in table with datatype or constraint.

Syntax

ALERT Table table_name DROP Column column_name;

Example

mysql> ALTER Table personal DROP Column fname;
Query OK, 1 row affected (0.11 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> EXPLAIN personal;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
1 row in set (0.02 sec)

mysql>

CHANGE Command

Alter command CHANGE is use to change column in table with datatype or constraint.

Syntax

ALERT Table table_name DROP Change old column_name new_column datatype constraint;

Example

mysql> ALTER Table personal CHANGE Column lname sir_name char(30);
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> EXPLAIN personal;
+----------+----------+------+-----+---------+-------+
| Field    | Type     | Null | Key | Default | Extra |
+----------+----------+------+-----+---------+-------+
| id       | int(11)  | YES  |     | NULL    |       |
| fname    | char(30) | YES  |     | NULL    |       |
| sir_name | char(30) | YES  |     | NULL    |       |
+----------+----------+------+-----+---------+-------+
3 rows in set (0.01 sec)

mysql>

MODIFY Command

Alter command MODIFY is use to upgrade column in table with datatype or constraint.

Syntax

ALERT Table table_name DROP Change old column_name new_column datatype constraint;

Example

mysql> ALTER Table personal MODIFY sir_name int;
Query OK, 1 row affected (0.09 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> EXPLAIN personal;
+----------+----------+------+-----+---------+-------+
| Field    | Type     | Null | Key | Default | Extra |
+----------+----------+------+-----+---------+-------+
| id       | int(11)  | YES  |     | NULL    |       |
| fname    | char(30) | YES  |     | NULL    |       |
| sir_name | int(11)  | YES  |     | NULL    |       |
+----------+----------+------+-----+---------+-------+
3 rows in set (0.01 sec)

mysql>

AFTER Command

AFTER is help to add, column after the first column that we choice.

Syntax

ALERT Table table_name ADD column_name datatype AFTER column_name;

Example

mysql> ALTER Table personal ADD lname char(30) AFTER fname;
Query OK, 1 row affected (0.14 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> DESCRIBE personal;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id    | int(11)  | YES  |     | NULL    |       |
| fname | char(30) | YES  |     | NULL    |       |
| lname | char(30) | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
3 rows in set (0.01 sec)

mysql>

---

RENAME Command

Rename command use to change the name of table and user. It also use with alter command.

Syntax

RENAME Table old_name TO new_name;

Example

mysql> RENAME Table personal TO mytable;
Query OK, 0 rows affected (0.02 sec)

mysql> DESCRIBE mytable;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id    | int(11)  | YES  |     | NULL    |       |
| fname | char(30) | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
2 rows in set (0.01 sec)

mysql> DESCRIBE personal;
ERROR 1146 (42S02): Table 'test.personal' doesn't exist
mysql>

Syntax

ALTER Table old_name RENAME TO new_name;

Example

mysql> ALTER Table mytable RENAME TO personal;
Query OK, 0 rows affected (0.02 sec)

mysql> DESCRIBE personal;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id    | int(11)  | YES  |     | NULL    |       |
| fname | char(30) | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
2 rows in set (0.01 sec)

mysql>