第3章 MySQLでデータベースを構築・操作する

この章のゴール

第2章では、業務から商品、取引先、出荷予定などの管理対象を見つけました。 この章では、そのうち商品分類、容器種別、商品、取引先をMySQLへ構築します。

この章を終えると、次のことができるようになります。

• MySQL Workbenchから研修用MySQLへ接続する
• SQLを入力し、実行結果とエラーを確認する
• 研修用データベースとテーブルを作成する
• 主キー、外部キー、必須、重複禁止などの制約を設定する
• データを登録し、必要な列と行を検索する
• 更新・削除の前後に、対象行を確認する
• 制約により不正なデータが拒否されることを確認する

180分のうち、約115分を講師と一緒に行う操作、約45分を演習、約20分を確認と振り返りに使います。 SQL構文を暗記するのではなく、第2章の設計がMySQL上でどのように実現されるかを確認します。

使用する環境と教材ファイル

受講者PCには、研修開始前にMySQL ServerとMySQL Workbenchが準備されているものとします。 接続名、ユーザー名、パスワードは講師から指定します。

本章では、次のSQLファイルを使用します。

1. 03-create-tables.sql — データベースと4テーブルを作成
2. 03-insert-sample-data.sql — 共通サンプルデータを登録
3. 03-basic-operations.sql — 検索、追加、更新、削除を練習

各ファイルは、上から順に使用します。 1番と2番は、新しい研修用データベースへ1回だけ実行します。

実在する業務データベースへ接続しないでください。

この章では、講師が指定したローカルの研修環境と、架空のサンプルデータだけを使用します。 Workbenchの接続名と、画面上部に表示される接続先を実行前に確認してください。

MySQL Workbenchから接続する

MySQL Workbenchを起動すると、登録済みの接続が一覧表示されます。 講師が指定した研修用接続を開き、パスワードが求められた場合は指定されたものを入力します。

接続後は、主に次の領域を使います。

図3-1 SQLを書く場所、検索結果を見る場所、実行成否を見る場所を区別します。

図は画面構成を説明する模式図です。 Workbenchのバージョンや画面幅によって、ボタンの形、パネルの位置、表示名が異なる場合があります。

領域	役割	この章での確認内容
Navigator／SCHEMAS	データベースやテーブルを表示する	作成した4テーブルがあるか
SQLエディター	SQLを入力・実行する	選択範囲または一文を実行する
Result Grid	SELECTの結果を表形式で表示する	行数、列名、値を確認する
Action Output	実行した処理の成否を表示する	成功、警告、エラーを確認する

接続を確認する

新しいSQLタブを開き、次の二文を一文ずつ実行します。

SELECT VERSION();

SELECT CURRENT_USER();

Result GridにMySQLのバージョンと接続ユーザーが表示されれば、SQLを実行できています。 研修では、講師が指定したバージョンと接続ユーザーに一致することを確認します。

SQLを実行する基本操作

SQLは、DBMSへ処理を依頼するための文です。 この教材では、SQLキーワードを大文字、テーブル名と列名を半角英小文字のsnake_caseで記述します。

SELECT product_code, product_name
FROM products
WHERE is_active = TRUE;

• SELECT、FROM、WHEREはSQLキーワード
• productsはテーブル名
• product_code、product_name、is_activeは列名
• 文末の;は一つのSQL文の終わり

Workbenchでは、実行したい一文にカーソルを置くか、実行範囲を選択してから実行ボタンを使います。 複数の文をまとめて実行する前に、選択範囲が意図どおりか確認してください。

コメントを利用する

-- から行末まではコメントです。--の後ろには半角空白を入れます。

-- 商品コード順に表示する
SELECT product_code, product_name
FROM products
ORDER BY product_code;

配布するSQLファイルでは、実行順と確認内容をコメントで示しています。

エラーが出たら止まって読む

エラーが出たときに、同じSQLを何度も実行しても解決しません。 次の順で確認します。

1. Action Outputで、失敗した文とエラーメッセージを確認する
2. テーブル名、列名、引用符、カンマ、;を確認する
3. 使用中のデータベースを確認する
4. そのSQLより前の処理が成功しているか確認する
5. 修正後は、必要な一文だけを選択して再実行する

データベースを作成する

MySQLでは、データベースとスキーマはほぼ同じ意味で使われます。 本研修では、研修用データベース名をai_database_trainingとします。

