Servage Magazine

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

MySQL Commands – Part 5

Monday, July 14th, 2014 by Servage

qqAUTO_INCREMENT data type

As its name implies, a column given this data type will set the value of its contents to that of the column entry in the previously inserted row, plus 1.

This is your introduction to the ALTER command, which is very similar to CREATE.

ALTER operates on an existing table, and can add, change, or delete columns. Our example adds a column named id with the following characteristics:

INT UNSIGNED

Makes the column take an integer large enough for you to store more than four billion records in the table.

NOT NULL

Ensures that every column has a value. Many programmers use NULL in a field to indicate that the field doesn’t have a value, but that would allow duplicates, which would violate the whole reason for this column’s existence. So, we disallow NULL values.

AUTO_INCREMENT

Causes MySQL to set a unique value for this column in every row, as described earlier. We don’t really have control over the value that this column will take in each row, but we don’t care: all we care about is that we are guaranteed a unique value.

KEY

An auto-incrementing column is useful as a key, because you will tend to search for rows based on this column.

Each entry in the column id will now have a unique number, with the first starting at 1 and the others counting upwards from there. And whenever a new row is inserted, its id column will automatically be given the next number in the sequence.

CREATE TABLE classics (

author VARCHAR(128),

title VARCHAR(128),

type VARCHAR(16),

year CHAR(4),

id INT UNSIGNED NOT NULL AUTO_INCREMENT KEY) ENGINE MyISAM;

If you wish to check whether the column has been added, use the following command to view the table’s columns and data types:

DESCRIBE classics;

Now that we’ve finished with it, the id column is no longer needed, so if you created it, you should now remove the column using the command.

Adding data to a table

To add data to a table, use the INSERT command. Let’s see this in action by populating the table classics with the data from Table 8-1, using one form of the INSERT command.

Populating the classics table.

INSERT INTO classics(author, title, type, year)

VALUES(‘Mark Twain’,’The Adventures of Tom Sawyer’,’Fiction’,’1876′);

INSERT INTO classics(author, title, type, year)

VALUES(‘Jane Austen’,’Pride and Prejudice’,’Fiction’,’1811′);

INSERT INTO classics(author, title, type, year)

VALUES(‘Charles Darwin’,’The Origin of Species’,’Non-Fiction’,’1856′);

INSERT INTO classics(author, title, type, year)

VALUES(‘Charles Dickens’,’The Old Curiosity Shop’,’Fiction’,’1841′);

INSERT INTO classics(author, title, type, year)

VALUES(‘William Shakespeare’,’Romeo and Juliet’,’Play’,’1594′);

After every second line, you should see a “Query OK” message. Once all lines have been entered, type the following command, which will display the table’s contents.

SELECT * FROM classics;

Let’s go back and look at how we used the INSERT command. The first part, INSERT INTO classics, tells MySQL where to insert the following data. Then, within parentheses, the four column names are listed—author, title, type, and year—all separated by commas. This tells MySQL that these are the fields into which the data is to be inserted.  continued…

Sources for further reading

MySQL Commands - Part 5, 2.7 out of 5 based on 3 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.