Change the field name from “full_names” to “fullname.The full names field in the members table is of varchar data type and has a width of 150. RENAME TABLE `movie_rentals` TO `movierentals` We will now rename the movie_rentals table back to its original name. RENAME TABLE `movierentals` TO `movie_rentals` Įxecuting the above script renames the table `movierentals` to `movie_rentals`. 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 `current_table_name` TO `new_table_name` The rename command has the following basic syntax. Renaming a table does not make it to lose any data is contained within it. The rename command is used to change the name of an existing database object(like Table,Column) to a new name. Let’look at an example DROP TABLE `categories_archive` Įxecuting the above script deletes the table named ` categories_archive ` from our database. The syntax to DROP a table from Database is as follow – DROP TABLE `sample_table` Notice that the credit card number has been dropped from the fields list. SHOW COLUMNS FROM `members` Įxecuting the above script in MySQL workbench against the myflixdb gives us the following results. Let’s now look at the columns in the members table to confirm if our column has been dropped. We can use the following script ALTER TABLE `members` DROP COLUMN `credit_card_number` Įxecuting the above script drops the column credit_card_number from the members table Suppose the online billing functionality will take some time and we want to DROP the credit card column In our previous example on the Alter Command, we added a column named credit card number to the members table. Let’s now look at practical examples that make use of the DROP command. Delete an object (like Table, Column)from a database.The data contained in the members’ data is not affected by the addition of the new column. SHOW COLUMNS FROM `members` FieldĪs you can see from the results returned, credit card number has been added to the members table. Executing the show columns script gives us the following results. ALTER TABLE `members` ADD COLUMN `credit_card_number` VARCHAR(25) Įxecuting 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. We can use the script shown below to add a new field to the members table. The script shown below helps us to do that. Let’s first look at the structure of the members table before we make any amendments. Towards that end, we have been asked to add a field for the credit card number in our members table. Let’s suppose that Myflix has introduced online billing and payments. “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’.“ALTER TABLE `table_name`” is the command that tells MySQL server to modify the table named `table_name`.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` 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. The alter command comes in handy in such situations. They don’t want to lose the existing data but just want to incorporate the new information. Our database users are using it and then they realize some of the vital information was left out in the design phase. Let’s suppose that we have completed our database design and it has been implemented. 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. MySQL provides the ALTER function that helps us incorporate the changes to the already existing database design. As business requirements change, Database designs need changing as well. With time business requirements change as well. As the saying goes Change is the only constant
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |