Тема: cтворення програмного проекта простої бази даних «Учні» з даними про прізвище, ім'я, успішність, забезпечення виведення даних про учнів за зазначиними критеріями.

Мета: навчити основам роботи з SQL.

Учні повинні знати:

• визначення та призначення баз даних;
• правила проектування та створення баз даних;
• правила фільтрування та пошуку інформації у баз даних;
• особливості налаштування та принцип роботи MySQL.

Учні повинні мати уявлення про можливості MySQL.

Учні повинні вміти:

• створювати нові бази данних та таблиці;
• створювати, редагувати, виконувати операції з об'єктами БД.

Обладнання: комп'ютери зі встановаленими ОС і одним з таких пакунків: або MySQL, або XAMPP або LAMP.

Примітка. При одночасному встановленні пакунків можлива некоректна робота щонайменше одного з них. Саме тому використано виключне "або". Для уникнення цього можна використати віртуальну машину, на якій встановити ОС і MySQL. При використанні XAMPP або LAMP матеріал цього уроку призначено лише для теоретичного опрацювання.

Структура уроку

1. Організаційний момент.
2. Актуалізація опорних знань.
3. Вивчення нового матеріалу.
4. Інструктаж з ТБ.
5. Закріплення вивченого матеріалу.
6. Підбиття підсумків уроку.
7. Домашнє завдання.

Хід уроку

1. Організаційний момент
Вітання з класом. Перевірка присутності і готовності учнів до уроку.

2. Актуалізація опорних знань
Дати тлумачення термінам, виділених жирним написанням, і порівняти з очікуваним.

База даних (БД) — це структурована сукупність даних, які відображають стан об'єктів певної предметної області та зв'язки між ними.

Предметна область (бази даних) — це сфера застосування конкретної бази даних.

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

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

Модель даних — це система правил, згідно з якими створюють структуру даних, здійснюють доступ до даних та змінюють їх.

Основні типи баз даних:

Реляційна модель (латиною rela­tio — відношення) побудована на взаємовідношенні частин, що її складають. У найпростішому випадку реляційна модель є двовимірним масивом (таблицею), а при створенні складних інформаційних моделей — сукупністю взаємопов’язаних таблиць, кожна з яких містить набір однотипних об'єктів.

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

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

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

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

Запит — вказівка системи управління базами даних (СУБД), результатом якої є вибірка даних, дія з ними або структурою бази даних чи її складових.

3. Вивчення нового матеріалу

Мова SQL (Structured Query Language) — мова структурованих запитів, призначена для керування даними в системі реляційних баз даних.

Крапка з комою — cтандартний показник завершення вказівки SQL.

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

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

Робота з базами даних

• SHOW DATABASES; — перегляд досяжних баз даних;
• CREATE DATABASE; — створення нової бази даних;
• USE назва_БД; — вибір бази даних для використання;
• SOURCE шлях_до_файлу.sql; — імпорт вказівок SQL з файлу;
• DROP DATABASE назва_БД; — видалення бази даних;

• SHOW VARIABLES; — перегляд списку змінних середовища MySQL і їхніх значень з INFORMATION_SCHEMA — службової бази даних про всі бази, підключені до сервера. Серед переліку змінних є datadir тека зі встановленим MySQL, куди й буде записано всі створені бази даних. Для виведення значення частини цих змінних їхні назви вказують у переліку полів або виразів запиту SELECT після символів @@. Наприклад,
  SELECT @@basedir;

Робота з таблицями

• SHOW TABLES; — перегляд таблиць, доступних в базі даних;

