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ユーザーで検証されました。
注:多くのRDBMSは、独自のSQL実装を使用していることに注意してください。 このチュートリアルで概説されているコマンドはほとんどのRDBMSで機能しますが、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;
データベースが正常に作成されると、次のような出力が表示されます。
OutputQuery OK, 1 row affected (0.01 sec)
deleteDB
データベースを選択するには、次のUSE
ステートメントを実行します。
USE deleteDB;
OutputDatabase 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;
警告:この構文の重要な部分はWHERE
句です。これにより、削除するデータの行を正確に指定できます。 これがないと、DELETE FROM table_name;
のようなコマンドは正しく実行されますが、テーブルからデータのすべての行が削除されます。
DELETE
操作が成功すると元に戻せないことに注意してください。 削除するデータを正確に知らずに実行すると、誤って間違ったレコードを削除する可能性があります。 誤って間違ったデータを削除しないようにする1つの方法は、最初にSELECT
クエリを発行して、DELETE
操作のWHERE
句によって返されるデータを確認することです。 。
たとえば、Korgiというブランドの音楽機器に関連するレコードをすべて削除したいとします。 ただし、安全のために、最初にクエリを作成して、brand
列にKorgi
と表示されている機器レコードを正確に確認することにします。
テーブル内のどの楽器がKorgによって作成されているかを見つけるには、次のクエリを実行できます。 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';
OutputQuery 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%';
OutputQuery 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 whose
name`値を返します。
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%');
OutputQuery 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';
OutputQuery 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;
OutputERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails
最初に、親テーブル(clubMembers
)に外部キー値が存在する子テーブル(この例ではclubEquipment
)の行を削除することで、このエラーを回避できます。
または、既存の外部キー制約をDELETE
操作を異なる方法で処理する制約に置き換えることでこの動作を変更できます。
注:すべてのデータベース管理システムまたはエンジンで、次の段落で概説するように、既存のテーブルに制約を追加または削除できるわけではありません。 MySQL以外のRDBMSを使用している場合は、その公式ドキュメントを参照して、制約を管理するための制限を理解する必要があります。
現在の制約を置き換えるには、最初にALTER TABLE
ステートメントで制約を削除する必要があります。 clubEquipment
のCREATE TABLE
ステートメントで、テーブルの外部キー制約の名前としてfk_ownerID
を定義したことを思い出してください。
ALTER TABLE clubEquipment DROP FOREIGN KEY fk_ownerID;
OutputQuery 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;
OutputQuery OK, 7 rows affected (0.07 sec) Records: 7 Duplicates: 0 Warnings: 0
この出力は、clubEquipment
テーブルの残りの7行すべてに影響を与えたことを示しています。
注:外部キーがDELETE
操作を処理する方法を変更するためにテーブルの定義を変更する代わりに、次のようにCREATE TABLE
ステートメントでこの動作を最初から定義できます。
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;
OutputQuery OK, 1 row affected (0.00 sec)
この出力は、1つの行にのみ影響することを示していますが、clubEquipment
テーブルのownerID
値を6
としてリストしている機器レコードもすべて削除されます。
結論
このガイドを読むことで、DELETE
ステートメントを使用して1つ以上のテーブルからデータを削除する方法を学びました。 また、SQLが外部キー制約と競合するDELETE
操作を処理する方法、およびそのデフォルトの動作を変更する方法についても学びました。
ここで概説するコマンドは、SQLを使用するすべてのデータベース管理システムで機能するはずです。 すべてのSQLデータベースは独自の言語実装を使用しているため、DELETE
ステートメントの処理方法と使用可能なオプションの詳細については、DBMSの公式ドキュメントを参照してください。
SQLの操作について詳しく知りたい場合は、SQLの使用方法に関するこのシリーズの他のチュートリアルを確認することをお勧めします。