【初心者向け】暇を持て余した日曜Webアプリ開発(その2：DB編)

暇を持て余した日曜Webアプリ開発、今回はデータベースを作ります。

 

前回の記事はこちら。

【初心者向け】暇を持て余した日曜Webアプリ開発(その1：サーバー編)

 

 

データベースってなんだ？

複雑・大量のデータを蓄積して管理するもの

例えば、ある会員サイトの会員データについて、管理者がすべきことを挙げてみます。

このようなデータの蓄積・管理(CRUD：Create/Read/Update/Delete)を効率的に行うために存在するのが、データベースという仕組みです。

 

会員サイトに限らず、データベースは世の中の至る所で利用されています。

今や、データの蓄積・管理なしにできることの方が少ないですし、データを上手に使えばビジネスチャンスを増やすことができますね。

データが複雑・大量であるほど、データベースの得意とする効率的な管理を実感することができます。

 

リレーショナルな表形式

データベースにも様々な種類がありますが、一般的なのは「リレーショナルデータベース(RDB)」と呼ばれるものです。

リレーション(Relation)はそのまま「関連・関係」という意味。

RDBでは、データを管理する表どうしがそれぞれ関連を持っていることが特徴です。

 

どういう意味か。例えば会員制のメモ帳サイトがあったとしましょう。

とても単純化して考えると、データベース内の表は「表1：会員のMailとPasswordを管理」と「表2：会員のMemoを管理」の2つになります。

表1と表2は両方とも「会員ID」の列を持っており、2つの表は会員IDで関連付けがされていることになります。

 

 

そんな疑問ももちろんあります。しかし、1つの表ではNGな理由があるのです。

 

リレーショナルではなく1つの表で管理する場合は、さきほどの2つをまとめて、次のような形になります。

 

これでも管理はできないことはなさそうに見えます。

しかし、【会員ID：01】がメールアドレスを変更した場合、どうなるでしょうか。

管理の手間や、処理の負荷を考えると、リレーショナルに設計しておくほうが楽になります。

企業などで管理するデータはとても複雑な構造になっており、実際のところ単一の表では無理があるのです。

 

 

SQLでデータを操作する

Excelやスプレッドシートを使えば、値の変更などのデータ操作を視覚的に行うことができます。

しかし、データベースを使う場合にはそのようにできません。

データベースでデータを操作するためには、「SQL (Structured Query Language)」と呼ばれる言語を使います。

 

 

データ管理の基本は「CRUD：Create/Read/Update/Delete」です。

それぞれに対応したSQLがあるので、さきほどのメモ帳サイトを例に紹介します。

 

おさらい

メモ帳サイトのデータ構造を、おさらいのため再度貼り付けします。

説明しやすいように、表と列にそれぞれ名前を付けました。

 

なお、以下では「表：テーブル」「行：レコード」「列：カラム」と呼びます。

 

Create (INSERT文)：レコードを作成

【会員：01】が'20/04/27'のメモを追加しました。

MEMBER_MEMOテーブルに新規レコードを作成する必要があります。

 

この場合にデータベースに発行するSQLは、Createに対応する「INSERT」となります。

SQLは英語が読めれば直感的に理解できるものが多いです。

「MEMBER_MEMOに()のVALUESのレコードを挿入せよ！」と命令しているのです。

 

このSQLをデータベースに発行すると、MEMBER_MEMOに新規レコード(ピンク枠)が追加されます。

 

なお、一般的なINSERT文は次のように書きます。

 

Read (SELECT文)：レコードを検索

【会員：01】が'20/04'に書いたメモを振り返ります。

MEMBER_MEMOテーブルから、'20/04'のメモを検索する必要があります。

 

この場合にデータベースに発行するSQLは、Readに対応する「SELECT」となります。

「MEMBER_MEMOからIDが'01'かつDATEが'20/04/01'〜'20/04/01'という条件でDATEとMEMOを選択せよ！」という命令です。

 

このSQLをデータベースに発行すると、MEMBER_MEMOのピンク枠のレコードが選択されます。

 

なお、一般的なSELECT文は次のように書きます。

すべてのカラムを選択する場合、「SELECT * FROM ~」という具合で  *  に置き換えます。

 

Update (UPDATE文)：レコードを更新

【会員：01】がメールアドレスを'a@com'から'x@com'に変更するようです。

