phpMyAdminでリレーショナルデータベースを操作してみよう

はじめに

リンクをコピーしました

概要

リンクをコピーしました

リレーショナルデータベースに関する用語を深く理解するため、「phpMyAdmin」というデータベース接続クライアントの活用が効果的です。このツールを使ってリレーショナルデータベースを操作し、実際に手を動かしながら学習を進めましょう。

リレーショナルデータベースの操作には、MySQLやMariaDBなどのRDBMSが「データベースを利用する側」のSQL文の指示に基づいて行うことが分かりました。

しかし、phpMyAdminのような「データベース接続クライアント」を使用すると、SQLで命令文を記述することなく、Webブラウザ上で簡単にRDBMSに指示し、リレーショナルデータベースを操作できます。

phpMyAdminでは、Webブラウザ上の画面操作でデータベースやテーブルを作成できますが、実際には裏でSQL文が自動生成され、そのSQL文によってRDBMSがリレーショナルデータベースを操作しています。

目標

リンクをコピーしました

• phpMyAdminの基本操作を習得する
• リレーショナルデータベースの概念を実践的に理解する
• CRUD操作をマスターする：Create（作成）、Read（読み取り）、Update（更新）、Delete（削除）というデータベース操作の基本をphpMyAdminを用いて実践する
• 外部キー制約を適用してデータ整合性を保つ方法を学ぶ

今回はphpMyAdminを使用して、リレーショナルデータベース内に新たなテーブルを作成します。このデータベースは、以前にrailsコマンドを使用して作成したemployee_management_developmentです。

作成済みのデータベース

作成するテーブルには、社員のデータを管理するemployeesと部署のデータを管理するdepartmentsが含まれます。

これらのテーブルのカラム名や格納するデータの詳細は、以下の画像に示されています。

データベースに作成するテーブルと格納するデータの詳細

必要な前提条件・事前準備

リンクをコピーしました

この章を始める前に、リレーショナルデータベース（RDB）の基本用語についての知識が必要です。リレーショナルデータベースに関連する用語には、データベース、テーブル、カラム、行、主キー、外部キーなどが含まれます。また、phpMyAdminを使ったデータベース操作を行うためには、phpMyAdminへのログインが必要です。

• リレーショナルデータベースに関する基本用語と概念の理解
• phpMyAdminへのログイン方法

ログインに成功すると、以下のような画面が表示されることになります。

これらの準備を整えた後、次のセクションに進んでください。

データベースの定義

リンクをコピーしました

データベースとは、システムやユーザーが扱うデータを１つの場所に集めたものでしたね。そして、リレーショナルデータベースとは、各データを表のような形で整理して、互いに関連付けて扱えるデータベースだと学習しました。

まずは、phpMyAdminを使ってリレーショナルデータベースを作成してみます。

railsコマンドによってリレーショナルデータベースは既に用意されていますが、データ定義をphpMyAdminで学ぶために、別のリレーショナルデータベースを作成します。

データベース一覧を表示しよう

リンクをコピーしました

開発環境とテスト環境のデータベースは、railsコマンドで既に作成していました。

以下のように画面上部に表示されている「データベース」をクリックしてください。

クリックすると、データベース一覧画面に遷移します。

以下のようにコマンドで作成した「employee_management_development」と「employee_management_test」のデータベースが表示されますね。

画面上部に表示されている「データベース」をクリックする以外にも、以下のようにサイドバーの「employee」>「management」の中から確認することができます。

データベースを作成しよう

リンクをコピーしました

データベースの新規作成は、データベース一覧画面から行えます。

まずは、画面上部に表示されている「データベース」をクリックして、データベース一覧画面にアクセスしましょう。

データベースの新規作成は、「データベースを作成する」の下にある入力欄に「データベースの名前」と「照合順序」を入力して、「作成」をクリックします。

照合順序とは、値を比較する際のルールのことです。

データベース名にpikawakaと入力し、照合順序にutf8mb4_general_ciを選んでデーベースを作成してみましょう。

データベース一覧画面を表示してみると、以下のように作成したpikawakaという名前のデータベースも含まれていることが確認できます。

データベースを削除しよう

リンクをコピーしました

データベースの削除は、データベース一覧画面から行います。

以下のようにデータベース一覧画面に表示されるpikawakaにチェックして、下に表示されている「削除」をクリックしましょう。

「削除」をクリックすると、以下のように確認ダイアログが表示されます。そのまま「OK」をクリックしましょう。

データベース一覧画面を表示すると、pikawakaという名前のデータベースが削除されていることが確認することができます。