• CREATE TABLE назва_таблиці (
    назва_поля_1 тип_даних_поля_1,
    назва_поля_2 тип_даних_поля_2, …
    PRIMARY KEY (назва_поля_первинного_ключа),
    FOREIGN KEY (назва_поля_зовнішнього_ключа) REFERENCES назва_основної_таблиці (назва_поля_основної_таблиці)
  );

  — cтворення нової таблиці. При створенні таблиці можна задати таке:

  • комірка таблиці не може мати значення NULL — достатньо після опису типу даних дописати NOT NULL;

  • дані комірки мають задовольняти певну умову — достатньо після опису типу даних дописати check умова;

  • можна утворити складений первинний ключ
    PRIMARY KEY (назва_поля_1, назва_поля_2, …);

  • зовнішній ключ задати таким чином
    FOREIGN KEY (назва_поля_1, …, назва_поля_n) REFERENCES назва_БД_2 (назва_поля_1, …, назва_поля_n);

  • вказати ON DELETE CASCADE, щоб отримати таке: при видаленні рядка батьківської таблиці автоматично видалити з дочірньою таблиці рядок, значення зовнішнього ключа якого дорівнює значенню довідкового ключа рядка батьківської таблиці;

  • вказати ON DELETE SET NULL, щоб отримати таке: при видаленні рядка батьківської таблиці у відповідних рядках і у стовпчиках зовнішнього ключа буде надано значення NULL.

• DESCRIBE назва_таблиці;
  — отримати дані про стовпчики таблиці (тип значень, чи є ключем);

• INSERT INTO назва_таблиці (назва_поля_1, назва_поля_2, назва_поля_3, …)
    VALUES (значення_1, значення_2, значення_3, …);

  — додавання даних у таблицю. При додаванні даних у кожний стовпчик таблиці не потрібно вказувати назви стовпчиків. У цьому випадку прийнятний такий синтаксис:

  INSERT INTO назва_таблиці
    VALUES (значення_1, значення_2, значення_3, …);

