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

18. Использование представлений

18.1.  Что такое представление

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

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

            SELECT cust_name, cust_contact

            FROM Customers, Orders, OrderItems

            WHERE Customers.cust_id = Orders.cust_id

               AND OrderItems.order_num = Orders.order_num

              AND prod_id = ‘RGAN01’;

Рекомендуемые материалы

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

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

            SELECT cust_name, cust_contact

            FROM ProductCustomers

            WHERE prod_id = ‘RGAN01’;

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

            CREATE VIEW ProductCustomers AS

SELECT cust_name, cust_contact, prod_id

            FROM Customers, Orders, OrderItems

            WHERE Customers.cust_id = Orders.cust_id

               AND OrderItems.order_num = Orders.order_num;

           

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

· для повторного использования операторов SQL;

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

· для вывода частей таблицы вместо вывода ее полностью;

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

· для изменения формирования и отображения данных. Представления могут возвращать данные, отформатированные и отображенные иначе, чем они хранятся в таблицах.

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

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

Представление создаются с помощью оператора CREATE VIEW. Оператор можно использовать только для создания представления, которого не существовало.

Для удаления представления используется оператор DROP VIEW.

Чтобы переписать или обновить представление, вначале нужно применить по отношению к нему оператор DROP, а затем заново создать представление.

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

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

· Представления, так же как и таблицы, должны иметь уникальные имена.

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

· Для того чтобы создать представление, необходимо иметь соответствующие права доступа, которые предоставляет администратор базы данных.

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

· Во многих СУБД запрещается использование предложения ORDER BY в запросах к представлениям.

· В некоторых СУБД требуется, чтобы каждый возвращаемый столбец имел имя – это подразумевает использование псевдонимов, если столбцы представляют собой вычисляемые поля.

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

· В некоторых СУБД представления трактуются как запросы, предназначенные только для чтения. Это означает, что из представлений можно выбирать данные, но их нельзя вносить в таблицы, на основе которых было создано представление.

· Некоторые СУБД позволяют создавать представления, которые не позволяют добавлять или обновлять строки, если это добавление или обновление может привести к тому, что строки уже не будут являться частью данного представления.

18.3.  Использование представлений для упрощения сложных объединений

Часто представления используются для упрощения работы с SQL, и нередко это относится к объединениям.

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

            CREATE VIEW ProductCustomers AS

SELECT cust_name, cust_contact, prod_id

            FROM Customers, Orders, OrderItems

            WHERE Customers.cust_id = Orders.cust_id

               AND OrderItems.order_num = Orders.order_num;

В Access оператор CREATE VIEW выдает ошибку – не работает.

Можно выполнить это в виде создания таблицы.

SELECT *

INTO ProductCustomers

FROM (SELECT cust_name, cust_contact, prod_id

                          FROM Customers, Orders, OrderItems

                          WHERE Customers.cust_id = Orders.cust_id

                 AND OrderItems.order_num = Orders.order_num);

Если использовать оператор SELECT * FROM ProductCustomers, то список будет содержать любого клиента, который сделал заказ.

Для выбора списка клиентов, заказавших продукт RGAN01, нужно выполнить запрос.

            SELECT cust_name, cust_contact

            FROM ProductCustomers

            WHERE prod_id = ‘RGAN01’;

---------------------------------------------------------

            cust_name                  cust_contact

            Fun4All                      Denise L. Stephens

The Toy Store             Kim Howard

Этот запрос возвращает указанные данные из представления в результате применения предложения WHERE. Когда СУБД обрабатывает запрос, она добавляет указанное предложение WHERE к каждому уже существующему предложению WHERE в запросе к представлению.

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

18.4.  Использование представлений для переформатирования выбранных данных

Другим частным случаем использования представления является переформатирование данных.

Рассмотрим следующий запрос.

            SELECT RTRIM(vend_name) + ‘(‘+RTRIM(nend_country)+’)’ AS vend_title

            FROM Vendors

ORDER BY vend_name;

----------------------------------------

vend_title

Bear Emporium (USA)

Bears R Us (USA)

Doll House Inc. (USA)

Fun and Games (England)

Furball Inc. (USA)

Jouets et ours (France)

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

            CREATE VIEW VendorLocations AS SELECT RTRIM(vend_name) +

 ‘(‘+RTRIM(nend_country)+’)’ AS vend_title

            FROM Vendors;

Замена в Access.

SELECT *

INTO VendorLocations

FROM (SELECT RTRIM(vend_name)+'('+RTRIM(vend_country)+')' AS vend_title

            FROM Vendors);

Посредством этого оператора создается представление. Для извлечения данных необходимо выполнить.

            SELECT *

            FROM VendorLocations;

            -----------------------------

Bear Emporium (USA)

Bears R Us (USA)

Doll House Inc. (USA)

Fun and Games (England)

Furball Inc. (USA)

Jouets et ours (France)

18.5.  Использование представлений для фильтрации нежелательных данных

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

Рассмотрим пример, необходимо определить представление таки образом, чтобы оно отфильтровывало клиентов, не имеющих адреса электронной почты.

            SREATE VIEW CustomerEMailList AS

            SELECT cust_id, cust_name, cust_email

            FROM Customers

WHERE cust_email IS NOT NULL;

            Замена в Access.

SELECT *

INTO CustomerEMailList

FROM (SELECT cust_id, cust_name, cust_email

                          FROM Customers

  WHERE cust_email IS NOT NULL);

Предложение WHERE отфильтрует строки, имеющие значения NULL в столбцах cust_email.

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

            SELECT *

            FROM CustomerEMailList;

            ----------------------------------------

            cust_id                                   cust_name                  cust_email

            1000000001               Village Toys   sales@villagetoys.com

            1000000003               Fun4All          jjones@fun4all.com

            1000000004               Fun4All          destephens@fun4all.com

18.6.  Использование представлений с вычисляемыми полями

Представления полезны для упрощения использования вычисляемых полей.

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

            SELECT prod_id,

              quantity,

              item_price,

              quantity*item_price AS expanded_price

            FROM OrderItems

            WHERE order_num = 20008;

            --------------------------------------------

            prod_id           quantity          item_price       expanded_price

            RGAN01        5                      4.99p.              24.95p.

BR03              5                      11.99p.            59.95p.

BNBG01        10                    3.49p.              34.90p.

BNBG02        10                    3.49p.              34.90p.

BNBG03        10                    3.49p.              34.90p.

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

            CREATE VIEW OrderItemsExpanded AS

            SELECT order_num,

               prod_id,

               quantity,

               item_price,

               quantity*item_price AS expanded_price

            FROM OrderItems;  

Замена в Access.

SELECT *

INTO OrderItemsExpanded

FROM (SELECT order_num,

                           prod_id,

                           quantity,

                           item_price,

                           quantity*item_price AS expanded_price

             FROM OrderItems);

Чтобы получить информацию относительно заказа 20008 необходио выполнить запрос.

            SELECT *

            FROM OrderItemsExpanded

            WHERE order_num = 20008

------------------------------------------------

            order_num      prod_id           quantity          item_price       expanded_price

Рекомендация для Вас - Введение. Основные термины и определения.

            20008              RGAN01        5                      4.99p.              24.95p.

20008              BR03              5                      11.99p.            59.95p.

20008              BNBG01        10                    3.49p.              34.90p.

20008              BNBG02        10                    3.49p.              34.90p.

20008              BNBG03        10                    3.49p.              34.90p.

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