この記事では、弊社の新人エンジニア研修の参考にMySQLを解説します。
前回はMySQLWorkbenchの使い方を解説しました。
今回は、MySQL Workbenchを使ってスキーマとテーブルを作成する手順を解説します。
以下のようなテーブルを作成し、データを投入してみましょう。
※MySQLではテーブル名や列名に日本語を使うことも可能です。
しかし、一般的ではないためここでも英語表記で進めていきます。
スキーマの作成
MySQL Workbenchを起動し、操作したいConnectionに接続します。
｢Schemas｣で右クリックして「Create Schema」を選択します。
スキーマ名を入力して「Apply」ボタンを押下します。
なお、Charsetは文字コード、Collationは照合順序のことです。
照合順序はあとあとレコードの並べ替えをする際に問題となります。
Defaultで「utf8_general_ci」となっており、日本語対応のWebアプリケーションを作る際に都合が良いのでこのままにします。
スキーマ名やテーブル名は全て小文字でないといけません。
以下のようなダイアログが出たら「OK」ボタンを押下することで自動的に小文字になります。
発行されるSQL文を確認して「Apply」ボタンを押下します。
このとき、観察眼に優れた方は`sip_a`のようにスキーマ名が`（バッククォート）で囲まれていることに気づいたかもしれません。
これは何のためにつけているかというとMySQLの予約語をエスケープするためです。
Javaにも予約語がありましたね。
例えば、「int int = 0」のように予約語を変数名などに使うことができませんでした。
MySQLにも予約語があります。（Workbenchで青字になる）
以下のように予約後はそのままではスキーマ名、テーブル名、列名に指定できないのです。
しかし、エスケープすることで指定が可能になるのです。
念の為、予約語が使われても大丈夫なようにというworkbenchの親切機能なのでした。
テーブルの作成
次のようなcarsテーブルを作成します。
この研修のルールとしてテーブル名には名詞の複数形を使用することとします。
[Create Table…]メニューを選択します
テーブルを作成したい｢Schemas｣の下の｢Tables｣を右クリックしてポップアップメニューを表示し[Create Table…]メニューを選択します。
テーブル名を入力します。
Table Name:の欄に今回は「cars」と入力してください。
Column NameとDatatypeを設定します
Columnは列、Datatypeはデータ型のことです。
idはINT型で5桁としました。
nameはVARCHAR型で45桁としました。
VARCHAR とはvariable characterの略で可変長の文字列という意味です。
可変長ですから固定長と違い短い文字列の場合も空白がデータ容量を使用しません。
priceは INT型で8桁としました。
end_of_life_dates（販売終了日）はDATE型としました。
なお、テーブル名や列名はこの研修ではスネークケースを使用することにします。
この後の最終課題でよく使うであろうMySQLのデータ型をまとめておきます。
MySQLのデータ型
| データ型 | 意味 | 備考 | 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 |
まずは、上記の4つをしっかり押さえてください。
また、このあとWebアプリケーションを作成する際にJavaのデータ型を決めるときのために「Javaのデータ型との対応」を確認しておいてください。
迷ったらひとまず（MySQLのことだけを考えれば）Stringにするという手もありえます。
例えば、INTの列に文字列の"1"をINSERTすることができます。
それでもデータを取り出したときには数値の1として取り出されます。
なぜなら、その列はINTと型指定されているためMySQLが自動で文字列を整数に変換して入力してくれるのです。
2.｢new_tableダイアログボックス｣が表示されます。
テーブル名やCollation、データベースエンジンを選択します。
当社の新人研修では､テーブル名のみ入力し、ここでもCharset/Collationはデフォルトのままで結構です。
テーブルの列の設定
今回は、id,name,price, end_of_life_dates の4列を定義します。
以下のように設定してください。
それぞれの項目の意味は以下のとおりです。
PK: Primary Key ＝ 主キー
NN: Not Null ＝ Nullを許容しない
UN:Unsigned ＝ マイナス符号なし
AI: Auto Increment ＝ 自動で採番する
Default/Expression ＝ デフォルト表現としてデータ入力がない場合の値
以上、本研修で使用が想定されるもののみを解説しました。
それぞれの意味は以下の部分でも確認することができます。
インデックスの設定
インデックスが必要な場合は､[Indexes]タブからインデックスを定義する列を選択します。
今回は確認にとどめインデックスを設定しません。
なお､主キーには自動的にインデックスが張られています。
画面下部の[Apply]ボタンを押します。
実行するSQL文が表示されます。内容を確認し問題がなければ[Apply]ボタンをクリックします。
以下のSQLが実行されます。
【CREATE TABLE 文】
CREATE TABLE sip_a.cars (id INT(5) NOT NULL AUTO_INCREMENT,name VARCHAR(45) NULL,price INT UNSIGNED NULL,end_of_life_dates DATE NULL DEFAULT NULL,
PRIMARY KEY (id));
なお、SQLは単語の途中でなければ改行や空白を入れて見やすくすることができます。
また、文末のセミコロン（ ; ）はJavaとは異なり必須ではないのですが、一つのファイルに複数のSQL文を記述する際には区切りとして必要になります。
研修では付けるようにしましょう。
これでテーブルが作成されました。
しかし、スキーマツリーには表示されていません。
スキーマツリーを右クリックして｢Refresh All｣を選択することで表示されます｡
テーブルに1件のレコードを挿入する
では、先に作成したcarsテーブルに1件のレコードを登録してみましょう。
これまでの操作でテーブルが表示されていればそのままで結構です。
表示されていない場合はcarsテーブルの「③テーブルを表示するアイコン」をクリックします。
以下のような空のテーブルが表示されました。
データの入力
テーブルに直接データを入力する
テーブル内をダブルクリックして編集状態にするとエクセルのように直接データを入力できます。
次のようなレコードを1件挿入してみます。
| 列名 | 値 | 備考 |
| id | 1 | オートインクリメントを利用 |
| name | 'セダン' | |
| price | 2590000 | |
| end_of_life_dates | '2020-01-01' |
SELECT * FROM sip_a.cars;
を実行すると空のテーブルが右下のサーバー応答エリアに表示されます。
サーバー応答エリアでテーブルのデータを直接入力します。
編集が完了しましたら、右下の｢Apply｣ボタンをクリックします。
編集内容をテーブルに反映させる以下のようなSQL文が表示されますのでApplyをクリックし、次の画面でFinishをクリックします。
このようにGUIで操作ができることも、その背後ではSQLが働いていることを意識することは重要です。
ということでレコードを挿入する INSERT文 を確認してみます。
【INSERT文】
INSERT INTO sip_a.cars (id, name, price, end_of_life_dates) VALUES ('1', 'セダン', '2590000', '2020-01-01');
なお、テーブルに含まれるすべての列に値を指定してデータを追加する場合は列名の指定を省略できます。
INSERT INTO sip_a.cars VALUES ('1', 'セダン', '2590000', '2020-01-01');
数値リテラルは シングルクォートで囲わなくて大丈夫です。
上記の例では、「 '2590000' 」を「2590000」と書いても同じ結果になります。
一方、文字列や日時データはシングルクォート(')またはダブルクオート (") で囲みます。
一般的にはシングルクォートで囲むことが多いです。
上記の例では、「 'セダン' 」や「 '2020-01-01' 」がその例です。
Javaの文字列はダブルクオートで囲んでいたことと混同しないようにしましょう。
ここでもしも、'null'としてしまうと「nullという文字列」という意味になってしまいます。
数値を文字列として入力しているという意味では上記の入力は間違っているのですが、MySQLはエラーにしません。
なぜなら、先に見たように列にはデータ型が指定されているからです。
変換可能な入力であれば文字列を数値として扱ってくれるのです。
なお、未定義を意味するNULLはシングルクォートで囲みません。
insert into cars values(3,"SUV",2990000,null);
Auto Increment設定のある列（id）はNULLのままで構いません｡
したがって上記のSQLは以下のようにidには未定義を意味するnullを指定しても同じことになります。
【idにnullを指定した場合のINSERT文】
INSERT INTO sip_a.cars (id, name, price, end_of_life_dates) VALUES (null','セダン', '2590000', '2020-01-01');
さらに列名を指定しなくても同じことになります。
【idを省略した場合のINSERT文】
INSERT INTO sip_a.cars (name, price, end_of_life_dates) VALUES ('セダン', '2590000', '2020-01-01');
行の更新
テーブルの内容を直接書き換えることでレコードの更新ができます。
【UPDATE文】
UPDATE sip_a.cars SET end_of_life_dates = '2021-01-01' WHERE (id = '1');
このSQL文の意味は、
sip_a.cars の
id = '1' のレコードの列
end_of_life_dates = '2021-01-01'
に更新しなさい。
という意味です。
行の削除
行を削除するには対象行を右クリックして｢Delete Row(s)｣を選択します｡
ただし、データベースの役割は事実を記録することです。
しかし、レコードを削除してしまうと事実が失われてしまいます。
したがって、レコードの削除はそもそも間違ったデータを入力してしまった場合など極めて限定的な場面でしか使いません。
レコードを削除する代わりに今回のcarsテーブルのようにend_of_life_dates（販売終了日）の列を用意するというのはよく使われる方法です。
この列がNULLではなく値が入っているときにすでに販売が終了されているとみなすわけです。
delete文で削除することを物理削除、実際には削除しないことを論理削除とも言います。
複数レコードの一括挿入
手入力することが困難な量のデータをテーブルに入力する方法を解説します。
例えば、以下のように１件しかないテストデータを増やす方法です。
SQLのinsert文を使う方法
必要なSQLのinsert文の確認
今回記述したいSQL文は以下の通りです。
このSQL文をエクセルなどのスプレッドシートを使って作成すると、テストデータを微調整したいときに便利なため紹介します。
ポイント
- すべての列にデータを挿入することでテーブル名の後ろの列名の列挙を省略しています。
- nullのデータは直接nullと書きました。（シングルクォートやダブルクオートで囲わない）
- AUTO_INCREMENT 属性のついた列はnullを入力しても適切な番号が振られました。
- 文字列はダブルクオートで囲むこともできます。エクセルの場合シングルクォートで囲うと別の意味になるためこうしています。
- 最後のセミコロンを忘れずに。
なお、上記の文はバルクインサートという方法でデータが増えても速度が速いという特徴があります。
以下の通常のinsert文と比較すると速度が速くなります。
エクセルでSQL文を組み立てる
この文をエクセルを使って組み立てます。
具体的には以下のように記述すればよいでしょう。
B列～J列には個々の記号や単語を入力します。
そして、K2セルで「=B2&C2&D2&E2&F2&G2&H2&I2&J2」という式を入れて文字列を結合してSQL文を作っています。
後は出来上がった上記SQL文(K列)をMySQLWorkbenchで流し込めばOKです。
ポイントは、まず１件のデータで上手くいくことを確かめてから全件を流し込むことです。
Workbenchのインポート機能を使う場合
講師が研修会場で実演します。
テーブル作成の例題
例題1
以下のようなprefectures表をインポートしなさい。
必要なデータは講師から入手すること。
例題2
以下のようなcustomers表をインポートしなさい。
必要なデータは講師から入手すること。
例題3
以下のようなsales表をインポートしなさい。
必要なデータは講師から入手すること。
テーブルの削除と変更
新人エンジニア研修では、作ったテーブルを変更することは良くあります。
また、データを空にしたり、テーブルそのものを削除することもあります。
ここでその方法を見ていきましょう。
テーブルを右クリックしてコンテキストメニューを表示します。
①のAlter Table...を選択するとテーブルの変更画面に移ります。
あとは、テーブルを変更してapplyボタンを押せば変更が反映されます。
②テーブルをスキーマごと削除してしまいたいときはDrop Table...を選びます。
以下のようなSQL文が発行され、テーブルが削除されます。
DROP TABLE sip_a.cars;
③テーブルの構造（スキーマ）を残して全てのレコードを削除したいときはTruncate Table...を選びます。
以下のようなSQL文が発行され、全てのレコードが削除されます。
TRUNCATE sip_a.cars;
その他の便利なコンテキストメニュー
コンテキストメニューを紹介したのでその他の便利な機能を紹介します。
当社の新人エンジニア研修ではこの後、さまざまなSQL文を学んでいきます。
その際に確実にキーボードからコマンドを打てるようになることがもっとも望ましいのですが、限られた研修期間中には難しいものです。
そこで、コンテキストメニューを使ってSQLの雛形を書く方法をお伝えします。
具体的なSQL文の意味は次回以降に回して、まずはどのような機能があるのかだけを見ていきます。
SQLエディターにSQLを送ることができます。
①全てのレコードを選択するSELECT文です。
SELECT cars.id,cars.name,cars.price,cars.end_of_life_dates
FROM sip_a.cars;
②レコードを挿入するINSERT文です。
INSERT INTO sip_a.cars
(id,name,price,end_of_life_dates)
VALUES
(<{id: }>,
<{name: }>,
<{price: }>,
<{end_of_life_dates: }>);
③レコードを更新するUPDATE文です。
UPDATE sip_a.cars
SETid = <{id: }>,name = <{name: }>,price = <{price: }>,end_of_life_dates = <{end_of_life_dates: }>
WHERE id = <{expr}>;
④レコードを削除する DELETE文です。
DELETE FROM sip_a.cars
WHERE <{where_expression}>;
以上、新人エンジニア研修で使うテーブルを作成し編集する方法について学びました。
まとめ
以下のSQL文を覚えましょう。
- スキーマを作るときはCreate Schema文
- テーブルを作るときはCreate Table文
- テーブルにレコードを挿入するときはINSERT文
- テーブルのデータを更新するときはUPDATE文
- レコードを削除するときはDELETE文
- テーブルの構成を変更するときはAlter Table文
- テーブルを構成ごと削除したいときはDrop Table文
- テーブルからデータのみ全て削除したいときはTruncate Table文
以上、今回は、新人エンジニア研修で使うテーブルを作成し編集する方法について学びました。
次は、 ER図からテーブルを作成する 方法について学んでいきましょう。