• UPDATE назва_таблиці
    SET (назва_поля_1  = значення_1, назва_поля_2  = значення_2, …
    WHERE умова;

  — оновлення (зміна) даних таблиці;

• DROP TABLE назва_таблиці; — видалення таблиці,

• DELETE FROM табличний_вираз WHERE умова; — видалення записів таблиці, що задовольняють певну умову, і повернення кількості видалених. Якщо умову не вказано, то вважають, що вона тотожно істинна. Тобто у цьому випадку буде видалено усі записи таблиці, але повернуто 0.
  
  Припустиме використання ORDER BY і LIMIT, наприклад, при видаленні певної кількості рядків у певному порядку.
  
  Якщо після службового слова DELETE записано службове слове LOW_PRIORITY, виконання вказівки DELETE буде затримано до тих пір, поки інші клієнти не завершать зчитування таблиці.
  
  Якщо після службового слова DELETE записано службове слове QUICK, то при опрацюванні таблиці індекси не буде об'єднано, що у деяких випадках прискорює виконання операції.

Робота з індексами

• CREATE UNIQUE INDEX назва індексу
    ON назва таблиці (
      назва_поля_1,
      назва_поля_2, …
    ); — створення індексу. Після назви поля при потребі можна вказати порядок спадання DESC. Якщо індекс не унікальний, слово UNIQUE потрібно вилучити;.

• DROP INDEX назва індексу
    ON назва таблиці; — видалення індексу.

Cтворення запитів на вибірку за допомогою вказівки SELECT у найпростішій формі має такий вигляд:

SELECT перелік полів або виразів
  FROM табличний вираз;

У переліку полів або виразів кожне поле можна записати в одній з двох форм:

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

Для правильного і зручного відображення виразів використовують інструкцію AS встановлення псевдоніму (того, що буде заголовком стовпчика результату запиту) з таким синтаксисом:

вираз AS псевдонім

Її записують у переліку полів або виразів.

Форми запитів у часткових випадках:

• SELECT назва_поля_1, назва_поля_2, …
    FROM назва_таблиці_1; — з деяких стовпчиків таблиці;

• SELECT * FROM назва_таблиці_1; — з усіх стовпчиків таблиці;

• SELECT DISTINCT назва_поля_1, назва_поля_2, …
    FROM назва_таблиці_1; — неповторювані дані з деяких стовпчиків таблиці.

Додаткові параметри запиту записують перед крапкою з комою:

• WHERE умови — згідно з умовами, що містять порівняння й логічні операції:
  • AND — логічне «і»;
  • OR — логічне «або»;
  • NOT — «заперечення»;

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

  • TRUE — істина;
  • FALSE — хибність;
  • NULL — невизначеність.

  Якщо хоча б одна із складових логічного виразу невизначена (її значення NULL), його значенням також невизначене — NULL. Для встановлення невизначеності виразу використовують оператор, що повертає лише TRUE або FALSE:
  
  вираз IS NULL

• WHERE назва_поля BETWEEN значення_1 AND значення_2 — вибір значень даних з певного проміжку;

• WHERE назва_поля LIKE шаблон — пошук схожого значення за шаблоном, у записі якого використовують такі позначення:
      %  — довільна кількість будь-яких символів;
      _   або  ? — довільний символ;
      […] — довільний символ з переліку, записаного на місці трикрапки;
      [!…] — довільний символ, якого немає серед записаних на місці три­крапки;

• WHERE назва_поля IN (значення_1, значення_2, …) — пошук за значенням з множини значень, заданої переліком елементів;

• GROUP BY перелік_полів_або_виразів — групування вихідних значень;

• HAVING умови — згідно з умовами, що містять агрегатні функції (службове слово HAVING було додано в SQL саме тому, що WHERE не можна використати для роботи з агрегатними функціями);

• ORDER BY назва_поля_1, назва_поля_2, … ASC | DESC — упорядкування результатів запиту за зростанням (як усталено) чи спаданням;

• LIMIT величина_зсуву, кількість_записів — обмеження кількості запитів і встанов­лення величини зсуву від початку у послідовності даних виведення. Такий запис працює в MySQL. В Microsoft Access відповідну вказівку TOP потрібно використовувати з іншим чином:

  SELECT DISTINCT TOP кількість_записів перелік полів або виразів
    FROM табличний_вираз
    ORDER BY перелік полів або виразів

  Тут перед упорядкуванням (ORDERED BY) можна використати вказівки WHERE, HAVING, GROUP BY. Зсув при обмеженні кількості запитів вбудованими засобами Microsoft Access не передбачено.

Агрегатна функція — функція множини значень, що повертає одне агреговане (узагальнене) значення.

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

Перелічимо основні агреговані функції, що є спільними для більшості СУБД:

• COUNT — кількість непорожніх значень сукупності;
• SUM — суму непорожніх значень сукупності;
• AVG — середнє значення непорожніх значень сукупності;
• MIN — найменше значення непорожніх значень сукупності;
• MAX — найбільше значення непорожніх значень сукупності.

Тут «непорожніх» означає «не NULL».

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

Підзапити у коді виділяють дужками.

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

• підзапит-значення — виведення усіх записів таблиці, у яких значення у певному полі досягає найменшого, найбільшого чи середнього значення;

• підзапит-стовпчик — виведення усіх записів таблиці, що задовольняють деяку умову порівняння з елементами отриманого стовпчика, записану з поєднанням:

  • оператора порівняння =, >, <, >=, <=, <>;
  • одного зі службових слів: ALL, ANY, SOME, IN, NOT IN

  з таким тлумаченням записів:

  • вираз оператор_порівняння ALL / ANY / SOME підзапит-стовпчик — справджу­ється, якщо справджується довільне / хоча б одне / хоча б одне спів­відно­шення порівняння між виразом і елементом підзапиту-стовпчика;

  • вираз IN / NOT IN підзапит-стовпчик — справджується, якщо значення виразу є / немає серед значень елементів підзапиту-стовпчика;

• підзапит-таблиця — використовують у виразах блоку FROM або умовах блоків WHERE та HAVING. При використанні підзапиту-таблиці в об'єднанні їй необхідно надати псевдонім за допомогою службового слова AS.

Табличні підзапити можуть бути аргументами:

• операторів об'єднання (записувати між аргументами):

  • UNION — повертає об'єднання без повторень рядків двох таблиць з однаковою кількістю полів сумісних типів;

  • UNION ALL — повертає об'єднання усіх рядків двох таблиць з однаковою кількістю полів сумісних типів;

• операторів існування (записувати перед аргументом):

  • EXISTS — повертає TRUE (істина), якщо підзапит містить хоча б один запис, інакше повертає FALSE;

  • NOT EXISTS — повертає TRUE (істина), якщо підзапит не містить жодного запису, інакше повертає FALSE.

• LEFT OUTER JOIN — ліве — включає до свого результату всі записи результату анало­гіч­ного INNER JOINT і ті записи аргумента, записаного ліворуч, для яких жодного разу не справдилася умова;

• RIGHT OUTER JOIN — праве — включає до свого результату всі записи результату анало­гіч­ного INNER JOINT і ті записи аргумента, записаного праворуч, для яких жодного разу не справдилася умова;

• FULL OUTER JOIN — повне — відсутнє у більшості СУБД — включає до свого результату всі записи результату аналогічного INNER JOINT і ті записи обох аргументів, для яких жодного разу не справдилася умова.

Синтаксис зовнішнього об'єднання — такий самий, як і внутрішнього.

View (перегляд) — це віртуальна таблиця SQL, створена в результаті виконання виразу. Вона містить рядки і стовпці і дуже схожа на звичайну SQL-таблицю. View завжди показує найсвіжішу інформацію з бази даних. Її створюють такою вказівкою:

CREATE VIEW назва_перегляду AS
  SELECT назва_поля_1, назва_поля_2, …
  FROM назва_таблиці_1
  WHERE умови;

Видалення віртуальної таблиці SQL здійснюють такою вказівкою:

DROP VIEW назва_перегляду;

— система керування реляційними базами даних, що використовує SQL.

Її розробила компанія «ТсХ» для підвищення швидкодії обробки великих баз даних. Зараз MySQL — одна з найпоширеніших систем керування базами даних. Вона має підтримку з боку різноманітних мов програмування. Її використовують, у першу чергу, для створення динамічних веб-сторінок. Саме її буде використано у процесі практичної роботи на уроці.

MySQL має подвійне ліцензування. MySQL зазвичай розповсюджують відповідно до умов ліцензії GNU GPL.

GNU General Public License (загальна публічна ліцензія GNU) — ліцензія на вільне програмне забезпечення проекту GNU.

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

4. Інструктаж з ТБ
5. Закріплення вивченого матеріалу

Завдання 1. Записати у файл structure.sql вказівки SQL для створення таблиць заданої структури (після назви кожної таблиці, виділеної жирним написанням, подано перелік трійок: назва поля — тип даних — тлумачення):

• pupils:
  • sename — varchar(15) — прізвище;
  • name — varchar(15) — ім'я;
  • form — varchar(3) — клас;
  • no — varchar(9) — номер особової справи, первинний ключ;
• classroom:
  • building — varchar(15) — корпус;
  • room_number — varchar(3) — номер аудиторії;
  • capacity — tinyint unsigned — кількість місць;
  • building, room_number — первинний ключ;
• department:
  • dept_name — varchar(20) — назва кафедри — первинний ключ;
  • persons — tinyint unsigned — check (persons > 0) — кількість ставок ;
  • dept_name;
• courses:
  • title — varchar(15) — назва дисципліни;
  • form — varchar(3) — клас;
  • no — varchar(9) — № особової справи вчителя;
  • hours — smallint unsigned — кількість навчальних годин на тиждень;
  • dept_name — varchar(15) — відповідальна кафедра;
  • title, form — первинний ключ;
  • зовнішній ключ no до таблиці teachers;
    з наданням значення NULL при видаленні рядка батьківської таблиці;
• teachers:
  • sename — varchar(15) — прізвище;
  • name — varchar(15) — ім'я;
  • quality — varchar(20) — кваліфікаційна категорія;
  • no — varchar(9) — номер особової справи, первинний ключ;
• marks:
  • course — varchar(15) — назва дисципліни;
  • form — varchar(3) — клас;
  • sename — varchar(15) — прізвище;
  • name — varchar(15) — ім'я;
  • day — date — день;
  • mark — int(2) — оцінка з діапазону 1-12;
  • type — ENUM ('контрольна','самостійна','усна','домашня') — тип роботи;
  • course, form, sename, name, day, type — первинний ключ.

1. Увійти в консоль MySQL за допомогою вказівки терміналу:
   
   mysql -u root -p
   
   Примітка.

   • Якщо працюють з MySQL під обліковим записом адміністратора ОС Linux, на початку вказівки потрібно писати службове слово sudo.

   • Якщо працюють з MariaDB пакунку XAMPP, то використовують таку вказівку терміналу:
     
     /opt/lampp/bin/mysql -u root -p

2. Отримати повідомлення — таке або схоже на таке:

   Welcome to the MySQL monitor.  Commands end with ; or \g.
   Your MySQL connection id is 31
   Server version: 8.0.21-0ubuntu0.20.04.4 (Ubuntu)
   
   Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
   
   Oracle is a registered trademark of Oracle Corporation and/or its
   affiliates. Other names may be trademarks of their respective
   owners.
   
   Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

3. Створити базу даних school такими вказівками консолі MySQL:

   SET collation_connection = 'utf8_unicode_ci';
   CREATE DATABASE school;
   ALTER DATABASE school  CHARACTER SET utf8 COLLATE utf8_unicode_ci;
   USE school;
   SOURCE structure.sql;
   SOURCE data.sql;

   Файли structure.sql і data.sql потрібно розташувати у поточній теці. Або в останніх двох вказівках доповнити назви шляхом до фалів з використанням звичайної похилої риски /.
   
   Виконання двох останніх вказівок цього кроку призведе до виведення таких повідомлень у вікні Терміналу (зірочками позначено цифри):
   
   Query OK, * row affected (*.** sec)
   
   У разі отримання повідомлення про помилки:

   • видалити БД school вказівкою консолі MySQL:
     DROP DATABASE school;
   • виправити виявлену помилку:
   • здійснити даний крок знову.

4. Пересвідчитися в успішності створення бази даних. Виконати вказівки консолі MySQL:
   
   SHOW DATABASES;
   SHOW TABLES;
   
   і отримати два повідомлення. Перше повідомлення повинно містити повідомлення про базу даних school. Щодо решти баз даних інформація залежить від результатів попоредньої роботи з MySQL.

5. Пересвідчитися в успішності створення структури бази даних . Виконати 6 вказівок консолі MySQL такого вигляду:
   
   DESCRIBE *;
   
   Замість зірочки * потрібно вказати одну зі створених таблиць: classroom, courses, department, marks, pupils, teachers. Отримати повідомлення з описом таблиць.

Завдання 4. Знайти місце розташування створеної бази даних.

1. Виконати вказівку консолі MySQL SHOW VARIABLES;
2. Встановити значення змінної datadir.
3. Виконати вказівку консолі MySQL SELECT @@datadir;
4. Отримати схоже на таке:

   mysql> SELECT @@datadir;
   +-----------------+
   | @@datadir       |
   +-----------------+
   | /var/lib/mysql/ |
   +-----------------+
   1 row in set (0.00 sec)

5. Пересвідчитися, що отримані значення datadir збігаються.
6. Відкрити теку розташування баз MySQL як адміністратор.
   
   Для цього знадобиться підтвердити права адміністратора,
   
   увівши пароль і натиснувши кнопку Розпізнавання.
7. Пересвідчитися у наявності файлів баз даних.
   
   
   

Завдання 5. Записати у файл 5.sql 14 запитів на отримання таких даних:

• всі поля з таблиці pupils про учнів 11Б класу (поле form);

• всі поля з таблиці classroom про аудиторії корпусу A (поле building, латиниця), ємність яких (поле capacity) менше 24 і більше від 16 (2 варіанти);

• з таблиці pupils про прізвище (поле sename) та ім'я (поле name) усіх тих записів, у яких прізвище закінчується на 'УК';

• з таблиці pupils про прізвище (поле sename) та ім'я (поле name) усіх тих записів, у яких ім'я (поле name) 'Андрій' або 'Григорій';

• з таблиці pupils про загальну кількість учнів;

• з таблиці teachers про категорії (поле quality) та кількість учителів цієї категорії;

• з таблиці teachers про категорії (поле quality) та кількість учителів цієї категорії, що перевищує 1;

• з таблиці marks про прізвище (поле sename) й оцінку (поле mark) за день 2020-09-24 у порядку зростання оцінки;

• з таблиці marks про прізвище (поле sename) й оцінку (поле mark) за день 2020-09-24 у порядку спадання оцінки;

• з таблиці marks про прізвище (поле sename) й оцінку (поле mark) за день 2020-09-24 у порядку спадання оцінки лише тих, хто посів 4, 5 і 6 місце у цьому списку з підписами стовпчиків 'Прізвище' й 'Оцінка';

• з таблиці courses список всіх дисциплін (поле course) і відповідну інформацію про кафедри з таблиці department у режимах;

  • JOIN;
  • LEFT OUTER JOIN;
  • RIGHT OUTER JOIN.

Виконати запити й порівняти з очікуваними кодом і результатом.

Завдання 6. Записати у файл 6.sql вказівки SQL для такого:

• створити віртуальну таблицю own_view усіх полів усіх записів таблиці marks, у яких оцінка (поле mark) менша ніж 7;

• переглянути own_view.

Виконати запити й порівняти з очікуваними кодом і результатом.

Завдання 7. Записати у файл 7.sql 6 вказівок SQL для такого:

• вивести дані таблиці teachers в алфавітному порядку за полем sename;
• видалити з таблиці teachers рядок зі значенням 'Фесенко' поле sename;
• вивести дані таблиці teachers в алфавітному порядку за прізвищем;
• видалити перші 4 (у вказаному вище порядку) записи таблиці teachers;
• вивести дані таблиці teachers в алфавітному порядку за прізвищем;
• вивести дані таблиці teachers у порядку запису у файлі.

Виконати запити й порівняти з очікуваними кодом і результатом.

6. Підбиття підсумків уроку

Дати відповідь на питання і порівняти з очікуваним.

1. Що таке SQL?
2. Яка ознака кінця вказівки SQL?
3. Які вказівки SQL створюють базу даних, таблицю, запит?
4. Що таке агрегатна функція SQL? Назвати приклади
5. Що таке внутрішнє і зовнішнє об'єднання?
6. Що таке MySQL?

Виставлення оцінок.

7. Домашнє завдання
У разі потреби доробити завдання. Повторити основні вказівки SQL.

Тема: використання коду SQL у середовищі phpMyAdmin збірки XAMPP.

Мета: навчити основам роботи у середовищі phpMyAdmin.

Учні повинні знати:

• джерело і спосіб встановлення XAMPP;
• спосіб використання SQL у середовищі phpMyAdmin.

Учні повинні мати уявлення про можливості XAMPP.

Учні повинні вміти: використовувати вказівки SQL у середовищі phpMyAdmin.

Обладнання: комп'ютери зі встановаленими ОС і XAMPP.

Структура уроку

1. Організаційний момент.
2. Актуалізація опорних знань.
3. Вивчення нового матеріалу.
4. Інструктаж з ТБ.
5. Закріплення вивченого матеріалу.
6. Підбиття підсумків уроку.
7. Домашнє завдання.

Хід уроку

1. Організаційний момент
Вітання з класом. Перевірка присутності і готовності учнів до уроку.

2. Актуалізація опорних знань

Дати відповідь на питання і порівняти з очікуваним.

1. Що таке SQL?
2. Яка ознака кінця вказівки SQL?
3. Які вказівки SQL створюють базу даних, таблицю, запит?
4. Що таке агрегатна функція SQL? Назвати приклади
5. Що таке внутрішнє і зовнішнє об'єднання?
6. Що таке MySQL?

3. Вивчення нового матеріалу

XAMPP — вільно поширювана багатоплатформова збірка веб-сервера, що містить HTTP-сервер Apache, базу даних MariaDB, MySQL й інтерпретатори скриптів для мов програмування PHP та Perl, а також додаткові бібліотеки, що дозволяють запустити повноцінний веб-сервер.

Сучасні версії XAMPP призначені лише для 64-бітної адресації пам'яті. При використання 32-бітної адресації використовують функціонально еквівалентний набір LAMP.

sudo /opt/lampp/lampp security
[sudo] пароль до *****:           
XAMPP:  Quick security check...
XAMPP:  MySQL is not accessable via network. Good.
XAMPP:  The MySQL/phpMyAdmin user pma has no password set!!! 
XAMPP: Do you want to set a password? [yes] yes
XAMPP: Password: 
XAMPP: Password (again): 
XAMPP:  Setting new MySQL pma password.
XAMPP:  Setting phpMyAdmin's pma password to the new one.
XAMPP:  MySQL has no root passwort set!!! 
XAMPP: Do you want to set a password? [yes] yes
XAMPP:  Write the password somewhere down to make sure you won't forget it!!! 
XAMPP: Password: 
XAMPP: Password (again): 
XAMPP:  Setting new MySQL root password.
XAMPP:  Change phpMyAdmin's authentication method.
XAMPP:  ProFTPD has a new FTP password. Great!
XAMPP: Do you want to change the password anyway? [no] yes
XAMPP: Password: 
XAMPP: Password (again): 
XAMPP: Reload ProFTPD...ok.
XAMPP:  Done.

Алгоритм підготовки ПК до використання XAMPP і SQL

1. Встановити XAMPP, дотримуючись вказівок офіційного сайту розробників.

2. Запустити XAMPP, дотримуючись вказівок офіційного сайту розробників. Для ОС Ubuntu/Linux Mint це означає виконати такі вказівки Терміналу:
   
   cd /opt/lampp
   sudo ./manager-linux-x64.run
   
   і отримати таке вікно діалогу.

   

3. Перейти на вкладення Manage Servers (керування серверами).

   

4. Якщо хоча б один з трьох серверів неактивний (червоним кругом позначено зупинку, жовтим — процес запуску), вибрати зупинений сервер, клацнувши на ньому лівою кнопкою миші, і натиснути кнопку з написом Start (Запустити). Якщо всі три сервери неактивні, їх можна запустити натисканням кнопки з написом Start All (Запустити всі). Дочекатися ознак працездатності усіх трьох серверів позначення зеленим кругом.

   

5. У вікні браузера перейти за посиланням http://localhost і пересвідчитися в успішному встановленні й працездатності ПЗ, отримавши вікно такого вигляду.

   

6. Перейти за посиланням phpMyAdmin у правому верхньому куті вікна.

7. Якщо у ході виконання вказівки:
   
   sudo /opt/lampp/lampp security
   
   були запроваджено пароль root (про це має повідомити учитель чи системний адміністратор), його потрібно буде ввести і натиснути кнопку Виконати для входу в систему.

   

8. Отримати таке вікно.

   

9. Перейти на вкладення SQL.

   

Таким чином, отримано можливість вводити код SQL і посилати його на виконання натисканням кнопки з написом Виконати.

Для роботи у консолі з MariaDB при втановленій збірці XAMPP при ОС Linux Mint / Ubuntu потрібно використати вказівку Терміналу:

/opt/lampp/bin/mysql -u root -p

4. Інструктаж з ТБ
5. Закріплення вивченого матеріалу

Виконаємо завдання 3, 4, 5, 7 попередньої практичної роботи в середовищі phpMyAdmin.

Завдання 3'. Налаштувати базу даних для подальшої роботи з MySQL.

1. Завантажити середовище phpMyAdmin і перейти на вкладення SQL.

2. Створити базу даних school і вказати на її використання вказівками SQL:

   CREATE DATABASE school;

   Після введення вказівок натиснути клавішу з написом Виконати

   

   і помітити появу бази даних у переліку наявних БД.

   

3. Задати структуру і наповнити базу даних school, завантаживши файли structure.sql і data.sql. Для цього потрібно для кожного з файлів:

   • у лівому полі вибрати базу даних school;
   • перейти на вкладення Імпорт;
   • натиснути кнопку з написом Огляд;
   • у вікні діалогу вибрати файл;
   • натиснути кнопку з написом Виконати;
   
   
   
   • пересвідчитися в успішному виконанні завантажених вказівок SQL;
   
   
   
   

4. Пересвідчитися в успішності створення структури бази даних . При виділеній базі даних school виконати 6 вказівок SQL на такого вигляду:
   
   DESCRIBE *;
   
   Замість зірочки * потрібно вказати одну зі створених таблиць: classroom, courses, department, marks, pupils, teachers. Отримати такі повідомлення (показано лише для таблиці classroom).

   

5. Пересвідчитися в успішності заповнення даними. Клацнути лівою кнопкою миші на кожній з наявних таблиць базі даних school і переглянути їхній вміст (показано лише для таблиці classroom). Помітити відображення вказівки SQL створення запиту SELECT * FROM …

Завдання 4'. Знайти місце розташування створеної бази даних.

1. Виконати вказівку SQL SELECT @@datadir;
2. Отримати схоже на таке:
   

3. Відкрити теку розташування баз MySQL і виявити у ній створену базу даних school.

   

4. Відкрити теку school як адміністратор.

   

   Для цього знадобиться підтвердити права адміністратора,

   
   

   увівши пароль і натиснувши кнопку Розпізнавання.

5. Виявити файли з таблицями бази даних school, що мають з ними однакові назви.

   

Завдання 5'. Використовуючи записані у файл вказівки SQL, виконати запити для отримання таких даних:

• всі поля з таблиці pupils про учнів 11Б класу (поле form);

  

• всі поля з таблиці classroom про аудиторії корпусу A (поле building, латиниця), ємність яких (поле capacity) менше 24 і більше від 16 (2 варіанти);

  
  
  

• з таблиці pupils про прізвище (поле sename) та ім'я (поле name) усіх тих записів, у яких прізвище закінчується на 'УК';

  

• з таблиці pupils про прізвище (поле sename) та ім'я (поле name) усіх тих записів, у яких ім'я (поле name) 'Андрій' або 'Григорій';

  

• з таблиці pupils про загальну кількість учнів;

  

• з таблиці teachers про категорії (поле quality) та кількість учителів цієї категорії;

  

• з таблиці teachers про категорії (поле quality) та кількість учителів цієї категорії, що перевищує 1;

  

• з таблиці marks про прізвище (поле sename) й оцінку (поле mark) за день 2020-09-24 у порядку зростання оцінки;

  

• з таблиці marks про прізвище (поле sename) й оцінку (поле mark) за день 2020-09-24 у порядку спадання оцінки;

  

• з таблиці marks про прізвище (поле sename) й оцінку (поле mark) за день 2020-09-24 у порядку спадання оцінки лише тих, хто посів 4, 5 і 6 місце у цьому списку з підписами стовпчиків 'Прізвище' й 'Оцінка';

  

• з таблиці courses список всіх дисциплін (поле course) і відповідну інформацію про кафедри з таблиці department у режимах;

  • JOIN;
  • LEFT OUTER JOIN;
  • RIGHT OUTER JOIN.

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

Завдання 7'. Використовуючи веб-інтерфейс phpMyAdmin, виконати такі запити.

• Ввивести дані таблиці teachers в алфавітному порядку за полем sename.

  
  
  

  Зверніть увагу на упорядкування за кодами символів, що суперечить алфавітному порядку для української абетки. Такої невідповідності не спостерігали для MySQL — див. матеріали попереднього уроку.

• Видалити з таблиці teachers рядок зі значенням 'Фесенко' поле sename;

  

• Видалити перші 4 (у вказаному вище порядку) записи таблиці teachers вказівкою SQL:
  
  DELETE FROM teachers ORDER BY sename LIMIT 4;

  

• Вивести дані таблиці teachers в алфавітному порядку за прізвищем.

  
  
  

• Вивести дані таблиці teachers у порядку запису у файлі.

  

6. Підбиття підсумків уроку

Дати відповідь на питання і порівняти з очікуваним.

1. Що таке XAMPP?
2. Що таке LAMP?
3. Що таке MariaDB?
4. Де ви побачили відмінність у роботі MySQL і XAMPP?

Виставлення оцінок.

7. Домашнє завдання
У разі потреби доробити завдання. Повторити основні вказівки SQL. Підготуватися до виконання самостійної практичної роботи.

Текст упорядкував Олександр Рудик з використанням матеріалів публікації SQL Recap for Interviews.