MEMBER_INFOテーブルの【会員：01】のMAILカラムを更新する必要があります。

 

この場合にデータベースに発行するSQLは、Updateに対応する「UPDATE」となります。

「IDが'01'という条件でMAILを'x@com'とするようにMEMBER_INFOを更新せよ！」という命令です。

 

このSQLをデータベースに発行すると、MEMBER_INFOのピンク枠の値が更新されます。

 

なお、一般的なSELECT文は次のように書きます。

 

Delete (DELETE文)：レコードを削除

【会員：01】が'20/04/27'のメモをやっぱり消すことにするようです。

MEMBER_MEMOテーブルの'20/04/27'のレコードを削除する必要があります。

 

この場合にデータベースに発行するSQLは、Deleteに対応する「DELETE」となります。

「MEMBER_MEMOからIDが'01'かつDATEが'20/04/27'という条件でレコードを削除せよ！」と命令しているのです。

 

このSQLをデータベースに発行すると、MEMBER_INFOのピンク枠に存在したレコードが削除されます。

 

なお、一般的なDELETE文は次のように書きます。

 

句

SELECTやUPDATEなどの「文」の中で、条件などを指定する要素は「句」と呼ばれます。

FROMは「FROM句」、WHEREは「WHERE句」です。

他にも、「JOIN句(テーブルの結合)」「ORDER BY句(レコードの並び替え)」「GROUP BY句(レコードの集約)」など、句の種類は様々あります。

すべては書ききれないので、今後必要になった際に都度紹介します。

 

主キーと外部キー

主キー

くどいようですがもう一度、メモ帳サイトのデータ構造を見てみましょう。

 

MEMBER_INFOテーブルでは、IDカラムでレコードを一意に特定することができます。

このように、レコードを一意(unique)に特定できるカラムを「主キー(PRIMARY KEY)」と呼びます。

テーブルを設計するうえで、かなり大事になるので次にまとめます。

 

なお、後述しますが、設計者自身が「この列は主キーだよ！」と教えてあげる必要があります。

絶対にないといけない、というものではないので、DB側で勝手に設定してくれません。

 

外部キー

MEMBER_MEMOテーブルを見てみましょう。

このテーブルでは、IDカラムとDATEカラムによって、レコードを一意に特定できます(複合キー)。

ここで、IDカラムはMEMBER_INFOテーブルの主キーになっています。

 

この場合のIDカラムのように、関連付けされた他のテーブルのキーを「外部キー(FOREIGN KEY)」と呼びます。

外部キーもテーブルを設計するうえで、かなり大事なので次にまとめます。

 

では、MEMBER_MEMOテーブルに、MEMBER_INFOテーブルに存在しないID、例えばID='99'のメモを追加できるでしょうか？

これはデータの整合性が崩れてしまうのでできません(外部キー制約)。存在しない会員のメモがあったらおかしいですよね。

外部キーを設定する意味は、「関連するテーブルとのデータの整合性を保つ」ことにあります。

 

データベース管理システム

スプレッドシートにMicrosoft Excel、Numbers、Google Sheets、・・・など様々な製品があるように、リレーショナルデータベースを管理する製品も様々です。

ここでは、リレーショナルデータベース管理システム(RDBMS)でメジャーな製品を簡単に紹介します。

 

基本的な使い方はいずれも同じですが、細かいところでSQLの文法に違いがあったり、実現できることに差があります。

どれを採用するかは何をしたいかによりますし、極論を言えば好みの問題だと思います。

 

MySQLを触ってみよう

MySQLをVPSにインストール

VPSについては、前回の記事を参照ください。

【初心者向け】暇を持て余した日曜Webアプリ開発(その1：サーバー編)

 

VPSへのMySQLのインストールは、ConoHaの公式マニュアルを参照しながら「3. セキュリティを高める」まで行ってください。どのVPSを利用していても、インストールコマンドはおそらく同じです。

インストールの手順を本記事に書いても、公式マニュアルと同じコマンドを写すだけになってしまいますので・・・。

 

MySQLにデータベースを作成する

マニュアルに沿ってMySQLをインストールして、「3. セキュリティを高める」まで完了すると、次のような画面になっていると思います。

 

 

MySQLにログイン

