Search Marketing

   
   

Change Database Table Structure Using Alter Statement & Drop Option

Fri May 02, 2008 9:53 pm
<<     >>
Comments: 0 Views: 3609

You would use the following statements to modify existing tables structures in MySQL.

If we are using the following database table structure.

mysql> explain Name;
+-------+-----------+------+-----+-------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-----------+------+-----+-------------------+----------------+
| ID | int(11) | NO | PRI | NULL | auto_increment |
| Name | char(100) | YES | | NULL | |
| Date | timestamp | NO | | CURRENT_TIMESTAMP | |
+-------+-----------+------+-----+-------------------+----------------+

And wish to add another field to it, then you will need to use the “alter” statement.

1.Add a field for Address before the Date field.

mysql> alter table Name add Address char(100) after Name;
Query OK, 5 rows affected (0.18 sec)
Records: 5 Duplicates: 0 Warnings: 0

mysql> explain Name;
+---------+-----------+------+-----+-------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------+-----------+------+-----+-------------------+----------------+
| ID | int(11) | NO | PRI | NULL | auto_increment |
| Name | char(100) | YES | | NULL | |
| Address | char(100) | YES | | NULL | |
| Date | timestamp | NO | | CURRENT_TIMESTAMP | |
+---------+-----------+------+-----+-------------------+----------------+

2.Notice that the new field Address is before the Date field. If we wanted to change the “Type” of field from char(100) to an integer field we would use the following statement with “modify”.

mysql> alter table Name Modify Address int;
Query OK, 5 rows affected (0.00 sec)
Records: 5 Duplicates: 0 Warnings: 0

mysql> explain Name;
+---------+-----------+------+-----+-------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------+-----------+------+-----+-------------------+----------------+
| ID | int(11) | NO | PRI | NULL | auto_increment |
| Name | char(100) | YES | | NULL | |
| Address | int(11) | YES | | NULL | |
| Date | timestamp | NO | | CURRENT_TIMESTAMP | |
+---------+-----------+------+-----+-------------------+----------------+

3.Add if we wanted to remove the Address field altogether, including whatever data were present in the field, we would use “drop” in the alter statement.

mysql> alter table Name drop Address;
Query OK, 5 rows affected (0.01 sec)
Records: 5 Duplicates: 0 Warnings: 0

mysql> explain Name;
+-------+-----------+------+-----+-------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-----------+------+-----+-------------------+----------------+
| ID | int(11) | NO | PRI | NULL | auto_increment |
| Name | char(100) | YES | | NULL | |
| Date | timestamp | NO | | CURRENT_TIMESTAMP | |
+-------+-----------+------+-----+-------------------+----------------+



Comments
Name:
*
URL: http: (ex. cnn.com)
Comment:
*
Number:
Math (15 + 3)
* required
SubCategories
Books

© 2017 Christonium LLC

Christonium.com
|
Terms of Use
|
Privacy
ccc