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ページを構築または使用するのに役立ちます。
