LabEx
ログイン登録

MySQL テーブル構造とデータ型

MySQLBeginner
オンラインで実践に進む

はじめに

この実験では、MySQL のテーブル構造とデータ型の基本を学びます。データベースを扱う人にとって、テーブルを作成、変更、管理する方法を理解することは不可欠です。MySQL におけるさまざまなデータ型、必要に応じた適切なデータ型の選択方法、および基本的なテーブル操作の方法を学びます。この実験を終えると、テーブルの作成、適切なデータ型で列を定義、テーブル構造の変更に関する実践的な経験を身につけることができます。

目的

この実験を完了することで、以下のことができるようになります。

  • MySQL のコアデータ型とその使用時機を理解する
  • 適切な列定義でテーブルを作成する
  • 既存のテーブル構造を変更する
  • 不要になったテーブルを削除する
  • テーブルのメタデータを表示して理解する

MySQL データ型の理解

このステップでは、最も一般的に使用される MySQL のデータ型を学びます。データ型を理解することは重要です。なぜなら、列に適切なデータ型を選ぶことは、データの整合性とデータベースのパフォーマンスの両方に影響するからです。

このステップでは多くの SQL コマンドが関係するため、ウェブ端末を使用することをお勧めします。「Terminal」タブをクリックして開きます。デスクトップ端末と同じ機能を持っています。

MySQL ウェブ端末インターフェイス

まず MySQL に接続しましょう。

sudo mysql -u root

接続したら、実験用の新しいデータベースを作成しましょう。

CREATE DATABASE store;
USE store;

MySQL のデータ型の主なカテゴリを見てみましょう。

  1. 数値型：

    • INT：整数用
    • DECIMAL：正確な小数用
    • FLOAT/DOUBLE：近似小数用

  2. 文字列型：

    • VARCHAR：可変長文字列用
    • CHAR：固定長文字列用
    • TEXT：長い文章用

  3. 日付と時刻型：

    • DATE：日付用（YYYY-MM-DD）
    • TIME：時刻用（HH:MM:SS）
    • DATETIME：日付と時刻の両方用

これらの異なるデータ型を示す簡単なテーブルを作成しましょう。

CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    price DECIMAL(10,2) NOT NULL,
    description TEXT,
    weight FLOAT,
    in_stock BOOLEAN,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

このテーブル構造を分解してみましょう。

  • id：自動増分の整数で主キー
  • name：NULL にできない可変長文字列
  • price：合計 10 桁で 2 桁の小数部分を持つ正確な小数
  • description：長い説明用のテキストフィールド
  • weight：近似小数値用の浮動小数点数
  • in_stock：ブール型フィールド（TRUE/FALSE）
  • created_at：作成日時を自動的に格納
  • last_updated：レコードが変更されると自動的に更新

テーブルの構造を見るには：

DESCRIBE products;

以下のような出力が表示されるはずです。

+--------------+---------------+------+-----+---------------------+-------------------------------+
| Field        | Type          | Null | Key | Default             | Extra                         |
+--------------+---------------+------+-----+---------------------+-------------------------------+
| id           | int(11)       | NO   | PRI | NULL                | auto_increment                |
| name         | varchar(100)  | NO   |     | NULL                |                               |
| price        | decimal(10,2) | NO   |     | NULL                |                               |
| description  | text          | YES  |     | NULL                |                               |
| weight       | float         | YES  |     | NULL                |                               |
| in_stock     | tinyint(1)    | YES  |     | NULL                |                               |
| created_at   | datetime      | YES  |     | current_timestamp() |                               |
| last_updated | timestamp     | NO   |     | current_timestamp() | on update current_timestamp() |
+--------------+---------------+------+-----+---------------------+-------------------------------+
8 rows in set (0.001 sec)

制約付きでテーブルを作成する

このステップでは、テーブル制約とそれがデータの整合性を維持する方法について学びます。さまざまな種類の制約を示すより複雑なテーブル構造を作成します。

関係と制約を理解するために、2 つの関連するテーブルを作成しましょう。

CREATE TABLE categories (
    category_id INT AUTO_INCREMENT PRIMARY KEY,
    category_name VARCHAR(50) NOT NULL UNIQUE,
    description VARCHAR(200),
    active BOOLEAN DEFAULT TRUE
);

CREATE TABLE inventory_items (
    item_id INT AUTO_INCREMENT PRIMARY KEY,
    category_id INT,
    sku VARCHAR(20) NOT NULL UNIQUE,
    item_name VARCHAR(100) NOT NULL,
    quantity INT NOT NULL CHECK (quantity >= 0),
    unit_price DECIMAL(10,2) NOT NULL,
    FOREIGN KEY (category_id) REFERENCES categories(category_id)
);

使用した制約を見てみましょう。

  1. PRIMARY KEY：各レコードの一意の識別を保証します。
  2. UNIQUE：列内の重複値を防止します。
  3. NOT NULL：列が NULL 値を含まないことを保証します。
  4. CHECK：挿入前にデータを検証します。
  5. FOREIGN KEY：テーブル間の参照整合性を保証します。
  6. DEFAULT：指定がない場合のデフォルト値を提供します。

制約付きのテーブル構造を見るには：

SHOW CREATE TABLE inventory_items;

これにより、すべての制約を含む完全な CREATE TABLE 文が表示されます。

MariaDB [store]> SHOW CREATE TABLE inventory_items;

<!-- サンプル出力 -->

CREATE TABLE `inventory_items` (
  `item_id` int(11) NOT NULL AUTO_INCREMENT,
  `category_id` int(11) DEFAULT NULL,
  `sku` varchar(20) NOT NULL,
  `item_name` varchar(100) NOT NULL,
  `quantity` int(11) NOT NULL CHECK (`quantity` >= 0),
  `unit_price` decimal(10,2) NOT NULL,
  PRIMARY KEY (`item_id`),
  UNIQUE KEY `sku` (`sku`),
  KEY `category_id` (`category_id`),
  CONSTRAINT `inventory_items_ibfk_1` FOREIGN KEY (`category_id`) REFERENCES `categories` (`category_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci

テーブル構造の変更

このステップでは、ALTER TABLE コマンドを使用して既存のテーブルを変更する方法を学びます。データ要件が時間とともに変化する場合、これは一般的なタスクです。

既存の製品テーブルに新しい列を追加し、既存の列を変更しましょう。

  1. 新しい列を追加する：
ALTER TABLE products
ADD COLUMN manufacturer VARCHAR(100) AFTER name;

このコマンドは、name列の後にmanufacturerという名前の新しい列を追加します。

  1. 既存の列を変更する：
ALTER TABLE products
MODIFY COLUMN description VARCHAR(500) NOT NULL DEFAULT 'No description available';

このコマンドは、description列を最大 500 文字の可変長文字列に変更します。また、新しい行に対してデフォルト値'No description available'を設定します。

  1. 列名を変更する：
ALTER TABLE products
CHANGE COLUMN weight product_weight DECIMAL(8,2);

このコマンドは、weight列の名前をproduct_weightに変更し、データ型を合計 8 桁で 2 桁の小数に変更します。

  1. 列を削除する：
ALTER TABLE products
DROP COLUMN in_stock;

このコマンドは、テーブルからin_stock列を削除します。

クエリのパフォーマンスを向上させるために複合インデックスを追加しましょう。

ALTER TABLE products
ADD INDEX idx_name_manufacturer (name, manufacturer);

このコマンドは、name列とmanufacturer列に複合インデックスを作成します。

行った変更をすべて表示するには：

DESCRIBE products;
SHOW INDEX FROM products;

更新されたテーブル構造とインデックスが表示されるはずです。

MariaDB [store]> DESCRIBE products;
+----------------+---------------+------+-----+--------------------------+-------------------------------+
| Field          | Type          | Null | Key | Default                  | Extra                         |
+----------------+---------------+------+-----+--------------------------+-------------------------------+
| id             | int(11)       | NO   | PRI | NULL                     | auto_increment                |
| name           | varchar(100)  | NO   | MUL | NULL                     |                               |
| manufacturer   | varchar(100)  | YES  |     | NULL                     |                               |
| price          | decimal(10,2) | NO   |     | NULL                     |                               |
| description    | varchar(500)  | NO   |     | No description available |                               |
| product_weight | decimal(8,2)  | YES  |     | NULL                     |                               |
| created_at     | datetime      | YES  |     | current_timestamp()      |                               |
| last_updated   | timestamp     | NO   |     | current_timestamp()      | on update current_timestamp() |
+----------------+---------------+------+-----+--------------------------+-------------------------------+
8 rows in set (0.001 sec)

MariaDB [store]> SHOW INDEX FROM products;
+----------+------------+-----------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
| Table    | Non_unique | Key_name              | Seq_in_index | Column_name  | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Ignored |
+----------+------------+-----------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
| products |          0 | PRIMARY               |            1 | id           | A         |           0 |     NULL | NULL   |      | BTREE      |         |               | NO      |
| products |          1 | idx_name_manufacturer |            1 | name         | A         |           0 |     NULL | NULL   |      | BTREE      |         |               | NO      |
| products |          1 | idx_name_manufacturer |            2 | manufacturer | A         |           0 |     NULL | NULL   | YES  | BTREE      |         |               | NO      |
+----------+------------+-----------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
3 rows in set (0.000 sec)

テーブル情報の操作

このステップでは、テーブルとその構造に関する情報を取得するさまざまな方法を学びます。

まず、テーブルのステータス情報を見てみましょう。

SHOW TABLE STATUS FROM store;

このコマンドは、データベース内の各テーブルに関する情報を表示します。これには、以下が含まれます。

  • ストレージエンジン
  • 行形式
  • 行数
  • 平均行長
  • データ長
  • インデックス長

データベース内のすべてのテーブルを表示するには：

SHOW TABLES;

特定の列に関する詳細情報を表示するには：

SHOW FULL COLUMNS FROM products;

これにより、各列に関する追加情報が提供されます。これには、以下が含まれます。

  • 列型
  • 照合順序
  • 権限
  • コメント

テーブルのすべてのインデックスを表示するには：

SHOW INDEX FROM products;

また、INFORMATION_SCHEMA データベースからテーブルに関する情報を取得することもできます。

SELECT
    TABLE_NAME,
    ENGINE,
    TABLE_ROWS,
    AVG_ROW_LENGTH,
    DATA_LENGTH,
    INDEX_LENGTH
FROM
    INFORMATION_SCHEMA.TABLES
WHERE
    TABLE_SCHEMA ='store';

テーブルの削除とクリーンアップ

この最後のステップでは、不要になったテーブルを安全に削除する方法を学びます。これは重要なスキルですが、テーブルを削除するとすべてのデータが永久に削除されるため、注意深く使用する必要があります。

テーブルを削除する前に、以下のことを行うのが良い慣例です。

  1. 正しいデータベースにいることを確認する
  2. テーブルが存在するかどうかを確認する
  3. 必要に応じてバックアップを取る

まず、現在のデータベースとテーブルを確認しましょう。

SELECT DATABASE();
SHOW TABLES;

安全にテーブルを削除するには、IF EXISTS 句を使用できます。

DROP TABLE IF EXISTS inventory_items;

外部キー制約があるテーブルは最初に削除する必要があることに注意してください。最初に categories テーブルを削除しようとすると、inventory_items が参照しているためエラーが発生します。

これで、残りのテーブルを削除できます。

DROP TABLE IF EXISTS categories;
DROP TABLE IF EXISTS products;

テーブルが削除されたことを確認するには：

SHOW TABLES;

空のセットが表示され、すべてのテーブルが削除されたことを示します。

まとめ

この実験では、MySQL テーブルとデータ型を操作するための重要な点を学びました。

  1. さまざまな MySQL データ型の理解と使用
  2. 適切な制約付きでテーブルを作成する
  3. ALTER TABLE を使用してテーブル構造を変更する
  4. テーブルのメタデータと情報を取得する
  5. 不要になったときに安全にテーブルを削除する

これらのスキルは、効果的に MySQL データベースを操作するための基礎を形成します。

関連 MySQL コース
初心者向け MySQL

初心者向け MySQL

databasemysql
高度な MySQL 実践ラボ

高度な MySQL 実践ラボ

databasemysql
MySQL ユーザー権限の管理

MySQL ユーザー権限の管理

databasemysql