この記事では、弊社の新人エンジニア研修の参考にMySQLを解説します。前回はMySQLWorkbenchの使い方を解説しました。

1. テーブルの完成イメージ

今回は、MySQL Workbenchを使ってスキーマとテーブルを作成する手順を解説します。下図のようなテーブルを作成し、データを投入し、さらにはデータを更新したり削除したりしてみましょう。

なお、MySQLではテーブル名やフィールド名に日本語を使うことも可能です。しかし、一般的ではないため当社の新人エンジニア研修でも英語表記で進めていきます。

また、この研修のルールとしてテーブル名、フィールド名はスネークケース(例.car_id)を使用します。

mysql スキーマ 作成
作成するcarsテーブル(中略)

deleted_at というフィールドをデータベースのテーブルに用意する理由

deleted_at:は「論理削除(Soft Delete)」を実装するために使用されるフィールドです。レコードを物理的に削除する代わりに、deleted_at フィールドに削除された日時を記録します。そうすることで、データはデータベースに残りますが、アプリケーション上では削除されたとして扱われます。これはデータ復旧や監査の際に有用です。

CRUD

MySQLのようなデータベース管理システムにおいては、データを作成して、検索して、更新して、削除するという機能が必須になります。それぞれ、【Create:新規作成と追加】、【Read:読み出しと検索】、【Update:更新】、【Delete:削除】の頭文字をとってCRUDと呼ぶことがあります。ここでは【Create】、【Update】、【Delete】の操作を学びます。【Read】は分量が多いので章を改めます。

2. スキーマの作成

MySQL Workbenchを起動し、操作したいConnectionに接続します。下図の「Schemas」で右クリックして「Create Schema」を選択します。

Create Schemaを選択
Create Schema

下図でスキーマ名を入力して「Apply」ボタンを押下します。

なお、Charsetは文字コード、Collationは照合順序のことです。照合順序はレコードの検索や並べ替えをする際に関係してきます。

MySQL8.0からDefaultで「utf8mb4_0900_ai_ci」となっており、日本語対応のWebアプリケーションを作る際に都合が良いのでこのままにします。

utf8mb4が文字コードです。UTF-8で、日本語のようなマルチバイト文字を4バイトとする文字コードになります。(通常マルチバイト文字は3バイト)

aiはAccent Insensitiveの略です。アクセントの違いを無視しますのでひらがなカタカナの「か」と「が」は同じ文字として評価されます。

ciはCase Insensitiveの略です。大文字と小文字の違いを無視しますので「a」と「A」は等しいと評価されます。

スキーマ名を入力
スキーマ名の入力

スキーマ名やテーブル名は全て小文字でないといけません

下図のようなダイアログが出たら「OK」ボタンを押下することで自動的に小文字になります。

図3.4 スキーマ名やテーブル名は全て小文字
スキーマ名やテーブル名は全て小文字 

