Что такое view в sql
Перейти к содержимому

Что такое view в sql

  • автор:

Оператор SQL VIEW

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

Для создания представления используется оператор SQL CREATE и синтаксис выглядит следующим образом:

CREATE VIEW view_name AS SELECT column_name FROM table_name WHERE condition

Примеры оператора SQL VIEW. Имеется следующая таблица Planets :

ID PlanetName Radius SunSeason OpeningYear HavingRings Opener
1 Mars 3396 687 1659 No Christiaan Huygens
2 Saturn 60268 10759.22 Yes
3 Neptune 24764 60190 1846 Yes John Couch Adams
4 Mercury 2439 115.88 1631 No Nicolaus Copernicus
5 Venus 6051 243 1610 No Galileo Galilei

Пример 1. Используя оператор SQL VIEW создать на основе таблицы Planets представление, содержащее в себе название планеты и год ее открытия:

CREATE VIEW PlanetsView AS SELECT PlanetName, OpeningYear FROM Planets

В результате будет создано представление с названием PlanetsView которая будет содержать в себе значения столбцов PlanetName и OpeningYear

SELECT * FROM PlanetsView
PlanetName OpeningYear
Mars 1659
Saturn
Neptune 1846
Mercury 1631
Venus 1610

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

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

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

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

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

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

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

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

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

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

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

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

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

  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 .

Для данного представления каждая строка соответствует единственной строке из таблицы 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 .

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

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

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

  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 .

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

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

3.1. Представления View

Первое, с чем мы начнем знакомиться – вьюшки (View), я их больше люблю называть просмотрщиками или объектами просмотра, но в данной книге буду стараться использовать понятие вью, как более устоявшееся, хотя могли бы наши переводчики найти русский аналог слова View. Мне, например, нравится понятие объект просмотра, ведь вьюшка – это объект, а слово View переводиться как просмотр, к тому же, это понятие лучше отображает суть и звучит на родном языке.

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

Для чего создаются вьюшки? Можно выделить следующие назначения:

  • когда необходимо дать пользователю возможность просматривать не всю таблицу, а только ее часть. В этом случае, пользователь обращается к View как к таблице и видит только те поля, которые ему нужны;
  • для объединение двух или более таблиц. Например, объединив таблицы с помощью вьюшек, упрощается работа со связанными таблицами;
  • вывод статистических данных. Например, чтобы не писать каждый раз запрос на создание статистики, можно создать вью, и использовать его по мере надобности;

3.1.1. Создание вьюшки

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

CREATE VIEW [ < database_name >. ] [ < owner >. ] view_name [ ( column [ . n ] ) ] [ WITH < view_attribute >[ . n ] ] AS select_statement [ WITH CHECK OPTION ] < view_attribute >::=

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

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

CREATE VIEW PhoneView AS SELECT pl.vcFamil, pl.vcName, pl.vcSurName, dDateBirthDay, vcPhoneNumber FROM tbPeoples pl, tbPhoneNumbers pn WHERE pn.idPeoples=*pl.idPeoples

Как теперь можно использовать эту вьюшку? Точно так же, как и таблицу, то есть выполнять запрос SELECT:

SELECT * FROM PhoneView

Результат выполнения запроса:

vcFamil vcName vcSurName dDateBirthDay vcPhoneNumber ------------------------------------------------------------------- mr.ИВАНОВ ИВАН ИВАНЫЧ 2004-01-31 (925) 102-51-01 mr.ИВАНОВ ИВАН ИВАНЫЧ 2004-01-31 (925) 163-31-52 mr.ПЕТРОВ ИВАН ПАЛЫЧ 1971-04-03 (923) 112-02-46 mr.СИДОРОВ ИВАН ПАЛЫЧ 1967-12-13 (923) 152-52-04 mr.СИДОРОВ ИВАН ПАЛЫЧ 1967-12-13 (095) 125-16-63 mr.КОНОНОВ ШВАРЦ ПЕТРОВИЧ 1981-12-13 (905) 100-10-10 . (33 row(s) affected)

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

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

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

Вьюшки позволяют вам хранить результат комплексного запроса. Другие запросы могут использовать этот суммирующий результат.

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

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

CREATE VIEW Зарплата AS SELECT разрешенные для налоговой поля FROM Работники, Доходы WHERE навести связи

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

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

Когда мы ограничиваем доступ к определенным полям, то такая защита называется вертикальной. Объекты просмотра позволяют создавать и горизонтальную защиту. Например, в таблице есть определенные пользователи, которые должны быть видны только привилегированным пользователям. У таких пользователей в поле «Категория» стоит значение 1. Если запретить прямой доступ к таблице, а для всех пользователей создать вьюшку, то можно скрыть записи. Вьюшка может выглядеть следующим образом:

CREATE VIEW Зарплата AS SELECT Список полей FROM Работники, Доходы WHERE навести связи AND Категория=1

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