CREATE DATABASE ai_database_training
    DEFAULT CHARACTER SET utf8mb4;

USE ai_database_training;

utf8mb4は、日本語を含むUnicode文字を保存するための文字セットです。 USEは、後続のSQLが操作するデータベースを選びます。

次のSQLで、現在選択されているデータベースを確認できます。

SELECT DATABASE();

結果がai_database_trainingでない場合は、そのままテーブルを作らず、USE文から確認します。

テーブルを作る順番

外部キーで参照される側のテーブルを先に作ります。 商品テーブルは商品分類と容器種別を参照するため、最後に作成します。

図3-2 親となるテーブルを先に作り、外部キーを持つ商品テーブルを後から作ります。

この章で作るテーブルは次の4つです。

テーブル	内容	他テーブルとの関係
product_categories	商品分類	商品から参照される
package_types	容器種別	商品から参照される
customers	取引先	本章では独立して作成する
products	商品	商品分類と容器種別を参照する

取引先と出荷先、出荷予定、出荷予定明細は、第5章の総合設計で追加します。

CREATE TABLEで構造を定義する

最初に、商品分類テーブルを見てみましょう。

CREATE TABLE product_categories (
    product_category_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    category_code VARCHAR(20) NOT NULL,
    category_name VARCHAR(50) NOT NULL,
    PRIMARY KEY (product_category_id),
    CONSTRAINT uq_product_categories_code
        UNIQUE (category_code)
) ENGINE = InnoDB;

列の定義

列名の後ろに、データ型と必要な属性を指定します。

記述	意味	使用例
INT UNSIGNED	0以上の整数	内部ID
SMALLINT UNSIGNED	比較的小さい0以上の整数	内容量、入数
VARCHAR(20)	最大20文字の可変長文字列	コード
BOOLEAN	真または偽	取扱中かどうか
NOT NULL	未入力を許可しない	必須項目
AUTO_INCREMENT	登録時に連番を割り当てる	内部ID
DEFAULT TRUE	省略時の初期値を真にする	取扱中フラグ

MySQLでBOOLEANを検索すると、結果には1または0と表示されることがあります。 この教材では、SQL中では意味が読み取りやすいTRUEとFALSEを使います。

制約の定義

制約は、業務ルールに合わないデータをDBMSが拒否するための仕組みです。

制約	目的	本章の例
PRIMARY KEY	一件を一意に識別する	product_id
UNIQUE	同じ業務コードの重複を防ぐ	product_code
NOT NULL	必須項目の未入力を防ぐ	product_name
FOREIGN KEY	存在する親データだけを参照する	商品から商品分類を参照
DEFAULT	値を省略したときの初期値を決める	is_activeを真にする

外部キーを定義する

商品テーブルでは、次のように商品分類を参照します。

CONSTRAINT fk_products_category
    FOREIGN KEY (product_category_id)
    REFERENCES product_categories (product_category_id)
    ON UPDATE RESTRICT
    ON DELETE RESTRICT

RESTRICTは、商品から参照されている商品分類のID変更や削除を拒否します。 分類を消した結果、商品がどの分類にも属さなくなることを防ぎます。

本章では、外部キーを確実に利用できるよう、各テーブルのストレージエンジンをInnoDBとします。

作成用SQLを実行する

03-create-tables.sqlをWorkbenchで開きます。 接続先が研修用MySQLであることを確認してから、ファイル全体を1回だけ実行してください。

実行後は、次を確認します。

1. Action Outputですべて成功している
2. SCHEMASにai_database_trainingが表示される
3. Tablesに4テーブルが表示される
4. 次のSQLが4テーブルそれぞれの列を表示する

DESCRIBE product_categories;
DESCRIBE package_types;
DESCRIBE customers;
DESCRIBE products;

SCHEMASへすぐに表示されない場合は、更新ボタンで一覧を更新します。

テーブル作成SQLそのものを確認する場合は、次を使います。

SHOW CREATE TABLE products;

WorkbenchのResult Gridに、主キー、UNIQUE、外部キーを含む定義が表示されます。

INSERTでサンプルデータを登録する

テーブルを作っただけでは、行はまだありません。 INSERTを使ってサンプルデータを登録します。

INSERT INTO package_types (
    package_type_id,
    package_type_code,
    package_type_name
) VALUES
    (1, 'CAN', '缶'),
    (2, 'PET', 'ペットボトル');

• INSERT INTOの後ろに登録先テーブルを書く
• 括弧内へ、値を対応させる列を順番に書く
• VALUESの後ろへ、一行分の値を書く
• 文字列はシングルクォート'で囲む
• 複数行を登録するときは、行同士をカンマで区切る

研修データでは全員のIDをそろえるため、最初のマスターデータへIDを明示しています。 通常の新規登録では、AUTO_INCREMENTのIDを省略し、MySQLに採番させます。

03-insert-sample-data.sqlを開き、ファイル全体を1回だけ実行します。

登録される件数は次のとおりです。

• 商品分類：4件
• 容器種別：2件
• 取引先：2件
• 商品：4件

SELECTで登録結果を確認する

SELECTは、テーブルから必要な列と行を取得します。 本研修ではSQL検索を網羅せず、設計と操作の確認に必要な範囲だけを扱います。

必要な列を選ぶ

SELECT
    product_code,
    product_name,
    units_per_case
FROM products;

SELECT *を使うと全列を取得できますが、列の意味を確認する練習では、必要な列名を明示します。

WHEREで行を絞る

SELECT
    product_code,
    product_name,
    units_per_case
FROM products
WHERE package_type_id = 1;

WHEREは条件に合う行だけを取得します。 この時点では容器種別IDの1しか表示できません。 第4章でテーブルを結合し、1を「缶」という名称と一緒に表示します。

ORDER BYで並べる

SELECT
    product_code,
    product_name
FROM products
ORDER BY product_code;

ORDER BYは結果の並び順を指定します。 指定しなければ、毎回同じ順で表示されるとは限りません。

追加・更新・削除を安全に練習する

更新と削除は、対象行を間違えると複数のデータへ影響します。 本章では、操作練習用の商品PRD099だけを使い、次の順序を守ります。

図3-3 更新・削除は、対象確認、実行、結果確認を一組にします。

INSERTで練習用商品を追加する

INSERT INTO products (
    product_code,
    product_name,
    product_category_id,
    package_type_id,
    content_amount,
    content_unit,
    units_per_case,
    is_active
) VALUES (
    'PRD099',
    '操作練習用ドリンク',
    1,
    1,
    350,
    'ml',
    24,
    TRUE
);

product_idは指定していません。 MySQLが次のIDを自動的に割り当てます。

UPDATEの前後で確認する

最初に、変更対象が一件であることを確認します。

SELECT product_code, product_name, is_active
FROM products
WHERE product_code = 'PRD099';

対象が正しければ、商品名と取扱状態を更新します。

UPDATE products
SET
    product_name = '操作練習用ソーダ',
    is_active = FALSE
WHERE product_code = 'PRD099';

同じSELECTをもう一度実行し、変更された列と変更されていない列を確認します。

WHEREのないUPDATEを実行しないでください。

WHEREを省略すると、テーブル内のすべての行が更新対象になります。

DELETEの前後で確認する

削除前にも、同じ条件のSELECTで対象を確認します。

SELECT product_code, product_name
FROM products
WHERE product_code = 'PRD099';

一件だけであることを確認してから削除します。

DELETE FROM products
WHERE product_code = 'PRD099';

削除後に同じSELECTを実行し、0行になったことを確認します。

削除した行は、この操作だけでは元に戻せません。

本章では再作成できる研修データだけを削除します。実務では、削除権限、承認、バックアップ、論理削除などを別途設計します。

制約がデータを守ることを確認する

外部キーを設定したproductsには、存在する商品分類IDと容器種別IDしか登録できません。

03-basic-operations.sqlの末尾には、存在しない商品分類ID999を指定したINSERTがコメントとして用意されています。 講師の指示がある場合だけ、その文を選択して実行します。

MySQLは外部キーの条件に合わないため登録を拒否します。 Action Outputのエラーを確認した後、次のSQLでPRD098が登録されていないことを確認します。

SELECT product_code, product_name
FROM products
WHERE product_code = 'PRD098';

エラーは操作の失敗ですが、この場合は制約が正しく働いた証拠でもあります。 AIやアプリから誤ったIDが送られた場合も、DBMSが最後の防波堤になります。

演習2：小規模な商品データベースを構築・操作する

配布された三つのSQLファイルを使い、次の順で実施してください。

課題1：データベースとテーブルを作る

