Змінні та тимчасові таблиці

 

Змінні та тимчасові таблиці

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

Оператор mySQL set

Мова запитів SQL, крім операторів для роботи з базами даних, включає в себе адміністративні команди, які є засобами управління СУБД. Серед команд адміністрування варто згадати оператор set, який дозволяє встановлювати системні і користувальницькі змінні.

Оператор set дозволяє встановлювати системні змінні, які впливають на роботу сервера і клієнта. А також встановлювати значення користувацьких змінних.

 

Приклад (використання оператора set)

set @total = 5;

                 (перегляд змінної)

SELECT @total;

 

Змінні SQL

СУБД MySQL надає можливість збереження результатів поточного запиту для використання в наступних запитах в змінних SQL. Оголошення змінних починається з символу @ за яким слідує ім'я змінної. Значення змінним присвоюються за допомогою оператора select з використанням оператора присвоювання ":=".

Приклад (Оголошення і використання змінних SQL)

SELECT @total := COUNT (*) FROM products;

@total := COUNT(*)

30

SELECT @total;

total

30

 

У прикладі оголошується змінна @ total, якій присвоюється число записів у таблиці products навчальної бази даних shop. Потім у рамках поточного сеансу у наступних запитах з'являється можливість використання даної змінної.

Зауваження:

• Змінна діє тільки у рамках одного сеансу з'єднання з сервером MySQL і припиняє своє існування після розриву з'єднання.

У наступному прикладі з таблиці products витягується інформація про товарну позицію з найвищою ціною.

Приклад (Витяг товарної позиції з найвищою ціною)

 

SELECT @price MAX(price) FROM products;

 

@price­ MAX(price)

725.00

 

SELECT id_product, name, price, count

FROM products WHERE price = ©price;

 

Id_product         name                                      price  count

 7              Intel Pentium 4 3.2GHz                  725.00        5

 

Якщо в якості значення змінної передається ім'я стовпця, то змінна отримає останнє значення (Наступний приклад).

 

Приклад

 

SELECT @id := id_catalog FROM catalogs;

 

@id := id_catalog

1

2

3

4

5

 

SELECT @id;

 

@id

5

 

SELECT * FROM catalogs WHERE id_catalog = @id;

 

Id_catalog name

5               Оперативна пам’ять

 

Як значення змінної необов'язково повинні виступати результати виконання функцій і значення стовпців таблиці, можна використовувати довільні числові і рядкові значення (приклад).

 

Приклад

 

 SELECT @id := 3;

 

@id := 3

3

 

SELECT * FROM catalogs WHERE id_catalog = @id;

 

id _catalog         name

3     Відеоадаптери

 

Слід пам'ятати, що імена змінних чутливі до регістру (Приклад).

 

Приклад (Імена змінних чутливі до регістру)

 

SELECT @id:=5, @ID:=3;

 

@id := 5   @ID := 3

 

SELECT @id, @ID;

 

@id @ID

5     3

 

Змінні також можуть оголошуватися за допомогою оператора set, як це продемонсторовано у прикладі.

 

Приклад

SET @last = CURDATE() - INTERVAL 7 DAY;

SELECT CURDATE(), @last;

 

CURDATE()     @last

2005-06-18        2005-06-11

 

 

Зауваження

• При використанні оператора set у якості оператора присвоєння може використовуватись як ":=", так і звичайний знак рівності "=".

 

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

 При використанні змінних слід пам'ятати, що не рекомендується одночасно присвоювати і використовувати змінну в одному запиті, тому що це може призводити до помилок, які важко виявити.

 

Приклад (Некоректне використання змінних)

SET @a = ’test1’;

SELECT @a, (@a : = 20);

 

@a  @a := 20

test 20

 

SET @a = 'test';

SELECT (@a : = 20), @a;   

 

@a  : = 20 @a

20             20

 

Як видно з прикладу, порядок проходження виразів за участю змінних  має значення для результату, що повертається запитом.

 

Тимчасові таблиці

 

Змінна SQL дозволяє зберегти одне проміжне значення. Коли необхідно зберегти результуючу таблицю, вдаються до тимчасових таблиць. Створення тимчасових таблиць здійснюється за допомогою оператора CREATE temporary table, синтаксис якого нічим не відрізняється від синтаксису оператора CREATE TABLE.

 

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

 

Приклад (Створення тимчасової таблиці)

CREATE TEMPORARY TABLE temp (id_catalog INT, name TINYINT);

 

