Лабораторна робота № 6

Створення , адміністрування та заповнення бази даних в середовищі СКБД MySQL

Мета: Ознайомитися з процесом інсталяції оболонки Denwer, СКБД MySQL з інтерфейсом phpMyAdmin. Навчитися встановлювати з'єднання з віртуальним сервером, створювати базу даних на локальному віртуальному диску і виконувати елементарні дії, налаштовувати права доступу і встановлювати цілісність даних.

Завдання:

1. Інсталювати на персональний комп'ютер інструментальний набір WEB-розробника Denwer і СКБД MySQL. Виконати запуск і тестування роботи компонентів.

2. Створити права з різними рівнями доступу до даних: користувача і адміністратора БД.

3. Створити базу даних в MySQL відповідно до індивідуального завдання.

4. Організувати підстановку даних до дочірніх таблиць через поле зі списком, використовуючи зв’язки із батьківськими таблицями.

5. За допомогою утиліти phpMyAdminзаповнити таблиці даними. Створити в таблиці 10 записів.

Теоретичні відомості та приклад створення бази даних в середовищі MYSQL

6.1. Установка і налаштування базового пакета Denwer

Інсталяційна програма знаходиться за доступом www.denwer.ru. Базовий пакет містить більшість необхідних програм і утиліт: Apache, SSL, SSI, PHP5 з підтримкою GD, MySQL5 включає підтримку транзакцій, систему управління віртуальними хостами, систему управління запуском і завершенням всіх компонентів Денвера, phpMyAdmin – система управління MySQL через Web-інтерфейс, емулятор send mail і SMTP-сервера. Пакет призначений для швидкого налаштування компонентів, необхідних для розробки WEB-додатків з використанням СКБД MySQL і скриптів PHP. Для установки пакета скористайтеся інсталятором, який можна завантажити на сайті www.denwer.ru. На цьому ж сайті міститься докладна інструкція по установці системи.

СКБД MySQL використовує клієнт-серверну технологію доступу до даних. Тому для з’єднання з сервером MySQL і роботи з даними, що містяться у базі будемо використовувати програму-клієнт phpMyAdmin. Дана програма є набір php-скриптів, що дозволяють працювати з об’єктами СКБД MySQL (базами даних, таблицями, полями, записами тощо), виконувати основні адміністративні завдання: створення користувачів бази даних, надання їм необхідних привілеїв, зміни глобальних налаштувань сервера MySQL. Мову php, з допомогою, якої написаний phpMyAdmin використовують для написання веб-сайтів динамічної структури, а тому розглянутий MySQL-клієнт має вигляд веб-сайту і прийоми роботи з ним такі ж, як при роботі з будь-якими гіпертекстовими веб-сторінками.

Для завантаження phpMyAdmin запускаємо віртуальний сервер (ярлик Start Denwer), запускаємо браузер і в рядку адреси вводимо ім’я віртуального веб-сервера (localhost) http://www.localhost/denwer/ . На цій сторінці виконати перевірку працездатності сервера за допомогою заданих посилань. Якщо скрипти розміщені в папці phpmyadmin, то для відкриття вікна роботи з phpMyAdmin у рядок адреси вводимо:

http://www.localhost/Tools/phpmyadmin

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

Рис. 6.1. Створення бази даних в phpMyAdmin.

Вікно phpMyAdmin складається з двох частин – фреймів. Лівий фрейм використовують для навігації та вибору поточної бази даних. Правий фрейм – робочий, у ньому редагують структуру таблиць, здійснюють редагування записів та інші операції з вибраною базою. Якщо не вибрана БД, то в правому фреймі відображаються загальні функції роботи з цілим сервером MySQL, та налаштування phpMyAdmin. При виборі бази даних, у лівому фреймі буде відображено список таблиць, що містить дана БД, а в правому – таблиці, але в розширеному вигляді.

У лівому фреймі знаходяться значки: переходу на початкову сторінку; виходу з системи; відкриття вікна SQL-запиту; документація по phpMyAdmin; документація по MySQL.

У фреймі справа задається ім’я нової БД, кодування нової БД, та мову інтерфейсу phpMyAdmin (рис. 1).При створенні баз, таблиць, полів кодування, як правило, явно не вказується, а по замовчуванню використовується кодування вказане в конфігурації сервера MySQL.

Для створення нової бази даних у розділі "Actions/MySQLlocalhost/Createnewdatabase" ввести ім'я бази даних, натиснути кнопку "Create" (рис.6.1). У заголовках сторінок розташовуються SQL-скрипти, що створюється при створенні бази даних.

6.2. Створення користувачів і надання привілеїв

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

На віртуальному локальному комп'ютері для тестування бази даних привілеї адміністратора і користувача створюються після створення самої бази даних. Для цього вибираємо базу даних і вкладку Privileges (рис.1.2.).

Рис.6.2. Редагування привілеїв.

Переходимо за посиланням AddaNewUser і в інтерфейсі адміністратора створюємо нового користувача та пароль (рис. 1.3.). Ім’я користувача і пароль повинні співпадати і входити в діапазон від 6 до 10 символів.

Рис. 6.3. Створення нового користувача, пароля та привілеїв

Для забезпечення мінімальних вимог функціональності системи у правах користувача при роботі з базою даних необхідно дозволити додавати записи в таблицю і вибирати дані з неї (операції INSERT і SELECT), також оновлювати і видаляти записи (UPDATE і DELETE).

6.3. Створення таблиць бази даних

Для створення таблиці (відношення) у базі даних, слід перейти в структуру БД (коли в правому фреймі відображається розширений список таблиць). Для цього можна в верхньому рядку правого фрейму клацнути по назві БД. Для створення таблиці в середовищі phpMyAdmin скористаємося інтерфейсом "Create New Table".

Під розширеним списком таблиць, розміщена форма створення нової таблиці. Щоб створити нову таблицю, слід ввести її назву і кількість полів, та натиснути кнопку "Go". Залежно від кількості полів з’явиться форма для введення полів і їх характеристик, не всі властивості обов’язково потрібно вказувати, але для кожного поля необхідно вказати назву та тип, а для символьних полів (VARCHAR та CHAR) необхідно вказати й розмір (довжину).

