Представлениями можно управлять в редакторе запросов, выполняя сценарии SQL, которые используют команды языка DDL: CREATE, ALTER и DROP. Основной синтаксис создания представления следующий:
CREATE VIEWимя_представления AS инструкция_SELECT
Например, чтобы создать представление v_Customer, возвращающее список клиентов с указанием города проживания, программным путем, в окне запросов должна быть выполнена следующая команда.
CREATE VIEW .
SELECT dbo.Customer.IdCust, dbo.Customer.FName, dbo.Customer.LName, dbo.City.CityName
FROM dbo.Customer INNER JOIN
dbo.City ON dbo.Customer.IdCity = dbo.City.IdCity
Попытка создать представление, которое уже существует, вызовет ошибку. Когда представление создано, инструкцию SELECT можно с легкостью отредактировать с помощью команды ALTER:
ALTER имя_представления AS измененная_инструкция_SELECT
Если изменение представления предполагает и изменение прав доступа на него, предпочтительнее удалить его и создать заново, поскольку удаление представления также приводит и к удалению разрешений доступа, установленных ранее.
Чтобы удалить представление из базы данных, используйте команду DROP:
DROP VIEW имя_представления
Представления служат источником данных для других запросов и не поддерживают сортировку внутри себя. Например, следующий код извлекает данные из представления v_Customerи упорядочивает их по полямLNameиFName. Предложение ORDER BY не является частью представления v_Customer, а применяется к нему с помощью вызова инструкции SQL:
SELECT IdCust, FName, LName, CityName
FROM dbo.v_Customer
ORDER BY LName, FName
Представление не может быть выполнено само по себе. Инструкция SELECT, на основе которой создано представление, может быть выполнена, однако в этой форме, с технической стороны, инструкция SQL не является представлением. Представление может быть полезно только как источник данных в запросе.
Именно поэтому контекстное меню «Открыть представление» утилиты ManagementStudioавтоматически генерирует простой запрос, извлекая из представления все столбцы. Представление отображает только результаты. Однако включение других панелей конструктора запросов позволяет увидеть и сам запрос, извлеченный из представления.
Панель SQL отобразит представление в предложении FROM инструкции SELECT. Именно в такой форме на представление ссылаются пользователи:
SELECT * FROM v_Customer
Задание для самостоятельной работы: Создайте представление возвращающее список заказов с указанием имени клиента и количества товаров в каждом заказе. Таким образом, результат должен включать следующие атрибуты:IdOrd,OrdDate,IdCust,FName,LName, Количество видов товаров в заказе.
Правила формирования идентификаторов
Все объекты в SQLServerимеют имена (идентификаторы). Примерами объектов являются таблицы, представления, хранимые процедуры и т.д. Идентификаторы могут включать до 128 символов, в частности, буквы, символы _ @ $ # и цифры. Первый символ всегда должен быть буквенным. Для переменных и временных таблиц используются специальные схемы именования. Имя объекта не может содержать пробелов и совпадать с зарезервированным ключевым словомSQLServer, независимо от используемого регистра символов. Путем заключения идентификаторов в квадратные скобки, в именах объектов можно использовать запрещенные символы.
Завершение инструкции
Стандарт ANSISQLтребует помещения в конце каждой инструкции точки с запятой. В то же время при программировании на языкеT-SQLточка с запятой не обязательна.
Комментарии
Язык T-SQL допускает использование комментариев двух стилей: ANCI и языка С. Первый из них начинается с двух дефисов и заканчивается в конце строки:
Это однострочный комментарий стиля ANSI
Также комментарии стиля ANSI могут вставляться в конце строки инструкции:
SELECT CityName – извлекаемые столбцы
FROM City – исходная таблица
WHERE IdCity = 1; -- ограничение на строки
Редактор SQL может применять и удалять комментарии во всех выделенных строках. Для этого нужно выбрать соответствующие команды в меню Правка или на панели инструментов.
Многие начинающие администраторы и программисты баз данных, да и просто системные администраторы, которые обслуживают некую базу данных, не знают что такое представление или VIEWS , и зачем вообще они нужны. Сейчас мы попробуем разобраться, что же это такое.
Начнем с небольшой теории.
VIEWS – представление, или например в PostgreSQL они называются «Видами » (т.е. Вид), русские админы часто называют их вьюхами, т.е. одно представление это вьюшка. Она представляет собой хранимый запрос к базе данных, также ее можно назвать виртуальная таблица, но в этой таблице данные не хранятся, а хранится только сам запрос. Но, тем не менее, к вьюшке можно обращаться как к обычной таблице и извлекать данные из нее.
Мы с Вами говорим о базах данных, в которых используется язык SQL, исходя из этого, можно сделать вывод, что VIEWS можно создать на данном языке. Это очень распространенный объект в базе данных, поэтому во всех СУБД есть возможность создавать представления в графическом интерфейсе путем нажатия кнопки «Создать представление » или «Создать новый вид », а также, конечно же, с использованием инструкции «CREATE VIEW».
Но перед тем как учиться создавать вьюшки, давайте поговорим о том, зачем они нужны и какие преимущества они нам дадут.
Одним из главных достоинством представлений является то, что они сильно упрощают взаимодействие с данными в базе данных. Допустим, Вам необходимо каждый раз делать сложную по своей структуре выборку, а как Вы знаете, запрос на выборку может быть, ну просто очень сложный и этому нет предела. И если не было бы вьюшек, то Вам приходилось бы каждый раз запускать этот запрос, или даже его модифицировать, например, для вставки условий. А так как у нас есть такие объекты как представления, нам этого делать не придется. Мы просто на всего создадим одну вьюшку, и потом уже к ней будем обращаться с помощью уже простых запросов, которые также можно делать сложными, если это необходимо. Например, вьюшки также можно объединять с другими таблицами или другими представлениями.
К представлениям можно обращаться и из приложений, например, Вам нужно вывести какой-нибудь отчет, формирование которого требует каких-то расчетов, это легко можно реализовать путем написания необходимого запроса (в котором и будут рассчитываться данные, например из разных таблиц ) и вставке этого запроса во вьюшку. А потом уже обращаться к этой вьюшке, например с помощью такого простого запроса как:
SELECT * FROM TableName
Теперь давайте поговорим о том, как создавать эти самые вьюшки. Во-первых, сразу скажу, что для этого необходимы знания SQL (для построения сложных запросов ). Во-вторых, Вы за ранее должны определиться, что Вам необходимо вывести в результате того или иного запроса. Рассматривать процесс создания представления путем нажатия кнопок мы не будем, так это достаточно просто. Мы рассмотрим создание VIEWS с использованием языка SQL (хотя и это тоже просто ).
Например, в PostgreSQL запрос создания представления будет выглядеть так:
CREATE VIEW MyView AS SELECT id, name, org FROM work.TableName
Здесь мы использовали простой запрос на выборку, Вы в свою очередь можете писать любой запрос, даже с объединением нескольких таблиц и условий к ним.
Полный синтаксис команды CREATE VIEW (в PostgreSQL) выглядит следующим образом:
CREATE VIEW view_name [(column_list)] AS select_statement CHECK OPTION]
После того, как Вы создали представление, Вы можете к нему обращаться. А данные, которые будет выводить вьюшка, будут изменяться в зависимости от изменений данных в исходных таблицах, так как данные во вьюшке формируются при обращении к этому представлению. Исходя из этого, можно сделать вывод, что данные, которые выводит вьюшка, будут всегда актуальные.
У меня все, надеюсь, теперь у Вас есть представление о том, что такое VIEWS, пока!
Представление – это виртуальная таблица. В действительности представление – всего лишь результат выполнения оператора SELECT, который хранится в структуре памяти, напоминающей SQL таблицу, например, Oracle . Для тех, кто работает с представлением, манипулирование его данными ничем не отличается от манипулирования данными таблицы. В некоторых случаях пользователь может вводить данные в представление, как если бы оно было таблицей. Работая с представлением нужно помнить, что:
Представление создается с помощью команды . После создания представления становятся частью схемы создавшего их пользователя. Переназначить их другому пользователю можно тогда, когда у него имеется системная привилегия CREATE ANY VIEW .
Синтаксис команды 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). Представление является обновляемым, если при его создании учитывались следующие критерии:
Пример 1.
CREATE VIEW Oracle
.
Простое представление, которое создается из данных одной таблицы:
London_view AS SELECT * FROM Salespeople WHERE city = ‘London’;
Пример 2.
CREATE VIEW Oracle
.
При создании представления можно можно задать новые имена полей:
Rating_view(rating,number) AS SELECT rating, COUNT (*) FROM Customers GROUP BY rating;
Пример 3.
CREATE VIEW Oracle
.
Представления могут получать информацию из любого количества базовых таблиц:
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;
Пример 4.
CREATE VIEW Oracle
.
При создании представлений можно использовать подзапросы, включая и связанные подзапросы:
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) FROM Orders c WHERE c.odate = b.odate);
Пример 5.
CREATE VIEW Oracle
.
Empl_v04 AS SELECT e.eid, e.sname, e.fname, e.otch, p.pname, d.dname FROM posts p, departments d, employees e WHERE e.did = d.did AND e.pid = p.pid;
В предыдущих статьях инструкции DDL и DML рассматривались применительно к базовым таблицам. Данные базовой таблицы хранятся на диске. В отличие от базовых таблиц, представления по умолчанию не существуют физически, т.е. их содержимое не сохраняется на диске. Это не относится к так называемым индексированным представлениям, которые рассматриваются позже. Представления (views) - это объекты базы данных, которые всегда создаются на основе одной или более базовых таблиц (или других представлений), используя информацию метаданных. Эта информация (включая имя представления и способ получения строк из базовых таблиц) - все, что сохраняется физически для представления. По этой причине представления также называются виртуальными таблицами.
Представление создается посредством инструкции CREATE VIEW , синтаксис которой выглядит следующим образом:
CREATE VIEW view_name [(column_list)] AS select_statement Соглашения по синтаксису
Инструкция CREATE VIEW должна быть единственной инструкцией пакета. (Это означает, что эту инструкцию следует отделять от других инструкций группы посредством инструкции GO.)
Параметр view_name задает имя определяемого представления, а в параметре column_list указывается список имен, которые будут использоваться в качестве имен столбцов представления. Если этот необязательный параметр опущен, то используются имена столбцов таблиц, по которым создается представление. Параметр select_statement задает инструкция SELECT, которая извлекает строки и столбцы из таблиц (или других представлений). Параметр WITH ENCRYPTION задает шифрование инструкции SELECT, повышая таким образом уровень безопасности системы баз данных.
Предложение SCHEMABINDING привязывает представление к схеме таблицы, по которой оно создается. Когда это предложение указывается, имена объектов баз данных в инструкции SELECT должны состоять из двух частей, т.е. в виде schema.db_object, где schema - владелец, а db_object может быть таблицей, представлением или определяемой пользователем функцией.
Любая попытка модифицировать структуру представлений или таблиц, на которые ссылается созданное таким образом представление, будет неудачной. Чтобы такие таблицы или представления можно было модифицировать (инструкцией ALTER) или удалять (инструкцией DROP), нужно удалить это представление или убрать из него предложение SCHEMABINDING.
Когда при создании представления указывается параметр VIEW_METADATA , все его столбцы можно обновлять (за исключением столбцов с типом данных timestamp), если представление имеет триггеры INSERT или UPDATE INSTEAD OF.
Инструкция SELECT в представлении не может содержать предложение ORDER BY или параметр INTO. Кроме этого, по временным таблицам нельзя выполнять запросы.
Представления можно использовать для разных целей:
Для ограничения использования определенных столбцов и/или строк таблиц. Таким образом, представления можно использовать для управления доступом к определенной части одной или нескольких таблиц.
Для скрытия подробностей сложных запросов. Если для приложения базы данных требуются запросы со сложными операциями соединения, создание соответствующих представлений может упростить такие запросы.
Для ограничения вставляемых или обновляемых значений некоторым диапазоном.
В примере ниже показано создание представления:
Запрос в этом примере выбирает из таблицы Works_on строки, удовлетворяющие условию Job="Консультант". Представление view_Consultant определяется строками и столбцами, возвращаемыми этим запросом. На рисунке ниже отображена таблица Works_on, в которой строки, выбранные в представлении view_Consultant, выделены красным цветом:
Запрос в этом примере задает выборку строк, т.е. он создает горизонтальное подмножество базовой таблицы Works_on. Возможно также создание представления с ограничениями на включаемые в него столбцы и строки. Создание такого представления показано в примере ниже:
USE SampleDb; GO CREATE VIEW view_WithoutBudget AS SELECT Number, ProjectName FROM Project;
Запрос в этом примере выбирает для включения в представление view_WithoutBudget все столбцы таблицы Project, за исключением столбца Budget.
Как уже упоминалось ранее, в общем формате инструкции CREATE VIEW не обязательно указывать имена столбцов представления. Однако, с другой стороны, в приведенных далее двух случаях обязательно требуется явно указывать имена столбцов:
если столбец представления создается из выражения или агрегатной функции;
если два или больше столбцов представления имеют одинаковое имя в базовой таблице.
В примере ниже показано создание представления, для которого явно указываются имена столбцов:
USE SampleDb; GO CREATE VIEW view_Count(projectNumber, countProject) AS SELECT ProjectNumber, COUNT(*) FROM Works_on GROUP BY ProjectNumber;
Здесь имена столбцов представления view_Count должны быть указаны явно по той причине, что инструкция SELECT содержит агрегатную функцию count(*), которая требует, чтобы все столбцы представления были именованы.
Не требуется явно указывать список столбцов в инструкции CREATE VIEW, если применить заголовки столбцов, как это показано в примере ниже:
USE SampleDb; GO CREATE VIEW view_Count1 AS SELECT ProjectNumber, COUNT(*) countProject FROM Works_on GROUP BY ProjectNumber;
Представление можно создать из другого представления, как показано в примере:
Представление view_project_p2 в примере ниже создается из представления view_Consultant. Все запросы, использующие представление view_project_p2, преобразовываются в эквивалентные запросы к базовой таблице Works_on.
Представления можно также создавать посредством среды Management Studio. Для этого выберите в обозревателе объектов базу данных, в которой требуется создать представление, щелкните в ней правой кнопкой мыши узел Views и в открывшемся контекстном меню выберите пункт New View. Откроется редактор представлений, в котором можно выполнять следующие действия:
выбрать базовые таблицы и строки в этих таблицах для создания представления;
присвоить представлению имя и определить условия в предложении WHERE соответствующего запроса.
Для изменения определения представления в языке Transact-SQL применяется инструкция ALTER VIEW . Синтаксис этой инструкции аналогичен синтаксису инструкции CREATE VIEW, применяющейся для создания представления.
Использование инструкции ALTER VIEW позволяет избежать переназначения существующих разрешений для представления. Кроме этого, изменение представления посредством этой инструкции не влияет на объекты базы данных, зависящие от этого представления. Если же модифицировать представление, сначала удалив его (инструкция DROP VIEW), а затем создав новое представление с требуемыми свойствами (инструкция CREATE VIEW), то все объекты базы данных, которые ссылаются на это представление, не будут работать должным образом, по крайней мере, в период времени между удалением представления и его воссоздания.
Использование инструкции ALTER VIEW показано в примере ниже:
В этом примере инструкция ALTER VIEW расширяет инструкцию SELECT в представлении view_WithoutBudget новым условием в предложении WHERE.
Инструкция DROP VIEW удаляет из системных таблиц определение указанного в ней представления. Применение этой инструкции показано в примере ниже:
USE SampleDb; GO DROP VIEW view_Count;
При удалении представления инструкцией DROP VIEW все другие представления, основанные на удаленном, также удаляются, как показано в примере ниже:
USE SampleDb; GO DROP VIEW view_Consultant;
Здесь инструкция DROP VIEW явно удаляет представление view_Consultant, при этом неявно удаляя представление view_project_p2, основанное на представлении view_Consultant. Теперь попытка выполнить запрос по представлению view_project_p2 возвратит сообщение об ошибке.
При удалении базовой таблицы представления, основанные на ней другие представления, не удаляются автоматически. Это означает, что все представления для удаленной таблицы нужно удалять явно, используя инструкцию DROP VIEW. С другой стороны, представления удаленной таблицы можно снова использовать на новой таблице, имеющей такую же логическую структуру, как и удаленная.
Последнее обновление: 14.08.2017
Представления или Views представляют виртуальные таблицы. Но в отличии от обычных стандартных таблиц в базе данных представления содержат запросы, которые динамически извлекают используемые данные.
Представления дают нам ряд преимуществ. Они упрощают комплексные SQL-операции. Они защищают данные, так как представления могут дать доступ к части таблицы, а не ко всей таблице. Представления также позволяют возвращать отформатированные значения из таблиц в нужной и удобной форме.
Для создания представления используется команда CREATE VIEW , которая имеет следующую форму:
CREATE VIEW название_представления [(столбец_1, столбец_2, ....)] AS выражение_SELECT
Например, пусть у нас есть три связанных таблицы:
CREATE TABLE Products (Id INT IDENTITY PRIMARY KEY, ProductName NVARCHAR(30) NOT NULL, Manufacturer NVARCHAR(20) NOT NULL, ProductCount INT DEFAULT 0, Price MONEY NOT NULL); CREATE TABLE Customers (Id INT IDENTITY PRIMARY KEY, FirstName NVARCHAR(30) NOT NULL); CREATE TABLE Orders (Id INT IDENTITY PRIMARY KEY, ProductId INT NOT NULL REFERENCES Products(Id) ON DELETE CASCADE, CustomerId INT NOT NULL REFERENCES Customers(Id) ON DELETE CASCADE, CreatedAt DATE NOT NULL, ProductCount INT DEFAULT 1, Price MONEY NOT NULL);
Теперь добавим в базу данных, в которой содержатся данные таблицы, следующее представление:
CREATE VIEW OrdersProductsCustomers AS SELECT Orders.CreatedAt AS OrderDate, Customers.FirstName AS Customer, Products.ProductName As Product FROM Orders INNER JOIN Products ON Orders.ProductId = Products.Id INNER JOIN Customers ON Orders.CustomerId = Customers.Id
То есть данное представление фактически будет возвращать сводные данные из трех таблиц. И после его создания мы сможем его увидеть в узле Views у выбранной базы данных в SQL Server Management Studio:
Теперь используем созданное выше представление для получения данных:
SELECT * FROM OrdersProductsCustomers
При создании представлений следует учитывать, что представления, как и таблицы, должны иметь уникальные имена в рамках той же базы данных.
Представления могут иметь не более 1024 столбцов и могут обращать не более чем к 256 таблицам.
Также можно создавать представления на основе других представлений. Такие представления еще называют вложенными (nested views). Однако уровень вложенности не может быть больще 32-х.
Команда SELECT , используемая в представлении, не может включать выражения INTO или ORDER BY (за исключением тех случаев, когда также применяется выражение TOP или OFFSET ). Если же необходима сортировка данных в представлении, то выражение ORDER BY применяется в команде SELECT, которая извлекает данные из представления.
Также при создании представления можно определить набор его столбцов:
CREATE VIEW OrdersProductsCustomers2 (OrderDate, Customer,Product) AS SELECT Orders.CreatedAt, Customers.FirstName, Products.ProductName FROM Orders INNER JOIN Products ON Orders.ProductId = Products.Id INNER JOIN Customers ON Orders.CustomerId = Customers.Id
Для изменения представления используется команда ALTER VIEW . Эта команда имеет практически тот же самый синтаксис, то и CREATE VIEW :
ALTER VIEW название_представления [(столбец_1, столбец_2, ....)] AS выражение_SELECT
Например, изменим выше созданное представление OrdersProductsCustomers:
ALTER VIEW OrdersProductsCustomers AS SELECT Orders.CreatedAt AS OrderDate, Customers.FirstName AS Customer, Products.ProductName AS Product, Products.Manufacturer AS Manufacturer FROM Orders INNER JOIN Products ON Orders.ProductId = Products.Id INNER JOIN Customers ON Orders.CustomerId = Customers.Id
Для удаления представления вызывается команда DROP VIEW :
DROP VIEW OrdersProductsCustomers
Также стоит отметить, что при удалении таблиц также следует удалить и представления, которые используют эти таблицы.
