16.4. Lesson: 検索¶

SELECT ... コマンドを書くとき、これは一般的にはクエリと言われますが、情報のデータベースに問い合わせしています。

このレッスンの目的： 有用な情報を返すクエリを作成する方法を学習します。

ノート

前のレッスンでそうしなかった場合は、以下の人々オブジェクトを people テーブルに追加します。外部キー制約に関連した何らかのエラーを受け取る場合は、まず街のテーブルに「主要道路」オブジェクトを追加する必要があります

insert into people (name,house_no, street_id, phone_no)
          values ('Joe Bloggs',3,2,'072 887 23 45');
insert into people (name,house_no, street_id, phone_no)
          values ('Jane Smith',55,3,'072 837 33 35');
insert into people (name,house_no, street_id, phone_no)
          values ('Roger Jones',33,1,'072 832 31 38');
insert into people (name,house_no, street_id, phone_no)
          values ('Sally Norman',83,1,'072 932 31 32');

16.4.1. 結果を並べ替える¶

自分の家の番号順に並べられた人々のリストを検索してみましょう:

select name, house_no from people order by house_no;

結果:

     name     | house_no
--------------+----------
 Joe Bloggs   |        3
 Roger Jones  |       33
 Jane Smith   |       55
 Sally Norman |       83
(4 rows)

結果の並べ替えは複数の列の値によってもできます:

select name, house_no from people order by name, house_no;

結果:

     name     | house_no
--------------+----------
 Jane Smith   |       55
 Joe Bloggs   |        3
 Roger Jones  |       33
 Sally Norman |       83
(4 rows)

16.4.2. フィルタリング¶

だいたいはデータベース内のすべてのレコード一つ一つを見たいとは思わないでしょう。特に何千ものレコードがあり、1つか2つを見たいだけの場合は。

これは house_no が50未満であるオブジェクトのみを返す数値フィルタの例です:

select name, house_no from people where house_no < 50;

      name     | house_no
  -------------+----------
   Joe Bloggs  |        3
   Roger Jones |       33
  (2 rows)

フィルタ（ WHERE 句を使用して定義される）はソート（ ORDER BY 句を使用して定義される）と組み合わせることができます:

select name, house_no from people where house_no < 50 order by house_no;

      name     | house_no
  -------------+----------
   Joe Bloggs  |        3
   Roger Jones |       33
  (2 rows)

テキストデータに基づいてもフィルタできます:

select name, house_no from people where name like '%s%';

      name     | house_no
  -------------+----------
   Joe Bloggs  |        3
   Roger Jones |       33
  (2 rows)

ここでは、 LIKE 句を使用し、 s を持つすべての名前を見つけます。このクエリは大文字小文字が区別されることに気づくでしょう、だから Sally Norman エントリは返されていません。

大文字小文字関係なく文字列を検索したい場合は、 ILIKE 句を使用すれば大文字小文字を無視した検索ができます:

select name, house_no from people where name ilike '%r%';

       name     | house_no
  --------------+----------
   Roger Jones  |       33
   Sally Norman |       83
  (2 rows)

そのクエリは、r か R を自分の名前に持つ people オブジェクトすべてを返します。

16.4.3. 結合¶

IDの代わりに人の詳細とその通りの名前を確認したい場合は？そのためには、単一のクエリ中で2つのテーブルを結合する必要があります。例を見てみましょう:

select people.name, house_no, streets.name
from people,streets
where people.street_id=streets.id;

ノート

結合によって、情報が由来する2つのテーブルを、この場合は人と通りですが、常に宣言することになります。また、どの2つのキー（外部キー＆主キー）が一致しなければならないかを指定する必要があります。それを指定しない場合は、人と街のすべての可能な組み合わせの一覧が得られますが、誰が実際にその通りに住んでいるか知るすべはありません！

正しい出力はこのように見えるでしょう:

     name     | house_no |    name
--------------+----------+-------------
 Joe Bloggs   |        3 | Low Street
 Roger Jones  |       33 | High street
 Sally Norman |       83 | High street
 Jane Smith   |       55 | Main Road
(4 rows)

結合については、後でより複雑なクエリを作成するときに再訪します。それらは二つ以上のテーブルからの情報を組合わせるための簡単な方法を提供するとだけ覚えておいてください。

16.4.4. 副選択¶

副選択は外部キー関係を介して連結されている別のテーブルからのデータに基づいて一つのテーブルからオブジェクトを選択できます。この場合は特定の街路に住む人々を見つけたいです。

まず、データをわずかに微調整しましょう:

insert into streets (name) values('QGIS Road');
insert into streets (name) values('OGR Corner');
insert into streets (name) values('Goodle Square');
update people set street_id = 2 where id=2;
update people set street_id = 3 where id=3;

それらの変更の後でデータを簡単に見てみましょう：前のセクションのクエリを再利用できます:

select people.name, house_no, streets.name
from people,streets
where people.street_id=streets.id;

結果:

     name     | house_no |    name
--------------+----------+-------------
 Roger Jones  |       33 | High street
 Sally Norman |       83 | High street
 Jane Smith   |       55 | Main Road
 Joe Bloggs   |        3 | Low Street
(4 rows)

それでは、このデータの副選択を表示してみましょう。 street_id 番号 1 に住む人だけを表示したい:

select people.name
from people, (
    select *
    from streets
    where id=1
  ) as streets_subset
where people.street_id = streets_subset.id;

結果:

     name
--------------
 Roger Jones
 Sally Norman
(2 rows)

これは非常に単純な例でありこの小さなデータセットでは不要ですが、大規模かつ複雑なデータセットを照会する際に有用かつ重要な副選択をする方法を示しています。

16.4.5. クエリの集約¶

データベースの強力な機能の1つは、そのテーブル内のデータを要約する能力です。これらの要約は集計クエリと呼ばれます。これは、人々オブジェクトが人々テーブルに何人いるかを教えてくれる代表的な例です:

select count(*) from people;

結果:

 count
-------
     4
(1 row)

人数を街路名で要約したい場合は、こうすることができます:

select count(name), street_id
from people
group by street_id;

結果:

 count | street_id
-------+-----------
     2 |         1
     1 |         3
     1 |         2
(3 rows)

ノート

ORDER BY 句を使用しなかったため、検索結果の順序はここに示したものとは一致しない場合があります。

16.4.5.1. Try Yourself ¶

通りの名前で人を要約し、street_idsの代わりに実際の通りの名前を表示してください。

結果を確認します

16.4.6. In Conclusion¶

クエリを使用して、データベース内の有用な情報を抽出できるような形でデータを返す方法を見てきました。

16.4.7. What’s Next?¶

次は今書いたクエリからビューを作成する方法について説明します。

Outdated version of the documentation. Find the latest one here.

内容リスト