Рис. 1.4. Створення атрибутів

У залежності від кількості полів з’явиться форма для їх опису і завдання характеристик. Не всі властивості обов’язково потрібно вказувати, але для кожного поля необхідно вказати назву та тип. Для символьних полів (VARCHAR та CHAR) необхідно вказати довжину. Для текстових полів важливим є кодування ("Порівняння"). Від нього залежить відображення даних, сортування та пошук даних. Приклад створення таблиці із заданням атрибутів, типи даних і властивості показано на рис.6.4. Для кирилиці рекомендується ставити кодування не чутливе до регістру символів при пошуку чи сортуванні – це cp1251_ci чи розрізняються великі і малі літери cp1251_cs.

Середовище phpMyAdmin автоматично генерує SQL-скрипт для створення нової таблиці. Синтаксис операції CREATE TABLE в MySQL, в цілому, відповідає стандарту SQL-92, проте має ряд особливостей. Зокрема, фрагмент фрази ENGINE=InnoDB визначає тип «движка» для роботи з даними, кожен з яких має свої характерні особливості. Движок за замовчуванням MyISAM не підтримує транзакцій і має середню надійність зберігання даних. Висока продуктивність читання даних з використанням оператора SELECT. Блокує всю таблицю при записі в неї даних, від чого маленька продуктивність при частих записах. Транзакційний тип движка InnoDB застосовується при інтенсивних операціях запису, завдяки можливості блокування рівня рядків таблиці. Характеризується відновлюваністю і високою надійністю зберігання даних. Підтримує зовнішні ключі.

6.4. Створення первинних ключів і встановлення зв’язків у БД

Для того, щоб при додаванні нового запису значення поля генерувалося автоматично для поля КОД встановлюємо ознаку AUTO_INCREMENT. Це означає, що поле перетвориться в лічильник, тобто при вставці нового запису значення даного поля буде автоматично збільшуватись на одиницю. Також встановлюємо ознаку PRIMARY в поле index. Аутоінкрементне поле в таблиці може бути лише одне і обов’язково включається у первинний ключ. Для числових полів є атрибут UNSIGNED (без знаку) та ZEROFILL (з правої сторони, при потребі, доповнюється нулями) (див. додаток 3). Також для всіх типів можна задати чи допускає поле порожні значення (null), та вказати значення, яке використовується по замовчуванню. Для створення таблиці після опису атрибутів натискаємо кнопку "Save". Описуємо в інтерфейсі імена атрибутів і їх типи див. рис. 6.5).

Рис. 6.5. Опис атрибутів.

Схему побудованої бази даних можна переглянути у закладці «Еще»\ «Дизайнер», яка стає доступною при виборі назви бази даних ( див. рис. 1.6.). Якщо в таблицях утворено зв’язки за допомогою зовнішніх ключів, то вони знайдуть своє відображення на схемі бази даних. На рис.1.7. наведено схему бази даних Primer.

Рис. 6.6. Виклик дизайнера для перегляду схеми бази даних

Рис.6.7. Схема бази даних Primer

6.5. Введення та редагування даних у таблицях

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

Для введення даних в таблицю вибираємо операцію «Insert»або «Вставка». Заповнюємо колонку «VALUE» («Значение) в інтерфейсі введення даних, поле ID (код) залишаємо порожнім і натискаємо на кнопку «Go»(«ОК») (рис. 1.8).

Рис. 6.8. Введення даних в таблицю

Введення даних до таблиць бази даних починаємо з батьківських незалежних таблиць. (У прикладі таблиця «Постачальник» наведена на рис.5.) Дані, введені до батьківської таблиці. будемо використовувати для формування поля зі списком, який допоможе забезпечити коректне введення даних до дочірньої таблиці, а саме, кодів, що відповідають зовнішнім ключам і забезпечують зв’язок між таблицями.

Для редагування (перегляду) структури таблиці, слід вибрати таблицю в лівому фреймі, клацнувши по ній. Переходимо на вкладку «Browse» («Обзор»), для того щоб переконатися в правильності заповнення даних. У режимі перегляду ви можете перейти в режим редагування рядка, клацнувши по ньому, виконати сортування записів по довільному полю, клацнувши по назві поля в заголовку таблиці. Щоб створити нову таблицю, слід перейти до структури бази даних, де додати нову таблицю.

Рис.6.9. Заповнена батьківська таблиця «Постачальник»

6.6 . Організація підстановки даних до дочірніх таблиць через поле зі списком

Для формування підстановки, в закладці «Структура» виберемо опцію «Связи» (рис.6.10).

Рис.6.10. Формування внутрішнього зв’язку

Рис. 6.11. Вибір стовпчика для формування списку

Рис. 6.12. Формування підстановки у дочірній таблиці

Рис. 6.13. Посилання на батьківську таблицю як джерела даних для підстановки в дочірній таблиці

В цьому ж вікні задаються правила підтримки цілісності посилань.

На рис. наведено приклад введення даних в дочірню таблицю («Накладна») з використанням списку, сформованого на основі даних з батьківської таблиці «Постачальник».

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

Рис.6.15. Заповнена таблиця «Накладна»

6.7. Питання для самоперевірки

1. Як налаштувати привілеї користувачів?

2. Які параметри використовуються для під'єднання до MYSQL?

3. Який SQL код формується при заповненні таблиці?

4. Який SQL код формується при коригуванні таблиці?

5. Який SQL код формується при перегляді таблиці?

6. Як заповнювати даними зв’язані таблиці?

7. В чому полягає призначення юнікоду?

Лабораторна робота № 7

Побудова запитів до Бази даних в середовищі MYSQL

Мета роботи: Навчитися створювати запити до бази даних в середовищі MYSQL.

Завдання:

1. Створити декілька простих запитів, за допомогою конструктора запитів, з однієї та декількох таблиць.

2. Створити запит з використанням складної довільної умови.

3. Створити підсумковий запит за допомогою агрегативних функції SQL – SUM( ), Count ( ), AVG ( ), MIN ( ), MAX ( ).

4. Виконати запит на упорядкування даних, за одним/кількома атрибутами, по зростанню та спаданню значень.

5. Створити запити на пошук з використанням індексованих полів.

