SQL入門ガイド: MySQLのINSERT、UPDATE、DELETEの基本から応用まで完全解説

MySQLは人気のあるリレーショナルデータベース管理システムであり、データの操作にはINSERT、UPDATE、DELETE文が使われます。この記事では、MySQLのINSERT、UPDATE、DELETE文について、初心者でも理解しやすいように丁寧に解説します。併せて削除で使用するTRUNCATE文についてもDELETE文との違いも含め、解説しています。
基本的な構文から実際に開発に参加している方が参考になる様な応用的な使用方法まで、幅広くカバーしますので、ぜひ最後までご覧ください。

テーブルの定義と作成

この記事では、以下のテーブルを使用して解説しますので、事前に作成していただくことで、実際に手を動かして学習する事が可能です。

テーブル定義

1. テーブル名: users
   列: id (INT, 主キー), name (VARCHAR), age (INT), email (VARCHAR)
2. テーブル名: orders
   列: id (INT, 主キー), customer_id (INT), product_id (INT), quantity (INT), total_amount (DECIMAL)
3. テーブル名: products
   列: id (INT, 主キー), name (VARCHAR), price (DECIMAL)
4. テーブル名: customers
   列: id (INT, 主キー), name (VARCHAR), age (INT), address (VARCHAR)

-- usersテーブルを作成
CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    age INT,
    email VARCHAR(100)
);

-- ordersテーブルを作成
CREATE TABLE orders (
    id INT PRIMARY KEY,
    customer_id INT,
    product_id INT,
    quantity INT,
    total_amount DECIMAL(10, 2)
);

-- productsテーブルを作成
CREATE TABLE products (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    price DECIMAL(10, 2)
);

-- customersテーブルを作成
CREATE TABLE customers (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  age INT,
  address VARCHAR(100)
);

もしCREATE文の使い方を学習したい場合は、次のページで詳しく解説していますので、併せてご覧ください。

SQL入門ガイド：MySQLのCREATE, ALTER, DROPを網羅的に解説MySQLは非常に人気のあるリレーショナルデータベース管理システムです。データベースの作成や変更、削除など、データベース管理に関連するさまざまな操作を実行するためのSQLコマンドがあります。この記事では、MySQLの「Create, Alter, Drop」ステートメントに焦点を当て、基本構文から応用的な使い方まで網羅的に解説します。また、テーブル、ビュー、トリガー、インデックス、プロシージャ、関数など、さまざまなオブジェクトの作成と変更についても詳しく説明します。...

INSERT文の使い方

INSERT文は、新しいデータをデータベースのテーブルに挿入するために使用されます。

構文

INSERT INTO テーブル名 (列1, 列2, 列3, ...)
VALUES (値1, 値2, 値3, ...);

テーブル名のあとにカッコで、列名を並べます。VALUESのあとのカッコに、登録したい値をカンマで区切って並べます。列名と値は順番を合わせる必要があります。

ーブルに定義された列名の順番に、VALUESのカッコの中に確実にデータを並べることができるのなら、列名は省略可能です。

INSERT INTO テーブル名 VALUES (値1, 値2,...);

INSERT分の使用例

それでは実際に作成したテーブルにデーターを入れてみましょう。

パターン1: テーブルに1行のデータを挿入

INSERT INTO users (name, age, email)
VALUES ('John Doe', 25, 'johndoe@example.com');

この例では、usersテーブルに1行のデータを挿入しています。INSERT文の構文では、INSERT INTO句の後に対象のテーブル名を指定し、カラム名を列挙します。次に、VALUES句を使用して挿入する値を指定します。各値はシングルクォーテーションで囲む必要があります。この例では、’John Doe’という名前のユーザーの年齢が25歳でメールアドレスは’johndoe@example.com’となります。

注意点:

1. 挿入する値の数と順序は、指定したカラムの数と順序と一致している必要があります。
2. 文字列や日付などの値を挿入する際には、シングルクォーテーションで値を囲む必要があります。

パターン2: 複数行のデータを一度に挿入

INSERT INTO users (name, age, email)
VALUES ('Jane Smith', 30, 'janesmith@example.com'),
       ('Bob Johnson', 35, 'bobjohnson@example.com'),
       ('Alice Brown', 28, 'alicebrown@example.com');

この例では、一度に複数行のデータをusersテーブルに挿入しています。VALUES句には複数の値セットをカンマで区切って指定します。各値セットは括弧で囲む必要があります。この例では、3人のユーザーの情報が一度に挿入されます。

注意点:

