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

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

Представления применяют для многих целей:

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

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

— Они могут усилить безопасность базы данных. Пользователям можно запретить просматривать сами таблицы, разрешив доступ только к представлениям. Классический пример — таблица employees, содержащая поля name, department и salary. Поскольку информация о зарплате (salary) конфиденциальна, прямой доступ к таблице может быть ограничен, для этого создаётся специальное представление, в которое не входят конфиденциальные поля.

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

CREATE VIEW
entries_with_category
AS
SELECT
entries.title
, entries.created
, categories.name AS category_name
FROM
entries
INNER JOIN categories
ON categories.category = entries.category

Эта команда определяет представление с именем entries_with_category. Ключевое слово AS используется для связи имени entries_with_category с командой SELECT. После определения представления, можно обращаться к нему, как к таблице:

SELECT
title
, category_name
FROM
entries_with_category

Конечно же, это не таблица, представление само по себе не хранит результатов команды SELECT. В этом примере сначала выполняется команда, ассоциированная с используемым представлением, её результаты сохраняются в промежуточную таблицу, а она, в свою очередь, становится результатом условия FROM. Итог всего запроса, показанный на Рис. 3.20, достаточно очевиден.

Набор результатов похож на полученный при внутреннем объединении, задающем само представление. Заметьте, что были возвращены всего два поля, как и было указано в команде SELECT, использующей представление (в отличие от команды SELECT, задающей представление). Также, заметьте, что полю name таблицы categories в определении представления был присвоен псевдоним category_name, этот псевдоним должен использоваться во всех командах SELECT, содержащих данное представление, и он же становится именем поля в итоговом наборе результатов. Одним из свойств представлений является то, что в запросах, использующих их, доступны только те поля, которые указаны в условии SELECT самого представления. Несмотря на то, что в таблице entries есть поле content, в запросе, использующем представление, оно недоступно, и обращение к нему вызовет синтаксическую ошибку.

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

Как можно использовать представления в повседневных задачах веб-разработки?

-При работе над большим проектом в команде, вам может быть предоставлен доступ только к представлениям, а не к самим таблицам. Например, вы можете всего лишь использовать таблицу, спроектированную в Database Administrator (DBA). Вы не будете даже подозревать, что работаете с представлениями, поскольку синтаксически и таблицы, и представления используются в условии FROM абсолютно одинаковым образом.

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

Подзапросы и производные таблицы

Мы начали эту главу с условия FROM, постепенно продвигаясь от простых таблиц к различным типам объединений. Мы вкратце рассмотрели запрос с UNION и его подвыборки, а также увидели как представления упрощают использование сложных выражений с объединением. Чтобы закончить эту главу, осталось разобрать производные таблицы. Вот пример:

SELECT
title
, category_name
FROM
( SELECT
entries.title
, entries.created
, categories.name AS category_name
FROM
entries
INNER JOIN categories
ON categories.category = entries.category
) AS entries_with_category

Здесь, производная таблица — это весь запрос SELECT, помещённый в скобках (скобки являются частью синтаксиса и предназначены для выделения запроса). Производная таблица — это общий случай подзапроса, запроса, который вложен в другой запрос или зависит от него (почти как подвыборки при слиянии). Выглядит знакомо, не так ли? Этот подзапрос — тот же, что использовался в представлении entries_with_category в предыдущем разделе. В самом деле, как каждому представлению, так и каждой производной таблице необходимо присвоить имя с помощью ключевого слова AS (в последней строке). За такое сходство производные таблицы часто называют встраиваемыми представлениями. Так и есть, они задают табличную структуру — результат подзапроса, встраивая её непосредственно в команду SQL, а эта табличная структура, в свою очередь, используется в условии FROM во внешнем или главном запросе. В целом, в условии FROM может быть указано любое выражение, генерирующее табличную структуру. Можно указать даже запрос с UNION, о котором мы вкратце говорили, если он оформлен в виде производной таблицы и заключён в скобки. Производные таблицы чрезвычайно полезны в SQL. Мы ещё встретимся с ними на страницах книги.

Подходя к концу: условие FROM

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

-отдельные таблицы

-объединённые таблицы

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

-подзапросы или производные таблицы

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