IWGPチャンピオンを題材にSQLを学ぶ 〜社内SQL勉強会資料〜

SQLとは

データベースに蓄積された情報を操作するプログラム言語です。

データベースの情報を

• 抽出したり
• 追加したり
• 変更したり
• 削除したり

できます。

---

データベースとは

特にリレーショナル・データベース(RDB)と呼ばれるものを説明します。

リレーショナル・データベースでは、エクセルのように表の形でデータを保持しています。

またそれぞれの表が結びつくことで、複雑な情報を整理して保存することができます。

雑にツイッター的なアプリのデータベースを考えると...

こんなイメージ。

「渋谷なう」のツイートはuser_idが1なので、Userテーブルを見た所、、「一郎」のツイートということになります。

---

データベースの用語

テーブル

さっきの例で言う「表」に当たるもの。

カラム

「列」に当たるもの。

レコード

「行」に当たるもの。

ユーザーの行動で増えたり減ったりします。

たとえば新規ユーザーが一人入会すると、Userテーブルにレコードが1行増えます。

一方、テーブルとカラムは開発者が初めに定義するもので、ユーザーの行動で増えたり減ったりはしません。

データベース

この全体を指します。

---

代表的なSQLコマンド

• SELECT
  • レコードを抽出する
• INSERT
  • レコードを追加する
• DELETE
  • レコードを削除する
• UPDATE
  • レコードを変更する

今回は”分析”を目的にするので「SELECT」だけを使います。

---

演習の準備

ここからは実際にSQLを書きながら慣れていきましょう！

まずは環境を準備します。
http://www.sqlfiddle.com/#!9/9fba11/13
というサイトを利用します。

データの準備

今回は、みんな大好き新日本プロレスのIWGP歴代チャンピオンを題材にします。

以下のコード（DDLと呼びます）をコピペしてDB FIDDLEの左フォームに貼り付けて「Build Schema」をクリックします。

必要なテーブルを作って、ここに記載されたレコードを挿入してくれます。

CREATE TABLE wrestlers
    (`id` int, `name` varchar(10), `birthday` datetime)
;

INSERT INTO wrestlers
    (`id`, `name`, `birthday`)
VALUES
    (1, 'アントニオ猪木', '1943-02-20 00:00:00'),
    (2, '藤波辰巳', '1953-12-28 00:00:00'),
    (3, 'ビッグバン・ベイダー', '1955-05-14 00:00:00'),
    (4, 'サルマン・ハシミコフ', '1953-05-04 00:00:00'),
    (5, '長州力', '1951-12-03 00:00:00'),
    (6, '藤波辰爾', '1953-12-28 00:00:00'),
    (7, 'グレート・ムタ', '1962-12-23 00:00:00'),
    (8, '橋本真也', '1965-07-03 00:00:00'),
    (9, '武藤敬司', '1962-12-23 00:00:00'),
    (10, '高田延彦', '1962-04-12 00:00:00'),
    (11, '佐々木健介', '1966-08-04 00:00:00'),
    (12, '蝶野正洋', '1963-09-17 00:00:00'),
    (13, 'スコット・ノートン', '1961-06-15 00:00:00'),
    (14, '天龍源一郎', '1950-02-02 00:00:00'),
    (15, '藤田和之', '1970-10-16 00:00:00'),
    (16, '安田忠夫', '1963-10-09 00:00:00'),
    (17, '永田裕志', '1968-04-24 00:00:00'),
    (18, '高山善廣', '1966-09-19 00:00:00'),
    (19, '天山広吉', '1971-03-23 00:00:00'),
    (20, '中邑真輔', '1980-02-24 00:00:00'),
    (21, 'ボブ・サップ', '1973-09-22 00:00:00'),
    (22, '小島聡', '1970-09-14 00:00:00'),
    (23, 'ブロック・レスナー', '1977-07-12 00:00:00'),
    (24, '棚橋弘至', '1976-11-13 00:00:00'),
    (25, '中西学', '1967-01-22 00:00:00'),
    (26, '真壁刀義', '1972-09-29 00:00:00'),
    (27, '小島聡', '1970-09-14 00:00:00'),
    (28, 'オカダ・カズチカ', '1987-11-08 00:00:00'),
    (29, 'AJスタイルズ', '1977-06-02 00:00:00'),
    (30, '内藤哲也', '1982-06-22 00:00:00'),
    (31, 'ケニー・オメガ', '1983-10-16 00:00:00'),
    (32, 'ジェイ・ホワイト', '1992-10-09 00:00:00')
;


