MariaDB講座 / 商品表を作成してデータを追加・検索する

CREATE TABLEでproducts表を作り、INSERTで三行を追加し、SELECTで保存内容を読み取ります。shopcourseに型と制約を明示して作成し、並び順と件数を指定することで、期待した結果と比較できます。

新しい実習表を準備する

データベースの作成を済ませ、実習用アカウントで接続します。最初に対象を調べます。

USE shopcourse;
SELECT DATABASE() AS current_database;
SHOW TABLES LIKE 'products';

shopcourseが存在し、productsがまだない状態を前提とします。同名の表がある場合は用途と内容を確認してから判断します。この例は既存表を無条件で置き換えません。CREATE TABLEの構造とINSERTの列指定で個々の命令を詳しく確認できます。

列の型と制約を決める

DECIMAL(10,2)は全体10桁のうち小数部が2桁で、整数10桁に小数2桁を加える意味ではありません。価格は同じ単位のサンプルで、実際の販売価格の主張ではありません。NOT NULLは欠損値を認めない指定です。在庫0は数量が分かっている状態でNULLとは違い、DEFAULT 0は列を省略した入力の既定値です。

これだけで業務規則が完成するわけではなく、範囲や不正入力の扱いには型やSQLモードも関係します。分類は導入用の単純な文字列で、表の関連付けは後から扱います。

作成と追加を一つのファイルで実行する

次の全体をUTF-8のlesson06-products.sqlに保存し、SOURCEの使い方に従って実行します。接続内で順番に入力しても構いません。

USE shopcourse;

CREATE TABLE products (
  product_id INT NOT NULL AUTO_INCREMENT,
  product_name VARCHAR(100) NOT NULL,
  category VARCHAR(30) NOT NULL,
  price DECIMAL(10, 2) NOT NULL,
  stock INT NOT NULL DEFAULT 0,
  PRIMARY KEY (product_id)
) ENGINE=InnoDB
  DEFAULT CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

INSERT INTO products (product_name, category, price, stock)
VALUES
  ('Notebook', 'Stationery', 3500.00, 25),
  ('Mug', 'Home', 12000.00, 8),
  ('Desk lamp', 'Home', 28000.00, 0);

SELECT product_id, product_name, category, price, stock
FROM products
ORDER BY product_id;

SELECT COUNT(*) AS product_count FROM products;

INSERTの列リストが値の意味を明示します。文字列は単引用符、数値はそのまま記述し、product_idはサーバーに生成させるため省略しています。ENGINE=InnoDBは保存エンジン、文字セットと照合順序は文字列の既定値です。

SOURCE中のエラーで停止しても、先に成功した表の作成や追加は自動で戻りません。エラー内容と現在の表を見ずに、ファイル全体を繰り返さないでください。

行と定義を確認する

新しい表で最初の追加ならこの三行になり、COUNT(*)は3です。以前使った表ではIDが1から3になるとは限りません。定義も調べます。

USE shopcourse;
DESCRIBE products;
SHOW CREATE TABLE products;

DESCRIBEは列、NULLの許可、既定値やキーを要約し、SHOW CREATE TABLEは保存済みの定義を示します。定義の表示はサーバーが正規化することもあります。ORDER BY product_idが並び順を指定しており、指定しない表示順に依存しないでください。

一つの条件を変えて結果を比較する

価格が10000以上の商品を読みます。

SELECT product_id, product_name, price
FROM products
WHERE price >= 10000
ORDER BY product_id;

MugとDesk lampが返ります。10000だけを20000に変えるとDesk lampだけになります。比較後は10000に戻してください。次は在庫0の商品です。

SELECT product_id, product_name, stock
FROM products
WHERE stock = 0
ORDER BY product_id;

Desk lampが返ります。stock = 0だけをstock > 0に変えるとNotebookとMugになります。元の条件へ戻して比較します。SELECTの条件を変えたのであり、保存済み在庫を更新したわけではありません。

再実行と初期化の範囲を確認する

Table already existsなら表を確認します。INSERTだけを再実行すると、同じ商品名でも別IDの行が追加されるため、件数が6などに増えます。主キーはIDの重複を防ぐもので、商品名の重複を自動禁止しません。想定外の値なら接続先とSELECT DATABASE()も調べます。

全体をやり直す場合は、削除してよい実習表か確認し、必要なデータを保存します。

USE shopcourse;
SELECT DATABASE() AS current_database;
SELECT COUNT(*) AS rows_to_remove FROM products;

この表を消してよいと確認した場合だけ実行します。

DROP TABLE shopcourse.products;

その後、全体のSQLを実行すると表と三行が新しく作られます。DROP TABLEは定義とデータを削除し、通常のROLLBACKでは元に戻りません。行だけ消しても自動採番がリセットされるとは限りません。対象を確認した一表に限定し、データベース全体の削除へ広げないでください。

関連する公式リファレンス

• create table（公式ドキュメント）
• insert（公式ドキュメント）
• select（公式ドキュメント）
• drop table（公式ドキュメント）