『ドラゴンクエスト』の武器データでSQLを学ぶ記事

はじめに

今回の記事では、RPG『ドラゴンクエスト』に登場する武器のデータを用いてSQLを、チートシート形式で徹底解説する。なお、本記事にて登場する武器や防具はドラゴンクエスト大辞典のデータを参照する。

対象とする読者

これからSQLを学ぶ初心者や、データベース(SQL)で詰まっている初心者。

SQLとは

SQL（Structured Query Language）は、リレーショナルデータベース管理システム（RDBMS）でデータを操作・管理するための標準的な言語だ。SQLではデータの取得、挿入、更新、削除などの操作を行うためのコマンドが提供されている。SQLはデータベースの基礎となる言語であり、エンジニアだけでなく、データアナリストや科学者にも利用されている。

SQLは大きく分けて次のようなコマンドから成り立っている。

命令	説明	文法
INSERT	データの追加	INSERT INTO テーブル名 カラム名 VALUES [値];
SELECT	データの参照	SELECT カラム名1, カラム名2, ... FROM テーブル名 [WHERE 絞込条件];
UPDATE	データの更新	UPDATE テーブル名 SET カラム名1=値1 [, カラム名2=値2 ...] [WHERE 絞込条件];
DELETE	データの削除	DELETE FROM テーブル名 [WHERE 絞込条件];

また、SQLはデータベース間での基本的な互換性を保証するための標準がある。各データベース製品は独自の拡張機能を提供している。SQLの知識はさまざまな環境やアプリケーションで役立ちます。

プログラミングの初心者にとって、SQLはデータの操作や分析に必要な重要なスキルだ。それは直感的な構文と豊富な機能性を持ち、あらゆる種類のデータ問題を解決するための強力なツールとなるからである。

本記事では、環境構築をできるだけ行わずに動作できるようにSQLiteを用いて説明する。

SQLiteとは

SQLiteサーバー不要で詳細な設定が要らず、軽量であり、ファイルベースのリレーショナルデータベース管理システムであるため、ローカルでの使用に最適だ。

今回の記事で使用するデータ

SQLを用いたデータベースの操作を説明する前に、今回の記事で使用するデータを以下に用意する。これは、『ドラゴンクエスト』(初代)で登場する武器の一覧だ。

名前	攻撃力	買値(G)	売値(G)
たけざお	2	10	5
こんぼう	4	60	30
どうのつるぎ	10	180	90
てつのおの	15	560	280
はがねのつるぎ	20	1,500	750
ほのおのつるぎ	28	9,800	4,900
ロトのつるぎ	40	null	null

学習の準備

(1) SQLiteのインストール

公式サイトから適切なバージョンを選択してインストールする。LinuxやmacOSでは、デフォルトでインストールされている。

(2) データベースの作成

ターミナル(Windowsであればコマンドプロンプト)を開き、以下のコマンドを使用してデータベースを新しく作る。

sqlite3 mydatabase.db

(3) テーブルの作成

次に、以下のSQLを使って新しくデータベースを作成する。

mydatabase.db

CREATE TABLE Items (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    attack INTEGER NOT NULL,
    cost_price INTEGER,
    selling_price INTEGER
);

上述のSQLコードはItemsという名前の新しいテーブルを作成するものだ。このテーブルは以下の5つの列から構成される。

まず、idという名前の列はテーブル内の各行に一意の識別子を提供する。この列のデータ型はSERIALで、これは自動的に付け足される整数を意味する。この列はPRIMARY KEYとして設定されており、これはこの列がテーブル内の各行を一意に識別するためのキーであることを示す。

次に、nameという名前の列はそれぞれのアイテムの名前を保持する。この列のデータ型はVARCHAR(255)で、これは255文字まで入る文字列を意味します。また、NOT NULL制約が付けられており、これはこの列が空値(NULL)を許さないことを示します。

その後、attackという名前の列は各アイテムの攻撃力を保持する。この列のデータ型はINTEGERで、これは整数を意味する。この列もNOT NULL制約が付けられていて、空値は許されていない。

次に、cost_priceという名前の列は買値を保持する。この列のデータ型もINTEGERで、この列はNULLを許す設定になっている。

最後に、selling_priceという名前の列は各アイテムの売値を保持する。この列のデータ型もINTEGERで、こちらもNULLを許す設定になっている。

(4) データの挿入

次に、今回の記事で使用するデータを挿入する。以下のようにSQLコードを書く。データを挿入する際にはINSERT文を用いる。

mydatabase.db

INSERT INTO Items (name, attack, cost_price, selling_price) VALUES ('たけざお', 2, 10, 5);
INSERT INTO Items (name, attack, cost_price, selling_price) VALUES ('こんぼう', 4, 60, 30);
INSERT INTO Items (name, attack, cost_price, selling_price) VALUES ('どうのつるぎ', 10, 180, 90);
INSERT INTO Items (name, attack, cost_price, selling_price) VALUES ('てつのおの', 15, 560, 280);
INSERT INTO Items (name, attack, cost_price, selling_price) VALUES ('はがねのつるぎ', 20, 1500, 750);
INSERT INTO Items (name, attack, cost_price, selling_price) VALUES ('ほのおのつるぎ', 28, 9800, 4900);
INSERT INTO Items (name, attack, cost_price, selling_price) VALUES ('ロトのつるぎ', 40, NULL, NULL);

上述のSQLコードでは、INSERT INTOを用いてデータを新規作成している。INSERT INTO文の部分が繰り返しになっいるので、本来であれば以下のようにコードをまとめるべきである。ところが、SQLiteでは以下のようなコードの書き方をサポートしていない。

