SQLでデータを削除する方法

序章

構造化照会言語（より一般的には SQL として知られています）では、DELETEステートメントはユーザーが利用できる最も強力な操作の1つです。 名前が示すように、DELETE操作は、データベーステーブルから1行以上のデータを不可逆的に削除します。 データ管理のこのような基本的な側面であるため、SQLユーザーはDELETEステートメントがどのように機能するかを理解することが重要です。

このガイドでは、SQLのDELETE構文を使用して1つ以上のテーブルからデータを削除する方法について説明します。 また、SQLが外部キー制約と競合するDELETE操作を処理する方法についても説明します。

前提条件

このガイドに従うには、SQLを使用するある種のリレーショナルデータベース管理システム（RDBMS）を実行しているコンピューターが必要です。 このガイドの手順と例は、次の環境を使用して検証されています。

• Ubuntu 20.04 の初期サーバーセットアップガイドで説明されているように、管理者権限を持つ非rootユーザーとUFWで構成されたファイアウォールを備えたUbuntu20.04を実行しているサーバー。
• Ubuntu 20.04にMySQLをインストールする方法で概説されているように、MySQLがサーバーにインストールされて保護されています。 このガイドは、ステップ3で説明されているプロセスを使用して作成された非ルートMySQLユーザーで検証されました。

また、データの削除を練習するために使用できるサンプルデータがロードされたデータベースとテーブルも必要です。 このガイドで例全体で使用するデータベースと2つのテーブルを作成する方法の詳細については、次のMySQLへの接続とサンプルデータベースの設定セクションをお読みになることをお勧めします。

このページに埋め込まれているインタラクティブ端末を使用して、このチュートリアルのサンプルクエリを試すこともできます。 次のLaunch an Interactive Terminal!ボタンをクリックして開始します。

インタラクティブターミナルを起動します！

MySQLへの接続とサンプルデータベースの設定

SQLデータベースシステムがリモートサーバーで実行されている場合は、ローカルマシンからサーバーにSSHで接続します。

ssh sammy@your_server_ip

次に、MySQLサーバープロンプトを開き、sammyをMySQLユーザーアカウントの名前に置き換えます。 このページに埋め込まれたインタラクティブ端末を使用している場合、プロンプトが表示されたときに使用するパスワードはsecretという単語であることに注意してください。

mysql -u sammy -p

deleteDBという名前のデータベースを作成します。

CREATE DATABASE deleteDB;

データベースが正常に作成されると、次のような出力が表示されます。

Output
Query OK, 1 row affected (0.01 sec)

deleteDBデータベースを選択するには、次のUSEステートメントを実行します。

USE deleteDB;

Output
Database changed

deleteDBデータベースを選択した後、そのデータベース内にいくつかのテーブルを作成します。 例として、あなたとあなたの友人の何人かが、メンバーが互いに音楽機器を共有できるクラブを始めたと想像してください。 クラブ会員とその備品を追跡しやすくするために、いくつかのテーブルを作成することにしました。 最初のテーブルには、次の4つの列があります。

• memberID：intデータ型で表される各クラブ会員の識別番号。 この列は、テーブルの主キーとしても機能します
• name：各メンバーの名前。最大30文字のvarcharデータ型を使用して表されます。
• homeBorough：この列には、各メンバーが住む自治区が格納されます。これもvarcharデータ型を使用して表されますが、最大15文字です。
• email：各メンバーに連絡できるメールアドレス。最大30文字のvarcharデータ型を使用して表現されます。

次の4つの列を持つclubMembersという名前のテーブルを作成します。

CREATE TABLE clubMembers (
memberID int PRIMARY KEY,
name varchar(30),
homeBorough varchar(15),
email varchar(30)
);

次の表には、次の列があります。

• equipmentID：各機器の一意の識別子。 この列の値は、intデータ型になります。 clubMembersテーブルのmemberID列と同様に、この列はテーブルの主キーとして機能します
• equipmentType：各行が表す機器またはツールのタイプ（guitar、mixer、amplifierなど）。 これらの値は、最大30文字のvarcharデータ型を使用して表されます。
• brand：最大30文字のvarcharデータ型を使用して表現された、各機器を製造したブランド
• ownerID：この列には、機器を所有するクラブメンバーのID番号が整数で表示されます。