コンソールから次のコマンドを打つと、MySQLのログインパスワードを求められます。

インストール時に設定したパスワードを入力すると、MySQLにログインします。

# mysql -u root -p

 

この画面になればOKです。

 

データベースの作成

まだMySQLの中には何もないので、次にデータベースを作成します。

次のコマンドで「MEMOAPP」というデータベースを作成します。

CREATE DATABASE MEMOAPP;

 

「Query OK」と返ってくればOKです。

 

MySQLにユーザーの作成

現在はMySQLにrootでログインしているので、さきほど作成したMEMOAPP用のユーザー「usr01」を作成しておきます。ユーザー名・パスワードはお好きなもので。

※公式マニュアルのコマンドではバージョンの違いでエラーになるので注意！

CREATE USER 'usr01'@'localhost' IDENTIFIED BY 'パスワード';
GRANT ALL PRIVILEGES ON MEMOAPP.* TO 'usr01'@'localhost';

 

両方とも「Query OK」と返ってくればOKです。

 

次に、作成したusr01にスイッチします。

一度MySQLをexitコマンドで抜けて、usr01でログインし直します。

exit;   #MySQLからログアウト

mysql -u usr01 -p;   #MySQLにusr01でログイン

 

テーブルの作成

現在、MEMOAPPのデータベースにはテーブルがありません。

さきほどのメモ帳サイトの「MEMBER_INFO」と「MEMBER_MEMO」の2つのテーブルを作成してみます。

 

まずは、CREATE TABLE文を使って、MEMBER_INFOテーブルを作成します。

とりあえず細かいことは気にせず、おまじないと思って、次のSQLを入力してください。

