Как создать представление в sql
Перейти к содержимому

Как создать представление в sql

  • автор:

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

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

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

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

Инструкция 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. Кроме этого, по временным таблицам нельзя выполнять запросы.

Представления можно использовать для разных целей:

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

В примере ниже показано создание представления:

USE SampleDb; GO CREATE VIEW view_Consultant AS SELECT EmpId, ProjectNumber, EnterDate FROM Works_on WHERE Job = 'Консультант';

Запрос в этом примере выбирает из таблицы 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;

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

USE SampleDb; GO CREATE VIEW view_project_p2 AS SELECT EmpId FROM view_Consultant WHERE ProjectNumber ='p2';

Представление 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 показано в примере ниже:

USE SampleDb; GO ALTER VIEW view_WithoutBudget AS SELECT Number, ProjectName FROM Project WHERE Number >= 'p3';

В этом примере инструкция 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. С другой стороны, представления удаленной таблицы можно снова использовать на новой таблице, имеющей такую же логическую структуру, как и удаленная.

Редактирование информации о представлениях

Наиболее важным представлением каталога применительно к представлениям является sys.objects. Как уже упоминалось, это представление каталога содержит информацию касательно всех объектов в текущей базе данных. Все строки этого представления со значением V в столбце type содержат информацию о представлениях.

А представление каталога sys.views содержит дополнительную информацию о существующих представлениях. Наиболее важным столбцом этого представления является столбец with_check_option, который информирует, указано или нет предложение WITH CHECK OPTION. Запрос для определенного представления можно отобразить посредством системной процедуры sp_helptext.

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

Представления можно создавать в ядре СУБД SQL Server с помощью SQL Server Management Studio или Transact-SQL. Представление можно использовать в следующих целях.

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

ограничения

Представление может быть создано только в текущей базе данных.

Представление может включать не более 1 024 столбцов.

Разрешения

Для выполнения этой инструкции требуется разрешение CREATE VIEW в отношении базы данных и разрешение ALTER в отношении схемы, в которой создается представление.

Использование среды SQL Server Management Studio

  1. В обозревателе объектовразверните базу данных, в которой необходимо создать новое представление.
  2. Щелкните правой кнопкой мыши папку «Представления» , а затем выберите «Создать представление. «.
  3. В диалоговом окне Добавить таблицу выберите один или несколько элементов, которые необходимо включить в новое представление, на одной из следующих вкладок: «Таблицы», «Представления», «Функции» и «Синонимы».
  4. Нажмите кнопку «Добавить«, а затем нажмите кнопку «Закрыть«.
  5. На Панели диаграммвыберите столбцы или другие элементы для включения в новое представление.
  6. На Панели критериеввыберите дополнительные условия сортировки или фильтрации для столбцов.
  7. В меню «Файл» выберите «Сохранить имя представления».
  8. В диалоговом окне «Выбор имени» введите имя нового представления и нажмите кнопку «ОК«. Дополнительные сведения о конструкторе запросов и представлений см. в разделе «Инструменты конструктора запросов и представлений» (визуальные инструменты для баз данных).

Использование Transact-SQL

  1. В обозревателе объектов подключитесь к экземпляру ядра СУБД.
  2. На стандартной панели выберите пункт Создать запрос.
  3. Скопируйте приведенный ниже пример в окно запроса и нажмите кнопку Выполнить.
USE AdventureWorks2022; GO CREATE VIEW HumanResources.EmployeeHireDate AS SELECT p.FirstName, p.LastName, e.HireDate FROM HumanResources.Employee AS e INNER JOIN Person.Person AS p ON e.BusinessEntityID = p.BusinessEntityID; GO -- Query the view SELECT FirstName, LastName, HireDate FROM HumanResources.EmployeeHireDate ORDER BY LastName; GO

Далее

Пример: Создание представлений базы данных в SQL Server с помощью SQL

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

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

Определение таблицы для сотрудников

CREATE TABLE employees ( emp_id integer not null, emp_name nvarchar(32), department smallint not null, hire_date datetime2 not null );

Определение таблицы для регионов

CREATE TABLE regions( objectid integer not null, emp_id integer not null, reg_id integer not null, rname varchar(32), region geometry );

Предоставление прав доступа к таблицам

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

В данном примере таблицы, на базе которых построены представления (employees и regions), принадлежат пользователю gdb. Представление создается пользователем rocket.

GRANT SELECT ON gdb.employees TO rocket; GRANT SELECT ON gdb.regions TO rocket;

Создание представления для ограничения доступа

В этом примере пользователь rocket создает представление (view_dept_201) таблицы employees, чтобы ограничить доступ только к тем строкам, где подразделение соответствует значению 201:

CREATE VIEW view_dept_201 AS SELECT emp_id, name, hire_date FROM gdb.employees WHERE department = 201;

