Г Ю Цибко - Вивчення опрацювання даних за допомогою запитів у субд access - страница 1

Страницы:
1  2 

Г.Ю.ЦИБКО

Чернігівський державний педагогічний університет

Вивчення опрацювання даних за допомогою запитів у СУБД ACCESS

Системи управління базами даних у курсі інформатики середніх шкіл і педагогічних вузів зараз вивчаються переважно на прикладі реляційної СУБД Microsoft ACCESS, яка входить до розповсюдженого комплекту програм MS Office. Розгляд ACCESS, як правило, завершує цикл вивчення програм вказаного комплекту, отже учнями вже отримані необхідні навички застосування графічного інтерфейсу при вивченні інших офісних прикладних програм (Word, Excel). З методичної точки зору перевагою СУБД ACCESS є збереження кожної бази даних в одному файлі, який має розширення .MDB. Це полегшує перенесення бази даних (БД) між комп'ютерами, перехід між різними об'єктами БД і їх дослідження. Спеціальний об'єкт БД "Схема даних" візуально подає зв'язки між таблицями і зберігає встановлені розробником засоби збереження цілісності і несуперечності даних.

В ході вивчення основних послуг СУБД розглядаються такі об'єкти БД, як таблиці, запити, форми і звіти. Як відомо, таблиця - це основний об'єкт реляційної бази даних, призначений для зберігання даних про предметну галузь; запит - об'єкт бази даних, призначений для опрацювання даних; форма - об'єкт бази даних, призначений для зручного і наочного перегляду, введення і редагування даних; звіт - об'єкт бази даних, призначений для підготовки даних до друку.

Досвід навчання основ проектування і опрацювання баз даних свідчить, що найскладнішим для засвоєння учнями об'єктом БД є запит. Тому при вивченні СУБД ACCESS саме запитам доцільно приділити найбільшу увагу.

Розглянемо основні методичні аспекти вивчення теми "Запити у СУБД ACCESS" в курсі інформатики і програмування ЧДПУ для студентів спеціальностей "математика та основи економіки", "праця, основи підприємництва та інформатики".

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

База даних, яку назвемо Firma, містить дві основні таблиці: СПІВРОБІТНИКИ(КОД*, ПІБ, ДАТА НАРОДЖЕННЯ, АДРЕСА, ПОСАДА, СТАЖ) і ВИПЛАТИ(КОД*, ДАТА ВИПЛАТИ*, СУМА) (зірочками позначені ключові поля) і допоміжну таблицю-довідник ШТАТНИЙ РОЗКЛАД(ПОСАДА), яка використовується для полегшення введення даних у таблицю Співробітники.

На заняттях з теми "Створення таблиць у СУБД ACCESS" студенти створюють файл Firma.mdb, у ньому - вказані таблиці, встановлюють між таблицями Співробітники і Виплати зв'язок у схемі даних "один-до багатьох" і заповнюють таблиці достатньою кількістю записів (не менше 10 записів у головній таблиці і не менше 30 - у зв'язаній). Після цього можна переходити до розгляду запитів.

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

ACCESS підтримує мову запитів SQL (Structured Query Language), але надає зручний інтерфейс для задання запитів шляхом заповнення бланків, подібних до бланків мови QBE (Query By Example) . Для переходу між двома формами запиту використовується кнопка Вид стандартної панелі інструментів. Заповнення бланку є більш наочним і простим шляхом засвоєння принципів роботи із запитами. При потребі ознайомлення з мовою SQL можна поєднувати дві форми створення запитів.

Всі дії з запитами у СУБД ACCESS виконуються на закладинці Запросы вікна бази даних. Кожен запит, що зберігається у файлі, зображується піктограмою з назвою запиту. Піктограми розрізняються у відповідності з типом запиту. Для опрацювання запиту можна використовувати контекстне меню і кнопки управління запитами. Создать - для створення нового запиту; Открыть -для перегляду таблиці-результату запиту (у випадку запитів на вибірку даних) або виконання певної дії з таблицями бази даних (у випадку запиту на зміну даних); Конструктор - для відкривання запиту в режимі конструктора, який дозволяє заповнювати бланк запиту, а також переглядати і редагувати його. Як і при роботі з іншими об'єктами, кнопка Вид на стандартній панелі інструментів забезпечує перемикання між режимами запиту і конструктора.

