Перейти к оглавлению
20. ВВЕДЕНИЕ: ПРЕДСТАВЛЕНИЯ
ПРЕДСТАВЛЕНИЕ (VIEW) - ОБЪЕКТ ДАННЫХ КОТОРЫЙ не содержит никаких
данных его владельца. Это - тип таблицы, чье содержание выбирается из
других таблиц с помощью выполнения запроса. Поскольку значения в этих
таблицах меняются, то автоматически, их значения могут быть показаны
представлением.
В этой главе, вы узнаете что такое представления, как они создаются,
и немного об их возможностях и ограничениях. Использование представле-
ний основанных на улучшеных средствах запросов, таких как обьединение
и подзапрос, разработаных очень тщательно, в некоторых случаях даст
больший выиигрыш по сравнениию с запросами.
====== ЧТО ТАКОЕ ПРЕДСТАВЛЕНИЕ ? =====
Типы таблиц, с которыми вы имели дело до сих пор, назывались - базо-
выми таблицами. Это - таблицы, которые содержат данные. Однако имеется
другой вид таблиц: - представления. Представления - это таблицы чье
содержание выбирается или получается из других таблиц. Они работают в
запросах и операторах DML точно также как и основные таблицы, но не
содержат никаких собственных данных. Представления - подобны окнам,
через которые вы просматриваете информацию( как она есть, или в другой
форме, как вы потом увидите ), которая фактически хранится в базовой
таблице. Представление - это фактически запрос, который выполняется
всякий раз, когда представление становится темой команды. Вывод запро-
са при этом в каждый момент становится содержанием представления.
============= КОМАНДА CREATE VIEW ===========
Вы создаете представление командой CREATE VIEW. Она состоит из слов
CREATE VIEW (СОЗДАТЬ ПРЕДСТАВЛЕНИЕ), имени представления которое нужно
создать, слова AS (КАК), и далее запроса, как в следующем примере:
CREATE VIEW Londonstaff
AS SELECT *
FROM Salespeople
WHERE city = 'London';
Теперь Вы имеете представление, называемое Londonstaff. Вы можете
использовать это представление точно так же как и любую другую табли-
цу. Она может быть запрошена, модифицирована, вставлена в, удалена
из, и соединена с, другими таблицами и представлениями. Давайте сдела-
ем запрос такого представления ( вывод показан в Рисунке 20.1):
SELECT *
FROM Londonstaff;
=============== SQL Execution Log ============
| |
| SELECT * |
| FROM Londonstaff; |
| |
| ==============================================|
| snum sname city comm |
| ------ ---------- ----------- ------- |
| 1001 Peel London 0.1200 |
| 1004 Motika London 0.1100 |
| |
===============================================
20.1 Рисунок 20.1: Представление Londonstaff
Когда вы приказываете SQL выбрать(SELECT) все строки ( * ) из предс-
тавления, он выполняет запрос содержащий в определении - Loncfonstaff,
и возвращает все из его вывода. Имея предикат в запросе представления,
можно вывести только те строки из представления, которые будут удов-
летворять этому предикату. Вы могли бы вспомнить, что в Главе 15, вы
имели таблицу, называемую Londonstaff, в которую вы вставляли это же
самое содержание ( конечно, мы понимаем что таблица - не слишком вели-
ка. Если это так, вы будете должны выбрать другое имя для вашего
представления). Преимущество использования представления, по сравнению
с основной таблицы, в том, что представление будет модифицировано ав-
томатически всякий раз, когда таблица лежащая в его основе изменяется.
Содержание представления не фиксировано, и переназначается каждый раз
когда вы ссылаетесь на представление в команде. Если вы добавите завт-
ра другого, живущего в Лондоне продавца, он автоматически появится в
представлении.
Представления значительно расширяют управление вашими данными. Это -
превосходный способ дать публичный доступ к некоторой, но не всей ин-
формации в таблице. Если вы хотите чтобы ваш продавец был показан в
таблице Продавцов, но при этом не были показаны комиссии других про-
давцов, вы могли бы создать представление с использованием следующего
оператора ( вывод показан в Рисунке 20.2 )
CREATE VIEW Salesown
AS SELECT snum, sname, city
FROM Salespeople:
=============== SQL Execution Log ============
| |
| SELECT * |
| FROM Salesown; |
| |
| ==============================================|
| snum sname city |
| ------ ---------- ----------- |
| 1001 Peel London |
| 1002 Serres San Jose |
| 1004 Motika London |
| 1007 Rifkin Barcelona |
| 1003 Axelrod New York |
===============================================
Рисунок 20.2: Представление Salesown
Другими словами, это представление - такое же как для таблицы Про-
давцов, за исключением того, что поле comm, не упоминалось в запросе,
и следовательно не было включено в представление.
МОДИФИЦИРОВАНИЕ ПРЕДСТАВЛЕНИЙ
Представление может теперь изменяться командами модификации DML, но
модификация не будет воздействовать на само представление. Команды бу-
дут на самом деле перенаправлены к базовой таблице:
UPDATE Salesown
SET city = 'Palo Alto'
WHERE snum = 1004;
Его действие идентично выполнению той же команды в таблице Продавцов.
Однако, если значение комиссионных продавца будет обработано командой
UPDATE
UPDATE Salesown
SET comm = .20
WHERE snum = 1004;
она будет отвергнута, так как поле comm отсутствует в представлении
Salesown. Это важное замечание, показывающее что не все представления
могут быть модифицированы. Мы будем исследовать проблемы модификации
представлений в Главе 21.
ИМЕНОВАНИЕ СТОЛБЦОВ
В нашем примере, поля наших представлений имеют свои имена, получе-
ные прямо из имен полей основной таблицы. Это удобно. Однако, иногда
вам нужно снабжать ваши столбцы новыми именами:
* когда некоторые столбцы являются выводимыми, и проэтому не имеющими
имен.
* когда два или более столбцов в объединении, имеют те же имена что в
их базовой таблице.
Имена, которые могут стать именами полей, даются в круглых скобках,
после имени таблиц. Они не будут запрошены, если совпадают с именами
полей запрашиваемой таблицы. Тип данных и размер этих полей будут от-
личаются от запрашиваемых полей которые "передаются" в них. Обычно вы
не указываете новых имен полей, но если вы все таки сделали это, вы
должны делать это для каждого поля в представлении.
КОМБИНИРОВАНИЕ ПРЕДИКАТОВ ПРЕДСТАВЛЕНИЙ
И ОСНОВНЫХ ЗАПРОСОВ В ПРЕДСТАВЛЕНИЯХ
Когда вы делаете запрос представления, вы собственно, запрашиваете
запрос. Основной способ для SQL обойти это, - объединить предикаты
двух запросов в один. Давайте посмотрим еще раз на наше представление
с именем Londonstaff :
CREATE VIEW Londonstaff
AS SELECT *
FROM Salespeople
WHERE city = 'London';
Если мы выполняем следующий запрос в этом представлении
SELECT *
FROM Londonstaff
WHERE comm > .12;
он такой же как если бы мы выполнили следующее в таблице Продавцов:
SELECT *
FROM Salespeople
WHERE city = 'London'
AND comm > .12;
Это прекрасно, за исключением того, что появляется возможная пробле-
ма с представлением. Имеется возможность комбинации из двух полностью
допустимых предикатов и получения предиката который не будет работать.
Например, предположим что мы создаем (CREATE) следующее представление:
CREATE VIEW Ratingcount (rating, number)
AS SELECT rating, COUNT (*)
FROM Customers
GROUP BY rating;
Это дает нам число заказчиков которые мы имеем для каждого уровня
оценки(rating). Вы можете затем сделать запрос этого представления
чтобы выяснить, имеется ли какая-нибудь оценка, в настоящее время наз-
наченная для трех заказчиков:
SELECT *
FROM Ratingcount
WHERE number = 3;
Посмотрим что случится если мы скомбинируем два предиката:
SELECT rating, COUNT (*)
FROM Customers
WHERE COUNT (*) = 3
GROUP BY rating;
Это недопустимый запрос. Агрегатные функции, такие как COUNT (СЧЕТ),
не могут использоваться в предикате. Првильным способом при формирова-
нии вышеупомянутого запроса, конечно же будет следующий:
SELECT rating, COUNT (*)
FROM Customers
GROUP BY rating;
HAVING COUNT (*) = 3;
Но SQL может не выполнить превращения. Может ли равноценный запрос
вместо запроса Ratingcount потерпеть неудачу? Да может! Это - неодноз-
начная область SQL, где методика использования представлений может
дать хорошие результаты. Самое лучшее что можно сделать в случае, ког-
да об этом ничего не сказано в вашей системной документации, так это
попытка в ней разобраться. Если команда допустима, вы можете использо-
вать представления чтобы установить некоторые ограничения SQL в син-
таксисе запроса.
ГРУППОВЫЕ ПРЕДСТАВЛЕНИЯ
Групповные представления - это представления, наподобии запроса Ra-
tingcount в предыдущем примере, который содержит предложение GROUP BY,
или который основывается на других групповных представлениях.
Групповые представления могут стать превосходным способом обрабаты-
вать полученную информацию непрерывно. Предположим, что каждый день вы
должны следить за порядком номеров заказчиков, номерами продавцов при-
нимающих порядки, номерами порядков, средним от порядков, и общей сум-
мой приобретений в порядках.
Чем конструировать каждый раз сложный запрос, вы можете просто соз-
дать следующее представление:
CREATE VIEW Totalforday
AS SELECT odate, COUNT (DISTINCT cnum), COUNT
(DISTINCT snum), COUNT (onum), AVG
(amt), SUM (amt)
FROM Orders
GROUP BY odate;
Теперь вы сможете увидеть всю эту информацию с помощью простого зап-
роса:
SELECT *
FROM Totalforday;
Как мы видели, SQL запросы могут дать вам полный комплекс возможнос-
тей, так что представления обеспечивают вас чрезвычайно гибким и мощ-
ным инструментом чтобы определить точно, как ваши данные могут быть
использованы. Они могут также делать вашу работу более простой, пере-
форматируя данные удобным для вас способом и исключив двойную работу.
ПРЕДСТАВЛЕНИЯ И ОБЬЕДИНЕНИЯ
Представления не требуют чтобы их вывод осуществлялся из одной базо-
вой таблицы. Так как почти любой допустимый запрос SQL может быть ис-
пользован в представлении, он может выводить информацию из любого чис-
ла базовых таблиц, или из других представлений. Мы можем, например,
создать представление которое показывало бы, порядки продавца и заказ-
чика по имени:
CREATE VIEW Nameorders
AS SELECT onum, amt, a.snum, sname, cname
FROM Orders a, Customers b, Salespeople c
WHERE a.cnum = b.cnum
AND a.snum = c.snum;
Теперь вы можете выбрать (SELECT) все порядки заказчика или продавца
( * ), или можете увидеть эту информацию для любого порядка. Например,
чтобы увидеть все порядки продавца Rifkin, вы должны ввести следующий
запрос ( вывод показан в 20.3 Рисунке ):
SELECT *
FROM Nameorders
WHERE sname = 'Rifkin';
=============== SQL Execution Log ==============
| |
| SELECT * |
| FROM Nameorders |
| WHERE sname = 'Rifkin'; |
| =============================================== |
| onum amt snum sname cname |
| ------ -------- ----- ------- ------- |
| 3001 18.69 1007 Rifkin Cisneros |
| 3006 1098.16 1007 Rifkin Cisneros |
| |
================================================
Рисунок 20.3: Порядки Rifkin показаные в Nameorders
Вы можете также объединять представления с другими таблицами, или
базовыми таблицами или представлениями, поэтому вы можете увидеть все
порядки Axelrodа и значения его комиссиионных в каждом порядке:
SELECT a.sname, cname, amt comm
FROM Nameorders a, Salespeople b
WHERE a.sname = 'Axelrod'
AND b.snum = a.snum;
Вывод для этого запроса показывается в Рисунке 20.4.
В предикате, мы могли бы написать - " WHERE a.sname = |Axelrod' AND
b.sname = |Axelrod| " , но предикат который мы использовали здесь бо-
лее общеупотребительный. Кроме того поле snum - это первичный ключ
таблицы Продавцов, и следовательно должен по определению быть уникаль-
ным.
=============== SQL Execution Log ==============
| |
| SELECT a.sname, cname, amt * comm |
| FROM Nameorders a, Salespeople b |
| WHERE a.sname = 'Axelrod' |
| AND b.snum = a.snum; |
| =============================================== |
| onum amt snum sname cname |
| ------ -------- ----- ------- ------- |
| 3001 18.69 1007 Rifkin Cisneros |
| 3006 1098.16 1007 Rifkin Cisneros |
| |
================================================
Рисунок 20. 4: Обьединение основной таблицы с представлением
Если бы там например было два Axelrodf, вариант с именем, будет объ-
единять вместе их данные. Более предпочтительный вариант - использо-
вать поле snum чтобы хранить его отдельно.
ПРЕДСТАВЛЕНИЯ И ПОДЗАПРОСЫ
Представления могут также использовать и подзапросы, включая соотне-
сенные подзапросы. Предположим ваша компания предусматривает премию
для тех продавцов которые имеют заказчика с самым высоким порядком для
любой указанной даты. Вы можете проследить эту информацию с помощью
представления:
CREATE VIEW Elitesalesforce
AS SELECT b.odate, a.snum, a.sname,
FROM Salespeople a, Orders b
WHERE a.snum = b.snum
AND b.amt =
(SELECT MAX (amt)
FROM Orders c
WHERE c.odate = b.odate);
Если, с другой стороны, премия будет назначаться только продавцу ко-
торый имел самый высокий порядок за последние десять лет, вам необхо-
димо будет проследить их в другом представлении основанном на первом:
CREATE VIEW Bonus
AS SELECT DISTINCT snum, sname
FROM Elitesalesforce a
WHERE 10 < =
(SELECT COUNT (*)
FROM Elitesalestorce b
WHERE a.snum = b.snum);
Извлечение из этой таблицы продавца, который будет получать премию -
выпоняется простым вопросом:
SELECT *
FROM Bonus;
Теперь мы видим истинную мощность SQL. Извлечение той же полученной
информации программами RPG или COBOL будет более длительной процеду-
рой. В SQL, это - только вопрос из двух комплексных команд, сохранен-
ных, как представление совместно с простым запросом. При самостоятель-
ном запросе - мы должны заботится об этом каждый день, потому что ин-
формация которую извлекает запрос, непрерывно меняется чтобы отражать
текущее состояние базы данных.
ЧТО НЕ МОГУТ ДЕЛАТЬ ПРЕДСТАВЛЕНИЯ
Имеются большое количество типов представлений ( включая многие из
наших примеров в этой главе ) которые являются доступными только для
чтения. Это означает, что их можно запрашивать, но они не могут под-
вергаться действиям команд модификации. ( Мы будем рассматривать эту
тему в Главе 21. )
Имеются также некоторые виды запросов, которые не допустимы в опре-
делениях представлений. Одиночное представление должно основываться на
одиночном запросе; ОБЪЕДИНЕНИЕ (UNION) и ОБЪЕДИНЕНИЕ ВСЕГО (UNIOM ALL)
не разрешаются. УПОРЯДОЧЕНИЕ ПО(ORDER BY) никогда не используется в
определении представлений. Вывод запроса формирует содержание предс-
тавления, которое напоминает базовую таблицу и является - по определе-
нию - неупорядоченным.
УДАЛЕНИЕ ПРЕДСТАВЛЕНИЙ
Синтаксис удаления представления из базы данных подобен синтаксису
удаления базовых таблиц:
DROP VIEW < view name >
В этом нет необходимости, однако, сначала надо удалить все содержа-
ние как это делается с базовой таблицей, потому что содержание предс-
тавления не является созданым и сохраняется в течении определеной ко-
манды. Базовая таблица из которой представление выводится, не эффек-
тивна когда представление удалено. Помните, вы должны являться вла-
дельцем представления чтобы иметь возможность удалить его.
=============== РЕЗЮМЕ ==============
Теперь, когда вы можете использовать представления, ваша способность
отслеживать и обрабатывать содержание вашей базы данных, значительно
расширилась. Любые вещи которые вы можете создать с запросом, вы всег-
да сможете определить как представление. Запросы этих представлений,
фактически, запрос запроса. Использование представлений и для удобства
и для защиты, также удобно как и многие возможности представлений для
форматирования и получения значений из постоянно меняющегося содержа-
ния вашей базы данных. Имеется один главный вывод относительно предс-
тавлений, это способность к модификации, которую мы выбрали в отличии
от Главы 21. Как показано, вы можете модифицировать представления так-
же как и базовую таблицу, с помощью изменений применяемых к таблице из
которой получается представление, но это не всегда возможно.
*************** РАБОТА С SQL *************
1. Создайте представление которое бы показывало всех заказчиков кото-
рые имеют самые высокие оценки.
2. Создайте представление которое бы показывало номер продавца в каж-
дом городе.
3. Создайте представление которое бы показывало усредненый и общий по-
рядки для каждого продавца после его имени. Предполагается, что все
имена - уникальны.
4. Создайте представление которое бы показывало каждого продавца с
многочислеными заказчиками.
( См. Приложение A для ответов. )
Перейти к оглавлению
