MySQLでデータを挿入および削除する方法

はじめに

データの追加と削除は、データベースが保持するデータを制御できる非常に基本的な操作です。挿入するには、新しい行ごとにテーブルの各列の要件を満たす項目を指定します。削除するには、削除したいテーブルの行に対する一致条件を提供します。

この記事では、MySQLテーブルにデータを追加または削除するためにINSERTコマンドとDELETEコマンドを使用する方法について説明します。構文に加え、単一のステートメントで複数の行を操作するような、少し高度なバリエーションについても触れます。

テーブル構造の確認

INSERTコマンドを使用する前に、テーブルの列、データ型、および制約によって課される要件に対応できるように、テーブルの構造を知っておく必要があります。

employeeという名前のテーブルの構造を見つけるには、MySQLのDESCRIBEコマンドを使用できます

DESCRIBE employee;

+-------------+-----------------+------+-----+-------------------+-------------------+
  Field       | Type            | Null | Key | Default           | Extra             |
+-------------+-----------------+------+-----+-------------------+-------------------+
  employee_id | bigint unsigned | NO   | PRI | NULL              | auto_increment    |
  first_name  | varchar(45)     | NO   |     | NULL              |                   |
  last_name   | varchar(45)     | NO   |     | NULL              |                   |
  last_update | timestamp       | NO   |     | CURRENT_TIMESTAMP | DEFAULT_GENERATED |
+-------------+-----------------+------+-----+-------------------+-------------------+
4 rows in set (0.00 sec)

この出力には、テーブルの列名、データ型、デフォルト値などが表示されます。

代替案として、テーブルを再作成するために使用できる情報を表示する方法があります。この情報はSHOW CREATE TABLEコマンドで確認できます。

SHOW CREATE TABLE employee\G

*************************** 1. row ***************************
       Table: employee
Create Table: CREATE TABLE `employee` (
  `employee_id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `first_name` varchar(45) NOT NULL,
  `last_name` varchar(45) NOT NULL,
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY `employee_id` (`employee_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

ここでは、出力を垂直に表示して読みやすくするために\Gターミネータを使用しています。手動で設定されたテーブル作成時のプロパティに加えて、MySQLのデフォルトによって設定された値も表示されます。

これらによって、テーブルの構造をよく理解し、値を正しく挿入できるようになるはずです。

INSERTを使用してテーブルに新しいレコードを追加する

SQLのINSERTコマンドは、既存のテーブルにデータの行を追加するために使用されます。テーブルの構造がわかれば、新しいレコードに挿入したい対応する値とテーブルの列を一致させるコマンドを構築できます。

このコマンドの基本的な構文は次のようになります。

INSERT INTO my_table(column1, column2)
VALUES ('value1', 'value2');

列リストの列は、値リスト内で提供される値に直接対応します。

例えば、上記のemployeeテーブルに新しい従業員を挿入するには、次のように入力できます。

INSERT INTO employee(first_name, last_name)
VALUES ('Bob', 'Smith');

ここでは、first_nameとlast_name列に値を提供し、他の列はデフォルト値で入力されるようにしています。テーブルをクエリすると、新しいレコードが追加されたことがわかります。

SELECT * FROM employee;

+-------------+------------+-----------+---------------------+
  employee_id | first_name | last_name | last_update         |
+-------------+------------+-----------+---------------------+
            1 | Bob        | Smith     | 2021-01-26 09:56:42 |
+-------------+------------+-----------+---------------------+
1 row in set (0.00 sec)

INSERTを使用して一度に複数の行を追加する

レコードを1つずつ挿入するのは、一度に複数の行を挿入するよりも時間がかかり、効率が悪いです。MySQLでは、同じテーブルに追加する複数の行を指定できます。各新しい行は括弧で囲まれ、それぞれの括弧のセットはカンマで区切られます。

複数レコード挿入の基本的な構文は次のようになります。

INSERT INTO my_table(column_name, column_name_2)
VALUES
    ('value', 'value2'),
    ('value3', 'value4'),
    ('value5', 'value6');

参照してきたemployeeテーブルの場合、次のように入力することで、単一のステートメントで4人の新しい従業員を追加できます。

INSERT INTO employee(first_name, last_name)
VALUES
    ('Abigail', 'Spencer'),
    ('Tamal', 'Wayne'),
    ('Katie', 'Singh'),
    ('Felipe', 'Espinosa');

Query OK, 4 rows affected (0.01 sec)
Records: 4  Duplicates: 0  Warnings: 0

DELETEを使用してテーブルから行を削除する

SQLのDELETEコマンドは、テーブルから行を削除するために使用され、INSERTの補完的なアクションとして機能します。テーブルから行を削除するには、WHERE句内に一致条件を提供することで、ターゲットとする行を特定する必要があります。

基本的な構文は次のようになります。

DELETE FROM <table>
WHERE <condition>;

例えば、employeeテーブル内のfirst_nameがAbigailに設定されているすべての行を削除するには、次のように入力できます。

DELETE FROM employee
WHERE first_name = 'Abigail';

Query OK, 1 row affected (0.01 sec)

ここでの戻り値は、DELETEコマンドが処理され、1つの行が削除されたことを示しています。

DELETEを使用して一度に複数の行を削除する

DELETEで一度に複数の項目を削除するには、WHERE句で指定された選択条件を操作します。

例えば、IDで複数の行を削除するには、次のように入力できます。

DELETE FROM employee
WHERE employee_id in (3,4);

Query OK, 2 rows affected (0.00 sec)

さらに、WHERE句を省略して、指定されたテーブルからすべての行を削除することもできます。

DELETE FROM employee;

Query OK, 2 rows affected (0.00 sec)

ただし、DELETEを使用してテーブルのデータを空にするのは、いくつかの注意点があるものの、テーブルをスキャンせずにデータを削除できるTRUNCATEコマンドほど効率的ではない場合があることに注意してください。

結論

この記事では、MySQLテーブルにデータを挿入および削除する方法について説明しました。まず、有効なデータ挿入クエリを構築するのに役立つテーブルの構造を見つける方法について説明しました。次に、INSERTコマンドを使用して、データを1つずつ、およびバッチで挿入しました。最後に、クエリ条件に従ってテーブルからレコードを削除するためのDELETEコマンドについて説明しました。

INSERTコマンドとDELETEコマンドは非常に基本的なものですが、テーブルが実際に保持するデータを管理するための最も有用なコマンドの一部です。これらの基本的な構文と操作を理解することで、データベース構造からレコードを迅速かつ効率的に追加または削除できるようになります。