ownerID列が有効なメンバーID番号を表す値のみを保持するようにするために、clubMemberテーブルのmemberID列を参照する外部キー制約を作成できます。 外部キー制約は、2つのテーブル間の関係を表現する方法です。 外部キーは、それが適用される列の値が、それが参照する列にすでに存在している必要があることを要求することによってこれを行います。 次の例では、外部キー制約により、ownerID列に追加された値がmemberID列にすでに存在している必要があります。

これらの列とclubEquipmentという名前のこの制約を持つテーブルを作成します。

CREATE TABLE clubEquipment (
equipmentID int PRIMARY KEY,
equipmentType varchar(30),
brand varchar(15),
ownerID int,
CONSTRAINT fk_ownerID
FOREIGN KEY (ownerID) REFERENCES clubMembers(memberID)
);

この例では、外部キー制約の名前fk_ownerIDが提供されていることに注意してください。 MySQLは、追加した制約の名前を自動的に生成しますが、後でこの制約を参照する必要がある場合は、ここで名前を定義すると便利です。

次に、次のINSERT INTOステートメントを実行して、6行のサンプルデータを含むclubMembersテーブルをロードします。

INSERT INTO clubMembers
VALUES
(1, 'Rosetta', 'Manhattan', '[email protected]'),
(2, 'Linda', 'Staten Island', '[email protected]'),
(3, 'Labi', 'Brooklyn', '[email protected]'),
(4, 'Bettye', 'Queens', '[email protected]'),
(5, 'Phoebe', 'Bronx', '[email protected]'),
(6, 'Mariya', 'Brooklyn', '[email protected]');

次に、別のINSERT INTOステートメントを実行して、clubEquipmentテーブルに20行のサンプルデータをロードします。

INSERT INTO clubEquipment
VALUES
(1, 'electric guitar', 'Gilled', 6),
(2, 'trumpet', 'Yemehe', 5),
(3, 'drum kit', 'Purl', 3),
(4, 'mixer', 'Bearinger', 3),
(5, 'microphone', 'Sure', 1),
(6, 'bass guitar', 'Fandar', 4),
(7, 'acoustic guitar', 'Marten', 6),
(8, 'synthesizer', 'Korgi', 4),
(9, 'guitar amplifier', 'Vax', 4),
(10, 'keytar', 'Poland', 3),
(11, 'acoustic/electric bass', 'Pepiphone', 2),
(12, 'trombone', 'Cann', 2),
(13, 'mandolin', 'Rouge', 1),
(14, 'electric guitar', 'Vax', 6),
(15, 'accordion', 'Nonher', 5),
(16, 'electric organ', 'Spammond', 1),
(17, 'bass guitar', 'Peabey', 1),
(18, 'guitar amplifier', 'Fandar', 3),
(19, 'cello', 'Yemehe', 2),
(20, 'PA system', 'Mockville', 5);

これで、ガイドの残りの部分に従い、SQLを使用してデータを削除する方法について学習する準備が整いました。

単一のテーブルからのデータの削除

SQLでデータを削除するための一般的な構文は、次のようになります。

DELETE FROM table_name
WHERE conditions_apply;

DELETE操作が成功すると元に戻せないことに注意してください。 削除するデータを正確に知らずに実行すると、誤って間違ったレコードを削除する可能性があります。 誤って間違ったデータを削除しないようにする1つの方法は、最初にSELECTクエリを発行して、DELETE操作のWHERE句によって返されるデータを確認することです。 。

たとえば、Korgiというブランドの音楽機器に関連するレコードをすべて削除したいとします。 ただし、安全のために、最初にクエリを記述して、brand列にKorgiと表示されている機器レコードを正確に確認することにします。

