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

Мета:

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

Після вивчення теми учень:

• описує: призначення форми, запиту, звіту;
• уміє:

  • знаходити в базі дані за певними критеріями відбору, створюючи прості вибіркові запити в автоматизованому режимі;

  • застосовувати майстри для створення таблиць, форм, запитів і звітів;

  • використовувати конструктор для змінення запитів і форм;

  • застосовувати засоби пошуку даних;

  • створювати звіти.

Обладнання: комп'ютери з встановленими ОС та LibreOffice, (дана) інструкція, база даних у форматі odb.

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

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

Хід уроку

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

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

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

Типи даних, які використовують у базі даних: Текст, Дата/Час, Число, Логічне тощо.

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

odb — це тип файлів для опрацюванням LibreOffice Base як усталено.

Cпособи створення таблиць у середовищі LibreOffice Base: у режимі дизайну, з допомогою помічника, cтворенням нового представлення таблиці.

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

Примітка. Вивчення нового матеріалу супроводити покроковим закріпленням — роботою учнів з наявною базою даних.

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

• відкрити файл бази даних;

• відкрити таблицю або форму, у якій буде здійснено пошук;

• або натиснути клавіші Ctrl + F,
  або натиснути кнопку Знайти запис;

• у вікні діалогу Пошук запису задати налаштування пошуку і натиснути кнопку Пошук.

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

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

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

У середовищі LibreOffice Base передбачено кілька способів застосування фільтрів.

• Автофільтр — швидко відбирає записи з однаковими значеннями у певному полі даних. Для застосування активувати потрібне поле з вибраним значенням і натиснути кнопку Швидкий фільтр (крайня кнопка ліворуч серед чотирьох кнопок роботи з фільтрами);

• Стандартний фільтр (Типовий фільтр) — дозволяє відібрати записи, вказавши діапазони значень у певних полях з використанням сполучників «і» та «або». Для застосування натиснути кнопку Типовий фільтр (друга праворуч серед чотирьох кнопок роботи з фільтрами), після чого у вікні діалогу Стандартний фільтр вказати критерій відбору і натиснути кнопку Гаразд;

  

Відміна дії — натисканням кнопки Очистити фільтр / сортування.

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

Запит (як категорія БД) — це спеціальний об’єкт, призначений для вибірки даних з таблиць бази, а також для виконання обчислень та інших операцій з базовими таблицями, включаючи їхнє перетворення. На відміну від реальної таблиці, цей набір записів реально не існує в базі даних. У результаті запиту утворюється таблиця. Її інформація є актуальною, тобто відбиває останні зміни даних.

Відповідно до функцій виділяють такі типи запитів:

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

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

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

2. Запити на зміну — вносять в таблиці значні зміни, відмінити які не можна. В LibreOffice Base такі запити часто заблоковані, тобто на виконання таких запитів необхідно мати специфічні права доступу. Запити такого типу поділяють підтипи:

   • запит на видалення записів;
   • запит на долучення записів;
   • запит на створення таблиці;
   • запит на оновлення значень полів.

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

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

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

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

Записати умови запиту можна по-різному. LibreOffice Base підтримує типи запитів QBE і SQL.

Запит QBE — запит за зразком, ангійською Query By Example. Його формують заповненням бланка запиту у вікні Конструктора запитів. Подають (графічним) бланком, який містить імена полів однієї або декількох таблиць у вигляді заголовків стовпців. У стовпцях можуть бути вирази. У рядках бланка указують ім'я таблиці, порядок сортування, вивід на екран, умови відбору. Бланк запиту нагадує бланк розширеного фільтру. На відміну від розширеного фільтру в запиті вказують конкретні поля для виводу в підсумковій таблиці. При заповненні бланка запит можна виконати («програти»), щоб переконатися у правильному відборі записів. Для цього використовують кнопку на панелі інструментів «Виконати». Готовий запит зберігають, вказавши ім'я. Як усталено буде «Запит1».

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

Будь-який запит, незалежно від способу його створення, можна проглянути у вигляді SQL інструкції, використавши вказівку меню Зміни / Редагувати у режимі SQL...

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

Подальший опис побудови запитів здійснено на прикладі бази даних, яку буде надано для опрацювання у ході практичної роботи. Ця база даних вже містить чотири таблиці: Дисципліни, Оцінки, Студенти, Форма оплати, зв'язані між собою.

