InvoiceDate
from
invoices
where
InvoiceId = 251)
Рис. 118
Возврат нескольких значений из подзапроса
До этого момента мы использовали только подзапросы для вычисления единственного значения, которое затем передается внешнему запросу. Также можно использовать подзапросы, возвращающие несколько записей. Допустим, руководство компании sTunes хочет получить только три конкретных счета. Чтобы их выбрать, нужен следующий запрос:
SELECT
InvoiceDate
FROM
invoices
WHERE
InvoiceId IN (251, 252, 255)
Рис. 119
В предыдущем запросе для возврата трех дат из таблицы invoices используется условие IN: 2012-01-09, 2012-01-22 и 2012-01-24. Теперь предположим, что нам нужна информация о покупках за эти три дня. Если необходимо выбрать все счета за эти три дня, мы можем написать новый запрос или просто использовать предыдущий в качестве подзапроса, например:
SELECT
InvoiceDate,
BillingAddress,
BillingCity
FROM
invoices
WHERE
InvoiceDate IN
(SELECT
InvoiceDate
from
invoices
where
InvoiceId in (251, 252, 255))
Преобразование существующего запроса в подзапрос полезно, когда вы «играете» со своими данными. Чтобы еще больше сузить область поиска, этот метод позволяет повторно использовать существующий запрос и изменять его.
Рис. 120
Подзапросы и условие DISTINCT
Другие примеры в этой главе показывают, что подзапросы очень полезны для сценариев, где надо просмотреть или сравнить запрос по условию, для вычисления которого требуется собственный запрос. Как вы уже знаете из главы 1, в каждой таблице есть одно уникальное поле, первичный ключ, содержащее уникальный номер для каждой записи, но другие поля могут содержать избыточную информацию. Для удобства работы с избыточной информацией стоит отфильтровать данные, чтобы они отображали только уникальные значения. В этом случае полезно условие DISTINCT. Лучше понять подзапросы и использование условия DISTINCT нам помогут таблицы tracks и invoice_items.
Таблица invoice_items показывает, какие треки какому счету соответствуют. Если мы создадим запрос, отображающий поля InvoiceId и TrackId, упорядоченные по TrackId, мы увидим, что определенные номера треков были заказаны несколько раз в разных счетах.
SELECT
InvoiceId,
TrackId
FROM
invoice_items
ORDER BY
TrackId
Так, треки № 2 и № 8 появляются в нескольких счетах, что означает, что они были заказаны несколько раз (рис. 121). Однако для трека № 7 счет отсутствует, поэтому мы можем сделать вывод, что никто не приобретал его. Руководство sTunes хочет знать о треках, которые не продаются. Нам необходимо найти таблицу, связывающую поля TrackId с InvoiceId. Для перечисления всех треков (по композитору и названию), которые не отображаются в таблице invoice_items, мы можем использовать подзапросы.
Рис. 121
Рис. 122
Если мы снова запустим тот же запрос, только на этот раз с ключевым словом DISTINCT, мы получим список только тех треков, которые появляются в счетах, без дубликатов.
SELECT
DISTINCT TrackId
FROM
invoice_items
ORDER BY
TrackId
Из примера видно, что некоторые номера TrackId (например, № 7) не появляются ни в одном счете, но списки треков, которые появляются в нескольких счетах, сокращены до одного. Теперь нам необходимо написать запрос, перечисляющий все треки из таблицы tracks, которые не входят (NOTIN) в созданный первым запросом список.
SELECT
TrackId,
Composer,
Name
FROM
tracks
WHERE
TrackId NOT IN
(select distinct
TrackId
from
invoice_items)
Итак, у нас есть список песен, которых не было ни в одном счете (рис. 123). Анализируя полученные результаты, мы видим, что трек № 7 находится в самой верхней части списка непродаваемых треков. Теперь отдел продаж sTunes имеет четкое представление о том, какие песни непопулярны.
Рис. 123
Примеры подзапросов из этой главы не являются исчерпывающими. Существует еще множество вариантов использования подзапросов, и все они имеют общие особенности. Подзапросы могут выполнять сложные многоступенчатые вычисления с помощью всего одного запроса. Они позволяют вычислить конкретное условие, а затем сравнить новый набор данных с тем же условием.
Примечание
Подзапросы — это альтернативный способ взаимодействия таблиц, имеющих общие ключевые поля. Но если нам нужно много работать с обеими таблицами, то вместо подзапросов эффективнее создать join-соединение. Например, гораздо эффективнее создать соединение между полем TrackId и таблицей tracks (вместо использования подзапросов), чтобы отображать всю информацию рядом.
Контрольные вопросы
1. Сколько счетов превышают среднюю сумму счетов, выставленных в 2010 году?
2. Какие клиенты получили эти счета?
3. Сколько клиентов живут в США?
Резюме
• Подзапросы позволяют выполнять в одном запросе несколько операторов SQL.
• Подзапросы состоят из двух или более отдельных операторов SQL, которые образуют внутренние и внешние запросы.
• Подзапросы обычно используются для сравнения существующих данных с данными, полученными с помощью агрегатов или других функций.
• Условие DISTINCT позволяет игнорировать избыточные данные в записях и искать только уникальные значения.
Глава 9. Представления
Представление является виртуальной таблицей. Это просто сохраненный SQL-запрос, который может выполняться многократно или на него (как подзапрос) могут ссылаться другие запросы. Представления полезны, если постоянно требуется один и тот же запрос, особенно когда он сложен. Предположим, руководство компании sTunes запрашивает одни и те же данные о продажах каждую неделю или квартал. Значит, имеет смысл подготовить представление — запрос на искомую информацию. Рассмотрим варианты использования представлений в SQL.
Работа с представлениями
Все операторы SQL, показанные в предыдущих главах, можно сохранить и использовать повторно, когда нам потребуется создать представление. Рассмотрим запрос, показанный в начале главы 8.
SELECT
ROUND(AVG(Total), 2) AS [Average Total]
FROM
invoices
Мы можем преобразовать этот оператор в представление, добавив над верхней строкой запроса команду CREATEVIEWV_AvgTotalAS:
CREATE VIEW V_ AvgTotal AS
SELECT
ROUND(AVG(Total), 2) AS [Average Total]
FROM
invoices
Итак, мы создали представление V_AvgTotal.
Примечание
При именовании представлений используется V_ в начале названия. После символа подчеркивания идет краткое описание того, как работает представление, при этом при необходимости используются дополнительные символы подчеркивания. Также можно назвать это представление V_AvgTotal_Rounded. Вы можете выбрать другое название.
При выполнении этого оператора мы получим следующее сообщение: QueryexecutedsuccessfullyCREATEVIEWV_AvgTotalAS (Запрос успешно выполнен: CREATEVIEWV_AvgTotalAS). Представление V_AvgTotal можно увидеть в разделе Views (Представления) на вкладке Database Structure (Структура базы данных) в DB Browser.
Рис. 124
Теперь, когда представление создано, мы можем выполнить несколько простых задач, просто щелкнув на нем правой кнопкой мыши. После этого появится окно меню (рис. 125).
Если выбрать пункт меню Browse Table (Просмотр таблицы) и перейти на соседнюю вкладку Browse Data (Просмотр данных), то можно просмотреть содержимое представления (как для любой таблицы базы данных). Из этого меню мы также можем удалить представление или сгенерировать копию кода (который ввели ранее для создания представления).
Рис. 125
Примечание
На рис. 125 пункт меню Modify View (Изменить представление) на момент публикации неактивен и недоступен. Изменение существующих представлений не поддерживается данной версией DB Browser. Однако в других реализациях SQL, таких как SQL Server, можно изменять существующие представления. Позже в этой главе мы объясним, как изменить представление в SQLite.
Использование представлений
Использование представлений в базах данных полезно по ряду причин, но в первую очередь из-за удобства. Если вам постоянно нужен один и тот же запрос или вы постоянно ссылаетесь на конкретное соединение, которое показывает взаимодействие двух таблиц, такой запрос удобно сохранить как представление, и тогда он будет доступен при необходимости в любое время. Кроме того, когда запрос сохраняется как представление, его можно вызвать как подзапрос, выбрав имя представления.
Рассмотрим представление V_AvgTotal, созданное в начале этой главы. В главе 8 для сравнения итоговых сумм счетов со средней суммой счета мы использовали функцию расчета среднего значения в качестве подзапроса. Теперь, чтобы не писать полный подзапрос расчета среднего значения, мы можем написать наш запрос следующим образом:
SELECT
InvoiceDate,
BillingAddress,
BillingCity,
Total
FROM
invoices
WHERE Total <
(SELECT
*
from
V_AvgTotal)
ORDER BY
Total DESC
Примечание
Хотя представление — это полноценный SQL-запрос, мы все равно должны ссылаться на него в операторе SELECT, если будем использовать представление вместе с подзапросами. В операторах SELECT можно использовать символ *, чтобы представление, на которое мы ссылаемся, возвращало все строки. В рассматриваемом примере строка только одна — агрегированная сумма.
Если какой-либо запрос часто используется в качестве подзапроса, то, сохранив его в качестве представления, мы можем упростить код и сделать более понятными для других наши действия. Кто угодно, например наш коллега, может просто перейти на вкладку Database Structure (Структура базы данных) и изучить, как работают наши представления. Использование представлений сокращает время написания запроса, особенно когда запросы становятся длинными и сложными.