テーブル内のどの楽器がコルグによって製造されているかを見つけるには、次のクエリを実行できます。 SELECTクエリやINSERT INTO操作とは異なり、DELETE操作では、データの行全体を削除することを目的としているため、個々の列を指定できないことに注意してください。 この動作を模倣するために、このクエリはSELECTキーワードの後にアスタリスク（*）を付けます。これはSQLの省略形であり、「すべての列」を表します。

SELECT * FROM clubEquipment
WHERE brand = 'Korgi';

このクエリは、clubEquipmentテーブルからすべての列を返しますが、brand列に値Korgiが含まれている行のみを返します。

Output
+-------------+---------------+-------+---------+
| equipmentID | equipmentType | brand | ownerID |
+-------------+---------------+-------+---------+
|           8 | synthesizer   | Korgi |       4 |
+-------------+---------------+-------+---------+
1 row in set (0.00 sec)

この行を削除するには、前のSELECTステートメントと同じFROMおよびWHERE句を持つDELETE操作を実行します。

DELETE FROM clubEquipment
WHERE brand = 'Korgi';

Output
Query OK, 1 row affected (0.01 sec)

この出力は、DELETE操作が単一の行にのみ影響したことを示しています。 ただし、複数の行を返すWHERE句を使用すると、複数行のデータを削除できます。

次のSELECTクエリは、equipmentType列にelectricという単語が含まれているclubEquipmentテーブルのすべてのレコードを返します。

SELECT * FROM clubEquipment
WHERE equipmentType LIKE '%electric%';

Output
+-------------+------------------------+-----------+---------+
| equipmentID | equipmentType          | brand     | ownerID |
+-------------+------------------------+-----------+---------+
|           1 | electric guitar        | Gilled    |       6 |
|          11 | acoustic/electric bass | Pepiphone |       2 |
|          14 | electric guitar        | Vax       |       6 |
|          16 | electric organ         | Spammond  |       1 |
+-------------+------------------------+-----------+---------+
4 rows in set (0.00 sec)

繰り返しますが、これら4つのレコードを削除するには、このクエリ操作を書き直しますが、SELECT *をDELETEに置き換えます。

DELETE FROM clubEquipment
WHERE equipmentType LIKE '%electric%';

Output
Query OK, 4 rows affected (0.00 sec)

subqueries を使用して、より詳細な結果セットを返したり削除したりすることもできます。 サブクエリは完全なクエリ操作です。つまり、SELECTで始まり、FROM句を含むSQLステートメント）は、周囲の操作自体のFROM句に続いて、別の操作に埋め込まれます。 。

たとえば、名前が「L」で始まるメンバーが所有するclubEquipmentテーブルにリストされている機器を削除したいとします。 最初に、次のようなステートメントを使用してこのデータをクエリできます。

SELECT *
FROM clubEquipment
WHERE ownerID IN
(SELECT memberID FROM clubMembers
WHERE name LIKE 'L%');