CREATE TABLE champions
    (`id` int, `wrestler_id` int, `defence_count` int, `received_at` datetime, `belt_id` int)
;

INSERT INTO champions
    (`id`, `wrestler_id`, `defence_count`, `received_at`, `belt_id`)
VALUES
    (1, 1, 4, '1987-06-12 00:00:00', 1),
    (2, 2, 0, '1988-05-08 00:00:00', 1),
    (3, 2, 7, '1988-06-24 00:00:00', 1),
    (4, 3, 0, '1989-04-24 00:00:00', 1),
    (5, 4, 0, '1989-05-25 00:00:00', 1),
    (6, 5, 0, '1989-07-12 00:00:00', 1),
    (7, 3, 4, '1989-08-10 00:00:00', 1),
    (8, 5, 1, '1990-08-19 00:00:00', 1),
    (9, 6, 0, '1990-12-26 00:00:00', 1),
    (10, 3, 0, '1991-01-17 00:00:00', 1),
    (11, 6, 3, '1991-03-04 00:00:00', 1),
    (12, 5, 4, '1992-01-04 00:00:00', 1),
    (13, 7, 5, '1992-08-16 00:00:00', 1),
    (14, 8, 4, '1993-09-20 00:00:00', 1),
    (15, 6, 0, '1994-04-04 00:00:00', 1),
    (16, 8, 9, '1994-05-01 00:00:00', 1),
    (17, 9, 5, '1995-05-03 00:00:00', 1),
    (18, 10, 1, '1996-01-04 00:00:00', 1),
    (19, 8, 7, '1996-04-29 00:00:00', 1),
    (20, 11, 3, '1997-08-31 00:00:00', 2),
    (21, 6, 2, '1998-04-04 00:00:00', 2),
    (22, 12, 0, '1998-08-08 00:00:00', 2),
    (23, 13, 4, '1998-09-23 00:00:00', 2),
    (24, 9, 5, '1999-01-04 00:00:00', 2),
    (25, 14, 0, '1999-12-10 00:00:00', 2),
    (26, 11, 5, '2000-01-04 00:00:00', 2),
    (27, 11, 1, '2001-01-04 00:00:00', 2),
    (28, 13, 0, '2001-03-17 00:00:00', 2),
    (29, 15, 2, '2001-04-09 00:00:00', 2),
    (30, 16, 1, '2002-02-16 00:00:00', 2),
    (31, 17, 10, '2002-04-05 00:00:00', 2),
    (32, 18, 3, '2003-05-02 00:00:00', 2),
    (33, 19, 0, '2003-11-03 00:00:00', 2),
    (34, 20, 1, '2003-12-09 00:00:00', 2),
    (35, 19, 1, '2004-02-15 00:00:00', 2),
    (36, 11, 0, '2004-03-12 00:00:00', 2),
    (37, 21, 1, '2004-03-28 00:00:00', 2),
    (38, 15, 1, '2004-06-05 00:00:00', 2),
    (39, 11, 2, '2004-10-09 00:00:00', 2),
    (40, 19, 0, '2004-12-12 00:00:00', 2),
    (41, 27, 1, '2005-02-20 00:00:00', 2),
    (42, 19, 1, '2005-05-14 00:00:00', 2),
    (43, 15, 0, '2005-07-18 00:00:00', 2),
    (44, 23, 3, '2005-10-08 00:00:00', 3),
    (45, 24, 4, '2006-07-17 00:00:00', 3),
    (46, 17, 2, '2007-04-13 00:00:00', 3),
    (47, 24, 1, '2007-10-08 00:00:00', 3),
    (48, 20, 2, '2008-01-04 00:00:00', 4),
    (49, 9, 4, '2008-04-27 00:00:00', 4),
    (50, 24, 3, '2009-01-04 00:00:00', 4),
    (51, 25, 0, '2009-05-06 00:00:00', 4),
    (52, 24, 1, '2009-06-20 00:00:00', 4),
    (53, 20, 6, '2009-09-27 00:00:00', 4),
    (54, 26, 3, '2010-05-03 00:00:00', 4),
    (55, 27, 1, '2010-10-11 00:00:00', 4),
    (56, 24, 11, '2011-01-04 00:00:00', 4),
    (57, 28, 2, '2012-02-12 00:00:00', 4),
    (58, 24, 7, '2012-06-16 00:00:00', 4),
    (59, 28, 8, '2013-04-07 00:00:00', 4),
    (60, 29, 2, '2014-05-03 00:00:00', 4),
    (61, 24, 1, '2014-10-13 00:00:00', 4),
    (62, 29, 1, '2015-02-11 00:00:00', 4),
    (63, 28, 3, '2015-07-05 00:00:00', 4),
    (64, 30, 1, '2016-04-10 00:00:00', 4),
    (65, 28, 12, '2016-06-19 00:00:00', 4),
    (66, 31, 3, '2018-06-09 00:00:00', 4),
    (67, 24, 0, '2019-01-04 00:00:00', 4),
    (68, 32, 0, '2019-02-11 00:00:00', 4),
    (69, 28, 2, '2019-04-06 00:00:00', 4)
