Представления
Представление — это виртуальная таблица, содержимое которой определяется запросом. Как и таблица, представление состоит из ряда именованных столбцов и строк данных. Пока представление не будет проиндексировано, оно не существует в базе данных как хранимая совокупность значений. Строки и столбцы данных извлекаются из таблиц, указанных в определяющем представление запросе и динамически создаваемых при обращениях к представлению.
Представление выполняет функцию фильтра базовых таблиц, на которые оно ссылается. Определяющий представление запрос может быть инициирован в одной или нескольких таблицах или в других представлениях текущей или других баз данных. Кроме того, для определения представлений с данными из нескольких разнородных источников можно использовать распределенные запросы. Это полезно, например, если нужно объединить структурированные подобным образом данные, относящиеся к разным серверам, каждый из которых хранит данные конкретного отдела организации.
Представления обычно используются для направления, упрощения и настройки восприятия каждым пользователем информации базы данных. Представления могут использоваться как механизмы безопасности, давая возможность пользователям обращаться к данным через представления, но не предоставляя им разрешений на непосредственный доступ к базовым таблицам, лежащим в основе представлений. Представления могут использоваться для обеспечения интерфейса обратной совместимости, моделирующего таблицу, которая существует, но схема которой изменилась. Представления также можно использовать при копировании данных в SQL Server и из нее для повышения производительности и секционирования данных.
Типы представлений
Помимо стандартной роли базовых пользовательских представлений SQL Server предоставляет следующие типы представлений, которые служат специальным целям в базе данных.
Индексированные представления
Индексированным называется материализованное представление. Это означает, что определение представления вычисляется, а результирующие данные хранятся точно так же, как и таблица. Индексировать представление можно, создав для него уникальный кластеризованный индекс. Индексированные представления могут существенно повысить производительность некоторых типов запросов. Индексированные представления эффективнее всего использовать в запросах, группирующих множество строк. Они не очень хорошо подходят для часто обновляющихся базовых наборов данных.
Секционированные представления
Секционированным называется представление, соединяющее горизонтально секционированные данные набора таблиц-элементов, находящихся на одном или нескольких серверах. При этом данные выглядят так, как будто находятся в одной таблице. Представление, которое объединяет таблицы-члены в том же экземпляре SQL Server, является локальным секционированием.
Системные представления
Системные представления предоставляют доступ к метаданным каталога. Системные представления можно использовать для возврата сведений об экземпляре SQL Server или объектах, определенных в экземпляре. Например, можно запросить sys.databases представление каталога, чтобы получить сведения о пользовательских базах данных, доступных в экземпляре. Дополнительные сведения см. в разделе «Системные представления» (Transact-SQL).
Распространенные задачи представления
В следующей таблице приведены ссылки на общие задачи, связанные с созданием или изменением представления.
| Задачи, связанные с представлениями | Тема |
|---|---|
| Описывает, как создать представление. | Создание представлений |
| Описывает, как создать индексированное представление. | Создание индексированных представлений |
| Описывает, как изменить определение представления. | Изменение представлений |
| Описывает, как изменить данные через представление. | Изменение данных через представление |
| Описывает, как удалить представление. | Удаление представлений |
| Описывает, как получить сведения о представлении, например его определение. | Получение сведений о представлении |
| Описывает, как переименовать представление. | Переименование представлений |
Связанный контент
- Создание представлений для столбцов XML
- CREATE VIEW (Transact-SQL)
- GRANT, предоставление разрешений на объект (Transact-SQL)
- Безопасность на уровне строк
Views – представления
Бывает удобно сохранить результат выборки для дальнейшего использования. Для этих целей в языке SQL используется оператор CREATE VIEW, который создает представление – виртуальную таблицу. В эту виртуальную таблицу как бы сохраняется результат запроса.
Таблица виртуальная потому, что на самом деле ее нет в базе данных. В такую таблицу не получится вставить данные, обновить их или удалить. Можно только посмотреть хранящиеся в ней данные, сделать из нее выборку.
С другой стороны, если вы вносите изменения в реальные таблицы, они будут отражены и в виртуальных, потому что СУБД каждый раз, когда запрашивается представление, использует SQL выражение представления для обновления данных. Рассмотрим простой пример:
sqlite> CREATE VIEW title_url AS . > SELECT title,url FROM pages; sqlite> SELECT * FROM title_url; What is Information|information Amount of Information|amount-information Binary System|binary Boolean Lows|boolean sqlite> INSERT INTO pages (title, url, theme) . > VALUES . > ('What is Algorithm', 'algorithm', 4); sqlite> SELECT * FROM title_url; What is Information|information Amount of Information|amount-information Binary System|binary Boolean Lows|boolean What is Algorithm|algorithm
Сначала было создано представление title_url, затем – добавлена еще одна запись в реальную таблицу pages. При выборке из представления мы видим эту запись.
Часто в представления объединяют данные из нескольких таблиц:
sqlite> CREATE VIEW change_page AS . > SELECT pages._id, pages.title, . > sections.name, dates.date . > FROM pages JOIN dates JOIN sections . > ON pages._id = dates.page_id . > AND sections._id = pages.theme . > ORDER BY dates.date DESC; sqlite> SELECT * FROM change_page; 8|Boolean Lows|Boolean Algebra|2019-06-04 3|Amount of Information|Information|2019-06-03 9|What is Algorithm|Algorithm|2019-06-01 8|Boolean Lows|Boolean Algebra|2019-05-30 3|Amount of Information|Information|2019-05-26 7|Binary System|Digital Systems|2019-05-26 1|What is Information|Information|2019-05-25
Удаляются представления с помощью команды DROP VIEW:
sqlite> DROP VIEW title_url;
Что если нам нужны только пять страниц, которые последними претерпели изменения. Как вывести определенную часть таблицы? Для этих целей есть оператор LIMIT:
sqlite> SELECT * FROM change_page LIMIT 5; 8|Boolean Lows|Boolean Algebra|2019-06-04 3|Amount of Information|Information|2019-06-03 9|What is Algorithm|Algorithm|2019-06-01 8|Boolean Lows|Boolean Algebra|2019-05-30 3|Amount of Information|Information|2019-05-26
Работает он как с виртуальными, так и реальными таблицами:
sqlite> SELECT * FROM dates LIMIT 3; 1|1|2019-05-25 2|3|2019-05-26 3|7|2019-05-26 sqlite> SELECT * FROM dates LIMIT 2, 3; 3|7|2019-05-26 4|8|2019-05-30 5|9|2019-06-01
Если после LIMIT указано два числа, то первое обозначает смещение, и только второе – количество выбираемых строк. Кроме того, можно указывать смещение с помощью ключевого слова OFFSET:
sqlite> SELECT * FROM dates LIMIT 3 OFFSET 2; 3|7|2019-05-26 4|8|2019-05-30 5|9|2019-06-01
X Скрыть Наверх
Введение в реляционные базы данных. SQLite
Представления (VIEW) в MySQL
В комментариях Хабра упоминались вопросы по использованию представлений. Данный топик является обзором представлений, появившихся в MySQL версии 5.0. В нем рассмотрены вопросы создания, преимущества и ограничения представлений.
Что такое представление?
Представление (VIEW) — объект базы данных, являющийся результатом выполнения запроса к базе данных, определенного с помощью оператора SELECT, в момент обращения к представлению.
Представления иногда называют «виртуальными таблицами». Такое название связано с тем, что представление доступно для пользователя как таблица, но само оно не содержит данных, а извлекает их из таблиц в момент обращения к нему. Если данные изменены в базовой таблице, то пользователь получит актуальные данные при обращении к представлению, использующему данную таблицу; кэширования результатов выборки из таблицы при работе представлений не производится. При этом, механизм кэширования запросов (query cache) работает на уровне запросов пользователя безотносительно к тому, обращается ли пользователь к таблицам или представлениям.
Представления могут основываться как на таблицах, так и на других представлениях, т.е. могут быть вложенными (до 32 уровней вложенности).
Преимущества использования представлений:
- Дает возможность гибкой настройки прав доступа к данным за счет того, что права даются не на таблицу, а на представление. Это очень удобно в случае если пользователю нужно дать права на отдельные строки таблицы или возможность получения не самих данных, а результата каких-то действий над ними.
- Позволяет разделить логику хранения данных и программного обеспечения. Можно менять структуру данных, не затрагивая программный код, нужно лишь создать представления, аналогичные таблицам, к которым раньше обращались приложения. Это очень удобно когда нет возможности изменить программный код или к одной базе данных обращаются несколько приложений с различными требованиями к структуре данных.
- Удобство в использовании за счет автоматического выполнения таких действий как доступ к определенной части строк и/или столбцов, получение данных из нескольких таблиц и их преобразование с помощью различных функций.
Ограничения представлений в 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, выбирающий данные из таблиц и/или других представлений, которые будут содержаться в представлении
- OR REPLACE — при использовании данной конструкции в случае существования представления с таким именем старое будет удалено, а новое создано. В противном случае возникнет ошибка, информирующая о сществовании представления с таким именем и новое представление создано не будет. Следует отметить одну особенность — имена таблиц и представлений в рамках одной базы данных должны быть уникальны, т.е. нельзя создать представление с именем уже существующей таблицы. Однако конструкция OR REPLACE действует только на представления и замещать таблицу не будет.
- ALGORITM — определяет алгоритм, используемый при обращении к представлению (подробнее речь об этом пойдет ниже).
- column_list — задает имена полей представления.
- 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 .
- Если в обоих операторах встречается условие WHERE, то оба этих условия будут выполнены как если бы они были объединены оператором AND.
- Если в определении представления есть конструкция ORDER BY, то она будет работать только в случае отсутствия во внешнем операторе SELECT, обращающемся к представлению, собственного условия сортировки. При наличии конструкции ORDER BY во внешнем операторе сортировка, имеющаяся в определении представления, будет проигнорирована.
- При наличии в обоих операторах модификаторов, влияющих на механизм блокировки, таких как 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 .
- В случае UNDEFINED MySQL пытается использовать MERGE везде где это возможно, так как он более эффективен чем TEMPTABLE и, в отличие от него, не делает представление не обновляемым.
- Если вы явно указываете MERGE, а определение представления содержит конструкции запрещающие его использование, то MySQL выдаст предупреждение и установит значение UNDEFIND.
Обновляемость представлений
- Соответствие 1 к 1 между строками представления и таблиц, на которых основано представление, т.е. каждой строке представления должно соответствовать по одной строке в таблицах-источниках.
- Поля представления должны быть простым перечислением полей таблиц, а не выражениеями 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 .
Таким образом, наше представление, основанное на двух таблицах, позволяет обновлять обе таблицы и добавлять данные только в одну из них.
Удачи в работе с представлениями!
Что такое представления VIEWS в базах данных? И зачем они нужны?
Многие начинающие администраторы и программисты баз данных, да и просто системные администраторы, которые обслуживают некую базу данных, не знают что такое представление или VIEWS, и зачем вообще они нужны. Сейчас мы попробуем разобраться, что же это такое.
Начнем с небольшой теории.
Что такое VIEWS?
VIEWS – представление, или, например, в PostgreSQL они называются «Видами» (т.е. Вид), русские админы часто называют их вьюхами, т.е. одно представление это вьюшка. Она представляет собой хранимый запрос к базе данных, также ее можно назвать виртуальная таблица, но в этой таблице данные не хранятся, а хранится только сам запрос. Но, тем не менее, к вьюшке можно обращаться как к обычной таблице и извлекать данные из нее.
Мы с Вами говорим о базах данных, в которых используется язык SQL, исходя из этого, можно сделать вывод, что VIEWS можно создать на данном языке. Это очень распространенный объект в базе данных, поэтому во всех СУБД есть возможность создавать представления в графическом интерфейсе путем нажатия кнопки «Создать представление» или «Создать новый вид», а также, конечно же, с использованием инструкции «CREATE VIEW».
Но перед тем как учиться создавать вьюшки, давайте поговорим о том, зачем они нужны и какие преимущества они нам дадут.
Зачем нужны представления?
Одним из главных достоинством представлений является то, что они сильно упрощают взаимодействие с данными в базе данных. Допустим, Вам необходимо каждый раз делать сложную по своей структуре выборку, а как Вы знаете, запрос на выборку может быть, ну просто очень сложный и этому нет предела. И если не было бы вьюшек, то Вам приходилось бы каждый раз запускать этот запрос, или даже его модифицировать, например, для вставки условий. А так как у нас есть такие объекты как представления, нам этого делать не придется. Мы просто на всего создадим одну вьюшку, и потом уже к ней будем обращаться с помощью уже простых запросов, которые также можно делать сложными, если это необходимо. Например, вьюшки также можно объединять с другими таблицами или другими представлениями.
К представлениям можно обращаться и из приложений, например, Вам нужно вывести какой-нибудь отчет, формирование которого требует каких-то расчетов, это легко можно реализовать путем написания необходимого запроса (в котором и будут рассчитываться данные, например из разных таблиц) и вставке этого запроса во вьюшку. А потом уже обращаться к этой вьюшке, например с помощью такого простого запроса как:
SELECT * FROM TableName
Как создать представление VIEWS?
Теперь давайте поговорим о том, как создавать эти самые вьюшки. Во-первых, сразу скажу, что для этого необходимы знания SQL (для построения сложных запросов). Во-вторых, Вы за ранее должны определиться, что Вам необходимо вывести в результате того или иного запроса. Рассматривать процесс создания представления путем нажатия кнопок мы не будем, так это достаточно просто. Мы рассмотрим создание VIEWS с использованием языка SQL (хотя и это тоже просто).
Например, в PostgreSQL запрос создания представления будет выглядеть так:
CREATE VIEW MyView AS SELECT id, name, org FROM work.TableName
- CREATE VIEW – команда создания представления;
- MyView – название Вашей будущей вьюшки;
- SELECT id, name, org FROM work.TableName – запрос на выборку.
Здесь мы использовали простой запрос на выборку, Вы в свою очередь можете писать любой запрос, даже с объединением нескольких таблиц и условий к ним.
Полный синтаксис команды CREATE VIEW (в PostgreSQL) выглядит следующим образом:
CREATE [OR REPLACE] [ALGORITHM = ] VIEW view_name [(column_list)] AS select_statement [WITH [CASCADED | LOCAL] CHECK OPTION]
После того, как Вы создали представление, Вы можете к нему обращаться. А данные, которые будет выводить вьюшка, будут изменяться в зависимости от изменений данных в исходных таблицах, так как данные во вьюшке формируются при обращении к этому представлению. Исходя из этого, можно сделать вывод, что данные, которые выводит вьюшка, будут всегда актуальные.
У меня все, надеюсь, теперь у Вас есть представление о том, что такое VIEWS, пока!