Что такое вьюха в базе данных

Представления (VIEW) в MySQL

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

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

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

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

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

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

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

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

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

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

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

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

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 .

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 .

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

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

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

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

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

При создании представления есть возможность явно указать используемый алгоритм с помощью необязательной конструкции [ALGORITHM = ]
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 .

SELECT subject, param FROM v WHERE param>1000;

* This source code was highlighted with Source Code Highlighter .

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 .

* This source code was highlighted with Source Code Highlighter .

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.

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

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

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

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

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

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

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 > 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 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 .

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

Name already in use

itstep / databases / presentation / 07.Views.rst

• Go to file T
• Go to line L
• Copy path
• Copy permalink

• Open with Desktop
• View raw
• Copy raw contents Copy raw contents

Copy raw contents

Copy raw contents

Представления — это виртульная таблица чье содержимое выбирается из других таблиц.

Представления испоьзуются в запросах и операторах DML точно также как и основные таблицы, но представления не содержат никаких собственных данных.

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

В представление можно добавлять функции SQL, команды WHERE, JOIN и представлять данные из различных таблиц так как будто они находятся в одной таблице.

1. Безопасность. Каждому пользователю можно разрешить доступ к небольшому числу представлений, содержащих только ту информацию, которую ему позволено знать. Таким образом можно осуществить ограничение доступа пользователей к хранимой информации.
2. Простота запросов. С помощью представления можно извлечь данные из нескольких таблиц и представить их как одну таблицу, превращая тем самым запрос ко многим таблицам в однотабличный запрос к представлению.
3. Структурная простота. С помощью представлений для каждого поль зователя можно создать собственную структуру базы данных, определив ее как множество доступных пользователю виртуальных таблиц.
4. Защита от изменений. Представление может возвращать непротиворечивый и неизменный образ структуры базы данных, даже если исходные таблицы разделяются, реструктуризуются или переименовываются. Заметим, однако, что определение представления должно быть обновлено, когда переименовываются лежащие в его основе таблицы или столбцы.
5. Целостность данных. Если доступ к данным или ввод данных осуществляется с помощью представления, СУБД может автоматически проверять, выполняются ли определенные условия целостности.

Создать представление «Current Product List» для всех продуктов (которые есть в наличии) из таблицы «Products».

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

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

Например, можно позволить менеджеру по продажам видеть в таблице SALESREPS только строки служащих, работающих в его регионе.

Теперь каждому менеджеру по продажам можно разрешить доступ к представлению EASTREPS и одновременно запретить доступ к таблице SALESREPS.

Еще одним распространенным применением представлений является ограничение доступа к столбцам таблицы.

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

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

Данные, полученные при помощи этого представления, представляют собой подмножество строк и столбцов таблицы CUSTOMERS.

В этом представлении будут видны только те столбцы, которые явно указаны в предложении SELECT, и только те строки, которые удовлетворяют условию отбора в предложении WHERE.

Запрос, определяющий представление, может содержать предложение GROUP BY. Представление такого типа называется сгруппированным представлением, поскольку данные в нем являются результатом запроса с группировкой.

Посчитать сколько категорий товаров проданных в 1997 году

В отличие от горизонтальных и вертикальных представлений, каждой строке сгруппированного представления не соответствует какая=то одна строка исходной таблицы.

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

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

Часто представления используют для упрощения многотабличных запросов.

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

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

Если представление ноебходимо изменить, то можно воспользоватся следующей командой

Теперь добавим поле Category к представлению «Current Product List».

Синтаксис удаления представления из базы данных подобен синтаксису удаления базовых таблиц:

Удалить представление «Current Product List»

1. Создайте представление которое бы показывало заказчиков с наивысшим рейтингом (rating). Таблица Customers.

.notes: CREATE VIEW Highratings AS SELECT * FROM Customers WHERE rating=(SELECT MAX(rating) FROM Customers);

1. Создайте представление которое бы показывало количество продавцов в каждом городе (city). Таблица Salespeople.

.notes: CREATE OR REPLACE VIEW Citynumber AS SELECT city, COUNT(DISTINCT snum) AS count FROM Salespeople GROUP BY city;