;


CREATE TABLE belts
    (`id` int, `name` varchar(3))
;

INSERT INTO belts
    (`id`, `name`)
VALUES
    (1, '初代'),
    (2, '2代目'),
    (3, '3代目'),
    (4, '4代目')
;

---

SELECTしてみる

では先程のデータが正しく入ったか見てみましょう。

SELECT *
FROM wrestlers
;

上記SQLを右フォームに記入して「Run SQL」で実行します。

画面下に表が出てきました。
これがSQLの実行結果です。

一覧にして気づきましたが、藤波辰巳と藤波辰爾が別のレコードで登録されていますね。
ちなみに本名は辰巳です。
レコードのソースはwikipediaから取得してきたのですが、どこかのタイミングで辰爾に改名したってことですかね。

---

SELECTを少し変えてみる

さて、先程のSQLだとすべてのカラムが表示されていました。
SELECT *となっていましたが、*はすべてのカラムという意味だからです。

そこで今回は特定のカラムを指定してみましょう。

SELECT id, name
FROM wrestlers
;

idとnameだけが表示されるようになりました。

---

WHEREで条件を指定する

先程の例では、すべてのレコードが表示されていました。
実際の利用シーンでは、特定条件でレコードを絞って抽出したいと思います。

抽出の条件を設定するにはWHEREを使います。

SELECT *
FROM wrestlers
WHERE id = 20
;

正しいSQLが書けていれば、いまや全米で大活躍のあのレスラーが表示されるはずです。

---

WHEREの色んな指定の仕方

WHEREの条件の指定方法はいろいろあります。

不等号で指定

SELECT *
FROM wrestlers
WHERE id > 20
;

ANDで複数の条件を組み合わせる

SELECT *
FROM wrestlers
WHERE id > 10 AND id < 20
;

範囲指定ならBETWEENでも

SELECT *
FROM wrestlers
WHERE id BETWEEN 20 AND 30
;

---

演習1

1. wrestlersテーブルからidが1,5,12,23のものを抽出する
2. wrestlersテーブルから誕生日が1980年1月1日以降のものを抽出する

---

ORDER

抽出結果のレコードを任意の順に並び替えることができます。

並び替えたいときはORDER BYを使います。
birthdayで並び替えてみましょう。

select *
from wrestlers
order by birthday
;

アントニオ猪木、天龍源一郎、長州力...といった順になりましたね。
昭和の名レスラーが名を連ねております。
明らかに年上順に並んでますね。

では次は順に並べましょう。
ORDER BY column DESCとDESCを付けると降順に並びます。

select *
from wrestlers
order by birthday desc
;

ジェイ・ホワイト、オカダ・カズチカ、、、
新世代のレスラーが並びますね。
最近のプロレスを追えてないのでいまいちしっくりこないです、、、

---

JOIN

複数のテーブルを結合する。

JOIN, INNER JOIN, OUTER JOIN, LEFT JOIN, RIGHT JOINとか色々あるけど、
以下３つだけ覚えておけば良い。

• JOIN (=INNER JOIN)
• LEFT JOIN (OUTER JOINの一種)
• RIGHT JOIN (OUTER JOINの一種)

select
  champions.id as champion_id,
  champions.defence_count,
  champions.received_at,
  champions.belt_id,
  wrestlers.id as wrestler_id,
  wrestlers.name,
  wrestlers.birthday
from champions
join wrestlers on champions.wrestler_id = wrestlers.id
;

チャンピオンテーブルにレスラー名が一緒に表示されて、だいぶわかりやすくなりました。

---

COUNTとGROUP BY

各ベルトが使われた回数を調べる。

select belt_id, count(*)
from champions
group by belt_id
;

3代目のベルトって、あまり使われていないんですね。

演習2

1. チャンピオンベルト獲得回数の多い順にレスラー名を表示する。

結果が出ると分かるんですが、アントニオ猪木って以外にも1回しかIWGPチャンピオンになっていないんですね。