У прикладі демонструється створення таблиці temp. Якщо виникає необхідність видалити таблицю до моменту завершення з'єднання з сервером, можна скористатися оператором drop table (приклад).

 

Приклад (Видалення тимчасової таблиці)

DROP table temp;

 

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

 

Приклад (Створення тимчасової таблиці за допомогою вкладеного запиту)

CREATE TEMPORARY TABLE temp

SELECT id_catalog, COUNT(id_catalog) AS total

FROM products GROUP BY id_catalog;

 

select * from temp;

 

id_catalog total

1               9

2               6

3               4

4               5

5               6

 

Слід звернути увагу на той факт, що при такому способі створення таблиці не потрібно визначати структуру таблиці temp - вона автоматично приймає структуру результуючої таблиці оператора select.

 Тимчасова таблиця temp може виступати в якості предмета запиту. У прикладі підраховується сума значень стовпця total.

 

Приклад (Запит до тимчасової таблиці)

select sum(total) from temp;

 

SUM(total)

30

 

Помістивши в змінну @total загальне число товарних позицій у таблиці products можна отримати відсоткове співвідношення числа товарних позицій у різних відділах навчального електронного магазину (приклад).

 

Приклад (Відсоткове відношення числа товарних позицій по відділах)

select @total := sum(total) from temp;

 

@total := SUM(total)

30

 

select id_catalog, format((total/@total)*100,2) AS percent

FROM temp;

 

id_catalog percent

1               30.00

2               20.00

3               13.33

4               16.61

5               20.00

 

Системна змінна big tables дозволяє повідомити СУБД MySQL, де повинні зберігатися тимчасові таблиці: в оперативній пам'яті або на диску. Якщо змінна встановлюється в 1, всі тимчасові таблиці зберігаються на диску, якщо в 0, то в оперативній пам'яті. Встановити нове значення змінної можна за допомогою оператора set (приклад).

 

Приклад (Управління системною змінною BIG TABLES)

select @@big_tables;

 

@@big_tables

       0

 

set big_tables = 1;

 

Для того щоб витягти поточне значення системної перемінної, її ім'я має передувати двома символами @.

 

Зауваження:

1. Починаючи з версії MySQL 4.0.0, ручне управління цією змінною, швидше за все, не буде потрібно, тому що MySQL сама приймає рішення про те, де розміщувати тимчасову таблицю.
2. В MySQL 5.0.1 з'явилася серйозна альтернатива тимчасовим таблицям у вигляді уявлень. Насправді уявлення часто також є тимчасовими таблицями, тільки про їх створення та знищення піклується СУБД MySQL.

 

Перегляди

 

 

Основною структурною одиницею в реляційних базах даних є таблиці. Однак мова запитів SQL надає ще один спосіб організації даних - перегляди. Перегляд - це запит на вибірку (select), якому присвоюється унікальне ім'я і який можна зберігати або видаляти з бази даних як звичайну процедуру. Перегляди дозволяють побачити результати збереженого запиту, таким чином, начебто це повноцінна таблиця бази даних.

Зауваження:

• Перегляди та всі пов'язані з ними оператори доступні в СУБД MySQL, починаючи з версії 5.0.1.

 

Коли СУБД MySQL зустрічає в запиті посилання на перегляд, він відшукує його визначення, збережене в базі даних. Потім відбувається перетворення користувальницького запиту за участю перегляду в еквівалентний запит до вхідних таблиць. Після чого відбувається виконання запиту. Таким чином, клієнт може працювати з переглядами так, як би вони існували незалежно від таблиць. Якщо тіло перегляду просте, то кожен рядок перегляду формується "на льоту". Якщо тіло складне, СУБД MySQL "матеріалізує" представлення у вигляді тимчасової таблиці в оперативній пам'яті або на жорсткому диску.

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

Враховуючи все вищесказане, можна виділити наступні переваги переглядів:

1. безпека - кожному користувачу можна дозволити доступ до невеликого числа переглядів, що містять тільки ту інформацію, яку йому дозволено знати;
2. простота запитів - за допомогою перегляду можна витягти дані з декількох таблиць і представити їх як одну таблицю, замінюючи запит до багатьох таблиць в однотабличний запит до подання;
3. простота структури - перегляди дозволяють створити для кожного користувача власну "структуру" бази даних, відображаючи лише ті дані, які йому потрібні, і "не засмічуючи" результати допоміжними стовпцями, які користувачу свідомо не знадобляться;

 

Крім переваг, описаних вище, перегляди мають ряд недоліків:

 

1. продуктивність-перегляди створюють лише видимість існування відповідної таблиці, і СУБД MySQL доводиться перетворювати запит до перегляду в запит до вхідних таблиць. Якщо подання відображає багатотабличний запит, то простий запит до перегляду стає складним об'єднанням і на його виконання може знадобитися багато часу;
2. обмеження на оновлення - коли користувач намагається оновити рядки перегляду, СКБД MySQL необхідно оновити рядки у вихідних таблицях. Це можливо тільки для простих переглядів; складні перегляди оновити не можна, вони доступні тільки для вибірки.

 

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

 

Створення переглядів

 

Створення переглядів здійснюється за допомогою оператора create view, який має наступний синтаксис:

 

CREATE [or replace] [algorithm = {undefined | merge | temptable}] VIEW view_name [(column_list)] AS select_statement [with [cascaded | local] check option]

 

Оператор створює перегляд view_name зі стовпцями, перерахованими в cnlumn_list, на підставі SELECT-запиту select_statement. У прикладі наводиться створення перегляду cat, який дублює таблицю catalogs навчальної бази даних shop.

 

Приклад (Створення перегляду)

create view cat as select * from catalogs;

 

select * from cat;

 

id_catalog name

1               Процесори

2               Материнскі плати

3               Відеоадаптери

4               Жорсткіе диски

5               Оперативна пам'ять

 

У каталозі даних для перегляду cat буде створений файл cat.frm, який, на відміну від файлів визначення структури таблиць, є не бінарним, а текстовим.

 

Приклад (Структура файлу перегляду)

TYPE=VIEW

query-select 'shop'.'catalogs'.'id_catalog' AS

'id_catalog','shop'.'catalogs'.'name' AS 'name' from 'shop'.'catalogs'

md5-d0f2f9ade597ab03f3c876837262b4d6

updatable=l

algorithm=0

with_check_opt ion=0

revision=l

timestamp-2005-07-14 14:51:37 create-version=l

source=CREATE VIEW cat AS SELECT * FROM catalogs

 

У попередньому прикладі список стовпців перегляду не уточнюється, тому перегляд приймає структуру таблиці catalogs. Однак при створенні перегляду можна явно вказати список стовпців і навіть змінити їх назви та порядок слідування (приклад).

 

Приклад (Зміни назви стовпців та порядку їх слідування)

CREATE VIEW cat (catalog, id)

AS SELECT name, id_catalog FROM catalogs;

 

SELECT * FROM cat;

 

catalog                        id

Процесорыи               1

Материнскі плати      2

Відеоадаптери           3

Жорсткіе диски          4

Оперативна пам'ять   5

 

Як видно з прикладу, назва стовпця name змінюється на catalog, a id_catalog на id. При цьому порядок їх слідування змінюється на зворотний. При формуванні списку стовпців перегляду задаються тільки імена стовпців, тип даних, довжина, а інші характеристики беруться з визначення стовпця вихідної таблиці.

В якості стовпців перегляду можуть виступати обчислювані стовпці. Нехай для таблиці products слід створити перегляд goods, що містить ім'я товарної позиції, а також її ціну в гривнях, доларах та євро. Нехай курс долара становитиме 8,8 гривень, а євро 14,5 гривень, тоді запит на створення перегляду може виглядати так, як це показано в наступному прикладі.

 

Приклад (Створення перегляду з обчислюваними стовпцями)

CREATE OR REPLACE VIEW goods (name, rubl, doll, euro)

AS SELECT name, price, price/8.8, price/14.5 FROM products;

 

SELECT * FROM goods;

 

name                           rubl            doll             euro

Celeron 1.8                 595.00        55.3819      46.2318

Celeron 2.0GHz 969.00        68.3680      57.0724

Celeron 2.4GHz 109.00        73.2291      61.1304

 

Слід зазначити, що перегляд goods, крім того, не містить додаткових стовпців, таких як опис кожної товарної позиції, зовнішній ключ для зв'язку з таблицею catalogs тощо, здатних ускладнити роботу з прайс-листом. Такі перегляди називаються вертикальними переглядами. Вертикальні перегляди широко застосовуються для обмеження доступу користувачів до стовпців таблиці. Наприклад, можна створити перегляд list_user, який відображатиме прізвище та ініціали користувача, приховуючи поля з контактними телефонами та адресами електронної пошти (приклад).

 

Приклад (Перегляд списку користувачів)

CREATE OR REPLACE VIEW list_user