В исходном стандарте SQL1 четко указано, какие представления базы данных обновимы в соответствии со стандартом (обновимость в данном контексте означает вставку, модификацию или удаление).

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

1. Должен отсутствовать предикат DISTINCT, т.е. повторяющиеся строки не должны исключаться из таблицы результатов запроса.
2. В предложении FROM должна быть задана только одна обновляемая таблица, т.е. у представления должна быть одна исходная таблица, а пользователь должен иметь соответствующие права доступа к ней. Если исходная таблица сама является представлением, то оно также должно удовлетворять этим условиям.
3. Каждое имя в списке возвращаемых столбцов должно быть ссылкой на простой столбец; в этом списке не должны содержаться выражения, вычисляемые столбцы или статистические функции.
4. Предложение WHERE не должно содержать подчиненный запрос; в нем могут присутствовать только простые построчные условия отбора.
5. В запросе не должны содержаться предложения GROUP BY и HAVING.

Представление может теперь изменяться командами модификации DML.

Но модификация не будет воздействовать на само представление. Команды будут на самом деле перенаправлены к базовой таблице.

Эквивалентна выплнению команды:

Однако, если в представлении остутсвуют заданные поля, то изменения будут отвергнуты.

Например, в таблице Products есть поле Discontinued, однакое в представлении «Current Product List» этого поля нет

не будет выполнена.

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

Рассмотрим такое представление:

Это — представление модифицируемое. Оно просто ограничивает доступ к определенным строкам и столбцам в таблице.

Предположим, что мы вставляем следующую строку:

Это — допустима команда в этом представлении. Строка будет вставлена, в таблицу Customers, однако когда она появится там, она исчезнет из представления, поскольку значение оценки не равно 300. Для решения проблемы можно использовать WITH CHECK OPTION в определении представления.

1. Создайте представление таблицы Salespeople, которое должно включать только поле comm и snum. С помощью этого представления, можно будет вводить или изменять комиссионные, но только для значений между 0.10 и 0.20.

.notes: CREATE VIEW Commissions AS SELECT snum, comm FROM Salespeople WHERE comm BETWEEN .10 AND .20 WITH CHECK OPTION;

Представления и табличные объекты

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

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

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

Например, пусть у нас есть три связанных таблицы:

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

То есть данное представление фактически будет возвращать сводные данные из трех таблиц. И после его создания мы сможем его увидеть в узле Views у выбранной базы данных в SQL Server Management Studio:

Теперь используем созданное выше представление для получения данных:

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

Представления могут иметь не более 1024 столбцов и могут обращаться не более чем к 256 таблицам.

Также можно создавать представления на основе других представлений. Такие представления еще называют вложенными (nested views). Однако уровень вложенности не может быть больще 32-х.

Команда SELECT , используемая в представлении, не может включать выражения INTO или ORDER BY (за исключением тех случаев, когда также применяется выражение TOP или OFFSET ). Если же необходима сортировка данных в представлении, то выражение ORDER BY применяется в команде SELECT, которая извлекает данные из представления.

Также при создании представления можно определить набор его столбцов:

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

Для изменения представления используется команда ALTER VIEW . Эта команда имеет практически тот же самый синтаксис, что и CREATE VIEW :

Например, изменим выше созданное представление OrdersProductsCustomers:

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

Для удаления представления вызывается команда DROP VIEW :

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

VIEW в MySQL

В MySQL VIEW (представление или вьюха) не является физической таблицей, а скорее представляет собой виртуальную таблицу, созданную запросом, соединяющим одну или несколько таблиц.

Создать VIEW

Синтаксис

Синтаксис для оператора CREATE VIEW в MySQL:

Параметры или аргументы

OR REPLACE — необязательный. Если вы не укажете этот атрибут и VIEW уже существует, оператор CREATE VIEW вернет ошибку.
view_name — имя VIEW, которое вы хотите создать в MySQL.
WHERE conditions — необязательный. Условия, которые должны быть выполнены для записей, которые должны быть включены в VIEW.

Пример

Ниже приведен пример использования оператора CREATE VIEW для создания представления в MySQL: