SQLでWHERE句を使用する方法
序章
構造化照会言語(SQL)ステートメントでは、 WHERE
句は、特定の操作が影響する行を制限します。 これは、検索条件と呼ばれる特定の基準を定義することによって行われます。この基準は、操作の影響を受けるために各行が満たす必要があります。
このガイドでは、で使用される一般的な構文について説明します。 WHERE
条項。 また、複数の検索条件述語を1つに組み合わせる方法についても概説します。 WHERE
より細かくデータをフィルタリングする句、およびその使用方法 NOT
特定の検索条件を満たす行を含めるのではなく、除外する演算子。
このガイドでは、例で SELECTステートメントのみを使用しますが、ここで説明する概念は、多くのSQL操作で使用できます。 実際には、 WHERE
句は、UPDATEおよびDELETE操作の重要なコンポーネントです。
前提条件
このガイドに従うには、SQLを使用するある種のリレーショナルデータベース管理システム(RDBMS)を実行しているコンピューターが必要です。 このガイドの手順と例は、次の環境を使用して検証されています。
- Ubuntu 20.04 の初期サーバーセットアップガイドで説明されているように、管理者権限を持つ非rootユーザーとUFWで構成されたファイアウォールを備えたUbuntu20.04を実行しているサーバー。
- Ubuntu 20.04にMySQLをインストールする方法で概説されているように、MySQLがサーバーにインストールされて保護されています。 このガイドは、ステップ3 で説明されているように、新しく作成されたユーザーによって検証されました。
- また、以下を含むクエリの記述を練習するために使用できるサンプルデータがロードされたいくつかのテーブルを含むデータベースも必要です。
WHERE
条項。 MySQLサーバーに接続し、このガイド全体の例で使用されるテストデータベースを作成する方法の詳細については、次のMySQLへの接続とサンプルデータベースのセットアップセクションを実行することをお勧めします。
注:多くのRDBMSは、独自のSQL実装を使用していることに注意してください。 このチュートリアルで概説されているコマンドはほとんどのRDBMSで機能しますが、MySQL以外のシステムでテストした場合、正確な構文または出力が異なる場合があります。
MySQLへの接続とサンプルデータベースの設定
SQLデータベースシステムがリモートサーバーで実行されている場合は、ローカルマシンからサーバーにSSHで接続します。
- ssh sammy@your_server_ip
次に、MySQLサーバープロンプトを開き、置き換えます sammy
with the name of your MySQL user account:
- mysql -u sammy -p
プロンプトから、という名前のデータベースを作成します where_db
:
- CREATE DATABASE where_db;
データベースが正常に作成されると、次のような出力が表示されます。
OutputQuery OK, 1 row affected (0.01 sec)
を選択するには where_db
データベース、以下を実行します USE
声明:
- USE where_db;
OutputDatabase changed
選択した後 where_db
、その中にテーブルを作成します。
このガイドで使用されている例に従うために、地元のゴルフコースでゴルフリーグを運営していると想像してください。 あなたは、彼らが参加する外出でのリーグのプレーヤーの個々のパフォーマンスに関する情報を追跡することにしました。 そのためには、SQLデータベースに情報を保存することにします。
このテーブルには6つの列が必要であると判断しました。
name
:各ゴルファーの名前。varchar
最大20文字のデータ型rounds_played
:各ゴルファーがプレーした完全なラウンドの総数。int
データ・タイプbest
:個々の外出に対する各ゴルファーの最高または最低のスコア。int
worst
:個々の外出に対する各ゴルファーの最低または最高のスコア。int
average
:プレーしたラウンドでの各ゴルファーのスコアのおおよその平均。 この列には、decimal
タイプ、最大4桁に制限され、小数点の右側にそれらの桁の1つがありますwins
:各ゴルファーがプレーグループの全員の中で最も低いスコアを持っていたラウンドの数。int
タイプ
次を実行します CREATE TABLE
名前の付いたテーブルを作成するステートメント golfers
次の6つの列があります。
- CREATE TABLE golfers (
- name varchar(20),
- rounds_played int,
- best int,
- worst int,
- average decimal (4,1),
- wins int
- );
次に、 golfers
いくつかのサンプルデータを含む表。 次を実行します INSERT INTO
リーグのゴルファー7人を表す7行のデータを追加する操作:
- INSERT INTO golfers
- VALUES
- ('George', 22, 68, 103, 84.6, 3),
- ('Pat', 25, 65, 74, 68.7, 9),
- ('Grady', 11, 78, 118, 97.6, 0),
- ('Diane', 23, 70, 92, 78.8, 1),
- ('Calvin', NULL, 63, 76, 68.5, 7),
- ('Rose', NULL, 69, 84, 76.7, 4),
- ('Raymond', 18, 67, 92, 81.3, 1);
これらの行のうちの2つに注意してください。 rounds_played
値は NULL
. このチュートリアルでは、これらのゴルファーがプレーしたラウンド数を報告していないため、これらの値は次のように記録されると想定します。 NULL
.
また、各ゴルファーの best
値は彼らよりも小さい worst
. これは、一般的なゴルフ規則では、ゴルファーのスコアは、コースの各ホールにボールを入れるのにかかるストローク数によって決定され、勝者は総ストローク数が最も少ない人であるためです。 したがって、他のほとんどのスポーツとは異なり、ゴルファーの最高スコアは最悪よりも低くなります。
これで、ガイドの残りの部分に従い、使用方法を学び始める準備が整いました。 WHERE
SQLの句。
を使用したデータのフィルタリング WHERE
条項
SQLでは、ステートメントは、テーブルの作成、データの挿入または削除、列またはテーブルの構造の変更など、ある種のタスクを実行するデータベースシステムに送信される操作です。 SQLステートメントは、さまざまな句で構成されており、特定のキーワードとそれらに必要な情報で構成されています。
冒頭で述べたように、 WHERE
句を使用すると、SQL操作の影響を受けないようにデータの特定の行を除外できます。 クエリでは、 WHERE
節は FROM
次の例のように、句:
- SELECT columns_to_query
- FROM table_to_query
- WHERE search_condition;
次の WHERE
キーワードは検索条件です。 検索条件は、1つ以上の述語、または1つ以上の値式を評価し、「true」、「false」、または「unknown」の結果を返すことができる式のセットです。 検索条件に単一の述語しか含まれていない場合、「検索条件」と「述語」という用語は同義語であることに注意してください。
の述語 WHERE
句の検索条件にはさまざまな形式がありますが、通常は次の構文に従います。
. . .
WHERE column_name OPERATOR value_expression
. . .
SQLでは、値式(スカラー式とも呼ばれる)は、単一の値を返す任意の式です。 値の式は、文字列や数値などのリテラル値、数式にすることができます。 ただし、ほとんどの場合、 WHERE
句の検索条件は列名になります。
を含むSQLクエリを実行する場合 WHERE
句を指定すると、データベース管理システムは、によって定義された論理テーブルのすべての行に検索条件を適用します。 FROM
句。 次に、検索条件のすべての述部が「true」と評価された行のみを返します。
このアイデアを説明するために、次のクエリを実行します。 これにより、からすべての値が返されます golfers
テーブルの name
桁:
- SELECT name
- FROM golfers
- WHERE (2 + 2) = 4;
このクエリには、 WHERE
句ですが、列名を指定する代わりに使用します (2 + 2)
最初の値の式として、それが2番目の値の式と等しいかどうかをテストします。 4
. なぜなら (2 + 2)
常には等しい 4
、この検索条件は、すべての行で「true」と評価されます。 その結果、すべての行が結果セットに返されます。
Output+---------+
| name |
+---------+
| George |
| Pat |
| Grady |
| Diane |
| Calvin |
| Rose |
| Raymond |
+---------+
7 rows in set (0.01 sec)
これ WHERE
句は常に「true」と評価され、テーブル内のすべての行を常に返すため、あまり便利ではありません。 前述のように、通常、少なくとも1つの列名を値式として使用します。 WHERE
句の検索条件。 クエリを実行すると、データベースシステムは検索条件を各行に順番に個別に適用します。 検索条件の値式として列名を指定することにより、DBMSに、その列の各行の値を、その行の検索条件の反復の値式として使用するように指示します。
次のクエリは WHERE
句は、前の例よりも排他的な検索条件を各行に適用します。 それは戻ります name
と wins
任意の行の値 wins
列の値は 1
:
- SELECT name, wins
- FROM golfers
- WHERE wins = 1;
2人のゴルファーだけが正確に1ラウンドに勝ったので、クエリはそれらの2つの行のみを返します。
Output+---------+------+
| name | wins |
+---------+------+
| Diane | 1 |
| Raymond | 1 |
+---------+------+
2 rows in set (0.01 sec)
前の例では、等号(=
)2つの値式が同等であるかどうかをテストしますが、使用する演算子は、結果セットのフィルタリングに使用する述語のタイプによって異なります。
SQL標準では、18種類の述語が定義されていますが、すべての述語がすべてのSQL実装に含まれているわけではありません。 以下に、最も一般的に使用される5つの述語タイプと、それぞれの述語タイプとそれらが使用する演算子の簡単な説明を示します。
比較
比較述語は、比較演算子を使用して、ある値(クエリでは、通常は指定された列の値)を別の値と比較します。 6つの比較演算子は次のとおりです。
=
:2つの値が等しいかどうかをテストします
- SELECT name
- FROM golfers
- WHERE name = 'George';
Output+--------+
| name |
+--------+
| George |
+--------+
1 row in set (0.00 sec)
<>
:2つの値が等しくないかどうかをテストします
- SELECT name, wins
- FROM golfers
- WHERE wins <> 1;
Output+--------+------+
| name | wins |
+--------+------+
| George | 3 |
| Pat | 9 |
| Grady | 0 |
| Calvin | 7 |
| Rose | 4 |
+--------+------+
5 rows in set (0.00 sec)
<
:最初の値が2番目の値よりも小さいかどうかをテストします
- SELECT name, wins
- FROM golfers
- WHERE wins < 1;
Output+-------+------+
| name | wins |
+-------+------+
| Grady | 0 |
+-------+------+
1 row in set (0.00 sec)
>
:最初の値が2番目の値より大きいかどうかをテストします
- SELECT name, wins
- FROM golfers
- WHERE wins > 1;
Output+--------+------+
| name | wins |
+--------+------+
| George | 3 |
| Pat | 9 |
| Calvin | 7 |
| Rose | 4 |
+--------+------+
4 rows in set (0.00 sec)
<=
:最初の値が2番目の値以下かどうかをテストします
- SELECT name, wins
- FROM golfers
- WHERE wins <= 1;
Output+---------+------+
| name | wins |
+---------+------+
| Grady | 0 |
| Diane | 1 |
| Raymond | 1 |
+---------+------+
3 rows in set (0.00 sec)
>=
:最初の値が2番目の値以上かどうかをテストします
- SELECT name, wins
- FROM golfers
- WHERE wins >= 1;
Output+---------+------+
| name | wins |
+---------+------+
| George | 3 |
| Pat | 9 |
| Diane | 1 |
| Calvin | 7 |
| Rose | 4 |
| Raymond | 1 |
+---------+------+
6 rows in set (0.00 sec)
ヌル
を使用する述語 IS NULL
オペレーターは、指定された列の値がNullであるかどうかをテストします。 その場合、述語は「true」と評価され、行は結果セットに含まれます。
- SELECT name, rounds_played
- FROM golfers
- WHERE rounds_played IS NULL;
Output+--------+---------------+
| name | rounds_played |
+--------+---------------+
| Calvin | NULL |
| Rose | NULL |
+--------+---------------+
2 rows in set (0.00 sec)
範囲
範囲述語は BETWEEN
指定された列の値が2つの値の式の間にあるかどうかをテストする演算子:
- SELECT name, best
- FROM golfers
- WHERE best BETWEEN 67 AND 73;
Output+---------+------+
| name | best |
+---------+------+
| George | 68 |
| Diane | 70 |
| Rose | 69 |
| Raymond | 67 |
+---------+------+
4 rows in set (0.00 sec)
メンバーシップ
メンバーシップ述語は、 IN
値が特定のセットのメンバーであるかどうかをテストする演算子:
- SELECT name, best
- FROM golfers
- WHERE best IN (65, 67, 69, 71);
Output+---------+------+
| name | best |
+---------+------+
| Pat | 65 |
| Rose | 69 |
| Raymond | 67 |
+---------+------+
3 rows in set (0.00 sec)
パターンマッチ
パターンマッチング述語は、 LIKE
値が1つ以上のワイルドカード文字(ワイルドカードとも呼ばれる)を含む文字列パターンと一致するかどうかをテストする演算子。 SQLは2つのワイルドカードを定義します。 %
と _
:
_
:アンダースコアは単一の不明な文字を表します
- SELECT name, rounds_played
- FROM golfers
- WHERE rounds_played LIKE '2_';
Output+--------+---------------+
| name | rounds_played |
+--------+---------------+
| George | 22 |
| Pat | 25 |
| Diane | 23 |
+--------+---------------+
3 rows in set (0.00 sec)
%
:パーセント記号は、0個以上の不明な文字を表します
- SELECT name, rounds_played
- FROM golfers
- WHERE name LIKE 'G%';
Output+--------+---------------+
| name | rounds_played |
+--------+---------------+
| George | 22 |
| Grady | 11 |
+--------+---------------+
2 rows in set (0.00 sec)
これらの述語タイプのそれぞれについて詳しく説明することは、このチュートリアルの範囲を超えています。 ただし、それらについて詳しく知りたい場合は、次のガイドを確認することをお勧めします。
複数の述語を AND
と OR
よりきめ細かくフィルタリングされた結果が必要な場合があります WHERE
単一の検索条件述語を持つ句が提供できます。 一方、いくつかの検索条件を満たす行が結果セットで受け入れられる場合もあります。 このような場合、あなたは書くことができます WHERE
複数の述語を含む句 AND
また OR
それぞれ演算子。
これらの演算子の使用を開始するには、次のクエリを実行して、から値を返します。 golfers
テーブルの name
, best
, worst
、 と average
列。 これは WHERE
節には、で区切られた2つの述語が含まれます AND
:
- SELECT name, best, worst, average
- FROM golfers
- WHERE best < 70 AND worst < 96;
最初の述語は、各行が best
値は70未満ですが、2番目は各行が worst
値は96未満です。 いずれかのテストが行に対して「false」と評価された場合、その行は結果セットに返されません。
Output+---------+------+-------+---------+
| name | best | worst | average |
+---------+------+-------+---------+
| Pat | 65 | 74 | 68.7 |
| Calvin | 63 | 76 | 68.5 |
| Rose | 69 | 84 | 76.7 |
| Raymond | 67 | 92 | 81.3 |
+---------+------+-------+---------+
4 rows in set (0.00 sec)
次に、次のクエリを実行します。 これは前の例と同じですが、2つの述語を OR
の代わりに演算子 AND
:
- SELECT name, best, worst, average
- FROM golfers
- WHERE best < 70 OR worst < 96;
行が返されるには、述語の1つだけが「true」と評価される必要があるため、この結果セットには、前の例より2つ多くの行が含まれています。
Output+---------+------+-------+---------+
| name | best | worst | average |
+---------+------+-------+---------+
| George | 68 | 103 | 84.6 |
| Pat | 65 | 74 | 68.7 |
| Diane | 70 | 92 | 78.8 |
| Calvin | 63 | 76 | 68.5 |
| Rose | 69 | 84 | 76.7 |
| Raymond | 67 | 92 | 81.3 |
+---------+------+-------+---------+
6 rows in set (0.00 sec)
1つの述語に必要な数の述語を含めることができます WHERE
それらを正しい構文と組み合わせる限り、句。 ただし、検索条件が複雑になると、フィルタリングするデータを予測することが難しくなる可能性があります。
データベースシステムは一般的に優先されることに注意することが重要です AND
演算子。 これは、述語が AND
演算子(または3つ以上の述語の場合は演算子)は、単一の分離された検索条件として扱われ、他の述語の前にテストされます。 WHERE
句。
説明のために、次のクエリを実行します。このクエリは、 name
, average
, worst
、 と rounds_played
で定義された検索条件を満たす任意の行の列 WHERE
句:
- SELECT name, average, worst, rounds_played
- FROM golfers
- WHERE average < 85 OR worst < 95 AND rounds_played BETWEEN 19 AND 23;
このクエリは、最初に述語が AND
演算子— worst < 95
と rounds_played BETWEEN 19 AND 23
—両方とも、現在の反復の行に対して「true」と評価されます。 その場合、その行が結果セットに表示されます。 ただし、どちらかが「false」と評価された場合、クエリは現在の行が average
値は85未満です。 その場合、行が返されます。
Output+---------+---------+-------+---------------+
| name | average | worst | rounds_played |
+---------+---------+-------+---------------+
| George | 84.6 | 103 | 22 |
| Pat | 68.7 | 74 | 25 |
| Diane | 78.8 | 92 | 23 |
| Calvin | 68.5 | 76 | NULL |
| Rose | 76.7 | 84 | NULL |
| Raymond | 81.3 | 92 | 18 |
+---------+---------+-------+---------------+
6 rows in set (0.00 sec)
括弧で囲むことにより、2つ以上の述部のセットに優先順位を付けることができます。 次の例は前の例と同じですが、 average < 85
と worst < 95
述語、 OR
括弧内の演算子:
- SELECT name, average, worst, rounds_played
- FROM golfers
- WHERE (average < 85 OR worst < 95) AND rounds_played BETWEEN 19 AND 23;
最初の2つの述語は括弧で囲まれているため、後続の述語は括弧で囲まれています AND
オペレーターは、それらを「真」と評価する必要がある個別の検索条件として扱います。 これらの述語の両方が— average < 85
と worst < 95
—「false」と評価すると、検索条件全体が「false」と評価され、クエリは結果セットから行をすぐに削除してから、次の行を評価します。
ただし、これらの最初の2つの述語のいずれかが「真」と評価された場合、クエリは特定のゴルファーが rounds_played
値は19から23の間です。 その場合、その行は結果セットに返されます。
Output+--------+---------+-------+---------------+
| name | average | worst | rounds_played |
+--------+---------+-------+---------------+
| George | 84.6 | 103 | 22 |
| Diane | 78.8 | 92 | 23 |
+--------+---------+-------+---------------+
2 rows in set (0.00 sec)
この出力が示すように、述語のセットに優先順位を付けて括弧で囲むことにより、そうでない場合、同一のクエリが大幅に異なる結果セットを返す可能性があります。
必ずしもそうする必要はありませんが、1つの検索条件で3つ以上の述部を組み合わせる場合は、常に括弧を含めることをお勧めします。 そうすることで、クエリをより読みやすく、理解しやすくすることができます。
結果を除外する NOT
これまでのこのガイドの例はすべて、次のコマンドを使用してクエリを作成する方法に焦点を当てています。 WHERE
結果セットで指定された検索条件を満たす行のみを含む句。 ただし、特定の行を除外するクエリを作成するには、 NOT
あなたの演算子 WHERE
条項。
範囲、メンバーシップ、およびパターンマッチングの述語句には、 NOT
演算子は通常、次の構文に従います。
- . . .
- WHERE column_name NOT OPERATOR value_expression
- . . .
説明のために、次のクエリを実行します。 これにより、からの値が返されます golfers
テーブルの name
列、しかし NOT
その中の演算子 WHERE
句を使用すると、DBMSはワイルドカードパターンに一致する行をすべて除外します。
- SELECT name
- FROM golfers
- WHERE name NOT LIKE 'R%';
Output+--------+
| name |
+--------+
| George |
| Pat |
| Grady |
| Diane |
| Calvin |
+--------+
5 rows in set (0.00 sec)
追加すると、状況が少し異なります。 NOT
オペレーターから IS NULL
述語。 そのような場合、あなたは NOT
の間に IS
と NULL
、次の例のように。 このクエリは、 name
と rounds_played
そのすべてのゴルファーの価値 rounds_played
値はNullではありません:
- SELECT name, rounds_played
- FROM golfers
- WHERE rounds_played IS NOT NULL;
Output+---------+---------------+
| name | rounds_played |
+---------+---------------+
| George | 22 |
| Pat | 25 |
| Grady | 11 |
| Diane | 23 |
| Raymond | 18 |
+---------+---------------+
5 rows in set (0.00 sec)
を配置することもできます NOT
直後の演算子 WHERE
キーワード。 これは、ゴルファーを返すこのクエリ例のように、複数の検索条件を満たすかどうかに基づいて行を除外する場合に役立ちます。 name
, average
, best
、 と wins
値:
- SELECT name, average, best, wins
- FROM golfers
- WHERE NOT (average < 80 AND best < 70) OR wins = 9;
Output+---------+---------+------+------+
| name | average | best | wins |
+---------+---------+------+------+
| George | 84.6 | 68 | 3 |
| Pat | 68.7 | 65 | 9 |
| Grady | 97.6 | 78 | 0 |
| Diane | 78.8 | 70 | 1 |
| Raymond | 81.3 | 67 | 1 |
+---------+---------+------+------+
5 rows in set (0.00 sec)
この結果セットの2行目に注意してください。 パットの average
スコアは80未満で彼女 best
スコアは70未満です。 ただし、彼女の行は引き続き結果セットに含まれています。 NOT
演算子は、括弧で囲まれた検索条件のみを無効にします。
複数の述語をで区切ってラップする場合は、 AND
また OR
括弧内は、SQLがそれらの述語に優先順位を付け、単一の分離された検索条件として扱います。 このため、 NOT
演算子は、最初の2つの述語に基づいて行のみを除外します。 average < 80
と best < 70
. ただし、3番目の述語に基づく行には行が含まれます。 wins = 9
.
次のように、3つすべてを括弧で囲むことにより、最初の2つとともに3番目の述語に基づいて行を除外するようにクエリを書き直すことができます。
- SELECT name, average, best, wins
- FROM golfers
- WHERE NOT ((average < 80 AND best < 70) OR wins = 9);
Output+---------+---------+------+------+
| name | average | best | wins |
+---------+---------+------+------+
| George | 84.6 | 68 | 3 |
| Grady | 97.6 | 78 | 0 |
| Diane | 78.8 | 70 | 1 |
| Raymond | 81.3 | 67 | 1 |
+---------+---------+------+------+
4 rows in set (0.00 sec)
SQLの実装によっては、以下を含めると、データベースシステムがクエリの構文を無効と見なす場合があります。 NOT
比較演算子の前。 例として、次のクエリを実行してみてください。
- SELECT name
- FROM golfers
- WHERE name NOT = 'Grady';
MySQLとその派生物では、これによりエラーが発生します。
OutputERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '= 'Grady'' at line 1
このエラーの理由は、 NOT
演算子は通常、比較演算子(=
, <>
, <
, <=
, >
、 と >=
)、最初の比較演算子が除外する行を返す別の演算子に置き換えることで、1つの比較演算子の反対の効果を実現できるためです。 たとえば、等価演算子を置き換えることができます(=
)非等価演算子(<>
).
結論
このガイドを読むことで、書き方を学びました WHERE
句を使用すると、クエリは指定された条件を満たす行のみを返します。 また、1つのクエリで複数の述語と検索条件を組み合わせる方法、および NOT
結果セットから情報を除外するキーワード。
ここに示すコマンドはほとんどのリレーショナルデータベースで機能するはずですが、すべてのSQLデータベースが独自の言語実装を使用していることに注意してください。 各コマンドとそのオプションの完全なセットの詳細については、DBMSの公式ドキュメントを参照してください。
SQLの操作について詳しく知りたい場合は、SQLの使用方法に関するこのシリーズの他のチュートリアルを確認することをお勧めします。