1. 研修用接続を開く
2. 03-create-tables.sqlを1回実行する
3. SELECT DATABASE();で使用中のDBを確認する
4. SCHEMASを更新し、4テーブルを確認する
5. DESCRIBE products;で列を確認する

確認欄：

• ai_database_trainingが表示された
• 4テーブルが表示された
• product_idが主キーになっている
• product_category_idとpackage_type_idがある

課題2：サンプルデータを登録する

1. 03-insert-sample-data.sqlを1回実行する
2. 各テーブルをSELECTして件数と値を目視確認する
3. 商品コードPRD001からPRD004があることを確認する
4. 日本語の商品名と容器名が文字化けしていないことを確認する

SELECT * FROM product_categories;
SELECT * FROM package_types;
SELECT * FROM customers;
SELECT * FROM products;

課題3：商品を検索する

次の問いに答えるSQLを作ってください。

1. 商品コードと商品名を、商品コード順に表示する
2. 容器種別IDが1の商品について、商品コード、商品名、入数を表示する
3. 取扱中の商品について、商品コード、商品名を表示する
4. 商品コードがPRD003の商品を一件表示する

この章では、容器種別名や商品分類名を一緒に表示できなくても構いません。 複数テーブルから名称を取得する方法は第4章で扱います。

課題4：追加・更新・削除を確認する

03-basic-operations.sqlの手順4から10を、一つの処理単位で選択して実行します。

段階	確認すること
追加後	PRD099が一件表示される
更新前	商品名が「操作練習用ドリンク」、取扱中である
更新後	商品名が変わり、is_activeが偽である
削除前	対象がPRD099一件だけである
削除後	同じ条件の検索結果が0行である

課題5：制約を説明する

次の三点を、自分の言葉で記録してください。

1. PRIMARY KEYは、どの重複を防ぐか
2. UNIQUEは、どの業務コードの重複を防ぐか
3. FOREIGN KEYがなければ、どのような商品を登録できてしまうか

よくあるエラーと確認場所

状況	主な原因	最初に確認すること
データベースが存在すると表示される	作成用SQLを再実行した	既に4テーブルがあるか
テーブルが見つからない	使用DBが選択されていない	SELECT DATABASE();
重複エラー	同じコードを2回登録した	SELECTで既存行を確認
外部キーエラー	親テーブルにIDがない	分類・容器種別を先に確認
日本語が文字化けする	接続や文字セットが異なる	DBの文字セットと接続設定
更新対象が0行	WHEREの値が存在しない	更新前のSELECT結果

エラー文の表現はMySQLやWorkbenchのバージョンで異なる場合があります。 エラー番号だけを暗記せず、「どの文が、どの制約により失敗したか」を確認します。

やり直す必要がある場合

作成用・登録用SQLを繰り返し実行すると、「既に存在する」「値が重複する」というエラーになります。 これは正常な制約確認でもあり、無理に同じ文を再実行する必要はありません。

研修用データベースを最初から作り直す必要がある場合は、必ず講師へ確認してください。 次のSQLはai_database_training内のテーブルとデータをすべて削除し、元に戻せません。

DROP DATABASE ai_database_training;

実在DBや別のスキーマへ置き換えて実行してはいけません。 削除後は、作成用SQL、登録用SQLの順で再実行します。

この章で作成したもの

• 研修用データベースai_database_training
• 商品分類、容器種別、取引先、商品の4テーブル
• 主キー、業務コードの重複禁止、外部キー
• 共通して使用する4商品のサンプルデータ
• 検索、追加、更新、削除の確認結果
• 制約によって不正データが拒否されることの確認

作成したデータベースは削除せず、第4章で引き続き使用します。

この章のまとめ

• Workbenchでは、SQLエディター、Result Grid、Action Outputを区別して使います。
• CREATE TABLEで列、データ型、主キー、外部キーなどを定義します。
• 外部キーで参照されるテーブルを先に作ります。
• INSERTで登録し、SELECTで登録結果を確認します。
• UPDATEとDELETEは、必ず対象確認、実行、結果確認の順で行います。
• 制約は、アプリやAIから誤った値が送られた場合にもデータを守ります。
• SQLは設計を実現し、設計どおりに動くか確認するための手段です。

次章では、外部キーをたどって複数テーブルを結合し、名称と数量を一緒に取り出します。

第4章「複数テーブルから必要な情報を取り出す」へ進む