Важно
Перевод - это работа сообщества : ссылка:Вы можете присоединиться. Эта страница в настоящее время переводится |прогресс перевода|.
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. Фильтрация
Часто вы не хотите видеть все записи в базе данных - особенно если их тысячи, а вас интересует только одна или две.
Вот пример числового фильтра, который возвращает только объекты, чей 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. Присоединяйтесь к
А что, если вы хотите увидеть данные человека и название его улицы вместо идентификатора? Для этого нужно объединить две таблицы в одном запросе. Давайте рассмотрим пример:
select people.name, house_no, streets.name
from people,streets
where people.street_id=streets.id;
Примечание
При объединении всегда указываются две таблицы, из которых берется информация, в данном случае люди и улицы. Также необходимо указать, какие два ключа должны совпадать (внешний ключ и первичный ключ). Если этого не сделать, вы получите список всех возможных комбинаций людей и улиц, но не сможете узнать, кто на самом деле живет на этой улице!
Вот как будет выглядеть правильный вывод:
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. Агрегатные запросы
Одной из мощных особенностей базы данных является возможность обобщать данные в таблицах. Такие сводки называются агрегированными запросами. Вот типичный пример, который позволяет узнать, сколько объектов «Люди» содержится в нашей таблице «Люди»:
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 и streets.name). Это необходимо сделать, если имя поля неоднозначно (т. е. не уникально для всех таблиц в базе данных).
15.4.6. В заключение
Вы уже видели, как использовать запросы для возврата данных из базы данных таким образом, чтобы извлечь из них полезную информацию.
15.4.7. Что дальше?
Далее вы увидите, как создавать представления на основе написанных вами запросов.