By: Sathya Narayana in MySQL Tutorials on 2010-10-24
To change the structure of the table, follow these steps:
First you'll add the price field to the table, using the ADD command and specifying the properties of the field:
mysql> ALTER TABLE book ADD (price INTEGER);
So here you've added a field called price that contains integer values.
Why would you represent price as an integer when a decimal would seem much more logical? Well, it was a deliberate mistake, so remove this field using the DROP command:
mysql> ALTER TABLE book DROP price;
Now add the field again, but this time as a decimal field, with a maximum of five characters, two of which are after the decimal point:
mysql> ALTER TABLE book ADD (prize DECIMAL(5,2));
Whoops, another boo-boo. This time, the data type of the field is correct (a decimal field), but the name of the field is wrong. Modify the field using the CHANGE command, rather than deleting it and creating a new one:
mysql> ALTER TABLE book CHANGE prize price DECIMAL(5,2);
At last, the field is just how you want it. Note that you had to specify all of the properties of the new field when you used the CHANGE command, even though you just wanted to change the field name.
You should know about a couple of other SQL commands used with ALTER TABLE. The first is the ALTER command for changing field definitions. For instance, if you wanted to set the status field to a default value of P for published, you could use the following:
mysql> ALTER TABLE book ALTER status SET DEFAULT 'P';
The other command is MODIFY, which can change the entire definition of a particular field. Say, to be awkward, you wanted to change the price field back into an integer field. Then you could use the following:
mysql> ALTER TABLE book MODIFY price INTEGER;
If you try this last command, change the field back to a decimal afterward because you'll need it to be that type when you start inserting data
This policy contains information about your privacy. By posting, you are declaring that you understand this policy:
- Your name, rating, website address, town, country, state and comment will be publicly displayed if entered.
- Aside from the data entered into these form fields, other stored data about your comment will include:
- Your IP address (not displayed)
- The time/date of your submission (displayed)
- Your email address will not be shared. It is collected for only two reasons:
- Administrative purposes, should a need to contact you arise.
- To inform you of new comments, should you subscribe to receive notifications.
- A cookie may be set on your computer. This is used to remember your inputs. It will expire by itself.
This policy is subject to change at any time and without notice.
These terms and conditions contain rules about posting comments. By submitting a comment, you are declaring that you agree with these rules:
- Although the administrator will attempt to moderate comments, it is impossible for every comment to have been moderated at any given time.
- You acknowledge that all comments express the views and opinions of the original author and not those of the administrator.
- You agree not to post any material which is knowingly false, obscene, hateful, threatening, harassing or invasive of a person's privacy.
- The administrator has the right to edit, move or remove any comment for any reason and without notice.
Failure to comply with these rules may result in being banned from submitting further comments.
These terms and conditions are subject to change at any time and without notice.
Most Viewed Articles (in MySQL )
Latest Articles (in MySQL)
- Data Science
- React Native
- Cloud Computing
- Java Beans
- Mac OS X
- Office 365
- Tech Reviews