Для створення нового запиту слід виконати такі дії:

- на закладинці Запросы натиснути кнопку Создать; у вікні, де пропонуються різні способи створення запитів, вибрати Конструктор і натиснути Ok;

- у вікні Добавление таблицы вибрати таблиці, до яких буде робитися запит (робота з цим вікном відбувається так само, як при роботі зі схемою даних);

- за допомогою команди меню Запрос встановити тип запиту, що створюється;

- у вікні Конструктора запитів заповнити бланк запиту, визначивши поля, що беруть участь у запиті, встановивши при потребі позначки для виведення полів та умови відбору даних;

- виконати запит натисненням кнопки Запуск на стандартній панелі інструментів;

- при потребі зберегти запит у файлі бази даних натисненням кнопки Сохранить і закрити його;

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

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

Доступними є такі типи запитів: Выборка - вибір даних з бази даних за певними критеріями;

Перекрестный - подання результатів запиту у вигляді, аналогічному до електронних таблиць; Создание таблицы - запит на створення нової таблиці; Обновление - запит на зміну даних у таблицях; Добавление - запит на додавання до таблиці нових записів;

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

Запити на вибірку даних

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

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

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

Для кожного стовпця бланку слід встановити такі характеристики:

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

- Имя таблицы: автоматично встановлюється у клітинку під назвою поля;

- Сортировка: в цій клітинці бланку можна розкрити список, що дозволяє вибрати спосіб сортування даних у вказаному полі: По возрастанию, По убыванию, Отсутствует;

- Групповая операция (цей рядок виводиться у бланку, якщо на стандартній панелі інструментів натиснути кнопку Групповая операция): дозволяє логічно групувати однакові дані у полі (елемент розкритого списку Группировка) або виконувати над даними з поля певні підсумкові обчислення (Sum - сума, Avg - середнє арифметичне, Min - мінімальне значення у полі, Max -максимальне значення у полі і ін.);

- Вывод на экран: в цій клітинці встановлюється прапорець, якщо поле повинно бути виведене на екран в результаті запиту. При розміщенні нового поля на бланку прапорець встановлюється автоматично;

- Условие отбора: в цій клітинці записується умова для відбору даних (види умов будуть розглянуті нижче);

- Или : записується складова умови "або", що стосується даних в одному полі або різних полях.

Для задання умов щодо даних числових, грошових типів і типу дата/час застосовуються оператори порівняння >, <, >=, <=, = (не пишеться).

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

Логічна константа Null визначає відсутність значення у полі.

Константи типу дата/час беруться в позначки #, наприклад #10.10.02#. Функція Date() повертає значення поточної дати.

Для задання подвійних нестрогих нерівностей (>=...and <=...) можна застосовувати оператор діапазону Between <найменше> And <найбільше>, наприклад, умови >=1 and <=5 і between 1 and 5 еквівалентні.

Для задання умов щодо текстових полів можна застосовувати символи шаблонів * і ?, які мають загальноприйняте значення (відповідно довільна кількість довільних символів і довільний єдиний символ). Якщо потрібно повне співпадіння з умовою відбору, відповідний текст записується без символів шаблонів. При вказуванні шаблону ACCESS автоматично бере його в лапки і дописує перед шаблоном оператор Like (подібний). Наприклад, при введенні умови відбору *бухгалтер вона перетвориться у Like "*бухгалтер".

Приклад роботи з запитами розглядається база даних Firma.

Запит 1. Сформувати список даних про співробітників (ПІБ, ДАТА НАРОДЖЕННЯ, ПОСАДА, СТАЖ), стаж роботи яких: а) не менше п'яти років; б) від 10 до 15 років; в) один рік або більше 20 років.

