Для чего нужны представления view в mysql
Представления (VIEW) в MySQL
Эта статья является обзором представлений, появившихся в MySQL версии 5.0. В ней рассмотрены вопросы создания, преимущества и ограничения представлений.
Что такое представление?
Представление (VIEW) — объект базы данных, являющийся результатом выполнения запроса к базе данных, определенного с помощью оператора SELECT, в момент обращения к представлению.
Представления иногда называют «виртуальными таблицами». Такое название связано с тем, что представление доступно для пользователя как таблица, но само оно не содержит данных, а извлекает их из таблиц в момент обращения к нему. Если данные изменены в базовой таблице, то пользователь получит актуальные данные при обращении к представлению, использующему данную таблицу; кэширования результатов выборки из таблицы при работе представлений не производится. При этом, механизм кэширования запросов (query cache) работает на уровне запросов пользователя безотносительно к тому, обращается ли пользователь к таблицам или представлениям.
Представления могут основываться как на таблицах, так и на других представлениях, т.е. могут быть вложенными (до 32 уровней вложенности).
Преимущества использования представлений:
Ограничения представлений в MySQL
В статье приведены ограничения для версии MySQL 5.1 (в дальнейшем их число может сократиться).
Создание представлений
Для создания представления используется оператор CREATE VIEW, имеющий следующий синтаксис:
view_name — имя создаваемого представления. select_statement — оператор SELECT, выбирающий данные из таблиц и/или других представлений, которые будут содержаться в представлении
Оператор CREATE VIEW содержит 4 необязательные конструкции:
По умолчанию колонки представления имеют те же имена, что и поля возращаемые оператором SELECT в определении представления. При явном указании имен полей представления column_list должен включать по одному имени для каждого поля разделенных запятой. Существует две причины по которым желательно использовать явное указание имен полей представления:
1. Имена полей представления должны быть уникальны в пределах данного представления. При создании представления основанного на нескольких таблицах возможна ситуация повторения имен полей представления. Например:
Для избежания такой ситуации нужно явно указывать имена полей представления
Того же результата можно добиться, используя синонимы (алиасы) для названий колонок:
Система управления базами данных SQLite. Изучаем язык запросов SQL и реляционные базы данных на примере библиотекой SQLite3. Курс для начинающих.
Тема 14: VIEW в SQL на примере базы данных SQLite: CREATE, DROP, UPDATE
Привет, посетитель сайта ZametkiNaPolyah.ru! Продолжаем изучать базы данных и наше знакомство с библиотекой SQLite3. В этой записи мы с вами разберемся с представлениями и их использованием в реляционных базах данных. Вообще VIEW в SQL довольно полезная штука, которая позволяет упростить SQL запросы SELECT, а также скрыть логику базы данных от пользователей и программного кода, тем самым создав дополнительный уровень абстракции, который защищает наши базы данных от вредоносного вмешательства. Многие считают VIEW виртуальными таблицами, что не совсем правильно, так как представление — это запрос хранимый в базе данных и доступный по его имени (VIEW это такой же объект базы данных, как скажем, триггер или таблица). Делать выборку данных из VIEW во многих СУБД намного быстрее, например, MySQL сервер любит кэшировать результаты запросов, а VIEW, как вы поняли, есть ни что иное, как запрос.
VIEW в SQL на примере базы данных SQLite: CREATE, DROP, UPDATE.
В этой записи мы с вами будем разбираться с использованием VIEW в SQL и реляционных базах данных на пример библиотеки SQLite. Сначала мы поговорим о том, что собой представляют VIEW в базах данных и разберемся с тем, как мы можем использовать представления. Затем поговорим про особенности работы представлений в SQLite3 и разберем SQL синтаксис VIEW, реализованный в данной СУБД. И затем попробуем поработать с VIEW в базах данных под управлением SQLite.
Что такое VIEW в контексте языка SQL и баз данных?
Прежде чем ответить на вопрос зачем нужны VIEW в SQL и реляционных базах данных давайте ответим на вопрос: «что такое VIEW в языке запросов SQL?». В Википедии, на мой взгляд, формулировка определения VIEW в SQL написана неправильно. Так как представление не является виртуальной таблицей (как минимум, для создания виртуальных таблиц в SQLite предусмотрен отдельный синтаксис).
Документация MySQL говорит нам о том, что представление можно рассматривать, как виртуальную таблицу, но не утверждает, что VIEW – это VIRTUAL TABLE. В разделе VIEW документации Oracle упоминаний про виртуальную таблицу при беглом чтении я не встретил. Конечно, кто-то со мной может не согласиться, но я считаю, что VIEW – это не виртуальная таблица. Итак, мы разобрались с тем, чем не является VIEW в SQL и реляционных базах данных.
Теперь давайте дадим правильное определение термину VIEW в контексте языка SQL. VIEW – это хранимый запрос в базе данных. Возможно, представление называют виртуальной таблицей (virtual table) по той причине, что структура VIEW полностью повторяет структуру результирующей таблицы запроса SELECT, но опять же, это не повод называть VIEW виртуальной таблицей.
Сервер MySQL довольно быстро работает с представлениями за счет того, что MySQL очень любит кэшировать результаты запросов, в принципе, многие современные системы управления базами данных любят и хорошо кэшируют запросы, поэтому вы не всегда сможете заметить разницу работы между VIEW и таблицей базы данных, особенно, если ваши таблицы небольшие.
Вместо термина VIEW в различных источниках вы можете встретит термины представления или просмотры. Мне удобнее использовать термин представление. Давайте вернемся к определению термина представления в базах данных. Итак, представление – это хранимый в базе данных запрос, которому нужно дать имя. Когда мы создали представление, мы можем обращаться к нему, как к обычной таблице базы данных, используя то имя, которое мы написали после команды CREATE VIEW.
Единственная команда языка SQL, возвращающая в результате своей работы таблицу – это команда SELECT, с помощь которой мы не только делаем выборку данных, но и создаем VIEW в базе данных. Практически в любой СУБД для работы с представлениями доступны все команды манипуляции данными, но в библиотеки SQLite3 это утверждение не верно, об этом мы поговорим чуть ниже.
Напомним себе, что команды: UPDATE, SELECT, INSERT, DELETE, относятся к командам манипуляции данными. VIEW создается на основе запроса SELECT. Но, например, в базах данных MySQL, вы не сможете использовать команды UPDATE, INSERT, DELETE, если SQL запрос создающий VIEW содержит:
Поэтому рекомендую вам сперва ознакомиться с документацией той или иной СУБД, прежде чем начать создавать представления в базе данных. Например, документация MySQL так и говорит, что команды манипуляции данными (за исключением SELECT, который можно применять к любому представлению) можно применять к VIEW в том случае, когда строки VIEW совпадают со строками таблицы в базе данных (это несколько вольный и не совсем точный перевод).
Мы разобрались с тем, что VIEW – это именованный запрос SELECT, который хранится в базе данных. Каждый раз, когда мы обращаемся к VIEW, СУБД выполняет этот запрос SELECT, а следом за ним, она выполняет наш запрос. Думаю, ничего сложно в понимание того, что такое VIEW нет, давайте теперь разберемся для чего мы можем использовать VIEW.
Использование представлений в SQL и реляционных базах данных
Первое и очевидное применение VIEW в базах данных заключается в том, чтобы упростить запросы на выборку данных. Ведь нам же не хочется писать полотно SELECT, которое объединяет три-четыре таблицы каждый раз, а потом еще задавать какие-нибудь условия выборки данных клаузулой WHERE? Итак, первое, для чего мы можем использовать представление – это для упрощения запросов выборки данных.
Второй пункт можно назвать безопасность. Во-первых, при помощи VIEW можно скрыть бизнес-логику и архитектуру базы данных от прикладных приложений, сделав так, что программа будет обращаться не к таблицам базы данных, а к представлениям. Во-вторых, так вы избавитесь от некоторых видов SQL-инъекций, плюсом к этому, особо талантливые программисты лишаться «чудесной возможности» конкатенировать SQL запросы (как только вы увидите, что программист конкатенирует SQL запрос, можете зарядить ему в щи с вертушки и прокричать: я угорел по базам данных, а ты не знаешь даже таких простых вещей), тем самым вы еще уменьшите вариативность атак на вашу базу данных.
Третий вариант применения VIEW сводится к обновлениям. Вы редко можете встретить базу данных без прикладного приложения. Мир не стоит на месте, всё летит, всё развивается, компании растут и объединяются, у клиентов появляются всё новые потребности и рано или поздно старые приложения становятся неудобными и возникает потребность в их модификации. Мы уже говорили, что VIEW позволяют скрывать бизнес-логику базы данных, но не всегда, создавая базу данных, вы создаете представления. Поэтому если у вас возникла потребность в обновлении программного кода, работающего с базой данных, вы можете создать новую структуру базы данных в виде представлений, с которой будет работать новый программный код, тем самым вы разделите схему хранения данных и схему представления данных. Если потребности в разделении схем нет, то в дальнейшем вы можете отказаться от использования VIEW и вернуться к таблицам, после того, как код приложения будет обновлен.
Пожалуй, это три самых важных аспекта работы с базами данных, для которых можно и даже нужно использовать представления.
Особенности работы с VIEW в базах данных SQLite
Теперь поговорим про особенности VIEW в базах данных SQLite, мы уже говорили о том, что представления в SQLite нельзя редактировать, их можно только создавать, удалять и делать выборку из VIEW, в то время, как другие СУБД позволяют выполнять другие команды манипуляции данными представлений.
Но это не совсем так, все дело в том, что SQLite не дает возможность редактировать представления при помощи обычных SQL запросов. Но в базах данных есть триггеры, которые успешно эмитируют работу команд UPDATE, INSERT и DELETE. Соответственно, если мы можем:
То ничто нам не помешает выполнить те же самые операции с VIEW в SQLite, правда они будут немного сложнее из-за того, что нам придется использовать триггеры.
SQL синтаксис VIEW в базах данных SQLite
Давайте разберемся с синтаксисом SQL, реализованным в SQLite, который позволяет нам создавать и удалять представления из базы данных. Начнем мы с синтаксиса создания представлений в SQLite, он показан на рисунке ниже.
SQL синтаксис создания VIEW в базах данных SQLite
Отметим, что создание VIEW начинается с той же команды, что и создание таблицы в базе данных: с команды CREATE. Это обусловлено тем, что VIEW – это такой же объект базы данных, как и таблица. Далее мы указываем, что хотим создать представление при помощи ключевого слова VIEW. Представление может быть временным, поэтому после ключевого слова CREATE вы можете использовать слово TEMP или TEMPORARY. Если вы не уверены, что создаете представление с уникальным именем и не хотите возникновения ошибок при создании VIEW в базе данных, то можете использовать ключевую фразу IF NOT EXIST (кстати, оператор EXISTS может быть использован для создания подзапроса SELECT). Далее вам необходимо указать имя представления, которое должно быть уникальным, в качестве имени можно использовать квалификатор, в том случае, если вы работаете с несколькими базами данных и хотите быть уверенным в том, что создаете VIEW для нужной базы данных.
После имени представления идет ключевое слово AS и запрос SELECT, который как раз-таки и будет храниться в файле базы данных SQLite и к которому SQLite будет обращаться по тому имени, которое вы указали при создании VIEW.
Теперь рассмотрим SQL синтаксис удаления VIEW из базы данных под управлением SQLite3. Он показан на рисунке ниже.
SQL синтаксис удаления VIEW из базы данных под управлением SQLite3
Хоть обычное представление, хоть временное, удаляются из базы данных под управлением SQLite одинаково: ключевое слово DROP, за которым следует VIEW, говорит SQLite о том, что вы хотите удалить из базы данных не просто объект, а представление. Далее следует конструкция IF EXISTS, которая осуществляет проверку наличия представления в базе данных, чтобы SQLite не возвращала ошибки в том случае, если представление, которое вы хотите удалить, уже удалено. После чего идет имя представления или квалификатор.
Отметим, что для представлений в SQLite команда ALTER не реализована. Если вам нужно изменить структуру VIEW, то вам нужно удалить старое представление, а затем создать новой и с новой структурой.
Итак, мы разобрались с SQL синтаксисом VIEW в базах данных SQLite и можем начинать работать с представлениями.
Что такое представления VIEWS в базах данных? И зачем они нужны?
Многие начинающие администраторы и программисты баз данных, да и просто системные администраторы, которые обслуживают некую базу данных, не знают что такое представление или VIEWS, и зачем вообще они нужны. Сейчас мы попробуем разобраться, что же это такое.
Начнем с небольшой теории.
Что такое VIEWS?
VIEWS – представление, или, например, в PostgreSQL они называются «Видами» (т.е. Вид), русские админы часто называют их вьюхами, т.е. одно представление это вьюшка. Она представляет собой хранимый запрос к базе данных, также ее можно назвать виртуальная таблица, но в этой таблице данные не хранятся, а хранится только сам запрос. Но, тем не менее, к вьюшке можно обращаться как к обычной таблице и извлекать данные из нее.
Мы с Вами говорим о базах данных, в которых используется язык SQL, исходя из этого, можно сделать вывод, что VIEWS можно создать на данном языке. Это очень распространенный объект в базе данных, поэтому во всех СУБД есть возможность создавать представления в графическом интерфейсе путем нажатия кнопки «Создать представление» или «Создать новый вид», а также, конечно же, с использованием инструкции «CREATE VIEW».
Но перед тем как учиться создавать вьюшки, давайте поговорим о том, зачем они нужны и какие преимущества они нам дадут.
Зачем нужны представления?
Одним из главных достоинством представлений является то, что они сильно упрощают взаимодействие с данными в базе данных. Допустим, Вам необходимо каждый раз делать сложную по своей структуре выборку, а как Вы знаете, запрос на выборку может быть, ну просто очень сложный и этому нет предела. И если не было бы вьюшек, то Вам приходилось бы каждый раз запускать этот запрос, или даже его модифицировать, например, для вставки условий. А так как у нас есть такие объекты как представления, нам этого делать не придется. Мы просто на всего создадим одну вьюшку, и потом уже к ней будем обращаться с помощью уже простых запросов, которые также можно делать сложными, если это необходимо. Например, вьюшки также можно объединять с другими таблицами или другими представлениями.
К представлениям можно обращаться и из приложений, например, Вам нужно вывести какой-нибудь отчет, формирование которого требует каких-то расчетов, это легко можно реализовать путем написания необходимого запроса (в котором и будут рассчитываться данные, например из разных таблиц) и вставке этого запроса во вьюшку. А потом уже обращаться к этой вьюшке, например с помощью такого простого запроса как:
Как создать представление VIEWS?
Теперь давайте поговорим о том, как создавать эти самые вьюшки. Во-первых, сразу скажу, что для этого необходимы знания SQL (для построения сложных запросов). Во-вторых, Вы за ранее должны определиться, что Вам необходимо вывести в результате того или иного запроса. Рассматривать процесс создания представления путем нажатия кнопок мы не будем, так это достаточно просто. Мы рассмотрим создание VIEWS с использованием языка SQL (хотя и это тоже просто).
Например, в PostgreSQL запрос создания представления будет выглядеть так:
Здесь мы использовали простой запрос на выборку, Вы в свою очередь можете писать любой запрос, даже с объединением нескольких таблиц и условий к ним.
Полный синтаксис команды CREATE VIEW (в PostgreSQL) выглядит следующим образом:
После того, как Вы создали представление, Вы можете к нему обращаться. А данные, которые будет выводить вьюшка, будут изменяться в зависимости от изменений данных в исходных таблицах, так как данные во вьюшке формируются при обращении к этому представлению. Исходя из этого, можно сделать вывод, что данные, которые выводит вьюшка, будут всегда актуальные.
У меня все, надеюсь, теперь у Вас есть представление о том, что такое VIEWS, пока!
Представления
Представление — это виртуальная таблица, содержимое которой определяется запросом. Как и таблица, представление состоит из ряда именованных столбцов и строк данных. Пока представление не будет проиндексировано, оно не существует в базе данных как хранимая совокупность значений. Строки и столбцы данных извлекаются из таблиц, указанных в определяющем представление запросе и динамически создаваемых при обращениях к представлению.
Представление выполняет функцию фильтра базовых таблиц, на которые оно ссылается. Определяющий представление запрос может быть инициирован в одной или нескольких таблицах или в других представлениях текущей или других баз данных. Кроме того, для определения представлений с данными из нескольких разнородных источников можно использовать распределенные запросы. Это полезно, например, если нужно объединить структурированные подобным образом данные, относящиеся к разным серверам, каждый из которых хранит данные конкретного отдела организации.
Представления обычно используются для направления, упрощения и настройки восприятия каждым пользователем информации базы данных. Представления могут использоваться как механизмы безопасности, давая возможность пользователям обращаться к данным через представления, но не предоставляя им разрешений на непосредственный доступ к базовым таблицам, лежащим в основе представлений. Представления могут использоваться для обеспечения интерфейса обратной совместимости, моделирующего таблицу, которая существует, но схема которой изменилась. Представления могут также использоваться при прямом и обратном копировании данных в SQL Server для повышения производительности и секционирования данных.
Типы представлений
Кроме основных определяемых пользователем представлений, выполняющих стандартные роли, в SQL Server предусмотрены следующие типы представлений, которые соответствуют специальным назначениям в базе данных.
Индексированные представления
Индексированным называется материализованное представление. Это означает, что определение представления вычисляется, а результирующие данные хранятся точно так же, как и таблица. Индексировать представление можно, создав для него уникальный кластеризованный индекс. Индексированные представления могут существенно повысить производительность некоторых типов запросов. Индексированные представления эффективнее всего использовать в запросах, группирующих множество строк. Они не очень хорошо подходят для часто обновляющихся базовых наборов данных.
Системные представления
Системные представления предоставляют доступ к метаданным каталога. Системные представления можно использовать для получения сведений об экземпляре SQL Server или объектах, определенных в экземпляре. Например, получить сведения об определяемых пользователем базах данных, доступных в экземпляре, можно через представление каталога sys.databases. Дополнительные сведения см. в разделе Системные представления (Transact-SQL).
Общие задачи работы с представлениями
В следующей таблице приведены ссылки на общие задачи, связанные с созданием или изменением представления.
Представления (VIEW) в MySQL
Эта статья является обзором представлений, появившихся в MySQL версии 5.0. В ней рассмотрены вопросы создания, преимущества и ограничения представлений.
Что такое представление?
Представление (VIEW) — объект базы данных, являющийся результатом выполнения запроса к базы данных, определенного с помощью оператора SELECT, в момент обращения к представлению.
Представления иногда называют «виртуальными таблицами». Такое название связано с тем, что представление доступно для пользователя как таблица, но само оно не содержит данных, а извлекает их из таблиц в момент обращения к нему. Если данные изменены в базовой таблице, то пользователь получит актуальные данные при обращении к представлению, использующему данную таблицу; кэширования результатов выборки из таблицы при работе представлений не производится. При этом, механизм кэширования запросов (query cache) работает на уровне запросов пользователя безотносительно к тому, обращается ли пользователь к таблицам или представлениям.
Представления могут основываться как на таблицах, так и на других представлениях, т.е. могут быть вложенными (до 32 уровней вложенности).
Преимущества использования представлений:
Ограничения представлений в MySQL
В статье приведены ограничения для версии MySQL 5.1 (в дальнейшем их число может сократиться).
Создание представлений
Оператор CREATE VIEW содержит 4 необязательные конструкции:
По умолчанию колонки представления имеют те же имена, что и поля возращаемые оператором SELECT в определении представления. При явном указании имен полей представления column_list должен включать по одному имени для каждого поля разделенных запятой. Существует две причины по которым желательно использовать явное указание имен полей представления:
Для просмотра содержимого представления мы используем оператор SELECT (полностью аналогично как в случае простой таблицы), с другой строны, оператор SELECT есть в самом определении представления, т.е. получается вложенная конструкция — запрос в запросе. При этом, некоторые конструкции оператора SELECT могут присутствовать в обоих операторах. Возможны три варианта развития событий: они обе будут выполнены, одна из них будет проигнорированна и результат неопределен. Рассмотрим подробнее эти случаи:
Алгоритмы представлений
При создании представления есть возможность явно указать используемый алгоритм с помощью необязательной конструкции [ ALGORITHM = < UNDEFINED | MERGE | TEMPTABLE >]
UNDEFINED означает, что MySQL сам выбирает какой алгоритм использовать при обращении к представлению. Это значение по умолчанию, если данная конструкция отсутствует.
Использование алгоритма MERGE требует соответствия 1 к 1 между строками таблицы и основанного на ней представления.
Пусть наше представление выбирает отношение числа просмотров к числу ответов для тем форума:
Пусть наше представление выбирает количество тем для каждого форума:
Найдем максимальное количество тем в форуме:
Подводя итог, следует отметить, что нет серьезных причин явно указывать алгоритм при создании представления, так как:
Обновляемость представлений
Представление называется обновляемым, если к нему могут быть применимы операторы UPDATE и DELETE для изменения данных в таблицах, на которых основано представление. Для того, чтобы представление было обновляемым должно быть выполнено 2 условия:
Обновляемое представление может допускать добавление данных ( INSERT ), если все поля таблицы-источника, не присутствующие в представлении, имеют значения по умолчанию.
Обратите внимание: для представлений, основанных на нескольких таблицах, операция добавления данных ( INSERT ) работает только в случае если происходит добавление в единственную реальную таблицу. Удаление данных ( DELETE ) для таких представлений не поддерживается.
При использовании в определении представления конструкции WITH [ CASCADED | LOCAL ] CHECK OPTION все добавляемые или изменяемые строки будут проверяться на соответствие определению представления.
Иными словами, нельзя добавить или изменить данные в представлении таким образом, чтобы они не были доступны через представление.
Ключевые слова CASCADED и LOCAL определяют глубину проверки для представлений основанных на других представлениях:
Рассмотрим пример обновляемого представления, основанного на двух таблицах. Пусть наше представление выбирает темы форума с числом просмотров более 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
Однако, если мы попробуем установить значение num_views меньше 2000, то новое значение не будет удовлетворять условию WHERE num_views> 2000 в определении представления и обновления не произойдет.
Не все обновляемые представления позволяют добавление данных:
Причина в том, что значением по умолчанию колонки forum_id является 0, поэтому добавляемая строка не удовлетворяет условию WHERE forum_id=f.id в определении представления. Указать же явно значение forum_id мы не можем, так как такого поля нет в определении представления:
Таким образом, наше представление, основанное на двух таблицах, позволяет обновлять обе таблицы и добавлять данные только в одну из них.