この操作は、clubEquipmentテーブルからすべての行を返します。このテーブルのownerID列は、4行目から始まるサブクエリによって返される値に表示されます。 このサブクエリは、「L」で始まるmemberID of any record whosename`値を返します。

Output
+-------------+------------------+-----------+---------+
| equipmentID | equipmentType    | brand     | ownerID |
+-------------+------------------+-----------+---------+
|          12 | trombone         | Cann      |       2 |
|          19 | cello            | Yemehe    |       2 |
|           3 | drum kit         | Purl      |       3 |
|           4 | mixer            | Bearinger |       3 |
|          10 | keytar           | Poland    |       3 |
|          18 | guitar amplifier | Fandar    |       3 |
+-------------+------------------+-----------+---------+
6 rows in set (0.00 sec)

次に、次のDELETEステートメントを使用して、このデータを削除できます。

DELETE FROM clubEquipment
WHERE ownerID IN
(SELECT memberID FROM clubMembers
WHERE name LIKE 'L%');

Output
Query OK, 6 rows affected (0.01 sec)

複数のテーブルからのデータの削除

JOIN句を含めることにより、1回の操作で複数のテーブルからデータを削除できます。

JOIN句は、2つ以上のテーブルの行を1つのクエリ結果に結合するために使用されます。 これを行うには、テーブル間で関連する列を見つけ、出力で結果を適切に並べ替えます。

JOIN句を含むDELETE操作の構文は次のようになります。

DELETE table_1, table_2
FROM table_1 JOIN table_2
ON table_2.related_column = table_1.related_column
WHERE conditions_apply;

JOIN句は複数のテーブルの内容を比較するため、この構文例では、列の名前の前にテーブルの名前とピリオドを付けて、各列を選択するテーブルを指定します。 これは、完全修飾列参照として知られています。 前の例で行ったように単一のテーブルからのみ選択する場合は必要ありませんが、どの操作でもこのように列を選択するテーブルを指定できます。

JOIN句を使用してデータを削除することを説明するために、クラブが音楽機器のメンバーが共有できるブランドを制限することを決定したとします。 次のステートメントを実行して、prohibitedBrandsという名前のテーブルを作成します。このテーブルには、クラブで受け入れられなくなったブランドを一覧表示します。 このテーブルには2つの列しかなく、どちらもvarcharデータ型を使用して、各ブランドの名前とブランドが運営されている国を保持しています。

CREATE TABLE prohibitedBrands (
brandName varchar(30),
homeCountry varchar(30)
);

次に、この新しいテーブルにいくつかのサンプルデータをロードします。

INSERT INTO prohibitedBrands
VALUES 
('Fandar', 'USA'),
('Givson', 'USA'),
('Muug', 'USA'),
('Peabey', 'USA'),
('Yemehe', 'Japan');

その後、クラブは、ブランドがprohibitedBrandsテーブルに表示され、米国を拠点とするclubEquipmentテーブルから機器のレコードを削除することを決定します。

次のSELECTステートメントのような操作で、このデータを照会できます。 この操作は、clubEquipmentテーブルとprohibitedBrandsテーブルを結合し、brand列とbrandName列が共通の値を共有する行のみを返します。 WHERE句は、homeCountry列にUSAが値として含まれていないブランドを除外することにより、この結果セットをさらに改良します。

SELECT * 
FROM clubEquipment JOIN prohibitedBrands
ON clubEquipment.brand = prohibitedBrands.brandName
WHERE homeCountry = 'USA';

Output
+-------------+---------------+--------+---------+-----------+-------------+
| equipmentID | equipmentType | brand  | ownerID | brandName | homeCountry |
+-------------+---------------+--------+---------+-----------+-------------+
|           6 | bass guitar   | Fandar |       4 | Fandar    | USA         |
|          17 | bass guitar   | Peabey |       1 | Peabey    | USA         |
+-------------+---------------+--------+---------+-----------+-------------+
2 rows in set (0.00 sec)

これが私たちが探しているすべての情報です。 つまり、clubEquipmentテーブルにも表示されるprohibitedBrandsテーブルの各USAベースのブランド。

これらのブランドをprohbitedBrandsテーブルから削除し、関連する機器をclubEquipmentから削除するには、前のSELECTステートメントを書き直しますが、SELECT *をDELETEに置き換えます。両方のテーブルの名前が続きます：

DELETE clubEquipment, prohibitedBrands
FROM clubEquipment JOIN prohibitedBrands
ON clubEquipment.brand = prohibitedBrands.brandName
WHERE homeCountry = 'USA';

Output
Query OK, 4 rows affected (0.01 sec)

この出力は、操作によって4行のデータが削除されたことを示しています。clubEquipmentから2行、prohibitedBrandsから2行です。 clubEquipmentテーブルからレコードを削除し、prohibitedBrandsテーブルのすべてのレコードを維持したい場合は、DELETEの後にclubEquipmentのみをリストします。キーワード、およびその逆。

外部キーの変更DELETEの動作

デフォルトでは、外部キーとの競合を引き起こすDELETEステートメントは失敗します。

MySQLへの接続と前提条件のサンプルデータベースセクションのセットアップから、clubEquipmentテーブルのownerID列がownerID列。 つまり、ownerID列に入力された値は、memberID列にすでに存在している必要があります。

memberID値がownerID列のどこかで使用されているclubMembersテーブルからデータの行を削除しようとすると、エラーが発生します。

DELETE FROM clubMembers
WHERE memberID = 6;

Output
ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails

最初に、親テーブル（clubMembers）に外部キー値が存在する子テーブル（この例ではclubEquipment）の行を削除することで、このエラーを回避できます。

または、既存の外部キー制約をDELETE操作を異なる方法で処理する制約に置き換えることでこの動作を変更できます。

現在の制約を置き換えるには、最初にALTER TABLEステートメントで制約を削除する必要があります。 clubEquipmentのCREATE TABLEステートメントで、テーブルの外部キー制約の名前としてfk_ownerIDを定義したことを思い出してください。

ALTER TABLE clubEquipment
DROP FOREIGN KEY fk_ownerID;

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

その後、特定のユースケースに適した方法でDELETE操作を処理するように構成された新しい外部キー制約を作成します。 外部キーに違反するDELETEステートメントを禁止するデフォルト設定の他に、ほとんどのRDBMSで使用可能な他の2つのオプションがあります。

• ON DELETE SET NULL：このオプションを使用すると、親テーブルからレコードを削除し、それらをNULLとして参照する子テーブルの値をリセットできます。
• ON DELETE CASCADE：親テーブルの行を削除すると、このオプションにより、SQLは子テーブルでその行を参照するすべてのレコードを自動的に削除します。

この例では、ON DELETE SET NULLは意味がありません。 メンバーがクラブを離れ、そのレコードがclubMembersテーブルから削除された場合、残りのメンバーはその機器を使用できなくなるため、clubEquipmentテーブルから削除する必要があります。 したがって、ON DELETE CASCADEオプションは、私たちの目的にとってより理にかなっています。

ON DELETE CASCADEの動作に従う外部キー制約を追加するには、次のALTER TABLEステートメントを実行します。 これにより、以前の外部キー定義を複製するnewfk_ownerIDという名前の新しい制約が作成されますが、ON DELETE CASCADEオプションが含まれます。

ALTER TABLE clubEquipment
ADD CONSTRAINT newfk_ownerID
FOREIGN KEY (ownerID)
REFERENCES clubMembers(memberID)
ON DELETE CASCADE;

Output
Query OK, 7 rows affected (0.07 sec)
Records: 7  Duplicates: 0  Warnings: 0

この出力は、clubEquipmentテーブルの残りの7行すべてに影響を与えたことを示しています。

CREATE TABLE clubEquipment (
equipmentID int PRIMARY KEY,
equipmentType varchar(30),
brand varchar(15),
ownerID int,
CONSTRAINT fk_ownerID
FOREIGN KEY (ownerID) REFERENCES clubMembers(memberID)
ON DELETE CASCADE
);

その後、clubMembersテーブルから任意のレコードを削除できるようになり、それを参照するclubEquipmentテーブルのすべての行も削除されます。

DELETE FROM clubMembers
WHERE memberID = 6;

Output
Query OK, 1 row affected (0.00 sec)

この出力は、1つの行にのみ影響することを示していますが、clubEquipmentテーブルのownerID値を6としてリストしている機器レコードも削除されます。

結論

このガイドを読むことで、DELETEステートメントを使用して1つ以上のテーブルからデータを削除する方法を学びました。 また、SQLが外部キー制約と競合するDELETE操作を処理する方法、およびそのデフォルトの動作を変更する方法についても学びました。

ここで概説するコマンドは、SQLを使用するすべてのデータベース管理システムで機能するはずです。 すべてのSQLデータベースは独自の言語実装を使用しているため、DELETEステートメントの処理方法と使用可能なオプションの詳細については、DBMSの公式ドキュメントを参照してください。

SQLの操作について詳しく知りたい場合は、SQLの使用方法に関するこのシリーズの他のチュートリアルを確認することをお勧めします。