لطفا منتظر باشید ...
Chapter 9: Advanced SQL
As we worked through our example of the Internet Joke Database Website,we had opportunities to explore most aspects of Structured Query Language
(SQL). From the basic form of a CREATE TABLE query, to
the two syntaxes of INSERT queries, you probably know many
of these commands by heart now.In this chapter, in an effort to tie up loose ends, we'll look at a
few more SQL tricks that we haven't seen before, either because they were
too advanced, or simply because "it didn't come up". As is typical, most of
these will expand on our knowledge of what is already the most complex and
potentially confusing SQL command available to us: the SELECT query.
Sorting SELECT Query Results
Long lists of information are always easier
to use when they're provided in some kind of order. To find a single author
in a list from our Authors table, for example,
could become an exercise in frustration if we had more than a few dozen registered
authors in our database. While at first it might appear that they are sorted
in order of database insertion, with the oldest records first and the newest
records last, you'll quickly notice that deleting records from the database
leaves invisible gaps in this order, which get filled in by newer entries
as they're inserted.What this amounts to is no reliable built-in result sorting capabilities
from SELECT queries. Fortunately, there is another optional
part of the SELECT query that lets us specify a column
by which to sort our table of results. Let's say we wanted to print out a
listing of the entries in our Authors table
for future reference. If you'll recall, this table has three columns: ID, Name, and EMail. Since ID isn't
really interesting in and of itself (it just provides a means to associate
entries in this table with entries in the Jokes table),
we will usually just list the remaining two columns when we work with this
table. Here's a short list of a table of authors:
mysql>SELECT Name, EMail FROM Authors;
+-----------------+----------------------+
| Name | EMail |
+-----------------+----------------------+
| Joan Smith | jsmith@somewhere.net |
| William Shatner | rocketman@earth.net |
| Kevin Yank | kevin@sitepoint.com |
| Amy Mathieson | amym@hotmail.com |
+-----------------+----------------------+
As you can see, the entries are sorted in no particular order. This
result is fine for a short list like this, but it would be easier to find
a particular author's email address (that of Amy Mathieson, for example) in
a very long list of authors, say a few hundred or so, if the authors' names
appeared in alphabetical order. Here's how:
mysql>SELECT Name, EMail FROM Authors ORDER BY Name;
+-----------------+----------------------+
| Name | EMail |
+-----------------+----------------------+
| Amy Mathieson | amym@hotmail.com |
| Joan Smith | jsmith@somewhere.net |
| Kevin Yank | kevin@sitepoint.com |
| William Shatner | rocketman@earth.net |
+-----------------+----------------------+
The entries now appear sorted alphabetically by their names. Just as
we can add a WHERE clause to a SELECT statement
to narrow down the list of results, we can also add an ORDER BY clause to specify a column by which a set of results should be sorted.
By adding the keyword DESC after the name of the sort column,
you can sort the entries in descending order:
mysql>SELECT Name, EMail FROM Authors ORDER BY Name DESC;
+-----------------+----------------------+
| Name | EMail |
+-----------------+----------------------+
| William Shatner | rocketman@earth.net |
| Kevin Yank | kevin@sitepoint.com |
| Joan Smith | jsmith@somewhere.net |
| Amy Mathieson | amym@hotmail.com |
+-----------------+----------------------+
You can actually use a comma-separated list of several column names
in the ORDER BY clause, to have MySQL sort the entries
by the first column, then sort any sets of tied entries by the second, and
so on. Any of the columns listed in the ORDER BY clause
may use the DESC keyword to reverse the sort order.
