WHAT IS THE ALTER COMMAND?
As the saying goes Change is the only constant
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
ALTER TABLE `table_name` ADD COLUMN `column_name` `data_type`;
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.
SHOW COLUMNS FROM `members`;
Field Type Null Key Default Extra membership_number int(11) NO PRI NULL auto_increment full_names varchar(350) NO NULL gender varchar(6) YES NULL date_of_birth date YES NULL physical_address varchar(255) YES NULL postal_address varchar(255) YES NULL contact_number varchar(75) YES NULL varchar(255) YES NULL
We can use the script shown below to add a new field to the members table.
ALTER TABLE `members` ADD COLUMN `credit_card_number` VARCHAR(25);
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.
SHOW COLUMNS FROM `members`;
Field Type Null Key Default Extra membership_number int(11) NO PRI NULL auto_increment full_names varchar(350) NO NULL gender varchar(6) YES NULL date_of_birth date YES NULL physical_address varchar(255) YES NULL postal_address varchar(255) YES NULL contact_number varchar(75) YES NULL varchar(255) YES NULL credit_card_number varchar(25) YES
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
ALTER TABLE `members` DROP COLUMN `credit_card_number`;
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.
SHOW COLUMNS FROM `members`;
Executing the above script in MySQL workbench against the myflixdb gives us the following results.
Field Type Null Key Default Extra membership_number int(11) NO PRI NULL auto_increment full_names varchar(350) NO NULL gender varchar(6) YES NULL date_of_birth date YES NULL physical_address varchar(255) YES NULL postal_address varchar(255) YES NULL contact_number varchar(75) YES NULL varchar(255) YES NULL
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 -
DROP TABLE `sample_table`;
Let'look at an example
DROP TABLE `categories_archive`;
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.
RENAME TABLE `current_table_name` TO `new_table_name`;
Let's suppose that we want to rename the movierentals table to movie_rentals, we can use the script shown below to achieve that.
RENAME TABLE `movierentals` TO `movie_rentals`;
Executing the above script renames the table `movierentals` to `movie_rentals`.
We will now rename the movie_rentals table back to its original name.
RENAME TABLE `movie_rentals` TO `movierentals`;
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.
SHOW COLUMNS FROM `members`;
Executing the above script in MySQL workbench against the myflixdb gives us the following results.
Field Type Null Key Default Extra membership_number int(11) NO PRI NULL auto_increment full_names varchar(150) NO NULL gender varchar(6) YES NULL date_of_birth date YES NULL physical_address varchar(255) YES NULL postal_address varchar(255) YES NULL contact_number varchar(75) YES NULL varchar(255) YES NULL
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 -
ALTER TABLE `members` CHANGE COLUMN `full_names` `fullname` char(250) NOT NULL;
Executing the above script in MySQL workbench against myflixdb and then executing the show columns script given above gives the following results.
Field Type Null Key Default Extra membership_number int(11) NO PRI NULL auto_increment fullnames char(250) NO NULL gender varchar(6) YES NULL date_of_birth date YES NULL physical_address varchar(255) YES NULL postal_address varchar(255) YES NULL contact_number varchar(75) YES NULL varchar(255) YES NULL
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.
ALTER TABLE `members`MODIFY `fullname` char(50) NOT NULL;
Executing the above script in MySQL workbench against myflixdb and then executing the show columns script given above gives the following results shown below.
Field Type Null Key Default Extra membership_number int(11) NO PRI NULL auto_increment fullnames char(50) NO NULL gender varchar(6) YES NULL date_of_birth date YES NULL physical_address varchar(255) YES NULL postal_address varchar(255) YES NULL contact_number varchar(75) YES NULL varchar(255) YES NULL
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.
ALTER TABLE `members` ADD `date_of_registration` date NULL AFTER `date_of_birth`;
Executing the above script in MySQL workbench against myflixdb and then executing the show columns script given above gives the following results shown below.
Note: The Hilighted row is added after date_of_birth cloumn
Field Type Null Key Default Extra membership_number int(11) NO PRI NULL auto_increment fullnames char(50) NO NULL gender varchar(6) YES NULL date_of_birth date YES NULL date_of_registration date YES NULL physical_address varchar(255) YES NULL postal_address varchar(255) YES NULL contact_number varchar(75) YES NULL varchar(255) YES NULL
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
Posts
![IVRAMANA](https://draft.blogger.com/img/profile_blank_gray.jpg" title="IVRAMANA)
0 comments:
Post a Comment