Меню

SQL Использование представлений

Представления в SQL

Здравствуйте, в этой статье будут разобраны представления или view в SQL. На простых примерах объясним как создавать представления и как к ним обращаться. Также затронем понятие модифицируемого представления в SQL.

Общее понятие

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

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

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

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

Создание представления осуществляется следующей командой:

Где name_view — имя, которое задает пользователь при создании. После ключевого слова as идет код запроса SELECT, данные которого и поместятся в представление. Чтобы легче понять разберем простой пример, иллюстрация которого была представлена выше.

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

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

Обращение к представлениям в SQL

Чтобы посмотреть данные из представления следует воспользоваться теми командами, которые были изучены в предыдущих уроках — например по оператору SELECT в SQL.

Помимо простого SELECT, в SQL к представлениям можно обращаться с различными условиями WHERE, как к обычным таблицам. Также можно использовать данные из этого представления, чтобы сформировать новое представление — хотя это и редко используется, тем не менее, в SQL есть такая возможность.

Примеры представлений

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

Представление с вложенным запросом

Рассмотрим такую задачу:

Создайте представление Cust_Rating_Max, которое бы показывало всех покупателей и их город, которые имеют самые высокие рейтинги.

Хорошей практикой является решение задачи запроса SELECT, а затем уже создание самого представления. То есть сначала лучше написать запрос, который вернет на экран то, что нужно. Затем создать представление. Вот итоговый код решения этой задачи:

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

Представление с группировкой

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

Нужно сделать группировку по имени продавца и следующий код нам в этом поможет:

По сути для каждого продавца рассчитывается среднее значение сумм его продаж и заносится в представление.

Представление с сортировкой

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

Это выделено в отдельный пункт, так как не все СУБД SQL поддерживают представления с сортировкой, но мы рассмотрим такую задачу:

Создайте представление Orders_All, которое бы показывало данные о заказах: сумму, дату, фамилию покупателя и продавца. Отсортировать по сумме заказа.

В этом запросе по сути создается представление, которое дублирует таблицу orders, и заменяет в ней идентификаторы на фамилии. Также происходит сортировка по сумме заказа.

На этом с практикой мы закончим и еще немного поговорим о теории представлений.

Понятие модифицируемого представления в SQL

Итак, в SQL есть такое понятие как модифицируемого представление — это означает, что при изменении данных в самом представлении, эти данные изменятся и в таблицах, которые эти данные хранят. То есть при использовании оператора UPDATE/INSERT/DELETE к представлению, данные обновятся и в таблицах.

Очевидно, что не все представления являются такими и на самом деле большая часть является немодифицируемыми. По причине неоднозначности: например в представлении хранятся данные из двух таблиц. Пользователь удаляет одну строку в представлении, и тут возникает вопрос — удалять ли исходные данные в таблицах, а если и удалять, то сразу из двух или только одной… В этом и состоит неоднозначность.

Поэтому выделили специальные критерии по которым можно определенно сказать — является ли представление SQL модифицируемым. Эти критерии вы можете найти по этой ссылке — тут статья на очень полезном ресурсе по SQL языку.

Заключение

На этом все для представлений. В этой статье было много теории, но без этого сложно понять как работают представления в SQL. Если у вас остались вопросы — задавайте их в комментариях и по возможности делитесь данными уроками в социальных сетях.

Источник

SQL — Использование представлений

Дата публикации: 2017-12-11

SQL — Использование представлений

От автора: представление — это не что иное, как оператор SQL, который хранится в базе данных с соответствующим именем. На самом деле представление является определенным структурированным набором данных таблицы в виде предопределенного SQL-запроса.

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

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

Структурировать данные таким образом, который будет наиболее естественным или интуитивным для пользователей или классов пользователей.

Ограничивать доступ к данным таким образом, чтобы пользователь мог находить и изменять (в некоторых случаях) только те данные, которые ему нужны, и не более того.

Бесплатный курс по PHP программированию

Освойте курс и узнайте, как создать динамичный сайт на PHP и MySQL с полного нуля, используя модель MVC

В курсе 39 уроков | 15 часов видео | исходники для каждого урока

Обобщать данные из разных таблиц, чтобы использовать их для различных отчетов.

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

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

Пользователь должен иметь соответствующие системные привилегии в зависимости от конкретной реализации.
Основной синтаксис CREATE VIEW следующий:

Источник



SQL Представление

SQL Инструкция CREATE VIEW

В SQL представление — это виртуальная таблица, основанная на результирующем наборе инструкции SQL.

Представление содержит строки и столбцы, как и настоящая таблица. Поля в представлении — это поля из одной или нескольких реальных таблиц в базе данных.

Читайте также:  История населения Земли 1951 2021

Вы можете добавить в представление инструкции SQL функций, WHERE и JOIN и представить данные так, как если бы они поступали из одной таблицы.

Синтаксис CREATE VIEW

Примечание: В представлении всегда отображаются актуальные данные! Компонент database engine воссоздает данные, используя инструкцию SQL представления, каждый раз, когда пользователь запрашивает представление.

Примеры SQL CREATE VIEW

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

Пример

Мы можем запросить представление выше следующим образом:

Пример

Следующий SQL создает представление, которое выбирает каждый продукт в таблице «Products» с ценой выше средней цены:

Пример