Создание представления для соединения двух таблиц

В этом примере представление emp_regions_view соединяет пространственную таблицу (класс объектов) с непространственной таблицей по столбцу emp_id. Представление включает в себя ObjectID, пространственный столбец (region) и имя региона (rname) из класса объектов regions, а также имя и ID сотрудника из таблицы employees.

CREATE VIEW emp_regions_view AS SELECT (e.emp_name,e.emp_id,r.objectid,r.rname,r.region) FROM employees e, regions r WHERE e.emp_id = r.emp_id;

Предоставление прав доступа к представлениям

Права доступа к представлениям можно предоставлять определенным пользователям, не передавая им права доступа к базовым таблицам (employees и regions). В данном примере пользователю dispatch_mgr предоставлено право доступа к обоим представлениям:

GRANT SELECT ON rocket.view_dept_201 TO dispatch_mgr; GRANT SELECT ON rocket.emp_regions_view TO dispatch_mgr;

Теперь пользователь dispatch_mgr может получить доступ к view_dept_201, чтобы просмотреть записи всех сотрудников отдела 201 и получить доступ к emp_region_view из подключения к базе данных в ArcMap или ArcGIS Pro , чтобы просмотреть все регионы. Когда dispatch_mgr запрашивает в представлении регион, ArcGIS возвращает название региона, а также имя и ID всех работников в этом регионе.

Как создать представление в sql

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

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

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

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

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

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

В качестве простого примера предположим, что вы хотите частично скрыть адреса электронной почты в таблице пользователей ( Users ).

Это может быть полезно, например, если политика вашей компании не позволяет всем использовать конфиденциальную информацию об пользователях. Поэтому вместо того, чтобы разрешить прямой доступ к таблице пользователей ( Users ), вы определяете представление с именем ViewUsers и требуете, чтобы все использовали его для доступа к данным о пользователях.

Вот пример определения этого представления:

MySQL
CREATE VIEW ViewUsers AS SELECT id, name, CONCAT(SUBSTR(email, 1, 2), '****', SUBSTR(email, -4)) AS email FROM Users; 


Представление в SQL-запросе выглядит и используется как обычная таблица:


MySQL
SELECT * FROM ViewUsers; 





id
name
email






1
Bruce Willis
ba****.com




2
George Clooney
te****.com




3
Kevin Costner
me****.com




4
Donald Sutherland
ra****.net




5
Jennifer Lopez
ba****.com




6
Ray Liotta
jd****.net




7
Samuel L. Jackson
mo****.com




8
Nikole Kidman
ok****.com




9
Alan Rickman
ka****.net




10
Kurt Russell
ga****.com




11
Harrison Ford
ko****.com




12
Russell Crowe
gl****.net




13
Steve Martin
ne****.com




14
Michael Caine
dm****.com




15
Angelina Jolie
ch****.net




16
Mel Gibson
ro****o.ca




17
Michael Douglas
ti****.com




18
John Travolta
wa****.com




19
Sylvester Stallone
ko****.com




20
Tommy Lee Jones
sz****.com




21
Catherine Zeta-Jones
fl****.com




22
Antonio Banderas
sa****.net




23
Kim Basinger
ja****.net




24
Sam Neill
cl****.com




25
Hideo Kojima
ad****.org




26
ClINT Eastwood
fr****.net




27
Brad Pitt
ke****.net




28
Johnny Depp
cg****o.ca




29
Pierce Brosnan
tr****.com




30
Sean Connery
js****.com




31
Bruce Willis
ke****.com




32
Mullah Omar
jg****.com




33
Vasanta Roberta
ro****.com






Если вы хотите узнать, какие столбцы доступны в представлении, вы можете использовать оператор DESCRIBE :


MySQL
DESCRIBE ViewUsers; 





Field
Type
Null
Key
Default
Extra






id
int
NO


name
varchar(32)
NO


email
varchar(38)
YES





MySQL
CREATE [OR REPLACE] VIEW имя_представления [(имена_полей_представления)] AS select_выражение 


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


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


Создание представлений, которые инкапсулируют сложные запросы, может помочь оптимизировать выполнение этих запросов. Это может привести к более быстрому выполнению запросов и улучшению общей производительности базы данных.


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


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


Давайте проверим как вы усвоили тему: выберите правильное утверждение на вопрос «Что такое представление в базе данных?»





Похожие публикации:
    

  1. Диспетчер восстановления чертежей autocad как открыть
    2. 

  2. Как перевести в чб в иллюстраторе
    3. 

  3. Как убрать безопасность windows 10 из панели задач windows
    4. 

  4. Как узнать ip адрес wifi камеры
    5. 









		

		
Добавить комментарий 
Ваш адрес email не будет опубликован. Обязательные поля помечены *