Чем отличается table от view sql
Перейти к содержимому

Чем отличается table от view sql

  • автор:

Представления и табличные объекты

Представления или 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:

Views in SQL Server Management Studio

Теперь используем созданное выше представление для получения данных:

SELECT * FROM OrdersProductsCustomers

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

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

Представления могут иметь не более 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

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

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

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

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

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

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

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

Индексированные представления

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

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

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

Системные представления

Системные представления предоставляют доступ к метаданным каталога. Системные представления можно использовать для возврата сведений об экземпляре SQL Server или объектах, определенных в экземпляре. Например, можно запросить sys.databases представление каталога, чтобы получить сведения о пользовательских базах данных, доступных в экземпляре. Дополнительные сведения см. в разделе «Системные представления» (Transact-SQL).

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

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

Задачи, связанные с представлениями Тема
Описывает, как создать представление. Создание представлений
Описывает, как создать индексированное представление. Создание индексированных представлений
Описывает, как изменить определение представления. Изменение представлений
Описывает, как изменить данные через представление. Изменение данных через представление
Описывает, как удалить представление. Удаление представлений
Описывает, как получить сведения о представлении, например его определение. Получение сведений о представлении
Описывает, как переименовать представление. Переименование представлений

Связанный контент

  • Создание представлений для столбцов XML
  • CREATE VIEW (Transact-SQL)
  • GRANT, предоставление разрешений на объект (Transact-SQL)
  • Безопасность на уровне строк

Виртуальные таблицы в SQL

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

Представления бывают двух видов.

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

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

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

  • Гибкая настройка прав доступа

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

  • Предотвращение дублирования запросов

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

  • Сокрытие реализации

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

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

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

CREATE VIEW view_name AS SELECT columns FROM tables;

Для создания представления используется оператор CREATE VIEW. view_name – это название представления. После оператора AS следует сам запрос.

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

Синтаксис данной операции следующий:

CREATE OR REPLACE VIEW view_name AS SELECT columns FROM tables;

Обновление применимо, например, в том случае, если вам необходимо что-то изменить в запросе.

Если представления не существует, то оно будет создано.

Удаление представления

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

DROP VIEW view_name;

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

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

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

CREATE MATERIALIZED VIEW view_name AS SELECT columns FROM tables;

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

Для этого применяется следующая команда:

REFRESH MATERIALIZED VIEW view_name;

Материализованные представления не являются частью стандарта ANSI SQL и поддерживаются не всеми системами управления базами данных (СУБД). PostgreSQL и Oracle Database поддерживают данный тип представлений.

Практическая часть

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

Схема БД представлена ниже.

Весь код далее был написан в pgAdmin. Соответственно в качестве СУБД использовалась PostgreSQL.

Код создание таблиц приведён ниже.

— Таблица «Страховой агент» CREATE TABLE insurance_agents ( id SERIAL PRIMARY KEY, last_name CHARACTER VARYING(30) NOT NULL CHECK(last_name != »), first_name CHARACTER VARYING(30) NOT NULL CHECK(first_name != »), middle_name CHARACTER VARYING(30) NOT NULL CHECK(middle_name != »), phone CHARACTER VARYING(18) NOT NULL UNIQUE CHECK(phone != »), email CHARACTER VARYING(30) NOT NULL CHECK(email != ») UNIQUE, insurance_percent REAL CHECK((insurance_percent >= 0.2) AND (insurance_percent

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

Сделаем тестовый запрос к БД.

— Страховая премия по объектам страхования SELECT object_insurance AS «Объект страхования», Sum(insurance_premium) AS «Общая страховая премия», Round(Avg(insurance_premium), 2), Min(insurance_premium), Max(insurance_premium) FROM insuranсe_policies GROUP BY object_insurance ORDER BY Sum(insurance_premium) DESC

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

Table vs View в SQL: различия, плюсы, минусы, примеры

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

Краткий обзор отличий:

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

Скопировать код

-- Таблица: Простое хранилище данных о сотрудниках CREATE TABLE employees ( id INT, name VARCHAR(50), dept_id INT ); -- Представление: Отбор сотрудников из IT-отдела CREATE VIEW it_dept_names AS SELECT name FROM employees WHERE dept_id = 10;

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

Трактовка понятий Представлений и Таблиц

Безопасность и Абстракция с Представлениями

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

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

Производительность: Плюсы и Минусы

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

Гибкость и Логическая Структура

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

Визуализация

Сравните таблицу с кладовой на вашей кухне и представление с рецептом блюда:

Кухня (��): | Элемент | Аналог в SQL | | -------------- | --------------- | | Кладовая | Таблица (��) | | Рецепт | Представление (��)|

Таблица — это кладовая с полным ассортиментом ингредиентов. Представление — список продуктов для приготовления конкретного блюда.

��: [Картофель, Курица, Рис, Брокколи] ��: [Курица, Брокколи]

В таблице сохранены все продукты, тогда как представление указывает, какие из них требуются для приготовления блюда.

��: Полный набор ингредиентов под рукой! ��: Выбранное сочетание для "Курицы с Брокколи".

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

Представления: Когда и Как Использовать

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

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

Потенциальные Подводные Камни Представлений

Важно помнить об возможных сложностях:

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

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

Индексирование: Увеличение Производительности

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

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

Использование триггеров INSTEAD OF в представлениях позволяет выполнять операции вставки, обновления и удаления. Этот подход позволяет реализовывать сложные CRUD-операции, сохраняя бизнес-логику и целостность данных.

Представления в DSL (Domain-Specific Language)

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

Полезные материалы

  1. Schema Objects – Документация Oracle — Обстоятельное изложение информации о схеме объектов, включая представления, от Oracle.
  2. Microsoft SQL Server – Использование Представлений — Официальное руководство по использованию представлений в SQL Server от Microsoft.
  3. Обсуждение на Stack Overflow: Представления против Таблиц, Зачем используют — Выводы экспертов о использовании представлений вместо таблиц.
  4. Незаменимое Руководство по Представлениям в SQL — Понятный материал, объясняющий концепции и применение представлений в SQL.
  5. Представления в SQL: Плюсы и Минусы — Анализ преимуществ и трудностей использования представлений в SQL.
  6. Когда использовать SET вместо SELECT для переменных в SQL Server — Лучшие практики присваивания значений переменным в SQL Server.

Добавить комментарий

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