1. 複数行のデータを挿入する際には、VALUES句内の各値セットの列の数と順序が一致している必要があります。
2. 指定したカラムの数よりも少ない値を挿入する場合、省略されたカラムはNULL値やデフォルト値で埋められます。
3. カラムにデフォルト値が設定されている場合、値を省略してINSERT文を実行することも可能です。

応用パターン: サブクエリを使用して別のテーブルからデータを挿入する

INSERT INTO orders (customer_id, product_id, quantity)
SELECT id, 1, 3
FROM customers
WHERE age >= 30;

この例では、別のテーブルであるcustomersから特定の条件（ageが30以上）を満たす顧客のデータを取得し、ordersテーブルに挿入しています。INSERT文のSELECT句内にサブクエリが使用されており、その結果がINSERTの値として利用されます。customersテーブルからid、1（固定値）、3（固定値）のデータを取得し、ordersテーブルに挿入されます。

この様にINSERT文の列指定と、SELECT句の返す値が対応しているとINSERT可能です。

INSERT文のアンチパターン

先程、「省略形のINSERT 文はシンプルで便利なように見えますが、思わぬトラブルが起きることがあります。」書きました。これはアンチパターンとして有名で、以下の様なトラブルがあります。

-- users_2は例なので、作っていません。
INSERT INTO users_2
SELECT * FROM users

NSERT INTO users_2の次にカッコ（）がありません。SELECT句も＊になっていています。つまりどちらも列名を省略していません。
実は２つのテーブル間の列名の定義がまったく同じなら、列名は省略できるのです。省略しても、内部的に全ての列名を書いたように動作します。

一見シンプルで見通しが良いように見えますが、これがエラーの原因となります。テーブルの列は、システムの仕様変更により追加や削除が行われます。ということは、列名や順番がすべて同じである前提が崩れます。

上記の様な理由から、無用なエラーを防ぐために、アンチパターンとされています。

UPDATE文の使い方

SQLにおけるUPDATE文は、基本的には「どのテーブルの何をどう更新するか」を記述したものです。さらに、UPDATE文にはとてもたくさんのバリエーションがあります！

構文

UPDATE文は、既存のデータを変更するために使用されます。以下は基本的な構文です。

UPDATE テーブル名
SET 列1 = 値1, 列2 = 値2, ...
WHERE 条件;

カラムが複数の場合は、カンマで区切ります。

UPDATE テーブル名 
SET 列1 = 値1, 列2 = 値2 
WHERE 条件;

全データが対象のUPDATEの場合、WHERE句は不要です。全レコードを更新することは稀です。だいたい条件を指定して、それに合うレコードだけ更新します。

UPDATE文の使用例

それではUPDATEを使用してデータを更新してみましょう。INSERTでデータを入れていない方は、事前にデータを入れておいて下さい。

パターン1: 特定の行のデータを更新

UPDATE users
SET age = 26
WHERE name = 'John Doe';

この例では、usersテーブル内のnameが’John Doe’というユーザーのageを26に更新しています。UPDATE文の構文では、UPDATE句の後に対象のテーブル名を指定し、SET句で更新する列と新しい値を指定します。WHERE句を使用して、更新する行の条件を指定します。この例では、nameが’John Doe’である行のageを26に変更しています。

注意点:

1. UPDATE文を実行する前に、更新する対象の行を正確に特定するために適切な条件を使用する必要があります。
2. WHERE句を省略すると、全ての行が更新される可能性があるので注意が必要です。
3. 主キーやINDEXをUPDATEするのは注意しないと、テーブルの整合性が壊れる可能性があります。

パターン2: 複数の列を更新する例

UPDATE users
SET age = 40, email = 'newemail@example.com'
WHERE name = 'Jane Smith';

この例では、nameが’Jane Smith’というユーザーのageを40に、emailを’newemail@example.com’に更新しています。SET句で複数の列とそれぞれの新しい値を指定します。WHERE句によって、更新する行を特定しています。

注意点:

1. 複数の列を更新する場合、SET句内で各列と新しい値をカンマで区切って指定します。
2. SET句で指定した値が適用されるのは、WHERE句で指定した条件に合致する行のみです。

応用パターン1: サブクエリを使用して条件に基づいてデータを更新

UPDATE products
SET price = price * 0.9
WHERE id IN (
    SELECT product_id
    FROM orders
    WHERE total_amount > 1000
);

この例では、ordersテーブル内でtotal_amountが1000を超える注文のproduct_idを取得し、それに基づいてproductsテーブルの価格を更新しています。UPDATE文のWHERE句内にサブクエリが使用されており、サブクエリの結果であるproduct_idがIN演算子によって結果に含まれる行が更新されます。productsテーブル内の該当する商品の価格が現在の価格の90%に更新されます。

