Запити 11 клас
Запит дозволяє вибрати необхідні дані з однієї чи кількох зв'язаних таблиць, провести обчислення та одержати результат у вигляді таблиці, поновити дані в таблиці, створити нову таблицю, додати чи видалити записи. Запити також використовують як джерело даних для форм і звітів.
Усі запити за результатами дії поділяють на дві групи: 1.Запити, за результатами виконання яких створюються нові таблиці і при цьому не вносяться зміни до джерел запитів, тобто до вихідних таблиць бази даних, її форм та звітів. 2.Запити на дії, результат виконання яких передбачає зміну даних, що розміщуються у таблицях бази даних, які є джерелом запиту.
1. Запити, за результатами виконання яких створюються нові таблиці і при цьому не вносяться зміни до джерел запитів, тобто до вихідних таблиць бази даних, її форм та звітів. До цієї групи належать такі запити: Запит на вибірку, при застосуванні якого виконується відбір деяких даних з таблиці чи декількох зв'язаних таблиць. Крім умов на вибірку можна задавати нові поля в таблиці, які будуть створені шляхом здійснення обчислень з даними, що зберігаються в одній чи декількох таблицях конкретної бази даних. На відміну від фільтрів, які працюють лише в межах однієї таблиці і відображають у результаті всі її поля, запити дозволяють вибирати довільну кількість полів, причому в одному запиті можна розміщувати дані з різних зв'язаних таблиць.
Запит з параметром є різновидом запиту на вибірку. Його доцільно використовувати при виконанні розрахунків в обчислювальних полях чи при формуванні критеріїв відбору потрібних даних тоді, коли заздалегідь не відоме значення деякого параметра, що входить до виразу для обчислення або умови відбору. При кожному виконанні запиту з параметром користувачеві потрібно з клавіатури вводити конкретне значення параметра, яке підставляється до обчислювального виразу. Кількість параметрів може бути довільною. Перехресний запит використовується в тому випадку, коли потрібно знайти спільні дані, які розміщуються в декількох зв'язаних таблицях
2. Запити на дії, результат виконання яких передбачає зміну даних, що розміщуються у таблицях бази даних, які є джерелом запиту. До цієї групи належать такі запити: Запит на створення нової таблиці на відміну від запитів на вибірку, які створюють таблицю лише в оперативній пам'яті, створюється нова таблиця, яка зберігається у базі даних. Запит на оновлення дозволяє автоматично змінювати значення деяких полів таблиці. Після встановлення типу запиту на оновлення у бланку запиту з'являється додатковий рядок Оновлення, у якому треба задати, які поля повинні змінюватись і яким чином. Після виконання запиту дані таблиці- джерела будуть автоматично змінені, і повториний запуск запиту може призвести до небажаних змін.
Запит на доповнення використовується в тому випадку, коли треба об'єднати однорідні дані з двох таблиць однакової структури. У процесі виконання запиту на доповнення спочатку створюється запит-вибірка на основі тієї таблиці, з якої треба перенести дані до іншої, після чого потрібно вказати назву таблиці, до якої необхідно додати відповідні записи. Запит на знищення дозволяє знищувати в таблиці-джерелі дані, що відповідають заданій умові. Такий запит є одноразовим, оскільки після знищення даних, що відповідають умові, повторний запуск такого запиту не знайде записів, які відповідають заданій умові щодо знищення даних.
Особливістю запитів на дії є те, що їх створення і виконання складається з двох етапів : 1.спочатку користувач створює запит- вибірку, що містить потрібні поля та записи, які відповідають певній умові, 2.перетворення запиту на дію та виконує ще раз. Це дозволяє уникнути помилок, оскільки результати роботи запитів на дії відмінити неможливо.
У середовищі СУБД MS Access об'єкт Запит має три режими роботи. Конструктор надає можливість створювати структуру запиту або вносити зміни і доповнення в раніше створену структуру. SQL дозволяє за допомогою вказівок мови SQL (аналогічно до використання мови програмування) будувати структуру запиту більш складної конструкції. Таблиця відображає у вигляді таблиці результат виконання запиту згідно зі сформованою структурою.
Для побудови запиту необхідно у вікні бази даних вибрати категорію Запити та клацнути на кнопці Створити. У вікні, що відкрилось, потрібно вибрати один із способів для створення нового запиту:
Створення запиту на вибірку за допомогою Майстра За допомогою Майстра можна створити лише прості запити на вибірку, які не містять умов відбору та обчислювальних полів. При виборі режиму Створення запиту за допомогою майстра відображається діалогове вікно, в якому слід з однієї чи кількох зв'язаних таблиць, назви яких можна вибрати з списку, що розкривається, вибрати поля, необхідні для формування запиту, та перенести їх до області Обрані поля у потрібній послідовності. На останньому кроці роботи Майстра залишається лише задати назву запиту. Усі інші параметри запитів можна налаштувати в режимі конструктора
Завдання. У базі даних Магазини створити за допомогою Майстра запит Продажі, що містить такі поля: Магазин з таблиці Магазини, Назва_фільму та Ціна_за__одиницю з таблиці Фільми та Кількість _продажів з таблиці Підсумок 1.Відкрийте файл Магазини. mdb, 2.У списку категорій об'єктів виберіть Запити, а потім Створення запиту за допомогою майстра. 3.У вікні Створення простих запитів у списку Таблиці й запити виберіть таблицю Магазини. 4.В області Доступні поля виділіть поле Магазин та натисніть кнопку [>], щоб перемістити одне виділене поле до області Обрані поля. У списку Таблиці й запити виберіть таблицю Фільми та перемістіть поля Назва_фільму та Ціна_за_одиницю до області Обрані поля. У списку Таблиці й запити виберіть таблицю Підсумок та перемістіть до області Обрані поля поле Кількість_продажів. Натисніть кнопку Далі. 5.На наступному кроці майстра виберіть режим Детальний (виведення кожного поля для кожного запису). Натисніть кнопку Далі. 6.У рядку введення введіть назву нового запиту Продажі. Натисніть кнопку Готово. 7.Натисніть кнопку Вигляд щоб відобразити результати виконання запиту на вибірку у вигляді таблиці. Переконайтесь, що знайдені записи нової таблиці містять лише вказані поля.
Зміна структури запиту в режимі конструктора Бланк конструктора запитів поділено на дві частини: у верхній частині вказано таблиці, на основі яких створено запит, у нижній частині задаються всі параметри запиту, тобто його структура (мал. 233).
Під час редагування структури запиту в режимі конструктора можна видаляти деякі поля чи додавати інші з таблиці, що взяті за основу, змінювати послідовність розміщення полів, задавати порядок сортування за деякими полями, вказувати умови відбору записів, додавати нові поля, яких немає в таблицях бази даних, але які можуть бути створені в результаті обчислень, виконувати підсумкові обчислення з використанням групових операцій, які можна застосувати до полів, значення в яких повторюються
Завдання. В режимі конструктора внести зміни в структуру запиту Продажі в базі даних Магазини, щоб відображались лише записи про фільми, продані в магазині Стиль, впорядковані за зростанням по кількості проданих копій 1.Відкрийте вікно бази даних Магазини. Зі списку категорій об'єктів виберіть Запити. Клацніть правою клавішею мишки на запиті Продажі та виберіть з контекстного меню вказівку Конструктор 2.На бланку запиту для поля Магазин у рядку Умова відбору введіть значення Стиль. 3.Для поля Кількість_продажів у рядку Сортування виберіть зі списку можливих значень за зростанням. 4.Натисніть кнопку Вигляд, щоб відобразити результати виконання запиту на вибірку у вигляді таблиці. Переконайтесь, що знайдені записи відповідають умові. 5.Поверніться до режиму конструктора. На бланку запиту для поля Магазин у рядку Умова відбору замість слова Стиль введіть таке: [Введіть назву магазину]. 6.Натисніть кнопку Вигляд, у діалоговому вікні введіть назву Стиль та натисніть клавішу Enter. Порівняйте одержаний результат із попереднім. 7.Закрийте вікно запиту та збережіть внесені зміни.
Вигляд вікна побудови запиту в режимі Конструктор
Як змінити тип запиту? Незалежно від того, запит якого типу слід створити, спочатку створюється запит на вибірку. Перш ніж змінити тип запиту на інший, бажано відкрити запит у режимі таблиці і переконатися, що умови відбору задано правильно, тобто за результатами виконання запиту відбираються потрібні записи ті, що відповідають умові завдання. Далі слід повернутися до режиму конструктора запиту та в меню Записи вибрати потрібний тип.
Використання обчислень. Нове поле повинно містити ім'я та вираз для його обчислення, відокремлені символом >. Вирази для обчислень можна створювати вручну із врахуванням правил їх запису, а можна скористатись Будівником виразів, застосування якого дозволяє спростити побудову потрібного виразу шляхом вибору потрібних імен полів, операцій та функцій з переліку запропонованих, що вбудовані у середовищі MS Access. Це також дозволяє уникнути синтаксичних помилок при запису виразу. Викликається Будівник виразів у режимі конструктора за допомогою вибору з контекстного меню вказівки Побудувати або натиснення відповідної кнопки на панелі інструментів. При цьому текстовий курсор має бути встановлений у позицію, починаючи з якої слід ввести вираз.
Використання обчислень. Вікно Будівника виразів складається з чотирьох областей. У верхній області відображається сформований вираз, у який можна вручну внести корективи. Три нижніх області містять списки об'єктів бази даних, які можуть бути використані у виразі, операцій та функцій, констант та загальних виразів у ієрархічному вигляді ліва область відповідає за загальні розділи (список об'єктів бази даних та вбудовані функції), середня відображає підрозділи вибраного розділу, а права конкретні значення. Для вставлення вибраного елемента у вираз треба двічі клацнути на ньому мишкою або натиснути кнопку Вставити у вікні Будівника виразів.
Для зручності створення виразів знаки арифметичних операцій, логічні операції та деякі інші символи, які найбільш часто використовуються, винесені у вікно Будівника виразів у вигляді окремих кнопок. Особливістю роботи будівника виразів є те, що автоматично не відслідковується коректність створеного виразу, а лише відображаються підказки щодо можливого типу та порядку розміщення елементів, які користувач після створення виразу має знищувати вручну. Якщо вираз будується в новому полі, йому буде надано ім'я Виразі тощо, тому треба або задати ім'я поля до початку введення виразу, або змінити стандартне (наприклад, Виразі) після завершення його побудови. Використання обчислень.
Завдання. В запиті Продажі бази даних Магазини створити нове поле Виторг, значення якого будуть визначені шляхом обчислень як добуток значень полів Ціна_за_одиницю та Кількість_продажів. 1.Відкрийте вікно бази даних Магазини та відкрийте запит Продажі в режимі конструктора. 2.У наступному стовпці після останнього поля в рядку Поле введіть ім'я нового поля Виторг та символ «:». 3.Натисніть кнопку Побудувати на панелі інструментів. 4.У вікні Будівника виразів у лівій нижній області двічі клацніть на назві Запити та виберіть запит Продажі. 5.Виділіть поле Ціна_за_одиницю та натисніть кнопку Вставити. Введіть символ «*», виділіть поле Кількість_продажів та натисніть кнопку Вставити. 6.У верхній області вікна Будівника виразів виділіть підказку «Вираз», що автоматично з'явилась після імені поля, та натисніть клавішу Delete. 7.У вікні Будівника виразів натисніть кнопку ОК. 8.На бланку конструктора запиту для поля Виторг встановіть прапорець у рядку Виведення на екран. 9.Перегляньте результати виконання запиту в режимі таблиці. 10.Закрийте вікно запиту та збережіть внесені зміни.
Вигляд вікна Будівник виразів
Як можна в запитах на вибірку використовувати групові операції? За допомогою запитів можна не лише знаходити в таблицях записи, які відповідають певним умовам, але й обчислювати підсумкові значення. Для цього в конструкторі запитів передбачено можливість використання групових операцій, які за замовчуванням не відображаються на бланку запиту. Якщо в меню Вигляд або контекстному меню вибрати вказівку Групові операції, то у бланку запиту з'явиться додатковий рядок з такою ж назвою. При цьому відбувається автоматичне групування значень кожного поля, але результат не відображається. Якщо ж потрібно виконати підсумкові обчислення для утворених груп, то у відповідному полі замість значення Групування треба застосувати одну з дев'яти можливих групових операцій {табл. 23.1) або додаткові можливості Вираз та Умова.
НазваПризначення Sum Обчислює суму всіх значень даного поля в кожній групі. Застосовується лише для числових та грошових полів Avg Обчислює середнє арифметичне всіх значень даного поля в кожній групі. Застосовується лише для числових та грошових полів Min Визначає найменше значення в межах кожної групи. Застосовується для будь- яких полів, крім логічних та типу MEMO. У випадку текстового вмісту виводиться найменше символьне значення незалежно від регістру Max Визначає найбільше значення в межах кожної групи. Застосовується для будь- яких полів, крім логічних та типу MEMO. У випадку текстового вмісту виводиться найбільше символьне значення незалежно від регістру Count Визначає кількість записів, у яких значення даного поля відмінне від Null (порожні). Але при використанні конструкції Count (*) можна підрахувати число всіх записів, включно з «нульовими» Stdev Підраховує статистичне стандартне відхилення для всіх значень даного поля в кожній групі. Функція застосовується лише для числових та грошових полів. Якщо в групі менше двох записів, повертається значення Null Var Підраховує статистичну дисперсію для всіх значень даного поля в кожній групі. Застосовується лише для числових та грошових полів. Якщо в групі менше двох записів, повертається значення Null FirstВизначає перше значення цього поля в групі LastВизначає останнє значення цього поля в групі
Завдання. Створити запит Підсумковий, за допомогою якого визначити загальну кількість проданих фільмів та загальний виторг по кожному магазину окремо. 1.Відкрийте базу даних Магазини. У списку категорій об'єктів виберіть Запити. 2.Відкрийте запит Продажі в режимі конструктора та видаліть умову для поля Магазин. Закрийте вікно запиту та збережіть внесені зміни. 3.Виберіть режим Створення запиту за допомогою майстра. У списку Таблиці й запити виберіть запит Продажі. 4.Перемістіть поля Магазин, Кількість_продажів та Виторг до області Обрані поля. Натисніть кнопку Далі. 5.На останньому кроці Майстра введіть назву запиту Підсумковий та натисніть кнопку Готово. 6.Перегляньте створений запит у режимі таблиці. 7.Відкрийте запит Підсумковий у режимі конструктора. Виконайте вказівку Вигляд/Групові операції. 8.У бланку запиту в рядку Групова операція для поля Магазин залиште значення Групування, а для полів Кількість_продажів та Виторг зі списку можливих типів операцій оберіть функцію Sum. 9.Перегляньте результати виконання запиту в режимі таблиці. Поясніть, чим відрізняється запит з використанням групових операцій від аналогічного запиту, створеного за допомогою Майстра. 10.Закрийте запит та збережіть внесені зміни.