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

##読者に要求する前提知識

• 主キー、外部キー、E-R図といったDBの基本的な知識があること。

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

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

world database

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

E-R図と各テーブルの概要

テーブルに格納されているデータ概要は以下の通り。

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

1.country(データ数：239)

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

• Code : アルファベット大文字3つからなる国コード
• Name : 国の英名
• Continent : どこの大陸にあるか
  
  ['Asia','Europe','North America','Africa','Oceania','Antarctica','South America']の中から選択
• Region : 国の大まかな位置
  
  (例) Eastern Europe(東ヨーロッパ), Middle East(中東)など
• SurfaceArea : 領土面積[$km^2$]
• IndepYear : 独立年
• Population : 人口
• LifeExpentancy : 平均寿命
• GNP : 国民総生産(単位、該当年不明)
• GNPOld : 前年の国民総生産(単位、該当年不明)
• LocalName : 現地語での国名
• GovernmentForm : 政治体制
  
  (例) 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 : 都市名
• CountryCode : アルファベット大文字3つからなる国コード
• countryテーブル"Code"の外部キー
• District : その都市がある行政区

(例)

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

3.countrylanguage(データ数：984)

###Column一覧

• CountryCode : アルファベット大文字3つからなる国コード
• countryテーブル"Code"の外部キー
• 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図と各テーブルの概要

テーブルに格納されているデータ概要は以下の通り。

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一覧

• actor_id : 俳優1人1人につく通し番号
• first_name : ファーストネーム(英大文字)
• last_name : 下の名前(英大文字)
• last_update : データが追加されたタイムスタンプ

2.address(データ数：603)

###Column一覧

• adress_id : 自然数の通し番号
• adress : 住所(いわゆるAddress Line1)
• adress2 : 住所その２(Address Line2)
• district : 所在地区(州)
• city_id : 所在地の都市id
• cityテーブル"city_id"の外部キー
• 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一覧

• category_id : 自然数の通し番号
• name : カテゴリ名
• last_update : データが追加されたタイムスタンプ

4.city(データ数：600)

###Column一覧

• city_id : 自然数の通し番号
• city : 都市名
• country_id : 都市のある国id
• countryテーブル"country_id"の外部キー
• last_update : データが追加されたタイムスタンプ

(例)

city_id	city	country_id
89	Braslia	15
205	Hiroshima	50

5.country(データ数：109)

###Column一覧

• country_id : 自然数の通し番号
• country : 国名
• last_update : データが追加されたタイムスタンプ

6.customer(データ数：599)

###Column一覧

• customer_id : 自然数の通し番号
• store_id : よく使う店舗(ホーム)
• storeテーブル"store_id"の外部キー
• first_name : ファーストネーム
• last_name : 下の名前
• email : メールアドレス
• address_id : 住所id
• adressテーブル"address_id"の外部キー
• 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一覧

• film_id : 自然数の通し番号
• title : 映画タイトル
• description : 概要文
• release_year : 公開年
• language_id : 言語id
• languageテーブル"language_id"の外部キー
• original_language_id : 翻訳前の元言語id
• languageテーブル"language_id"の外部キー
• rental_duration : レンタル期間[day]
• rental_rate : rental_duration日の間DVDを借りる値段[$]
• length : 映画の長さ[min]
• replacement_cost : 期限までに返却されない時・DVDを壊れた状態で返却した時の追加料金[$]
• rating : 公開レーティング
  
  ['G','PG','PG-13','R','NC-17']のどれか
• special_features : DVD特典映像の種類
  
  ['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一覧

• actor_id : 出演役者のid
• actorテーブル"actor_id"の外部キー
• film_id : 映画のid
• filmテーブル"film_id"の外部キー
• last_update : データが追加されたタイムスタンプ

9.film_category(データ数：1000)

###Column一覧

• film_id : 映画のid
• filmテーブル"film_id"の外部キー
• category_id : カテゴリid
• categoryテーブル"category_id"の外部キー
• 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一覧

• inventory_id : 自然数の通し番号
• film_id :
• filmテーブル"film_id"の外部キー
• store_id :
• storeテーブル"store_id"の外部キー
• last_update : データが追加されたタイムスタンプ

(例)

inventory_id	film_id	store_id
1	1	1
2	1	1
5	1	2

12.language(データ数：6)

###Column一覧

• language_id : 自然数の通し番号
• name : 言語名
• last_update : データが追加されたタイムスタンプ

13.payment(データ数：16049)

###Column一覧

• payment_id : 自然数の通し番号
• customer_id : 支払いをした顧客番号
• customerテーブル"customer_id"の外部キー
• staff_id : 対応した店員番号
• staffテーブル"staff_id"の外部キー
• rental_id : 支払いがレンタル料金の支払いだった場合、そのレンタルid
  
  (延長料金の支払いなどで対応していない場合はなし)
• rentalテーブル"rental_id"の外部キー
• 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一覧

• rental_id : 自然数の通し番号
• rental_date : 貸出日
• inventory_id : 貸したDVDのid
• inventoryテーブル"inventory_id"の外部キー
• customer_id : DVDを借りた顧客番号
• customerテーブル"customer_id"の外部キー
• return_date : 返却日
• staff_id : 対応した店員番号
• staffテーブル"staff_id"の外部キー
• 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一覧

• staff_id : 自然数の通し番号
• first_name : ファーストネーム
• last_name : 下の名前
• address_id : 住所id
• adressテーブル"address_id"の外部キー
• picture : 写真(blob型)
• email : メールアドレス
• store_id : いつも務めている店のid
• storeテーブル"store_id"の外部キー
• 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一覧

• store_id : 自然数の通し番号。主キー
• manager_staff_id : 店長の店員id
• staffテーブル"staff_id"の外部キー
• address_id : 住所id
• adressテーブル"address_id"の外部キー
• last_update : データが追加されたタイムスタンプ

(例)

store_id	manager_staff_id	address_id
1	1	1
2	2	2

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

E-R図と各テーブルの概要

テーブルに格納されているデータ概要は以下の通り。

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

1.departments(データ数：9)

###Column一覧

• dept_no : 部署の通し番号
• dept_name : 部署名

(例)

dept_no	dept_name
d001	Marketing
d007	Sales

2.dept_emp(データ数：331603)

###Column一覧

• emp_no : 社員の通し番号
• employeesテーブル"emp_no"の外部キー
• dept_no : 社員の所属部署の番号
• departmentsテーブル"dept_no"の外部キー
• 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一覧

• emp_no : 部長を務める社員の通し番号
• employeesテーブル"emp_no"の外部キー
• dept_no : 部署の番号
• departmentsテーブル"dept_no"の外部キー
• from_date : 勤務開始日
• to_date : 勤務終了日

4.employees(データ数：300024)

###Column一覧

• emp_no : 社員の通し番号
• 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 : 職種
  
  (例) 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構成に関する理解が深まると思います。