データベースを削除すると、元に戻すことができません。データベースを削除される際には、注意しましょう。

テーブル定義

リンクをコピーしました

テーブルとは、データを表のような形で格納する領域のことでしたね。リレーショナルデータベースでデータを扱うには、テーブルを作成する必要があります。

テーブルは、共通点を持った行の集合です。次のように「社員」という共通点を持つ行が集まることでemployeesテーブルができます。

またテーブルの行と列には、レコードとカラムという別名がありましたね。

レコードは「テーブルに格納されたデータ1件分」のことで、カラムは「テーブルに格納されたデータを構成する各項目」のことです。

employee_management_developmentのリレーショナルデータベースには、社員と部署の関係性のテーブルを作成していきます。

テーブルの詳細情報を確認しよう

リンクをコピーしました

これから作成するテーブルは、社員のデータを管理するemployeesと部署のデータを管理するdepartmentsです。

カラムには、「データ型」と「制約」を指定することができましたね。

データ型はテーブルに格納するデータの種類を限定するもので、制約はデータベースに格納するデータが満たさなければならない条件のことです。

NULL自身はデータの値ではなく、値が何も入っていない状態を指します。カラムに対して、「非NULL制約」を設定すると、NULLを禁止することができます。

employeesとdepartmentsの各テーブルで設定するデータ型と制約を確認します。

employeesテーブル

リンクをコピーしました

employeesテーブルで設定するデータ型と制約は、以下の通りです。

主キーであるidのカラムには、自動的に一意の値を付与してくれるAUTO_INCREMENTという機能を使用します。departmentsテーブルでも同様です。

ほとんどのカラムには、非NULL制約を設定しますが、birthdayのカラムだけは、デフォルト値をNULLにできるよう制約を設定しません。

departmentsテーブル

リンクをコピーしました

departmentsテーブルで設定するデータ型と制約は、以下の通りです。

それでは、実際に手を動かしてテーブルを作成していきましょう

データベースを選択しよう

リンクをコピーしました

テーブルを作成するデータベースを選びます。

画面上部に表示されている「データベース」をクリックして、データベース一覧画面からemployee_management_developmentをクリックしましょう。

以下のようにテーブルの管理画面が表示されます。データベースにはテーブルが作成されていないので、何もない状態です。

departmentsテーブルを作成しよう

リンクをコピーしました

作成するテーブル名はdepartmentsで、必要なカラムはidとnameです。

以下のように作成するテーブルの名前をdepartments、カラム数を4から2へ変更して「実行」をクリックしましょう。

クリックすると、以下のように設定画面が表示されます。
こちらのフォームを入力することで、テーブルのカラムに関する設定ができます。

設定画面の「名前」にはカラム名、「タイプ」でデータ型を選ぶことができます。

「AI」とは、自動的に一意の値を付与してくれるAUTO_INCREMENTのことです。

主キーとなるカラムの「AI」にチェックを入れると、以下のように自動的にインデックスのフォームにPRIMARYが設定されます。

上のように「AI」にチェックを入れることで、主キー制約を設定することができます。

他にもNULLを禁止できる「非NULL制約」を設定する場合は、以下のように「NULL」のチェックを外した状態にしておきます。

カラムの「名前」「タイプ」「長さ/値」などを設定していきます。departmentsテーブルの2つのカラムについて、以下のように入力しましょう。

nameカラムのデータ型はVARCHARなので、最大の文字数を指定する必要があります。「長さ/値」に30と指定することで、nameカラムの最大文字数を30に設定できます。

全て入力したら、以下のように「保存する」をクリックします。

departmentsテーブルが作成されると、以下のように画面にはテーブル構造が表示されます。各項目に設定した内容が反映されています。

idのカラムには「主キー制約」が設定されており、鍵のアイコンが表示されていますね。

テーブル管理画面を開くと、以下のように先ほど作成したdepartmentsテーブルが1件表示されています。

この流れでemployeesテーブルも作成していきます。

employeesテーブルを作成しよう

リンクをコピーしました

作成するテーブル名はemployeesで、必要なカラムはid・name・birthday・department_idです。

以下のように作成するテーブルの名前をemployees、カラム数を4のままで「実行」をクリックしましょう。

クリックすると、以下のように設定画面が表示されます。

カラムの「名前」「タイプ」「長さ/値」などを設定していきます。employeesテーブルの4つのカラムについて、以下のように入力しましょう。

birthdayのカラムには、デフォルト値（何も指定しない場合そのまま使われる値）としてNULLを設定します。

全て入力したら、以下のように「保存する」をクリックします。

