Инструкция языка sql create view используется для

Время на прочтение
10 мин

Количество просмотров 392K

В комментариях Хабра упоминались вопросы по использованию представлений. Данный топик является обзором представлений, появившихся в MySQL версии 5.0. В нем рассмотрены вопросы создания, преимущества и ограничения представлений.

Что такое представление?

Представление (VIEW) — объект базы данных, являющийся результатом выполнения запроса к базе данных, определенного с помощью оператора SELECT, в момент обращения к представлению.

Представления иногда называют «виртуальными таблицами». Такое название связано с тем, что представление доступно для пользователя как таблица, но само оно не содержит данных, а извлекает их из таблиц в момент обращения к нему. Если данные изменены в базовой таблице, то пользователь получит актуальные данные при обращении к представлению, использующему данную таблицу; кэширования результатов выборки из таблицы при работе представлений не производится. При этом, механизм кэширования запросов (query cache) работает на уровне запросов пользователя безотносительно к тому, обращается ли пользователь к таблицам или представлениям.

Представления могут основываться как на таблицах, так и на других представлениях, т.е. могут быть вложенными (до 32 уровней вложенности).

Преимущества использования представлений:

1. Дает возможность гибкой настройки прав доступа к данным за счет того, что права даются не на таблицу, а на представление. Это очень удобно в случае если пользователю нужно дать права на отдельные строки таблицы или возможность получения не самих данных, а результата каких-то действий над ними.
2. Позволяет разделить логику хранения данных и программного обеспечения. Можно менять структуру данных, не затрагивая программный код, нужно лишь создать представления, аналогичные таблицам, к которым раньше обращались приложения. Это очень удобно когда нет возможности изменить программный код или к одной базе данных обращаются несколько приложений с различными требованиями к структуре данных.
3. Удобство в использовании за счет автоматического выполнения таких действий как доступ к определенной части строк и/или столбцов, получение данных из нескольких таблиц и их преобразование с помощью различных функций.

Ограничения представлений в MySQL

В статье приведены ограничения для версии MySQL 5.1 (в дальнейшем их число может сократиться).

• нельзя повесить триггер на представление,
• нельзя сделать представление на основе временных таблиц; нельзя сделать временное представление;
• в определении представления нельзя использовать подзапрос в части FROM,
• в определении представления нельзя использовать системные и пользовательские переменные; внутри хранимых процедур нельзя в определении представления использовать локальные переменные или параметры процедуры,
• в определении представления нельзя использовать параметры подготовленных выражений (PREPARE),
• таблицы и представления, присутствующие в определении представления должны существовать.
• только представления, удовлетворяющие ряду требований, допускают запросы типа UPDATE, DELETE и INSERT.

Создание представлений

Для создания представления используется оператор CREATE VIEW, имеющий следующий синтаксис:

