3.テーブルにデータを格納し編集できるようになる

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

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

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

2. スキーマの作成

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

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

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

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

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

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

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

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

3. テーブルの作成

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

3.1. テーブルの作成手順

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

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

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

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

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にしておけば日付の加算・減算をすることもできますが、文字列型の場合はそのままでは実現できません。

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 ＝ デフォルト表現としてデータ入力がない場合の値

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

3.4. インデックスの設定

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

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

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

以下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｣を選択することで表示されます｡

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

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

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

4.1. データの入力

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

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

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

SELECT * FROM sip_a.cars;

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

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

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

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

このように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)｣を選択します｡ 

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

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

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

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

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

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

7.1. SQLのinsert文を使う方法

必要なSQLのinsert文の確認

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

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

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

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

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

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

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

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

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

例題1

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

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

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

例題2

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

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

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

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

下図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文を送ることができます。

①以下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文を暗記しなくても使うことはできます。ただし、繰り返しになりますが、暗記してあればそれだけ早く、確実に、確信を持って使うことができます。

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

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

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