この記事は「MySQL Casual Advent Calendar 2018」9日目の記事です。
21日目の記事も書いています。
Sakilaとは
MySQLから公式のサンプルデータベースが公開されています。 MySQL :: Sakila Sample Database その名も Sakila !! イルカのマスコットの名前が冠されています。
Sakilaデータベースは書籍やチュートリアルなどのサンプルとして使えるような標準のデータベースを提供しているので、MySQL8.0の新機能を試してみるなどの用途に最適です。 私も新機能の検証や確認などのちょっとした目的のためにサンプルとして使っています。 通常のテーブルに加え、ビュー、ストアドプロシージャ、トリガなども含まれているので、これらの使い方を具体的なコードとして参照することもでき、学習教材として使うこともできます。
SakilaにはレンタルDVD店のシステムをモデルとしたデータベースが構成されています。 顧客の情報やレンタルの履歴、映画のマスタ情報などが登録されています。
Sakilaデータベースのインストール
Sakilaデータベースは MySQL :: Other MySQL Documentation からダウンロードできます。
Example Databases の sakila database からダウンロードでき、形式は TGZ と zip から選択できます。
$ wget 'http://downloads.mysql.com/docs/sakila-db.zip' $ unzip sakila-db.zip
解凍すると sakila-db/ ディレクトリが展開されます。テーブルなどのデータベース構成用のSQLが sakila-schema.sql に記されています。エディタで見てみるとテーブルの他にビュー、プロシージャ、ファンクションを作成するSQLも含まれています。
その後 sakila-data.sql を読み込んでデータを登録します。
mysql> source sakila-db/sakila-schema.sql; mysql> source sakila-db/sakila-datasql;
一連の作業をワンライナーで実行することもできます。
$ mysql -u root -p < <( \ wget 'http://downloads.mysql.com/docs/sakila-db.zip' \ && unzip -o sakila-db.zip 1>/dev/null \ && cat sakila-db/sakila-schema.sql sakila-db/sakila-data.sql \ )
※表示の都合上複数行で表示しています。
正しくインストールできている場合は次のようにテーブルが表示されます。
mysql> use sakila; Database changed mysql> show tables; +----------------------------+ | Tables_in_sakila | +----------------------------+ | actor | | actor_info | | address | | category | | city | | country | | customer | | customer_list | | film | | film_actor | | film_category | | film_list | | film_text | | inventory | | language | | nicer_but_slower_film_list | | payment | | rental | | sales_by_film_category | | sales_by_store | | staff | | staff_list | | store | +----------------------------+ 23 rows in set (0.00 sec)
show tables ではテーブルに加えビューも一覧に表示されるので、ビューのみを表示したい場合は information_schema を参照する必要があります。
mysql> select table_name from information_schema.views where table_schema = 'sakila'; +----------------------------+ | TABLE_NAME | +----------------------------+ | actor_info | | customer_list | | film_list | | nicer_but_slower_film_list | | sales_by_film_category | | sales_by_store | | staff_list | +----------------------------+ 7 rows in set (0.00 sec)
7つのビューが定義されているのがわかります。
MySQL Workbench も使える
Sakilaに格納されているテーブルのリレーションについては、sakila.mwbをMySQL Workbenchで開くことで参照できます。
それぞれのテーブルやビューがどういう設計になっているかもドキュメントが用意されています。
MySQL :: Sakila Sample Database :: 5.1 Tables
MySQL :: Sakila Sample Database :: 5.2 Views
MySQL :: Sakila Sample Database :: 5.3 Stored Procedures
MySQL :: Sakila Sample Database :: 5.4 Stored Functions
MySQL :: Sakila Sample Database :: 5.5 Triggers
データベースの利用例とちょっとした解説
データベースの利用例についてもサンプルが公開されています。
MySQL :: Sakila Sample Database :: 6 Usage Examples
DVDを借りる
customer_id=3のLinda Williamsさんが、inventory_id=10のAce Goldfingerをレンタルするケースを考えます。対応するのは staff_id=1のMike Hillyerさんです。
在庫の確認
DVDをレンタルするためにはまず指定された商品の在庫を確認する必要があります。
在庫の有無の確認は investory_in_stock(10) のようにファンクションに在庫のID inventory.inventory_id を与えることで確認できます（１or0が返ります）。
inventory_in_stock() では rental テーブルから貸出中の情報を参照し在庫の有無を判断しています。 rental テーブルにレコードが存在し rental.return_date is null のレコードが貸出中となります。
mysql> select inventory_in_stock(10); +------------------------+ | inventory_in_stock(10) | +------------------------+ | 1 | +------------------------+ 1 row in set (0.00 sec)
貸出を登録
貸出は rental テーブルへ行を挿入します。
mysql> insert into rental (rental_date, inventory_id, customer_id, staff_id) values(now(), 10, 3, 1); Query OK, 1 row affected (0.20 sec)
支払いの登録
get_customer_balance() ファンクションで支払い金額を算出します。
payment テーブルに登録されていない未払いの金額があればそれも加算されます。
mysql> select @balance := get_customer_balance(3, now()); +--------------------------------------------+ | @balance := get_customer_balance(3, now()) | +--------------------------------------------+ | 4.99 | +--------------------------------------------+ 1 row in set (0.00 sec)
支払いが済んだら金額を payment テーブルに登録します。
mysql> insert into payment (customer_id, staff_id, rental_id, amount, payment_date) values(3, 1, last_insert_id(), @balance, now()) ; Query OK, 1 row affected (0.02 sec) mysql> select * from payment;
最後に
これまではちょっとしたSQLを実行するにもサンプルのデータベースを自分で作ってデータを登録したりしていましたが、Sakilaを知ってからは積極的に使うようになりました。 MySQL8.0ではCTEやWindow関数などの新機能が追加されているので、Sakilaを使ってSQLを書いてみたいと思います。