AS SELECT CONCAT(surname, "

SUBSTRING(name,1,1),

SUBSTRING (patronymic, 1,1), ".") AS name

FROM users ORDER BY name;

 

SELECT * FROM list_user;

 

name

Іванов А.В.

Корнєєв A.A.

Кузнецов M.B.

Лосев С.І.

 

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

 

Приклад

SELECT * FROM list_user ORDER BY name DESC;

 

Під час сортування результатів запиту до перегляду list_user пропозицію order by самого перегляду ігнорується.

 Поряд з вертикальними переглядами використовуються горизонтальні перегляди, які обмежують доступ користувачів до рядків таблиць, роблячи видимими тільки ті рядки, з якими вони працюють. Припустимо, у навчальному електронному магазині shop, у якому п'ять каталогів, працюють три менеджера, відповідальність яких розподіляється за каталогами наступним чином:

• Відеоадаптери - перший менеджер;
• Жорсткі диски - другий менеджер;
• Материнські плати - перший менеджер;
• Оперативна пам'ять - третій менеджер;
• Процесори - третій ме неджер.

Для того щоб при роботі з таблицею products менеджер бачив тільки ті товарні позиції, за які він відповідає, можна створити три перегляди: first, second та third для першого, другого і третього менеджерів відповідно (дивіться приклад). Потім облікові записи менеджерів слід позбавити привілеї доступу до таблиці products і дозволити проглядати тільки свої перегляди. Таким чином, з одного боку, менеджерам не потрібно шукати свої товарні позиції серед великої кількості інших товарних позицій, що не мають до них відношення, з іншого боку, вони не зможуть випадково внести зміни в товарні позиції інших менеджерів.

 

Приклад (створення переглядів first, second та third)

CREATE VIEW first

AS SELECT * FROM products

WHERE id_catalog IN         (SELECT id_catalog

       FROM catalogs

       WHERE паше = 'Відеоадаптери' OR

       name = 'Материнскі плати')

ORDER BY name;

 

SELECT name, price, count FROM first;

 

name                                                       price           count

ASUSTEK A9600XT/TD             515.00        2

Asustek P4C800-E Delux             539.00        4

Asustek P4P800-VM4L i865G      I        251.00        6

 

CREATE VIEW second

AS SELECT * FROM products

WHERE id_catalog IN (SELECT id_catalog

       FROM catalogs

       WHERE name = 'Жорсткіе диски')

 ORDER BY name;

 

SELECT name, price, count FROM second

 

name                                     price           count

Maxtor 6B200P0                 358.00        4

Maxtor 6Y120P0                 245.00        6

Samsung SP0812C     209.00        5

 

CREATE VIEW third

AS SELECT * FROM products

WHERE id_catalog     IN (SELECT id_catalog

FROM catalogs

       WHERE name = 'Процессори' OR

       name = 'Оперативна пам’ять')

ORDER BY name;

 

SELECT name, price, count FROM third;

name                           price           count

Celeron 1.8                 595.00        10

Celeron 2.0GHz 969.00        2

Celeron 2.4GHz 109.00        4

 

 

Використання вкладених запитів при формуванні переглядів не зовсім раціонально, тому що на перегляди first, second та third можуть накладатися зовнішні умови. Це призведе до того, що швидкість виконання запиту буде невелика. При формуванні запитів краще поступитися їх читабельністю і обчислити заздалегідь первинні ключі каталогів, які повертаються вкладеними запитами (приклад нижче). Це дозволить не виконувати багаторазово додатковий вкладений запит для кожного рядка.

 

Приклад

CREATE VIEW first

AS SELECT * FROM products

WHERE id_catalog IN (2, 3)

ORDER BY name;

 

CREATE VIEW second

AS SELECT * FROM products

WHERE id_catalog IN (4)

ORDER BY name;

 

CREATE VIEW third

AS SELECT * FROM products

WHERE id_catalog IN (1, 5)

ORDER BY name;

 

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

Пропозиція algorithm визначає алгоритм формування кінцевого запиту за участю перегляду і може приймати три значення.

• merge - при використанні даного алгоритму запит об'єднується з переглядом таким чином, що перегляд замінює собою відповідні частини в запиті.
• temptable - результуюча таблиця перегляду поміщається в тимчасову таблицю, яка потім використовується в кінцевому запиті.
• Undefined - в даному випадку СУБД MySQL самостійно намагається вибрати алгоритм, вважаючи за краще використовувати підхід merge і вдаючись до алгоритму temptable (створення тимчасової таблиці) тільки у разі необхідності, тому що метод merge більш ефективний.