Мы можем запросить представление выше следующим образом:

Пример

SQL Обновление представления

Представление можно обновить с помощью команды CREATE или REPLACE VIEW.

SQL Синтаксис CREATE OR REPLACE VIEW

Следующий SQL добавляет столбец «City» в представлении «Brazil Customers»:

Пример

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

Представление удаляется с помощью команды DROP VIEW.

SQL Синтаксис DROP VIEW

Следующий SQL отбрасывает представление «Brazil Customers»:

Источник

Работа с данными через представления

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

Выборка информации из представления

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

Инструкция SELECT в примере выше трансформируется в следующую эквивалентную форму с использованием таблицы из представления view_d2:

Далее мы рассмотрим использование представлений с тремя другими инструкциями языка DML: INSERT, UPDATE и DELETE. Модифицирование данных посредством этих инструкций подобно выборке данных. Единственное отличие состоит в том, что для представления, используемого для вставки, модифицирования и удаления данных из таблицы, на основе которой оно создано, существуют некоторые ограничения.

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

Инструкцию INSERT можно применять с представлением, как если бы оно было обычной базовой таблицей. Вставляемые в представление строки в действительности вставляются в таблицу в основе представления.

В примере ниже создается представление view_dept, которое содержит первые два столбца таблицы Department. Последующая инструкция INSERT вставляет две строки в таблицу, связанную с представлением, используя значения d4 и ‘Разработка’. Столбцу Location, который не вошел в представление view_dept, присваивается значение NULL:

При использовании представления обычно возможно вставить строку, которая не удовлетворяет условиям в предложении WHERE запроса представления. Чтобы ограничить вставку только строками, которые удовлетворяют условиям запроса, применяется предложение WITH CHECK OPTION. При использовании этого предложения компонент Database Engine проверяет каждую вставляемую строку на удовлетворение условий предложения WHERE. Если это предложение отсутствует, такая проверка не выполняется, вследствие чего каждая вставляемая в представление строка также вставляется в таблицу в его основе. Это может вызвать путаницу, когда строка вставляется в представление, но впоследствии не возвращается из этого представления инструкцией SELECT, т.к. для нее принудительно выполняются условия предложения WHERE. Предложение WITH CHECK OPTION также применяется и с инструкцией UPDATE.

Читайте также:  Таблица радиосвязь и телевидение

В примерах ниже показана разница между применением и неприменением предложения WITH CHECK OPTION соответственно:

В этом примере система проверяет, соответствует ли вставляемое в столбец EnterDate значение True (истина) при вычислении условия в предложении WHERE инструкции SELECT. Если вставляемое значение не удовлетворяет этим условиям, строка не вставляется.

В результате выполнения этого запроса будут отображены следующие строки:

Ограничение на вставку данных в представление

Поскольку во втором примере предложение WITH CHECK OPTION не применяется, инструкция будет выполнена, и строка вставляется в основную таблицу Works_on. Обратите внимание на тот факт, что вставленная строка не будет возвращена инструкцией SELECT, поскольку ее нельзя извлечь посредством представления view_2007_nocheck.

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

предложение FROM в определении представления содержит более чем одну таблицу, и список столбцов содержит столбцы более чем из одной таблицы;

столбец в представлении создается из агрегатной функции;

инструкция SELECT в представлении содержит предложение GROUP BY или параметр DISTINCT;

столбец в представлении создается из константы или выражения.

В примере ниже показано представление, которое нельзя использовать для вставки строк в таблицу, на которой основано это представление:

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

Обновление данных с помощью представления

Инструкцию UPDATE можно применять с представлением, как будто бы это была базовая таблица. При модифицировании строк представления также модифицируется содержимое таблицы в его основе. Запрос в примере создает представление, посредством которого затем модифицируется таблица Works_on:

Операцию обновления представления view_p1 в примере выше можно рассматривать эквивалентной выполнению следующей инструкции UPDATE:

Логическое значение предложения WITH CHECK OPTION для инструкции UPDATE имеет такое же значение, как и для инструкции INSERT. Использование предложения WITH CHECK OPTION в инструкции UPDATE показано в примере ниже:

Здесь компонент Database Engine проверяет, будет ли измененное значение столбца Budget давать значение True в условии предложения WHERE инструкции SELECT. Попытка изменения значения завершается неудачей, поскольку условие не удовлетворяется, т.е. вставляемое значение 93000 не больше, чем значение 100000.

В примере ниже показано представление, которое нельзя использовать для изменения значений в таблице, на которой основано представление:

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

Представление в котором нельзя обновить данные

Представление view_Pound нельзя использовать с инструкцией UPDATE (или с инструкцией INSERT), поскольку значения столбца budgetPounds являются результатом вычисления арифметического выражения, а не первоначальными значениями столбца таблицы, на которой основано это представление.

Удаление данных с помощью представления

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

Запрос в примере создает представление, посредством которого затем удаляются строки из таблицы Works_on. В отличие от инструкций INSERT и UPDATE, инструкция DELETE допускает значения, получаемые из констант или выражений, в столбце представления, используемого для удаления строк из таблицы, на которой оно основано.

В примере ниже показано представление, посредством которого можно удалять строки, но не вставлять строки или изменять значения столбцов:

Инструкция DELETE в примере выше удаляет все строки таблицы Project, на которой основано представление view_budget.

Источник