Если у вас возникли сложности с курсовой, контрольной, дипломной, рефератом, отчетом по практике, научно-исследовательской и любой другой работой - мы готовы помочь.
Предоплата всего
Подписываем
Тема дисципліни: «Технологія проектування структури бази даних, редагування і коригування основних об’єктів СУБД Access»
Самостійна робота № 19
Тема самостійної роботи: «Призначення, особливості використання та технологія створення запитів з параметрами, підсумкових та перехресних запитів».
Форма роботи: вивчення теоретичного матеріалу.
Кількість годин – 2
Мета: дати поняття про запити, їх класифікацію. Навчити створювати найпростіші запити на вибірку з допомогою майстра; навчити створювати перехресні запити з допомогою Майстра за Конструктора, запити з параметром, додавати умови для відображення полів.
Література:
Студент повинен знати: теоретичні основи створення та застосування, призначення запитів, типи запитів, способи створення запитів.
Студент повинен вміти: створювати запити з допомогою майстра, створювати перехресні запити, запити з параметром, додавати в запити умови для відображення полів.
Міждисциплінарна та внутрішньопредметна інтеграція: дисципліна базується на раніше отриманих знаннях в області «Інформатика», «Іноземна мова» опанування дисципліни йде паралельно з вивченням дисциплін «Бухгалтерський облік», «Вища математика», набуті знання і вміння використовуються при вивчені дисципліни «Економіка торгівлі».
Порядок виконання самостійної роботи
Ознайомитись з метою самостійної роботи та методичними рекомендаціями стосовно її виконання.
Опрацювати теоретичний матеріал.
Запитання для самоконтролю.
МЕТОДИЧНІ ВКАЗІВКИ
Завдання 2.1.
Найбільш потужний і ефективний спосіб отримання корисної інформації з бази даних – це запити.
Запит — це вираз, який визначає, яку інформацію потрібно відшукати в одній або кількох таблицях.
Запити, на відміну від фільтрів, можуть не лише знаходити необхідні дані з таблиць, але й проводити деякий аналіз даних. За допомогою запитів можна також об'єднувати інформацію, вміщену в кількох зв'язаних таблицях, а також виконувати обчислення на основі значень з полів таблиці. При розробці запита можна вибрати, які поля вихідних таблиць і в якій послідовності будуть включені до таблиці результатів.
Результат роботи запиту – це група записів, які задовольняють заданому критерію запиту. Це тимчасова таблиця, які не являється об’єктом бази даних і зберігається тільки в пам’яті комп’ютера. Якщо з моменту останнього запуску запиту дані у вихідній таблиці були змінені, то при виконанні запиту результуючий набір даних буде включати вже оновлені дані.
Класифікація запитів
Запит на вибірку. Тип запиту, прийнятий за замовчуванням. Здійснює вибірку даних, які відповідають вказаним умовам відбору, з одної або декількох таблиць. Результат виконання такого запиту – це набір записів, який відображається в режимі таблиці. Крім цього, застосовуючи при використанні запиту на вибірку групові операції, можна групувати чи дані, наприклад, обчислювати суми, середні значення або кількість записів, що задовольняють критерію відбору.
Запит на зміну. Група запитів, об'єднаних однією загальною властивістю: за допомогою таких запитів можна відразу змінити цілий набір записів, наприклад, оновити, додати в таблицю дані або видалити їх, а також створити на основі результуючого набору нову таблицю.
Перехресний запит. Перехресний запит — це таблиця, у якій дані упорядковані по двох категоріях Заголовки як рядків, так і стовпців результуючої таблиці перехресного запиту визначаються на підставі значень полів, а не їхніх назв.
Запит з параметром. Спеціальний "інтерактивний" тип запиту: перш, ніж запит буде виконаний, на екран ви водиться діалогове вікно з запрошенням ввести один чи ряд параметрів, наприклад умова відбору записів по визначеному полю. Запит, таким чином, допускає деяку модифікацію при кожному своєму запуску, що дуже зручно, наприклад, при генеруванні місячних чи квартальних звітів про доходи підприємства. Запити з параметрами можуть бути віднесені до окремого типу умовно, тому що введення параметра можна організувати для запитів на вибірку, запитів на зміну і перехресні запити.
Запит SQL. При побудові запиту в режимі конструктора програма Access автоматично генерує відповідну запиту інструкцію мовою SQL (Structured Query Language — мова структурованих запитів), що використовується для створення запитів, маніпулювання даними в таблицях, а також керування базами дані Інструкції SQL можна переглядати і редагувати, в режимі SQL вікна запиту.
Створення запиту за допомогою майстра простих запитів
Майстер простих запитів Access створює елементарні запити на вибірку. Умови відбору для операцій фільтрації і сортування в цих запитах указати не можна. Однак, на відміну від раніше розглянутих засобів фільтрації, за допомогою цього майстра можна вибрати для відображення в результуючій таблиці тільки ті поля, що необхідні. Крім того, як джерела даних для запиту можна вказати майстру поля однієї чи декількох таблиць або запитів. Якщо в запиті беруть участь числові поля, майстер дозволяє створити так званий підсумковий запит.
При створенні простого запиту за допомогою майстра послідовність дій повинна бути наступною.
У вікні відкритої бази даних клацнути на кнопці Запросы, а потім в області об'єктів двічі клацнути на ярлику Создание запроса с помощью мастера (Можна також відкрити вкладку Запросы, клацнути на кнопці Создать, зі списку доступних методів створення нового запиту слід вибрати пункт Простой запрос). На екрані з'явиться перше діалогове вікно майстра.
З розкривного списка Таблицы и запросы вибрати таблиці (або запити), поля яких повинні бути включені в новий запит.
Включити в запит поля, що підходять, зі списку Доступные поля. Вибрати поля можна з допомогою кнопок вибору полів. Виділені поля перемістяться в список Выбранные поля. Можна включити в запит поля і з інших таблиць, однак між ними обов’язково потрібні бути встановлені зв’язки. Після сформування списку вибраних полів треба клацнути на кнопці Далее, щоб перейти до наступного етапу.
В наступному вікні майстер пропонує вибрати тип запиту, що створюється: подробный (відображаються всі записи) та итоговый (з виконанням арифметичних операцій над числовими полями таблиці). Після вибору потрібної опції натиснути кнопку Далее.
Заключне вікно майстра пропонує вказати ім’я запиту. Якщо в цьому вікні вибрати опцію Открыть запрос для просмотра данных і клацнути на кнопку Готово, майстер створить запит, збереже його і відобразить на екрані результати виконання нового запиту.
Прості запити, створені майстром, потребують подальшої доробки в режимі конструктора запитів. Однак з запитами в режимі таблиці можна працювати так само, як і з таблицями, застосовуючи при потребі функції сортування і фільтрації.
Збережений запит можна знову виконати, відкривши вкладку Запросы вікна бази даних і двічі клацнути на імені потрібного запиту (або виділити його і натиснути кнопку Открыть панелі інструментів вікна бази даних).
Перед тим як створити запит слід продумати відповіді на такі запитання:
Яка таблиця (чи таблиці) містить потрібну інформацію?
Чи мають таблиці відповідні ключі.
Тип запиту, який вам потрібно використати.
Умови і критерії полів, які мають задовольняти записи.
Необхідні обчислення.
Порядок сортування.
Ім'я, під яким потрібно зберегти запит.
Завдання 2.2
Створення запитів на зміну. Використання умов в запитах.
Багатотабличні запити
При використанні одразу кількох таблиць для запиту програма Access повертає результуючі дані як один об’єднаний набір записів в режимі таблиці. Перед створенням багатотабличного запиту в базі даних мають бути встановлені всі необхідні зв’язки між таблицями.
Індексація даних
Індекс – це внутрішня службова структура Microsoft Access, яка впорядковує записи таблиці згідно деякої схеми і відіграє роль “предметного” вказівника для вмісту певного поля (або полів). Аналогічно тому, як потрібні відомості можна знайти, подивившись в предметний вказівник і одразу відкривши потрібну сторінку, Access здійснює пошук даних за індексом, звертаючись безпосередньо до певного запису, а не продивляючись всю таблицю повністю.
Індекси пришвидшують пошук, сортування і фільтрацію даних в таблицях. Тому, якщо часто доводиться виконувати, наприклад, сортування по деякому у полю, варто створити для цього поля індекс.
Індекс не можна створити для полів з типом даних Поле МЕМО, Гіперпосилання або Поле Об’єкта OLE.
Щоб створити індекс поля, необхідно:
Відкрити таблицю в режимі конструктора.
Виділити поле, для якого створюється індекс.
В області Свойства поля на вкладці Общие клацнути в полі введення опції Индексированное поле. Справа рядка з’явиться кнопка розкривного списку.
Вибрати зі списку одну з опцій:
Зберегти таблицю, щоб зберегти внесені зміни.
В програмі Microsoft Access можна створювати індекси як по одному полю (простий індекс), так і по кільком полям (складений індекс). Складені індекси, наприклад, використовуються з метою прискорити одночасне сортування або пошук в кількох полях.
Для створення запита на вибірку в режимі конструктора в основному вікні бази даних активізують вкладку Запросы та кнопку Создать, у результаті чого на екрані з'являється вікно Новый запрос, в якому активізують команду Конструктор для самостійного створення запиту.
Після активізації кнопки ОК на екрані з'являється вікно Добавление таблицы, в якому вибирають потрібну таблицю (або таблиці).
Після визначення таблиць та полів запит має вигляд, показаний на малюнку.
Створений запит можна переглянути у вигляді таблиці, активізувавши команди Вид/Режим.
Після закінчення роботи із запитом його закривають. У результаті на екрані з'являється вікно, в якому треба зазначити ім'я запиту і папку, де він зберігається.
При обслуговуванні реальних баз даних повинні бути механізми, які автоматизують модифікацію великої кількості записів в таблиці. Таким механізмом Microsoft Access є запити на зміну, які дозволяють виконувати складні операції маніпулювання даними.
Основою для будь-якого запита на зміну є звичайний запит на вибірку, який визначає групу записів, над якими будуть виконані певні дії. Тому при створенні запиту на зміну спочатку необхідно побудувати запит на вибірку, а потім змінити в режимі конструктора його тип. Зробити це можна, вибравши тип запиту, який підходить, з допомогою кнопки Тип запроса панелі Конструктор запросов або з меню Запрос. Кожен з запитів відмічений власною піктограмою, яка доповнена знаком оклику. Оскільки запити на зміну модифікують інформацію, яка зберігається в таблицях, щоб попередити втрату даних, перед запуском будь-якого запиту такого типу варто скористатись кнопкою Режим таблицы, щоб переглянути ті зміни, які будуть зроблені в таблиці в результаті виконанні запиту.
Нижче перераховані чотири підтипи запитів на зміну, які підтримуються в Microsoft Access.
Запит на створення нової таблиці
Такий запит використовується для створення нової таблиці, значення якої відповідають значенням запиту на вибірку. Для виконання цієї дії запит активізують у режимі конструктора, з рядка головного меню вибирають команди Запрос/Создание таблицы. В результаті на екрані з'являється вікно Создание таблицы, в якому вводять ім'я нової таблиці та місце її розташування, після чого активізують кнопку ОК і закривають запит. Ця таблиця з'являється в об'єкті Таблицы під заданою назвою.
Запит на вилучення
Для створення запиту на вилучення значень із таблиці спочатку створюють новий запит, в який включають поля тієї таблиці (або таблиць), значення якої треба вилучити та вказують умову для вилучення записів.
Запит на додавання
Такий запит використовується для додавання нових записів. Записи із результуючого набору такого запиту можуть бути додані як в таблицю вже відкритої бази даних, так і в будь-яку іншу бази даних Microsoft Access.
Такий запит дозволяє оновити дані для групи однієї або кількох таблиць. В такому запиті можна вказати критерій відбору записів, які підлягають оновленню, а також нові значення для одного або кількох полів вихідної таблиці. Значення вказаних полів будуть змінені в тих записах вихідної таблиці, які відповідають записам результуючого набору. Наприклад, з допомогою такого запиту можна швидко задати нові значення цін на товари визначеного типу, знизивши їх на 20% в зв’язку з сезонним розпродажем.
Оператори та вирази Microsoft Access
Вираз — інструкція для виконання розрахунків, що складається з одного або кількох операторів, ідентифікаторів, літералів, функцій.
Оператори. Щоб створити вирази, використовують такі типи операторів:
Арифметичні - для роботи з числовими значеннями;
Арифметичні оператори для роботи з числовими значеннями:
|
Оператор |
Опис |
Приклад |
|
+ |
Додає два операнди |
Кількість + наСкладі |
|
- |
Віднімає два операнди |
ДатаЗамовлення- 10 |
|
- (унарний) |
Змінює знак |
-20 |
|
* |
Множить два операнди |
Ціна*знижка |
|
/ |
Ділить один операнд на інший |
Ціна/2 |
|
\ |
Повертає результат цілого ділення одного операнда на інший, при цьому десяткові значення округляються до цілих значень |
11\3, 4 (=3) |
|
Mod |
Повертає остачу при цілому діленні двох чисел |
11 Mod 3, 4 (=2) |
|
^ |
Підносить до степеня |
Ціна^2 |
Оператори присвоєння та порівняння (=, <, >, <=, >=, < >) – для присвоєння значення і порівняння двох значень при створенні правил перевірки, умов відбору записів; повертають логічні значення True, False. До цієї групи належать такі оператори:
Логічні оператори - для створення комбінації результатів двох або більше операцій порівняння. Ці оператори стосуються виразів, що повертають логічні значення True, False, Null. Такими операторами є:
Оператор конкатенації (&) — для створення комбінації рядка символів.
Літерали. Існують літерали таких типів:
числові – вводяться як ряд цілих і дробових чисел;
текстові – містять літери та цифри, при створенні їх беруть у подвійні лапки. Для об'єднання кількох лібералів використовують оператор конкатенації (&);
літерали дати/часу застосовуються для створення полів типу дата або час (12. 01. 99), при цьому автоматично додається знак # на початку і в кінці виразу (#12. 01. 99#).
Відбір записів за умовою та сортування в запитах
Умови відбору - це обмеження, що накладаються на запит або розширений фільтр для визначення записів, з якими він буде працювати.
Для створення умови відбору записів запит відкривають у режимі Конструктор.
Для визначення одного або кількох полів, не потрібних для виведення на екран дисплея, використовують перемикач у рядку Вывод на экран відповідного стовпця.
У полях типу Дата/время також можна використовувати умови, наприклад логічні (>, <, >=, <=, =), або оператор Between.
Якщо, наприклад, треба відібрати тільки ті значення запиту, які стосуються дати 2001 рік, то умова матиме такий вигляд:
Between #01. 01. 01#and#31. 12. 01#.
Для текстових полів використовують оператор Like. Якщо, наприклад, потрібно відібрати значення записів, у яких назви починаються з “К” чи “Ш”, то умова матиме такий вигляд:
Like"К*"оr"А*".
Нехай, наприклад, треба відібрати назви квітучих рослин. Для цього у полі Условие отбора під відповідним іменем поля (Цветение) з клавіатури вводять умову Да.
Нехай, наприклад, потрібно із таблиці Фотографии вилучити записи, в яких вказана дата до 1 вересня 2006 р. (з використанням запиту на видалення) Для цього створюють новий запит Фотографии Запрос, у рядок Поле із списку полів вибирають Дата съемки. В режимі конструктора вибрати тип запиту Удаление, після чого в рядку Условие отбора створюють потрібну умову: <#01.09.2006#
Для вилучення записів, що відповідають заданій умові, активізують кнопку Запуск на панелі інструментів або команду Запрос/3апуск. У результаті на екрані з'являється вікно з повідомленням про кількість вилучених записів, після чого активізують кнопку Да. Тепер у вказаній таблиці уже не буде записів, в яких вказана дата до 1 вересня 2006 р.
Завдання 2.3
Запити з параметрами та їх використання
Запит з параметрами — це запит, при виконанні якого, відображується у власному діалоговому вікні запрошення ввести дані, наприклад, умова для повернення записів або значення, що потрібно вставити в поле. Можна розробити запит, що виводить запрошення на введення декількох одиниць даних, наприклад, двох дат. Потім Microsoft Access може повернути всі записи, що приходяться на інтервал часу між цими датами.
Наприклад, потрібно створити запит для таблиці Покупки, який буде надавати інформацію про дату та вартість покупки за вказаною назвою рослини. Для цього потрібно створити запит, в якому вказати доступними таблиці Покупки та Растения, вивести поля ОбычноеНазвание, ДатаПокупки, Стоимость, в полі ОбычноеНазвание, в рядку Условие отбора в квадратних дужках вказати [Введіть назву рослини].
При виконанні цього запиту з'явиться вікно в якому потрібно вказати назву рослини, після чого виведеться потрібна інформація.
Завдання 2.4
Перехресні запити. Результати, отримані з допомогою перехресного запита, організовані в спеціальному форматі, який нагадує електронну таблицю. Вони групуються за двома наборами даних: перший виводиться в стовпці зліва (заголовки рядків), а другий – в верхньому рядку (заголовки стовпців). В ролі заголовків рядків і стовпців виступаються не назви, а значення полів. Для створення перехресного запита потрібно як мінімум три елемента: поле для визначення заголовків рядків, поле, яке визначає заголовки стовпців, і поле для вибору значень, над якими будуть безпосередньо виконуватись обчислення.
Перехресні запити можна створювати за допомогою Майстра або Конструктора.
Створення перехресного запита за допомогою Конструктора
За замовчуванням, заголовки стовпців сортуються за алфавітом або за числовим значенням. Якщо потрібно розташувати їх в іншому порядку, або потрібно обмежити кількість відображуваних заголовків стовпців, слід використовувати властивість Заголовки столбцов запиту.
Значення Значение може бути обрано тільки для одного поля.
Наприклад, потрібно створити перехресний запит, який надасть інформацію про вартість покупок, вказавши назву, тип рослини та кількість одиниць товару.
Щоб створити такий запит з допомогло конструктора, потрібно:
Вибрати створення запиту з допомогою Конструктора.
Вибрати таблиці Сведения заказа, Растения, Типы.
Додати в бланк запиту такі поля:
Вказати тип запиту - Перекрестный запрос. Після цього в бланку запиту з’явиться рядок Перекрестная таблица, в якому вказуються заголовки рядків, стовпців та значення. Вказати наступні значення:
Зберегти запит та запустити на виконання.
Створення перехресних запитів за допомогою майстра
В вікні бази даних вибрати значок Запросы в списку Объекти і натиснути кнопку Создать на панелі інструментів вікна бази даних. У діалоговому вікні Новый запрос вибрати у списку рядок Перекрестный запрос.
Слідуючи вказівкам Майстра, користувач створює запит. В останньому діалоговому вікні можна вибрати між запуском запита і відкриттям його в режимі конструктора. Якщо отриманий запит не відповідає вимогам, можна знову звернутися до майстра або внести зміни в запит у режимі конструктора.
Наприклад, потрібно створити запит, який обчислить суму, витрачену на покупки рослин у різні роки. Для цього потрібно:
Вибрати створення перехресного запита.
Цей запит можна створити на основі створеного раніше запиту Запит на основі кількох таблиць.
Вказати поле, значення якого буде використовувати у якості заголовків рядків. У нашому прикладі це буде поле ОбычноеНазвание.
Вказати поле, значення якого буде використовувати у якості заголовків стовпця. У нашому прикладі це буде поле ДатаПокупки.
Оскільки завдання полягає в підбитті підсумків по роках, то в наступному діалоговому вікні вказати інтервал Год.
Вказати комірку, де будуть показані обраховані значення. Для створення такої комірки потрібно вказати поле Стоимость та функцію Сумма.
Після цього в наступному вікні вибрати опцію запуску перегляду створеного запиту. Створений запит буде мати вигляд:
Запити для визначення записів, які повторюються. За заданим значенням поля (полів) таблиці створюється запит на вибірку, який дозволяє виявити в таблиці однакові записи. З допомогою цього запита можна перевірити на унікальність потенційне ключове поле існуючої таблиці.
Запити на пошук записів без підпорядкованих записів. Цей запит переглядає дані двох зв’язаних таблиць і відшукує всі записи „батьківської” таблиці, для якої в підпорядкованій таблиці відсутні відповідні записи. Наприклад, можна вияснити, які товари ще не були включені в замовлення або хто з клієнтів ще не здійснив покупок.
Завдання 3
Запитання для самоконтролю
Умова відбору записів
Умова на виведення тільки квітучих рослин
кщо потрібно впорядкувати записи, то у рядку Сортировка встановлюють курсор, розкривають список і вибирають тип сортування
Кнопки вибору полів запиту
Список доступних полів вибраної таблиці або запиту
Розкривний список для вибору таблиць та запитів