Материал: Graber_Martin_-_SQL_dlya_prostykh_smertnykh_-_2014

Комаида CREATE VIEW

то она будет отвергнута, поскольку в представлении нет поля соmш. Важно заметить, что не все представления могут обновляться. Об этом пойдет речь в главе 21.

Именование столбцов

До сих пор в качестве имен полей представлений использовались непосредственно имена полей таблицы, лежащей в основе представления. На начальном этапе работы с представлениями это допустимо. Однако иногда требуется задать новые имена для столбцов:

• Когда некоторые столбцы являются выходными столбцами и, следовательно, не

поименованы.

•Когда два или более столбцов в соединении имеют одинаковые имена в соот­

ветствующих таблицах.

Имена, которые станут именами полей, даются в круглых скобках после имени таб­ лицы. Это делать необязательно, если они соответствуют именам полей таблицы, уча­

ствующей в запросе. Типы данных и размеры выводятся из полей запроса. Часто не

требуется задавать новые имена полей, но при необходимости это можно сделать для

каждого поля в представлении.

Комбинирование предикатов представлений и запросов, основанных на представлениях

Запрос для представления - это запрос к запросу. Основной метод состоит в ком­ бинировании предикатов двух запросов в один. Можно посмотреть еще раз на пред­ ставление с именем Londonstaff:

CREATE VIEW Londonstaff

AS SELECT •

FROM Salespeople

WHERE city = .London. ;

Если для этого представления сформулировать следующий запрос:

SELECT •

FROM Londonstaff

WHERE comm > . 12;

то он выполняется так, как если бы был сформулирован для таблицы Salespeople:

SELECT •

FROM Salespeople

WHERE city = "London·

215

Глава 20. Введе11ие в представления

AND comm > . 12;

Это может вызвать ряд проблем, касающихся представлений. Можно без особых проблем комбинировать два однотипных предиката и получить предикат, который ра­ ботать не будет. Например, создается (CREATE) следующее представление:

САЕАТЕ VIEW Ratingcount (rating, number)

AS SELECT rating, COUNT (•)

FROM Customers

GROUP ВУ rating;

В соответствии с запросом представления получается некоторое число покупате­

лей для каждого уровня рейтинга. Затем для этого представления можно сформулиро­ вать запрос с целью выяснить, назначен ли какой-нибудь рейтинг для группы из трех

покупателей:

SELECT •

FROM Ratingcount

WHERE number = 3;

Если скомбинировать два предиката, получится:

SELECT rating, COUNT (•)

FROM Customers

GROUP ВУ rating;

Это неверный запрос. Функции агрегирования, одной из которых является COUNT, нельзя использовать в предикате.

Правильный способ сформировать рассмотренный выше запрос конечно же такой:

SELECT rating, COUNT (•)

FROM Customers

GROUP ВУ rating;

HAVING COUNT (•) = 3;

Но SQL не выполнит такое преобразование. Будет ли эквивалентный запрос о Ratingcount ошибочным? Возможно. Это область SQL, в которой техника выполнения представлений может значительно воздействовать на результаты. Наилучший способ разрешения этой проблемы, если она не отражена в документации по системе, - вы­ полнить и проверить результат. Если команда воспримется, то можно будет использо­ вать представления для того, чтобы получить нечто вроде ограничений SQL на

синтаксис запросов.

216

Кома11да CREATE VIEW

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

Групповые представления (grouped vie11's) - это представления, подобные Ratingcount в предыдущем примере, которые содержат предложение GROUP ВУ или базируются на других групповых представлениях.

Групповые представления могут быть прекрасным способом непрерывной обра­ ботки производной информации. Предположим, что каждый день нужно отслеживать количество покупателей, имеющих заказы, количество продавцов, получивших зака­ зы, количество заказов, среднее количество заказов и общее количество поступивших заказов. Вместо того, чтобы многократно конструировать сложный запрос, можно

просто создать следующее представление:

CREATE VIEW Totalforday

AS SELECT odate, COUNT (DISTINCT cnum), COUNT (DISTINCT snum),

COUNT (onum), AVG(amt), SUM(amt)

FROM Orders

GROUP ВУ odate;

Теперь можно получить всю необходимую информацию с помошью единственного

запроса:

SELECT *

FROM Totalforday;

Запросы SQL могут быть достаточно сложными, и, следовате.1ьно, представле­ ния являются гибким и мощным средством определения того, как будут использо­ ваться данные. Они приносят пользу, переформатируя данные необходимым образом и исключая многочисленные повторения.

Представления и соединения

Представления не обязательно выводятся из единственной базовой таблицы, они могут получать информацию из любого количества базовых таблиц или других пред­

ставлений. Например, можно определить представление, которое показывает для каж­

дого заказа имена продавца и покупателя:

AND a.snum = c.snum;

Теперь можно выбрать все заказы покупателя или продавца, либо увидеть эту ин­ формацию для любого заказа. Например, для того чтобы увидеть все заказы продавца, нужно ввести следующий запрос (выходные данные представлены на рис. 20.3):

217

Глава 20. Введение в представления

Рис. 20.3. Заказы для продавца Rifkin, представленные в таблице Nameorders

SELECT *

FROM Nameorders

WHERE sname = 'Rifkin';

Представления можно соединять с другими таблицами, как с базовыми, так и с представлениями; таким образом можно увидеть все заказы Axelrod и ее комиссион­

ные:

SELECT a.sname, cname, amt * comm

FROM Nameorders а, Salespeople Ь

WHERE a.sname = ·дxelrod'

AND b.snum = a.snum;

Выходные данные для запроса представлены на рис. 20.4.

В предикате можно было указать WHERE a.sname = 'Axelrod' AND b.sname ""

'Axelrod', но здесь использовался предикат более общего вида. Чтобы адаптировать за­ прос для любого человека, нужно просто указать его имя вместо Axelrod. Кроме того, snum является первичным ключом для таблицы Salespeople, и, следовательно, по опре­

делению является уникальным. Если бы было два человека с именем Axelrod, то вер­

сия с указанием поля name комбинировала бы их данные. Предпочтительнее является

вариант с использованием поля snum, который позволяет различать их.

218

Команда CREATE VIEW

Рис. 20.4. Соединение базовой таблицы и представления

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

Представления могут также использовать подзапросы, включая и связанные подза­

просы. Допустим, что компания платит вознаграждение продавцу, который имеет по­ купателя с наибольшим количеством заказов на заданную дату. Можно получить

информацию с помощью следующего представления:

CREATE VIEW Elitesalesforce

AS SELECT b.odate, a.snum, a.sname,

FROM Salespeople а, Orders Ь

WHERE a.snum

AND b.amt =

(SELECT МАХ (amt) FROM Orders с WHERE c.odate

Если вознаграждение получает только тот продавец, который имел наибольшее ко­ личество заказов по крайней мере 1О раз, то можно получить эту информацию с помо­

щью другого представления, основанного на первом:

CREATE VIEW Bonus

AS SELECT DISTINCT snum, sname

219