CREATE TABLE MEMOAPP.MEMBER_INFO (
   ID int(3) UNSIGNED ZEROFILL NOT NULL AUTO_INCREMENT,
   MAIL varchar(30) NOT NULL,
   PASSWD varchar(30) NOT NULL,
   PRIMARY KEY (ID)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

 

MEMBER_INFOテーブルが作成できたか確認してみます。次のコマンドを入力してください。

DESC MEMOAPP.MEMBER_INFO;

 

CREATE TABLE文で設定した内容通りにテーブルを作成できています。

それぞれの設定内容について簡単に説明します。理解できなくても特に支障はないです。

 

同じ要領で、MEMBER_MEMOテーブルも作成します。

CREATE TABLE MEMOAPP.MEMBER_MEMO (
   ID int(3) UNSIGNED ZEROFILL NOT NULL,
   DATE DATE NOT NULL,
   MEMO varchar(500),
   FOREIGN KEY (ID) REFERENCES MEMBER_INFO(ID)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

以上でテーブルの作成ができました。

 

都度、「DB名.テーブル名」と入力するのは面倒なので、次のコマンドを入力しておきましょう。

use MEMOAPP;

 

テストデータをCreate (INSERT文)

MEMBER_INFOテーブルにテストデータを新規作成してみます。

Createの操作は「INSERT文」が対応するので、次のSQLを入力してみます。

INSERT INTO MEMBER_INFO(MAIL, PASSWD) VALUES('a@com', 'pass01');
INSERT INTO MEMBER_INFO(MAIL, PASSWD) VALUES('b@com', 'pass02');

 

「Query OK」と返ってくればOKです。この後のRead(SELECT文)で確認しましょう。

IDカラムはauto_incrementに設定しているので、INSERT文の中で指定しなくても自動で採番を行ってくれます。

 

また、MEMBER_MEMOテーブルについてもテストデータを新規作成しておきます。

INSERT INTO MEMBER_MEMO(ID, DATE, MEMO) VALUES('001', '2020/04/01', 'GOHAN TABETA');

INSERT INTO MEMBER_MEMO(ID, DATE, MEMO) VALUES('001', '2020/04/02', 'YOKU NETA');

INSERT INTO MEMBER_MEMO(ID, DATE, MEMO) VALUES('001', '2020/04/26', 'KARESHI HOSHII');

INSERT INTO MEMBER_MEMO(ID, DATE, MEMO) VALUES('001', '2020/04/27', 'KEKKON SHITAI');

INSERT INTO MEMBER_MEMO(ID, DATE, MEMO) VALUES('002', '2020/04/01', 'HA MIGAITA');

INSERT INTO MEMBER_MEMO(ID, DATE, MEMO) VALUES('002', '2020/04/02', 'FURO HAITTA');

 

テストデータをRead (SELECT文)

さきほど作成したテストデータをすべて検索してみます。

Readの操作はSELECT文、「すべてのレコード」は「SELECT *」で行うのでしたね。

SELECT * FROM MEMBER_INFO;

SELECT * FROM MEMBER_MEMO;

テストデータがちゃんと作成できていることが確認できました。

 

では、「【会員：001】が'20/04'に書いたメモ」を検索してみます。

SQLが長くなると、1行では非常に読みにくくなるので、適当な箇所で改行したり。(私は「句」で改行する派です。)

SELECT
   DATE, MEMO
FROM
   MEMBER_MEMO
WHERE
   ID = '01'
   AND DATE > '20/04/01'
   AND DATE < '20/04/30'
;

「【会員：001】が'20/04'に書いたメモ」のみを検索できました。

 

テストデータをUpdate (UPDATE文)

次に、「【会員：001】のメールアドレスを'a@com'から'x@com'に更新」してみます。

Updateの操作はUPDATE文が対応します。「【会員：001】」をWHERE句で条件指定します。

UPDATE MEMBER_INFO SET MAIL='x@com' WHERE ID='01';

 

「【会員：001】のメールアドレスを'x@com'に更新」ができました。

 

テストデータをDelete (DELETE文)

次に、「【会員：001】の'20/04/27'のメモを削除」をしてみます。

Deleteの操作はDELETE文が対応します。「【会員：001】かつ'20/04/27'」をWHERE句で条件指定します。

DELETE FROM MEMBER_MEMO WHERE ID = '001' AND DATE = '20/04/27';

 

「【会員：001】の'20/04/27'のメモを削除」ができました。

 

テストデータで主キー制約の確認

今回のケースで主キー制約は、「主キーであるIDカラムに重複があってはいけない」ことです。

既に【会員：001】が存在している状況で、再度【会員：001】のレコードをINSERTしたらどうなるでしょうか。

次のSQLをDBに投入してみます。

INSERT INTO MEMBER_INFO(ID, MAIL, PASSWD) VALUES('001', 'a@com', 'pass01');

 

「Duplicate entry '001' for key 'PRIMARY'」と怒られました。

主キーが重複するようなレコードは、主キー制約によってDBが弾いてくれるのです。

 

テストデータで外部キー制約の確認

今回のケースで外部キー制約は、「存在しない会員のメモを追加してはいけない」ことです。データの整合性が取れなくなります。

MEMBER_INFOテーブルに存在しないID、例えばID='99'のメモをINSERTしようとするとどうなるでしょうか。

次のSQLをDBに投入してみます。

INSERT INTO MEMBER_MEMO(ID, DATE, MEMO) VALUES('099', '2020/04/28', 'TSUKARETA');

 

「a foreign key constraint fails」と怒られました。

MEMBER_INFOに存在しないIDのメモレコードは、外部キー制約でDBが弾いてくれるのです。

 

JOIN句：表の結合

今回はMEMBER_INFOとMEMBER_MEMOの2つにテーブルを分割しました。

しかし、2つのテーブルをくっつけて参照したいケースも、多々あります。

次のような状態でSELECT文の結果を返してほしい場合です。

 

このような場合では、次のJOIN句を使います。

SELECT
   *
FROM
   MEMBER_INFO
JOIN
   MEMBER_MEMO
   ON MEMBER_INFO.ID = MEMBER_MEMO.ID
;

 

MEMBER_INFOとMEMBER_MEMOの両方のテーブルにある、IDカラムをキーにして、2つのテーブルを結合した結果を参照できました。

 

 

 

 

 

おわりに

今回はデータベースについての話でした。

SQLを使ったデータ操作も慣れれば、スプレッドシートより快適に扱うことができます。(レコードの規模によりますが・・・)

SEさんと話をする時は「○○と△△をJOINして、□□でORDER BYしてSELECTする」とか言ったり。えっ、言わないですか？

 

次回は、アプリケーションについてプログラムを書いていきます。

アプリケーションから、今回作成したMySQLに対して「会員データください！」の処理を書いていきます。

JavaにするかAPIにしてしまうか、どのように作るかは未定です・・・が、コピペで作れるような記事にします。