Лабораторна робота №5, Механізми зберігання даних

« Назад

Мета: отримання навиків роботи з таблицями різних типів. Теоретична частина.

Механізми зберігання даних

При створенні таблиці можна вказати її тип:

 

MySQL підтримує наступні типи таблиць:

MyІSAM. Цей тип використовується по замовчуванню. Таблиці MyІSAM допускають стискання і підтримують повнотекстовий пошук, але не забезпечують безпечне виконання транзакцій і не підтримують зовнішні ключі.

MEMORY. Таблиці зберігаються в пам'яті, а індекси їх хешуються. Використовуються для зберігання тимчасових даних.

MERGE. Ці таблиці дозволяють при запитах обробляти колекції таблиць MyІSAM, що дозволяє обійти обмеження на максимальний розмір файлів в операційних системах.

ARCHIVE. Таблиці можуть зберігати великі об’єми даних, але з обмеженими можливостями їх обробки (тільки запити insert, select).

CSV. Ці таблиці зберігаються на сервері у вигляді єдиного файлу, який містить розділені комами значення.

InnoDB. Ці таблиці підтримують безпечне виконання транзакцій, тобто підтримують можливості commit, rollback.

Таблиці MyISAM

Таблиці ISAM можуть бути, статичні, динамічні або стиснуті. Таблиці з числовими типами і типом char будуть статичними. Таблиці, які мають хоча б один стовпчик типу varchar, text або blob будуть динамічними.

Стискаються таблиці за допомогою програми myisampack. Стиснуті таблиці доступні тільки для читання. Для внесення в них змін їх потрібно розтискати.

Таблиці MyISAM забезпечують повнотекстовий пошук і індексацію.

1. Створити таблицю для повнотекстового пошуку:

Таблиці InnoDB

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

Після створення таблиці її тип можна поміняти за допомогою оператора alter table:

Ці оператори перетворюють таблиці в тип InnoDB. При необхідності можна зробити зворотне перетворення, але з параметром type=MyISAM.

2. Змінити тип існуючих таблиць:

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

set autocommit=0;

і включити командою

set autocommit=1;

Якщо режим автоматичної фіксації включений, то транзакція має починатися з оператора:

start transaction;

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

commit;

Відмінити не зафіксовану транзакцію і повернутися до попереднього стану бази даних можна за допомогою команди:

rollback.

3. Відкрити два з'єднання з базою даних. У першому з'єднанні добавити нові записи в таблицю Замовлення в режимі транзакції:

4. Перевірити, чи видно нове замовлення:

5. Залишивши поточне з'єднання відкритим, перейти в інше з'єднання і виконати такий же запит select. Замовлення не відобразиться у виводі:

Empty set (0.00 sec)

Повернутися до першого з'єднання і зафіксувати транзакцію

>commit;

Тепер відповідний рядок буде видно і в другому з'єднанні.

6. В таблиці Замовлення зняти 10 грн. із поля Сума для Код_замовлення=1 і добавити в поле Сума для Код_замовлення=2:

Зовнішні ключі

Тип InnoDB підтримує зовнішні ключі. Наприклад, потрібно вставити рядок в таблицю Елементи_замовлення. При цьому необхідно вказувати допустиме значення стовпця Код_замовлення. У випадку таблиць MyІSAM допустимість значення, що вставляється Код_замовлення приходиться перевіряти в коді додатку. Застосування зовнішніх ключів в таблиці InnoDB дозволяє цю перевірку доручити базі даних. 

7. Зробити поле Код_замовлення в таблиці Елементи_Замовлення зовнішнім ключем по відношенню до поля Замовлення.Код_Замовлення:

Щоб переконатися в роботі цієї зміни, можна попробувати вставити значення поля Код_замовлення = 555, якого не має в таблиці Замовлення.

В результаті буде отримано повідомлення про помилку:

ERROR: Cannot add or update a child row 

Процедури

Для роботи з процедурами користувач повинен мати привілеї: create routine, drop routine, execute.

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

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

delimiter // - змінює обмежувач кінця оператора з ";" на "//".

create procedure Загальна_сума_Замовлень(out total float) - створення процедури з іменем Загальна_сума_Замовлень. Параметр total має значення in [out, inout] - вхідний, тип - float. Тіло процедури розміщується між операторами begin, end.

delimiter; - змінює обмежувач кінця оператора з "//" на ";".

2.  Виклик процедури

call Загальна_сума_Замовлень(@t);

@t - змінна для зберігання результату. 

3. Select(@t); - перегляд результату виконання. 

Створення функцій

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

1. Функція, яка обчислює ціну із збільшеною надбавкою:

2. Виклик процедури select Ціна_з_надбавкою(100)

Локальні змінні можна оголосити всередині блоку begin ... end за допомогою оператора declare.

3. Створені функції і процедури можна продивитися або вилучити: