重要
翻訳は あなたが参加できる コミュニティの取り組みです。このページは現在 85.11% 翻訳されています。
15.4. レッスン: クエリ
When you write a SELECT ... command it is commonly known as a query -
you are interrogating the database for information.
このレッスンの目的： 有用な情報を返すクエリを作成する方法を学習します。
注釈
If you did not do so in the previous lesson, add the following people
objects to your people table. If you receive any errors related to
foreign key constraints, you will need to add the 'Main Road' object to your
streets table first
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');
15.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)
15.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)
Here we used the LIKE clause to find all names with an s in them.
You'll notice that this query is case-sensitive, so the Sally Norman entry
has not been returned.
If you want to search for a string of letters regardless of case, you can do a
case in-sensitive search using the ILIKE clause:
select name, house_no from people where name ilike '%r%';
name | house_no
--------------+----------
Roger Jones | 33
Sally Norman | 83
(2 rows)
That query returned every people object with an r or R in their
name.
15.4.3. 結合
IDの代わりに人の詳細とその通りの名前を確認したい場合は？そのためには、単一のクエリ中で2つのテーブルを結合する必要があります。例を見てみましょう:
select people.name, house_no, streets.name
from people,streets
where people.street_id=streets.id;
注釈
結合によって、情報が由来する2つのテーブルを、この場合はpeopleとstreetsですが、常に宣言することになります。また、どの2つのキー（外部キー＆主キー）が一致しなければならないかを指定する必要があります。それを指定しない場合は、peopleとstreetsのすべての可能な組み合わせの一覧が得られますが、誰が実際にその通りに住んでいるか知るすべはありません！
正しい出力はこのように見えるでしょう:
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)
結合については、後でより複雑なクエリを作成するときに再訪します。それらは二つ以上のテーブルからの情報を組合わせるための簡単な方法を提供するとだけ覚えておいてください。
15.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)
Now let's show you a sub-selection on this data. We want to show only people who
live in street_id number 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)
これは非常に単純な例でありこの小さなデータセットでは不要ですが、大規模かつ複雑なデータセットを照会する際に有用かつ重要な副選択をする方法を示しています。
15.4.5. クエリの集約
データベースの強力な機能の1つは、そのテーブル内のデータを要約する能力です。これらの要約は集計クエリと呼ばれます。これは、peopleオブジェクトがpeopleテーブルに何人いるかを教えてくれる代表的な例です:
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)
注釈
Because we have not used an ORDER BY clause, the order of your
results may not match what is shown here.
やってみよう: ★★☆ （中級レベル）
通りの名前で人を要約し、street_idsの代わりに実際の通りの名前を表示してください。
答え
使うべき正しいSQL文はこちらです:
select count(people.name), streets.name
from people, streets
where people.street_id=streets.id
group by streets.name;
結果:
count | name
------+-------------
1 | Low Street
2 | High street
1 | Main Road
(3 rows)
フィールド名の前にテーブル名を付けていることにお気づきでしょうか（people.nameやstreet.nameなど）。これは、フィールド名があいまいな場合（つまり、データベース内のすべてのテーブルで一意でない場合）に使う必要があります。
15.4.6. 結論
クエリを使用して、データベース内の有用な情報を抽出できるような形でデータを返す方法を見てきました。
15.4.7. 次は?
次は今書いたクエリからビューを作成する方法について説明します。