6. Розробити WEB-додаток на мові HTML з використанням скриптів php для установки з'єднання з віртуальним сервером та перегляду результатів запитів.

Теоретичні відомості та приклад виконання роботи

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

7.1.1.Вибірка даних з однієї таблиці

Запит – команда, яку надають СКБД, для виведення певних даних з таблиць у пам’ять. Ці дані, звичайно, виводиться безпосередньо на екран комп’ютера або терміналу, хоча, у більшості випадків, їх можна також послати принтеру, зберегти у файлі (як об’єкт у пам’яті комп’ютера), або представити як вхідну інформацію для іншої команди або процесу. Всі запити в SQL складаються з однієї команди. Структура цієї команди проста, але можна розширювати її так, щоб виконати досить складні оцінки й обробку даних. Цю команду можна називати – SELECT (ВИБІР). Використання розриву рядка (Клавіша ENTER) є довільним. Користувач може вибрати точно, як зручніше скласти запит, у кілька рядків або в один рядок, Це пов’язано з тим, що SQL використовує крапку з комою, щоб вказувати на кінець команди, більшість програм опрацьовують розрив рядка (через натискання клавіші ENTER) як проміжок.

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

SELECT <перелік_полів> FROM <назва_таблиці>

SELECT * FROM <назва_таблиці>

Для побудови запиту в СКБД MySQL можна використати конструктор запитів (рис вкладка «Запрос по шаблону»), або самостійно написати текст запиту у вкладці SQL.

Рис.7.1. Конструктор запитів - вкладка «Запрос по шаблону»

Спочатку слід обрати таблицю (таблиці ) з яких буде відбуватися вибірка даних. Після натискання кнопки «Дополнить запрос» вам стануть доступними поля обраних таблиць.

Рис.7.2. Вибір таблиці для формування запиту

Рис.7.3. Поля таблиці Накладна доступні для вибірки

По замовчуванню, бланк складається з 3 полів для вибірки даних з таблиці. Кількість полів можна збільшити використовуючи опцію «Добавить\удалить столбцы». Після вибору кількості додаткових стовпців слід натиснути кнопку «Дополнить запрос».

Рис.7.4.Обрані поля з відміткою «Показать»

Після натискання кнопки «Дополнить запрос» у бланку запиту з’явиться SQL код запиту для перегляду. Для виконання запиту слід натиснути кнопку «Выполнить запрос».

Рис.7.5. Запит підготовлений до виконання

Рис.7.6. Результат виконання запиту

7.1.2. Внутрішнє та зовнішнє об’єднання таблиць

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

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

Для об’єднання таблиць використовуємо конструкцію JOIN, яка може мати наступний вигляд:

<таблиця> INNER JOIN <таблиця> [ON <умова>]

<таблиця> LEFT / RIGHT [OUTER] JOIN <таблиця> ON <умова>

Де під <таблиця> мається на увазі наступний запис:

<назва_таблиці> [ [AS] псевдонім]

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

Припустимо, потрібно вивести список студентів, що складається з двох полів: імені студента і факультету, на якому навчається:

SELECT students.Name, faculty.Name FROM students LEFT JOIN faculty ON students.faculty_id = faculty.ID

У даному запиті до таблиці students під’єднується таблиця faculty так, що до кожного рядка таблиці студентів приєднується справа рядок з таблиці факультетів за таким критерієм: students.faculty_id = faculty.ID. Якщо рядка, який відповідає заданій умові, в таблиці факультетів не знайшлось, то в відповідні поля результату заноситься NULL.

Оскільки при об’єднанні використано LEFT JOIN, то з таблиці students (лівої) будуть виведені всі рядки, а з таблиці faculty, які задовольняють умові об’єднання.

Конструкція INNER JOIN виводить лише ті рядки з обох таблиць, які задовольняють умові об’єднання.

Вибір способу об’єднання таблиць може впливати на кінцевий результат, і в загальному випадку призвести до втрати окремих рядків.

Для вибірки даних з двох зв’язаних таблиць, у конструкторі запитів виділяємо обидві таблиці разом та натискаємо кнопку «Дополнить запрос». Таким чином стають доступними для вибору всі поля обох обраних таблиць (рис7.7.,7.8.)

Рис. 7.7. Вибір таблиць для побудови запита зі зв’язаних таблиць

Рис.7.8.Доступні поля для побудови запиту з таблиць Накладна та Постачальник

Далі виконуємо всі кроки, як в попередньому прикладі. Отримаємо код наведений на рис 7.9. і відповідно, результат, наведений на рис.7.10.

Рис.7.9.SQL код запиту

За рахунок використання оператора з’єднання LEFT JOIN до вибірки потрапили всі рядки з таблиці Накладна та тільки зв’язані рядки з таблиці Постачальник, як показано на рис.7.10.

Рис.7.10. Результат запиту до таблиць Накладна та Постачальник

Відкоригуємо текст запиту і замість LEFT JOIN використаємо вираз RIGHT JOIN , як показано на рис.2.11. Результат вибірки, наведений на рис.2.12. , буде вміщувати всі рядки з таблиці Постачальник і зв’язані з ними рядки таблиці Накладна. Значення полів, для яких не знайшлося даних в таблиці Накладна, відтворюються як NULL .

Рис.7.11.Відкоригований текст запиту

Рис.2.12. Результат запиту з використанням оператора RIGHT JOIN

7.2.Створення запитів з використанням складної довільної умови

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

WHERE – конструкція команди SELECT, що дозволяє встановлювати предикати, умова яких може бути або істинною, або хибною для будь-якого рядка таблиці. Команда витягає тільки ті рядки з таблиці, для яких таке твердження істинне. Має наступну структуру:

SELECT * from <назва_таблиці> WHERE (<назва_поля> = X)

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

Рис.7.13. Запит з умовою фільтрації

Рис.7.14.Результати фільтрації

Предикати умови. Оператор IN визначає набір значень, які може приймати дана величина. Для цього достатньо написати такий запит:

SELECT * FROM <назва_таблиці> WHERE <назва_поля> IN (X,Y)

Даний запит еквівалентний використанню логічного оператора АБО:

SELECT * FROM <назва_таблиці> WHERE (<назва_поля> = X) OR (<назва_поля> = Y)

Оператор LIKE застосовують тільки до текстових полів, наприклад CHAR або VARCHAR – виконує пошук у текстовому полі, чи співпадає з умовою його вмісту. Є два типи групових символів, що використовуються з LIKE:

¾ символ підкреслення ( _ ) заміщає будь-який один символ;

¾ знак відсотка ( % ) заміщає послідовність будь-якого числа символів (включаючи символи нуля).

SELECT * FROM <назва_таблиці> WHERE <назва_поля> LIKE ‘text%’

7.3. Створення підсумкового запиту за допомогою агрегативних функцій

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

COUNT – видає кількість не порожніх (значення не NULL) рядків даного поля, які вибрав запит.

SUM – видає арифметичну суму всіх вибраних значень даного поля.

AVG – видає середнє арифметичне всіх вибраних значень даного поля.

MAX – видає найбільше із всіх вибраних значень даного поля.

MIN – видає найменше із всіх вибраних значень даного поля.

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

Наприклад, структура визначення кількості рядків:

SELECT COUNT(<назва_поля>) FROM <назва_таблиці>

Приклад такого запиту наведено на рис. 7.15., а його результат на рис.2.16.

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

SELECT MIN(<назва_поля>) FROM <назва_таблиці> WHERE (<назва_поля> <> 0)

Рис.7.15. Формування запита з агрегативною функцією

Рис.7.16. Результат запита з агрегативною функцією

Конструкція GROUP BY дозволяє визначати підмножину значень, і застосовувати агрегативну функцію згрупованої підмножини. Це дає можливість поєднувати поля й агрегативні функції в єдиній конструкції SELECT. Звичайно порожні групи не показуються в результаті.

SELECT <назва_поля1>, COUNT (<назва_поля2>) FROM <назва_таблиці> GROUP BY <назва_поля>

Приклад запиту з групуванням наведено на рис. 7.17.

Конструкція HAVING визначає критерії, що використовуються для видалення певних груп з виведення результату конструкції GROUP BY, так само як конструкція WHERE робить це для окремих рядків. Використання WHERE буде суперечити строгій інтерпретації ANSI. Наприклад:

SELECT <назва_поля1>, COUNT(<назва_поля2>) FROM <назва_таблиці>

GROUP BY <назва_поля1>

HAVING COUNT(<назва_поля2>) > X

Рис. 7.17. Результат запита з агрегативною функцією та групуванням

Для побудови запиту зі зв’язаних таблиць можна використати закладку «Запрос по шаблону», а потім доповнити його агрегативною функцією та групуванням.

Рис.7.18. Запит з групуванням та агрегативною функцією для зв’язаних таблиць

Рис.7.19. Результат виконання запита з групуванням, агрегативною функцією та виразом

7.4. Формування запиту на упорядкування даних

Таблиці є неупорядкованими наборами даних, і для їх упорядкування в SQL використовується команда ORDER BY. Кілька стовпців впорядковуються каскадно один усередині іншого. Можна визначати зростаючий (ASC) чи спадаючий (DESC) порядок для кожного стовпця. За замовчуванням установлений – зростаючий. Наприклад:

SELECT * FROM <назва_таблиці> ORDER BY <назва_поля1> ASC, <назва_поля2> DESC

7.5. Використання індексів при формуванні запитів

Створення індексів є одним зі способів пришвидшити виконання SQL-запитів. Будь-які стовпці в таблицях можуть бути проіндексовані. Індекси використовують для того, щоб: швидко знайти рядки, що відповідають виразу WHERE; вибрати рядки з інших таблиць при виконанні об'єднань (оператор JOIN); знайти величини MAX( ) або MIN( ) для заданого індексованого стовпця; виконувати сортування чи групування в таблиці, якщо ці операції робляться на крайньому лівому префіксі ключа, що використовується.

Якщо дана таблиця має складний індекс, то будь-який крайній лівий префікс цього індексу використовується як оптимізатор для знаходження рядків. Наприклад, якщо є індекс по трьох стовпцях (col1, col2, col3), то існує потенційна можливість індексованого пошуку по (col1), (col1, col2) і (col1, col2, col3).

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

SELECT * FROM <назва_таблиці> WHERE col1=val1;

SELECT * FROM <назва_таблиці> WHERE col2=val2;

SELECT * FROM <назва_таблиці> WHERE col2=val2 AND col3=val3;

Якщо індекс існує по (col1, col2, col3), то тільки перший показаний вище запит використає даний індекс. Другий і третій запити дійсно включають індексовані стовпці, але (col2) і (col2, col3) не є крайньою лівою частиною префіксів (col1, col2, col3).

MySQL застосовує індекси також для порівнянь LIKE, якщо аргумент у виразі LIKE є статичним рядом, що не починається із символу-шаблона. Наприклад, такі команди як SELECT використовують індекси:

SELECT * FROM <назва_таблиці> WHERE key_col LIKE "Patrick%";

SELECT * FROM <назва_таблиці> WHERE key_col LIKE "Pat%_ck%";

У першій команді розглядаються тільки рядки з "Patrick" <= key_col < "Patrick", а в другій – тільки рядки з "Pat" <= key_col < "Pat".

Наступні команди SELECT не будуть використовувати індекси:

SELECT * FROM <назва_таблиці> WHERE key_col LIKE "%Patrick%";

SELECT * FROM <назва_таблиці> WHERE key_col LIKE other_col;

У першій команді величина LIKE починається із шаблонового символу. У другій команді величина LIKE не є константою.

При створенні індексу по текстових полях (char, varchar text) важливо вдало підібрати довжину індексу. Довжина індексу визначає кількість початкових символів в рядку, по яких буде будуватися індексація. Занадто велика довжина спричинить збільшення розміру індексу, а отже і бази даних, а індекси занадто малої довжини просто будуть неефективно використовуватись в тих запитах, де довжина рядка-константи, що фігурує в запиті, перевищує довжину індексу.

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

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

Оскільки СКБД MySQL для економії часу здійснює кешування результатів виконання запитів, то при кількаразовому виконанні одного і того ж запиту реальний пошук в таблиці здійснюється лише перший раз, потім результат запиту виводиться з кеша. Для того, щоб при багаторазовому виконанні одного і того ж запиту здійснювався реальний пошук в таблиці, слід перед виконанням запиту очистити кеш командою RESET QUERY CACHE (команди виконуються аналогічно до запитів, тобто у вікні SQL команд).

7.6. Розробка клієнтського WEB-додатку для перегляду даних

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

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

Веб-додаток – клієнт-серверний додаток, в якому клієнтом є браузер, а сервером – веб-сервер і сервер баз даних. Логіка веб-додатків розподілена між сервером і клієнтом, зберігання даних здійснюється, переважно, на сервері, обмін інформацією відбувається по мережі. Одним з переваг такого підходу є той факт, що клієнти не залежать від конкретної операційної системи користувача, тому веб-додатки є крос-платформеними сервісами. Для створення веб-додатків на стороні сервера використовуються різноманітні технології та будь-які мови програмування, здатні здійснювати виведення в стандартну консоль.

В даному курсі клієнтський додаток буде розроблятися засобами мов гіпертекстової розмітки HTML, PHP.

Структура інформації після установки пакета розробника «Denwer».

Припустимо, що папка «C:\WebServers», в яку був встановлений Denwer проектується на віртуальний диск S. Тоді в папці «S:\usr\local\mySQL-5.1\data\lab1\» буде знаходиться розроблена нами база даних. Для створення клієнтської програми необхідно увійти в папку «S:\home\localhost\www\" і створити в ній каталог Lab2.

Створюємо файл «S:\home\localhost\www\Lab2\index.html», в якому буде розміщено HTML-скрипт титульної сторінки проекту:

<HTML>

<HEAD>

<metaname="Author" content="М’якшило Олена Михайлівна">

</ HEAD>

<TITLE> Лабораторна робота № 1. З'єднання з базою даних </ title>

<BODY>

<AHREF="magazine.php"> Перелік студентів</ A>

</ BODY>

</ HTML>

Якщо в браузері ввести адресу «http://localhost/lab1/», побачимо посилання «Перелік студентів».

Завданням програми-додатку в даній лабораторній роботі є виведення таблиці. Для цього необхідно виконати наступні дії:

1) встановити з'єднання з базою даних;

2) виконати вибірку даних;

3) відобразити дані у вигляді таблиці.

Для установлення зв’язку MySQL із PHP знадобляться наступні функції:

1) створити з’єднання MySQL: intmySQL_connect (stringhostname, stringusername, stringpassword). Параметри: Hostname – ім'я хосту, на якому знаходиться база даних, Username – ім'я користувача, Password – пароль користувача. Функція повертає параметр типу int, що більше за 0, якщо з'єднання пройшло успішно, і дорівнює 0 у протилежному випадку.

2) вибрати базу даних для роботи: intmySQL_select_db (stringdatabase_name, intlink_identifier). Параметри: Database_name – ім'я бази даних; link_identifier – ID з’єднання, що відповідає функції mySQL_connect. (Параметр необов'язковий, якщо його не вказано, то використовується ID від останнього виклику mySQL_connect). Функція повертає значення true або false.

3) запит до бази даних: intmySQL_query (stringquery, intlink_identifier). Параметри: Query – Рядок запиту, link_identifier – див попередню функцію. Функція повертає ID результату або 0, якщо виникла помилка.

4) У циклі вибираємо записи, отримані в результаті запиту: arraymySQL_fetch_array (Resourceresult [, intresult_type]). Параметри: result – покажчик на ресурс результатів вибірки, другий (необов'язковий) – прапорець, який відповідає за вміст повертає його функцією асоціативного масиву. Функція повертає масив, відповідний витягнутому рядку, або FALSE, якщо рядків більше немає. Функція mySQL_fetch_array( ) – це розширена версія mySQL_fetch_row( ). Крім збереження даних в числових індексах результуючого масиву, вона також зберігає дані в асоціативних індексах, використовуючи як ключі імена полів.

5) закрити з’єднання intmySQL_close (intlink_identifier). Параметри: link_identifier – див вище. Функція повертає значення true або false.

Скрипт для видачі списку абонентів у вигляді таблиці, розташований у файлі «S:\home\localhost\www\Lab1\phlib.php» матиме вигляд:

<HTML>

<head>

<metaname="Author" content="М’якшило Олена Михайлівна">

<METANAME="Keywords" CONTENT="Лабораторна робота, MySQL, з'єднання з базою даних">

<METANAME="Description" CONTENT="Лабораторна робота №1. З'єднання з базою даних">

</ Head>

<title> Лабораторна робота. З'єднання з базою даних </ title>

<BODY>

/ * Змінні для з'єднання з базою даних * /

$ Hostname = "localhost";

$ Username = "student";

$ Password = "student";

$ DbName = "lab2";

/ * Таблиця MySQL, у якій зберігаються дані * /

$ Userstable = "student";

/ * Створити з’єднання * /

mySQL_connect ($ hostname, $ username, $ password) OR DIE ("Не можу створити з’єднання");

/ * Обрати базу даних. Якщо відбудеться помилка – вивести її * /

mySQL_select_db ($ dbName) ordie (mySQL_error ( ));

/ * Скласти запит для вибірки інформації * /

$ Query = "SELECT * FROM $ userstable";

/ * Виконати запит. * /

$ Result = mySQL_query ($ query) ordie (mySQL_error ( ));

echo "<TABLE BORDER=1>";

/ * Вибрати черговий запис з таблиці. * /

while ($ row = mySQL_fetch_array ($ result)) / / беремо

/ / Результати з кожного рядка

{

echo;

/ * Вивести її у вигляді HTML * /

echo "<tr><td>". $ row ['ID']. "</ td><td>". $ row ['FIO']. "</ td><td>". $ row [' bdate ']. "</ td></ tr>";

};

echo "</ TABLE>";

/ * Закрити з’єднання * /

mySQL_close ( );

</ BODY>

Результат роботи скрипту наведено на рисунку 2.20.

Рис. 7.20. Результат виконання скрипту вибірки з бази даних

7.7. Питання для самоперевірки

1. Що таке клієнтський додаток?

2. В чому полягають особливості WEB – додатку?

3. Що таке повне ім’я поля?

4. Що формується в результаті виконання оператора SELECT?

5. Поясніть приклади виконання фільтрації даних.

6. Наведіть приклади використання операторів IN, LIKE?

7. Поясніть і наведіть приклади використання інструкції для об'єднання таблиць.

8. Які агрегативні функції ви знаєте?

9. Для чого використовують оператор групування даних?

10. Як виконується упорядкування результатів запиту?

Лабораторна робота № 8

Організація маніпулювання даними в СКБД MYSQL

Мета роботи: Навчитись за допомогою Web-інтерфейсу phpMyAdmin маніпулювати даними, використовуючи оператори INSERT, UPDATE, DELETE і оператор SELECT для простих форм. Вивчення методів розробки клієнтських застосувань для введення і корекції даних.

Завдання:

1. Створити запити для маніпулювання даними у таблицях: додавання, редагування та видалення записів операторами INSERT, UPDATE, DELETE.

2. Розробити WEB-додаток на мові HTML з використанням скриптів php виконати управління даними у таблицях: редагування, додавання, видалення та перегляду результатів запитів:

a) створити кілька параметричних запитів для зміни вмісту будь-якого поля в таблиці.

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

c) виконати зміну останнього введеного запису таблиці.

Теоретичні відомості та приклад виконання роботи

8.1. Поняття про оператори маніпулювання даними

До операцій маніпулювання даними відносяться три операції: введення нового запису, видалення запису (записів), коригування запису (записів), виконувані операторами INSERT, DELETE та UPDATE, відповідно.

Всі оператори маніпулювання даними дозволяють змінювати дані тільки однієї таблиці.

Оператор введення даних має наступний синтаксис:

INSERT INTO ім‘я_таблиці [(<список стовпців>)] VALUES (<список значень>)

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

В набір даних також можуть бути введені функції та вирази. Єдиним обмеженням на ці функції та вирази є те, що вони повинні бути визначені на момент введення даних. Наприклад, до бібліотечного формуляра, заданого таблицею Exemplar (INV, ISBN, YES_NO, NUM_READER, DATE_IN, DATE_OUT) маємо внести дані про інвентарний номер екземпляру книжки, бібліотечний шифр, відмітку про наявність на полиці, номер читача, дату видачі та дату повернення книги [1]. Причому для введення поточної дати використаємо функцію Get Date(), яка автоматично зчитує системну дату, а для введення дати повернення функцію DateADD (d. GetDate(),14), яка з поточної дати виділяє день та додає до виділеного значення 14 днів і таким чином формує дату повернення книги. Запишемо команду для книги з інвентарним номером 1872 та відповідними значеннями інших даних:

INSERT INTO Exemplar (INV, ISBN, YES_NO, NUM_READER, DATE_IN, DATE_OUT)

VALUES ( 1872, ‘5-88782-290-2’, NO, 344, GetDate(),DateADD (d. GetDate(),14))

Оператор введення даних дозволяє ввести одночасно декілька рядків таблиці, якщо їх можна вибрати з іншої таблиці. Для цього слід використати підзапит, заданий оператором SELECT.

Наприклад, можна внести всіх студентів, як читачів бібліотеки, до таблиці READER одним оператором, вибравши дані про них з таблиці STUDENT:

INSERT INTO READER (NAME_READER, ADRESS, HOME_PHONE, BIRTH_DAY)

SELECT (NAME_STUDENT, ADRESS, HOME_PHONE, BIRTH_DAY)

FROM STUDENT

Оператор видалення даних дозволяє знищити один або декілька рядків таблиці у відповідності з умовами видалення . Синтаксис оператора DELETE наступний:

DELETE FROM ім‘я_таблиці [WHERE <умови_відбору>]

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

DELETE FROM Продаж_поточний.

Для видалення всіх замовлень від магазину “Фуршет” з таблиці ЗАМОВЛЕННЯ, яку ми розглядали в розділі 6.8, маємо написати вираз:

DELETE FROM ЗАМОВЛЕННЯ WERE [Замовник] =”Фуршет”

В частині WHERE може знаходитись вбудований запит. Наприклад , з бази даних заданої відношенням ЗАМОВНИК (код замовника, назва замовника, адреса, реквізити) та ЗАМОВЛЕННЯ (код замовлення, код замовника, дата замовлення, назва продукції, кількість, ціна, сума) видалити замовників, що замовляли продукцію не пізніше 31.12.2010 р. Текст оператора має наступний вигляд:

DELETE FROM ЗАМОВНИК

WERE [код замовника] IN (SELECT DISTINCT [код замовника]

FROM ЗАМОВНИК RIGHT JOIN ЗАМОВЛЕННЯ ON

ЗАМОВНИК.[ код замовника] = ЗАМОВЛЕННЯ.[код замовника]

WHERE Замовлення.[дата] < 31.12.2010)

В наведеному прикладі умова видалення замовника пов‘язана з терміном замовлення, тому для видалення використовується вкладений запит, який дозволяє вибрати замовлення, що надійшли до початку 2011 року і ,за рахунок зв‘язку між таблицями, визначити код замовника для вилучення. Оскільки замовлень у одного замовника може бути декілька, тобто, код замовника в результаті виконання підзапита може повторюватись декілька разів, використовуємо слово DISTINCT, щоб уникнути повторень. Для відображення зв‘язку між таблицями слугують слова RIGHT JOIN. Це вказує на те, що для кожного замовлення слід знайти відповідний запис у таблиці ЗАМОВНИК.

Для уникнення втрат даних на операцію DELETE можуть накладатися деякі обмеження. Взагалі, всі операції маніпулювання даними проводяться у відповідності з правилами збереження цілісності даних, але особливу увагу приділяють операціям вилучення даних та модифікації. В наведеному вище прикладі ми вилучили замовників з таблиці ЗАМОВНИК не зважаючи на те, що відповідні замовлення в таблиці ЗАМОВЛЕННЯ залишились. Для реальної бази даних така операція не припустима, тому що порушує цілістність даних, тобто логічну узгодженість даних. В такому випадку, в залежності від правила збереження цілісності, система запропонує спочатку вилучити дані з дочірньої таблиці, або автоматично вилучить ЗАМОВЛЕННЯ сама. Про засоби підтримки цілісності даних і правила збереження цілісності даних більш детально буде викладено в розділі 9. А зараз слід пам‘ятати, що не всі операції можуть бути виконані системою, хоча вираз написано правильно.

Операція оновлення використовується тоді коли маємо відкорегувати дані, що зберігаються в таблиці. Операція оновлення має наступний синтаксис:

UPDATE ім‘я_таблиці

SET ім‘я_стовпця = нове_значення

[WHERE умова_відбору ]

Частина WHERE не є обов‘язковою, як і в операторі DELETE. Вона дозволяє вибрати рядки, яких стосується операція модифікування. Якщо умову відбору не задано, то операцію модифікування буде застосовано до всіх рядків таблиці. В операторі UPDATE теж дозволено використання вкладених запитів. Наприклад: В базі даних заданій відношеннями СТУДЕНТ (код_студента, № зал_кн, ПІП, група) та ЗВЕДЕНА ВІДОМІСТЬ (код_екзамену, код_студента, дата, дисципліна, оцінка) замінити оцінку з дисципліни “Моделювання систем” студенту Бойко на “4”.

Оператор оновлення, що відповідає умові задачі, має вигляд:

UPDATE ЗВЕДЕНА ВІДОМІСТЬ

SET [оцінка]= “4”

WHERE [дисципліна] = “Моделювання систем” AND [код_студента] IN (SELECT [код_студента] FROM ЗВЕДЕНА ВІДОМІСТЬ JOIN СТУДЕНТ ON ЗВЕДЕНА ВІДОМІСТЬ. [код_студента] = СТУДЕНТ.[код_студента]

WHERE СТУДЕНТ.[ПІП] = “Бойко”).

8.2. Розробка додатку для модифікації даних

Модифікуємо клієнтський WEB-додаток. Для управління таблицею додамо після PHP-скрипта у файлі phlib. Php-форму, в якій буде три текстові поля (inputtype=”text”) для введення номера, Ф.І.О. і дати народження, а також три кнопки (inputtype=”submit”) – «Додати», «Редагувати» і «Видалити». Відзначимо, що при натисненні кнопок форма відправлятиме дані методом post скрипту, розташованому в тому ж самому файлі (action="phplib.php").

<tr><td>№</td><td>Ф.И.О.</td><td>Датарождения</td></tr>

<tr>

<td valign="top"><input name="my_id" type="text"

size="5" /></td>

<td valign="top"><input name="my_name" type="text"

size="50" /></td>

<td valign="top"><input name="my_data" type="text"

size="50" /></td>

</tr>

</td></tr>

</table>

</form>

Форма введення має вигляд, показаний на рисунку 2.4.

Рис.8.1. WEB-додаток модифікації даних таблиці

Розробимо обробник події по натисненню кнопок «Додати», «Змінити» і «Видалити». Вважатимемо, що поля редагування заповнені належним чином, і нам необхідно перенести з них інформацію в базу даних.

/* Обробка даних, отриманих методом post */

if ($_POST['add'])