CREATE [OR REPLACE]
[ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
VIEW view_name [(column_list)]
AS select_statement
[WITH [CASCADED | LOCAL] CHECK OPTION]

* This source code was highlighted with Source Code Highlighter.

view_name — имя создаваемого представления. select_statement — оператор SELECT, выбирающий данные из таблиц и/или других представлений, которые будут содержаться в представлении

Оператор CREATE VIEW содержит 4 необязательные конструкции:

1. OR REPLACE — при использовании данной конструкции в случае существования представления с таким именем старое будет удалено, а новое создано. В противном случае возникнет ошибка, информирующая о сществовании представления с таким именем и новое представление создано не будет. Следует отметить одну особенность — имена таблиц и представлений в рамках одной базы данных должны быть уникальны, т.е. нельзя создать представление с именем уже существующей таблицы. Однако конструкция OR REPLACE действует только на представления и замещать таблицу не будет.
2. ALGORITM — определяет алгоритм, используемый при обращении к представлению (подробнее речь об этом пойдет ниже).
3. column_list — задает имена полей представления.
4. WITH CHECK OPTION — при использовании данной конструкции все добавляемые или изменяемые строки будут проверяться на соответствие определению представления. В случае несоответствия данное изменение не будет выполнено. Обратите внимание, что при указании данной конструкции для необновляемого представления возникнет ошибка и представление не будет создано. (подробнее речь об этом пойдет ниже).

По умолчанию колонки представления имеют те же имена, что и поля возращаемые оператором SELECT в определении представления. При явном указании имен полей представления column_list должен включать по одному имени для каждого поля разделенных запятой. Существует две причины по которым желательно использовать явное указание имен полей представления:

1. Имена полей представления должны быть уникальны в пределах данного представления. При создании представления основанного на нескольких таблицах возможна ситуация повторения имен полей представления. Например:
   

   CREATE VIEW v AS SELECT a.id, b.id FROM a,b;* This source code was highlighted with Source Code Highlighter.

   Для избежания такой ситуации нужно явно указывать имена полей представления

   CREATE VIEW v (a_id, b_id) AS SELECT a.id, b.id FROM a,b;* This source code was highlighted with Source Code Highlighter.

   Того же результата можно добиться, используя синонимы (алиасы) для названий колонок:

   CREATE VIEW v AS SELECT a.id a_id, b.id b_id FROM a,b;* This source code was highlighted with Source Code Highlighter.

2. В случае если в определении представления получаемые данные преобразуются с помощью каких-то функций, то именем поля будет данное выражение, что не очень удобно для дальнейших ссылок на это поле. Напимер:
   

   CREATE VIEW v AS SELECT group_concat(DISTINCT column_name oreder BY column_name separator '+') FROM table_name;* This source code was highlighted with Source Code Highlighter.

   Вряд ли удобно использовать в дальнейшем в качестве имени поля `group_concat(DISTINCT username ORDER BY username separator ‘+’)`

Для просмотра содержимого представления мы используем оператор SELECT (полностью аналогично как в случае простой таблицы), с другой строны, оператор SELECT есть в самом определении представления, т.е. получается вложенная конструкция — запрос в запросе. При этом, некоторые конструкции оператора SELECT могут присутствовать в обоих операторах. Возможны три варианта развития событий: они обе будут выполнены, одна из них будет проигнорированна и результат неопределен. Рассмотрим подробнее эти случаи:

1. Если в обоих операторах встречается условие WHERE, то оба этих условия будут выполнены как если бы они были объединены оператором AND.
2. Если в определении представления есть конструкция ORDER BY, то она будет работать только в случае отсутствия во внешнем операторе SELECT, обращающемся к представлению, собственного условия сортировки. При наличии конструкции ORDER BY во внешнем операторе сортировка, имеющаяся в определении представления, будет проигнорирована.
3. При наличии в обоих операторах модификаторов, влияющих на механизм блокировки, таких как HIGH_PRIORITY, результат их совместного действия неопределен. Для избежания неопределенности рекомендуется в определении представления не использовать подобные модификаторы.

Алгоритмы представлений

Существует два алгоритма, используемых MySQL при обращении к представлению: MERGE и TEMPTABLE.

В случае алгоритма MERGE, MySQL при обращении к представлению добавляет в использующийся оператор соответствующие части из определения представления и выполняет получившийся оператор.

В случае алгоритма TEMPTABLE, MySQL заносит содержимое представления во временную таблицу, над которой затем выполняется оператор обращенный к представлению.
Обратите внимание: в случае использования этого алгоритма представление не может быть обновляемым (см. далее).

При создании представления есть возможность явно указать используемый алгоритм с помощью необязательной конструкции [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
UNDEFINED означает, что MySQL сам выбирает какой алгоритм использовать при обращении к представлению. Это значение по умолчанию, если данная конструкция отсутствует.

Использование алгоритма MERGE требует соответствия 1 к 1 между строками таблицы и основанного на ней представления.

Пусть наше представление выбирает отношение числа просмотров к числу ответов для тем форума:

CREATE VIEW v AS SELECT subject, num_views/num_replies AS param FROM topics WHERE num_replies>0;* This source code was highlighted with Source Code Highlighter.

Для данного представления каждая строка соответствует единственной строке из таблицы topics, т.е. может быть использован алгоритм MERGE. Рассмотрим следующее обращение к нашему представлению:

SELECT subject, param FROM v WHERE param>1000;* This source code was highlighted with Source Code Highlighter.

В случае MERGE алгоритма MySQL включает определение представления в использующийся оператор SELECT: заменяет имя представления на имя таблицы, заменяет список полей на определения полей представления и добавляет условие в части WHERE с помощью оператора AND. Итоговый оператор, выполняемый затем MySQL, выглядит следующим образом:

SELECT subject, num_views/num_replies AS param FROM topics WHERE num_replies>0 AND num_views/num_replies>1000;* This source code was highlighted with Source Code Highlighter.

Если в определении представления используются групповые функции (count, max, avg, group_concat и т.д.), подзапросы в части перечисления полей или конструкции DISTINCT, GROUP BY, то не выполняется требуемое алгоритмом MERGE соответствие 1 к 1 между строками таблицы и основанного на ней представления.

Пусть наше представление выбирает количество тем для каждого форума:

CREATE VIEW v AS SELECT forum_id, count(*) AS num FROM topics GROUP BY forum_id;* This source code was highlighted with Source Code Highlighter.

Найдем максимальное количество тем в форуме:

SELECT MAX(num) FROM v;* This source code was highlighted with Source Code Highlighter.

Если бы использовался алгоритм MERGE, то этот запрос был бы преобразован следующим образом:

SELECT MAX(count(*)) FROM topics GROUP BY forum_id;* This source code was highlighted with Source Code Highlighter.

Выполнение этого запроса приводит к ошибке «ERROR 1111 (HY000): Invalid USE of GROUP function», так как используется вложенность групповых функций.

В этом случае MySQL использует алгоритм TEMPTABLE, т.е. заносит содержимое представления во временную таблицу (данный процесс иногда называют «материализацией представления»), а затем вычисляет MAX() используя данные временной таблицы:

CREATE TEMPORARY TABLE tmp_table SELECT forum_id, count(*) AS num FROM topics GROUP BY forum_id;
SELECT MAX(num) FROM tmp_table;
DROP TABLE tpm_table;* This source code was highlighted with Source Code Highlighter.

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

1. В случае UNDEFINED MySQL пытается использовать MERGE везде где это возможно, так как он более эффективен чем TEMPTABLE и, в отличие от него, не делает представление не обновляемым.
2. Если вы явно указываете MERGE, а определение представления содержит конструкции запрещающие его использование, то MySQL выдаст предупреждение и установит значение UNDEFIND.

Обновляемость представлений

Представление называется обновляемым, если к нему могут быть применимы операторы UPDATE и DELETE для изменения данных в таблицах, на которых основано представление. Для того, чтобы представление было обновляемым должно быть выполнено 2 условия:

1. Соответствие 1 к 1 между строками представления и таблиц, на которых основано представление, т.е. каждой строке представления должно соответствовать по одной строке в таблицах-источниках.
2. Поля представления должны быть простым перечислением полей таблиц, а не выражениеями col1/col2 или col1+2.

Обратите внимание: встречающиеся в русско-язычной литературе требования, чтобы обновляемое представление было основано на единственной таблице и присутствие в числе полей представления первичного ключа физичекой таблицы не являются необходимыми. Скорее всего требование единственной таблицы является ошибкой перевода. Дело в том, что через представление, основанное на нескольких таблицах, может обновлять только одну таблицу за запрос, т.е. конструкция SET оператора UPDATE должна перечислять колонки только одной таблицы из определения представления. Кроме того, чтобы представление, основанное на нескольких таблицах, было обновляемым, таблицы в его определении должны быть объединены только с помощью INNER JOIN, а не OUTER JOIN или UNION.

Обновляемое представление может допускать добавление данных (INSERT), если все поля таблицы-источника, не присутствующие в представлении, имеют значения по умолчанию.

Обратите внимание: для представлений, основанных на нескольких таблицах, операция добавления данных (INSERT) работает только в случае если происходит добавление в единственную реальную таблицу. Удаление данных (DELETE) для таких представлений не поддерживается.

При использовании в определении представления конструкции WITH [CASCADED | LOCAL] CHECK OPTION все добавляемые или изменяемые строки будут проверяться на соответствие определению представления.

• Изменение данных (UPDATE) будет происходить только если строка с новыми значениями удовлетворяет условию WHERE в определении представления.
• Добавление данных (INSERT) будет происходить только если новая строка удовлетворяет условию WHERE в определении представления.

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

Ключевые слова CASCADED и LOCAL определяют глубину проверки для представлений основанных на других представлениях:

• Для LOCAL происходит проверка условия WHERE только в собственном определении представления.
• Для CASCADED происходит проверка для всех представлений на которых основанно данное представление. Значением по умолчанию является CASCADED.

Рассмотрим пример обновляемого представления, основанного на двух таблицах. Пусть наше представление выбирает темы форума с числом просмотров более 2000.

punbb >CREATE OR REPLACE VIEW v AS
-> SELECT forum_name, `subject`, num_views FROM topics,forums f
-> WHERE forum_id=f.id AND num_views>2000 WITH CHECK OPTION;
Query OK, 0 rows affected (0.03 sec)

punbb >

SELECT * FROM v WHERE subject='test';
+------------+---------+-----------+
| forum_name | subject | num_views |
+------------+---------+-----------+
| Новости | test | 3000 |
+------------+---------+-----------+
1 row IN SET (0.03 sec)

punbb >

UPDATE v SET num_views=2003 WHERE subject='test';
Query OK, 0 rows affected (0.03 sec)
Rows matched: 1 Changed: 0 WARNINGS: 0

punbb >

SELECT * FROM v WHERE subject='test';
+------------+---------+-----------+
| forum_name | subject | num_views |
+------------+---------+-----------+
| Новости | test | 2003 |
+------------+---------+-----------+
1 row IN SET (0.01 sec)

punbb >

SELECT subject, num_views FROM topics WHERE subject='test';
+---------+-----------+
| subject | num_views |
+---------+-----------+
| test | 2003 |
+---------+-----------+
1 rows IN SET (0.01 sec)* This source code was highlighted with Source Code Highlighter.

Однако, если мы попробуем установить значение num_views меньше 2000, то новое значение не будет удовлетворять условию WHERE num_views>2000 в определении представления и обновления не произойдет.

punbb >UPDATE v SET num_views=1999 WHERE subject='test';
ERROR 1369 (HY000): CHECK OPTION failed 'punbb.v'* This source code was highlighted with Source Code Highlighter.

Не все обновляемые представления позволяют добавление данных:

punbb >INSERT INTO v (subject,num_views) VALUES('test1',4000);
ERROR 1369 (HY000): CHECK OPTION failed 'punbb.v'* This source code was highlighted with Source Code Highlighter.

Причина в том, что значением по умолчанию колонки forum_id является 0, поэтому добавляемая строка не удовлетворяет условию WHERE forum_id=f.id в определении представления. Указать же явно значение forum_id мы не можем, так как такого поля нет в определении представления:

punbb >INSERT INTO v (forum_id,subject,num_views) VALUES(1,'test1',4000);
ERROR 1054 (42S22): Unknown COLUMN 'forum_id' IN 'field list'* This source code was highlighted with Source Code Highlighter.

С другой строны:

punbb >INSERT INTO v (forum_name) VALUES('TEST');
Query OK, 1 row affected (0.00 sec)* This source code was highlighted with Source Code Highlighter.

Таким образом, наше представление, основанное на двух таблицах, позволяет обновлять обе таблицы и добавлять данные только в одну из них.

Удачи в работе с представлениями!

Кросспост Представления (VIEW) в MySQL с SQLinfo.

CREATE VIEW Oracle

Представление – это виртуальная таблица. В действительности представление – всего лишь результат выполнения оператора SELECT, который хранится в структуре памяти, напоминающей SQL таблицу, например, Oracle. Для тех, кто работает с представлением, манипулирование его данными ничем не отличается от манипулирования данными таблицы. В некоторых случаях пользователь может вводить данные в представление, как если бы оно было таблицей. Работая с представлением нужно помнить, что:

• Представления добавляют уровень защиты данных (например, можно создать представление для таблицы, где пользователю, выполняющему SELECT над представлением, видны только сведения о зарплате)
• Представления могут скрывать сложность данных, комбинируя нужную информацию из нескольких таблиц
• Представления могут скрывать настоящие имена столбцов, порой трудные для понимания, и показывать более простые имена.

Представление создается с помощью команды CREATE VIEW. После создания представления становятся частью схемы создавшего их пользователя. Переназначить их другому пользователю можно тогда, когда у него имеется системная привилегия CREATE ANY VIEW.

Синтаксис команды CREATE VIEW Oracle

Основные ключевые слова и параметры CREATE VIEW Oracle:
OR REPLACE, FORCE, NOFORCE, Sсhema, View, Alias, AS subquery, WITH CHECK OPTION, Constraint

OR REPLACE — пересоздает представление, если оно уже существует. Можно использовать эту опцию для изменения определения представления без того, чтобы удалять его, создавать заново и вновь назначать все объектные привилегии, которые были назначены по данному представлению;

FORCE — создает представление независимо от того, существуют ли базовые таблицы этого представления, и от того, имеет ли владелец схемы, содержащей представление, привилегии по этим таблицам. Необходимо чтобы оба названных условия были удовлетворены, прежде чем по данному представлению можно будет выдавать любые предложения SELECT, INSERT, UPDATE или DELETE. По умолчанию применяется параметр NOFORCE;

NOFORCE — создает представление только в том случае, если существуют базовые таблицы этого представления, а владелец схемы, содержащей представление, имеет привилегии по этим таблицам;

Sсhema — схема, в которой создается представление. Если СХЕМА опущена, то ORACLE создает представление в схеме пользователя;

View — ключевое слово view это имя создаваемого представления;

Alias — специфицирует имена для выражений, выбираемых запросом представления. Число алиасов должно совпадать с количеством выражений, выбираемых подзапросом. Алиасы должны удовлетворять правилам именования объектов схем. Алиасы должны быть уникальны внутри представления. Если алиасы опускаются, то ORACLE определяет их по именам или алиасам столбцов в запросе представления. Поэтому использовать алиасы нужно, если запрос представления содержит, помимо имен столбцов, выражения;

AS subquery — идентифицирует столбцы и строки таблиц, на которых базируется представление. Запрос представления может быть любым предложением SELECT, не содержащим фраз ORDER BY или FOR UPDATE. Его список выборки может содержать до 254 выражений;

WITH CHECK OPTION — указывает, что вставки и обновления, которые будут осуществляться через этот запрос, должны давать в результате только такие строки, которые могут быть выбраны запросом этого же представления. Опция CHECK OPTION не может гарантировать этого, если существует подзапрос в запросе этого представления или любого представления, на котором базируется  данное представление. Другими словами, при указании параметра WITH CHECK OPTION пользователь не может вводить, удалять и обновлять информацию таблицы, из которой он не имеет возможности считать информацию через простое представление (создаваемое из данных одной таблицы). Обновляемое представление, использующее несколько связанных таблиц, нельзя создавать с данным параметром;

Constraint — имя, которое присваивается ограничению CHECK OPTION. Если этот идентификатор опущен, то ORACLE автоматически назначает этому ограничению имя следующего вида:

SYS_Cn , где n — целое, которое делает имя ограничения уникальным внутри базы данных.

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

Если команды обновления DML (INSERT, UPDATE, DELETE) можно применить к представлению, то говорят, что представление является обновляемым (updatable); в противном случае оно является только читаемым (read-only). Представление является обновляемым, если при его создании учитывались следующие критерии:

• представление должно включать первичный ключ таблицы
• не должно содержать полей, полученных в результате применения функций агрегирования
• не должно содержать DISTINCT, GROUP BY, HAVING в своем определении
• может быть определено на другом представлении, но это представление должно быть обновляемым
• не может содержать константы, строки или выражения (например, comm*100) в списке выбираемых выходных полей

SQL CREATE VIEW. Пример 1.
CREATE VIEW Oracle.
Простое представление, которое создается из данных одной таблицы:

CREATE VIEW London_view AS SELECT * FROM Salespeople WHERE city = ‘London’;

SQL CREATE VIEW. Пример 2.
CREATE VIEW Oracle.
При создании представления можно можно задать новые имена полей:

CREATE VIEW Rating_view(rating,number) AS SELECT rating, COUNT(*) FROM Customers GROUP BY rating;

SQL CREATE VIEW. Пример 3.
CREATE VIEW Oracle.
Представления могут получать информацию из любого количества базовых таблиц:

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;

SQL CREATE VIEW. Пример 4.
CREATE VIEW Oracle.
При создании представлений можно использовать подзапросы, включая и связанные подзапросы:

CREATE VIEW Sales_view AS SELECT b.odate, a.snum, a.sname, FROM Salespeople a, Orders b WHERE a.snum = b.snum AND b.amt = (SELECT MAX(amt) FROMOrders c WHERE c.odate = b.odate);

SQL CREATE VIEW. Пример 5.
CREATE VIEW Oracle.

CREATE VIEW empl_v04 AS SELECT e.eid, e.sname, e.fname, e.otch, p.pname, d.dname FROM posts p, departments d, employees e WHERE e.did = d.did AND e.pid = p.pid;

Представление View — это виртуальная таблица, оформленная в виде запроса типа SELECT,
который будет подставлен как подзапрос при использовании View.

В отличие от обычных таблиц реляционных СУБД, виртуальная таблица View не является
самостоятельной частью набора данных, хранящегося в базе. Содержимое представления формируется
динамически на основании данных, находящихся в реальных таблицах. Изменение данных в реальных
таблицах отражается в содержимом всех представлений, использующих эти таблицы в своих запросах.

Описание View для СУБД, поддерживающих язык запросов SQL, представляет собой запрос.
Поэтому, содержимое представления — это результат выполнения данного запроса. Так, для типичных СУБД
типа Oracle, MSSQL, PostgreSQL, Interbase, Firebird, MySQL, Derby представление может содержать :

• подмножество записей из одной или нескольких таблиц, отвечающие определённым условиям. К
  примеру, при наличии одной таблицы «Заказы» можно создать два представления : «Список
  товаров/услуг» и «Стоимость заказов», в каждом из которых будут только соответствующая
  информация : либо список отпущенных товаров, либо сумма оплаченная за товары сумма;
• подмножество столбцов из одной или нескольких таблиц. Например, из таблицы «Заказчики»
  представление может извлекать только информацию о клиенте в виде наименования компании,
  менеджере компании и его телефоне;
• результат обработки данных таблицы. Например, текстовые значения реальной таблицы могут быть
  приведены в верхний или нижний регистр, либо на основании цены и количества вычислена общая
  стоимость товара в определенной позиции и т.д.;
• результат соединения (join) нескольких таблиц;
• результат объединения (union) нескольких таблиц;

Создание представления, CREATE VIEW

Для создания представления необходимо использовать команду CREATE VIEW, включающую
необязательное наименования схемы (schema_name), наименование представления, необязательные
наименования колонок (column_name) ключевое слова AS и далее текст SQL-запроса :

CREATE VIEW [schema_name.]view_customers [(column_name [, …])]
 AS 
    select name, address, phone
       from [schema.]customers
          [where city ='Moscow']; 
    [WITH CHECK OPTION]

Схема, в которой создается представление VIEW, может отличаться от схемы с таблицей/ами, определенными
в запросе. В этом случае необходимо помнить, что пользователи данного представления должны иметь доступ
ко всем «представленным» во View объектам. SQL-запрос представления может включать не только таблицы
и условия «where», но также и другие представления.

Выражение WITH CHECK OPTION предназначено для предварительного выполнения проверки, если
SQL-запрос представления включает определенные условия и используется для внесения изменений в основную
таблицу.

Представление можно использовать в тексте SQL-запроса как обычную таблицу.

-- запрос без дополнительных условий
select * from view_customers;

-- запрос с дополнительными условиями
select name, phone 
  from view_customers
    where address like '%ВДНХ%';  

SQL представление с подзапросами

Представление VIEW может включать подзапросы. В качестве примера рассмотрим задачу определения
в компании менеджеров, заказы которых превысили определенную сумму (50000) :

CREATE VIEW view_managers 
  AS 
    select o.odate, o.order_id, o.total, m.manager_id, m.name
      from orders o, managers m
         where m.manager_id = o.manager_id and
               o.order_id in (select order_id
                                from orders c 
                                  where c.odate = o.odate and
                                        c.total > 50000); 

Теперь, если потребуется определить менеджера с самым высоким заказом в определенный период
времени, то можно использовать простой запрос с включением в него VIEW :

select odate, manager_id, name, MAX(total)
  from view_managers m
    where m.odate between '2015-01-01' and '2015-12-31' 
	group by odate, manager_id, name;

SQL представление с группировкой данных

Записи в запросе представления VIEW можно сгруппировать, получив, таким образом, групповое
представление. В качестве примера можно рассмотреть представление, формирующего статистическую
информацию :

CREATE VIEW stat_orders
  select odate, MAX(total), MIN(total), AVG(total), SUM(total), COUNT (order_id)
    from orders 
      group by odate;

Использование данного VIEW позволяет получить всю статистическую информацию по заказам
за день :

select * from stat_orders; 

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

Удаление представления, DROP View

Синтаксис удаления представления из базы данных включает ключевые слова DROP VIEW.
При необходимости нужно указать наименование схемы :

DROP VIEW [schema_name.]view_name

Следует помнить, что для удаления представления нужно иметь соответствующие права.

Изменение значений с помощью VIEW

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

• запрос представления использует только одну базовую таблицу;
• в запрос включены поля первичного ключа таблицы;
• запрос не содержит никаких полей, которые бы являлись агрегатными функциями, константами или выражениями;
• в запросе не должны использоваться ключевые слова DISTINCT, GROUP BY или HAVING;
• в запросе не должны быть использованы подзапросы;
• для вставки INSERT представление должно содержать любые поля таблицы которые имеют ограничение NOT NULL,
  если другое ограничение по умолчанию, не определено.

Примеры CREATE VIEW

-- Представление только для чтения, т.к. присутствует GROUP BY
CREATE VIEW view_orders (odate, total) 
  AS select odate, SUM (total) as total
      from orders 
        group by odate; 

-- Представление позволяет модифицировать данные
CREATE VIEW view_customers 
  AS select * from customers 
       where country ='Germany';

-- Представление только для чтения, т.к. присутствует выражение в последнем поле 
CREATE VIEW managers_tver
  AS select id, name, num, comm * 100 
      from managers 
        where city ='Tver'; 

Проверка значения при модификации

В следующем примере представление делает выборку записей заказов, в которых сумма превышает заданное значение (5000) :

CREATE VIEW order_5000
  AS select order_id, odate, onumber, total
    from orders 
      where total > 5000; 

Данное представление ограничивает доступ к определенным заказам в таблице и позволяет модифицировать данные. Для вставки
записи необходимо использовать следующий SQL-скрипт :

INSERT INTO order_5000 
   VALUES (123456, '2015-11-23', '20151123/24', 2000); 

Данный скрипт будет выполнен и запись будет добавлена в таблицу. Однако с помощью данного представления «order_5000» мы это не
увидим, т.к. данная запись не попадет в результирующий набор в виду ограничения на значение поля «total». Чтобы гарантировать
выполнение проверки значений перед записью следует в скрипте создания представления использовать ключевое выражение
WITH CHECK OPTION, т.е. с опцией проверки.

Вышеупомянутая вставка будет отклонена следующим VIEW :

CREATE VIEW order_5000_check
  AS select order_id, odate, onumber, total
    from orders 
      where total > 5000
  WITH CHECK OPTION;

Необходимо отметить, что выражение WITH CHECK OPTION не делает каскадированного изменения. Оно применяется только
в представлениях в которых оно определено, но не в представлениях основанных на этом представлении.

Исключенные поля представления

Необходимо быть аккуратным при использовании представления с условиями для вставки записей. Рассмотрим такое представление, в
котором поле страны «country» отсутствует.

CREATE VIEW customers_germany
  AS select id, name, address, phone from customers 
       where country ='Germany';

Внешне все нормально и представление можно использовать для внесения изменений в записи таблицы. Но вот как быть с «country»,
которое отсутствует в полях набора? Что будет при вставке записи через данное представление, если «country» не допускает ввода
нулевых (NULL) значений? А как вообще управлять этим полем «country» в основной таблице, не создавать же под каждую страну свое
представление. Ответа на данные вопросы не последует, они очевидные. Полагаю, что Вы не будете создавать подобные представления,
которые будут Вас ограничивать при выполнении определенных транзакций.