Learning Mysql

First, let’s run a simple query to list the tracks that have been played, along with the time each track was played. We’ve enclosed the query in parentheses for consistency with our other examples—the parentheses don’t actually have any effect here—and haven’t used an ORDER BY
or
LIMIT
clause:
mysql> (SELECT track_name, played
-> FROM track INNER JOIN played USING (artist_id, album_id, track_id)
-> );
+-----------------------+---------------------+
| track_name | played |
+-----------------------+---------------------+
| Fine Time | 2006-08-14 10:21:03 |
| Fine Time | 2006-08-14 10:27:03 |
| Temptation | 2006-08-14 10:25:22 |
| True Faith | 2006-08-14 10:30:25 |
| The Perfect Kiss | 2006-08-14 10:36:54 |
| Ceremony | 2006-08-14 10:41:43 |
| Regret | 2006-08-14 10:43:37 |
| Crystal | 2006-08-14 10:47:21 |
| Bizarre Love Triangle | 2006-08-14 10:54:02 |
| In A Silent Way | 2006-08-15 14:00:03 |
| Intruder | 2006-08-15 14:26:12 |
| New Blues | 2006-08-15 14:33:57 |
+-----------------------+---------------------+
12 rows inset sec)
The query returns all the played tracks, in no particular order (seethe second and third entries).
Now, let’s add an ORDER BY
clause to this query:
mysql> (SELECT track_name, played
-> FROM track INNER JOIN played USING (artist_id, album_id, track_id)
-> ORDER BY played ASC);
+-----------------------+---------------------+
| track_name | played |
+-----------------------+---------------------+
| Fine Time | 2006-08-14 10:21:03 |
| Temptation | 2006-08-14 10:25:22 |
| Fine Time | 2006-08-14 10:27:03 |
| True Faith | 2006-08-14 10:30:25 |
| The Perfect Kiss | 2006-08-14 10:36:54 |
| Ceremony | 2006-08-14 10:41:43 |
| Regret | 2006-08-14 10:43:37 |
| Crystal | 2006-08-14 10:47:21 |
| Bizarre Love Triangle | 2006-08-14 10:54:02 |
| In A Silent Way | 2006-08-15 14:00:03 |
| Intruder | 2006-08-15 14:26:12 |
| New Blues | 2006-08-15 14:33:57 |
+-----------------------+---------------------+
12 rows inset sec)
As expected, we get all the played tracks, in the order that they’ve been played.

Download 4.24 Mb.

Share with your friends: