Music Tutorial/ja

音楽データベース

Music database

このチュートリアルで結合(join)の文法を紹介する。音楽データベースには2つのテーブル: アルバムalbum と トラックtrack があり、一対多の関係である。
訳者注　このデータベースはMySQLでないと動かない。データベースの設定を確認すること。

album(asin, title, artist, price, release, label, rank) 訳者注　実際のテーブルでは release ではなく rdate がフィールド名

    asin Amazon商品識別番号
    title タイトル
    artist アーティスト
    price 価格
    release リリース
    label レーベル
    rank ランク

track(album, dsk, posn, song)

    album アルバム
    dsk ディスク
    posn 再生順
    song 曲

このデータベースについての詳細

結合方法

このフレーズ FROM album JOIN track ON album.asin=track.album はテーブルalbumとtrackの結合を表している。 このJOIN(結合)で全てのトラックについて1行が得られる（訳者注 トラックの行数と同じ行数になる）。 trackのフィールド (album,disk, posn and song)に加えてalbumに関する詳細(title, artist ...)が含まれる。

曲song 'Alison'がレコーディングされた（訳者注 アルバムの）タイトルtitleとアーティストartistを見つける。

SELECT title, artist
  FROM album JOIN track
         ON (album.asin=track.album)
 WHERE song = 'Alison'

SELECT title, artist
  FROM album JOIN track
         ON (album.asin=track.album)
 WHERE song = 'Alison'

どのアーティストartistが曲song 'Exodus'をレコーディングしたか？

SELECT artist
  FROM album JOIN track ON (asin=album)
 WHERE song = 'Exodus'

アルバムalbum 'Blur'の各トラックtrackの曲songを表示する。

SELECT song
  FROM album JOIN track ON (asin=album)
 WHERE title = 'Blur'

集約関数とGROUP BYをテーブル結合の式で使うことができる。

各albumについてタイトルtitleとトラックtrackの総数を表示する。

SELECT title, COUNT(*)
  FROM album JOIN track ON (asin=album)
 GROUP BY title

SELECT title, COUNT(*)
  FROM album JOIN track ON (asin=album)
 GROUP BY title

各アルバムalbumのタイトルtitleと'Heart'という単語を含むトラックtrackの総数を表示する。 (そのようなトラックを含まないアルバムは表示する必要はない)。

song LIKE '%Heart%' をHeartを含むtrackの検索で使う。

SELECT title, COUNT(*)
  FROM album JOIN track ON (asin=album)
 WHERE song LIKE '%Heart%'
 GROUP BY title

"タイトル トラック" とは曲songがタイトルtitleと同じもののこと. タイトル トラックを見つける。

SELECT song
  FROM album JOIN track ON (asin=album)
 WHERE song = title

"代名詞"アルバムalubmとはタイトルtitleがアーティストartistと同じもののこと。 (例えば、バンド'Blur'のアルバム'Blur'。) 代名詞アルバムを表示する。

この例ではテーブル1つにしかアクセスする必要がない。だからJOINは使わないこと。

SELECT title
  FROM album
 WHERE artist = title

2枚を超えるアルバムalbumに現れる曲songを見つける。登場回数も含めて表示する。

HAVING節をGROUP BYの後で使う。 訳者ヒント song = のレコードを複数含むアルバムがいくつかある。登場回数のカウントでは重複を除外すること。

SELECT song, COUNT(DISTINCT asin)
  FROM album JOIN track ON asin=album
GROUP BY song
HAVING COUNT(DISTINCT asin)>2

"お値打ち"アルバムalbumとは1トラックtrack辺りの価格priceが50ペンス（訳者注 0.50 と比較）を下回るもののこと。 お値打ちアルバムを見つける。タイトルtitleと価格とtrack総数を表示する。

SELECT title, price, COUNT(song)
  FROM album JOIN track ON asin=album
GROUP BY title, price
HAVING price/COUNT(song) < 0.50

ワーグナーの Ring cycle にはご立派にも173ものトラックがあり、ビング・クロスビーは101トラックを記録した。

トラック数の多い順にアルバムのリストを作る。 タイトルtitleとトラックtrack数を表示する。

2つ以上の同じトラック数のアルバムが有ったらアルファベット順に並べる。

SELECT title, COUNT(asin)
FROM album JOIN track ON asin=album
GROUP BY asin,title
ORDER BY 2 DESC, title

映画データベースのチュートリアル