SQL: быстрое погружение

Представления очень полезны для хранения длинных или сложных запросов. В главе 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

С помощью приведенного выше запроса, объединяющего пять таблиц, мы можем узнать, какие треки были проданы каждым сотрудником и какому клиенту. Агрегируя данные, мы также определим, какой трек продавался лучше всего, какой общий доход был получен от продажи трека, а также информацию о сотрудниках, осуществивших продажи. Теперь мы можем при желании сохранить это объединение как представление.

Примечание

Соединенные представления будут работать только в том случае, если ключевые поля, общие для всех этих таблиц, были включены в первоначальное соединение.

Удаление представлений с помощью оператора DROP

Ранее мы уже описали один способ удаления представления — щелчок правой кнопкой мыши по имени представления на вкладке Database Structure (Структура базы данных). Представление также можно удалить с помощью команды DROP. Это выглядит следующим образом:

DROP VIEW

V_AvgTotal

Она удалит представление V_AvgTotal. Из базы данных удаляется только представление — синтаксис DROPVIEW не влияет на данные.

Примечание

Если вы удалите представление, на которое ссылаются другие представления, эти представления больше не будут работать.

Внимание

DROP — это команда для удаления любых элементов. Она может также удалить из базы данных таблицу. В следующей главе вы изучите инструменты, с помощью которых можно редактировать или удалять данные без возможности восстановления. Как вы уже знаете, представление также можно удалить из базы данных, щелкнув по нему правой кнопкой мыши и выбрав опцию Remove View (Удалить представление).

Контрольные вопросы

Для этого задания используем запрос из главы 8:

SELECT

BillingCity,

AVG(Total) AS [City Average],

(SELECT

avg(total)

from

invoices) AS [Global Average]

FROM

invoices

GROUP BY

BillingCity

ORDER BY

BillingCity

1. Из запроса SELECT возьмите внутренний запрос и создайте из него представление. Сохраните представление с именем V_GlobalAverage.

2. Удалите подзапрос из приведенного выше кода и замените его вновь созданным представлением V_GlobalAverage.

3. Сохраните этот новый запрос как представление с именем V_CityAvgVsGlobalAvg.

4. Удалите представление V_GlobalAverage. Как будет работать V_CityAvgVsGlobalAvg?

Резюме

• Представления — это виртуальные запросы, созданные с помощью SQL, на которые можно ссылаться в других запросах.