In this lesson we are going to learn how to use the MySQL alter command.
There are lots of Graphical User Interface (GUI) Tools to manage our MySQL databases as discussed on this page (link), through that modifying the structure of a database as well as the structure of tables, the field data types and much more depending on the capability of that tool. All of that comes in a handy way because these tools has a drag and drop capability. The users can easily change the data types of a certain field, rename a table, and add new columns. In this lesson we are not going to tolerate those manners (drag, drop and click).
PAIN is GAIN
If you really want to achieve something then work hard. Learning the hard way seems to be difficult, but believe it or not it is the most effective way. In the long run, you will really appreciate what you have done.
Let us now proceed to the lesson proper.
ALTER Command – this command is used to rename a table, remove a field, and many more. The following examples will explain further.
Renaming a Table
We can rename our table by using the ALTER TABLE command. Let’s try to rename our table employees to workers, to do that issue the command:
ALTER TABLE employees RENAME TO workers;
Note: if you have encoded the command correctly, it will display success message:
Query OK, 0 rows affected (0.01 sec)
Another way to rename a table (result is still the same):
RENAME TABLE employees TO workers;
Rename a Fieldname
In this example, we are going to rename the field address to location.
Issue the command:
ALTER TABLE workersCHANGE address location varchar(255);
Note: include the column definition or the data type when changing the fieldname.
Add new Field or column
In adding new fields just include the ADD clause in your ALTER TABLE command. For example, we are going to add a field in our table workers named gender. Issue the command:
ALTER TABLE workers ADD gender varchar(10);
Note: include the column definition or the data type when adding a field.
Removing a Field of column
You can remove a field in a table by adding the DROP clause in your ALTER TABLE command. Here’s how to remove the field gender in our workers table:
ALTER TABLE workers DROP gender;
Thank you for reading. Hope you enjoy this lesson, watch out for more.
