イチから学ぶデータベース・SQL（７）では、サンプルデータベースに登録されているデータから四則演算を使用して日付に関する計算をしました。
今回も、これまでと同様paizaラーニングを参考に、オンラインRPGを題材としてSQLについて学習していきます。
今回も、これまでと同様paizaラーニングを参考に、オンラインRPGを題材としてSQLについて学習していきます。
イチから学ぶデータベース・SQL（８）
1. 前回の振り返りと今回学習する内容
前回は、四則演算とMIN関数、MAX関数を使用してプレイヤーの累計プレイ期間を求めたり、YEARやTIMESTAMPDIFFなどの日付や時間の関数を使用し、現在の年とデータベースに登録されているユーザーの生年月日から数え年、満年齢を求めていきました。
そして最後に今回学習するテキスト検索について軽く触れ、前回の学習としました。
今回使用するサンプルデータベースは前回までと同じようにこちらです。
今回学習する内容は、前回最後に触れた通りテキスト検索についてです。
どのユーザーが、いつ、どこで、何をしたのかを検索していきます。
eventsテーブルのevent_summaryに、ユーザーが行ったイベント（新規登録やログイン、町に入ったなど）を実行した、イベント情報のデータが入っています。
今回はこのデータをメインとして、学習を進めていきます。
そして最後に今回学習するテキスト検索について軽く触れ、前回の学習としました。
今回使用するサンプルデータベースは前回までと同じようにこちらです。
今回学習する内容は、前回最後に触れた通りテキスト検索についてです。
どのユーザーが、いつ、どこで、何をしたのかを検索していきます。
eventsテーブルのevent_summaryに、ユーザーが行ったイベント（新規登録やログイン、町に入ったなど）を実行した、イベント情報のデータが入っています。
今回はこのデータをメインとして、学習を進めていきます。
2. ユーザーが実行したイベントを表示してみる
まずはじめに、各ユーザーがいつ、何のイベントを実行したのかを調べるSQLを作成します。
SELECT
userID,
startTime,
events.event_summary
FROM
eventlog
INNER JOIN events ON events.eventID = eventlog.eventID
ORDER BY userID;
このSQLを実行すると、
このように、ユーザーが登録したり、ログインしたりと、イベント情報が表示されます。
次に、userIDが2のユーザーに絞り込んで、登録やログインなどのシステム情報を非表示にして表示してみます。
これらのシステムイベントは、eventsテーブルにあるevent_stageカラムが0で登録されているので、event_stageカラムが0以外のイベントを表示するようにSQLを作成します。
SELECT
userID,
startTime,
events.event_summary
FROM
eventlog
INNER JOIN events ON events.eventID = eventlog.eventID
WHERE
userID = 2 AND events.event_stage <> 0;
WHEREを使い、userIDが2、それからeventsテーブルのevent_stageを<> 0（0以外）として表示させています。
このSQL文を実行すると、
このように、登録やログインなどのシステム情報が表示されず、ユーザーがゲームをプレイしている流れがわかりやすく表示されました。
次に、全ユーザーに対して、中ボスやラスボスと戦ったイベントだけを抽出して表示させます。
ボス達との闘いは、event_summaryに、”~~との闘い”のようにデータが保存されてあるので、その文字列に対して検索をする必要があります。
そのために、LIKE命令を使ってSQLを作成します。
SELECT
userID,
startTime,
events.event_summary
FROM
eventlog
INNER JOIN events ON events.eventID = eventlog.eventID
WHERE
events.event_stage <> 0
AND events.event_summary LIKE '%との闘い'
ORDER BY
userID, startTime;
WHERE句で、event_stageが0以外のものでシステムイベントを除きつつ、event_summaryに、との闘いで終わる文字列をLIKE句を使って検索しています。
このSQLを実行すると、
このように~~との闘いと、ボス戦のみが表示されました。
LIKEで使用している%は、0文字以上の任意の文字列を検索しています。
ですので、’%との闘い’と指定した場合は亡霊との闘いやダダメシとの闘いなどが表示されます。
いわゆるワイルドカードと呼ばれるもので、他にも_があり、こちらは_は任意の1文字を指定します。
例えばこれをLIKE句で、’亡霊との%’ で指定した場合は、このようになります。
亡霊との闘い、亡霊との会話のみが表示されました。
SELECT
userID,
startTime,
events.event_summary
FROM
eventlog
INNER JOIN events ON events.eventID = eventlog.eventID
ORDER BY userID;
このSQLを実行すると、
このように、ユーザーが登録したり、ログインしたりと、イベント情報が表示されます。
次に、userIDが2のユーザーに絞り込んで、登録やログインなどのシステム情報を非表示にして表示してみます。
これらのシステムイベントは、eventsテーブルにあるevent_stageカラムが0で登録されているので、event_stageカラムが0以外のイベントを表示するようにSQLを作成します。
SELECT
userID,
startTime,
events.event_summary
FROM
eventlog
INNER JOIN events ON events.eventID = eventlog.eventID
WHERE
userID = 2 AND events.event_stage <> 0;
WHEREを使い、userIDが2、それからeventsテーブルのevent_stageを<> 0（0以外）として表示させています。
このSQL文を実行すると、
このように、登録やログインなどのシステム情報が表示されず、ユーザーがゲームをプレイしている流れがわかりやすく表示されました。
次に、全ユーザーに対して、中ボスやラスボスと戦ったイベントだけを抽出して表示させます。
ボス達との闘いは、event_summaryに、”~~との闘い”のようにデータが保存されてあるので、その文字列に対して検索をする必要があります。
そのために、LIKE命令を使ってSQLを作成します。
SELECT
userID,
startTime,
events.event_summary
FROM
eventlog
INNER JOIN events ON events.eventID = eventlog.eventID
WHERE
events.event_stage <> 0
AND events.event_summary LIKE '%との闘い'
ORDER BY
userID, startTime;
WHERE句で、event_stageが0以外のものでシステムイベントを除きつつ、event_summaryに、との闘いで終わる文字列をLIKE句を使って検索しています。
このSQLを実行すると、
このように~~との闘いと、ボス戦のみが表示されました。
LIKEで使用している%は、0文字以上の任意の文字列を検索しています。
ですので、’%との闘い’と指定した場合は亡霊との闘いやダダメシとの闘いなどが表示されます。
いわゆるワイルドカードと呼ばれるもので、他にも_があり、こちらは_は任意の1文字を指定します。
例えばこれをLIKE句で、’亡霊との%’ で指定した場合は、このようになります。
亡霊との闘い、亡霊との会話のみが表示されました。
3. URLを取り出してみる
では、今度はサービスを呼び出すための特定のURLを取り出してみます。
まずは、イベントURLを表示します。
SELECT
userID,
startTime,
events.event_summary,
events.event_url
FROM
eventlog
INNER JOIN events ON events.eventID = eventlog.eventID
WHERE
events.event_stage <> 0
ORDER BY
userID, startTime;
このSQLを実行すると、
このように、イベントを呼び出すためのURLが表示されました。
例えば1番上のevent_summaryがタイトルの行では、タイトルを呼び出すためにprg/paiza/1/titleというURLを呼び出しています。
ではこのevent_urlから、dungeonという文字列が含まれているURLだけを取り出してみます。
SELECT
userID,
startTime,
events.event_summary,
events.event_url
FROM
eventlog
INNER JOIN events ON events.eventID = eventlog.eventID
WHERE
events.event_stage <> 0
AND events.event_url LIKE '%dungeon%'
ORDER BY
userID, startTime;
LIKE句で、dungeonを含むevent_urlを検索しています。
このSQLを実行すると、
このように、dungeonを含んだurl、イベントのみが表示されました。
LIKE句を使うと、柔軟に文字列を検索することができ、非常に便利です。
まずは、イベントURLを表示します。
SELECT
userID,
startTime,
events.event_summary,
events.event_url
FROM
eventlog
INNER JOIN events ON events.eventID = eventlog.eventID
WHERE
events.event_stage <> 0
ORDER BY
userID, startTime;
このSQLを実行すると、
このように、イベントを呼び出すためのURLが表示されました。
例えば1番上のevent_summaryがタイトルの行では、タイトルを呼び出すためにprg/paiza/1/titleというURLを呼び出しています。
ではこのevent_urlから、dungeonという文字列が含まれているURLだけを取り出してみます。
SELECT
userID,
startTime,
events.event_summary,
events.event_url
FROM
eventlog
INNER JOIN events ON events.eventID = eventlog.eventID
WHERE
events.event_stage <> 0
AND events.event_url LIKE '%dungeon%'
ORDER BY
userID, startTime;
LIKE句で、dungeonを含むevent_urlを検索しています。
このSQLを実行すると、
このように、dungeonを含んだurl、イベントのみが表示されました。
LIKE句を使うと、柔軟に文字列を検索することができ、非常に便利です。
4. LIKEを使って色々な検索をしてみる
では最後に、LIKEを使ってイベントやURLだけではなく、このサンプルデータベースに保存されているいろんな文字列の検索をします。
まずは、areaテーブルから、ユーザーが登録している都道府県名を表示します。
ここでは、’島’が使われている都道府県名のみを表示してみたいと思います。
SELECT
areaID,
area_name
FROM
area
WHERE
area_name LIKE '%島%';
このSQL文を実行すると、
このように、島が使われている都道府県名のみが表示されました。
さらに、これにユーザーを追加し、’島’を含む都道府県で登録したユーザーを調べてみます。
SELECT
userID,
name,
area.areaID,
area_name
FROM
users
INNER JOIN area ON area.areaID = users.areaID
WHERE
area.area_name LIKE '%島%';
このSQL文を実行すると、
このように表示されました。
‘島’がつく県は５つありましたが、登録しているユーザーで’島’が付く都道府県に住んでいるユーザーは全員広島県在住ということがわかりました。
まずは、areaテーブルから、ユーザーが登録している都道府県名を表示します。
ここでは、’島’が使われている都道府県名のみを表示してみたいと思います。
SELECT
areaID,
area_name
FROM
area
WHERE
area_name LIKE '%島%';
このSQL文を実行すると、
このように、島が使われている都道府県名のみが表示されました。
さらに、これにユーザーを追加し、’島’を含む都道府県で登録したユーザーを調べてみます。
SELECT
userID,
name,
area.areaID,
area_name
FROM
users
INNER JOIN area ON area.areaID = users.areaID
WHERE
area.area_name LIKE '%島%';
このSQL文を実行すると、
このように表示されました。
‘島’がつく県は５つありましたが、登録しているユーザーで’島’が付く都道府県に住んでいるユーザーは全員広島県在住ということがわかりました。