PostgreSQLとは何ですか?

PostgreSQLは、SQLクエリ言語を利用したオープンソースのデータベース管理システムです。 PostgreSQL、または単に「Postgres」は、Webサイトやその他のアプリケーションのデータストレージのニーズを処理できるため、VPSサーバー上で非常に便利なツールです。

このガイドでは、PostgreSQLデータベースにクエリを実行する方法を検討します。 これにより、Postgresに、探している基準に一致する管理対象のすべてのデータを返すように指示できます。

このチュートリアルは、マシンにPostgresがインストールされていることを前提としています。 Ubuntu 12.04を使用しますが、最新のLinuxディストリビューションであればすべて機能するはずです。

PostgreSQLにログインします

インターネットから使用するサンプルデータベースをダウンロードします。

まず、次のコマンドを使用してデフォルトのPostgresユーザーにログインします。

sudo su - postgres

次のように入力して、データベースファイルを取得します。

wget http://pgfoundry.org/frs/download.php/527/world-1.0.tar.gz

gzipで圧縮されたアーカイブを抽出し、コンテンツディレクトリに移動します。

tar xzvf world-1.0.tar.gz
cd dbsamples-0.1/world

データベースを作成して、ファイル構造を次の場所にインポートします。

createdb -T template0 worlddb

最後に、新しく作成されたデータベースへの入力として.sqlファイルを使用します。

psql worlddb < world.sql

これで、新しく作成した環境にログインする準備が整いました。

psql worlddb

PostgreSQLでデータを表示する方法

始める前に、インポートしたばかりのデータの種類について理解しましょう。 テーブルのリストを表示するには、次のコマンドを使用できます。

\d+
                         List of relations
 Schema |      Name       | Type  |  Owner   |  Size  | Description 
--------+-----------------+-------+----------+--------+-------------
 public | city            | table | postgres | 264 kB | 
 public | country         | table | postgres | 48 kB  | 
 public | countrylanguage | table | postgres | 56 kB  | 
(3 rows)

ここには3つのテーブルがあります。 「city」テーブルを構成する列を確認したい場合は、次のコマンドを発行できます。

\d city
          Table "public.city"
   Column    |     Type     | Modifiers 
-------------+--------------+-----------
 id          | integer      | not null
 name        | text         | not null
 countrycode | character(3) | not null
 district    | text         | not null
 population  | integer      | not null
Indexes:
    "city_pkey" PRIMARY KEY, btree (id)
Referenced by:
    TABLE "country" CONSTRAINT "country_capital_fkey" FOREIGN KEY (capital) REFERENCES city(id)

各列に関する情報、およびこのテーブルと他のデータセットとの関係を確認できます。

PostgreSQLでSelectを使用してデータをクエリする方法

「select」ステートメントを使用して、Postgresからの情報を照会(要求)します。 これらのステートメントは、次の一般的な構文を使用します。

SELECT columns_to_return FROM table_name;

たとえば、「\ dcountry」を発行すると、「country」テーブルに多くの列があることがわかります。 次のようにして、国の名前とその国が存在する大陸をリストするクエリを作成できます。

SELECT name,continent FROM country;
                     name                     |   continent   
----------------------------------------------+---------------
 Afghanistan                                  | Asia
 Netherlands                                  | Europe
 Netherlands Antilles                         | North America
 Albania                                      | Europe
 Algeria                                      | Africa
 American Samoa                               | Oceania
 Andorra                                      | Europe
 . . .

特定のテーブルのすべての列を表示するには、アスタリスク(*)ワイルドカード文字を使用できます。 これは「すべての可能性に一致する」ことを意味し、その結果、すべての列を返します。

SELECT * FROM city;
  id  |               name                | countrycode |           district            | population 
------+-----------------------------------+-------------+-------------------------------+------------
    1 | Kabul                             | AFG         | Kabol                         |    1780000
    2 | Qandahar                          | AFG         | Qandahar                      |     237500
    3 | Herat                             | AFG         | Herat                         |     186800
    4 | Mazar-e-Sharif                    | AFG         | Balkh                         |     127800
    5 | Amsterdam                         | NLD         | Noord-Holland                 |     731200
    6 | Rotterdam                         | NLD         | Zuid-Holland                  |     593321
    7 | Haag                              | NLD         | Zuid-Holland                  |     440900