Конечно же, вы можете создать и горизонтальную защиту и вертикальную в одном объекте просмотра. Этого нам никто запретить не может.

С помощью разных вьюшек к одним и тем же таблицам экономисты могут видеть одни данные, бухгалтерия другие, а отдел кадров третьи. Если нужно показать какую-то дополнительную колонку, то просто добавляем в запрос вьюшки и все. Никаких прав изменять уже не надо будет.

В каждой базе данных могут быть системные вьюшки, которые создаются сервером автоматически. Не советую разрешать к ним доступ, потому что они могут показать что-нибудь лишнее, что поможет хакеру поднять свои права или просто испортить данные. Системные вьюшки начинаются с префикса sys и в колонке Type списка светиться надпись System (если просматривать хранимые на сервере вью с помощью программы Enterprise Manager).

Когда вы создаете вью, SQL Server проверяет существование объектов, на которые ссылаются в объявлении вью. Ваше имя вью должно соответствовать правилам именования объектов базы данных. Вы должны именовать вьюшки так, чтобы их имена отличались от имен таблиц, и их можно было выделить среди других объектов. Это значит, что имя вью не должно конфликтовать не только с существующими объектами просмотра (View), но и с именами таблиц базы данных. Если бы можно было создать просмотр с именем tbPeoples, то следующим запрос не смог бы определить, откуда выбирать данные – из вью или из таблицы с таким именем:

SELECT FROM tbPeoples

Для выполнения оператора CREATE VIEW, вы должны иметь соответствующие права, например, быть владельцем базы данных. Вы также должны иметь права на выполнение оператора SELECT всех таблиц, используемых в вью. Чтобы избежать ситуации, когда владельцем вью является один человек, а владельцем таблиц другой, всеми объектами должен владеть dbo. Всегда указывайте имя dbo при создании объектов. Например, в следующем примере показано, как PhoneView, который мы рассматривали ранее в этой главе, с явным указанием владельца (dbo):

CREATE VIEW dbo.PhoneView AS SELECT pl.vcFamil, pl.vcName, pl.vcSurName, dDateBirthDay, vcPhoneNumber FROM tbPeoples pl, tbPhoneNumbers pn WHERE pn.idPeoples=*pl.idPeoples

Все поля вью должны иметь имена, и они должны быть уникальными. Поэтому, необходимо во время выполнения оператора CREATE VIEW учитывать следующие особенности:

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

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

CREATE VIEW NamesView AS SELECT pl.vcName, count(*) FROM tbPeoples pl GROUP BY vcName

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

CREATE VIEW NamesView AS SELECT pl.vcName, count(*) AS Количество FROM tbPeoples pl GROUP BY vcName

Есть еще один способ задания полей для View – в скобках после имени вьюшки:

CREATE VIEW NamesView1(Имя, Количество) AS SELECT pl.vcName, count(*) FROM tbPeoples pl GROUP BY vcName

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

Имя Количество ------------------------------------ АНДРЕЙ 5 БОЛИК 1 ВАСЯ 1 ВИКТОР 1 ВИТЯ 1 .

Теперь рассмотрим пример конфликта колонок. Допустим, что мы написали следующий запрос и решили его превратить в объект просмотра:

SELECT pl.vcFamil, pl.vcName, pl.vcSurName, dDateBirthDay, vcPhoneNumber, pl.idPeoples, pn.idPeoples FROM tbPeoples pl, tbPhoneNumbers pn WHERE pn.idPeoples=*pl.idPeoples

В секции SELECT поле «idPeoples» выбирается из таблицы работников и из таблицы телефонов. Если смотреть на этот код как запрос, то ошибки нет, и он выполнится. Но если попытаться создать вью:

CREATE VIEW ConflictView AS SELECT pl.vcFamil, pl.vcName, pl.vcSurName, dDateBirthDay, vcPhoneNumber, pl.idPeoples, pn.idPeoples FROM tbPeoples pl, tbPhoneNumbers pn WHERE pn.idPeoples=*pl.idPeoples

В ответ на это мы тут же увидим ошибку. Сервер сообщит нам о том, что поля вьюшки должны быть уникальными. Проблема опять же решается с помощью задания одному из конфликтующих полей псевдонима с уникальным именем. Например, в следующем запросе полю «idPeoples» из таблицы tbPhoneNumbers задается псевдоним PhoneNumbersID:

CREATE VIEW ConflictView AS SELECT pl.vcFamil, pl.vcName, pl.vcSurName, dDateBirthDay, vcPhoneNumber, pl.idPeoples, pn.idPeoples AS PhoneNumbersID FROM tbPeoples pl, tbPhoneNumbers pn WHERE pn.idPeoples=*pl.idPeoples

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

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

  • нельзя использовать ключевое слово INTO;
  • нельзя использовать опции COMPUTE или COMPUTE BY;
  • можно использовать оператор ORDER BY только если используется ключевое слово TOP;
  • вью не может ссылаться на временные таблицы;
  • вью, как и таблица не может содержать более чем 1024 колонки.