応用パターン2: JOINを使用し条件に基づいてデータを更新

UPDATE users
INNER JOIN orders ON users.id = orders.user_id
SET users.status = 'VIP'
WHERE orders.total_amount > 10000;

この例では、usersテーブルとordersテーブルをINNER JOINして、total_amountが10000を超える注文を行ったユーザーのステータスを’VIP’に更新しています。UPDATE文のSET句内でusersテーブルのstatus列を更新しています。JOIN条件としてusersテーブルのid列とordersテーブルのuser_id列を比較して結合しています。

WHEREでも同様の事ができますので、可読性など考慮して使用して下さい。

応用パターン3: NULLを置き換えて更新

UPDATE文やDELETE文において、NULL値だけを対象にする方法としては、IS NULL演算子やIS NOT NULL演算子を使用することができます。また、NULL値を空文字に置き換えて更新する場合は、COALESCE関数やIFNULL関数を利用する方法があります。以下にそれぞれの方法について詳しく説明します。

IS NULL演算子とIS NOT NULL演算子を使用する方法

IS NULL演算子は、特定の列がNULL値を持つ行を選択するために使用されます。IS NOT NULL演算子は、特定の列がNULL値ではない行を選択するために使用されます。

例えば、usersテーブルのname列がNULLの行を選択する場合は、次のようにします。

SELECT * FROM users WHERE name IS NULL;

また、name列がNULLでない行を選択する場合は、次のようにします。

SELECT * FROM users WHERE name IS NOT NULL;

このように、IS NULL演算子とIS NOT NULL演算子を組み合わせることで、NULL値を対象にした更新や削除の条件を指定することができます。

上記NULLの抽出方法を利用し、次の様に書くことで更新ができます。

UPDATE users SET name = COALESCE(name, '') WHERE name IS NULL;

UPDATE users 
SET name = IFNULL(name, '') 
WHERE name IS NULL;

DELETE文の使い方

テーブルからデータを削除するときは、DELETE文を使用します。
DELETE文を使用する際には、DELETE FROMの後ろに削除対象のレコードが格納されている表名を記載します。 削除対象のレコードの条件はWHEREに記載します。 DELETE文ではWHERE条件に合致するレコードすべてが削除されます。

構文

DELETE FROM テーブル名
WHERE 条件;

WHEREで抽出されたデータを削除します。WHEREを忘れると全データが削除されますので、ご注意ください。（全データ削除は後述するTruncateを使用する事が殆どです。）

DELETE文の使用例

それではDELETEを使用してデータを削除してみましょう。INSERTでデータを入れていない方は、事前にデータを入れておいて下さい。

パターン1: 特定の行を削除

DELETE FROM users
WHERE name = 'Bob Johnson';

この例では、usersテーブルからnameが’Bob Johnson’というユーザーの行を削除しています。DELETE文の構文では、DELETE FROM句の後に対象のテーブル名を指定し、WHERE句で削除する行の条件を指定します。この例では、nameが’Bob Johnson’である行が削除されます。

注意点:

1. DELETE文を実行する前に、削除する対象の行を正確に特定するために適切な条件を使用する必要があります。
2. WHERE句を省略すると、テーブル内の全ての行が削除される可能性があるので、慎重に使用してください。

パターン2: 全ての行を削除

DELETE FROM users;

この例では、usersテーブル内の全ての行が削除されます。DELETE文の構文では、DELETE FROM句の後に対象のテーブル名を指定するだけで、WHERE句を使用せずに実行することができます。

注意点:

1. 全ての行を削除する操作は非常に破壊的なため、慎重に使用してください。データのバックアップや確認を十分に行ってから実行することをおすすめします。

応用パターン1: DELETE文とJOINを組み合わせて関連するデータを削除

DELETE users, orders
FROM users
JOIN orders ON users.id = orders.user_id
WHERE users.status = 'Inactive';

この例では、usersテーブルとordersテーブルをJOINして、ステータスが’Inactive’であるユーザーと関連する注文データを同時に削除しています。DELETE文のFROM句で複数のテーブルを指定し、JOIN条件としてusersテーブルのid列とordersテーブルのuser_id列を比較して結合しています。削除するデータがusersテーブルとordersテーブルの両方に存在するため、両方のテーブルからデータが削除されます。

TRUNCATE文の使い方

TRUNCATE文は、テーブル内の全データを削除するために使用されるSQL文です。TRUNCATE文を実行すると、テーブルのデータは完全に削除されますが、テーブル自体の構造や定義は保持されます。以下にTRUNCATE文の使用方法と注意点を説明します。

構文

TRUNCATE TABLE table_name;

table_nameは削除したいテーブルの名前です。

TRUNCATE文の注意点:

1. TRUNCATE文を実行すると、テーブル内の全データが一括して削除されます。データは元に戻せないので、削除する前に注意が必要です。
2. TRUNCATE文は、テーブルのデータを削除するだけであり、テーブルの構造や定義は保持されます。つまり、主キーやインデックス、制約などの設定は削除されません。
3. TRUNCATE文は、通常のDELETE文と比べて効率的にデータを削除できる場合があります。DELETE文は削除される行ごとにトランザクションログに記録されるのに対し、TRUNCATE文はログを記録せずにデータを削除します。
4. TRUNCATE文は、削除操作が即座に実行されるため、トリガーや削除に関連する他の処理が実行されません。DELETE文とは異なり、トリガーが発火しないことに注意してください。

TRUNCATE文とDELETE文の違い

TRUNCATE文とDELETE文は、どちらもデータベースのテーブルからデータを削除するためのSQL文ですが、いくつかの重要な違いがあります。

1. 削除の仕組み:
   • TRUNCATE文: TRUNCATE文は、テーブル内のデータを一括して削除します。TRUNCATE文はデータを物理的に削除するため、トランザクションログに記録された情報を使わずに高速にデータを削除します。
   • DELETE文: DELETE文は、テーブル内のデータを行単位で削除します。DELETE文はトランザクションログに削除された行の情報を記録するため、ロールバックやリカバリのための情報を提供します。
2. テーブルの構造や定義:
   • TRUNCATE文: TRUNCATE文はテーブルのデータを削除しますが、テーブル自体の構造や定義は保持されます。つまり、主キー、インデックス、制約などの設定は削除されません。
   • DELETE文: DELETE文はテーブルのデータを削除しますが、テーブルの構造や定義は保持されます。削除操作によってテーブルの構造が変わることはありません。
3. トリガーの動作:
   • TRUNCATE文: TRUNCATE文を使用してテーブルのデータを削除すると、トリガーは発火しません。TRUNCATE文は一括でデータを削除するため、トリガーが発生することはありません。
   • DELETE文: DELETE文を使用してテーブルのデータを削除すると、トリガーが発火する場合があります。DELETE文は行単位でデータを削除するため、トリガーが関連する操作を実行することがあります。
4. ロールバックの可能性:
   • TRUNCATE文: TRUNCATE文はデータの物理的な削除を行うため、トランザクション内でTRUNCATE文を実行してもロールバックすることはできません。TRUNCATE文が実行されると、データは完全に削除されます。
   • DELETE文: DELETE文はトランザクションログに削除された行の情報が保存されるため、ロールバック操作によって削除操作を元に戻すことができます。

要点をまとめると、TRUNCATE文はデータを一括して高速に削除するための方法であり、テーブルの構造や定義は保持されますが、トリガーが発火せず、ロールバックもできません。一方、DELETE文は行単位でデータを削除し、トリガーが発火する場合があり、ロールバックによって削除操作を元に戻すことができます。

使用する場合は、以下のようなガイドラインに従って選択してください：

1. テーブルのデータを完全に削除したい場合や高速な削除が必要な場合は、TRUNCATE文を使用します。
2. 削除操作によってトリガーや関連する処理を実行したい場合や、削除操作をロールバックできる必要がある場合は、DELETE文を使用します。

ただし、どちらの方法を使用する場合でも、データの削除には注意が必要です。データのバックアップや削除操作の確認などを十分に行ってください。

TRUNCATE文の使用例

TRUNCATE TABLE customers;

上記の例では、customersテーブルのデータを全て削除しています。TRUNCATE文を実行すると、テーブル内のデータがすべてなくなります。

TRUNCATE文を使用する際は、データのバックアップや削除操作の確認など、注意深く行ってください。

まとめ

この記事では、MySQLのINSERT、UPDATE、DELETE文、TRUNCATE文について、初心者の方が次のステップに進めるように解説しました。基本的な構文から始め、サブクエリやJOINなどの高度な使用方法についても解説しました。これらの知識を身につけることで、データベースの操作においてより自信を持てるようになるでしょう。練習を重ねながら、MySQLのINSERT、UPDATE、DELETE文をマスターしてください。

スポンサーリンク

スポンサーリンク