employeesテーブルが作成されると、以下のように画面にはテーブル構造が表示されます。各項目に設定した内容が反映されています。

idのカラムには「主キー制約」が設定されており、鍵のアイコンが表示されていますね。またbirthdayのカラムには、デフォルト値としてNULLが設定されます。

テーブル管理画面を開くと、以下のように先ほど作成したemployeesテーブルが表示されています。

外部キー制約を設定しよう

リンクをコピーしました

employee_management_developmentのリレーショナルデータベースには、2つのテーブルを作成することができましたね。

しかし、現時点ではテーブル同士の関連付けが行われていないので、employeesテーブルの情報からdepartmentsテーブルの情報を取得することができません。

テーブル同士を関連付けるために「外部キー」が必要で、データの一貫性や正当性を保つためには「外部キー制約」が必要です。

外部キーに関する用語の整理

リンクをコピーしました

テーブルのカラムに外部キー制約を設定する前に、少し用語を振り返りましょう。

テーブル同士の関連付けを行うために、他のテーブルの主キーを参照するカラムのことを外部キー（FK：FOREIGN KEY）と呼びましたね。

関連付けられたテーブルは親子関係が成り立っています。参照される側のテーブルを「親テーブル」と呼び、参照する側のテーブルを「子テーブル」と呼びます。

親である「部署テーブル」に存在しないデータを子である「社員テーブル」に登録されてしまうと、親テーブルと子テーブルの整合性が崩れてしまいます。

外部キーとして扱いたいカラムに対して、外部キー制約（FOREIGN KEY制約）を設定することで、親テーブルに存在しないデータが子テーブルに間違って登録されないように防止することができます。

親テーブルのデータを削除する場合は、子テーブルにはどのような影響があるでしょうか。

次のように親である部署テーブルのレコードを削除すると、子の社員テーブルのデータの参照先がなくなることになります。親テーブルと子テーブルの整合性が崩れてしまいますね。

整合性を保つために外部キー制約の設定では、2通りの対処方法がありましたね。

親テーブルのデータを削除する際に同じキーを持つ子テーブルのデータもあわせて削除するか、そもそも親テーブルのデータを削除できないようにするかを選択することができます。

制約を設定するカラムの確認

リンクをコピーしました

外部キー制約は、子テーブルであるemployeesテーブルのdepartment_idのカラムに設定します。

department_idは、departmentsテーブルの主キーであるidを参照します。

さらに親テーブルと子テーブルの整合性を保つために、外部キー制約を設定する際に親テーブルのデータを削除できないようにRESTRICTというオプションを付けます。

外部キー制約の設定

リンクをコピーしました

それでは、phpMyAdminでカラムに外部キー制約を設定してみます。

続いて、employeesテーブルの画面上部にある「構造」を開き、以下のように「リレーションビュー」をクリックしましょう。

※departmentsテーブルではなく、employeesテーブルの詳細画面で操作してください。

クリックすると、外部キー制約の設定画面が表示されます。

• 制約プロパティ - 制約名や親テーブルに対する削除・更新時の操作を選ぶ
• カラム - 外部キー制約を設定するカラムを選ぶ
• 外部キー制約(INNODB) - 参照先のデータベース・テーブル・カラムを選ぶ

外部キー制約の設定について、以下のように入力しましょう。

全て入力したら、以下のように「保存する」をクリックします。

外部キー制約が設定されると、以下のように画面には設定した内容が表示されます。

実際には裏でSQL文を自動生成しており、そのSQL文によってRDBMSがリレーショナルデータベースを操作しています。

今回の設定で自動生成されたSQL文は、以下の通りです。

1
2
3
4
5
ALTER TABLE `employees`
  ADD FOREIGN KEY (`department_id`) 
  REFERENCES `departments`(`id`)
    ON DELETE RESTRICT
    ON UPDATE RESTRICT;

SQLについては後で学習しますが、上記は「employeesテーブルを変更する。変更内容としては、外部キー制約をdepartment_idに設定し、departmentsテーブルのidを参照する。親テーブルに対する削除と更新は拒否する。」という内容です。

employeesテーブルの構造で確認すると、department_idのカラムには「外部キー制約」が設定されており、鍵のアイコンが表示されていますね。

これでemployee_management_developmentのデータベースに必要なテーブルを用意することができました。

データ操作

リンクをコピーしました

データ操作では、CRUDに対応する操作をおさえることが重要です。

CRUDとは、Create（作成）・Read（読み込み）・Update（更新）・Delete（削除）の頭文字を繋げた用語で、データ操作の基本となる４つの処理のことです。

