لطفا منتظر باشید ...
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 need only fairly simple lists of results, we'll
just consider 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 the
other side! | 2000-04-01 |
+----+---------------------------------------------------
------------+------------+
1 row in set (0.05 sec)
It looks a little disorganised 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 you can see, we have just 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 book.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.
