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
Представление Базы Данных
Представление Базы Данных это результат посланных запросов к данным, которые находятся в таблице базы данных. В отличие от обычных таблиц в реляционной базе данных, представление является виртуальной таблицей, рассчитанной или собранной динамически из данных БД при запросе доступа к ней. Изменения, применяемые к данным соответствующей базовой таблице, воспроизводятся на данных, которые будут показаны при последующем вызове представления.
Преимущества представления над таблицами:
- Представления могут предоставлять подмножества данных, которые содержатся в таблице.
- Представления могут присоединять и упрощать множество таблиц в одну виртуальную таблицу.
Представлению не требуется много места; база данных содержит только определение представления, а не копию всех данных, которые оно предоставляет.
Вы можете манипулировать вложенными представлениями, так как одно представление может собрать данные из других представлений.
Данные представлений не упорядочены. Представление является реляционной таблицей, соответственно, предоставляет только набор данных. Однако, вы можете получить отсортированные данные из представления таким же образом, каким вы это делаете, работая с любой другой таблицей — в качестве части запроса для этого представления.
Представления Базы Данных AnyLogic
AnyLogic предоставляет пользователям набор предопределенных представлений базы данных, журналов действий собранных во время моделирования.
Вы также можете создать свое собственное представление базы данных, используя свои собственные таблицы базы данных, или таблицы с исходными данными.
Как создать новое представление базы данных
- В разделе Проекты, нажмите правой кнопки на элемент Представление БД из всплывающего меню.
- Вы увидите диалоговое окно Новое представление БД.
- Укажите Имя представления в поле Имя вида.
- Выделите таблицы, которые вы хотите включить в это представление базы данных.
- Когда закончите, нажмите на кнопку OK.
- В центре рабочей области вы увидите редактор представления базы данных, который будет отображать содержимое таблицы.
- В меню дерева Проекты, новое представление будет расположено в подветви ветки База Данных модели.
- В панели Свойства, вы найдете свойства представления (см. описание ниже). Вы можете изменять представление, редактируя SQL запрос этого представления в поле Задание представления.
Свойства
Имя — Название представления базы данных.
Мне не нужен этот журнал — [Параметр виден, если представление является журналом AnyLogic] Если опция отмечена, данные для этого журнала не собираются.
Создать редактируемую копию этого представления — [Параметр виден, если представление является журналом AnyLogic] Щелкните мышью по этой кнопке, чтобы создать копию этого нередактируемого представления и настроить его (путем редактирования SQL запроса в поле Задание представления).
Задание представления — [Параметр виден, если представление базы данных создано пользователем] SQL запрос, формирующий это представление. Если вам будет нужно изменить представление, отредактируйте этот запрос.
Столбцы — [Параметр виден, если представление базы данных создано пользователем] Нередактируемый список столбцов представления (он формируется SQL запросом представления). Вы можете видеть Имя столбца и Тип для каждого столбца этого представления.
Представления (VIEW) в MySQL
Эта статья является обзором представлений, появившихся в MySQL версии 5.0. В ней рассмотрены вопросы создания, преимущества и ограничения представлений.
Что такое представление?
Представление (VIEW) — объект базы данных, являющийся результатом выполнения запроса к базы данных, определенного с помощью оператора SELECT, в момент обращения к представлению.
Представления иногда называют «виртуальными таблицами». Такое название связано с тем, что представление доступно для пользователя как таблица, но само оно не содержит данных, а извлекает их из таблиц в момент обращения к нему. Если данные изменены в базовой таблице, то пользователь получит актуальные данные при обращении к представлению, использующему данную таблицу; кэширования результатов выборки из таблицы при работе представлений не производится. При этом, механизм кэширования запросов (query cache) работает на уровне запросов пользователя безотносительно к тому, обращается ли пользователь к таблицам или представлениям.
Представления могут основываться как на таблицах, так и на других представлениях, т.е. могут быть вложенными (до 32 уровней вложенности).
Преимущества использования представлений:
- Дает возможность гибкой настройки прав доступа к данным за счет того, что права даются не на таблицу, а на представление. Это очень удобно в случае если пользователю нужно дать права на отдельные строки таблицы или возможность получения не самих данных, а результата каких-то действий над ними.
- Позволяет разделить логику хранения данных и программного обеспечения. Можно менять структуру данных, не затрагивая программный код, нужно лишь создать представления, аналогичные таблицам, к которым раньше обращались приложения. Это очень удобно когда нет возможности изменить программный код или к одной базе данных обращаются несколько приложений с различными требованиями к структуре данных.
- Удобство в использовании за счет автоматического выполнения таких действий как доступ к определенной части строк и/или столбцов, получение данных из нескольких таблиц и их преобразование с помощью различных функций.
Ограничения представлений в MySQL
В статье приведены ограничения для версии MySQL 5.1 (в дальнейшем их число может сократиться).
- нельзя повесить триггер на представление,
- нельзя сделать представление на основе временных таблиц; нельзя сделать временное представление;
- в определении представления нельзя использовать подзапрос в части FROM,
- в определении представления нельзя использовать системные и пользовательские переменные; внутри хранимых процедур нельзя в определении представления использовать локальные переменные или параметры процедуры,
- в определении представления нельзя использовать параметры подготовленных выражений (PREPARE),
- таблицы и представления, присутствующие в определении представления должны существовать.
- только представления, удовлетворяющие ряду требований, допускают запросы типа UPDATE, DELETE и INSERT.
Создание представлений
Для создания представления используется оператор CREATE VIEW , имеющий следующий синтаксис:
CREATE [ OR REPLACE ]
[ ALGORITHM = UNDEFINED ]
VIEW view_name [ ( column_list ) ]
AS select_statement
[ WITH [ CASCADED | LOCAL ] CHECK OPTION ]
view_name — имя создаваемого представления. select_statement — оператор SELECT, выбирающий данные из таблиц и/или других представлений, которые будут содержаться в представлении
Оператор CREATE VIEW содержит 4 необязательные конструкции:
- OR REPLACE — при использовании данной конструкции в случае существования представления с таким именем старое будет удалено, а новое создано. В противном случае возникнет ошибка, информирующая о сществовании представления с таким именем и новое представление создано не будет. Следует отметить одну особенность — имена таблиц и представлений в рамках одной базы данных должны быть уникальны, т.е. нельзя создать представление с именем уже существующей таблицы. Однако конструкция OR REPLACE действует только на представления и замещать таблицу не будет.
- ALGORITM — определяет алгоритм, используемый при обращении к представлению (подробнее речь об этом пойдет ниже).
- column_list — задает имена полей представления.
- WITH CHECK OPTION — при использовании данной конструкции все добавляемые или изменяемые строки будут проверяться на соответствие определению представления. В случае несоответствия данное изменение не будет выполнено. Обратите внимание, что при указании данной конструкции для необновляемого представления возникнет ошибка и представление не будет создано. (подробнее речь об этом пойдет ниже).
По умолчанию колонки представления имеют те же имена, что и поля возращаемые оператором SELECT в определении представления. При явном указании имен полей представления column_list должен включать по одному имени для каждого поля разделенных запятой. Существует две причины по которым желательно использовать явное указание имен полей представления:
- Имена полей представления должны быть уникальны в пределах данного представления. При создании представления основанного на нескольких таблицах возможна ситуация повторения имен полей представления. Например:
CREATE VIEW v AS SELECT a.id, b.id FROM a,b;
Для избежания такой ситуации нужно явно указывать имена полей представления
CREATE VIEW v ( a_id, b_id ) AS SELECT a.id, b.id FROM a,b;
Того же результата можно добиться, используя синонимы (алиасы) для названий колонок:
CREATE VIEW v AS SELECT a.id a_id, b.id b_id FROM a,b; - В случае если в определении представления получаемые данные преобразуются с помощью каких-то функций, то именем поля будет данное выражение, что не очень удобно для дальнейших ссылок на это поле. Напимер:
CREATE VIEW v AS SELECT group_concat ( distinct column_name oreder by column_name separator ‘+’ ) FROM table_name;
Для просмотра содержимого представления мы используем оператор SELECT (полностью аналогично как в случае простой таблицы), с другой строны, оператор SELECT есть в самом определении представления, т.е. получается вложенная конструкция — запрос в запросе. При этом, некоторые конструкции оператора SELECT могут присутствовать в обоих операторах. Возможны три варианта развития событий: они обе будут выполнены, одна из них будет проигнорированна и результат неопределен. Рассмотрим подробнее эти случаи:
- Если в обоих операторах встречается условие WHERE , то оба этих условия будут выполнены как если бы они были объединены оператором AND .
- Если в определении представления есть конструкция ORDER BY , то она будет работать только в случае отсутствия во внешнем операторе SELECT , обращающемся к представлению, собственного условия сортировки. При наличии конструкции ORDER BY во внешнем операторе сортировка, имеющаяся в определении представления, будет проигнорирована.
- При наличии в обоих операторах модификаторов, влияющих на механизм блокировки, таких как HIGH_PRIORITY , результат их совместного действия неопределен. Для избежания неопределенности рекомендуется в определении представления не использовать подобные модификаторы.
Алгоритмы представлений
Существует два алгоритма, используемых MySQL при обращении к представлению: MERGE и TEMPTABLE .
В случае алгоритма MERGE , MySQL при обращении к представлению добавляет в использующийся оператор соответствующие части из определения представления и выполняет получившийся оператор.
В случае алгоритма TEMPTABLE , MySQL заносит содержимое представления во временную таблицу, над которой затем выполняется оператор обращенный к представлению.
Обратите внимание: в случае использования этого алгоритма представление не может быть обновляемым (см. далее).
При создании представления есть возможность явно указать используемый алгоритм с помощью необязательной конструкции [ ALGORITHM = MERGE ]
UNDEFINED означает, что MySQL сам выбирает какой алгоритм использовать при обращении к представлению. Это значение по умолчанию, если данная конструкция отсутствует.
Использование алгоритма MERGE требует соответствия 1 к 1 между строками таблицы и основанного на ней представления.
Пусть наше представление выбирает отношение числа просмотров к числу ответов для тем форума:
CREATE VIEW v AS SELECT subject, num_views/num_replies as param FROM topics WHERE num_replies> 0 ;
Для данного представления каждая строка соответствует единственной строке из таблицы topics, т.е. может быть использован алгоритм MERGE . Рассмотрим следующее обращение к нашему представлению:
SELECT subject, param FROM v WHERE param> 1000 ;
В случае 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 ;
Если в определении представления используются групповые функции (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;
Найдем максимальное количество тем в форуме:
SELECT MAX ( num ) FROM v;
Если бы использовался алгоритм MERGE , то этот запрос был бы преобразован следующим образом:
SELECT max ( count ( * ) ) FROM topics GROUP BY forum_id;
Выполнение этого запроса приводит к ошибке » 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;
Подводя итог, следует отметить, что нет серьезных причин явно указывать алгоритм при создании представления, так как:
- В случае UNDEFINED MySQL пытается использовать MERGE везде где это возможно, так как он более эффективен чем TEMPTABLE и, в отличие от него, не делает представление не обновляемым.
- Если вы явно указываете MERGE , а определение представления содержит конструкции запрещающие его использование, то MySQL выдаст предупреждение и установит значение UNDEFIND .
Обновляемость представлений
Представление называется обновляемым, если к нему могут быть применимы операторы UPDATE и DELETE для изменения данных в таблицах, на которых основано представление. Для того, чтобы представление было обновляемым должно быть выполнено 2 условия:
- Соответствие 1 к 1 между строками представления и таблиц, на которых основано представление, т.е. каждой строке представления должно соответствовать по одной строке в таблицах-источниках.
- Поля представления должны быть простым перечислением полей таблиц, а не выражениеями 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 >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 )
Однако, если мы попробуем установить значение 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’
Не все обновляемые представления позволяют добавление данных:
punbb >INSERT INTO v ( subject,num_views ) VALUES ( ‘test1’ , 4000 ) ;
ERROR 1369 ( HY000 ) : CHECK OPTION failed ‘punbb.v’
Причина в том, что значением по умолчанию колонки 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’
С другой строны:
punbb >INSERT INTO v ( forum_name ) VALUES ( ‘TEST’ ) ;
Query OK, 1 row affected ( 0.00 sec )
Таким образом, наше представление, основанное на двух таблицах, позволяет обновлять обе таблицы и добавлять данные только в одну из них.
Удачи в работе с представлениями!
Оператор 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 |