MySQL公式テストデータ徹底解説

#この記事について
DBやSQLを実際に触って練習しようとなったときに真っ先に困ることといえば、ある程度まとまった大きさのデータが入手しにくい！ということです。
適当にWebサイトスクレイピングしてSQLのinsert文に加工→それをDBに突っ込むでもいいのですが、時間がかかる上に作業が面倒です。
そんな人のために、MySQLでは公式ページでテストデータが配布されています。これをインポートするだけで、SQLのクエリを叩くのにいい練習になりそうなちょうどいいテーブルを簡単に作ることができます。
本記事では、テストデータをインポートする方法・各テーブル・Culumnの意味といったデータの詳細な情報について記述し、すぐにテストデータを触っ手のSQLの練習ができるようになるようサポートします。

##使用する環境・バージョン
- OS : MacOS Mojave ver 10.14.5
- mysql : Ver 8.0.18 for osx10.14 on x86_64 (Homebrew)

使用する公式テストデータは以下のリンクから入手可能です。
[MySQL :: Other MySQL Documentation](https://dev.mysql.com/doc/index-other.html)

##読者に要求する前提知識
- 主キー、外部キー、E-R図といったDBの基本的な知識があること。

#ファイルのインポート
上述したURLから、公式データのSQLファイルを入手します。
そのSQLファイルが置いてあるディレクトリに`cd`コマンドで移動してから以下のコマンドを実行します。

```sql
$ mysql -u root -p
パスワード入力
mysql> source [ファイル名].sql
```

# world database
world内には、世界の国・都市・言語の情報が3個のテーブルに分かれて格納されています。

## E-R図と各テーブルの概要
テーブルに格納されているデータ概要は以下の通り。
<img width="514" alt="world.png" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/520877/eb8fd24c-b12a-6f28-f71d-1b88e59b2f3e.png">

1. country : 世界の国の基本情報
2. city : 世界の都市の基本情報
3. countrylanguage : 各国で使われている言語の情報

## 1.country(データ数：239)

###Column一覧
注：以下、テーブルの主キーを***太字+斜体***で、外部キーとその参照先の対応を<font color="Indigo">色文字</font>で表す。

- <font color="Indigo">***Code***</font> : アルファベット大文字3つからなる国コード
- Name : 国の英名
- Continent : どこの大陸にあるか<br>
['Asia','Europe','North America','Africa','Oceania','Antarctica','South America']の中から選択
- Region : 国の大まかな位置<br>
(例) Eastern Europe(東ヨーロッパ), Middle East(中東)など
- SurfaceArea : 領土面積[$km^2$]
- IndepYear : 独立年
- Population : 人口
- LifeExpentancy : 平均寿命
- GNP : 国民総生産(単位、該当年不明)
- GNPOld : 前年の国民総生産(単位、該当年不明)
- LocalName : 現地語での国名
- GovernmentForm : 政治体制<br>
(例) Constitutional Monarchy(立憲君主制)、Republic(共和制)など
- HeadOfState : 国家元首の名前
- Capital : 首都のcityテーブルでのID
- Code2 :アルファベット大文字2つからなる国コード

(例)

|Code|Name|LocalName|Capital|Code2|
|---|---|---|---|---|
|JPN|Japan|Nihon/Nippon|1532|JP|
|DEU|Germany|Deutschland|3068|DE|

## 2.city(データ数：4079)
###Column一覧
- ***ID*** : 自然数の通し番号
- Name : 都市名
- Country<font color="Indigo">Code</font> : アルファベット大文字3つからなる国コード
 - countryテーブル<font color="Indigo">"Code"</font>の外部キー
- District : その都市がある行政区

(例)

|ID|Name|CountryCode|District|
|---|---|---|---|
|1536|Sapporo|JPN|Hokkaido|
|3796|Houston|USA|Texas|

## 3.countrylanguage(データ数：984)
###Column一覧
- ***Country<font color="Indigo">Code</font>*** : アルファベット大文字3つからなる国コード
 - countryテーブル<font color="Indigo">"Code"</font>の外部キー
- ***Language*** : 言語名
- IsOfficial : 公用語かどうか
- Percentage : 使用率

(例)フィンランドの場合

|Country|Language|IsOfficial|Percentage|
|---|---|---|---|
|FIN|Estonian|F|0.2|
|FIN|Finnish|T|92.7|
|FIN|Russian|F|0.4|
|FIN|Saame|F|0.0|
|FIN|Swedish|T|5.7|

#sakila database
sakila内には、ある架空の貸しDVD屋の業務データが16個のテーブルに分かれて格納されています。

## E-R図と各テーブルの概要
テーブルに格納されているデータ概要は以下の通り。
<img width="1256" alt="sakila.png" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/520877/846350b6-4598-2aa7-b448-60c656bcf775.png">

1. actor : 俳優の情報
2. address : 住所録
3. category : 映画に付けられるカテゴリ一覧(アクション・ホラーなど)
4. city : 世界の都市の情報
5. country : 世界の国の情報
6. customer : 顧客情報
7. film : 映画の情報
8. film_actor : 映画に出演している俳優の情報 ← actorとfilmの中間テーブル
9. film_category : 映画のカテゴリ情報 ← categoryとfilmの中間テーブル
10. film_text : 映画の説明文の一覧
11. inventory : どの店にどの映画DVDの在庫があるかの一覧
12. language : 言語の一覧
13. payment : 支払い記録
14. rental : レンタル先・日時の記録
15. staff : スタッフ名簿
16. store : 店舗情報

## 1.actor(データ数：200)
###Column一覧
- <font color="DarkGreen">***actor_id***</font> : 俳優1人1人につく通し番号
- first_name : ファーストネーム(英大文字)
- last_name : 下の名前(英大文字)
- last_update : データが追加されたタイムスタンプ

## 2.address(データ数：603)
###Column一覧
- <font color="YellowGreen">***adress_id***</font> : 自然数の通し番号
- adress : 住所(いわゆるAddress Line1)
- adress2 : 住所その２(Address Line2)
- district : 所在地区(州)
- <font color="LightCoral">city_id</font> : 所在地の都市id
 - cityテーブル<font color="LightCoral">"city_id"</font>の外部キー
- postal_code : 郵便番号
- phone : 電話番号
- location : 所在地の位置情報
- last_update : データが追加されたタイムスタンプ

(例)

|address_id|address|district|city_id|postal_code|phone|
|---|---|---|---|---|---|
|6|1121 Loja Avenue|California|449|17886|838635286649|
|10|1795 Santiago de Compostela Way|Texas|295|18743|860452626434|

## 3.category(データ数：16)
###Column一覧
- <font color="CadetBlue">***category_id***</font> : 自然数の通し番号
- name : カテゴリ名
- last_update : データが追加されたタイムスタンプ

## 4.city(データ数：600)
###Column一覧
- <font color="LightCoral">***city_id***</font> : 自然数の通し番号
- city : 都市名
- <font color="DarkRed">country_id</font> : 都市のある国id
 - countryテーブル<font color="DarkRed">"country_id"</font>の外部キー
- last_update : データが追加されたタイムスタンプ

(例)

|city_id|city|country_id|
|---|---|---|
|89|Braslia|15|
|205|Hiroshima|50|

## 5.country(データ数：109)
###Column一覧
- <font color="DarkRed">***country_id***</font> : 自然数の通し番号
- country : 国名
- last_update : データが追加されたタイムスタンプ

## 6.customer(データ数：599)
###Column一覧
- <font color="MidnightBlue">***customer_id***</font> : 自然数の通し番号
- <font color="DarkOrange">store_id</font> : よく使う店舗(ホーム)
 - storeテーブル<font color="DarkOrange">"store_id"</font>の外部キー
- first_name : ファーストネーム
- last_name : 下の名前
- email : メールアドレス
- <font color="YellowGreen">address_id</font> : 住所id
 - adressテーブル<font color="YellowGreen">"address_id"</font>の外部キー
- active : 在籍しているか退会しているか(0or1の値)
- create_date : DB登録日
- last_update : データが追加されたタイムスタンプ

(例)

|customer_id|store_id|first_name|last_name|address_id|active|create_date|
|---|---|---|---|---|---|---|
|4|2|BARBARA|JONES|8|1|2006-02-14 22:04:36|
|271|1|PENNY|NEAL|276|0|2006-02-14 22:04:36|


## 7.film(データ数：1000)
###Column一覧
- <font color="RoyalBlue">***film_id***</font> : 自然数の通し番号
- title : 映画タイトル
- description : 概要文
- release_year : 公開年
- <font color="OliveDrab">language_id</font> : 言語id
 - languageテーブル<font color="OliveDrab">"language_id"</font>の外部キー
- original_<font color="OliveDrab">language_id</font> : 翻訳前の元言語id
 - languageテーブル<font color="OliveDrab">"language_id"</font>の外部キー
- rental_duration : レンタル期間[day]
- rental_rate : rental_duration日の間DVDを借りる値段[$]
- length : 映画の長さ[min]
- replacement_cost : 期限までに返却されない時・DVDを壊れた状態で返却した時の追加料金[$]
- rating : 公開レーティング<br>
['G','PG','PG-13','R','NC-17']のどれか
- special_features : DVD特典映像の種類<br>
['Trailers','Commentaries','Deleted Scenes','Behind the Scenes']から複数選択
- last_update : データが追加されたタイムスタンプ

(例)

|film_id|title|language_id|rental_duration|rental_rate|length|rating|special_features|
|---|---|---|---|---|---|---|---|
|86|BOOGIE AMELIE|1|6|4.99|121|R|Commentaries,Behind the Scenes|
|674|PET HAUNTING|1|3|0.99|99|PG|Trailers,Commentaries|

## 8.film_actor(データ数：5462)
###Column一覧
- <font color="DarkGreen">***actor_id***</font> : 出演役者のid
 - actorテーブル<font color="DarkGreen">"actor_id"</font>の外部キー
- <font color="RoyalBlue">***film_id***</font> : 映画のid
 - filmテーブル<font color="RoyalBlue">"film_id"</font>の外部キー
- last_update : データが追加されたタイムスタンプ

## 9.film_category(データ数：1000)
###Column一覧
- <font color="RoyalBlue">***film_id***</font> : 映画のid
 - filmテーブル<font color="RoyalBlue">"film_id"</font>の外部キー
- <font color="CadetBlue">***category_id***</font> : カテゴリid
 - categoryテーブル<font color="CadetBlue">"category_id"</font>の外部キー
- last_update : データが追加されたタイムスタンプ

## 10.film_text(データ数：1000)
###Column一覧
- ***film_id*** : 映画id
- title : タイトル
- description : 説明文

これはテーブル7"film"の3つの列を抜き出したものと同じです。データの追加・更新・削除もトリガーで同期されています。
MySQL5.6.10以前のInnoDBでは、descriptionで使われているデータ構造"text"の検索がサポートされていなかったため、該当箇所だけMyISAMで作り直したのがこの"film_text"テーブルです。

## 11.inventory(データ数：4581)
###Column一覧
- <font color="DarkTurquoise">***inventory_id***</font> : 自然数の通し番号
- <font color="RoyalBlue">film_id</font> : 
 - filmテーブル<font color="RoyalBlue">"film_id"</font>の外部キー
- <font color="DarkOrange">store_id</font> :
 - storeテーブル<font color="DarkOrange">"store_id"</font>の外部キー
- last_update : データが追加されたタイムスタンプ

(例)

|inventory_id|film_id|store_id|
|---|---|---|
|1|1|1|
|2|1|1|
|5|1|2|

## 12.language(データ数：6)
###Column一覧
- <font color="OliveDrab">***language_id***</font> : 自然数の通し番号
- name : 言語名
- last_update : データが追加されたタイムスタンプ

## 13.payment(データ数：16049)
###Column一覧
- ***payment_id*** : 自然数の通し番号
- <font color="MidnightBlue">customer_id</font> : 支払いをした顧客番号
 - customerテーブル<font color="MidnightBlue">"customer_id"</font>の外部キー
- <font color="RosyBrown">staff_id</font> : 対応した店員番号
 - staffテーブル<font color="RosyBrown">"staff_id"</font>の外部キー
- <font color="Brown">rental_id</font> : 支払いがレンタル料金の支払いだった場合、そのレンタルid<br>
(延長料金の支払いなどで対応していない場合はなし)
 - rentalテーブル<font color="Brown">"rental_id"</font>の外部キー
- amount : 支払い額[$]
- payment_date : 取引日
- last_update : データが追加されたタイムスタンプ

(例)

|payment_id|customer_id|staff_id|rental_id|amount|payment_date|
|---|---|---|---|---|---|
|56|2|1|14475|4.99|2005-08-21 13:24:32|

## 14.rental(データ数：16044)
###Column一覧
- <font color="Brown">***rental_id***</font> : 自然数の通し番号
- rental_date : 貸出日
- <font color="DarkTurquoise">inventory_id</font> : 貸したDVDのid
 - inventoryテーブル<font color="DarkTurquoise">"inventory_id"</font>の外部キー
- <font color="MidnightBlue">customer_id</font> : DVDを借りた顧客番号
 - customerテーブル<font color="MidnightBlue">"customer_id"</font>の外部キー
- return_date : 返却日
- <font color="RosyBrown">staff_id</font> : 対応した店員番号
 - staffテーブル<font color="RosyBrown">"staff_id"</font>の外部キー
- last_update : データが追加されたタイムスタンプ

(例)

|rental_id|rental_date|inventory_id|customer_id|return_date|staff_id|
|---|---|---|---|---|---|
|88|2005-05-25 14:13:54|2221|53|2005-05-29 09:32:54|2|

## 15.staff(データ数：2)
###Column一覧
- <font color="RosyBrown">***staff_id***</font> : 自然数の通し番号
- first_name : ファーストネーム
- last_name : 下の名前
- <font color="YellowGreen">address_id</font> : 住所id
 - adressテーブル<font color="YellowGreen">"address_id"</font>の外部キー
- picture : 写真(blob型)
- email : メールアドレス
- <font color="DarkOrange">store_id</font> : いつも務めている店のid
 - storeテーブル<font color="DarkOrange">"store_id"</font>の外部キー
- active : 在籍しているかどうか。0or1
- username : 業務システムでのユーザーネーム
- password : 業務システムでのPW
- last_update : データが追加されたタイムスタンプ

(例)

|staff_id|first_name|last_name|address_id|active|username|password|
|---|---|---|---|---|---|---|
|4|BARBARA|JONES|8|1|2006-02-14 22:04:36||
|271|PENNY|NEAL|276|0|2006-02-14 22:04:36||

## 16.store(データ数：2)
###Column一覧
- <font color="DarkOrange">***store_id***</font> : 自然数の通し番号。主キー
- manager_<font color="RosyBrown">staff_id</font> : 店長の店員id
 - staffテーブル<font color="RosyBrown">"staff_id"</font>の外部キー
- <font color="YellowGreen">address_id</font> : 住所id
 - adressテーブル<font color="YellowGreen">"address_id"</font>の外部キー
- last_update : データが追加されたタイムスタンプ

(例)

|store_id|manager_staff_id|address_id|
|---|---|---|
|1|1|1|
|2|2|2|

#employee database
employee内には、架空の会社の人事情報が6個のテーブルに分かれて格納されています。

## E-R図と各テーブルの概要
テーブルに格納されているデータ概要は以下の通り。
<img width="635" alt="employ.png" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/520877/5163047f-dec1-d75c-cfc9-78fe18b54ce2.png">

1. departments : 会社の部署一覧
2. dept_emp : 社員の部署所属一覧　←departmentsとemployeesの中間テーブル
3. dept_manager : 部署のマネージャー一覧　←departmentsとemployeesの中間テーブル
4. employees : 社員名簿
5. salaries : 社員の給与履歴
6. titles : 社員の役職履歴

## 1.departments(データ数：9)
###Column一覧
- <font color="Magenta">***dept_no***</font> : 部署の通し番号
- dept_name : 部署名

(例)

|dept_no|dept_name|
|---|---|
|d001|Marketing|
|d007|Sales|

## 2.dept_emp(データ数：331603)
###Column一覧
- <font color="DeepSkyBlue">***emp_no***</font> : 社員の通し番号
 - employeesテーブル<font color="DeepSkyBlue">"emp_no"</font>の外部キー
- <font color="Magenta">***dept_no***</font> : 社員の所属部署の番号
 - departmentsテーブル<font color="Magenta">"dept_no"</font>の外部キー
- from_date : 勤務開始日
- to_date : 勤務終了日

(例)

|emp_no|dept_no|from_date|to_date|
|---|---|---|---|
|10001|d005|1986-06-26|9999-01-01|
|10011|d009|1990-01-22|1996-11-09|

## 3.dept_manager(データ数：24)
###Column一覧
- <font color="DeepSkyBlue">***emp_no***</font> : 部長を務める社員の通し番号
 - employeesテーブル<font color="DeepSkyBlue">"emp_no"</font>の外部キー
- <font color="Magenta">***dept_no***</font> : 部署の番号
 - departmentsテーブル<font color="Magenta">"dept_no"</font>の外部キー
- from_date : 勤務開始日
- to_date : 勤務終了日

## 4.employees(データ数：300024)
###Column一覧
- <font color="DeepSkyBlue">***emp_no***</font> : 社員の通し番号
- birth_date : 誕生日
- first_name : 名字
- last_name : 下の名前
- gender : 性別。MかF
- hire_date : 雇用開始日

(例)

|emp_no|birth_date|first_name|last_name|gender|hire_date|
|---|---|---|---|---|---|
|10044|1961-09-21|Mingsen|Casley|F|1994-05-21|

## 5.salaries(データ数：2844047)
###Column一覧
- ***emp_no*** : 社員の通し番号
- salary : 給料
- ***from_date*** : 給料の試算開始日
- to_date : 給料の試算終了日

(例)

|emp_no|salary|from_date|to_date|
|---|---|---|---|
|12658|73254|1985-03-28|1986-03-28|

## 6.titles(データ数：443308)
###Column一覧
- ***emp_no*** : 社員の通し番号
- ***title*** : 職種<br>
(例) Engineer, Technique Leader, Staff など
- ***from_date*** : 着任日
- to_date : 解任日

(例)

|emp_no|title|from_date|to_date|
|---|---|---|---|
|13959|Engineer|1985-05-15|1992-05-15|
|14005|Technique Leader|1985-05-10|9999-01-01|

#さいごに
これで、公式データの各テーブル間の関係・各Columnの意味がわかったかと思います。これがわかれば、テーブル結合を使ったSQL文の練習もガンガンできるのではないでしょうか。
また、公式データにはインデックスやパーティションといった、効率的に利用するための各種設定が最初から行われています。ですので、

- どうしてこの列にはインデックスが張ってある/ないんだろう？
- この2つの列の間には関連があるのに、どうして外部キー設定がないんだろう？
- どうしてデータを1つのテーブルにまとめないで2つに分けたんだろう？

といったことを考察すると、DB構成に関する理解が深まると思います。