下図の発行されるSQL文を確認して「Apply」ボタンを押下します。このとき、観察眼に優れた方は`sip_a`のようにスキーマ名が`(バッククォート)で囲まれていることに気づいたかもしれません。

図3.5 発行されるSQL文
発行されるSQL文

これは何のためにつけているかというとMySQLの予約語をエスケープするためです。Javaにも予約語がありましたね。例えば、「int int = 0」のように予約語を変数名などに使うことができませんでした。

MySQLにも予約語があります。下図で赤の波線が示すように予約語はそのままではスキーマ名、テーブル名、フィールド名に指定できないのです。しかし、エスケープすることで指定が可能になるのです。

バッククオートでエスケープされていたのは、念のためスキーマ名に予約語が使われても大丈夫なようにというworkbenchの親切機能なのでした。なお、予約語はWorkbenchで青字になります。

予約語をエスケープ
予約語はそのままではスキーマ名、テーブル名、フィールド名に指定できない

3. テーブルの作成

下図のようなcarsテーブルを作成します。この研修のルールとしてテーブル名には名詞の複数形(例えばcars)を使用することにします。複数形にする理由は同じもの(例えばcar)の集合であるということが明確に表現できるうえに、SQL文の中にあってすぐにテーブルだと識別できるからです。

mysql スキーマ 作成
作成するcarsテーブル(再掲)

3.1. テーブルの作成手順

[Create Table…]メニューを選択します

下図のようにテーブルを作成したい「Schemas」の下の「Tables」を右クリックしてポップアップメニューを表示し[Create Table…]メニューを選択します。

Create Tableを選択
Create Tableメニュー

テーブル名を入力します。

下図の「new_tableダイアログボックス」が表示されます。テーブル名やCollation、データベースエンジンを選択します。当社の新人研修では、テーブル名のみ入力し、ここでもCharset/Collationはデフォルトのままで結構です。Table Name:の欄に今回は「cars」と入力してください。

図3.9 new_tableダイアログボックス
new_tableダイアログボックス

Column NameとDatatypeを設定します

Columnは列(フィールド)、Datatypeはデータ型のことです。

car_idとpriceはINT型としました。

nameはVARCHAR型で45桁としました。

VARCHAR とは【variable character】の略で可変長の文字列という意味です。45桁は純粋に文字数です。(本来、半角英数字と日本語ではバイト数が異なりますが、UTF-8で設定しているのでその点を考慮しなくてよいのは楽ですね)

可変長ですから固定長と違い、短い文字列の場合も空白がデータ容量を使用しません。できるだけ最小の文字数(や桁数)で設計すれば最小のバイト数になり、メモリに展開された際に素早く動くデータベースになります。

他方で、設定した桁数や文字数を超えて文字列を入れるとエラーになります。そのため、必要十分かつ最小の桁数や文字数になるように設計しなければなりません。また、同じデータベースでフィールドごとに桁数や文字数がバラバラですと毎回確認が必要になりますから、一律の長さにすることもあります。

ちなみに、あとあとユーザーにHTMLのフォームからname(車の名前)等を入れてもらうこともあるでしょう。その際にはこの45桁とフォームのmaxlength 属性の数値を合わせることで間違いの起きにくいシステムになります。

deleted_atはレコードの削除日時でVARCHAR型で45桁としました。あえてDATETIMEやTIMESTAMPは、後々Javaで扱う際に型を変換する必要があるため使っていません。


なお、テーブル名やフィールド名はこの研修ではスネークケースを使用することにしたということは前述の通りです。

3.2. MySQLのデータ型

この後の最終課題でよく使うであろうMySQLのデータ型をまとめておきます。まずは、下表3. 1の3つの型をしっかり押さえてください。また、このあとWebアプリケーションを作成する際にJavaのデータ型を決めるときのために「Javaのデータ型との対応」を確認しておいてください。迷ったらひとまず(極論ですが)Javaの型は全てStringにするという手もありえます。

例えば、MySQLのINT型のフィールドに文字列の"1"をINSERTすることができます。それでもデータを取り出したときには数値の1として取り出されます。なぜなら、そのフィールドはINT型と型指定されているためMySQLが自動で文字列を整数に変換してくれるのです。

※ただし、データ型をDATETIMEにしておけば日付の加算・減算をすることもできますが、文字列型の場合はそのままでは実現できません。(参考記事:JavaのLocalDateTime型をMySQLのDATETIME型で扱う

データ型意味備考Javaのデータ型との対応
INT(m)整数型mは桁数 値の範囲(4バイト):-2147483648~2147483647 
ただし、UNSIGNEDの場合の値の範囲:0~4294967295
int, java.lang.String
VARCHAR(m)可変長文字型mは文字数(最大文字数は文字セットによって変わりますが、最大バイト数は65,535バイト)java.lang.String
DATETIME日付時刻型 基本のフォーマットは 'YYYY-MM-DD HH:MM:SS'java.time.LocalDateTime, java.lang.String
表3.1

3.3. テーブル列の設定

列を定義します。以下図のように設定してください。

それぞれの項目の意味は以下のとおりです。

PK: Primary Key = 主キー

NN: Not Null Nullを許容しない

UN:Unsigned マイナス符号なし

AI: Auto Increment 自動で採番する

Default/Expression デフォルト表現としてデータ入力がない場合の値

それぞれの意味はWorkbenchの右下の下図の部分でも確認することができます。

mysql スキーマ 作成
テーブル列の定義

なお、カラムを右クリックすると以下のメニューが表示されますので、ここからDefaultの値を選択することも可能です。

mysql スキーマ 作成
デフォルト値の設定

3.4. インデックスの設定

インデックスが必要な場合は、[Indexes]タブからインデックスを定義するフィールドを選択します。今回は確認にとどめインデックスを設定しません。なお、主キーには自動的にインデックスが張られています。

インデックスとは?

データベースのインデックスは、データ検索の効率を高めるためのデータ構造です。インデックスは、特定の列(または列の組み合わせ)の値に対して参照ポインタ(本のシオリのようなもの)を提供します。それによりデータベースエンジンはフルテーブルスキャンを避け、より効率的にデータを検索できます。

基本情報技術者試験の勉強をされた方は、データ構造の「B-Tree」や「ハッシュ」という言葉を聞いたことのある方も多いと思います。MySQLで使用される主要なインデックスのデータ構造は「B-Tree」と「ハッシュ」です。

下図の[Apply]ボタンを押します。

インデックスの設定確認
[Indexes]タブ

下図のように実行するSQL文が表示されます。内容を確認し問題がなければ[Apply]ボタンをクリックします。

図3.13 Reviewダイアログボックスのイメージ
Reviewダイアログボックスのイメージ

以下3_1sqlが実行されます。

<CREATE TABLE 文>

CREATE TABLE `sip_a`.`cars` (
  `car_id` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(45) NULL DEFAULT NULL,
  `price` INT UNSIGNED NULL DEFAULT NULL,
  `deleted_at` VARCHAR(45) NULL DEFAULT NULL,
  PRIMARY KEY (`car_id`));

なお、SQL文は単語の途中でなければ改行や空白を入れて見やすくすることができます。また、文末のセミコロン( ; )はJavaとは異なり必須ではないのですが、一つのファイルに複数のSQL文を記述する際には区切りとして必要になります。忘れずに付けるようにしましょう。

これでテーブルが作成されました。しかし、スキーマツリーには表示されていません。下図のスキーマツリーを右クリックして「Refresh All」を選択することで表示されます。

図3.14 「Refresh All」を選択
「Refresh All」を選択

4. テーブルに1件のレコードを挿入する

では、先に作成したcarsテーブルに1件のレコードを登録してみましょう。これまでの操作でテーブルが表示されていればそのままで結構です。表示されていない場合は下図のcarsテーブルの「③テーブルを表示するアイコン」をクリックします。  

MySQL_workbench 図3.15 テーブルを表示するアイコン
テーブルを表示するアイコン

下図のような空のテーブルが表示されました。

図3.16 空のテーブル
空のテーブル

4.1. データの入力

テーブルに直接データを入力する

テーブル内をダブルクリックして編集状態にするとエクセルのように直接データを入力できます。下表3.2のようなレコードを1件挿入してみます。

フィールド名備考
car_id(空欄)オートインクリメントで自動採番されるので何も入力しない
name'セダン'車の名前
price2590000車の価格
deleted_at'2024-04-01'レコードが削除された日(時)
表3.2

以下の3_2.sqlを実行すると空のテーブルが右下のサーバー応答エリアに表示されます。

SELECT * FROM sip_a.cars;

なお、スキーマ名を意味する「sip_a.」の部分はスキーマが選択された状態であれば省略することも可能ですので確かめてください。

 サーバー応答エリアでテーブルのデータを下図のように直接入力します。

mysql スキーマ 作成
データを直接入力できる

 編集が完了しましたら、「Apply」ボタンをクリックします。  

編集内容をテーブルに反映させる以下のようなINSERT文が表示されます。このSQL文は後で解説するのでご記憶ください。図のApplyをクリックし、次の画面でFinishをクリックします。 

mysql スキーマ 作成
INSERT文

入力されたことを確認してください。

このようにGUIで操作ができることも、その背後ではSQLが働いていることを意識することは重要です。

ということでレコードを挿入するINSERT文を確認してみます。以下の3_3.sqlを見てください。

INSERT INTO `sip_a`.`cars` (`name`, `price`, `deleted_at`) 
VALUES ('セダン', '2590000', '2024-04-01');

なお、テーブルに含まれるすべてのフィールドに値を指定してデータを追加する場合は、以下3_4.sqlのようにフィールド名の指定を省略できます。この場合、オートインクリメントの指定があるcar_idはNULLを指定します。また、current_timestamp()はMysqlの用意している組み込み関数で現在の日時を返します。

INSERT INTO `cars` VALUES (NULL, 'セダン', '2590000', '2024-04-01');

また、フィールド名を指定することで特定の値のみ挿入して、それ以外の項目はデフォルト値とする以下3_5.sqlのような記述も可能です。

INSERT INTO sip_a.cars (name, price) VALUES ('セダン', '2590000');

数値リテラルは シングルクォートで囲わなくて大丈夫です。上記の例では、「 '2590000' 」を「2590000」と書いても同じ結果になります。数値を文字列として入力しているという意味では上記の入力は間違っているのですが、MySQLはエラーにしません。なぜなら、先に見たようにフィールドにはデータ型が指定されているからです。MySQLは変換可能な入力であれば文字列を数値として扱ってくれるのです。

一方、文字列や日時データはシングルクォート(')またはダブルクオート (") で囲みます。一般的にはシングルクォートで囲むことが多いです。上記の例では、「 'セダン' 」や「 '2024-04-01' 」がその例です。Javaの文字列はダブルクオートでしか囲めなかったことと混同しないようにしましょう。


Auto Increment設定のあるフィールド(car_id)はNULLのままで構わないのでしたね。したがって上記のSQL文は以下3_6.sqlのようにcar_idには未定義を意味するnullを指定しても同じことになります。

【car_idにnullを指定した場合のINSERT文】

INSERT INTO sip_a.cars (car_id, name, price) VALUES (null, 'セダン', '2590000');

このとき、未定義を意味するNULLはシングルクォートで囲んではいけません。もしも、'null'としてしまうと「nullという文字列」という意味になってしまいます。

このあたりのnullの挙動はWebアプリケーションで重要になるのですが、おそらくその頃には忘れていると思いますので、またここに戻ってきて確認してください。

5. 行の更新

テーブルの内容を直接書き換えることでレコードの更新ができます。

このときのSQL文は以下の3_9.sqlの通りです。(deleted_atを'2024-05-01'へ変更した場合)

UPDATE `sip_a`.`cars` SET `deleted_at` = '2024-05-01' WHERE (`car_id` = '1');

このSQL文の意味は、

sip_a.cars の「car_id = '1' のレコードのフィールドdeleted_at を '2024-05-01 00:00:00'」に更新しなさい。という意味です。(時間は省略することも可能)

6. 行の削除

行を削除するには、下図のように対象行を右クリックして「Delete Row(s)」を選択します。 

行の削除
「Delete Row(s)」を選択

ただし、データベースの役割は事実を記録することです。もし、上記の方法でレコードを削除してしまうと事実が失われてしまいます。したがって、レコードの削除は、極めて限定的な場面でしか使いません。限定的な場面とは、間違ったデータを削除するとか個人情報保護法の保有個人データの利用停止・消去の請求があった場合などです。

レコードを削除する代わりに今回のcarsテーブルのようにdeleted_at(販売終了日)のフィールドを用意するというのはよく使われる方法です。このフィールドがNULLではなく値が入っているときにすでに販売が終了されているとみなすわけです。

あるいは、テーブルにBoolean型の削除フラグというフィールドをつくり、そのTRUE/FALSEで削除をされたかどうかを判定する方法もあります。削除フラグにはデフォルトでFALSEにしておき、レコードを削除したら削除フラグをTRUEにするという運用です。

delete文で削除することを物理削除、実際には削除しないことを論理削除とも言います

データを削除・更新できなくする理由

例えば、クレジットカードのキャンセル処理を考えてみましょう。キャンセル対象の処理を削除(delete)したり、更新(update)したりすれば確かにそれでキャンセルになります。しかし、キャンセルがあったという事実は記録できません。そこで同じ額をマイナスするレコードをデータベースに挿入することでキャンセル処理をします。

7. 複数レコードの一括挿入

手入力することが困難な量のデータをテーブルに入力する方法を解説します。例えば、下図のように1件しかないテストデータを増やす方法です。

1件しかないテストデータを増やす
1件しかないテストデータを増やす

7.1. SQLのinsert文を使う方法

必要なSQLのinsert文の確認

今回記述したいSQL文は3_10.sqlのとおりです。全体が一文になっているのがお分かりになるでしょうか?

この書き方はバルクインサートbulk insert】という方法です。データが増えても速度が速いという特徴があります。あとの3_11.sqlと比較してみてください。英語でも一括注文を【bulk order】といったりしますね。

INSERT INTO `sip_a`.`cars` (`name`, `price`, `deleted_at`) 
VALUES 
    ('セダン', '2590000', '2024-04-01'),
    ('クーペ', '4990000', '2024-04-01'),
    ('SUV', '2990000', NULL),
    ('ハッチバック', '1980000', NULL),
    ('ステーションワゴン', '2010000', NULL),
    ('ミニバン', '3590000', NULL),
    ('軽自動車', '1000000', NULL),
    ('商用車', '1390000', NULL);

上記バルクインサートは、以下3_11.sqlの通常のinsert文と比較すると速度が速くなります。

INSERT INTO `sip_a`.`cars` (`name`, `price`, `deleted_at`) VALUES ('セダン', '2590000', '2024-04-01');
INSERT INTO `sip_a`.`cars` (`name`, `price`, `deleted_at`) VALUES ('クーペ', '4990000', '2024-04-01');
INSERT INTO `sip_a`.`cars` (`name`, `price`) VALUES ('SUV', '2990000');
INSERT INTO `sip_a`.`cars` (`name`, `price`) VALUES ('ハッチバック', '1980000');
INSERT INTO `sip_a`.`cars` (`name`, `price`) VALUES ('ステーションワゴン', '2010000');
INSERT INTO `sip_a`.`cars` (`name`, `price`) VALUES ('ミニバン', '3590000');
INSERT INTO `sip_a`.`cars` (`name`, `price`) VALUES ('軽自動車', '1000000');
INSERT INTO `sip_a`.`cars` (`name`, `price`) VALUES ('商用車', '1390000');

7.2. Workbenchのインポート機能を使う方法

エクセルでCSVファイルを編集してWorkbenchでインポートします。ポイントは先に雛形をエクスポートして、一行目のフィールド名の下に追加したいレコードを貼り付けることです。少々複雑なので講師が研修会場で実演いたしますが、ここでも少し触れておきます。

問題は文字コードです。サクラエディタのようなエディタでインポートしたいCSVファイルを開きます。その後、「名前をつけて保存」のメニューで「文字コードセット」に「SJIS」を選択すれば文字化けしないCSVファイルになります。

ダミーデータの入手方法

システム開発演習などで大量の個人データが必要になることがあります。そんな時には以下の「疑似個人情報データ生成サービス」が便利です。

https://hogehoge.tk/personal/

例題

下図のようなcustomers表をインポートしなさい。

必要なデータは講師から入手すること。

※本当は姓名は「名字と名前」のようにフィールドを分けて、細かい部品にした方が並べ替えや検索の時に便利なうえに高速になります。

customers表
customers表

例題

下図のようなsales表をインポートしなさい。

必要なデータは講師から入手すること。

mysql スキーマ 作成
sales表

8. テーブルの削除と変更

当社の新人エンジニア研修では、作ったテーブルを変更することは良くあります。また、データを空にしたり、テーブルそのものを削除することもあります。ここでその方法を見ていきましょう。

下図のようにテーブルを右クリックしてコンテキストメニューを表示します。

テーブルの削除と変更
テーブルを選んでコンテキストメニューを表示させる

①のAlter Table...を選択するとテーブルの変更画面に移ります。

あとは、テーブルを変更してapplyボタンを押せば変更が反映されます。

②テーブルをスキーマごと削除してしまいたいときはDrop Table...を選びます。

以下3_10.sqlのようなSQL文が発行され、テーブルが削除されます。

DROP TABLE sip_a.cars;

③テーブルの構造(スキーマ)を残して全てのレコードを削除したいときはTruncate Table...を選びます。

以下3_11.sqlのようなSQL文が発行され、全てのレコードが削除されます。

TRUNCATE sip_a.cars;

なお、TRUNCATE TABLE 文を使用するとデータの削除だけではなく AUTO_INCREMENT もリセットされることを覚えておくと良いでしょう。

9. その他の便利なコンテキストメニュー

コンテキストメニューを紹介したのでその他の便利な機能を紹介します。当社の新人エンジニア研修ではこの後、さまざまなSQL文を学んでいきます。その際に確実にキーボードからコマンドを打てるようになることがもっとも望ましいのですが、限られた研修期間中には難しいものです。そこで、コンテキストメニューを使ってSQL文の雛形を書く方法をお伝えします。具体的なSQL文の意味は次回以降に回して、まずはどのような機能があるのかだけを見ていきます。

下図のようにテーブル名を右クリックして、「Send to SQL Editor」を選びSQLエディターにSQL文を送ることができます。

mysql スキーマ 作成
「Send to SQL Editor」を選ぶ

①以下3_12.sqlは全てのレコードを選択するSELECT文です。

SELECT `cars`.`car_id`,
    `cars`.`name`,
    `cars`.`price`,
    `cars`.`deleted_at`
FROM `sip_a`.`cars`;

②以下3_13.sqlは1件のレコードを挿入するINSERT文です。以下の<{name: }>のところを具体的な値(例えば「セダン」)などに書き換えて使ってください。

INSERT INTO `sip_a`.`cars`
(`car_id`,
`name`,
`price`,
`deleted_at`)
VALUES
(<{car_id: }>,
<{name: }>,
<{price: }>,
<{deleted_at: }>);
SELECT * FROM sip_a.cars;

③以下3_14.sqlはレコードを更新するUPDATE文です。

UPDATE `sip_a`.`cars`
SET
`car_id` = <{car_id: }>,
`name` = <{name: }>,
`price` = <{price: }>,
`deleted_at` = <{deleted_at: }>
WHERE `car_id` = <{expr}>;

④以下3_15.sqlは特定の条件のレコードを削除するDELETE文です。

DELETE FROM `sip_a`.`cars`
WHERE <{where_expression}>;

このようにひな形を書き換えることで、SQL文を暗記しなくても使うことはできます。ただし、繰り返しになりますが、暗記してあればそれだけ早く、確実に、確信を持って使うことができます。

例題

この章で作成した3つのテーブルを削除してからできるだけ何も見ずに作成してください。

なお、この後の研修で3つのテーブルを使うので必ず最後は初期のデータを復元するようにしてください。

JavaとSQLの共通点と相違点

既にJavaを学んだことのある皆さんは、Javaと比較したSQLの特徴をお話すると記憶に残りやすいのではと思います。ここでは、両言語の共通点と相違点を6つまとめました。

共通点
  • データ型: 数値、文字列など、基本的なデータ型はどちらの言語にも存在します。
  • コメント: 両言語ともに、コード内にコメントを記述することができます。Javaでは //(一行コメント) と /* */(複数行コメント) がありましたが、SQLでは -- (一行コメント)と /* */(複数行コメント) を使用します。複数行コメントの書き方はどちらも同じですね。
  • 文末のセミコロン: 両言語ともに、命令や文の終わりに;を使用します。
相違点
  • 文字列の囲み方: Javaでは文字列を"(ダブルクオーテーション)で囲みますが、SQLでは通常'(シングルクォーテーション)で囲みます。
  • 等しい演算子: Javaでは等しいことを判定するために==を使用しますが、SQLでは=(単一のイコール)を使用します。
  • 予約語の使用: Javaでは予約語(例えばint)を識別子(フィールド名や変数名)に使えませんが、SQLでは予約語(例えばSELECT)を「`」で囲めば識別子(フィールド名やテーブル名)に使用できます。

以上、新人エンジニア研修で使うテーブルを作成し編集する方法について学びました。

<まとめ:隣の人に正しく説明できたらチェックを付けましょう>

□ スキーマを作るときはCreate Schema文

□ テーブルを作るときはCreate Table文

□ テーブルにレコードを挿入するときはINSERT文

□ テーブルのデータを更新するときはUPDATE文

□ レコードを削除するときはDELETE文

□ テーブルの構成を変更するときはAlter Table文

□ テーブルを構成ごと削除したいときはDrop Table文

□ テーブルからデータのみ全て削除したいときはTruncate Table文

以上、今回は、新人エンジニア研修で使うテーブルを作成し編集する方法について学びました。

次は、 ER図からテーブルを作成する 方法について学んでいきましょう。