MySQL FAQs - Understanding SQL CREATE, ALTER and DROP Statements

Home >> FAQs/Tutorials >> MySQL Tutorials

By: FYIcenter.com

Part: 1 2 3 4 5

(Continued from previous part...)

How To Add a New Column to an Existing Table?

If you have an existing table with existing data rows, and want to add a new column to that table, you can use the "ALTER TABLE ... ADD COLUMN" statement. The tutorial script below shows you a good example:

mysql> ALTER TABLE tip ADD COLUMN author VARCHAR(40);
Query OK, 1 row affected (0.18 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> SHOW COLUMNS FROM tip;
+-------------+--------------+------+-----+---------+-------
| Field       | Type         | Null | Key | Default | Extra 
+-------------+--------------+------+-----+---------+-------
| id          | int(11)      | NO   | PRI |         |       
| subject     | varchar(80)  | NO   |     |         |       
| description | varchar(256) | NO   |     |         |       
| create_date | date         | YES  |     | NULL    |       
| author      | varchar(40)  | YES  |     | NULL    |       
+-------------+--------------+------+-----+---------+-------
5 rows in set (0.01 sec)

This SQL script added a new column called "author" to the "tip" table. NULL values were added to this column on all existing data rows.

How To Delete an Existing Column in a Table?

If you have an existing column in a table and you do not need that column any more, you can delete it with "ALTER TABLE ... DROP COLUMN" statement. Here is a tutorial script to delete an existing column:

mysql> ALTER TABLE tip DROP COLUMN create_date;
Query OK, 1 row affected (0.48 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM tip;
+----+-------------+-------------------------+--------+
| id | subject     | description             | author |
+----+-------------+-------------------------+--------+
|  1 | Learn MySQL | Visit dev.fyicenter.com | NULL   |
+----+-------------+-------------------------+--------+
1 row in set (0.00 sec)

As you can see the column "create_date" is gone.

How To Rename an Existing Column in a Table?

If you have an existing column in a table and you want to change the column name, you can use the "ALTER TABLE ... CHANGE" statement. This statement allows you to change the name of a column, and its definition. The tutorial script below gives you a good example:

mysql> ALTER TABLE tip CHANGE COLUMN subject 
   title VARCHAR(60);
Query OK, 1 row affected (0.51 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> SHOW COLUMNS FROM tip;
+-------------+--------------+------+-----+---------+-------
| Field       | Type         | Null | Key | Default | Extra 
+-------------+--------------+------+-----+---------+-------
| id          | int(11)      | NO   | PRI |         |       
| title       | varchar(60)  | YES  |     | NULL    |       
| description | varchar(256) | NO   |     |         |       
| author      | varchar(40)  | YES  |     | NULL    |       
+-------------+--------------+------+-----+---------+-------
4 rows in set (0.02 sec)

How To Rename an Existing Table?

If you want to rename an existing table, you can use the "ALTER TABLE ... RENAME TO" statement. The tutorial script below shows you a good example:

mysql> ALTER TABLE tip RENAME TO faq;
Query OK, 0 rows affected (0.01 sec)

mysql> SELECT * FROM faq;
+----+-------------+-------------------------+--------+
| id | title       | description             | author |
+----+-------------+-------------------------+--------+
|  1 | Learn MySQL | Visit dev.fyicenter.com | NULL   |
+----+-------------+-------------------------+--------+
1 row in set (0.00 sec)

(Continued on next part...)

Part: 1 2 3 4 5

MySQL Tutorials:

More...