SQLでテーブルから行を選択する方法
序章
データベースを操作する上で最も基本的な部分の1つは、データベース内に保持されているデータに関する情報を取得する方法です。 リレーショナルデータベース管理システムでは、テーブルから情報を取得するために使用される操作は、クエリと呼ばれます。
このガイドでは、構造化照会言語(SQL)での照会の構文と、それらのより一般的に使用される関数および演算子のいくつかについて説明します。
前提条件
このガイドに従うには、SQLを使用するある種のリレーショナルデータベース管理システム(RDBMS)を実行しているコンピューターが必要です。 このガイドの手順と例は、次の環境を使用して検証されています。
- Ubuntu 20.04 の初期サーバーセットアップガイドで説明されているように、管理者権限を持つ非rootユーザーとUFWで構成されたファイアウォールを備えたUbuntu20.04を実行しているサーバー。
- Ubuntu 20.04にMySQLをインストールする方法で概説されているように、MySQLがサーバーにインストールされて保護されています。 このチートシートは、ステップ3 で説明されているように、新しく作成されたユーザーによって検証されました。
注:多くのRDBMSは、独自のSQL実装を使用していることに注意してください。 このチュートリアルで概説されているコマンドはほとんどのRDBMSで機能しますが、MySQL以外のシステムでテストした場合、正確な構文または出力が異なる場合があります。
- また、クエリの記述を練習するために使用できるサンプルデータがロードされたいくつかのテーブルを含むデータベースも必要です。 MySQLサーバーに接続し、このガイド全体の例で使用されるテストデータベースを作成する方法の詳細については、次のMySQLへの接続とサンプルデータベースのセットアップセクションを実行することをお勧めします。
このページに埋め込まれているインタラクティブ端末を使用して、このチュートリアルのサンプルクエリを試すこともできます。 次のLaunch an Interactive Terminal!
ボタンをクリックして開始します。
MySQLへの接続とサンプルデータベースの設定
SQLデータベースシステムがリモートサーバーで実行されている場合は、ローカルマシンからサーバーにSSHで接続します。
- ssh sammy@your_server_ip
次に、MySQLサーバープロンプトを開き、sammy
をMySQLユーザーアカウントの名前に置き換えます。 このページに埋め込まれたインタラクティブ端末を使用している場合、プロンプトが表示されたときに使用するパスワードはsecret
という単語であることに注意してください。
- mysql -u sammy -p
プロンプトから、queries_db
という名前のデータベースを作成します。
- CREATE DATABASE queries_db;
データベースが正常に作成されると、次のような出力が表示されます。
OutputQuery OK, 1 row affected (0.01 sec)
queries_db
データベースを選択するには、次のUSE
ステートメントを実行します。
- USE queries_db;
OutputDatabase changed
queries_db
を選択した後、その中にいくつかのテーブルを作成します。
このガイドで使用されている例に従うために、ニューヨーク市で公園のクリーンアップイニシアチブを実行していると想像してください。 このプログラムは、定期的にゴミを拾うことで、自宅近くの都市公園の清掃に取り組むボランティアで構成されています。 イニシアチブに参加すると、これらのボランティアはそれぞれ、毎週何個のゴミ袋を拾いたいかという目標を設定しました。 ボランティアの目標に関する情報を、次の5つの列を持つテーブルを持つSQLデータベースに格納することにしました。
vol_id
:int
データ型で表される各ボランティアの識別番号。 この列は、テーブルの主キーとして機能します。つまり、各値は、それぞれの行の一意の識別子として機能します。 主キーのすべての値は一意である必要があるため、この列にもUNIQUE
制約が適用されますname
:最大20文字のvarchar
データ型を使用して表された各ボランティアの名前park
:各ボランティアがゴミを拾う公園の名前。最大20文字のvarchar
データ型を使用して表されます。 複数のボランティアが同じ公園のゴミを片付けることができることに注意してくださいweekly_goal
:int
タイプで表された、1週間に何袋のゴミを拾いたいかという各ボランティアの目標max_bags
:int
として表される、1週間に最も多くのゴミを拾った各ボランティアの個人記録。
次のCREATE TABLE
ステートメントを実行して、次の5つの列を持つvolunteers
という名前のテーブルを作成します。
- CREATE TABLE volunteers (
- vol_id int UNIQUE,
- name varchar(20),
- park varchar(30),
- weekly_goal int,
- max_bags int,
- PRIMARY KEY (vol_id)
- );
次に、volunteers
テーブルにサンプルデータをロードします。 次のINSERT INTO
操作を実行して、プログラムのボランティアの7人を表す7行のデータを追加します。
- INSERT INTO volunteers
- VALUES
- (1, 'Gladys', 'Prospect Park', 3, 5),
- (2, 'Catherine', 'Central Park', 2, 2),
- (3, 'Georgeanna', 'Central Park', 2, 1),
- (4, 'Wanda', 'Van Cortland Park', 1, 1),
- (5, 'Ann', 'Prospect Park', 2, 7),
- (6, 'Juanita', 'Riverside Park', 1, 4),
- (7, 'Georgia', 'Prospect Park', 1, 3);
これで、ガイドの残りの部分に従い、SQLでクエリを作成する方法を学び始める準備が整いました。
必要なクエリコンポーネント:SELECT
およびFROM
句
SQLでは、ステートメントは、テーブルの作成、データの挿入または削除、列またはテーブルの構造の変更など、ある種のタスクを実行するデータベースシステムに送信される操作です。 query は、データベースに保持されているデータに関する情報を取得するSQLステートメントです。
クエリ自体は、テーブルに保持されている既存のデータを変更しません。 クエリの作成者が明示的に要求したデータに関する情報のみが返されます。 特定のクエリによって返される情報は、その結果セットと呼ばれます。 結果セットは通常、指定されたテーブルの1つ以上の列で構成され、結果セットで返される各列は1つ以上の情報行を保持できます。
SQLクエリの一般的な構文は次のとおりです。
- SELECT columns_to_return
- FROM table_to_query;
SQLステートメントは、さまざまな句で構成されています。これらの句は、特定のキーワードと、これらのキーワードに必要な情報で構成されています。 少なくとも、SQLクエリでは、SELECT
句とFROM
句の2つの句を含めるだけで済みます。
注:この構文例では、両方の句がそれぞれの行に記述されています。 ただし、次のように、任意のSQLステートメントを1行で記述することもできます。
- SELECT columns_to_return FROM table_to_query;
このガイドでは、ステートメントを複数の行に分割するという一般的なSQLスタイルの規則に従って、各行に1つの句のみが含まれるようにします。 これは、各例をより読みやすく理解しやすくすることを目的としていますが、構文エラーを含めない限り、1行または必要な数の行にクエリを記述できることに注意してください。
すべてのSQLクエリはSELECT
句で始まり、クエリを一般的にSELECTステートメントと呼ぶ人もいます。 SELECT
キーワードの後に、結果セットに返される列のリストが表示されます。 これらの列は、FROM
句で指定されたテーブルから取得されます。
SQLクエリでは、実行の順序はFROM
句で始まります。 SELECT
句はFROM
句の前に記述されているため、これは混乱を招く可能性がありますが、RDBMSは、クエリ対象の完全な作業データセットを知ってから、情報の取得を開始する必要があることに注意してください。 。 クエリをSELECT
-指定された列FROM
指定されたテーブルと考えると便利です。 最後に、すべてのSQLステートメントはセミコロン(;
)で終了する必要があることに注意することが重要です。
例として、次のクエリを実行します。 これにより、volunteers
テーブルからname
列が取得されます。
- SELECT name
- FROM volunteers;
このクエリの結果セットは次のとおりです。
Output+------------+
| name |
+------------+
| Gladys |
| Catherine |
| Georgeanna |
| Wanda |
| Ann |
| Juanita |
| Georgia |
+------------+
7 rows in set (0.00 sec)
この操作はvolunteers
テーブル全体を調べましたが、指定された列name
のみを返します。
次のクエリのように、各列の名前をコンマで区切ることにより、複数の列から情報を取得できます。 これにより、volunteers
テーブルからvol_id
、name
、およびpark
列が返されます。
- SELECT park, name, vol_id
- FROM volunteers;
Output+-------------------+------------+--------+
| park | name | vol_id |
+-------------------+------------+--------+
| Prospect Park | Gladys | 1 |
| Central Park | Catherine | 2 |
| Central Park | Georgeanna | 3 |
| Van Cortland Park | Wanda | 4 |
| Prospect Park | Ann | 5 |
| Riverside Park | Juanita | 6 |
| Prospect Park | Georgia | 7 |
+-------------------+------------+--------+
7 rows in set (0.00 sec)
この結果セットは、最初にpark
列を返し、次にname
列、次にvol_id
を返すことに注意してください。 SQLデータベースは通常、SELECT
句にリストされている順序で列を返します。
テーブルからすべての列を取得したい場合があります。 クエリのすべての列の名前を書き出す代わりに、アスタリスク(*
)を入力できます。 SQLでは、これは「すべての列」の省略形です。
次のクエリは、volunteers
テーブルのすべての列を返します。
- SELECT *
- FROM volunteers;
Output+--------+------------+-------------------+-------------+----------+
| vol_id | name | park | weekly_goal | max_bags |
+--------+------------+-------------------+-------------+----------+
| 1 | Gladys | Prospect Park | 3 | 5 |
| 2 | Catherine | Central Park | 2 | 2 |
| 3 | Georgeanna | Central Park | 2 | 1 |
| 4 | Wanda | Van Cortland Park | 1 | 1 |
| 5 | Ann | Prospect Park | 2 | 7 |
| 6 | Juanita | Riverside Park | 1 | 4 |
| 7 | Georgia | Prospect Park | 1 | 3 |
+--------+------------+-------------------+-------------+----------+
7 rows in set (0.00 sec)
この結果セットの列が、前のMySQLへの接続とサンプルデータベースのセットアップセクションのCREATE TABLE
ステートメントで定義されたのと同じ順序でリストされていることに注目してください。 これは、ほとんどのリレーショナルデータベースシステムが、個々の列名の代わりにアスタリスクを使用するクエリを実行するときに、結果セット内の列を並べ替える方法です。
JOIN
キーワードを使用すると、同じクエリで複数のテーブルから情報を取得できることに注意してください。 これを行う方法の詳細については、SQLでの結合の使用方法に関するガイドに従うことをお勧めします。
DISTINCT
で重複する値を削除する
デフォルトでは、RDBMSは、重複する値を含め、クエリによって返された列からすべての値を返します。
例として、次のクエリを実行します。 これにより、volunteers
テーブルのpark
列の値が返されます。
- SELECT park
- FROM volunteers;
Output+-------------------+
| park |
+-------------------+
| Prospect Park |
| Central Park |
| Central Park |
| Van Cortland Park |
| Prospect Park |
| Riverside Park |
| Prospect Park |
+-------------------+
7 rows in set (0.00 sec)
この結果セットに、Prospect Park
とCentral Park
の2つの重複した値が含まれていることに注意してください。 複数のボランティアが同じ公園のゴミを片付けることができるので、これは理にかなっています。 ただし、列に保持されている一意の値のみを知りたい場合があります。 SELECT
の後にDISTINCT
キーワードを指定すると、重複する値を削除するクエリを発行できます。
次のクエリは、parks
列のすべての一意の値を返し、重複を削除します。 DISTINCT
キーワードが含まれていることを除いて、前のクエリと同じです。
- SELECT DISTINCT park
- FROM volunteers;
Output+-------------------+
| park |
+-------------------+
| Prospect Park |
| Central Park |
| Van Cortland Park |
| Riverside Park |
+-------------------+
4 rows in set (0.00 sec)
このクエリの結果セットは、Central Park
値の1つとProspect Park
値の2つを削除したため、前のクエリより3行少なくなっています。
SQLは結果セットのすべての行を個別のレコードとして扱い、DISTINCT
は、複数の行が各列で同じ値を共有する場合にのみ重複を排除することに注意してください
これを説明するために、DISTINCT
キーワードを含み、name
列とpark
列の両方を返す次のクエリを発行します。
- SELECT DISTINCT name, park
- FROM volunteers;
Output+------------+-------------------+
| name | park |
+------------+-------------------+
| Gladys | Prospect Park |
| Catherine | Central Park |
| Georgeanna | Central Park |
| Wanda | Van Cortland Park |
| Ann | Prospect Park |
| Juanita | Riverside Park |
| Georgia | Prospect Park |
+------------+-------------------+
7 rows in set (0.00 sec)
クエリにDISTINCT
が含まれていても、park
列の重複する値(Prospect Park
が3回、Central Park
が2回)がこの結果セットに表示されます。キーワード。 結果セットの個々の列に重複する値が含まれる場合がありますが、DISTINCT
で削除するには、行全体が別の行と完全に重複している必要があります。 この場合、name
列のすべての値は一意であるため、SELECT
句でその列が指定されている場合、DISTINCT
は行を削除しません。
WHERE
句を使用したデータのフィルタリング
データベース内のテーブルからより詳細な情報を取得したい場合があります。 次のように、クエリのFROM
句の後にWHERE
句を含めることで、特定の行を除外できます。
- SELECT columns_to_return
- FROM table_to_query
- WHERE search_condition;
この例の構文でWHERE
キーワードに続くのは、検索条件です。これは、結果セットから除外される行を実際に決定するものです。 検索条件は、1つ以上の述語、または1つ以上の値式を評価できる式のセットです。 SQLでは、値式(スカラー式とも呼ばれる)は、単一の値を返す任意の式です。 値の式は、リテラル値(文字列や数値など)、数式、または列名にすることができます。
WHERE
句の検索条件の述語はさまざまな形式をとることができますが、通常は次の構文に従います。
. . .
WHERE value expression OPERATOR value_expression
. . .
WHERE
キーワードの後に、値式を指定し、その後に、演算子の後に続く値式(または複数の値式)に対して列の値を評価するために使用されるいくつかの特別なSQL演算子の1つを指定します。 SQLで使用できるこのような演算子はいくつかあります。このガイドでは、このセクションの後半でそれらのいくつかを簡単に説明しますが、説明のために、最も一般的に使用される演算子の1つである等号(=
)のみに焦点を当てます。 )。 この演算子は、2つの値式が同等であるかどうかをテストします。
述語は常に「true」、「false」、または「unknown」のいずれかの結果を返します。 WHERE
句を含むSQLクエリを実行すると、DBMSはFROM
句で定義されたテーブルのすべての行に検索条件を順番に適用します。 検索条件のすべての述部が「true」と評価された行のみが返されます。
このアイデアを説明するために、次のSELECT
ステートメントを実行します。 このクエリは、volunteers
テーブルのname
列から値を返します。 ただし、このWHERE
句は、テーブルの列の1つから値を評価する代わりに、(2 + 2)
と4
の2つの値式が同等かどうかをテストします。
- SELECT name
- FROM volunteers
- WHERE (2 + 2) = 4;
(2 + 2)
は常にが4
に等しいため、この検索条件はテーブルのすべての行に対して「true」と評価されます。 したがって、すべての行のname
値が結果セットに返されます。
Output+------------+
| name |
+------------+
| Gladys |
| Catherine |
| Georgeanna |
| Wanda |
| Ann |
| Juanita |
| Georgia |
+------------+
7 rows in set (0.00 sec)
この検索条件は常に「true」の結果を返すため、あまり役に立ちません。 SELECT name FROM volunteers;
は同じ結果セットを生成するため、WHERE
句をまったく含めない方がよいでしょう。
このように2つのリテラル値を比較するのではなく、通常、WHERE
句の検索条件の値式の1つとして列名を使用します。 そうすることで、データベース管理システムに、その列の各行の値を、その行の検索条件の反復の値式として使用するように指示します。
次のクエリのWHERE
句は、より排他的な検索条件を各行に適用します。 max_bags
の値が4
と等しい任意の行からname
およびmax_bags
の値を返します。
- SELECT name, max_bags
- FROM volunteers
- WHERE max_bags = 4;
1人のボランティアのmax_bags
値のみが4
と正確に等しいため、クエリはそのボランティアのレコードのみを返します。
Output+---------+----------+
| name | max_bags |
+---------+----------+
| Juanita | 4 |
+---------+----------+
1 row in set (0.00 sec)
検索条件述部の文字列値を評価することもできます。 次のクエリは、name
の値が'Wanda'
と等しいすべての行のvol_id
およびname
の値を返します。
- SELECT vol_id, name
- FROM volunteers
- WHERE name = 'Wanda';
Wanda
という名前のボランティアは1人だけなので、クエリはその行からの情報のみを返します。
Output+--------+-------+
| vol_id | name |
+--------+-------+
| 4 | Wanda |
+--------+-------+
1 row in set (0.00 sec)
繰り返しになりますが、このセクションの例はすべて、同じ検索条件演算子(等号)を使用してデータをフィルター処理します。 ただし、さまざまな述語を記述できる他の種類の演算子がいくつかあり、クエリが返す情報を高度に制御できます。
SQL標準では、18種類の述語が定義されていますが、すべてのRDBMSですべてがサポートされているわけではありません。 最も一般的に使用される5つの検索条件述語タイプとそれらが使用する演算子は次のとおりです。
Compareson :比較述語は、ある値の式を別の式と比較します。 クエリでは、ほとんどの場合、これらの値の式の少なくとも1つが列の名前である場合があります。 6つの比較演算子は次のとおりです。
=
:2つの値が等しいかどうかをテストします<>
:2つの値が同等でないかどうかをテストします<
:最初の値が2番目の値よりも小さいかどうかをテストします>
:最初の値が2番目の値より大きいかどうかをテストします<=
:最初の値が2番目の値以下かどうかをテストします>=
:最初の値が2番目の値以上かどうかをテストします
Null :IS NULL
演算子を使用する述語は、特定の列の値がNullであるかどうかをテストします Range :範囲述語は、BETWEEN
演算子を使用して1つの値式が他の2つの間にあるMembership:このタイプの述語は、IN
演算子を使用して、値が特定のセットのメンバーであるかどうかをテストしますパターンマッチ:パターン一致する述語は、LIKE
演算子を使用して、値がワイルドカード値を含む文字列パターンと一致するかどうかをテストします
これらの述語タイプのそれぞれについて詳しく説明することは、このチュートリアルの範囲を超えています。 ただし、それらについて詳しく知りたい場合は、次のガイドを確認することをお勧めします。
WHERE
句の一般的な詳細については、SQLでのWHERE句の使用方法に関するガイドを参照してください。
ORDER BY
を使用したクエリ結果の並べ替え
クエリによって、直感的でない方法や特定のニーズに合わない方法で情報が返される場合があります。 クエリステートメントの最後にORDER BY
句を追加することで、クエリ結果を並べ替えることができます。
ORDER BY
句を使用したクエリの一般的な構文は次のとおりです。
- SELECT columns_to_return
- FROM table_to_query
- ORDER BY column_name;
これがどのように機能するかを説明するために、どのボランティアがmax_bags
の値が最も高いかを知りたいとします。 volunteers
テーブルからname
およびmax_bags
の値を返す次のクエリを実行できます。
- SELECT name, max_bags
- FROM volunteers;
ただし、このクエリは、各行が追加された順序で結果セットを並べ替えます。
Output+------------+----------+
| name | max_bags |
+------------+----------+
| Gladys | 5 |
| Catherine | 2 |
| Georgeanna | 1 |
| Wanda | 1 |
| Ann | 7 |
| Juanita | 4 |
| Georgia | 3 |
+------------+----------+
7 rows in set (0.00 sec)
このような比較的小さなデータセットの場合、結果セットの順序はそれほど重要ではなく、この結果セットのmax_bags
値をスキャンして、最も高い値を見つけることができます。 ただし、大量のデータを処理する場合、これはすぐに面倒になる可能性があります。
代わりに、同じクエリを実行できますが、各行のmax_bags
値に基づいて結果セットを並べ替えるORDER BY
句を追加します。
- SELECT name, max_bags
- FROM volunteers
- ORDER BY max_bags;
Output+------------+----------+
| name | max_bags |
+------------+----------+
| Georgeanna | 1 |
| Wanda | 1 |
| Catherine | 2 |
| Georgia | 3 |
| Juanita | 4 |
| Gladys | 5 |
| Ann | 7 |
+------------+----------+
7 rows in set (0.00 sec)
この出力が示すように、ORDER BY
句を含むSQLクエリのデフォルトの動作は、指定された列の値を昇順(昇順)で並べ替えることです。 DESC
キーワードをORDER BY
句に追加することで、この動作を変更して降順で並べ替えることができます。
- SELECT name, max_bags
- FROM volunteers
- ORDER BY max_bags DESC;
Output+------------+----------+
| name | max_bags |
+------------+----------+
| Ann | 7 |
| Gladys | 5 |
| Juanita | 4 |
| Georgia | 3 |
| Catherine | 2 |
| Georgeanna | 1 |
| Wanda | 1 |
+------------+----------+
7 rows in set (0.00 sec)
結論
このガイドを読むことで、基本的なクエリの記述方法と、クエリ結果セットのフィルタリングと並べ替えの方法を学びました。 ここに示すコマンドはほとんどのリレーショナルデータベースで機能するはずですが、すべてのSQLデータベースが独自の言語実装を使用していることに注意してください。 各コマンドとそのオプションの完全なセットの詳細については、DBMSの公式ドキュメントを参照してください。
SQLの操作について詳しく知りたい場合は、SQLの使用方法に関するこのシリーズの他のチュートリアルを確認することをお勧めします。