3.1.2. Редактирование вью

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

Оператор ALTER VIEW изменяет объявление вью. Это позволяет вам сохранить разрешения. В общем виде команда выглядит следующим образом:

ALTER VIEW [ < database_name >. ] [ < owner >. ] view_name [ ( column [ . n ] ) ] [ WITH < view_attribute >[ . n ] ] AS select_statement [ WITH CHECK OPTION ]

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

ALTER VIEW PhoneView AS SELECT vcFamil, vcName, vcSurname, vcPositionName, vcPhoneNumber, pn.idPeoples FROM tbPeoples pl, tbPosition ps, tbPhoneNumbers pn WHERE pl.idPosition=ps.idPosition AND pl.idPeoples=pn.idPeoples

Как видите, изменение происходит также, как и создание объекта просмотра.

3.1.3. Удаление вью

Если вам больше не нужен объект просмотра, то его следует удалить. Ничего лишнего в базе данных не должно быть, ведь не используемые объекты отрицательно влияют на безопасность. Для удаления используется оператор DROP VIEW.

DROP VIEW < view >[ . n ]

Можно удалять сразу несколько объектов просмотра. Следующий пример удаляет сразу три объекта:

DROP VIEW NamesView, NamesView1, ConflictView

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

3.1.4. Изменение содержимого вью

Я уже говорил о том, что с объектом просмотра можно работать также как и с простой таблицей. Это значит, что возвращаемый результат можно воспринимать как таблице и редактировать. Давайте попробуем изменить в вьюшке PhoneView фамилию работника с идентификатором 1 на Печкина:

UPDATE PhoneView SET vcFamil='ПОЧЕЧКИН' WHERE idPeoples=1

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

UPDATE PhoneView SET vcPositionName='Самый генеральный директор' WHERE vcPositionName='Генеральный директор'

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

Объект просмотра PhoneView выводит результат из трех таблиц сразу. А что, если мы хотим изменить поля из нескольких таблиц одновременно? Давайте попробуем выполнить следующий запрос:

UPDATE PhoneView SET vcFamil='ПОЧЕЧКИН', vcPositionName='Самый генеральный директор' WHERE idPeoples=1

В этом примере изменяется поле фамилии, которое находится в таблицы tbPeoples и поле названия должности из таблицы tbPosition. Если попытаться выполнить этот запрос, то сервер вернет ошибку и сообщит нам, что изменять сразу несколько полей нельзя. Именно это и есть ограничение при изменении записей.

3.1.5. Удаление строк из вью

Попробуем удалить запись из вьюшки PhoneView:

DELETE FROM PhoneView WHERE idPeoples=1

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

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

CREATE VIEW PhoneView1 AS SELECT pl.vcFamil, pl.vcName, pl.vcSurName FROM tbPeoples pl

Теперь попробуем удалить запись:

DELETE FROM PhoneView1 WHERE vcFamil='КОТИКОВ'

На этот раз все пройдет успешно, и запись будет удалена, потому что объект просмотра использует поля только одной таблицы.

3.1.6. Опции объекта просмотра

Очень интересной опцией, которую можно использовать при создании объекта просмотра является ENCRYPTION, которая заставляет шифровать текст вьюшки в системных таблицах SQL сервера. Таким образом, если злоумышленник получил доступ к системе, то просмотр текста объекта просмотра останется затруднительным. Это действительно является очень полезным свойством. Защита лишней не бывает.

Давайте создадим объект просмотра, данные которого (исходный код) будут зашифрованными в системной таблице:

CREATE VIEW PhoneView1 WITH ENCRYPTION AS SELECT pl.vcFamil, pl.vcName, pl.vcSurName FROM tbPeoples pl

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

VIEW SQL Server

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

Создать VIEW

Синтаксис

Синтаксис оператора CREATE VIEW в SQL Server (Transact-SQL):

CREATE VIEW [schema_name.]view_name AS
[ WITH < ENCRYPTION | SCHEMABINDING | VIEW_METADATA >
SELECT expressions
FROM tables
[WHERE conditions];

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

schema_name — имя схемы, которой будет принадлежать представление.
view_name — имя VIEW, которую вы хотите создать.
ENCRYPTION — это зашифрует текст оператора ALTER VIEW в sys.syscomments.
SCHEMABINDING — это гарантирует, что определения базовых таблиц не могут быть изменены, чтобы повлиять на VIEW.
VIEW_METADATA — это обеспечит наличие метаданных SQL Server относительно VIEW.
expressions — столбцы или вычисления, которые вы хотите включить в VIEW.
tables — таблицы, определяющие VIEW. Должна быть хотя бы одна таблица, перечисленная в предложении FROM.
WHERE conditions — необязательный. Условия, которые должны быть выполнены для записей, отображаемых в VIEW.

Пример

Рассмотрим пример использования оператора CREATE VIEW в SQL Server (Transact-SQL).
Например:

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *