Лабораторна робота №2 на тему Створення запитів з однієї чи декількох таблиць, Організація баз даних і знань, НУДПСУ
« Назад
Лабораторна робота №2 на тему Створення запитів з однієї чи декількох таблиць(за умовою, підсумковий запит, перехресний запит на пошук записів, модифікація даних за допомогою запитів) Запит - це об'єкт бази даних, що дозволяє відбирати дані з однієї або декількох таблиць за умовами. Крім того, використовуючи запит можна створювати обчислюють поля, що, для кожного запису або для групи записів. Це робиться в запитах “на вибірку”. Запити на відновлення даних дозволяють вносити зміни відразу в кілька записів, які задовольняють зазначеним умовам. Запит - найважливіший об'єкт роботи з даними. Розглянемо послідовно всі типи запитів. 2.1. Робота з однією таблицею У перших, потрібно навчитися записувати умови відбору даних у запитах. Умови записуємо в рядку “Умова відбору:”. Для числових полів умови мають вигляд, наприклад, <100 , або >40 And <60. Тобто, записуємо знак відносини й числове значення. Можна також використати логічні функції And , Or, Not. Функція Beetween нижня_границя And верхня_границя використається для завдання умови влучення в інтервал. Наприклад, Beetwen 40 And 60 влучення значення поля від 40 до 60. Умови для текстових полів записуються з використанням функції Like “Рядок пошуку із символами шаблона” Символ шаблона * заміняє довільна кількість символів у даній позиції, символ ? заміняє один який-небудь символ у позиції, символ # указує, що в даній позиції повинна стояти цифра. Наприклад, умова Like “CA-#######” дозволить відібрати запису, у яких записані номери паспортів серії СА. 2.1.1 Запит, що відбирає дані з однієї таблиці за умовою Розглянемо приклад створення запиту, що відбирає з таблиці Книги ті екземпляри, ціна яких більше 20 гривень і прізвище автора починається або з букви “Б”, або з букви “В”. Перейдіть на вкладку Запити. Натисніть кнопку Створити. Виберіть у меню Конструктор. У вікні діалогу Додавання таблиці на вкладці Таблиці виберіть таблицю, з якої потрібно відібрати дані. Для нашого випадку це - Книги. Натисніть кнопку Додати. Закрийте вікно Додавання таблиці. Тепер потрібно вибрати ті поля, які необхідно включити в запит. Виберемо поля: Автор, Назва, Рік, Вартість. Для того, щоб помістити ці поля в бланк запиту, потрібно двічі нажати кнопкою миші на імені поля в таблиці. Є й інші можливості: перетягнути назва поля з таблиці в бланк запиту або вибрати необхідні поля в списку назв полів у бланку запиту. Малюнок 7. Конструктор запиту 1.1 на вибірку Далі необхідно поставити умови, по яких буде вестися відбір. Для цього в рядку Умова відбору для Вартості ставимо умову ”>20”. Для поля Автор ставимо умову “Б*” Or “В*”, де * означає всі символи після першої Б. Функція Like буде додана автоматично. Для перегляду результату натисніть кнопку “!” або виберіть у пункті меню ВИД -режим таблиці. Малюнок 8. Результат виконання запиту 1.1 2.1.2 Підсумкові запити Якщо нам потрібні не окремі записи, а підсумкові значення для групи даних, наприклад, кількість книг по інформатиці, або кількість книг кожного видавництва, або кількість читачів по кафедрах, то ми створюємо підсумкові запити. Такі запити створюють групи записів, для яких збігаються значення зазначених полів (для таких полів вибираємо Групові операції – Угруповання), і обчислюють підсумкові значення для інших полів (для таких полів вибираємо Групові операції –Sum, Avg, Count, …)... Створимо такий підсумковий запит: порахуємо кількість читачів кожної кафедри. Вибираємо таблицю Читачі, включаємо поля Кафедра й Прізвище. Для створення підсумкового запиту на панелі інструментів вибираємо піктограму Групові операції (зі значком S). У бланку запиту з'являється рядок Групові операції. У списку, що розкривається, цього рядка є список можливих обчислень для груп записів: SUM- обчислення суми значень для групи; AVG - середнє значення поля для даних із групи записів; MIN - мінімальне значення для даних із групи записів; MAX - максимальне значення для даних із групи записів; COUNT- кількість записів, у яких є значення із групи; STDEV - стандартне відхилення; VAR - дисперсія; FIRST - значення в першому записі групи; LAST - значення в останньому записі групи. Малюнок 9. Результат виконання запиту 1.2 За значенням першого поля Кафедра створюємо групу, а для другого вибираємо COUNT (підрахунок кількості записів у групі). Для того, щоб при перегляді запиту кожне поле мало зрозумілий підпис, визначимо властивості поля. Для цього вибираємо ВИД - Властивості й ставимо Підпис – Кількість читачів. Малюнок 10. Конструктор запиту 1.2 2.1.3 Перехресний запит - особливий тип підсумкового запиту, що дозволяє вивести обчислені значення в перехресній таблиці, що нагадує зведену таблицю в Excel. Наприклад, підрахуємо сумарну вартість книжок для кожного року видання по видавництвах. Для цього створимо перехресний запит майстром запитів Запрос-Создать-Перекрестний запросо. У вікні діалогу вибираємо послідовно таблицю, назви рядків, назви стовпців, функцію обробки даних. Для нашого випадку це: таблиця - Книги; назви рядків - Рік; назви стовпців - Видавництво; функція - Sum(Вартість). Установимо підсумкове значення - Підсумкове значення по строках. Малюнок 11. Результат виконання запиту 1.3 Переглянемо створений запит у режимі Таблиця (малюнок 11) і в режимі Конструктор (малюнок 12). Малюнок 12. Конструктор запиту 1.3 2.2. Запити, які відбирають дані з декількох таблиць При створенні таких запитів до верхньої частини вікна конструктора запитів вибираємо кілька таблиць. Якщо зв'язку між таблицями були створені у вікні “Схема даних”, то в цьому вікні вони з'являться автоматично. Тепер можна вибирати в один запит поля з декількох таблиць. Існує два різних типи об'єднання двох таблиць - внутрішнє й зовнішнє. Якщо об'єднання внутрішнє (саме його ви встановили), то в один запит будуть об'єднані записи, у яких збігаються значення в полях зв'язку. Якщо який-небудь запис в одній таблиці не має відповідного запису в іншій таблиці, то цей запис не буде включена в запит. Зовнішнє об'єднання буде розглянуто пізніше. Створення полів, які обчислюються. У таблицях баз даних (на відміну від електронних таблиць) ніколи не зберігають дані, які можуть бути обчислені за даними з таблиць. У більшості випадків тому, що це вимагає значних витрат пам'яті й сповільнює пошук необхідних даних й обробку даних у таблицях. Всі необхідні обчислення виконують у запитах, створюючи поля, які обчислюються. Для створення таких полів у вільному стовпці конструктора запитів записують нове ім'я поля, знак “:”, а потім вираження, що використає імена інших полів й обчислює необхідне значення. Наприклад, обчислимо вартість замовлення, якщо є поля Вартість одиниці й Кількість одиниць. У новому стовпці запишемо: Вартість замовлення: [Вартість одиниці]*[Кількість одиниць] Імена полів записуються у квадратних дужках. При записі виражень можна використати убудовані функції Access. Імена цих функцій, на відміну від Excel, пишуться по-англійському, але за змістом багато з них збігаються з функціями Excel. Приведемо кілька функцій: Iif(умова; вираження1; вираження2) – обчислює вираження1, якщо умова виконується, і вираження2, якщо умова не виконується (збігається з функцією ЯКЩО в Excel). DateDiff(“d”; дата1; дата2) - обчислює різницю між двома датами в днях. Якщо перший аргумент “m” – різниця дат обчислюється в місяцях, якщо “y” - у літах. Повний список всіх функцій ви знайдете у вікні «Построитель виражень». 2.2.1 Створимо запит, що відбирає й поєднує запису із двох таблиць - Книги й ЧитКниги, щоб показати, які книги були видані читачам. Створимо обчислює поле, що, з ім'ям Пеня, що нараховується на кожну книгу, що не повертається вчасно. Розмір пені дорівнює 1% від вартості книги за кожний прострочений день. Створимо запит з ім'ям Список1. Для створення запиту Список1 перейдіть на вкладку Запити й виберіть кнопку Створити. Додайте в запит таблиці Книги й ЧитКниги. У вікні схеми даних повинна бути показана зв'язок між таблицями по полю Инв№ - інвентарний номер книги. Перетягніть мишею в нижню половину вікна поля, які необхідно включити в запит: Автор, Назва, Вартість, Инв№, Дата видачі, Дата повернення, NB. Останнє поле буде потрібно для зв'язку запиту з таблицею Читачі. У першому вільному стовпчику нижньої частини вікна створіть поле, що обчислюється, з ім'ям Пеня. Для цього наберіть у верхньому рядку (де розташовується ім'я поля) такий текст: Пеня: iif([Дата повернення]>Date(); Для створення такого вираження можна використати Построитель виражень. Для цього натисніть кнопку Побудувати на панелі інструментів. На екрані відкривається вікно Построителя виражень. У цьому вікні створюється вираження послідовним додаванням до нього полів таблиць або запитів, функцій, констант й операторів (малюнок 13). Імена полів записуються у квадратних дужках. Малюнок 13. Використання Построителя виражень Збережете запит під ім'ям Список1. Переглянете запит, клацнувши на кнопці Відкрити. Розмір пені, зрозуміло, залежить від поточної дати, на яку він обчислювався. Приклад наведений на 26.01.03. Малюнок 14. Результат виконання запиту Список1 2.2.2 Створимо підсумковий багато табличний запит. Обчислити для кожного читача кількість книг на руках, їхню загальну вартість і пеню, що нарахована для книг даного читателя. Для цього виберіть таблиці Книги, Читачі йзапит Список1. У бланк запиту включите поля Прізвище з таблиці Читачі, Инв№ з таблиці Книги, Вартість із таблиці Книги й Пеня із запиту Список1. Для створення підсумкового запиту на панелі інструментів вибираємо піктограму Групові операції. У бланку запиту з'являється рядок Групові операції. Для першого поля запиту Прізвище з таблиці Читачі вибираємо Угруповання, для Вартість із таблиці Книги – SUM (підрахунок суми), для поля Инв№ з таблиці Книги- CountідляПеня із запиту Список1- SUM. Для того, щоб при перегляді запиту кожне поле мало зрозумілий підпис, визначимо властивості полів. Для цього вибираємо Виду-властивості й задаємо Підпис для кожного поля -кількість книг, Загальна вартість книг і Загальна сума пені. Малюнок 15. Конструктор запит 2.2 Малюнок 16. Результат виконання запиту 2.2 2.2.3 Створення запиту на пошук записів, які не мають зв'язаних в іншому списку. Такі запити називаються «Записи без підлеглих». Для створення таких запитів необхідно змінювати тип об'єднання таблиць на зовнішній. При такому типі об'єднання в запит обов'язково включаються всі записи з першої таблиці й до них додаються поля із другої таблиці, якщо в другій таблиці є запис, для якої значення зв'язаних полів збігаються. Якщо в другій таблиці такого запису ні, то до запису з першої таблиці додаються поля з порожніми значеннями. Зовнішнє об'єднання в конструкторі таблиць зображується лінією зі стрелкой. Наприклад, створимо список книг, які не видані на руки читачам. У запит включимо дві таблиці Книги й ЧитКниги. Включимо в запит поля Инв№, Шифр, Автор, Назва з таблиці Книги й Инв№ з таблиці ЧитКниги. Знайдемо ті записи, які присутні в таблиці Книги й відсутні в таблиці ЧитКниги. Змінимо Параметри об'єднання. Необхідно створити Зовнішнє об'єднання. Для цього двічі натисніть на лінії зв'язку між таблицями. У вікні діалогу виберіть другий тип – “Об'єднання ВСІХ записів з ‘Книги' і тільки тих записів з 'ЧитКниги', у яких зв'язані поля збігаються”. Тепер створене зовнішнє об'єднання, у яке включені всі книги з таблиці Книги. Для тих книг, які не мають записів з таблиці ЧитКниги встановлюється значення ‘Null’, тому для поляИнв№ з таблиці ЧитКниги встановимо такі параметри Умова відбору – Is Null і Виведення на екран відсутній. Малюнок 17. Конструктор запиту 2.3. Переконаєтеся, що запит вибирає необхідну інформацію. Малюнок 18. Установка параметрів об'єднання Малюнок 19. Результат виконання запиту 2.2 2.3. Модифікація даних за допомогою запитів 2.3.1 Запити, які змінюють значення групи записів Такі запити називаються запити на відновлення даних. Наприклад, за допомогою такого запиту можна здійснювати зміна вартості книг по даті їхнього видання або по інших умовах. Наприклад, збільшити вартість на 20% для книг 1998 року видання. Цієї запит змінює таблицю, тому для повного захисту від можливих помилок зробіть резервну копію таблиці. Для цього перейдіть у вікно бази даних, виберіть необхідну таблицю й виконаєте команду Виправлення - Копіювати. Далі виберіть команду Виправлення - Вставити й у вікні діалогу дайте копії таблиці нове ім'я. Перш, ніж обновити дані, створимо запит на вибірку. Перевіримо, чи вибираються необхідні записи, а після перешикуємо його в запит на відновлення. У запит включимо поля Рік і Вартістьіз таблиці Книги. Надамо можливість уводити рік видання як параметр. Для цього у квадратних дужках уведемо [Уведіть рік] у рядку Умова відбору.. Під час виконання запиту цей текст з'явиться в діалоговому вікні для уведення року видання. Перевіримо, чи вибираються необхідні записи. Далі перетворимо цей запит у запит на відновлення. Для цього в основному меню вибираємо Запит-Відновлення. У рядку Відновлення вводимо вираження [Вартість]*([націнка]+100)/100 для перерахування вартості книг. Це вираження використає ще один параметр [націнка], що дає можливість уводити розмір націнки у відсотках. На екрані з'являється вікно діалогу, що повідомляє про кількість обновлених записів. Після підтвердження таблиця обновляється, і доступу до початковим даних уже немає. Малюнок 20. Конструктор запиту 3.1 2.3.2. Запит на створення нових таблиць Такі запити створюють нову таблицю й записують у неї дані з інших наявних таблиць або запитів. Наприклад, створимо таблицю, що містить інформацію про читачів, які не повернули книги вчасно. Перед тим, як зробити запит на зміну даних, створимо запит на вибірку, щоб переконатися, що робота йде з необхідними даними. У запит включимо таблицю Читачі йзапит Запит 2-2, що для кожного читача підраховує суму пені. Виберіть поля NB, Прізвище, Кафедра, Телефон- Читачі, Count-Инв№, Sum-Вартість, Sum-Пеня із запиту Запит 2-2. З'єднаєте таблицю й запит по полю Прізвище. Для поля Sum-Пеня задайте умову “>0” Ця умова буде вибирати тих читачів, у яких є пеня. Переконаєтеся, що запит вибирає необхідну інформацію. Для перебудови запиту на створення таблиці перейдіть до команди Запит-Створення таблиці. У вікні діалогу наберіть ім'я нової таблиці (наприклад, “Боржники на 25 січня”). Після запуску запиту на екрані з'явиться вікно діалогу з повідомленням про кількість записів, які будуть вставлені в таблицю. Для підтвердження створення таблиці натисніть кнопку ТАК. На вкладці Таблиці вікна бази даних перевірте наявність таблиці із цим ім'ям і переглянете її вміст. Малюнок 21. Конструктор запиту 3.2 2.3.3. Створення запитів на видалення даних Такі запити дозволяють видаляти з таблиць групи записів, які задовольняють яким-небудь умовим. Наприклад, створимо запит, що видаляє запис із таблиці ЧитКниги на момент повернення читачем книги. Для повного захисту від помилок зробіть резервну копію таблиці. Для цього перейдіть у вікно бази даних, виберіть необхідну таблицю й виконаєте команду Виправлення^-Копіювати. Далі виберіть команду Виправлення-Вставити й у вікні діалогу дайте копії таблиці нове ім'я. Перед тим, як зробити запит на зміну даних, створимо запит на вибірку, щоб переконатися, що робота йде з необхідними даними. У запит включимо таблицю ЧитКниги: поля Инв№ й NB. Поставимо умови на значення цих полів через параметри [Уведіть Инв№ книги], [Уведіть номер читацького квитка]. При виконанні цього запиту з'являться послідовно два вікна діалогу для уведення необхідної інформації. Переконаєтеся, що запит вибирає необхідну інформацію. Малюнок 22. Конструктор запиту 3.3. Для перебудови запиту на видалення перейдіть до команди Запит- Видалення. У рядку Видалення залишіть Умову. Після запуску запиту на екрані з'явиться вікно діалогу з повідомленням про кількість записів, які будуть вилучені з таблиці. Для підтвердження натисніть кнопку Так. Після підтвердження таблиця обновляється й доступу до початковим даних уже немає. Малюнок 23. Результат роботи запиту 3-3. З повагою ІЦ "KURSOVIKS"!
|