序章

データベースを操作する上で最も基本的な部分の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で接続します。

  1. ssh sammy@your_server_ip

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

  1. mysql -u sammy -p

プロンプトから、queries_dbという名前のデータベースを作成します。

  1. CREATE DATABASE queries_db;

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

Output
Query OK, 1 row affected (0.01 sec)

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

  1. USE queries_db;
Output
Database changed

queries_dbを選択した後、その中にいくつかのテーブルを作成します。

このガイドで使用されている例に従うために、ニューヨーク市で公園のクリーンアップイニシアチブを実行していると想像してください。 このプログラムは、定期的にゴミを拾うことで、自宅近くの都市公園の清掃に取り組むボランティアで構成されています。 イニシアチブに参加すると、これらのボランティアはそれぞれ、毎週何個のゴミ袋を拾いたいかという目標を設定しました。 ボランティアの目標に関する情報を、次の5つの列を持つテーブルを持つSQLデータベースに格納することにしました。

  • vol_idintデータ型で表される各ボランティアの識別番号。 この列は、テーブルの主キーとして機能します。つまり、各値は、それぞれの行の一意の識別子として機能します。 主キーのすべての値は一意である必要があるため、この列にもUNIQUE制約が適用されます
  • name:最大20文字のvarcharデータ型を使用して表された各ボランティアの名前
  • park:各ボランティアがゴミを拾う公園の名前。最大20文字のvarcharデータ型を使用して表されます。 複数のボランティアが同じ公園のゴミを片付けることができることに注意してください
  • weekly_goalintタイプで表された、1週間に何袋のゴミを拾いたいかという各ボランティアの目標
  • max_bagsintとして表される、1週間に最も多くのゴミを拾った各ボランティアの個人記録。

次のCREATE TABLEステートメントを実行して、次の5つの列を持つvolunteersという名前のテーブルを作成します。

  1. CREATE TABLE volunteers (
  2. vol_id int UNIQUE,
  3. name varchar(20),
  4. park varchar(30),
  5. weekly_goal int,
  6. max_bags int,
  7. PRIMARY KEY (vol_id)
  8. );

次に、volunteersテーブルにサンプルデータをロードします。 次のINSERT INTO操作を実行して、プログラムのボランティアの7人を表す7行のデータを追加します。

  1. INSERT INTO volunteers
  2. VALUES
  3. (1, 'Gladys', 'Prospect Park', 3, 5),
  4. (2, 'Catherine', 'Central Park', 2, 2),
  5. (3, 'Georgeanna', 'Central Park', 2, 1),
  6. (4, 'Wanda', 'Van Cortland Park', 1, 1),
  7. (5, 'Ann', 'Prospect Park', 2, 7),
  8. (6, 'Juanita', 'Riverside Park', 1, 4),
  9. (7, 'Georgia', 'Prospect Park', 1, 3);

これで、ガイドの残りの部分に従い、SQLでクエリを作成する方法を学び始める準備が整いました。

必要なクエリコンポーネント:SELECTおよびFROM

SQLでは、ステートメントは、テーブルの作成、データの挿入または削除、列またはテーブルの構造の変更など、ある種のタスクを実行するデータベースシステムに送信される操作です。 query は、データベースに保持されているデータに関する情報を取得するSQLステートメントです。

クエリ自体は、テーブルに保持されている既存のデータを変更しません。 クエリの作成者が明示的に要求したデータに関する情報のみが返されます。 特定のクエリによって返される情報は、その結果セットと呼ばれます。 結果セットは通常、指定されたテーブルの1つ以上の列で構成され、結果セットで返される各列は1つ以上の情報行を保持できます。

SQLクエリの一般的な構文は次のとおりです。

  1. SELECT columns_to_return
  2. FROM table_to_query;

SQLステートメントは、さまざまなで構成されています。これらの句は、特定のキーワードと、これらのキーワードに必要な情報で構成されています。 少なくとも、SQLクエリでは、SELECT句とFROM句の2つの句を含めるだけで済みます。

:この構文例では、両方の句がそれぞれの行に記述されています。 ただし、次のように、任意のSQLステートメントを1行で記述することもできます。

  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列が取得されます。

  1. SELECT name
  2. FROM volunteers;

このクエリの結果セットは次のとおりです。

Output
+------------+ | name | +------------+ | Gladys | | Catherine | | Georgeanna | | Wanda | | Ann | | Juanita | | Georgia | +------------+ 7 rows in set (0.00 sec)

この操作はvolunteersテーブル全体を調べましたが、指定された列nameのみを返します。

次のクエリのように、各列の名前をコンマで区切ることにより、複数の列から情報を取得できます。 これにより、volunteersテーブルからvol_idname、およびpark列が返されます。

  1. SELECT park, name, vol_id
  2. 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テーブルのすべての列を返します。

  1. SELECT *
  2. 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列の値が返されます。

  1. SELECT park
  2. 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 ParkCentral Parkの2つの重複した値が含まれていることに注意してください。 複数のボランティアが同じ公園のゴミを片付けることができるので、これは理にかなっています。 ただし、列に保持されている一意の値のみを知りたい場合があります。 SELECTの後にDISTINCTキーワードを指定すると、重複する値を削除するクエリを発行できます。

次のクエリは、parks列のすべての一意の値を返し、重複を削除します。 DISTINCTキーワードが含まれていることを除いて、前のクエリと同じです。

  1. SELECT DISTINCT park
  2. 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列の両方を返す次のクエリを発行します。

  1. SELECT DISTINCT name, park
  2. 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句を含めることで、特定の行を除外できます。

  1. SELECT columns_to_return
  2. FROM table_to_query
  3. 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つの値式が同等かどうかをテストします。

  1. SELECT name
  2. FROM volunteers
  3. 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の値を返します。

  1. SELECT name, max_bags
  2. FROM volunteers
  3. 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の値を返します。

  1. SELECT vol_id, name
  2. FROM volunteers
  3. 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句を使用したクエリの一般的な構文は次のとおりです。

  1. SELECT columns_to_return
  2. FROM table_to_query
  3. ORDER BY column_name;

これがどのように機能するかを説明するために、どのボランティアがmax_bagsの値が最も高いかを知りたいとします。 volunteersテーブルからnameおよびmax_bagsの値を返す次のクエリを実行できます。

  1. SELECT name, max_bags
  2. 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句を追加します。

  1. SELECT name, max_bags
  2. FROM volunteers
  3. 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句に追加することで、この動作を変更して降順で並べ替えることができます。

  1. SELECT name, max_bags
  2. FROM volunteers
  3. 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の使用方法に関するこのシリーズの他のチュートリアルを確認することをお勧めします。