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

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

今回は、MySQL Workbenchを使ってスキーマとテーブルを作成する手順を解説します。下図3.1のようなテーブルを作成し、データを投入してみましょう。なお、MySQLではテーブル名や列名に日本語を使うことも可能です。しかし、一般的ではないため当社の新人エンジニア研修でも英語表記で進めていきます。

cars_table
図3.1 作成するcarsテーブル

2. スキーマの作成

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

Create Schemaを選択
図3.2 Create Schema

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

なお、Charsetは文字コード、Collationは照合順序のことです。照合順序はレコードの検索や並べ替えをする際に関係してきます。MySQL.0からDefaultで「utf8mb4_0900_ai_ci」となっており、日本語対応のWebアプリケーションを作る際に都合が良いのでこのままにします。意味は、utf8mb4が文字コードです。UTF8で、日本語のようなマルチバイト文字を4バイトとする文字コードになります。(通常マルチバイト文字は3バイト)、aiはAccent Insensitiveの略です。アクセントの違いを無視しますのでひらがなカタカナの「か」と「が」は同じ文字として評価されます。ciはCase Insensitiveの略です。大文字と小文字の違いを無視しますので「a」と「A」は等しいと評価されます。

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

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

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

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

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

mysql スキーマ 作成
図3.5 発行されるSQL文

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

MySQLにも予約語があります。下図3.6で赤の波線が示すように予約後はそのままではスキーマ名、テーブル名、列名に指定できないのです。しかし、エスケープすることで指定が可能になるのです。バッククオートでエスケープされていたのは、念のためスキーマ名に予約語が使われても大丈夫なようにというworkbenchの親切機能なのでした。なお、予約語はWorkbenchで青字になります。

予約語をエスケープ
図3.6 予約後はそのままではスキーマ名、テーブル名、列名に指定できない

3. テーブルの作成

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

cars_table
図3.7 carsテーブル(再掲)

3.1. テーブルの作成手順

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

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

Create Tableを選択
図3.8 Create Tableメニュー

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

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

mysql スキーマ 作成
図3.9 new_tableダイアログボックス

Column NameとDatatypeを設定します

Columnは列、Datatypeはデータ型のことです。

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

nameとend_of_life_dates(販売終了日)はVARCHAR型で45桁としました。

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

可変長ですから固定長と違い短い文字列の場合も空白がデータ容量を使用しません。できるだけ最小の文字数(や桁数)で設計すれば最小のバイト数になりメモリに展開された際に素早く動くデータベースになります。他方で、設定した桁数や文字数を超えて文字列を入れるとエラーになります。そのため、必要十分かつ最小の桁数や文字数になるように設計しなければなりません。また、同じデータベースで列ごとに桁数や文字数がバラバラですと毎回確認が必要になりますから、一律の長さにすることもあります。

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


なお、テーブル名や列名はこの研修ではスネークケースを使用することにします。

3.2. MySQLのデータ型

この後の最終課題でよく使うであろうMySQLのデータ型をまとめておきます。まずは、下表3.1の4つをしっかり押さえてください。また、このあとWebアプリケーションを作成する際にJavaのデータ型を決めるときのために「Javaのデータ型との対応」を確認しておいてください。迷ったらひとまず(極論ですが)Javaの型は全てStringにするという手もありえます。例えば、MySQLのINT型の列に文字列の"1"をINSERTすることができます。それでもデータを取り出したときには数値の1として取り出されます。なぜなら、その列はINT型と型指定されているためMySQLが自動で文字列を整数に変換してくれるのです。

※ただし、データ型をDATEにしておけば日付の加算・減算をすることもできますが、文字列型の場合はそのままでは実現できません。

データ型意味備考Javaのデータ型との対応
INT(m)整数型mは桁数 値の範囲(4バイト):-2147483648~2147483647 
ただし、UNSIGNEDの場合の値の範囲:0~4294967295
int, java.lang.String
VARCHAR(m)可変長文字型mはバイト数 バイト数の範囲:0~65535バイト java.lang.String
DATE 日付型基本のフォーマットは'YYYY-MM-DD'
他には、'YYYY/MM/DD''YYYYMMDD'なども可
java.sql.Date, java.lang.String
DATETIME日付時刻型 基本のフォーマットは 'YYYY-MM-DD HH:MM:SS'java.sql.Timestamp, java.lang.String
表3.1

3.3. テーブル列の設定

id,name,price, end_of_life_dates の4列を定義します。以下図3.10のように設定してください。

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

PK: Primary Key = 主キー

NN: Not Null Nullを許容しない

UN:Unsigned マイナス符号なし

AI: Auto Increment 自動で採番する

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

テーブル列の設定項目
図3.10

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

mysql スキーマ 作成
図3.11

3.4. インデックスの設定

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

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

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

下図3.13の実行するSQL文が表示されます。このSQL文は後で解説しますのでよく見ておいてください。内容を確認し問題がなければ[Apply]ボタンをクリックします。

実行するSQL文の確認
図3.13 実行するSQL文

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

<CREATE TABLE 文>

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

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

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