. . .
Here, we see the "city" table in its entirety.

PostgreSQLでのクエリ結果の順序付け

「orderby」句を使用して、クエリの結果を整理できます。 これにより、返されるデータの並べ替え順序を指定できます。

「orderby」句は、通常のselectステートメントの後にあります。 これは一般的な構文です:

SELECT columns FROM table ORDER BY column_names [ ASC | DESC ];

国の表から国と大陸を選択し、大陸ごとに並べ替える場合は、次のように指定できます。

SELECT name,continent FROM country ORDER BY continent;
                     name                     |   continent   
----------------------------------------------+---------------
 Algeria                                      | Africa
 Western Sahara                               | Africa
 Madagascar                                   | Africa
 Uganda                                       | Africa
 Malawi                                       | Africa
 Mali                                         | Africa
 Morocco                                      | Africa
 Côte d\u0092Ivoire                           | Africa
 . . .

ご覧のとおり、デフォルトでは、orderステートメントはデータを昇順で整理します。 これは、文字付きの組織の先頭から始まり、数値検索の最小数であることを意味します。

ソート順を逆にしたい場合は、「orderby」列宣言の後に「desc」と入力できます。

SELECT name,continent FROM country ORDER BY continent DESC;
                     name                     |   continent   
----------------------------------------------+---------------
 Paraguay                                     | South America
 Bolivia                                      | South America
 Brazil                                       | South America
 Falkland Islands                             | South America
 Argentina                                    | South America
 Venezuela                                    | South America
 Guyana                                       | South America
 Chile                                        | South America
. . .

複数の列で並べ替えることもできます。 プライマリソートフィールドを設定し、次に、複数のレコードがプライマリソートフィールドに同じ値を持っている場合に使用される追加のソートフィールドを設定できます。

たとえば、大陸で並べ替えてから国で並べ替えると、各大陸の国のレコードのアルファベット順のリストを取得できます。

SELECT name,continent FROM country ORDER BY continent,name;
                     name                     |   continent   
----------------------------------------------+---------------
 Angola                                       | Africa
 Burundi                                      | Africa
 Benin                                        | Africa
 Burkina Faso                                 | Africa
 Botswana                                     | Africa
 Central African Republic                     | Africa
 Côte d\u0092Ivoire                           | Africa
 Cameroon                                     | Africa
 Congo, The Democratic Republic of the        | Africa
. . .

これで、2つの列にアルファベット順に並べ替えられます。

PostgreSQLでのクエリ結果のフィルタリング

必要な列を指定してテーブルから特定の情報のみを選択する方法を学びましたが、Postgresはよりきめ細かいフィルタリングメカニズムを提供します。

「where」句を含めることで結果をフィルタリングできます。 where句の後に、受け取りたい結果の説明が続きます。

たとえば、米国内のすべての都市を選択する場合、3桁の国コードが「USA」である都市の名前を返すようにPostgresに指示できます。

SELECT name FROM city WHERE countrycode = 'USA';
          name           
-------------------------
 New York
 Los Angeles
 Chicago
 Houston
 Philadelphia
 Phoenix
 San Diego
 Dallas
 San Antonio
. . .

上記のUSAのような文字列値は、Postgresによって正しく解釈されるように一重引用符で囲む必要があります。

前のクエリでは、「=」を使用して、列の値が式の右側に指定された値と完全に一致するかどうかを比較しました。 「like」比較演算子を使用すると、より柔軟に検索できます。

like演算子は、「_」を1文字に一致するワイルドカードとして使用し、「%」を0個以上の文字に一致するワイルドカードとして使用できます。

フィルタリング用語を「and」または「or」と組み合わせることもできます。 いくつかのフィルタリングを組み合わせて、名前が「N」で始まる米国の都市を見つけましょう。

SELECT name FROM city WHERE countrycode = 'USA' AND name LIKE 'N%';
        name        
--------------------
 New York
 Nashville-Davidson
 New Orleans
 Newark
 Norfolk
 Newport News
 Naperville
 New Haven
 North Las Vegas
 Norwalk
 New Bedford
 Norman
(12 rows)
We can, of course, sort these results just like with regular, unfiltered select data.
SELECT name FROM city WHERE countrycode = 'USA' AND name LIKE 'N%' ORDER BY name;
        name        
--------------------
 Naperville
 Nashville-Davidson
 Newark
 New Bedford
 New Haven
 New Orleans
 Newport News
 New York
 Norfolk
 Norman
 North Las Vegas
 Norwalk
(12 rows)

PostgreSQLの高度な選択操作

より複雑なクエリをいくつか調べます。 次のことを考慮してください。

SELECT country.name AS country,city.name AS capital,continent FROM country JOIN city ON country.capital = city.id ORDER BY continent,country;
                country                |              capital              |   continent   
---------------------------------------+-----------------------------------+---------------
 Algeria                               | Alger                             | Africa
 Angola                                | Luanda                            | Africa
 Benin                                 | Porto-Novo                        | Africa
 Botswana                              | Gaborone                          | Africa
 Burkina Faso                          | Ouagadougou                       | Africa
 Burundi                               | Bujumbura                         | Africa
 Cameroon                              | Yaoundé                           | Africa
 Cape Verde                            | Praia                             | Africa
 Central African Republic              | Bangui                            | Africa
 Chad                                  | N´Djaména                         | Africa
. . .

このクエリにはいくつかの異なる部分があります。 最後から始めて、逆方向に作業していきましょう。

ステートメントの「orderby」セクション(ORDER BY continent、country)はよく知っているはずです。

ステートメントのこのセクションは、最初に大陸に基づいて並べ替え、次に国の列で大陸の値が一致するエントリを並べ替えるようにPostgresに指示します。

次の部分であるテーブルの仕様を説明するために、テーブルの結合について学習します。

結合を使用してPostgreSQLの複数のテーブルからデータを選択する

Postgresでは、「join」句を使用して、関連するさまざまなテーブルからデータを選択できます。 テーブルには、同じデータを参照できる列がそれぞれある場合に関連します。

サンプルデータベースでは、「country」テーブルと「city」テーブルがいくつかのデータを共有しています。 次のように入力すると、「country」テーブルが「city」テーブルを参照していることがわかります。

\d country
. . .
. . .
Foreign-key constraints:
    "country_capital_fkey" FOREIGN KEY (capital) REFERENCES city(id)
. . .
. . .

このステートメントは、「country」テーブル内の「capital」列が「city」テーブル内の「id」列への参照であることを示しています。 これは、これらの列の値を一致させることにより、これら2つのテーブルを1つの巨大なテーブルとしてほぼ扱うことができることを意味します。

クエリでは、テーブルの選択には「FROM country JOIN city ON country.capital=city.id」と表示されます。

このステートメントでは、Postgresに両方のテーブルから情報を返すように指示しています。 「join」ステートメントは、「内部結合」とも呼ばれるデフォルトの結合を指定します。

内部結合は、両方のテーブルに存在する情報を返します。 たとえば、外部キーとして明示的に関連付けられていない列を照合している場合、一方のテーブルの値がもう一方のテーブルと一致しないという状況に遭遇する可能性があります。 これらは、通常の結合を使用して返されることはありません。

「on」キーワードの後のセクションは、Postgresがデータの関連性を認識できるように、テーブルが共有する列を指定します。 この情報は、以下を指定することによって提供されます。

table_name.column_name

この例では、両方のテーブルで値が一致するレコードを選択しています。ここで、countryテーブルのcapital列をcityテーブルのid列と比較する必要があります。

PostgreSQLでのテーブル結合の命名選択基準

これで、クエリステートメントの最初になりました。 列を選択する部分。 この部分は、前のセクションに基づいて解読するのがかなり簡単であるはずです。

両方のテーブルに存在する列名を選択しようとしている場合は、列が含まれているテーブルに名前を付ける必要があります。

たとえば、countryテーブルとcityテーブルの両方から「name」列を選択しました。 「table_name」を省略した場合。 選択の一部では、一致があいまいになり、Postgresはどのデータを返すかわかりません。

名前の衝突がある場合にどのテーブルから選択するかを明示することで、この問題を回避します。 読みやすさを維持するために、名前が一意であるかどうかに関係なく、テーブルに名前を付けると役立つ場合があります。、/ p>

結論

これで、クエリを作成する方法の基本的な考え方がわかりました。 これにより、さまざまなソースから特定のデータを返すことができます。 これは、このテクノロジーを中心としたアプリケーションやインタラクティブなWebページを構築または使用するのに役立ちます。