Print Friendly, PDF & Email

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

前回はMySQLWorkbenchの使い方を解説しました。

今回は、MySQL Workbenchを使ってスキーマとテーブルを作成する手順を解説します。

以下のようなテーブルを作成し、データを投入してみましょう。

car_table

※MySQLではテーブル名や列名に日本語を使うことも可能です。

しかし、一般的ではないためここでも英語表記で進めていきます。

スキーマの作成

MySQL Workbenchを起動し、操作したいConnectionに接続します。

「Schemas」で右クリックして「Create Schema」を選択します。

mysql スキーマ 作成

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

なお、Charsetは文字コード、Collationは照合順序のことです。

照合順序はあとあとレコードの並べ替えをする際に問題となります。

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

mysql スキーマ 作成

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

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

mysql スキーマ 作成

発行されるSQL文を確認して「Apply」ボタンを押下します。

このとき、観察眼に優れた方は`sip_a`のようにスキーマ名が`(バッククォート)で囲まれていることに気づいたかもしれません。

mysql スキーマ 作成

これは何のためにつけているかというとMySQLの予約語をエスケープするためです。

Javaにも予約語がありましたね。

例えば、「int int = 0」のように予約語を変数名などに使うことができませんでした。

MySQLにも予約語があります。(Workbenchで青字になる)

以下のように予約後はそのままではスキーマ名、テーブル名、列名に指定できないのです。

mysql スキーマ 作成

しかし、エスケープすることで指定が可能になるのです。

念の為、予約語が使われても大丈夫なようにというworkbenchの親切機能なのでした。

テーブルの作成

次のようなcarsテーブルを作成します。

この研修のルールとしてテーブル名には名詞の複数形を使用することとします。

car_table
carsテーブル

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

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

mysql スキーマ 作成

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

Table Name:の欄に今回は「cars」と入力してください。

mysql スキーマ 作成

Column NameとDatatypeを設定します

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

idはINT型で5桁としました。

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

VARCHAR とはvariable characterの略で可変長の文字列という意味です。

可変長ですから固定長と違い短い文字列の場合も空白がデータ容量を使用しません。

priceは INT型で8桁としました。

end_of_life_dates(販売終了日)はDATE型としました。

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

mysql スキーマ 作成

この後の最終課題でよく使うであろう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 デフォルト表現としてデータ入力がない場合の値

mysql スキーマ 作成

以上、本研修で使用が想定されるもののみを解説しました。

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

mysql スキーマ 作成

インデックスの設定

インデックスが必要な場合は、[Indexes]タブからインデックスを定義する列を選択します。

今回は確認にとどめインデックスを設定しません。

なお、主キーには自動的にインデックスが張られています。

画面下部の[Apply]ボタンを押します。

mysql スキーマ 作成

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

mysql スキーマ 作成

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

mysql スキーマ 作成

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

では、先に作成したcarsテーブルに1件のレコードを登録してみましょう。

これまでの操作でテーブルが表示されていればそのままで結構です。

表示されていない場合はcarsテーブルの「③テーブルを表示するアイコン」をクリックします。  

MySQL_workbench

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

mysql スキーマ 作成

データの入力

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

テーブル内をダブルクリックして編集状態にするとエクセルのように直接データを入力できます。

次のようなレコードを1件挿入してみます。

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

SELECT * FROM sip_a.cars;

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

mysql スキーマ 作成

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

mysql スキーマ 作成

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

mysql スキーマ 作成

 編集内容をテーブルに反映させる以下のようなSQL文が表示されますのでApplyをクリックし、次の画面でFinishをクリックします。 

mysql スキーマ 作成
MySQL_workbench

このように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');

行の更新

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

mysql スキーマ 作成

【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)」を選択します。 

mysql スキーマ 作成

ただし、データベースの役割は事実を記録することです。

しかし、レコードを削除してしまうと事実が失われてしまいます。

したがって、レコードの削除はそもそも間違ったデータを入力してしまった場合など極めて限定的な場面でしか使いません。

レコードを削除する代わりに今回のcarsテーブルのようにend_of_life_dates(販売終了日)の列を用意するというのはよく使われる方法です。

この列がNULLではなく値が入っているときにすでに販売が終了されているとみなすわけです。

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

複数レコードの一括挿入

手入力することが困難な量のデータをテーブルに入力する方法を解説します。

例えば、以下のように1件しかないテストデータを増やす方法です。

MySQLWorkbench
求める結果

SQLのinsert文を使う方法

必要なSQLのinsert文の確認

今回記述したいSQL文は以下の通りです。

このSQL文をエクセルなどのスプレッドシートを使って作成すると、テストデータを微調整したいときに便利なため紹介します。

ポイント

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

なお、上記の文はバルクインサートという方法でデータが増えても速度が速いという特徴があります。

以下の通常のinsert文と比較すると速度が速くなります。

mysql スキーマ 作成

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

この文をエクセルを使って組み立てます。

具体的には以下のように記述すればよいでしょう。

mysql スキーマ 作成
エクセルを使ってインサート文を組み立てる

B列~J列には個々の記号や単語を入力します。

そして、K2セルで「=B2&C2&D2&E2&F2&G2&H2&I2&J2」という式を入れて文字列を結合してSQL文を作っています。

後は出来上がった上記SQL文(K列)をMySQLWorkbenchで流し込めばOKです。

ポイントは、まず1件のデータで上手くいくことを確かめてから全件を流し込むことです。

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

講師が研修会場で実演します。

テーブル作成の例題

例題1

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

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

mysql スキーマ 作成

例題2

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

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

mysql スキーマ 作成

例題3

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

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

mysql スキーマ 作成

テーブルの削除と変更

新人エンジニア研修では、作ったテーブルを変更することは良くあります。

また、データを空にしたり、テーブルそのものを削除することもあります。

ここでその方法を見ていきましょう。

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

テーブルを選んでコンテキストメニューを表示させる
テーブルを選んでコンテキストメニューを表示させる

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

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

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

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

DROP TABLE sip_a.cars;

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

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

TRUNCATE sip_a.cars;

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

コンテキストメニューを紹介したのでその他の便利な機能を紹介します。

当社の新人エンジニア研修ではこの後、さまざまなSQL文を学んでいきます。

その際に確実にキーボードからコマンドを打てるようになることがもっとも望ましいのですが、限られた研修期間中には難しいものです。

そこで、コンテキストメニューを使ってSQLの雛形を書く方法をお伝えします。

具体的なSQL文の意味は次回以降に回して、まずはどのような機能があるのかだけを見ていきます。

SQLエディターにSQLを送ることができます。

mysql スキーマ 作成

①全てのレコードを選択する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
SET
id = <{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図からテーブルを作成する 方法について学んでいきましょう。

データベース(MySQL)へ戻る