{

echo "Виконаний запит: Додати ".$_POST['my_name']."

".$_POST['my_data']."<BR>";

$sql = mysql_query("INSERT into phonelib(FIO,bdate)

values ('".$_POST['my_name']."', '".$_POST['my_data']."');");

}

if ($_POST['delete'])

{

echo "Виконаний запит: Видалити ".$_POST['my_id']."<BR>";

$sql = mysql_query("DELETE from phonelib where ID=".$_POST['my_id']);

}

if ($_POST['update'])

{

echo "Виконаний запит: Змінити ".$_POST['my_id']. " на

".$_POST['my_name']." ".$_POST['my_data']."<BR>";

$sql = "UPDATE phonelib SET FIO='".$_POST['my_name']."',

bdate='".$_POST['my_data']."' where ID=".$_POST['my_id'];

echo $sql."<BR>";

mysql_query($sql) ORDIE ("Не можу виконати запит");

}

Розташовуємо описаний вище скрипт після оператора вибору бази даних

(mysql_select_db). Зразок результату роботи додатку наведено на рис.8.2.

Рис.8.2. Результат роботи додатку

8.3. Питання для самоперевірки

1. Які оператори відносяться до мови маніпулювання даними?

2. З якою частиною бази даних оперують оператори введення, оновлення та вилучення даних?

3. Як забезпечити введення даних з однієї таблиці до іншої?

4. Як забезпечити вилучення даних з таблиці, яка має зв‘язок з іншою таблицею?

5. Як можна використати вкладений запит в операторі оновлення даних?

6. Які змінні при створенні php-скрипту використовуються для під'єднання до бази даних?

7. Які команди мови SQL використовуються для створення бази даних, таблиць, введення даних?

8. Яка послідовність виведення на екран таблиці в php-скрипті?

Лабораторна робота № 9

Організація віддаленого доступу до сервера бази даних в MYSQL

Мета: Ознайомитися із використанням транзакцій в роботі з MySQL. Навчитися створювати і використовувати представлення (views).

Завдання:

1. Створити представлення даних (views) і реалізувати його на мові HTML з використанням скриптів php.

2. Створити транзакцію на основі існуючого запиту.

3. Створити паралельний запит.

4. Виконати запит з блокуванням таблиць.

Теоретичні відомості та приклад виконання роботи

9.1. Створення представлень

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

Нижче наведено можливі переваги при використанні представлень:

¾ Можливість багаторазового використання SQL запитів.

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

¾ Можливість надання доступу лише до частини інформації з таблиць (при адмініструванні).

¾ Можливість змінювати форматування і представлення даних.

¾ Представлення можуть повертати дані відформатовані і упорядковані по іншому, ніж в таблицях, що лежать в їх основі.

Правила використання представлень:

¾ імена представлень повинні бути унікальні, ім’я нового представлення не повинно співпадати з іменем іншого представлення, чи звичайної таблиці, які вже існують в даній БД;

¾ для створення представлень, користувач повинен мати відповідні права;

¾ представлення можуть бути вкладені – запит, що міститься в представленні може брати дані з іншого представлення;

¾ представлення не можуть мати індексів чи тригерів;

¾ представлення можуть використовуватися разом з таблицями, наприклад, можна створювати конструкцію SELECT, яка містить об’єднання (JOIN) представлення з таблицею.

¾ Представлення створюються за допомогою конструкції CREATE VIEW:

CREATE VIEW <назва_представлення> AS SELECT …

Щоб знищити представлення, використовується конструкція DROP. Синтаксис її простий:

DROP VIEW <назва_представлення>

Щоб змінити представлення, можна використати DROP, а потім знову CREATE. Або використати конструкцію CREATE OR REPLACE VIEW, яка створить представлення, якщо його ще немає, чи виконає заміну, якщо представлення з таким іменем вже існує.

Рис.9.1. Структура та вміст представлення постачальник_товар

9.2. Керування транзакціями

Транзакція – це сукупність однієї або декількох SQL-інструкцій, що має початок і кінець. Наприкінці транзакції відбувається або її скасування, або завершення.

Скасування транзакції називається відкатом (rollback), тому що відбувається послідовне скасування всіх зроблених змін. Завершення транзакції називається фіксацією (commit). Транзакції реалізуються шляхом ведення журналу всіх змін, внесених у базу даних у ході кожної транзакції. Коли відбувається відкат, СКБД звіряється з журналом і скасовує всі зміни. Завдяки журналу легко можна відновити погоджений стан бази даних у випадку збою. Транзакції з'явилися в MySQL порівняно недавно. Вони підтримуються для таблиць розширених типів, таких як InnoDB BerkeleyDB і Gemini. Варто відзначити, що в багатьох ситуаціях транзакції не потрібні, тому що табличних блокувань буває цілком достатньо. Internet-магазину потрібна така ж цілісність бази даних, як і в банку, тільки інтенсивність трафіку значно нижча. У цій ситуації можна користуватися табличними блокуваннями.

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

MySQL – конструкція, яка вказує на початок транзакції:

START TRANSACTION

MySQL – команда ROLLBACK дозволяє відмотати (відмінити) MySQL команди, так як в цьому фрагменті:

SELECT * FROM <назва_таблиці>;

START TRANSACTION;

DELETE FROM <назва_таблиці>;

SELECT * FROM <назва_таблиці>;

ROLLBACK;

SELECT * FROM <назва_таблиці>;

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

Очевидно, що ROLLBACK може бути використаний лише в транзакції, тобто після того як була виконана команда START TRANSACTION.

Не всі інструкції MySQL можуть бути відкатані назад. Транзакції використовуються для управління інструкціями INSERT, UPDATE та DELETE. Ви не можете відмінити SELECT, тому що ця команда не робить ніяких змін. Також ви не можете відкатити операції CREATE чи DROP. Дані інструкції можуть бути присутні в транзакції, але коли виконаєте ROLLBACK, то вони не будуть відмінені.

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

START TRANSACTION;

DELETE FROM goods WHERE FirmID = 1200155;

DELETE FROM firms WHERE FirmID = 1200155;

COMMIT;

Даний запит розпочинає транзакцію, знищує всі товари, що продає фірма з номером 1200155 з таблиці товарів, а потім знищує і саму фірму з таблиці firms, якщо дві функції DELETE виконались без помилок виконається команда COMMIT і зміни запишуться в обидві таблиці, якщо перший DELETE виконується успішно, а другий викликає помилку, тоді вся транзакція може буде відмінена (виконанням ROLLBACK) і ми будемо впевнені, що видалення фірми не виконалося тільки частково (товари знищились, а фірма залишилася).

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

9.3. Паралельні запити

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

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

В MySQL блокування реалізуються і без прямого втручання з боку користувачів. Одиночні запити виконуються в атомарному режимі, у якому кожний запит являє собою окрему транзакцію. Таким чином, описану вище проблему неузгодженого замовлення можна вирішити, об'єднавши кілька операцій в одному запиті, наприклад:

UPDATE item

SET Inventory=Inventory-30

WHERE ID=3

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

Оскільки MySQL не підтримує вкладені транзакції, то при виклику START TRANSACTION, якщо попередня транзакція не завершена вона автоматично підтверджується (виконується COMMIT). Тому при використанні механізму керування транзакціями об’єкта ADOConnection не слід явно виконувати команди MySQL керування транзакціями і навпаки.

9.4. Питання для самоперевірки

1. Що таке представлення?

2. Які види представлень ви знаєте?

3. Які правила використання представлень ви знаєте?

4. Що таке транзакція і в яких випадках використовується?

5. Що свідчить про закінчення транзакції?

6. Що означає точка відката і якою командою вона задається?

7. Поясніть як вирішується проблема паралельних запитів.

Лабораторна робота № 10

Резервування та відновлення бази даних засобами MYSQL

Мета: Навчитися створювати дамп (бекап) бази даних за різними параметрами з використанням серверного додатку MySQLdump.

Завдання:

1. Встановити серверний додаток MySQLdump для резервування даних.

2. Виконати імпорт та експорт однієї з таблиць з MSAccess.

3. Створити резервну копію бази даних MySQL на сервері із використанням MySQLdump за різними параметрами.

4. Виконати відновлення бази даних з створених дампів.

Теоретичні відомості та приклад виконання роботи

10.1. Встановлення MySQLdump

MySQLdump – це безкоштовний серверний додаток, який дозволяє робити резервне копіювання (далі дамп) баз даних і зберігати їх в окремому файлі. При цьому можна здійснювати гнучкі настроювання дампу: декілька або всі бази даних, архівація в gzip-архів, додавання команд lock, drop і т.д.. Також можливий зворотний імпорт резервних копій БД. Засоби phpmyadmin дозволяють здійснювати бекап бази даних і призначені для невеликих проектів, які мають малий об’єм даних. Програма mySQLdump зручна при реалізації експорту і імпорту даних з БД, стандартно встановлюється на mysql сервер хостингу, а також на denwer. Додаток mySQLdumpдозволяє отримати дамп вмісту бази даних або сукупності баз для створення резервної копії або пересилки даних на будь-який SQL-сервер. Дамп міститиме набір команд SQL для створення і/або заповнення таблиць. Так само mySQLdumpмає можливість розгортання баз даних із створеного SQL-файла.

Поряд