Learning Mysql

the album have been played. So, we want to group together by artist and by album we don’t want to group by track, since that’d split the tracks from each album into different groups and tell us how many times we’d listened to each track. We also need a four-way join between all four tables in the database, but that isn’t hard to dousing the skills we’ve developed so far. Here’s the query:
mysql> SELECT artist_name, album_name, COUNT) FROM
-> artist INNER JOIN album USING (artist_id)
-> INNER JOIN track USING (artist_id, album_id)
-> INNER JOIN played USING (artist_id, album_id, track_id)
-> GROUP BY album.artist_id, album.album_id;
+-------------+----------------------------+----------+
| artist_name | album_name | COUNT) |
+-------------+----------------------------+----------+
| New Order | Retro - Miranda Sawyer POP | 8 |
| Miles Davis | Live Around The World | 3 |
+-------------+----------------------------+----------+
2 rows inset sec)
You can see we’ve only listened to two albums we’ve listened to one or more tracks on New Order’s Retro - Miranda Sawyer POP eight times, and one or more tracks on the Miles Davis album Live Around The World three times. We don’t know whether it’s the same track we’ve listened to multiple times, different tracks a few times, or many tracks once the GROUP BY
clause hides the details. Again, we use
COUNT(*)
to do the counting of rows in the groups, and you can seethe INNER JOIN
spread over lines 2 to in the query.
Before we end this section, let’s consider how results are displayed fora grouping operation. The output rows are grouped together according to the GROUP BY
clause, with one row displayed for each group. You will typically not ask for fields that are collected together in the grouping process, since the result will be meaningless. For example,
grouping the tracks by artist will produce:
mysql> SELECT * FROM track GROUP BY artist_id;
+----------+----------------------+-----------+----------+----------+
| track_id | track_name | artist_id | album_id | time |
+----------+----------------------+-----------+----------+----------+
| 0 | Elegia | 1 | 1 | 00:04:93 |
| 0 | Do You Love Me | 2 | 1 | 00:05:95 |
| 0 | In A Silent Way | 3 | 1 | 00:01:81 |
| 0 | Rocks Off | 4 | 1 | 00:04:54 |
| 0 | Breaking Into Heaven | 5 | 1 | 00:11:37 |
| 0 | Spinning Around | 6 | 1 | 00:03:46 |
+----------+----------------------+-----------+----------+----------+
6 rows inset sec)
Only the artist_id here is meaningful the rest of the columns just contain the first- listed entry from each group. To illustrate this point, “Elegia” is the first track that would be listed for artist_id
1 if we hadn’t performed any grouping:
mysql> SELECT * FROM track WHERE artist_id=1;
+----------+----------------------+-----------+----------+----------+

Download 4.24 Mb.

Share with your friends: