Create view sql примеры

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

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

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

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

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

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

В статье приведены ограничения для версии MySQL 5.1 (в дальнейшем их число может сократиться).

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

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

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

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

Оператор CREATE VIEW содержит 4 необязательные конструкции:

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

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

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

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

Того же результата можно добиться, используя синонимы (алиасы) для названий колонок:

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

Вряд ли удобно использовать в дальнейшем в качестве имени поля `group_concat(DISTINCT username ORDER BY username separator ‘+’)`

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

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

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

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

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

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

При создании представления есть возможность явно указать используемый алгоритм с помощью необязательной конструкции [ALGORITHM = ]
UNDEFINED означает, что MySQL сам выбирает какой алгоритм использовать при обращении к представлению. Это значение по умолчанию, если данная конструкция отсутствует.

Использование алгоритма MERGE требует соответствия 1 к 1 между строками таблицы и основанного на ней представления.

Пусть наше представление выбирает отношение числа просмотров к числу ответов для тем форума:

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

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

Если в определении представления используются групповые функции (count, max, avg, group_concat и т.д.), подзапросы в части перечисления полей или конструкции DISTINCT, GROUP BY, то не выполняется требуемое алгоритмом MERGE соответствие 1 к 1 между строками таблицы и основанного на ней представления.

Пусть наше представление выбирает количество тем для каждого форума:

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

Если бы использовался алгоритм MERGE, то этот запрос был бы преобразован следующим образом:

Выполнение этого запроса приводит к ошибке "ERROR 1111 (HY000): Invalid USE of GROUP function", так как используется вложенность групповых функций.

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

Подводя итог, следует отметить, что нет серьезных причин явно указывать алгоритм при создании представления, так как:

  1. В случае UNDEFINED MySQL пытается использовать MERGE везде где это возможно, так как он более эффективен чем TEMPTABLE и, в отличие от него, не делает представление не обновляемым.
  2. Если вы явно указываете MERGE, а определение представления содержит конструкции запрещающие его использование, то MySQL выдаст предупреждение и установит значение UNDEFIND.

Представление называется обновляемым, если к нему могут быть применимы операторы UPDATE и DELETE для изменения данных в таблицах, на которых основано представление. Для того, чтобы представление было обновляемым должно быть выполнено 2 условия:

  1. Соответствие 1 к 1 между строками представления и таблиц, на которых основано представление, т.е. каждой строке представления должно соответствовать по одной строке в таблицах-источниках.
  2. Поля представления должны быть простым перечислением полей таблиц, а не выражениеями 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.

Однако, если мы попробуем установить значение num_views меньше 2000, то новое значение не будет удовлетворять условию WHERE num_views>2000 в определении представления и обновления не произойдет.

Не все обновляемые представления позволяют добавление данных:

Причина в том, что значением по умолчанию колонки forum_ >

С другой строны:

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

Читайте также:  Как выключить голосовую почту на велкоме

Вы создаете представление командой CREATE VIEW. Она состоит из слов CREATE VIEW (СОЗДАТЬ ПРЕДСТАВЛЕНИЕ), имени представления которое нужно создать, слова AS (КАК), и далее запроса, как в следующем примере: Теперь Вы имеете представление, называемое Londonstaff. Вы можете использовать это представление точно так же как и любую другую таблицу. Она может быть запрошена, модифицирована, вставлена в, удалена из, и соединена с, другими таблицами и представлениями. Давайте сделаем запрос такого представления ( вывод показан в Рисунке 20.1): 20.1 Рисунок 20.1: Представление Londonstaff Когда вы приказываете SQL выбрать(SELECT) все строки ( * ) из представления, он выполняет запрос содержащий в определении — Loncfonstaff, и возвращает все из его вывода. Имея предикат в запросе представления, можно вывести только те строки из представления, которые будут удовлетворять этому предикату. Вы могли бы вспомнить, что в Главе 15, вы имели таблицу, называемую Londonstaff, в ко- торую вы вставляли это же самое содержание ( конечно, мы понимаем что таблица — не слишком велика. Если это так, вы будете должны выбрать другое им для вашего представления). Преимущество использования представления, по сравнению с основной таблицы, в том, что представление будет модифицировано автоматически всякий раз, когда таблица лежащая в его основе изменяется. Содержание представления не фиксировано, и переназначается каждый раз когда вы ссылаетесь на представление в команде. Если вы добавите завтра другого, живущего в Лондоне продавца, он автоматически появится в представлении.

Представления значительно расширяют управление вашими данными. Это — превосходный способ дать публичный доступ к некоторой, но не всей инфор- мации в таблице. Если вы хотите чтобы ваш продавец был показан в таблице Продавцов, но при этом не были показаны комиссии других продавцов, вы могли бы создать представление с использованием следующего оператора ( вывод показан в Рисунке 20.2 ) Рисунок 20.2: Представление Salesown Другими словами, это представление — такое же как для таблицы Продавцов, за исключением того, что поле comm, не упоминалось в запросе, и следовательно не было включено в представление.

МОДИФИЦИРОВАНИЕ ПРЕДСТАВЛЕНИЙ

ИМЕНОВАНИЕ СТОЛБЦОВ

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

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

* когда два или более столбцов в объединении, имеют те же имена что в их базовой таблице.

Имена, которые могут стать именами полей, даются в круглых скобках ( ), после имени таблиц. Они не будут запрошены, если совпадают с именами полей запрашиваемой таблицы. Тип данных и размер этих полей будут отличаться от запрашиваемых полей которые "передаются" в них. Обычно вы не указываете новых имен полей, но если вы все таки сделали это, вы дол- жны делать это для каждого пол в представлении.

КОМБИНИРОВАНИЕ ПРЕДИКАТОВ ПРЕДСТАВЛЕНИЙ И ОСНОВНЫХ ЗАПРОСОВ В ПРЕДСТАВЛЕНИЯХ

ГРУППОВЫЕ ПРЕДСТАВЛЕНИЯ

Групповые представления — это представления, наподобие запроса Ratingcount в предыдущем примере, который содержит предложение GROUP BY, или который основывается на других групповых представлениях. Групповые представления могут стать превосходным способом обрабатывать полученную информацию непрерывно. Предположим, что каждый день вы должны следить за порядком номеров заказчиков, номерами про- давцов принимающих порядки, номерами порядков, средним от порядков, и общей суммой приобретений в порядках.

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

ПРЕДСТАВЛЕНИЯ И ОБЪЕДИНЕНИЯ

Представления не требуют чтобы их вывод осуществлялся из одной базовой таблицы. Так как почти любой допустимый запрос SQL может быть использован в представлении, он может выводить информацию из любого числа базовых таблиц, или из других представлений. Мы можем, например, создать представление которое показывало бы, порядки продавца и заказчика по имени: Теперь вы можете выбрать (SELECT) все порядки заказчика или про- давца ( * ), или можете увидеть эту информацию для любого порядка. Например, чтобы увидеть все порядки продавца Rifkin, вы должны ввести следующий запрос ( вывод показан в 20.3 Рисунке ): Рисунок 20.3: Порядки Rifkin показанные в Nameorders Вы можете также объединять представления с другими таблицами, или базовыми таблицами или представлениями, поэтому вы можете увидеть все порядки Axelrodа и значения его комиссионных в каждом порядке: Вывод для этого запроса показывается в Рисунке 20.4.

В предикате, мы могли бы написать — " WHERE a.sname = ‘Axelrod’ AND b.sname = ‘Axelrod’ " , но предикат который мы использовали здесь более общеупотребительный. Кроме того поле snum — это первичный ключ таблицы Продавцов, и следовательно должен по определению быть уникальным. Рисунок 20. 4: Объединение основной таблицы с представлением Если бы там например было два Axelrodf, вариант с именем, будет объединять вместе их данные. Более предпочтительный вариант — использовать поле snum чтобы хранить его отдельно.

ПРЕДСТАВЛЕНИЯ И ПОДЗАПРОСЫ

ЧТО НЕ МОГУТ ДЕЛАТЬ ПРЕДСТАВЛЕНИЯ

УДАЛЕНИЕ ПРЕДСТАВЛЕНИЙ

РЕЗЮМЕ

РАБОТА С SQL

1. Создайте представление которое бы показывало всех заказчиков которые имеют самые высокие оценки.

2. Создайте представление которое бы показывало номер продавца в каждом городе.

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

Читайте также:  Сброс пароля через adb

4. Создайте представление которое бы показывало каждого продавца с многочисленными заказчиками.

CREATE VIEW Oracle

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

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

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

Синтаксис команды CREATE VIEW Oracle

Синтаксис команды Create View

Основные ключевые слова и параметры CREATE VIEW Oracle :
OR REPLACE, FORCE, NOFORCE, Sсhema, View, Alias, AS subquery, WITH CHECK OPTION, Constraint

OR REPLACE — пересоздает представление, если оно уже существует. Можно использовать эту опцию для изменения определения представления без того, чтобы удалять его, создавать заново и вновь назначать все объектные привилегии, которые были назначены по данному представлению;

FORCE — создает представление независимо от того, существуют ли базовые таблицы этого представления, и от того, имеет ли владелец схемы, содержащей представление, привилегии по этим таблицам. Необходимо чтобы оба названных условия были удовлетворены, прежде чем по данному представлению можно будет выдавать любые предложения SELECT, INSERT, UPDATE или DELETE. По умолчанию применяется параметр NOFORCE;

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

Sсhema — схема, в которой создается представление. Если СХЕМА опущена, то ORACLE создает представление в схеме пользователя;

View — ключевое слово view это имя создаваемого представления;

Alias — специфицирует имена для выражений, выбираемых запросом представления. Число алиасов должно совпадать с количеством выражений, выбираемых подзапросом. Алиасы должны удовлетворять правилам именования объектов схем. Алиасы должны быть уникальны внутри представления. Если алиасы опускаются, то ORACLE определяет их по именам или алиасам столбцов в запросе представления. Поэтому использовать алиасы нужно, если запрос представления содержит, помимо имен столбцов, выражения;

AS subquery — идентифицирует столбцы и строки таблиц, на которых базируется представление. Запрос представления может быть любым предложением SELECT, не содержащим фраз ORDER BY или FOR UPDATE. Его список выборки может содержать до 254 выражений;

WITH CHECK OPTION — указывает, что вставки и обновления, которые будут осуществляться через этот запрос, должны давать в результате только такие строки, которые могут быть выбраны запросом этого же представления. Опция CHECK OPTION не может гарантировать этого, если существует подзапрос в запросе этого представления или любого представления, на котором базируется данное представление. Другими словами, при указании параметра WITH CHECK OPTION пользователь не может вводить, удалять и обновлять информацию таблицы, из которой он не имеет возможности считать информацию через простое представление (создаваемое из данных одной таблицы). Обновляемое представление, использующее несколько связанных таблиц, нельзя создавать с данным параметром;

Constraint — имя, которое присваивается ограничению CHECK OPTION. Если этот идентификатор опущен, то ORACLE автоматически назначает этому ограничению имя следующего вида:

SYS_Cn , где n — целое, которое делает имя ограничения уникальным внутри базы данных.

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

Если команды обновления DML (INSERT, UPDATE, DELETE) можно применить к представлению, то говорят, что представление является обновляемым (updatable); в противном случае оно является только читаемым (read-only). Представление является обновляемым, если при его создании учитывались следующие критерии:

  • представление должно включать первичный ключ таблицы
  • не должно содержать полей, полученных в результате применения функций агрегирования
  • не должно содержать DISTINCT, GROUP BY, HAVING в своем определении
  • может быть определено на другом представлении, но это представление должно быть обновляемым
  • не может содержать константы, строки или выражения (например, comm*100) в списке выбираемых выходных полей

SQL CREATE VIEW. Пример 1.
CREATE VIEW Oracle.
Простое представление, которое создается из данных одной таблицы:

CREATE VIEW London_view AS SELECT * FROM Salespeople WHERE city = ‘London’;

SQL CREATE VIEW. Пример 2.
CREATE VIEW Oracle.
При создании представления можно можно задать новые имена полей:

CREATE VIEW Rating_view(rating,number) AS SELECT rating, COUNT(*) FROM Customers GROUP BY rating;

SQL CREATE VIEW. Пример 3.
CREATE VIEW Oracle.
Представления могут получать информацию из любого количества базовых таблиц:

CREATE VIEW Nameorders AS SELECT onum, amt,a.snum, sname, cname FROM Orders a, Customers b, Salespeople C WHERE a.cnum = b.cnum AND a.snum = c.snum;

SQL CREATE VIEW. Пример 4.
CREATE VIEW Oracle.
При создании представлений можно использовать подзапросы, включая и связанные подзапросы:

CREATE VIEW Sales_view AS SELECT b.odate, a.snum, a.sname, FROM Salespeople a, Orders b WHERE a.snum = b.snum AND b.amt = (SELECT MAX(amt) FROMOrders c WHERE c.odate = b.odate);

SQL CREATE VIEW. Пример 5.
CREATE VIEW Oracle.

CREATE VIEW empl_v04 AS SELECT e.e >