Розглянемо засоби створення запитів з детальним дослідженням інтерфейсу й інструментів. Ці знання допоможуть обирати найефективніший метод формування запиту.

На вкладці Запити можна обрати один з трьох варіантів створення запиту в розділі Завдання:

1. Створити запит у режимі дизайну;
2. Використати Помічника для створення запиту;
3. Створити запит у SQL-представленні.

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

Створення запиту за допомогою Помічника виконують покроково:

Крок 1 — вибір з наявних таблиць (запитів) таких полів, які мають складати майбутню таблицю запиту.

Поля обирають у список (Поля у запиті):

• поодинці за допомогою кнопки ;
• усі разом за допомогою кнопки .

• поодинці за допомогою кнопки ;
• усі разом за допомогою кнопки .

Крок 2 — визначення порядку та типу впорядкування даних таблиці-результату.

Крок 3 — визначення умови відбору даних з вихідних таблиць.

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

Кроки 5 і 6 визначають наявність та умову групування. Вони часто неактивні навіть при наявності записів в таблиці, які можна групувати.

Крок 7 дає можливість дати нові назви (псевдоніми) обраним полям.

Крок 8 — визначення назву запиту та можливість перегляду загальної інформації щодо властивостей таблиці запиту у полі Огляд.

Створення запиту в режимі дизайну

1. У вікні діалогу Додати таблицю чи запит додати у вікно запиту ті таблиці або запити, на основі яких створюють запит.

   

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

2. Вибрати поля для Запиту.

   

   Це можна зробити кількома способами:

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

• двічі клацнути в табличці на назві поля;

• перетягнути на стовпець бланку назву одного поля чи кількох або символ * (всі поля). При цьому ім'я таблиці автоматично з'явиться на бланку.

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



4. Вказати впорядкування за потрібними полями, вибравши із списку, що розкривається, варіанти: за збільшенням, за зменшенням, відсутній. Як усталено впорядкування не здійснюють.



5. Замовити умови відбору в одному рядку або в кількох рядках. Якщо умови вказано в одному рядку, то їх сполучено логічною операцією «І»

   

   якщо в різних рядках — логічною операцією «АБО».

   

6. Проглянути результат роботи: виконати запит, використовуючи кнопку Виконати запит або натиснувши функціональну клавішу F5.

7. Відредагувати запит у режимі роботи з бланком у Конструкторі або в режимі SQL. Можна змінити таке:

   • послідовність стовпців (виділенням і перетягуванням);

   • ширину стовпців (перетягуванням за заголовок);

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

8. Зберегти запит як об'єкт, давши йому ім'я (як усталено Запит1).

Створення запиту в SQL-поданні

Створення запиту у SQL-поданні передбачає знання мови SQL і специфічного синтаксису формування запитів. Для створення запитів у текстовому форматі в LibreOffice Base існує SQL-подання, яке буде подано чистим аркушем, якщо запит ще не створено, або аркушем з SQL-кодом, якщо запит відкрито для редагування у SQL-поданні.

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

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

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

Результатом цього запиту буде така таблиця:

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

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

• виділити поля: Прізвище, Ім'я, Назва_дисципліни, Оцінка, які розташовано у трьох різних таблицях;

• записати в рядку Критерій поля Оцінка: >=4.

Результатом цього запиту буде така таблиця:

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

• виділити поля: Прізвище, Ім'я, Назва_дисципліни, Оцінка, розташовані у трьох різних таблицях;

• записати в рядку Критерій поля Оцінка: <=4;

• записати в рядку Критерій поля Назва дисципліни: Математика (звертайте увагу на регістр і розмір літер в текстовому записі).

Результатом цього запиту буде така таблиця:

Таким самим чином можна сформувати умови із знаками: =, <, >, >=, <= та використанням логічних функцій OR (або), AND (і), NOT (заперечення).

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

• виділити поля: Прізвище, Ім'я ;

• записати в рядку Критерій поля Ім'я: LIKE'*O*'.

Результатом цього запиту буде така таблиця:

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

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

Замість кількох запитів для різних груп студентів доцільно створити один запит з параметром — номером групи. Якщо необхідно створити пошук за конкретною дисципліною з виведенням списку оцінок, то Критерій виглядатиме так:

:Введіть_номер_групи;
:Дисципліна.

Результатом такого запиту буде таблиця:

