Розробка 1 уроку — практичної роботи

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

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

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

Учні повинні мати уявлення про можливості 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. На розсуд учителя це знайомство можна здійснити під час виконання завдань, записаних у порядку вивчення відповідних частин теоретичного матеріалу за виключенням того, видалення записів розглянуто останнім.

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

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

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

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

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


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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Внутрішнє об'єднання INNER JOIN (службове слово INNER не є обов'язковим) має синтаксис:

SELECT перелік полів або виразів
  FROM табличний_вираз_1 INNER JOIN табличний_вираз_2
  ON умова


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

SELECT перелік полів або виразів
  FROM табличний_вираз_1, табличний_вираз_2
  WHERE умова


Зовнішнє об'єднання має 3 різновиди:

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

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 для створення таблиць заданої структури (після назви кожної таблиці, виділеної жирним написанням, подано перелік трійок: назва поля — тип даних — тлумачення):

Порівняти з очікуваним.

Примітка (про те, що не знали і що потрібно запам'ятати). Наявність в останньому рядку опису таблиці marks у демонстраціному розв'язанні доповнення

engine=InnoDB default charset=utf8

дозволяє уникнути помилки

ERROR 1291 (HY000): Column 'type' has duplicated value '??????????' in ENUM

що виникає внаcлідок використання в описі типу ENUM літер, яких немає у латиниці.

Дія всіх вказівок з CHARACTER SET utf8 COLLATE utf8_unicode_ci спрямована на уникнення винятків, породжених конфліктами різних схем упорядкування й порівняння.

Завдання 2. Ознайомитися зі структурою файлу data.sql очищення й наповнення бази даних.

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

  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 запитів на отримання таких даних:

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

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

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

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

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

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

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

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

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

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


Розробка 2 уроку — практичної роботи

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

Мета: навчити основам роботи у середовищі 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.

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

Виправлення прогалин у безпеці при ОС Ubuntu / Linux Mint можна здійснити за допомогою вказівки Терміналу:

sudo /opt/lampp/lampp security

Під час перевірку безпеки користувач відповідає на запитання і залежно від відповіді налаштування буде змінено або ні.

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.


  • MariaDB®система керування базами даних з відкритим вихідним кодом, яку використовують замість MySQL у складі популярного набору XAMPP, починаючи з XAMPP 5.5.30.

    MariaDB створено на початку 2009 року як відгалуження MySQL. Вказівки і засоби обох СУБД однакові. Проект досі підтримує високий ступінь сумісності з MySQL.

  • Алгоритм підготовки ПК до використання 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

    Публікація сайту при ОС Linux Mint / Ubuntu — у теці /opt/lampp/htdocs, у рядку адреси браузера писати localhost. Доцільно створити у цій теці підтеку, наприклад, з назвою work і працювати саме у ній, в /opt/lampp/htdocs/work. А у рядку адреси браузера писати localhost/work.

    При неузгодженій роботі Apache при ОС Linux Mint / Ubuntu потрібно:

    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, виконати запити для отримання таких даних:





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











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

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

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

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

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

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


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