Практична робота 6
Тема. Створення запитів на зміну. Використання умов в запитах.
Мета. Дати поняття про багатотабличні запити, запити на зміну, видалення, додавання інформації, індексацію даних. Навчити створювати запити, додавати в запити умови.
Учні повинні знати: способи створення запитів, типи запитів.
Учні повинні вміти: створювати запити на зміну, видалення, додавання інформації, додавати в запити умови відбору.
Обладнання: ПК, програма Microsoft Access.
Теоретичні відомості
Багатотабличні запити
При використанні одразу кількох таблиць для запиту програма Access повертає результуючі дані як один об’єднаний набір записів в режимі таблиці. Перед створенням багатотабличного запиту в базі даних мають бути встановлені всі необхідні зв’язки між таблицями.
Індексація даних
Індекс – це внутрішня службова структура Microsoft Access, яка впорядковує записи таблиці згідно деякої схеми і відіграє роль “предметного” вказівника для вмісту певного поля (або полів). Аналогічно тому, як потрібні відомості можна знайти, подивившись в предметний вказівник і одразу відкривши потрібну сторінку, Access здійснює пошук даних за індексом, звертаючись безпосередньо до певного запису, а не продивляючись всю таблицю повністю.
Індекси пришвидшують пошук, сортування і фільтрацію даних в таблицях. Тому, якщо часто доводиться виконувати, наприклад, сортування по деякому у полю, варто створити для цього поля індекс.
Індекс не можна створити для полів з типом даних Поле МЕМО, Гіперпосилання або Поле Об’єкта OLE.
Щоб створити індекс поля, необхідно:
Відкрити таблицю в режимі конструктора.
Виділити поле, для якого створюється індекс.
В області Свойства поля на вкладці Общие клацнути в полі введення опції Индексированное поле. Справа рядка з’явиться кнопка розкривного списку.
Вибрати зі списку одну з опцій:
Да…/Совпадения не допускаются, якщо вміст поля повинен бути унікальним для кожного запису;
Да…/Допускаються совпадения, якщо значення поля повинен можуть повторюватися;
Опція Нет припиняє створення індексованого поля.
Зберегти таблицю, щоб зберегти внесені зміни.
В програмі Microsoft Access можна створювати індекси як по одному полю (простий індекс), так і по кільком полям (складений індекс). Складені індекси, наприклад, використовуються з метою прискорити одночасне сортування або пошук в кількох полях.
Для створення запита на вибірку в режимі конструктора в основному вікні бази даних активізують вкладку Запросы та кнопку Создать, у результаті чого на екрані з'являється вікно Новый запрос, в якому активізують команду Конструктор для самостійного створення запиту.
Після активізації кнопки ОК на екрані з'являється вікно Добавление таблицы, в якому вибирають потрібну таблицю (або таблиці).
Після визначення таблиць та полів запит має вигляд, показаний на малюнку.
Створений запит можна переглянути у вигляді таблиці, активізувавши команди Вид/Режим.
Після закінчення роботи із запитом його закривають. У результаті на екрані з'являється вікно, в якому треба зазначити ім'я запиту і папку, де він зберігається.
При обслуговуванні реальних баз даних повинні бути механізми, які автоматизують модифікацію великої кількості записів в таблиці. Таким механізмом Microsoft Access є запити на зміну, які дозволяють виконувати складні операції маніпулювання даними.
Основою для будь-якого запита на зміну є звичайний запит на вибірку, який визначає групу записів, над якими будуть виконані певні дії. Тому при створенні запиту на зміну спочатку необхідно побудувати запит на вибірку, а потім змінити в режимі конструктора його тип. Зробити це можна, вибравши тип запиту, який підходить, з допомогою кнопки Тип запроса панелі Конструктор запросов або з меню Запрос. Кожен з запитів відмічений власною піктограмою, яка доповнена знаком оклику. Оскільки запити на зміну модифікують інформацію, яка зберігається в таблицях, щоб попередити втрату даних, перед запуском будь-якого запиту такого типу варто скористатись кнопкою Режим таблицы, щоб переглянути ті зміни, які будуть зроблені в таблиці в результаті виконанні запиту.
Нижче перераховані чотири підтипи запитів на зміну, які підтримуються в Microsoft Access.
Запит на створення нової таблиці
Такий запит використовується для створення нової таблиці, значення якої відповідають значенням запиту на вибірку. Для виконання цієї дії запит активізують у режимі конструктора, з рядка головного меню вибирають команди Запрос/Создание таблицы. В результаті на екрані з'являється вікно Создание таблицы, в якому вводять ім'я нової таблиці та місце її розташування, після чого активізують кнопку ОК і закривають запит. Ця таблиця з'являється в об'єкті Таблицы під заданою назвою.
Запит на вилучення
Для створення запиту на вилучення значень із таблиці спочатку створюють новий запит, в який включають поля тієї таблиці (або таблиць), значення якої треба вилучити та вказують умову для вилучення записів.
Запит на додавання
Такий запит використовується для додавання нових записів. Записи із результуючого набору такого запиту можуть бути додані як в таблицю вже відкритої бази даних, так і в будь-яку іншу бази даних Microsoft Access.
Запит на оновлення
Такий запит дозволяє оновити дані для групи однієї або кількох таблиць. В такому запиті можна вказати критерій відбору записів, які підлягають оновленню, а також нові значення для одного або кількох полів вихідної таблиці. Значення вказаних полів будуть змінені в тих записах вихідної таблиці, які відповідають записам результуючого набору. Наприклад, з допомогою такого запиту можна швидко задати нові значення цін на товари визначеного типу, знизивши їх на 20% в зв’язку з сезонним розпродажем.
Оператори та вирази Microsoft Access
Вираз — інструкція для виконання розрахунків, що складається з одного або кількох операторів, ідентифікаторів, літералів, функцій.
Оператори. Щоб створити вирази, використовують такі типи операторів:
Арифметичні - для роботи з числовими значеннями;
Арифметичні оператори для роботи з числовими значеннями:
Оператори присвоєння та порівняння (=, <, >, <=, >=, < >) – для присвоєння значення і порівняння двох значень при створенні правил перевірки, умов відбору записів; повертають логічні значення True, False. До цієї групи належать такі оператори:
Is - разом із Null визначає, яким є значення поля - нульовим чи ненульовим (Not Null);
Like - визначає, чи починається символьне значення з одного або кількох символів. При цьому застосовується шаблон з одного чи кількох символів * або ? (наприклад, Like«A*»);
In — визначає, чи є значення із поданого списку. Наприклад, In (6; 10) або In («лд»; «бв»);
Between — визначає, чи входить число в поданий діапазон значень (наприклад, Between 10 and 15).
Логічні оператори - для створення комбінації результатів двох або більше операцій порівняння. Ці оператори стосуються виразів, що повертають логічні значення True, False, Null. Такими операторами є:
And — логічне І;
Оr — логічне АБО;
Not — логічне НЕ.
Оператор конкатенації (&) — для створення комбінації рядка символів.
Літерали. Існують літерали таких типів:
числові – вводяться як ряд цілих і дробових чисел;
текстові – містять літери та цифри, при створенні їх беруть у подвійні лапки. Для об'єднання кількох лібералів використовують оператор конкатенації (&);
літерали дати/часу застосовуються для створення полів типу дата або час (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 р.
Хід роботи
Створити запит на створення нової таблиці Классификация, в якій будуть вказані назва, тип та зображення рослини.
Послідовність дій:
Вибрати створення запиту в режимі конструктора.
Додати в запит таблиці Растения та Типы.
Додати в запит поля (вибрати з розкривного списку або перетягти з їх допомогою миші): Обычное Название, Название Типа, Изображение.
Вказати тип запита: створення нової таблиці. В діалоговому вікні, яке відкриється, дати назву таблиці та зберегти її в поточній базі даних.
Зберегти запит, дати назву Создание новой таблицы.
Запустити запит на виконання, перевірити наявність створеної таблиці.
Створити запит на видалення записів з таблиці Растения, де вказано рослини, які не цвітуть.
Послідовність дій:
Оскільки дані при виконанні цього запита безповоротно знищаться, слід створити копію таблиці Растения, давши їй назву Цветущие растения.
Послідовність створення запиту аналогічна, перенести в запит всі поля.
Вказати тип запиту: на видалення.
В полі Условие отбора для поля Цветение вказати Нет.
Зберегти запит, давши назву Запрос на удаление, запустити запит на виконання.
Перевірити результат виконання запиту.
Створити запит на оновлення записів в таблиці Растения, змінивши дані про частоту поливу на запис “не заливати” в тих записах, де в полі Цветение вказано Да.
Послідовність дій:
Оскільки дані при виконанні цього запита безповоротно знищаться, слід створити копію таблиці Растения, давши їй назву Растения1.
Послідовність створення запиту аналогічна, перенести в запит поля Частота полива та Цветение.
Вказати тип запиту: на оновлення.
В полі Условие отбора для поля Цветение вказати Да.
В полі Обновление для поля Частота полива вказати Не заливати.
З
берегти запит, давши назву Запрос на обновление, запустити запит на виконання.Перевірити результат виконання запиту.
Створити запит на оновлення записів в таблиці Сведения заказа, додавши поле Стоимость, в якому буде обчислюватися сума залежно від кількості одиниць товару та ціни за одиницю товару.
П
ослідовність дій:
Відкрити таблицю Сведения заказа в режимі Конструктора, додати поле Стоимость, вказавши тип даних Денежный.
Створити запит Перерахунок, вказати доступну таблицю Сведения заказа, перенести в запит поле Стоимость.
Вказати тип запиту: на оновлення.
В полі Условие отбора для поля Стоимость записати вираз для обчислення значення поля: [Количество]*[ЦенаЕдиницыТовара].
Зберегти запит, давши назву Перерахунок, запустити запит на виконання.
Перевірити результат виконання запиту, відкривши таблицю Сведения заказа.
Створити запит на оновлення поля Стоимость в таблиці Покупки.
Послідовність дій:
Створити запит Перерахунок таблиці Покупки, вказати доступними таблиці Сведения заказа та Покупки, перенести в запит поле Стоимость з таблиці покупки.
Вказати тип запиту: на оновлення.
В полі Обновление записати вираз для обчислення значення поля:
[Сведения заказа]![Количество]*[Сведения заказа]![ЦенаЕдиницыТовара].Зберегти запит, давши назву Перерахунок таблиці Покупки, запустити запит на виконання.
Перевірити результат виконання запиту, відкривши таблицю Покупки.
Створити самостійно запит на основі кількох таблиць, в якому відібрати записи за умовою та відсортувати записи (див. малюнок). Назвати запит “Запит на основі кількох таблиць”
Контрольні запитання
Що таке запит на вибірку? Як можна створити запит на вибірку?
Що таке запити на зміну?
Які є типи запитів на зміну?
Для чого потрібна індексація даних?
Які використовується типи операторів в СУБД Access?
Що таке умови відбору?
Де вказуються умови відбору?