Inserting Data into a Table

by Kevin Yank of SitePoint.com

Our database is created and our table is built; all that's left is to put some actual jokes into our database. The command for inserting data into our database is called (appropriately enough) INSERT. There are two basic forms for this command that you can choose from:

mysql> INSERT INTO <table name> SET -> columnName1 = value1, -> columnName2 = value2, -> ... -> ;

mysql> INSERT INTO <table name> -> (columnName1, columnName2, ...) -> VALUES (value1, value2, ...);

So to add a joke to our table, we can choose from either of the following two commands:

mysql> INSERT INTO Jokes SET -> JokeText = "Why did the chicken cross theroad? To get to the other side!", -> JokeDate = "2000-04-01";

mysql> INSERT INTO Jokes -> (JokeText, JokeDate) VALUES ( -> "Why did the chicken cross the road? Toget to the other side!", -> "2000-04-01" -> );

Note that in the second form of the INSERT command, the order you list the columns in must match with the order you list the values in. Otherwise, the order of the columns doesn't matter, as long as you give values for all required fields.

Now that you know how to add entries to a table, let's see how we can view those entries.

Viewing Stored Data

The command for viewing data stored in your database tables, SELECT, is easily the most complicated command in the SQL language. The reason for this complexity is that the chief strength of a database is its flexibility in retrieving and presenting data. Since at this point in our experience with databases we only have need of fairly simple lists of results, we'll limit ourselves to considering only the simpler forms of the select command.

The following command will list everything stored in the Jokes table:

mysql> SELECT * FROM Jokes;

Read aloud, this command says "select everything from Jokes". If you try this command, you'll see something resembling the following:

+----+---------------------------------------------------------------+------------+| ID | JokeText | JokeDate |+----+---------------------------------------------------------------+------------+| 1 | Why did the chicken cross the road? To get to the other side! | 2000-04-01 |+----+---------------------------------------------------------------+------------+1 row in set (0.05 sec)

It looks a little messed up, because the text in the JokeText column is too long for the table to fit properly on the screen. For this reason, you might want to tell MySQL to leave out the JokeText column. The command for doing this is as follows:

mysql> SELECT ID, JokeDate FROM Jokes;

This time instead of telling it to "select everything", we told it precisely which columns we were interested in seeing. The results look like this:

+----+------------+| ID | JokeDate |+----+------------+| 1 | 2000-04-01 |+----+------------+1 row in set (0.00 sec)

Not bad, but we'd like to see at least some of the Joke text, wouldn't we? In addition to listing the columns that we want the select command to show us, we can modify those columns with "functions". One function, called LEFT, lets us tell MySQL to display up to a maximum of some specific number of characters when displaying a column. For example, let's say we wanted to see only the first 20 characters of the JokeText column:

mysql> SELECT ID, LEFT(JokeText,20), JokeDate FROM Jokes;+----+----------------------+------------+| ID | LEFT(JokeText,20) | JokeDate |+----+----------------------+------------+| 1 | Why did the chicken | 2000-04-01 |+----+----------------------+------------+1 row in set (0.05 sec)

See how that worked? Another useful function is COUNT, which simply lets us count the number of results returned. So, for example, if we wanted to find out how many jokes were stored in our table, we could use the following command:

mysql> SELECT COUNT(*) FROM Jokes;+----------+| COUNT(*) |+----------+| 1 |+----------+1 row in set (0.06 sec)

As we can see, we only have one joke in our table.

So far, all of our examples have fetched all the entries in the table. By adding what's called a "WHERE clause" (for reasons that will become obvious in a moment) to a SELECT command, we can limit what entries are returned as results. Take the following example:

mysql> SELECT COUNT(*) FROM Jokes -> WHERE JokeDate >= "2000-01-01";

This query will count the number of jokes that have dates "greater than or equal to" January 1st, 2000. "Greater than or equal to" when dealing with dates means "on or after".

Another variation on this theme lets you search for entries containing a certain piece of text. Check out this query:

mysql> SELECT JokeText FROM Jokes -> WHERE JokeText LIKE "%chicken%";

This query displays the text of all jokes that contain the word chicken in their JokeText column. The LIKE keyword tells MySQL that the named column must match the given pattern. In this case, the pattern we've used is "%chicken%". The % signs here indicate that the word chicken may be preceded and/or followed by any string of text.

Conditions may also be combined in the WHERE clause to further restrict results. For example, to display knock-knock jokes from April 2000 only, we could use the following query:

mysql> SELECT JokeText FROM Jokes WHERE -> JokeText LIKE "%knock knock%" AND -> JokeDate >= "2000-04-01" AND -> JokeDate < "2000-05-01";

Enter a few more jokes into the table and experiment with SELECT statements a little. A good familiarity with the SELECT statement will come in handy later in this series.
There's a lot more you can do with the SELECT statement, but we'll save looking at some of its more advanced features for when we need them. If you're too curious to wait, the MySQL Reference Manual has got plenty of information on the subject.