mysql スキーマ 作成
図3.14 「Refresh All」を選択

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

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

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

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

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

4.1. データの入力

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

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

列名備考
id1オートインクリメントを利用
name'セダン'
price2590000
end_of_life_dates '2020-01-01'
表3.2

以下の3_2.sqlを実行すると空のテーブルが右下のサーバー応答エリアに表示されます。なお、スキーマ名を意味する「sip_a.」の部分はスキーマが選択された状態であれば省略することも可能ですので確かめてください。

SELECT * FROM sip_a.cars;

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

データを直接入力できる
図3.17 データを直接入力できる

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

「Apply」ボタンをクリック
図3.18

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

INSERT文
図3.19 INSERT文

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

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

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

INSERT INTO sip_a.cars (car_id, name, price, end_of_life_dates) VALUES ('1', 'セダン', '2590000', '2020-01-01');

なお、テーブルに含まれるすべての列に値を指定してデータを追加する場合は以下3_4.sqlのように列名の指定を省略できます。

INSERT INTO sip_a.cars  VALUES ('1', 'セダン', '2590000', '2020-01-01');

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

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

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

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


なお、未定義を意味するNULLはシングルクォートで囲んではいけません。(例:3_6.sql)ここでもしも、'null'としてしまうと「nullという文字列」という意味になってしまいます。

INSERT INTO into sip_a.cars VALUES(3,"SUV",2990000,null);

Auto Increment設定のある列(id)はNULLのままで構いません。したがって上記のSQLは以下3_7.sqlのようにidには未定義を意味するnullを指定しても同じことになります。

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

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

さらに以下3_8.sqlのように列名を指定しなくても同じことになります。

【idを省略した場合のINSERT文】

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

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

5. 行の更新

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

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

UPDATE sip_a.cars SET end_of_life_dates = '2021-01-01' WHERE (id = '1');

このSQL文の意味は、

sip_a.cars の「car_id = '1' のレコードの列をend_of_life_dates = '2021-01-01'」に更新しなさい。という意味です。

6. 行の削除

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

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

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

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

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

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

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

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

求める結果
図3.21 求める結果

7.1. SQLのinsert文を使う方法

必要なSQLのinsert文の確認

今回記述したいSQL文は下図3.22のとおりです。全体が一文になっているのがお分かりになるでしょうか?この書き方はバルクインサートという方法でデータが増えても速度が速いという特徴があります。あとの図3.27と比較してみてください。

mysql スキーマ 作成
図3.22 バルクインサート

以下はこれまでの復習も兼ねたポイント解説です。

ポイント

  1. すべての列にデータを挿入することでテーブル名の後ろの列名の列挙を省略しています。
  2. nullのデータは直接nullと書きました。(シングルクォートやダブルクオートで囲わない)
  3. AUTO_INCREMENT 属性のついた列はnullを入力すれば適切な番号が振られます。
  4. 文字列はダブルクオートで囲むこともできます。(エクセルの場合シングルクォートで囲うと別の意味になるため)
  5. 最後のセミコロンを忘れずに。

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

mysql スキーマ 作成
図3.23 通常のinsert

エクセルでSQL文を組み立てる

SQL文をエクセルなどのスプレッドシートを使って作成すると、テストデータを微調整したいときに便利なため紹介します。上図3.27のSQL文をエクセルを使って組み立ててみましょう。具体的には下図3.24のように記述すればよいでしょう。

エクセルでインサート文
図3.24 エクセルを使ってインサート文を組み立てる

B列~J列には個々の記号や単語を入力します。そして、K2セルで「=CONCAT(範囲)」という式を入れて文字列を結合してSQL文を作っています。ちなみに、CONCATは【concatenate】の略で「連結」という意味のエクセル関数です。後は出来上がった上記SQL文(K列)をMySQLWorkbenchで流し込めばOKです。ポイントは、まず1件のデータで上手くいくことを確かめてから全件を流し込むことです。

7.2. Workbenchのインポート機能を使う場合

エクセルでCSVファイルを編集してWorkbenchでインポートします。日本語を扱うと文字化けが起こる場合があります。ポイントは先に雛形をエクスポートして、一行目の列名の下に追加したいレコードを貼り付けることです。少々複雑なので講師が研修会場で実演いたします。

7.3. テーブルをインポートする例題

例題1

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

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

※本当は姓名や住所も「名字と名前「や「県と市町村」のように細かい部品に分けた方が並べ替えや検索の時に便利なうえに高速になります。

customers表
図 3.25 customers表

例題2

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

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

mysql スキーマ 作成
図 3.26 sales表

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

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

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

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

①の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文の意味は次回以降に回して、まずはどのような機能があるのかだけを見ていきます。

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

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

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

SELECT cars.id,
cars.name,
cars.price,
cars.end_of_life_dates
FROM sip_a.cars;

②以下3_13.sqlは1件のレコードを挿入するINSERT文です。

INSERT INTO sip_a.cars
(car_id,
name,
price,
end_of_life_dates)
VALUES
(<{car_id: }>,
<{name: }>,
<{price: }>,
<{end_of_life_dates: }>);

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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