Help us understand the problem. What is going on with this article?

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

More than 1 year has passed since last update.

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

takahi5
React Native/React/Expo/Rails/ UdemyでReact Native講座やってます。クーポンこちら→ https://blog.takahi5.com/udemy-coupon/
https://blog.takahi5.com
Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away