Запит з обчислюваними полями отримують таким чином. Для обчислюваних віртуальних полів у стовпець замість імені поля вводять дію, яку треба виконати з відповідними полями. Причому назви полів вводять у лапках, а дію – математичним символом без пробілів. Назву обчислюваного поля вводять у рядку Псевдонім. Наприклад, для обчислення стипендії, яку отримають студенти з урахуванням пільгових надбавок, використовують таку форму запису: "Розмір_стипендії"+"Розмір_пільг".

Результатом описаного запиту буде така таблиця:

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

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

Cтворити звіт можна як за допомогою помічника для створення звітів, так і у режимі дизайну. Зазвичай помічник використовують для створення основної частини звіту, тобто структури, а режим дизайну — для удосконалення зовнішнього вигляду звіту.

Розглянемо, наприклад, створення звіту для подання списку учнів (прізвище, ім'я, дата народження) за класами для наявної бази даних. Потрібно спочатку відкрити цю базу даних у середовищі LibreOffice Base. Клацнути по елементу Звіти в області База даних і двічі клацнути в області Завдання по рядку Використати помічник для створення звіту.

Крок 1. Вибір полів. У вікні діалогу Помічник звітів вибрати поля з таблиць і запитів, вміст яких буде відображено у звіті. Наприклад з таблиці Студенти поля Код студента, Прізвище, Ім'я, Телефон i Розмір_стипендії. Після завершення вибору натиснути кнопку Далі.

Крок 2. Поля позначок. При потребі вказати (змінити) позначення полів (записів бази даних), заповнивши відповідні поля вікна діалогу Помічник звітів. Інакше позначення будуть збігатися з назвами полів запису бази даних (що й рекомендовано зробити учням на уроці). Після цього потрібно натиснути кнопку Далі вікна діалогу Помічник звітів.

Крок 3. Групування. Додати необхідні рівні групування. Для цього перемістити поля звіту зі списку Поля у список Групування за допомогою кнопки зі стрілкою праворуч (>), відміна при потребы робити за допомогою кнопки зі стрілкою ліворуч (<). На ілюстрації нижче подано приклад групування за розміром стипендії. Для продовження потрібно натиснути кнопку Далі.

Крок 4. Параметри сортування. Визначити порядок упорядкування записів для відображення у звіті. На малюнку далі проілюстровано встановлення впорядкування лише за розміром стипендії, за яким буде проведено групування. Натиснути кнопку Далі.

Крок 5. Вибір стилю. Вибрати розмітку й орієнтацію сторінки. Наприклад, колонковий вигляд і книжну орієнтацію. Натиснути кнопку Далі.

Крок 6. Створити звіт:

• вказати заголовок створеного звіту (Стипендії студентів на поданій ілюстрації);

• вибрати типу звіту (Динамічний звіт — для збереження звіту у вигляді шаблону. При відкритті такого звіту його буде відображено з поточним вмістом. Інакше звіт міститеме );

• у групі Що ви збираєтеся робити після створення звіту? вибрати Створити звіт зараз для збереження звіту і завершення роботи.

Натиснути кнопку Завершити. Звіт буде відкрито у вікні LibreOffice Writer у режимі Тільки для читання.

5. Закріплення вивченого матеріалу

У наявній базі даних:

• створити запит Оцінки_cтудентів з видимими полями: Прізвище, Ім'я, Дисципліна, Оцінка;

• створити запит Група_151 на вибірку лише тих студентів 151 групи з видимими полями: Прізвище, Ім'я, Група;

• cтворити звіт по студентам (за полями Прізвище Стипендія: групування — за групою, упорядкування (сортування) — за групою, а потім — за прізвищем.

5. Підбиття підсумків уроку
Виставлення оцінок.

6. Домашнє завдання
Вивчити матеріал уроку. У наявній базі даних створити запит і звіт, відмінні від створених у класі.

Текст упорядкувала Ломака Ірина Михайлівна, вчитель гімназії «Міленіум» № 318 Солом'янського району міста Києва, під час виконання випускної роботи на курсах підвищення кваліфікації з 03.11.2014 по 21.11.2014.

Використано випускні роботи курсантів Голуб Євгенії Сергіївни (на курсах з 01.10.2012 до 08.12.12) і Музичук Тетяни Вікторівни (на курсах з 01.10.2012 по 08.12.2012).