INSERT INTO Items (name, attack, cost_price, selling_price) VALUES 
('たけざお', 2, 10, 5),
('こんぼう', 4, 60, 30),
('どうのつるぎ', 10, 180, 90),
('てつのおの', 15, 560, 280),
('はがねのつるぎ', 20, 1500, 750),
('ほのおのつるぎ', 28, 9800, 4900),
('ロトのつるぎ', 40, NULL, NULL);

ただ、SQLiteではUNION ALL SELECTを用いて複数の行を1つのクエリで挿入できる。

INSERT INTO Items (name, attack, cost_price, selling_price) 
SELECT 'たけざお', 2, 10, 5 UNION ALL 
SELECT 'こんぼう', 4, 60, 30 UNION ALL 
SELECT 'どうのつるぎ', 10, 180, 90 UNION ALL
SELECT 'てつのおの', 15, 560, 280 UNION ALL
SELECT 'はがねのつるぎ', 20, 1500, 750 UNION ALL
SELECT 'ほのおのつるぎ', 28, 9800, 4900 UNION ALL
SELECT 'ロトのつるぎ', 40, NULL, NULL;

上述のSQLの書き方はSQLiteで複数の行を1つのクエリで挿入する唯一の方法になる。

汎用的なコマンド一覧

全部のデータを出力

SELECT * FROM Items;

Itemsテーブルのすべての行を選択する。

特定の列を選択

SELECT name, attack FROM Items;

nameとattackの2つの列だけを選択する。

条件に一致するデータの選択

SELECT * FROM Items WHERE attack > 10;

attackが10より大きいすべての行を選択する。

データの更新

UPDATE Items SET attack = 30 WHERE name = 'てつのおの';

nameがてつのおのの行のattackを30に更新する。

データの削除

DELETE FROM Items WHERE name = 'たけざお';

nameがたけざおの行を削除する。

データの並び替え

SELECT * FROM Items ORDER BY attack DESC;

attackを基準にデータを降順で並び替える。

データを集約する

SELECT AVG(cost_price) AS AverageBuyPrice FROM Items;

すべての行のcost_priceの平均値を計算する。

データのグループ化と集約

SELECT attack, COUNT(*) AS NumberOfItems
FROM Items
GROUP BY attack;

attackごとにアイテムの数を数える。

グループ化したデータに対する条件付け

SELECT attack, COUNT(*) AS NumberOfItems
FROM Items
GROUP BY attack
HAVING COUNT(*) > 1;

attackごとにアイテムをグループ化し、そのグループが1つ以上のアイテムを含む場合だけに結果を表示する。

文字列の検索

SELECT * FROM Items WHERE name LIKE '%つるぎ%';

nameにつるぎを含む全てのアイテムを選択する。

結合

SELECT Items.name, Items.attack, OtherTable.other_name
FROM Items
INNER JOIN OtherTable ON Items.name = OtherTable.name;

ItemsテーブルとOtherTableテーブルを結合し、nameが一致する行を選択する。

複数の条件に一致するデータの選択

SELECT * FROM Items WHERE attack > 10 AND cost_price < 500;

攻撃力が10より大きく、かつ、買値が500より小さいすべての行を選択する。

NULL値の検索

SELECT * FROM Items WHERE cost_price IS NULL;

cost_priceがすべてNULLの行を選択する。

余談：ACID(Atomicity, Consitency, Isolation, Durability)

SQLを使うデータベース(RDBMS)を用いる上で必要不可欠な概念の一つに「ACID」がある。ACIDはトランSQLを使うデータベースが持つべき4つの特性を表現したアルファベットのアクロニムだ。

A: Atomicity(原子性)

「全体、あるいは無し」を意味する。トランザクションとは、一つのまとまりの操作を示す。そのすべてが完全に実行されるか（コミット）、あるいは一つも実行されないか（ロールバック）のどちらか一方となる。

例えば、プレイヤーが「どうのつるぎ」を560Gで購入し、その代金を自分の所持金から支払うという取引を行うと仮定する。これらの操作、いわゆる承認の購入と金銭の移動は一つのトランザクションとしてまとめられる。もし何らかの理由で、所持金から代金を差し引く操作が失敗した場合、原子性により、このトランザクション全体がロールバックされ、「どうのつるぎ」の購入も行われない状態に戻ってしまう。

下の図は「どうのつるぎを購入する」という処理をトランザクション形式で置き換えた時の図解である。

C: Consistency(一貫性)

トランザクションがデータベースの一貫性を保つという性質だ。言い換えれば、各トランザクションはデータベースを一貫性のある状態から別の一貫性のある状態へと変化させるべき、ということになる。

例えば、プレイヤーが7000Gで、本来9800Gもする「ほのおのつるぎ」を購入しようとする場合、一貫性の原則によりトランザクションが許可されない。これを『ドラゴンクエスト』の世界に例えると、「もうしわけありませんが、おかねがたりないようです」というような類のメッセージが出る。これが一貫性になる。

I: Isolation(独立性)

独立性とは、並列して行われる複数のトランザクションが、互いに影響を与えないという性質そのものを意味する。

例えば、複数のプレイヤーが、ゲーム内で同時に「どうのつるぎ」を購入する場合を考える。これは独立性の原則により、これらのトランザクションはお互いのゲームシステムに影響を与えず、正しく処理される。

D: Durability(永続性)

永続性とは、一度コミットされたトランザクションは恒久的に保存され、システム障害やクラッシュが発生した場合でもその結果が失われることはないという性質になる。

例えば、プレイヤーが「たけざお」を購入すると、そのトランザクション(「たけざお」を購入した履歴)はゲーム内で永続的に保存されるということになる。

これにより、武器屋というデータベースシステムがクラッシュしても、再起動後にデータベースが最後に保存した状態を正常に反映できるのだ。

参考記事

https://qiita.com/devopsCoordinator/items/0f742158caf0fdb533e3