SQL: быстрое погружение - [34]

Рассмотрим представление 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 (Структура базы данных) и изучить, как работают наши представления. Использование представлений сокращает время написания запроса, особенно когда запросы становятся длинными и сложными.

Изменения представлений

Как мы упоминали ранее, текущая версия DB Browser (на момент написания этой книги) не поддерживает изменение существующих представлений. В качестве альтернативы в SQLite необходимо создать новое представление и присвоить ему новое имя или удалить существующее представление. Чтобы изменить представление в SQLite, необходимо перейти на вкладку Execute SQL (Выполнить SQL-запрос), щелкнуть правой кнопкой мыши на представлении и скопировать оператор CREATEVIEW. Затем можно вставить результаты на вкладку Execute SQL (Выполнить SQL-запрос), внести изменения и снова запустить оператор на выполнение.

Примечание

При повторном запуске представления, если исходное представление все еще сохранено в разделе Views (Представления), необходимо переименовать новое представление (или удалить существующее). Иначе появится сообщение об ошибке, указывающее, что представление уже существует. Имя представления должно быть уникальным.

Практические задания

• Измените представление V_AvgTotal, чтобы в нем не было функции ROUND().

• Из главы 8 выберите другой пример подзапроса, преобразуйте часть подзапроса в представление и запустите его снова.

Соединенные представления

Представления очень полезны для хранения длинных или сложных запросов. В главе 6 мы рассказывали о соединениях. Соединения помогают создавать связи между таблицами, обычно это довольно длинные запросы, поэтому их можно сохранить в качестве представлений, чтобы не вводить заново. В последней главе, посвященной подзапросам, мы использовали вместе таблицы invoice_items и tracks, чтобы выяснить, какие песни из таблицы tracks никогда не были заказаны. Нам бы пригодилось представление, связывающее эти две таблицы. На него можно было бы ссылаться в подзапросе либо просто сохранить как представление, чтобы использовать при необходимости.

Чтобы создать представление для этих двух таблиц, сначала необходимо решить, какое соединение мы хотим использовать. Поскольку мы ищем коррелирующие поля, то будем использовать условие INNERJOIN для таблиц invoice_items и tracks.

SELECT

ii.InvoiceId,

ii.UnitPrice,

ii.Quantity,

t. Name,

t. Composer,

t. Milliseconds

FROM

invoice_items ii

INNER JOIN

tracks t

ON ii.TrackId = t.TrackId

Напоминание

При создании соединения для каждой таблицы мы используем короткие псевдонимы, а затем, выявив общее поле, связываем таблицы друг с другом. В нашем случае зададим t для таблицы tracks и ii для таблицы invoice_items, так как i уже используется для таблицы invoices.

Теперь, когда соединение создано, мы можем добавить еще одну строку в начало запроса, чтобы сохранить его как представление.

CREATE VIEW V_Tracks_InvoiceItems AS

SELECT

ii.InvoiceId,

ii.UnitPrice,

ii.Quantity,

t. Name,

t. Composer,

t. Milliseconds

FROM

invoice_items ii

INNER JOIN

tracks t

ON ii.TrackId = t.TrackId

Теперь возьмем из главы 6 соединение, объединяющее таблицы invoices, customers и employees, и сохраним как представление.

CREATE VIEW V_inv_cus_emp AS

SELECT

i. InvoiceId,

i. InvoiceDate,

i. Total,

i. CustomerId,

c. FirstName,

c. LastName,

c. SupportRepId,

e. EmployeeId,

e. LastName,

e. FirstName,

e. Title

FROM

invoices AS i

INNER JOIN

customers AS c

ON

i. CustomerId = c.CustomerId

INNER JOIN

employees AS e

ON

e. EmployeeId = c.SupportRepId

ORDER BY

InvoiceDate

Примечание

В главе 6 мы немного изменили это соединение. Из условия SELECT мы удалили символ *. Смысл представлений — показать именно то, что необходимо. Таким образом, мы включили только необходимые поля из таблиц invoices, customers и employees.

Мы видим, что оба этих соединения сохранены как представления на вкладке Database Structure (Структура базы данных) (рис. 126).

Рис. 126

Теперь, когда у нас имеются эти два представления, мы используем их во внутреннем соединении:

SELECT *

FROM

V_Tracks_InvoiceItems ii

INNER JOIN

V_inv_cus_emp ice

ON

ii.InvoiceId = ice.InvoiceId