ITその他

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

2020年4月27日

eyecatch_himaweb-db

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

DBサーバー

 

前回の記事はこちら。

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

 

早速、はじめていきましょう!

 

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

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

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

○新規登録してくれた方の会員データは追加(Create)してあげる必要があります。

○会員のログインがあれば、適切な会員データを検索(Read)してあげる必要があります。

○会員データの更新(Update)削除(Delete)が必要な場合もあるでしょう。

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

 

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

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

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

 

リレーショナルな表形式

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

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

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

 

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

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

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

RDB01

 

なんで2つに分けるの?1つでいいじゃん・・・。

 

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

 

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

RDB02

 

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

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

○リレーショナルの場合は、「表1」の【会員ID:01】の1行のみを変更すればOKです。

○リレーショナルではない場合は、【会員ID:01】の行(レコード)すべてを変更する必要があります。

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

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

 

1つの表をリレーショナルに分割していくことを「正規化」と呼んだりします。気になった方は調べてみてください!

 

SQLでデータを操作する

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

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

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

 

言語といっても、そんなに難しいものではありません。(※仕事で扱うようになると、とても奥が深いです・・・。データベース管理者は高年収?)

 

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

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

 

おさらい

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

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

RDB03

 

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

 

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

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

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

 

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

INSERT  INTO  MEMBER_MEMO(ID, DATE, MEMO)  VALUES ( '01', '20/04/27', '結婚したい');

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

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

 

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

RDB04

 

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

INSERT  INTO  テーブル名(カラム1 [ , カラム2 ]・・・)  VALUES ( 値1 [ , 値2 ]・・・);

 

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

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

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

 

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

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

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

 

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

RDB05

 

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

SELECT  カラム名  FROM  テーブル名  WHERE  検索条件;

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

 

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

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

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

 

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

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

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

 

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

RDB06

 

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

UPDATE  テーブル名  SET  列名1 = 値1 [,列名2 = 値2]・・・  WHERE  検索条件;

 

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

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

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

 

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

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

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

 

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

RDB07

 

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

DELETE  FROM  テーブル名  WHERE  検索条件;

 

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

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

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

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

 

複雑なSQLには、句や'サブクエリ'というものがたくさん。SQLを使いこなせるとExcelが不便に感じるようになるかも・・・?

主キーと外部キー

主キー

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

RDB03

 

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

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

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

【主キー】

○テーブルのレコードを一意に特定できるカラムのこと。

○主キーを設定することでデータの整合性をとりやすくなる、データの検索処理が高速化する。

○ID='01'が2つあるなど、ダブりがあってはいけない(主キー制約)。

 

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

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

 

外部キー

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

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

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

 

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

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

【外部キー】

○関連付けされた他のテーブルの主キーのこと。

○外部キーを設定することでデータの整合性をとりやすくなる。

 

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

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

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

 

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

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

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

【RDBMSのメジャーな製品】

○Oracle:データベース界の巨人・Oracle社のDB。とにかく高機能・高性能。世界1位のシェア。有償(とても高価)。

○MySQL:Oracle傘下のオープンソースDB。シンプルな利用用途では十分。世界2位のシェア。Editionによって無償/有償。

○PostgreSQL:オープンソースではMySQLと並んでメジャーなDB。商用製品に引けをとらない機能の高さ。世界4位のシェア。無償。

○SQLServer:米・Microsoft社のDB。Windowsサーバーを使う場合に採用される?世界3位のシェア。有償。(使ったことがないので、正直あまり知らないです・・・)

※業界シェアは2019年12月時点のものです。

 

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

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

以下では、MySQL(Community Edition・無償)を使って、実際にDBを触っていきます。

 

MySQLを触ってみよう

MySQLをVPSにインストール

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

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

 

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

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

サボりではないですよ!画面キャプチャを撮るのが面倒だからではないですよ!

 

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

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

 

mysql01

 

MySQLにログイン

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

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

# mysql -u root -p

 

mysql02

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

 

データベースの作成

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

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

CREATE DATABASE MEMOAPP;

 

mysql03

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

 

MySQLにユーザーの作成

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

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

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

 

mysql04

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

 

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

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

exit;   #MySQLからログアウト

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

mysql05

 

テーブルの作成

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

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

RDB03

 

まずは、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;

mysql06

 

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

DESC MEMOAPP.MEMBER_INFO;

mysql07

 

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

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

○Type:カラムの型。int(3) unsigned zerofillは「先頭を0で埋めた3桁の整数」なので、IDは'001'のように設定されます。varchar(30)は「30バイトまでの可変長文字列」を表します。

○Null:NULL許容。NOは「カラムの値にNULLを許容しない」を表します。つまり、レコードが追加される時、ID・MAIL・PASSWDは空白であってはならないという意味です。

○Key:キー。PRIMARY KEY(PRI)、FOREIGN KEY(MUL)に設定しているかを表します。

○Default:デフォルト値。カラムの値を指定しなかった場合の値。

○Extra:追加設定。auto_incrementは連番を表します。IDは'001'、'002'、・・・のように連続した整数に設定されます。

 

同じ要領で、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;

mysql08

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

 

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

use MEMOAPP;

mysql11

 

テストデータを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');

mysql09

 

「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');

mysql10

 

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

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

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

SELECT * FROM MEMBER_INFO;

SELECT * FROM MEMBER_MEMO;

mysql12

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

 

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

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

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

mysql13

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

 

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

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

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

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

mysql14

 

「【会員: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';

mysql15

 

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

 

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

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

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

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

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

mysql16

 

「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');

mysql17

 

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

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

 

JOIN句:表の結合

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

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

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

RDB02

 

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

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

mysql18

 

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

 

SELECT  カラム名  FROM  テーブル名1  JOIN テーブル名2  ON  テーブル名1.結合キー  =  テーブル名2.結合キー;

 

JOINにはOUTER JOINなど、様々な結合種類があります。使いこなせれば、スプレッドシートいらずになるかも!

 

 

 

おわりに

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

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

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

 

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

AP

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

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

  • この記事を書いた人
  • 最新記事
しよこ

しよこ

ゆるりと楽に生きたい20代ITえんじにあ。ITのことはよく分からない。ライターをやったり、Web開発をやったり、投資をやったり、興味が散らかりがち。最近はペンタブに挑戦中・・・。

-ITその他
-, , ,

Copyright© pocco log , 2020 All Rights Reserved.