With time business requirements change as well. As business requirements change, Database designs need changing as well. MySQL provides the ALTER function that helps us incorporate the changes to the already existing database design. The alter command is used to modify an existing database, table, view or other database objects that might need to change during the life cycle of a database. Let’s suppose that we have completed our database design and it has been implemented. Our database users are using it and then they realize some of the vital information was left out in the design phase. They don’t want to lose the existing data but just want to incorporate the new information. The alter command comes in handy in such situations. We can use the alter command to change the data type of a field from say string to numeric, change the field name to a new name or even add a new column in a table.

Alter- syntax

The basic syntax used to add a column to an already existing table is shown below HERE

“ALTER TABLE table_name” is the command that tells MySQL server to modify the table named table_name.

“ADD COLUMN column_name data_type” is the command that tells MySQL server to add a new column named column_name with data type `data_type’.

Let’s suppose that Myflix has introduced online billing and payments. Towards that end, we have been asked to add a field for the credit card number in our members table. We can use the ALTER command to do that. Let’s first look at the structure of the members table before we make any amendments. The script shown below helps us to do that. We can use the script shown below to add a new field to the members table. Executing the above script in MySQL against the Myflixdb adds a new column named credit card number to the members table with VARCHAR as the data type. Executing the show columns script gives us the following results. As you can see from the results returned, credit card number has been added to the members table. The data contained in the members’ data is not affected by the addition of the new column.

WHAT IS THE DROP COMMAND?

The DROP command is used to

Delete a database from MySQL server Delete an object (like Table , Column)from a database.

Let’s now look at practical examples that make use of the DROP command. In our previous example on the Alter Command, we added a column named credit card number to the members table. Suppose the online billing functionality will take some time and we want to DROP the credit card column We can use the following script Executing the above script drops the column credit_card_number from the members table Let’s now look at the columns in the members table to confirm if our column has been dropped. Executing the above script in MySQL workbench against the myflixdb gives us the following results. Notice that the credit card number has been dropped from the fields list. DROP TABLE The syntax to DROP a table from Database is as follow – Let’look at an example Executing the above script deletes the table named categories_archive from our database.

WHAT IS THE RENAME COMMAND?

The rename command is used to change the name of an existing database object(like Table,Column) to a new name. Renaming a table does not make it to lose any data is contained within it. Syntax:- The rename command has the following basic syntax. Let’s suppose that we want to rename the movierentals table to movie_rentals, we can use the script shown below to achieve that. Executing the above script renames the table movierentals to movie_rentals. We will now rename the movie_rentals table back to its original name.

CHANGE KEYWORD

Change Keywords allows you to

Change Name of Column Change Column Data Type Change Column Constraints

Let’s look at an example. The full names field in the members table is of varchar data type and has a width of 150. Executing the above script in MySQL workbench against the myflixdb gives us the following results. Suppose we want to

Change the field name from “full_names” to “fullname Change it to char data type with a width of 250 Add a NOT NULL constraint

We can accomplish this using the change command as follows – Executing the above script in MySQL workbench against myflixdb and then executing the show columns script given above gives the following results.

MODIFY KEYWORD

The MODIFY Keyword allows you to

Modify Column Data Type Modify Column Constraints

In the CHANGE example above, we had to change the field name as well other details. Omitting the field name from the CHANGE statement will generate an error. Suppose we are only interested in changing the data type and constraints on the field without affecting the field name, we can use the MODIFY keyword to accomplish that. The script below changes the width of “fullname” field from 250 to 50. Executing the above script in MySQL workbench against myflixdb and then executing the show columns script given above gives the following results shown below.

AFTER KEYWORD

Suppose that we want to add a new column at a specific position in the table. We can use the alter command together with the AFTER keyword. The script below adds “date_of_registration” just after the date of birth in the members table. Executing the above script in MySQL workbench against myflixdb and then executing the show columns script given above gives the following results shown below.

Summary

	The alter command is used when we want to modify a database or any object contained in the database.

	The drop command is used to delete databases from MySQL server or objects within a database.

	The rename command is used to change the name of a table to a new table name.

	The Change keyword allows you to change a column name , data type and constraints

	The Modify Keyword allows you to modify a column data type and constraints

	The After keyword is used to specify position of a column in a table