Самоучитель по SQL-сервер в Linux



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

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

В подобных ситуациях обычно используются представления (views). Представление можно рассматривать как хранимый запрос, на основе которого создается объект базы данных. Этот объект очень похож на таблицу, но в его содержимом динамически отражается состояние только тех записей, которые были заданы при создании. Представления весьма гибки и универсальны; они могут строиться на основе как простых и стандартных запросов к одной таблице, так и чрезвычайно сложных запросов, в которых задействовано несколько таблиц.

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

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

CREATE VIEW представление

AS запрос

  • представление. Имя (идентификатор) создаваемого представления.
  • запрос. Полная команда SQL SELECT, определяющая содержимое представления.

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

Таблица 4.1. Таблица shipments

Поле

Тип

Модификатор

id

integer

NOT NULL DEFAULT nextval ( 'shipments_ship_id_seq' )

customer_id

integer

 

isbn

text

 

ship_date

timestamp

 

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

booktown=# SELECT COUNT(*) FROM shipments;

count

32

(1 row)

Звездочка (*) в этом запросе просто указывает PostgreSQL на необходимость эдсчета всех записей вместе со значениями NULL, которые могли бы присутствоваъ в поле с явно заданным именем. Запрос подсчитывает общее количество записей, то есть количество зарегистрированных поставок.

Предположим, данные из таблиц editions и books были объединены секцией JOIN, чтобы в выходные данные входили названия всех книг. Более того, в запрос была включена секция GROUP BY, обеспечивающая группировку поставок по названиям книг.

Вспомните, о чем говорилось выше в этой главе, — при группировке по полю title функция count() подсчитывает количество записей в каждой группе (в данном случае — для каждого названия книги). Наконец, для поля ship_date таблицы shipments вызывается функция max(), чтобы в результатах запроса выводилась дата последней поставки по каждой книге и количество экземпляров:

booktown=# SELECT count(*) AS num_shipped, max(ship_date), title

booktown-# FROM shipments

booktown-# JOIN editions USING (isbn)

booktown-# NATURAL JOIN books AS b (book_id)

booktown-# GROUP BY b.title

booktown-# ORDER BY num_shipped DESC;

num_shipped | max | title

5 | 2001-08-13 09:47:04-07 | The Cat in the Hat

5 | 2001-08-14 13:45:51-07 | The Shining

4 | 2001-08-11 09:55:05-07 | Bartholomew and the Oobleck

3 | 2001-08-14 13:49:00-07 | Franklin in the Dark

3 | 2001-08-15 11:57:40-07 | Goodnight Moon

3 | 2001-08-14 13:41:39-07 | The Tell-Tale Heart

2 | 2001-08-15 14:02:01-07 | 2001: A Space Odyssey

2 | 2001-08-14 08:42:58-07 | Dune

2 | 2001-08-07 13:00:48-07 | Little Women

2 | 2001-08-09 09:30:46-07 | The Velveteen Rabbit

1 | 2001-08-14 07:33:47-07 | Dynamic Anatomy

(11 rows)

Запрос выдает полезную информацию, но синтаксис получается слишком громоздким, и часто вводить его вручную нежелательно. В листинге 4.62 показано, как на базе этого запроса создать представление командой CREATE VIEW.

Листинг 4.62. Создание представления

booktown=# CREATE VIEW recent_shipments

booktown-# AS SELECT count(*) AS num_shipped, max(ship_date), title

booktown-# FROM shipments

booktown-# JOIN editions USING (isbn)

booktown-# NATURAL JOIN books AS b (book_id)

booktown-# GROUP BY b.title

booktown-# ORDER BY num_shipped DESC;

CREATE

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

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

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

Листинг 4.63. Использование представления

booktown=# SELECT * FROM recent_shipments;

num_shipped | max | title

5 | 2001-08-13 09:47:04-07 | The Cat in the Hat

5 | 2001-08-14 13:45:51-07 | The Shining

4 | 2001-08-11 09:55:05-07 | Bartholomew and the Oobleck

3 | 2001-08-14 13:49:00-07 | Franklin in the Dark

3 | 2001-08-15 11:57:40-07 | Goodnight Moon

3 | 2001-08-14 13:41:39-07 | The Tell-Tale Heart

2 | 2001-08-15 14:02:01-07 | 2001: A Space Odyssey

2 | 2001-08-14 08:42:58-07 | Dune

2 | 2001-08-07 13:00:48-07 | Little Women

2 | 2001-08-09 09:30:46-07 | The Velveteen Rabbit

1 | 2001-08-14 07:33:47-07 | Dynamic Anatomy

(11 rows)

booktown=# SELECT * FROM recent_shipments

booktown-# ORDER BY max DESC

booktown-# LIMIT 3;

num_shipped | max | title

2 | 2001-08-15 14:02:01-07 | 2001: A Space Odyssey

3 | 2001-08-15 11:57:40-07 | Goodnight Moon

3 | 2001-08-14 13:49:00-07 | Franklin in the Dark

(3 rows)

Листинг 4.63 демонстрирует еще одно важное обстоятельство: хотя при создании представления используется секция ORDER BY, итоговый набор можно заново отсортировать при выводе. Для этого в команду SELECT, осуществляющую выборку из представления, включается другая секция ORDER BY.

ПРИМЕЧАНИЕ

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

Уничтожение представлений

Команда уничтожения представления имеет следующий синтаксис (представление — имя уничтожаемого представления):

DROP VIEW представление

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




Книжный магазин