Пусть требуется выбрать данные о тех изданиях, цена которых больше среднего значения. Это можно сделать с помощью следующего запроса:
SELECT *
FROM editions
WHERE cost > (SELECT AVG(cost)
FROM editions);
В данном запросе сначала выполняется подзапрос (SELECT AVG(cost) FROM editions). Он возвращает единственное значение (а не набор записей) – среднее значение столбца cost. Точнее, данный подзапрос возвращает единственную запись, содержащую единственное поле. Далее выполняется внешний запрос, который выводит все столбцы таблицы editions и записи, в которых значение столбца cost больше значения, полученного с помощью подзапроса. Таким образом, сначала выполняется подзапрос, а затем внешний запрос, использующий результат подзапроса.
Работа со списком значений из одного столбца
Рассмотрим применение подзапросов, возвращающих не единственное значение, а список значений из одного столбца. Получим все данные о покупках, которые совершили покупатели с именем James:
SELECT *
FROM shipments
WHERE customer_id IN (SELECT id
FROM customers
WHERE first_name = 'James');
Сначала выполняется подзапрос, возвращающий список идентификаторов покупателей, которых зовут James. Далее, внешний запрос сравнивает значение поля customer_id из каждой записи таблицы shipments с полученным списком. Если сравнение успешно (сравниваемое значение имеется в списке), то запись о покупке добавляется в итоговый набор.
Теперь сформулируем запрос, возвращающий коды ISBN, книги с которыми никто не покупал:
SELECT isbn
FROM editions
WHERE isbn NOT IN (SELECT isbn
FROM shipments);
Чтобы секция IN сравнивала несколько полей, следует сгруппировать их имена в круглые скобки в секции WHERE непосредственно перед IN. Сгруппированные поля должны соответствовать полям целевого списка подзапроса как по количеству, так и по типу данных.
|
|
Например, получим данные обо всех книгах в бумажной обложке, отсутствующих на складе:
SELECT isbn, cost, retail
FROM stock
WHERE (isbn, stock) IN (SELECT isbn, 0
FROM editions
WHERE type = 'p');
Подзапрос к таблице editions группирует поле isbn с целочисленной константой 0 для всех книг в бумажной обложке. Возвращаемые подзапросом записи сравниваются с полем isbn и stock таблицы stock с использованием ключевого слова IN.
Пусть подзапрос возвращает несколько записей. Тогда чтобы в условии внешнего оператора WHERE можно было использовать операторы сравнения, требующие единственного значения, используются кванторы ALL и SOME (ANY).
Получим список книг с самой высокой ценой:
SELECT *
FROM stock AS s1
WHERE s1.retail >= ALL (SELECT s2.retail
FROM stock AS s2);
Подзапрос SELECT s2.retail FROM stock AS s2 возвращает список цен всех книг. Выражение >= ALL означает, что внешний запрос должен вернуть только те записи, в которых значение столбца retail больше или равен каждого значения, возвращенного вложенным подзапросом.
Итоговый набор книг будет иным, если вместо квантора ALL применить SOME или ANY:
SELECT *
FROM stock AS s1
WHERE s1.retail > SOME (SELECT s2.retail
FROM stock AS s2);
Этот запрос вернет список книг, цена которых выше цены хотя бы одной какой-либо книги.
Работа с набором записей
Вообще говоря, подзапрос может быть вставлен не только в операторы WHERE и HAVING, но и в оператор FROM. В этом случае подзапросу необходимо присвоить псевдоним.
Допустим, что имеется таблица Рейсы (Начальный_пункт, Конечный_пункт), содержащая сведения о том, из каких пунктов и в какие можно попасть с помощью того или иного авиарейса. Получим сведения, в какие пункты можно попасть, сделав не более одной пересадки (т.е. без пересадок или с одной пересадкой), только из пункта А:
|
|
SELECT *
FROM
(SELECT Начальный_пункт, Конечный_пункт
FROM Рейсы
UNION
SELECT T1.Начальный_пункт, T2.Конечный_пункт
FROM Рейсы T1, Рейсы T2
WHERE T1.Конечный_пункт = T2.Начальный_пункт) AS T
WHERE T.Начальный_пункт = 'A';
Связанные подзапросы
Связанные (коррелированные) подзапросы позволяют выразить более сложные вопросы относительно сведений, хранящихся в базе данных. При выполнении запросов, содержащих связанные подзапросы, нет такого четкого разделения по времени выполнения между подзапросом и запросом, как в случае простых подзапросов. В случае простых подзапросов сначала выполняется подзапрос, а затем содержащий его запрос. При наличии связанного подзапроса порядок выполнения запроса в целом иной. Основной признак связанного подзапроса заключается в том, что он не может быть выполнен самостоятельно, вне всякой связи с основным запросом. Формально этот признак обнаруживается в выражении сложного запроса следующим образом: подзапрос ссылается на таблицу, которая упоминается в основном запросе.
Рассмотрим некоторый абстрактный и, в то же время, типичный запрос, содержащий связанный подзапрос:
SELECT T1.A
FROM T1
WHERE T1.B = (SELECT T2.B
FROM T2
WHERE T2.C = T1.C);
Данный запрос на выборку данных содержит подзапрос, сформулированный в выражении, размещенном в основном запросе после ключевого слова WHERE. Запрос в целом использует две таблицы: T1 и T2, в которых есть столбцы с одинаковыми именами B и C и одинаковыми типами. Подзапрос (SELECT T2.B FROM T2 WHERE T2.C = T1.C) обращается к этим же таблицам. Поскольку одна из таблиц (T1) фигурирует как в подзапросе, так и во внешнем запросе, то подзапрос нельзя выполнить самостоятельно, вне связи с внешним запросом. Поэтому выполнение запроса в целом (т.е. внешнего запроса) происходит следующим образом:
|
|
1. Сначала выделяется первая запись из таблицы T1, указанной в операторе FROM внешнего запроса (вся запись таблицы T1, а не только значение столбца A). Эта запись называется текущей. Значения столбцов для этой записи доступны и могут быть использованы в подзапросе.
2. Затем выполняется подзапрос, который возвращает список значений столбца B таблицы T2 в тех записях, в которых значение столбца C равно значению столбца C из таблицы T1.
3. Будем считать, что в этом примере подзапрос возвращает единственное значение. Если это не так, то потребуется использование, например, предикатов вместо оператора сравнения (=). Теперь выполняется оператор WHERE основного запроса. Если значение столбца B в текущей (выделенной) записи таблицы T1 равно значению, возвращенному подзапросом, то эта запись выделяется внешним запросом.
4. Оператор SELECT внешнего запроса выполняет проверку условия своего оператора WHERE. Если оно истинно, то значение столбца A текущей записи таблицы T1 помещается в результатную таблицу, в противном случае запись игнорируется. Затем происходит переход к следующей записи таблицы T1. Теперь для нее выполняется подзапрос. Аналогичным образом все описанное происходит для каждой записи таблицы T1.
Получим, например, список покупателей, когда-либо делавших покупки:
SELECT last_name, first_name
FROM customers
WHERE (SELECT count(isbn)
FROM shipments
WHERE customer_id = customers.id) >= 1;
Как известно, запрос возвращает одну или несколько записей либо не возвращает ничего. Рассмотрим пример, в котором требуется проверка существования записей. Так, иногда требуется выборка записей из одной таблицы при условии, что в другой таблице существует хотя бы одна соответствующая запись.
Итак, получим список покупателей, когда-либо делавших покупки:
SELECT last_name, first_name
FROM customers
WHERE EXISTS (SELECT isbn
FROM shipments
WHERE customer_id = customers.id);
Аналогично, чтобы получить список покупателей, никогда не делавших покупки, можно воспользоваться следующим запросом:
SELECT last_name, first_name
FROM customers
WHERE NOT EXISTS (SELECT isbn
FROM shipments
WHERE customer_id = customers.id);
Предикат UNIQUE имеет такой же смысл, как и EXISTS, но при этом для его истинности требуется, чтобы все записи в результатной таблице не только существовали, но и были уникальны (т.е. не повторялись).
Предикат DISTINCT почти такой же, как и UNIQUE. Отличие этих предикатов обнаруживается применительно к значениям NULL. Так, если в результатной таблице все записи уникальны (предикат UNIQUE истинен), то и предикат DISTINCT тоже истинен. С другой стороны, если в результатной таблице имеются хотя бы две неопределенные записи, то предикат DISTINCT ложен, хотя предикат UNIQUE истинен.
Представления
При работе с SQL нередко возникают ситуации, когда один и тот же запрос приходится использовать повторно. В подобных ситуациях обычно используются представления (views). Представления могут строиться на основе как простых и стандартных запросов к одной таблице, так и чрезвычайно сложных запросов, в которых задействовано несколько таблиц.
Представление можно рассматривать как хранимый запрос, на основе которого создается объект базы данных. Этот объект очень похож на таблицу, но в его содержимом динамически отражается состояние только тех записей, которые были заданы при создании представления. Представления не являются физическими объектами хранения данных. Данные в представлениях, подобно в ответах на запрос SELECT, просто выбираются из таблиц базы данных, т.е. представляются в том или ином виде. В действительности за представлением стоит скрытый SQL-запрос. Работать с представлением можно как с обычной таблицей. Однако любой запрос к представлению в действительности инициирует скрытый запрос, который комбинируется с пользовательским.
Представления, будучи созданы, могут быть доступны многим пользователям и существуют в базе данных до тех пор, пока не будут принудительно удалены.
Представления создаются командой
CREATE VIEW имяПредставления AS
запросSELECT;
Как и обычной таблице базы данных, представлению присваивается имя, которое не должно совпадать ни с одним именем таблиц. За ключевым словом AS следует SQL-выражение запроса на выборку данных.
Пусть требуется получить для каждого наименования книги количество ее покупок и дату последней покупки:
SELECT title, count(*) AS num_shipped,
max(ship_date) AS last_date
FROM shipments JOIN editions USING (isbn)
JOIN books ON (book_id = books.id)
GROUP BY title
ORDER BY num_shipped DESC;
Запрос получается слишком громоздким, и часто вводить его вручную нежелательно. Создадим на базе этого запроса представление:
CREATE VIEW recent_shipments AS
SELECT title, count(*) AS num_shipped,
max(ship_date) AS last_date
FROM shipments JOIN editions USING (isbn)
JOIN books ON (book_id = books.id)
GROUP BY title
ORDER BY num_shipped DESC;
Представления значительно упрощают получение нужных данных. Вместо того, чтобы вводить длинный запрос, достаточно ввести простую команду SELECT:
SELECT *
FROM recent_shipments;
Получим названия книг, которые были куплены в наибольшем количестве:
SELECT title
FROM recent_shipments
WHERE num_shipped = (SELECT max(num_shipped)
FROM recent_shipments)
ORDER BY title;
Некоторые современные СУБД поддерживают операции изменения содержимого представлений – вставку, изменение и удаление записей. Однако это возможно лишь в том достаточно редком случае, когда имеется однозначное соответствие между столбцами представления и столбцами таблицы базы данных. Для этого в операторе SELECT представления не должно использоваться более одной таблицы, вычисляемых выражений, группировки, ключевого слова DISTINCT и т.п.
В PostgreSQL при попытке вызова команд INSERT, UPDATE или DELETE для представления происходит ошибка.
Представления удаляются из базы данных командой
DROP VIEW имяПредставления;
При удалении представления данные сохраняются без изменения. Теряется только запрос, на котором основано представление.
Лабораторная работа 5
Следующие запросы к базе данных booktown рекомендуется набирать в файлах (по одному запросу в файле). Для хранения файлов можно использовать каталог D:\DB. Запустить запрос из файла можно командой \i имя_файла.
1. Пусть автор получает половину от стоимости (cost) каждой проданной книги. Определить прибыль каждого автора. Отсортировать по авторам.
2. Определить, сколько книг было на складе до продажи. Отсортировать по кодам isbn.
3. Список тем, нашедших отражение в произведениях только одного автора. Отсортировать по названию темы.
4. Список покупателей, купивших более одной книги, но все на одну и ту же тему. Отсортировать по покупателю.
5. Список покупателей, купивших книги и в твердой, и в бумажной обложках. Отсортировать по покупателям.
6. Список тем, книги по которым выдержали наибольшее количество изданий.
7. Название самой популярной у покупателей темы (тем).
8. Код isbn, автор и издатель бестселлера(ов) – самой покупающейся книги.
9. Издатель, средняя цена книг которого самая дешевая.
10. Для каждой темы определить, сколько книг издано в жесткой и сколько – в бумажной обложках. Отсортировать по теме.
11. Фамилии авторов, писавших на одну и ту же тему несколько раз. Отсортировать по фамилии автора.
12. Список покупателей, покупавших одно и то же произведение несколько раз. Отсортировать по покупателю.
13. Список авторов, не написавших ни одной книги. Отсортировать по автору.
14. Названия тем, которые никогда не были изданы. Упорядочить по названию темы.
15. Коды ISBN книг с наименьшей разницей между ценой закупки и ценой продажи.
16. Название издательства (издательств), выпустивших наибольшее количество книг (с различными isbn).
17. Список издателей, книги которых хуже всего продаются.
18. Все различные пары кодов isbn, относящихся к одной и той же книге.
19. Все различные пары авторов, писавших на одну и ту же тему.
20. Все различные пары авторов, никогда не писавших на одну и ту же тему.