Servage Magazine

Information about YOUR hosting company – where we give you a clear picture of what we think and do!

MySQL Commands – Part 6

Wednesday, July 16th, 2014 by Servage

figure2Continued from part 5…

The second line of each INSERT command contains the keyword VALUES followed by four strings within parentheses, separated by commas. This supplies MySQL with the four values to be inserted into the four columns previously specified. (As always, my choice of where to break the lines was arbitrary.)

Each item of data will be inserted into the corresponding column, in a one-to-one correspondence. If you accidentally listed the columns in a different order from the data, the data would go into the wrong columns. The number of columns must match the number of data items.

Renaming a table

Renaming a table, like any other change to the structure or meta-information of a table, is achieved via the ALTER command. So, for example, to change the name of the table classics to pre1900, you would use the following command:

ALTER TABLE classics RENAME pre1900;

If you try that command, you should rename the table back again by entering the following, so that later examples in this series of articles will work as printed:

ALTER TABLE pre1900 RENAME classics;

Changing the data type of a column

Changing a column’s data type also makes use of the ALTER command, this time in conjunction with the MODIFY keyword. So to change the data type of the column year from CHAR(4) to SMALLINT (which requires only two bytes of storage and so will save disk space), enter the following:

ALTER TABLE classics MODIFY year SMALLINT;

When you do this, if the data type conversion makes sense to MySQL, it will automatically change the data while keeping its meaning. In this case, it will change each string to a comparable integer, and so on, as the string is recognizable as referring to an integer.

Adding a new column

Let’s suppose that you have created a table and populated it with plenty of data, only to discover you need an additional column. Not to worry. Here’s how to add the new column pages, which will be used to store the number of pages in a publication:

ALTER TABLE classics ADD pages SMALLINT UNSIGNED;

This adds the new column with the name pages using the UNSIGNED SMALLINT data type, sufficient to hold a value of up to 65,535—hopefully that’s more than enough for any book ever published!

If you now ask MySQL to describe the updated table using the DESCRIBE command, as follows, you will see the change has been made.

DESCRIBE classics;

Renaming a column

Looking again, you may decide that having a column named type can be confusing, because that is the name used by MySQL to identify data types. Again, no problem—let’s change its name to category, like this:

ALTER TABLE classics CHANGE type category VARCHAR(16);

Note the addition of VARCHAR(16) on the end of this command. That’s because the CHANGE keyword requires the data type to be specified, even if you don’t intend to change it, and VARCHAR(16) was the data type specified when that column was initially created as type.

Sources for further reading

MySQL Commands – Part 6, 4.0 out of 5 based on 2 ratings
You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

No comments yet (leave a comment)

You are welcome to initiate a conversation about this blog entry.

Leave a comment

You must be logged in to post a comment.