Inserting Data into a Table - Page 4

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 of this command:

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 these commands:

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

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

Note that in the second form of the INSERT command, the order in which you list the columns must match the order in which you list the values. 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 we use to view data stored in your database tables, SELECT, is 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 data retrieval and presentation. As, at this point in our experience with databases, we only need fairly simple lists of results, we'll consider only the simpler forms of the SELECT command.

This 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, your results will resemble this:

+----+-------------------------------------------------
--------------+------------+
| ID | JokeText
              | JokeDate   |
+----+-------------------------------------------------
--------------+------------+
|  1 | Why did the chicken cross the road? To get to th
e 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 wanted to see. 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 specified maximum number of characters when it displays 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 our examples have fetched all the entries in the table. But if we add what's called a WHERE clause (for reasons that will become obvious in a moment) to a SELECT command, we can limit which entries are returned as results. Consider this 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 that contain 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.

Additional 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%" 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 later, when we need them.