Learning Mysql

changeably with the word-based alternatives if you want to. However, we always use the word-based versions, as that’s what you’ll see used inmost SQL statements.
ORDER BY Clauses
We’ve so far discussed how to choose the columns and rows that are returned as part of the query result, but not how to control how the result is displayed. Ina relational database, the rows in a table form a set there is no intrinsic order between the rows,
and so we have to ask MySQL to sort the results if we want them in a particular order.
In this section, we explain how to use the ORDER BY
clause to do this. Sorting has no effect on what is returned, and only affects what order the results are returned.
Suppose you want to return a list of the artists in the music database, sorted in alphabetical order by the artist_name
. Here’s what you’d type:
mysql> SELECT * FROM artist ORDER BY artist_name;
+-----------+---------------------------+
| artist_id | artist_name |
+-----------+---------------------------+
| 6 | Kylie Minogue |
| 3 | Miles Davis |
| 1 | New Order |
| 2 | Nick Cave & The Bad Seeds |
| 4 | The Rolling Stones |
| 5 | The Stone Roses |
+-----------+---------------------------+
6 rows inset sec)
The
ORDER
BY
clause indicates that sorting is required, followed by the column that should be used as the sort key. In this example, we’re sorting by alphabetically-as- cending artist_name
. The default sort is case-insensitive and in ascending order, and
MySQL automatically sorts alphabetically because the columns are character strings.
The way strings are sorted is determined by the character set and collation order that are being used. We discuss these in Collation and Character Sets For most of this book, we assume that you’re using the default settings.
Consider a second example. This time, let’s sort the output from the track table by ascending track length—that is, by the time column. Since it’s likely that two or more tracks have the same length, we’ll add a second sort key to resolve collisions and determine how such ties should be broken. In this case, when the track times are the same,
we’ll sort the answers alphabetically by track_name
. Here’s what you type:
mysql> SELECT time, track_name FROM track ORDER BY time, track_name;
+------+------------------------------------------------------------+
| time | track_name |
+------+------------------------------------------------------------+
| 1.34 | Intermission By Alan Wise Olympia, Paris 12/11/01] |
| 1.81 | In A Silent Way |
| 2.38 | Rip This Joint |
| 2.78 | Jangling Jack |
| 2.81 | Full Nelson |

Download 4.24 Mb.

Share with your friends: