MySQL でデータベースとテーブルを作成・削除する方法

はじめに

MySQL は、管理するデータをテーブルとデータベース内に保持します。テーブルは、カラムとデータ型の事前定義されたスキーマ内にデータを格納します。データベースは、複数のテーブル、インデックス、プロシージャを保持し、特権やカスタマイズなどのスコープを定義するのに役立つ構造です。

このガイドでは、MySQL でテーブルとデータベースを作成する方法を説明します。両方の構造のいくつかの機能を管理する方法を示し、不要になった場合にそれらを削除してクリーンアップする方法を説明します。

前提条件

このガイドに沿って進めるには、mysql コマンドラインクライアントを使用して、管理権限を持つユーザーで MySQL サーバーにログインする必要があります。

root MySQL 管理アカウントでログインしたくない場合は、このガイドに記載されているアクションを実行するために、少なくともCREATE 権限が必要です。

ユーザーが利用可能な権限は、以下を入力することで確認できます。

SHOW GRANTS\G

新しいデータベースを作成するには？

まず、新しいデータベースの作成方法を説明します。MySQL では、データベースには通常、単一のプロジェクトに関連するテーブル、インデックス、プロシージャ、およびその他のリソースが含まれます。これらのリソースはすべてデータベース内に作成する必要があるため、新しいデータベースの作成方法を学ぶことは良い第一歩となります。

新しいデータベースを作成するための基本的な構文は次のとおりです。

CREATE DATABASE <db_name>;

コマンドを実行する前に、<db_name> をデータベースに付けたい名前に置き換えてください。このコマンドは、指定された名前でデータベースを作成し、現在のユーザーを新しいデータベースの所有者として設定します。

データベースは、文字セット、照合順序、および暗号化プロパティにシステム全体でのデフォルト値を使用します。

• CHARACTER SET: 新しいデータベースの文字セットを設定します。これは、どの文字が大文字、小文字、数字と見なされるかに影響するローカライズオプションです。
• COLLATE: データベースの照合順序、つまりソート順を設定します。これは、アイテムが順序付けられたときにどのように整理されるかを決定するローカライズオプションです。
• ENCRYPTION: 新しいデータベースの暗号化を設定します。これはブールオプションで、はいの場合は 'Y'、いいえの場合は 'N' です。

インストールで利用可能な文字セットは、以下を入力することで確認できます。

SHOW CHARACTER SET;

同様に、利用可能な照合順序は、以下を入力することで確認できます。

SHOW COLLATION;

これらのパラメータのいずれかにデフォルト以外の値を指定したい場合は、メインの作成ステートメントの後に次のように追加することで指定できます。

CREATE DATABASE <db_name>
  CHARACTER SET = 'utf8mb4'
  COLLATE = 'utf8mb4_0900_ai_ci'
  ENCRYPTION = 'Y';

このガイドの例に沿って進めるために、インスタンスのデフォルトロケール設定と UTF8 文字セットを使用して、school という名前のデータベースを作成します。

CREATE DATABASE school CHARACTER SET = 'utf8mb4';

これにより、指定した仕様を使用して新しいデータベースが作成されます。

既存のデータベースを一覧表示する

サーバーで利用可能なデータベースを一覧表示するには、以下を入力します。

SHOW DATABASES;

これにより、環境内で現在定義されている各データベースが一覧表示されます。

+--------------------+
  Database           |
+--------------------+
  information_schema |
  mysql              |
  performance_schema |
  school             |
  sys                |
  testing            |
+--------------------+
6 rows in set (0.00 sec)

指定した設定が新しい school データベースに適用されたことを確認するには、SHOW CREATE DATABASE コマンドを使用します。

SHOW CREATE DATABASE school;

出力には、データベースの作成に使用されたコマンドとオプションが表示され、適用されたデフォルト値でオプションが埋められます。

+----------+----------------------------------------------------------------------------------------------------------------------------------+
  Database | Create Database                                                                                                                  |
+----------+----------------------------------------------------------------------------------------------------------------------------------+
  school   | CREATE DATABASE `school` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */ |
+----------+----------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

MySQL で別のデータベースに切り替える方法

データベースが作成されたら、いくつかの異なる方法で作業できます。

最初のオプションは、使用するすべてのコマンドでデータベースを引数として指定することです。ただし、データベースのオブジェクトに関連する複数のコマンドを実行する場合は、時間を節約するために、データベースを「切り替え」たり「使用」したりして、実行するデータベース関連コマンドのデフォルトコンテキストとして新しいデータベースを設定することもできます。

別のデータベースに切り替えるには、以下を入力します。

USE <db_name>;

私たちの場合、作成した school データベースに、以下を入力することで切り替えることができます。

USE school;

データベース内にテーブルを作成する

データベースを作成したら、その中にテーブルやその他のオブジェクトを定義してデータを保存・管理できます。MySQL では、テーブルはテーブル名、カラム定義（これには名前、データ型、制約などが含まれます）、およびテーブルオプションで構成されます。

CREATE TABLE <table_name> (
    <column_name> <data_type> [<column_constraint>],
    [<table_constraint>,]
);

CREATE TABLE IF NOT EXISTS table_name (
    column_name TYPE [column_constraint],
    [table_constraint,]
);

CREATE TABLE supplies (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(255),
  description VARCHAR(255),
  manufacturer VARCHAR(255),
  color VARCHAR(255),
  inventory INT CHECK (inventory >= 0)
);

SHOW TABLES;

+------------------+
  Tables_in_school |
+------------------+
  supplies         |
+------------------+
1 row in set (0.01 sec)

SHOW CREATE TABLE supplies\G

*************************** 1. row ***************************
       Table: supplies
Create Table: CREATE TABLE `supplies` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `description` varchar(255) DEFAULT NULL,
  `manufacturer` varchar(255) DEFAULT NULL,
  `color` varchar(255) DEFAULT NULL,
  `inventory` int DEFAULT NULL,
  PRIMARY KEY (`id`),
  CONSTRAINT `supplies_chk_1` CHECK ((`inventory` >= 0))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec

CREATE TABLE teachers (
  id INT AUTO_INCREMENT PRIMARY KEY,
  first_name VARCHAR(255),
  last_name VARCHAR(255),
  subject VARCHAR(255),
  grade_level INT
);

MySQL でテーブルを表示する方法

MySQL では、探している情報に応じて、いくつかの異なる方法でテーブルを一覧表示できます。

データベース内で利用可能なテーブルを確認したい場合は、以下を入力します。

SHOW TABLES;

+------------------+
  Tables_in_school |
+------------------+
  supplies         |
  teachers         |
+------------------+
2 rows in set (0.00 sec)

テーブルのスキーマが指定と一致することも確認できます。

DESCRIBE teachers;

+--------------+--------------+------+-----+---------+----------------+
  Field        | Type         | Null | Key | Default | Extra          |
+--------------+--------------+------+-----+---------+----------------+
  id           | int          | NO   | PRI | NULL    | auto_increment |
  name         | varchar(255) | YES  |     | NULL    |                |
  description  | varchar(255) | YES  |     | NULL    |                |
  manufacturer | varchar(255) | YES  |     | NULL    |                |
  color        | varchar(255) | YES  |     | NULL    |                |
  inventory    | int          | YES  |     | NULL    |                |
+--------------+--------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)

teachers テーブルは私たちの定義と一致しているようです。

ALTER TABLE コマンドでテーブルを変更する方法

MySQL で既存のテーブルのスキーマを変更する必要がある場合は、ALTER TABLE コマンドを使用できます。ALTER TABLE コマンドは CREATE TABLE コマンドと非常によく似ていますが、既存のテーブルに対して操作を行います。

ALTER TABLE <table_name> <change_command> <change_parameters>;

ALTER TABLE some_table ADD missing_column text;

ALTER TABLE some_table DROP COLUMN useless_column;

ALTER TABLE resident MODIFY COLUMN id INT;

テーブルを削除する

テーブルを削除したい場合は、DROP TABLE SQL ステートメントを使用できます。これにより、テーブルとその中に保存されているすべてのデータが削除されます。

基本的な構文は次のとおりです。

DROP TABLE <table_name>;

これにより、テーブルが存在すれば削除され、テーブル名が存在しない場合はエラーがスローされます。

テーブルが存在すれば削除し、存在しない場合は何もしないようにしたい場合は、ステートメントに IF EXISTS 修飾子を含めることができます。

DROP TABLE IF EXISTS <table_name>;

他のテーブルやオブジェクトに依存関係を持つテーブルは、それらの依存関係が存在する間はデフォルトで削除できません。

以前に作成した supplies テーブルを、以下を入力して削除します。

DROP TABLE supplies;

データベースを削除するステートメントがテーブルのようなすべての子オブジェクトも削除することを示すために、teachers テーブルは保持します。

データベースを削除する

DROP DATABASE ステートメントは、MySQL に指定されたデータベースを削除するように指示します。基本的な構文は次のとおりです。

DROP DATABASE <database_name>;

<database_name> プレースホルダーを、削除したいデータベースの名前に置き換えてください。これにより、データベースが見つかった場合は削除されます。データベースが見つからない場合はエラーが発生します。

DROP DATABASE some_database;

ERROR 1008 (HY000): Can't drop database 'some_database'; database doesn't exist

データベースが存在すれば削除し、それ以外の場合は何もしないようにしたい場合は、オプションの IF EXISTS オプションを含めます。

DROP DATABASE IF EXISTS some_database;

Query OK, 0 rows affected, 1 warning (0.00 sec)

これにより、データベースが削除されるか、見つからない場合は何も行われません。

このガイドで使用した school データベースを削除するには、システム上の既存のデータベースを一覧表示します。

SHOW DATABASES;

+--------------------+
  Database           |
+--------------------+
  information_schema |
  mysql              |
  performance_schema |
  school             |
  sys                |
  testing            |
+--------------------+
6 rows in set (0.00 sec)

以下のコマンドで school データベースを削除します。

DROP DATABASE school;

これにより、school データベースが、その中に定義された teachers テーブルとともに削除されます。

まとめ

この記事では、MySQL でデータベースとテーブルを作成・削除する基本的な方法について説明しました。これらは、データベースシステムをセットアップし、データの構造を定義するために必要な最も基本的なコマンドの一部です。

前述のとおり、この MySQL チュートリアルで説明した SQL ステートメント、特に CREATE TABLE ステートメントには、システムの動作を変更するために使用できる多くの追加パラメータがあります。これらの詳細については、MySQL の公式ドキュメントを確認してください。

よくある質問

ALTER TABLE <table_name> <change_command> <change_parameters>;

ALTER TABLE some_table ADD missing_column text;

DROP TABLE IF EXISTS <table_name>;

DROP INDEX <index_name> ON <table_name>;

CREATE TEMPORARY TABLE table_name(
   column_1_definition,
   column_2_definition,
   ...,
   table_constraints
);