Важно
Перевод - это работа сообщества : ссылка:Вы можете присоединиться. Эта страница в настоящее время переводится |прогресс перевода|.
15.4. Урок: Запросы
Когда вы пишете команду SELECT ..., она обычно называется запросом - вы запрашиваете информацию у базы данных.
Цель этого урока: Научиться создавать запросы, которые будут возвращать полезную информацию.
Примечание
Если вы не сделали этого в предыдущем уроке, добавьте следующие объекты people в таблицу people. Если вы получите какие-либо ошибки, связанные с ограничениями внешних ключей, вам нужно будет сначала добавить объект „Main Road“ в таблицу streets
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)
Здесь мы использовали предложение 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)
Этот запрос возвращает все объекты people, в имени которых есть r или R.
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)
Теперь давайте покажем вам подвыборку по этим данным. Мы хотим показать только людей, которые живут на улице_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)
Хотя этот пример очень прост и не нужен для наших небольших наборов данных, он иллюстрирует, насколько полезными и важными могут быть подвыборки при запросах к большим и сложным наборам данных.
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)
Примечание
Поскольку мы не использовали предложение ORDER BY, порядок ваших результатов может не совпадать с тем, что показано здесь.
Попробуйте сами: ★★☆
Суммируйте людей по названиям улиц и показывайте фактические названия улиц вместо 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. Что дальше?
Далее вы увидите, как создавать представления на основе написанных вами запросов.