RDBMSの標準のデータベース言語であるSQLには、CRUDに対応する操作としてINSERT・UPDATE・DELETE・SELECTという４つの構文が用意されています。

今回はphpMyAdminを利用して、CRUDのデータ操作を行います。

phpMyAdminを利用することで、SQLで命令文を記述しなくても、Webブラウザ上で容易にRDBMSに指示し、CRUDに対応するデータ操作を行えます。

データを挿入しよう

リンクをコピーしました

departmentsテーブルに挿入

リンクをコピーしました

departmentsテーブルは、部署のデータを管理するテーブルです。
以下のように開発と営業を挿入してみます。

まずは、データベースであるemployee_management_developmentのテーブル管理画面からdepartmentsテーブルの行にある「挿入」をクリックしましょう。

クリックすると、以下のように挿入するデータの入力画面が表示されます。

デフォルトでは、レコード2件分を挿入できるようにフォームが表示されていますが、「無視」にチェックが付いていあると、チェックボックスの下にあるフォームは利用されず、上にある1件分のデータのみ挿入されます。

departmentsテーブルには2件のレコードを挿入するので、チェックは外しておきましょう。

nameカラムの値に開発と営業を入力して、実行をクリックしましょう。

※idにはAUTO_INCREMENT（自動的に一意の値を付与）の機能を設定しているので、何も入力しません。

実行をクリックすると、以下のSQL文により2件分のレコードが挿入されます。

departmentsテーブルで「表示」をクリックすると、以下のように挿入したデータを確認することができます。idには値を入力しませんでしたが、自動的に1と2が設定されています。

同じ手順でemployeesテーブルにもデータを挿入してみましょう。

employeesテーブルに挿入

リンクをコピーしました

employeesテーブルは、社員のデータを管理するテーブルです。
以下のように6件分のデータを挿入してみます。

まずは、データベースであるemployee_management_developmentのテーブル管理画面からemployeesテーブルの行にある「挿入」をクリックしましょう。

クリックすると、以下のように挿入するデータの入力画面が表示されます。

デフォルトでは、レコード2件分を挿入できるように設定されていますが、6件分のデータを挿入するために、ページ下部にある2を6に変更しておきましょう。

以下のようにid以外の値を入力して「実行」をクリックしましょう。フォームにデータを入力すると、自動的に「無視」のチェックが外れます。

※外部キー制約のより、department_idには参照先のdepartmentテーブルのidの値のみ選択可能です。

実行をクリックすると、以下のSQL文により6件分のレコードが挿入されます。

employeesテーブルで「表示」をクリックすると、以下のように挿入したデータを確認することができます。

外部キー制約の設定により、department_idの値はdepartmentテーブルの主キーを参照します。

以下のようにdepartment_idの値をクリックすると、参照先のレコードにアクセスすることができます。

データを更新しよう

リンクをコピーしました

employeesテーブルを更新

リンクをコピーしました

employeesテーブルのデータを更新してみましょう。
山口冬子さんの生年月日を1993-05-12から1993-12-12に更新してみます。

まずは、employeesテーブルの「表示」の画面から以下のように6行目にある「編集」をクリックしましょう。

編集をクリックすると、idが6のレコードの編集画面が表示されます。

以下のようにbirthdayの値を1993-05-12から1993-12-12に変更して「実行」をクリックしてみましょう。

実行をクリックすると、以下のSQL文によりbirthdayの値が更新されます。

departmentsテーブルを更新

リンクをコピーしました

前回の正規化で学習しましたが、RDBでは一般にデータ同士の関連を保ったままテーブルを分けて管理することで、データを変更するときに最小限の修正で済みます。

少しおさらいしてみます。「営業部」を今後増やす予定で、従来の営業部は「法人営業部」に変更するとします。次のように1つのテーブルに情報をまとめてしまうと、「営業部」の全ての箇所を「法人営業部」に修正する必要があります。

このときに1件でも修正漏れがあると、データ内容に矛盾が生じてしまいます。

しかし、1つのテーブルにまとまっていた情報を分けて、テーブル同士を関連付けることで、修正は１箇所で済みます。

今回はすでにemployeesとdepartmentのテーブル同士が関連付けられている状態なので、部署を営業から法人営業に修正する際には、１箇所だけで済みます。

まずは、departmentsテーブルの「表示」の画面から以下のように2行目にある「編集」をクリックしましょう。

編集をクリックすると、idが2のレコードの編集画面が表示されます。
以下のようにnameの値を営業から法人営業に変更して、「実行」をクリックしてみましょう。

実行をクリックすると、以下のSQL文によりnameの値が更新されます。