При створенні запиту слід у вікні Добавление таблицы вибрати таблицю СПІВРОБІТНИКИ. Відповідний вміст бланків трьох запитів зображено на рис.1.


Поле

ГПБ

 

Стаж

Имя таблицы

Співробітники

 

Співробітники

Вывод на экран

0

 

0

Условие отбора

 

 

>=5

Или                 |                                 || |

а)

Поле

ПІБ

 

Стаж

Имя таблицы

Співробітники

 

Співробітники

Вывод на экран

0

 

0

Условие отбора

 

 

>=10 and <=15

Или                 |                                 || |

б)

Поле

ПІБ

 

Стаж

Имя таблицы

Співробітники

 

Співробітники

Вывод на экран

0

 

0

Условие отбора

 

 

1

Или

 

 

>20

в)

Рис.1. Бланки запитів 1а), 1б), 1в)

Запит 2. Сформувати список даних про співробітників (ПІБ, ДАТА НАРОДЖЕННЯ, АДРЕСА), що проживають на проспекті Миру. Вміст бланку запиту зображено на рис.2._

Поле

...

Адреса

 

Имя таблицы

...

 

 

Вывод на экран

...

0

 

Условие отбора

 

Like "*Просп. Миру*"

 

Или                   |            | ||

Рис.2. Бланк запиту 2

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

Запит 3. Вивести відомості про всі виплати всім співробітникам. В результаті повинні

міститися поля ПІБ, ДАТА ВИПЛАТИ, СУМА.

Бланк запиту вказаний на рис.3.

Поле

ПІБ

Дата виплати

Сума

Имя таблицы

Співробітники

Виплати

Виплати

Вывод на экран

0

0

0

Условие отбора

Или

Рис.3. Бланк запиту 3

Обчислення у запитах

У СУБД ACCESS існують два види обчислень у запитах:

- створення нових обчислювальних полів;

- обчислення підсумкових значень.

Для створення обчислювального поля слід у бланку запиту в рядку Поле ввести конструкцію

виду:

<Назва_обчислювального_поля> : <Вираз>

Вираз задає правило, за яким обчислюються значення у створюваному полі. Він може містити знаки математичних операцій, дужки, стандартні функції ACCESS, імена полів таблиці, до якої створюється запит, взяті в квадратні дужки. Якщо треба використати поле іншої таблиці, вказується конструкція [Ім'ятаблиці]. [Ім'яполя].

Запит 4. Вивести дані про розміри всіх виплат співробітникам з указанням відрахувань у пенсійний фонд (2%) Вміст бланку запиту зображено на рис.4._

 

 

 

 

Пенсійний

Поле

Код

ПІБ

Сума

фонд: CCur([Сума] *0,02)

Имя таблицы

Співроб.

Співроб.

Виплати

 

Вывод на экран

 

0

0

0

Условие отбора

Или

Рис.4. Бланк запиту 4

Зауваження: функція CCur переводить значення числового типу у значення грошового типу, додаючи два знаки після коми і позначення грошової одиниці. Без використання цієї функції значення у полі Пенсійний фонд виглядають як звичайні цілі числа.

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

СУБД ACCESS дозволяє виконувати такі групові операції над даними: Группировка - операція призначена для групування даних у тому полі, в якому вона встановлена (відповідні записи виводяться підряд); Sum - знаходження суми значень у відповідному полі; Avg (Average) - знаходження середнього арифметичного; Min - знаходження мінімального значення у полі; Max - знаходження максимального значення у полі; Count - знаходження кількості записів; StDev - знаходження середньоквадратичного відхилення; Var - знаходження дисперсії; First - знаходження першого значення; Last - знаходження останнього значення.

Запит 5. Отримати загальну суму виплат, зроблених кожному співробітнику. У відповіді вивести поля ПІБ, ЗАГАЛЬНА СУМА.

Бланк запиту зображений на рис.5.

Поле

Страницы:
1  2 


Похожие статьи

Г Ю Цибко - Вивчення опрацювання даних за допомогою запитів у субд access