Edited at

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


SQLとは

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

データベースの情報を


  • 抽出したり

  • 追加したり

  • 変更したり

  • 削除したり

できます。



データベースとは

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

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


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

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

スクリーンショット 2019-06-20 13.20.25.png

こんなイメージ。

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



データベースの用語


テーブル

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

2019-06-20 13.14のイメージ.jpg


カラム

「列」に当たるもの。

2019-06-20 13.14のイメージ 2.jpg


レコード

「行」に当たるもの。

2019-06-20 13.14のイメージ 3.jpg

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

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

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


データベース

この全体を指します。

2019-06-20 13.14のイメージ 4.jpg



代表的なSQLコマンド


  • SELECT


    • レコードを抽出する



  • INSERT


    • レコードを追加する



  • DELETE


    • レコードを削除する



  • UPDATE


    • レコードを変更する



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



演習の準備

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

まずは環境を準備します。

http://www.sqlfiddle.com/#!9/9fba11/13

というサイトを利用します。


データの準備

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

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

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

SQL_Fiddle1.png

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_Fiddle2.png

画面下に表が出てきました。

これがSQLの実行結果です。

一覧にして気づきましたが、藤波辰巳藤波辰爾が別のレコードで登録されていますね。

ちなみに本名は辰巳です。

レコードのソースはwikipediaから取得してきたのですが、どこかのタイミングで辰爾に改名したってことですかね。



SELECTを少し変えてみる

さて、先程のSQLだとすべてのカラムが表示されていました。

SELECT *となっていましたが、*はすべてのカラムという意味だからです。

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

SELECT id, name

FROM wrestlers
;

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



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テーブルからid1,5,12,23のものを抽出する


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



ORDER

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

並び替えたいときはORDER BYを使います。

birthdayで並び替えてみましょう。

select *

from wrestlers
order by birthday
;

アントニオ猪木、天龍源一郎、長州力...といった順になりましたね。

昭和の名レスラーが名を連ねております。

明らかに年上順に並んでますね。

では次は順に並べましょう。

ORDER BY column DESCDESCを付けると降順に並びます。

select *

from wrestlers
order by birthday desc
;

ジェイ・ホワイト、オカダ・カズチカ、、、

新世代のレスラーが並びますね。

最近のプロレスを追えてないのでいまいちしっくりこないです、、、



JOIN

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

JOIN, INNER JOIN, OUTER JOIN, LEFT JOIN, RIGHT JOINとか色々あるけど、

以下3つだけ覚えておけば良い。


  • 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チャンピオンになっていないんですね。