employeesテーブルで2のdepartment_idの値をクリックすると、以下のように更新された法人営業が表示されます。

RESTRICT：親テーブルの主キーの値は更新できない

リンクをコピーしました

employeesテーブルのdepartment_idに外部キー制約を設定した際に、親テーブルに対する更新時の操作でRESTRICTを選択していましたね。

これは「親テーブルに対する更新操作を拒否する」というオプションです。

親テーブルであるdepartmentテーブルのnameカラムの値は更新することができますが、主キーであるidカラムの値は更新することができません。

更新しようとすると、以下のようにエラーが起こります。

RESTRICTの設定により、department_idカラムが参照するdepartmentテーブルの主キーであるidカラムの値は、更新することができないので注意しましょう。

データを削除しよう

リンクをコピーしました

以下のようにemployeesテーブルのidが6のレコードを削除してみます。

まずは、employeesテーブルの「表示」の画面から以下のように6行目にある「削除」をクリックしましょう。

以下のようにダイアログが表示されるので、「ok」とクリックします。

okをクリックすると、ダイアログで表示されたSQL文によりidが6のレコードが削除されます。以下のようにemployeesテーブルの中に存在しません。

RESTRICT：親テーブルのレコードは削除できない

リンクをコピーしました

親テーブルに対する更新操作を拒否したように、外部キー制約の設定により、親テーブルのレコードを削除することもできないので注意してください。

以下のように削除時の操作もRESTRICTを選択していましたね。

これは「親テーブルに対する削除操作を拒否する」というオプションです。

親テーブルであるdepartmentテーブルのレコードは削除することができません。削除しようとすると、以下のようにエラーが起こります。

データを抽出しよう

リンクをコピーしました

各テーブルの画面上部に表示されている「検索」をクリックして、検索条件を設定する画面からテーブルのデータを抽出することができます。

テーブルから特定のカラムの値を抽出

リンクをコピーしました

テーブルから特定のカラムの値だけを抽出することができます。検索の条件を設定して、employeesテーブルのデータを抽出してみましょう。

まずは、employeesテーブルの画面上部に表示されている「検索」をクリックして、検索条件を設定する画面を開きましょう。

以下の画面でデータを抽出する条件を設定することができます。

それではオプションを開いて、以下のように表示するカラムでnameを選択して、実行をクリックしましょう。

実行をクリックすると、以下のSQL文によりnameカラムだけの値が表示されます。

このようにオプションを利用することで、テーブルから特定のカラムの値だけを抽出することができます。

特定のレコードを抽出

リンクをコピーしました

検索条件を設定する画面の「演算子」を使うことで、特定のレコードを抽出することができます。

まずは、department_idカラムの値が1であるレコードだけをemployeesテーブルから抽出してみます。以下のように入力して「実行」をクリックしてください。

以下では、department_id = 1という条件を設定します。

実行すると、以下のようにdepartment_id = 1の条件に当てはまるレコードだけが表示されます。

以下のように検索条件の画面でbirthdayの行の演算子を>にして、値を1986-01-01と入力して「実行」をクリックしましょう。

実行すると、以下のようにbirthday > 1986-01-01の条件に当てはまるレコードだけが表示されます。

データの整列

リンクをコピーしました

検索条件の画面にある「表示順」でデータの整列をすることができます。

以下のようにオプションの「表示順」を入力して、「実行」をクリックしてください。

実行をクリックすると、以下のようにidの値が降順に並び替えられます。

この章のまとめ

リンクをコピーしました

今回の章では、phpMyAdminを用いてリレーショナルデータベースの操作を行いました。SQL文を直接扱わずに、Webブラウザ上で直感的な操作によりデータベースやテーブルの作成が可能であることを学びました。裏側ではSQL文が自動生成され、それによりRDBMSがリレーショナルデータベースを操作しています。

次の章では、SQL文について学習します。この章を終える前に、以下の手順に従って今まで作成したテーブルを削除することを忘れないでください。こうすることで、SQLの章に進む前に環境を整えることができます。

employee_management_developmentにあるテーブルを全て削除します。

以下のようにテーブルの管理画面からemployeesテーブルの行にある「削除」をクリックしましょう。子テーブルから削除します。

クリックすると、以下のように確認ダイアログが表示されます。そのまま「OK」をクリックしましょう。

この流れで親テーブルであるdepartmentテーブルを削除しましょう。

親テーブルから削除した場合、以下のように外部キー制約違反でエラーが起こり、テーブルを削除できないので、注意しましょう。

employee_management_developmentデータベースのテーブルを全て削除すると、以下のようにテーブル数が0と表示されます。