4 Лабораторна робота № 5 «Робота з поданнями»

4.1 Мета роботи

Метою роботи є придбання навичок створення та використання подань в середовищі MySQL.

4.2 Завдання до лабораторної роботи

4.2.1 Ознайомитися зі змістом пункту 4.3 даних методичних указівок.

4.2.2 Згідно з отриманим завданням створити відповідні об’єкти БД. Навести приклади їхнього використання.

4.3 Основні теоретичні відомості

Бувають ситуації, коли користувачеві необхідно дозволити доступ не до всієї інформації, що зберігається в таблиці, а лише до її частини: наприклад, необхідно, щоб продавці могли переглядати таблицю Salespeople, але не могли бачити комісійні своїх колег. Або ситуація, коли щодня необхідно одержувати інформацію на основі різних таблиць за допомогою складних запитів. Для того, щоб не повторювати щораз складний запит, можна створити на його основі подання (VIEW).

Подання – це динамічний результат однієї або декількох реляційних операцій над базовими відношеннями з метою створення деякого іншого відношення.

Подання є віртуальним відношенням (virtul relation), якого реально в базі даних не існує, але яке створюється на вимогу окремого користувача в момент надходження цієї вимоги.

З погляду користувача подання є відношенням, що постійно існує та з яким можна працювати, як із базовим відношенням.

Однак подання не зберігається в базі даних так, як базові відношення (хоча його визначення зберігається в системному каталозі). Вміст подання визначається як результат виконання запиту до одного або декількох базових відношень.

Подання  це вікно, через яке користувач може побачити інформацію з БД. При зверненні до подання виконується запит до базової таблиці або таблиць, на основі яких створено подання.

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

create view Прайс-лист

as

select назва, ціна_відпускна as ціна

from Товари;

Зверніть увагу на те, що в пропозиції CREATE VIEW після імені подання не зазначені імена стовпців, з яких воно буде складатися. Тому беруться імена стовпців базової таблиці або їхні псевдоніми, на основі яких будується подання. Якщо при створенні подання використовуються агрегатні функції, то такі стовпці повинні бути названі в пропозиції CREATE VIEW.

Подання, засновані на запиті, що використовує речення GROUP BY, називаються груповими. При цьому допускається використання речення HAVING.

При створенні подання можна використовувати з'єднання таблиць. Наприклад, в БД є три таблиці:

Покупці(Код,Прізвище,Тел),

Продавці (Код, Прізвище),

Замовлення (Ном, Покупець, Дата, Вартість, Продавець).

Необхідно визначити за минулий місяць всіх покупців з їхніми замовленнями та продавців, які їх обслуговували:

CREATE VIEW Покупці_Замовлення

AS

SELECT П.Прізвище as Покупець, Пр.Прізвище as Продавець, З.Дата, З.Вартість

FROM Замовлення З, Продавці Пр, Покупці П

WHERE З.Покупець=П.Код and З.Продавець=Пр.Код

and month(date())-month (З.Дата)=1;

Це подання побудовано на основі запиту, що з'єднує три таблиці. Звернутися до нього можна також через запит. Наприклад, нам необхідно одержати всі замовлення покупця Іванова в минулому місяці:

SELECT *

FROM Покупці_Замовлення

WHERE Покупець='Іваненко';

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

Якщо користувачі вносять у подання деякі припустимі зміни, ці зміни негайно заносяться до відповідних базових таблиць.

На підставі таблиці Продавці (Код, Прізвище, Місто, Рейтінг) створимо подання:

CREATE VIEW Топ_Продавці

AS

SELECT *

FROM Продавці

WHERE Рейтінг=300;

А тепер виконаємо команду оновлення