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以外のシステムでテストした場合、正確な構文または出力が異なる場合があります。
このページに埋め込まれているインタラクティブ端末を使用して、このチュートリアルのサンプルクエリを試すこともできます。 次のLaunch an Interactive Terminal!
ボタンをクリックして開始します。
インタラクティブターミナルを起動します!
MySQLへの接続とサンプルデータベースの設定
SQLデータベースシステムがリモートサーバーで実行されている場合は、ローカルマシンからサーバーにSSHで接続します。
ssh sammy@your_server_ip
次に、MySQLサーバープロンプトを開き、sammy
をMySQLユーザーアカウントの名前に置き換えます。 このページに埋め込まれたインタラクティブ端末を使用している場合、プロンプトが表示されたときに使用するパスワードはsecret
という単語であることに注意してください。
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
:最大20文字のvarchar
データ型を使用して表現された各ゴルファーの名前rounds_played
:int
データ型で表される、各ゴルファーがプレーした完全なラウンドの総数。best
:個々の外出に対する各ゴルファーの最高または最低のスコア。int
としても表されます。worst
:個々の外出の各ゴルファーの最低または最高のスコア。これもint
として表されます。average
:各ゴルファーがプレーしたラウンドでのスコアのおおよその平均。 この列には、decimal
タイプの値が保持され、最大4桁に制限され、そのうちの1桁が小数点の右側にあります。wins
:int
タイプを使用して表された、各ゴルファーがプレーグループの全員の中で最も低いスコアを持っていたラウンドの数
次のCREATE TABLE
ステートメントを実行して、次の6つの列を持つgolfers
という名前のテーブルを作成します。
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);
これらの行のrounds_played
値の2つがNULL
であることに注意してください。 このチュートリアルでは、これらのゴルファーがプレーしたラウンド数を報告していないため、これらの値はNULL
として記録されていると想定します。
また、各ゴルファーのbest
の値がworst
よりも小さいことに気付くかもしれません。 これは、一般的なゴルフ規則では、ゴルファーのスコアは、コースの各ホールにボールを入れるのにかかるストローク数によって決定され、勝者は総ストローク数が最も少ない人であるためです。 したがって、他のほとんどのスポーツとは異なり、ゴルファーの最高スコアは最悪よりも低くなります。
これで、ガイドの残りの部分に従い、SQLでWHERE
句を使用する方法を学び始める準備が整いました。
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
句の検索条件の値式の少なくとも1つは列名になります。
WHERE
句を含むSQLクエリを実行すると、データベース管理システムは、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」と評価され、テーブル内のすべての行を常に返すため、あまり役に立ちません。 前述のように、通常、WHERE
句の検索条件で値式として少なくとも1つの列名を使用します。 クエリを実行すると、データベースシステムは検索条件を各行に順番に個別に適用します。 検索条件の値式として列名を指定することにより、DBMSに、その列の各行の値を、その行の検索条件の反復の値式として使用するように指示します。
次のクエリのWHERE
句は、前の例よりも排他的な検索条件を各行に適用します。 wins
列の値が1
と等しい任意の行からname
およびwins
の値を返します。
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
句が提供できるよりも、よりきめ細かくフィルタリングされた結果が必要になる場合があります。 一方、いくつかの検索条件を満たす行が結果セットで受け入れられる場合もあります。 このような場合、AND
またはOR
演算子をそれぞれ使用して、複数の述語を含むWHERE
句を記述できます。
これらの演算子の使用を開始するには、golfers
テーブルのname
、best
、worst
、およびaverage
から値を返す次のクエリを実行します。 ]列。 そのWHERE
句には、AND
で区切られた2つの述語が含まれています。
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つの述語をAND
ではなくOR
演算子で区切る点が異なります。
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つ以上の述語の場合は演算子)で区切られた述語は、[X226Xの他の述語の前にテストされる単一の分離された検索条件として扱われることを意味します。 ]句。
説明のために、次のクエリを実行します。このクエリは、検索条件を満たす任意の行の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
演算子は、それらを「true」と評価する必要がある個別の検索条件として扱います。 これらの述語(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
句を使用してクエリを作成する方法に焦点を当てています。 ただし、WHERE
句にNOT
演算子を含めることで、特定の行を除外するクエリを作成できます。
NOT
演算子を含む範囲、メンバーシップ、およびパターンマッチングの述語句は、通常、次の構文に従います。
. . . WHERE column_name NOT OPERATOR value_expression . . .
説明のために、次のクエリを実行します。 これにより、golfers
テーブルのname
列から値が返されますが、WHERE
句のNOT
演算子により、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
の間に配置します。 このクエリは、rounds_played
値がNullではないすべてのゴルファーのname
およびrounds_played
値を返します。
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)
WHERE
キーワードの直後にNOT
演算子を配置することもできます。 これは、ゴルファーの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の使用方法に関するこのシリーズの他のチュートリアルを確認することをお勧めします。