Примітка:

• Якщо жодне із значень algorithm не вказано, за замовчуванням призначається UNDEFINED.

 

Приклад:

CREATE ALGORITHM = TEMPTABLE VIEW cat AS SELECT * FROM catalogs;

 

Запит у прикладі вимагає від MySQL при кожному зверненні до перегляду cat створювати тимчасову таблицю. При створенні перегляду за допомогою запиту, поданого в наступному прикладі, тимчасова таблиця для результуючої таблиці SELECT-запиту не буде створюватися ніколи.

 

Приклад:

CREATE ALGORITHM = MERGE VIEW cat AS SELECT * FROM catalogs;

 

Найбільш зручно використовувати перегляди для формування згрупованих таблиць. При роботі з такими таблицями СУБД MySQL самостійно формує тимчасову таблицю. Створимо перегляд price із загальною вартістю товарних позицій в кожному з каталогів (приклад).

 

Приклад (перегляд price)

CREATE VIEW price

AS SELECT id_catalog, SUM(price*count) AS price

FROM products

GROUP BY id_catalog

ORDER BY price;

 

SELECT * FROM price;

 

id_catalog price

       3       63430.00

       4       68718.00

       2       74291.00

5     121031.00

1     182554.00

 

Приклади змішаного використання переглядів та таблиць в вибірках:

SELECT catalogs.name, price.price

FROM price, catalogs

WHERE price.id catalog = catalogs.id_catalog

ORDER BY catalogs.name;

 

name                           price

Відеоадаптери           3430.00

Жорсткіе диски          8718.00

Материнскіе плати     4291.00

Оперативна пам'ять   21031.00

Процессори                82554.00

 

SELECT MIN(price), MAX(price), SUM(price) FROM price;

 

MIN (price)                 MAX (price)        SUM (price)

3430.00                      82554.00             10024.00

 

До згрупованого перегляду price не можна застосувати оператори update та delete, т. к. сумарна вартість товару для кожного з каталогів зберігається в тимчасовій таблиці, а самі операції оновлення і видалення сумарної вартості, враховуючи структуру бази даних shop, не мають сенсу. Використання операторів update та delete спільно з представленнями не допускається, коли в select-запит, що лежить в основі подання, входять наступні вирази:

• distinct;
• group by;
• having;
• union або union all;
• вкладені запити в select;
• перехресне, ліве або праве об'єднання;
• перегляди в конструкції from, до яких не можна застосовувати оператори update та delete;
• вкладені запити в конструкції where, які посилаються на таблиці з секції from;
• прості літеральні значення, такі як цифра 5 або рядок 'Hello' в списку стовпців на вибірку;
• algorithm = temptable (при явному використання тимчасових таблиць).

Необов'язкове ключове слово or replace дозволяє замінити перегляд, якщо перегляд з таким ім'ям вже існує. Якщо це ключове слово не використовується, спроба створення перегляду поверх існуючого призводить до помилки 1050: "Таблиця cat вже існує" (приклад).

 

Приклад:

CREATE VIEW cat as SELECT * FROM catalogs;

error 1050: Table cat already exists

 

CREATE OR REPLACE VIEW cat as SELECT * FROM catalogs;

Query OK, 0 rows affected (0.02 sec)

 

Зауваження:

• Використання оператора create view вимагає привілеї create view, а також привілеїв select для кожного стовпця, використовуваного в перегляді. При застосуванні ключового слова or replace також потрібно привілей delete для перегляду.

При створенні переглядів за допомогою оператора create view перегляд зберігається в поточній базі даних. Однак перегляд можна створити і в довільній базі даних, вказавши ім'я бази даних за допомогою розширеного імені таблиці dbase.tbl (приклад).

 

Приклад:

CREATE VIEW test.cat AS SELECT * FROM catalogs;

 

USE test;

SELECT * FROM cat;

 

id_catalog          name

1                        Процессори

2                        Материнскі плати

3                        Відеоадаптери

 

Тепер отримати доступ до таблиці catalogs можна і в базі даних test, не вдаючись до розширеного імені - достатньо звернутися до перегляду cat. Таблиці та перегляди розділяють простір імен бази даних, тому імена переглядів і таблиць не можуть збігатися і повинні бути унікальними в межах однієї бази даних.

В якості select-запиту до перегляду може виступати не тільки запит до таблиці, але і запит до системної функції MySQL (приклад).

 

Приклад (Перегляд функції now)

CREATE VIEW time AS SELECT NOW() AS time;

 

SELECT * FROM time;

 

time

2005-07-14 18:15:44

 

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

• SELECT-запит не може містити підзапит в конструкції from.
• SELECT-запит не може посилатися на системну або користувацьку змінну.
• Будь-які таблиці або перегляди, на які в свою чергу посилається перегляд, повинні існувати фізично.
• Усередині збережених процедур перегляд не може посилатися на параметри процедури або локальні змінні процедури.
• Перегляди не можуть посилатися на тимчасові таблиці і самі перегляди не можуть бути оголошені тимчасовими за допомогою ключового слова temporary.
• Тригер не можна асоціювати з переглядом.

Параметр with check option додається до переглядів, к яким можуть бути застосовані оператори insert та update. У цьому випадку відбувається перевірка, щоб вставлені дані задовольняли where-умові select-запиту, що лежить в основі перегляду.

       Зауваження:

• Параметр with check option було додано в СУБД MySQL, починаючи з версії 5.02.

 

Ключові слова local і cascaded в перегляді with check option визначають контекст обмеження стосовно до інших переглядів. Ключове слово local повідомляє, що обмеження with check option поширюється тільки на поточний перегляд, а where-обмеження переглядів, на які посилається поточний перегляд, не впливають на процес вставки нових даних. Тоді як ключове слово cascaded вимагає, щоб перевірка на відповідність вставлених даних враховувала where-умови і тих переглядів, на які посилається поточний перегляд.

Якщо ні одне з ключових слів local або cascaded не вказано, вважається, що обраний варіант local.

Для демонстрації ефекту перегляду with check option створимо таблицю tbl з одним-єдиним цілочисельним стовпцем а (приклад).

 

Приклад:

create table tbl (a INT);

 

CREATE VIEW v1 AS SELECT * FROM tbl WHERE a < 2

WITH CHECK OPTION;

 

CREATE VIEW v2 AS SELECT * FROM v1 WHERE a > 0

WITH LOCAL CHECK OPTION;

 

CREATE VIEW v3 AS SELECT * FROM v1 WHERE a > 0

WITH CASCADED CHECK OPTION;

 

Далі в прикладі створюється перегляд v1, що обмежує вибірку значеннями менше двох. На перегляд v1 посилаються два перегляди: v2 і v3, які обмежують вибірку знизу значеннями більше нуля. Подання v2 обмежує вставку нових значень за допомогою виразу with local check option, і, отже, не враховує обмеження а <2 з батьківського означення. Подання v3 обмежує вставку нових даних за допомогою виразу with cascaded check option, і, отже, реально вставка нових значень обмежена умовами 0 <а <2 (наступний приклад).

 

Приклад:

INSERT INTO vl VALUES (-1) ;

Query OK, 1 row affected (0.03 sec)

 

INSERT INTO vl VALUES (2);

ERROR 1369: CHECK OPTION failed 'test.vl'

 

INSERT INTO v2 VALUES (-1) ;

ERROR 1369: CHECK OPTION failed 'test.v2'

 

INSERT INTO v2 VALUES (2);

Query ok, 1 row affected (0.05 sec)

 

видалення переглядів

 

Для видалення переглядів призначений оператор drop view, який має наступний синтаксис:

 

DROP VIEW [IF EXISTS]

view_name [, view_name] ...

 

Оператор drop view дозволяє знищити перегляд або відразу кілька переглядів за їхніми іменами (наступний приклад).

 

Приклад:

DROP VIEW cat, tbl1, tbl2;

 

Зауваження:

• Застосування по відношенню до перегляду оператора drop table призводить до виникнення помилки 1051: unknown table 'cat' (Невідома табліца'cat ').

 

Необов'язкове ключове слово if exists дозволяє уникнути помилки при використанні оператора drop view до вже видаленого перегляду (приклад). Це особливо зручно при пакетному виконанні групи операторів, коли ця незначна помилка може призвести до зупинки виконання всіх залишившихся операторів в пакетному файлі.

 

Приклад:

drop view if exists cat;

 

Редагування переглядів

 

Для редагування переглядів призначений оператор alter view, який має наступний синтаксис:

alter [algorithm - {undefined i merge | temptable}] view view_name [ (column_list) ] as select_statement

[with [cascaded | local] check option]

 

Приклад:

alter view cat as select * from catalogs;

 

Зауваження:

• Використання оператора alter view вимагає привілегії create view і delete, a також привілегіїв select для кожного стовпця, використовуваного в перегляді