Запити у базах даних
1. Необхідність запитів
Припустимо, що на великому підприємстві є величезна база даних «Кадри», що містить найдокладніші відомості про кожного співробітника. Крім формальної інформації база може містити і конфіденційну, наприклад відомості про заробітну плату. Вся ця інформація зберігається в базових таблицях. Працювати з базою даних «Кадри» можуть різні підрозділи підприємства, і усім їм потрібні різні дані. Не все те, що дозволено знати службі безпеки підприємства, повинно бути доступно головному лікарю, і навпаки. Тому доступ користувачів до базових таблиць закривають.
Для доступу до даних є інший, набагато більш гнучкий і зручний засіб - запити. Для однієї і тієї ж таблиці можна створити багато різних запитів, кожний із який зможе добувати з таблиці лише малу частину інформації, але саме ту частину, що у даний момент необхідна. У співробітника бухгалтерії повинний бути запит, що дозволить визначити скільки днів у році через хворобу був відсутнім той або інший працівник, але в нього не повинно бути запиту, що дозволяє дізнатись, чим він хворів і де лікувався, а в головного лікаря такий запит бути повинний.
У результаті роботи запиту з загальної вихідної бази формується результуюча таблиця, що містить частину загальної інформації, що відповідає запиту.
Важливою властивістю запитів є те, що при створенні результуючої таблиці можна не тільки вибирати інформацію з бази, але й обробляти її. При роботі запиту дані можуть упорядковуватися (сортуватися), фільтруватися (відсіюватися), об'єднуватися, розділя-тися, змінюватися, і при цьому ніяких змін у базових таблицях може не відбуватися.
Результати обробки позначаються тільки на змісті результуючої таблиці, а вона має тимчасовий характер, і іноді її навіть називають моментальним знімком.І ще одною цінною властивістю запитів є їхня можливість виконувати підсумкові обчислення. Запит може не тільки видати результуючу таблицю, але і знайти, наприклад, середнє (найбільше, найменше, сумарне і т.п.) значення по якомусь полю.
2. Запити на вибірку
Існує чимало різних видів запитів, але найпростіші з них і, до того ж, використовувані найбільше часто - це запити на вибірку. З них і прийнято починати знайомство зі створенням запитів. Мета запиту на вибірку полягає в створенні результуючої таблиці, у якій відображаються тільки потрібні за умовою запиту дані з базових таблиць.
Як і інші об'єкти Access 9х, запити можна створювати автоматично за допомогою “Мастера” або вручну. І, як звичайно, на етапі навчання краще не користуватися “Мастeром”, щоб відчути роботу з запитами "кінчиками пальців".
Для створення запитів до баз даних існує спеціальна мова запитів. Вона називається SQL (Structured Query Language - структурована мова запитів). На щастя, ті, хто користуються СКБД Access 9х, можуть дозволити собі не вивчати цю мову. Замість нього в Access 9х є простий засіб, що називається бланком запиту за зразком. З його допомогою можна сформувати запит простими прийомами, перетягуючи елементи запиту між вікнами.
Вибір базових таблиць для запиту
1.Створення запиту до бази починається з відкриття вкладки “Запросы” діалогового вікна “База данных” і натиснення лівої клавіші миші на кнопці “Создать”.
2.У діалоговому вікні “Новый запрос” задають ручний режим створення запиту вибором пункту “Конструктор”.
3.Створення запиту в режимі “Конструктора” починають із вибору тих таблиць бази, на яких буде заснований запит.
4.Вибір таблиць виконують у діалоговому вікні “Добавление таблицы”. У ньому відображаються всі таблиці, наявні в базі.
5.Обрані таблиці заносять у верхню половину бланка “запиту за зразком” натисненням лівої клавіші миші на кнопці “Добавить”.
6.У вікні “Добавление таблицы” зверніть увагу на наявність трьох вкладок: “Таблицы”, “Запросы”, “Запросы и таблицы”. Вони говорять про те, що запит не обов'язково створювати тільки на основі таблиць. Якщо раніше вже був створений запит, то новий запит може основуватись і на ньому.
3. Бланк запиту за зразком
Бланк запиту за зразком - зручний засіб створення запитів. Напевно, воно в чималому ступені сприяє успіху, що СКБД Ассеss 9х має серед споживачів.
1. Бланк запиту за зразком має дві панелі. На верхній панелі розташовані списки полів тих таблиць, на яких засновується запит.
2. Рядки нижньої панелі визначають структуру запиту, тобто структуру результуючої таблиці, у якому будуть міститися дані, отримані за результатами запиту.
3. Рядок “Поле” заповнюють перетягуванням назв полів із таблиць у верхній частині бланка. Кожному полю майбутньої результуючої таблиці відповідає один стовпець бланка запиту за зразком.
4. Рядок Ім'я таблиці заповнюється автоматично при перетягуванні поля.
5. Якщо натиснути на рядок “Сортировка”, з'явиться кнопка списку, що розкривається, який містить види сортування. Якщо призначити сортування по якомусь полю, дані в результуючій таблиці будуть відсортовані по цьому полю.6. Бувають випадки, коли поле повинне бути присутнім у бланку запиту за зразком, але не повинно відображатися в результуючій таблиці. У цьому випадку можна заборонити його виведення на екран, скинувши відповідний прапорець.
7. Найцікавіший рядок у бланку запиту за зразком називається “Условие отбора”. Саме тут і записують ті критерії, по якому вибирають запису для включення в результуючу таблицю. По кожному полю можна створити свою умову відбору. У нашому прикладі призначені дві умови відбору: по вазі гравця (більш 80 кг) і по зросту (менше 190 см).
8. Запуск запиту виконують натисненням лівої клавіші миші на кнопці “Вид”. При запуску утвориться результуюча таблиця.
9. Щоб вийти з результуючої таблиці і повернутися до створення запиту в бланку запиту за зразком, потрібно ще раз натиснути на кнопку “Вид”.
4. Запити з параметром
Вище ми розглянули роботу запиту, що вибирає воротарів футбольних клубів, чий зріст менше заданого, а вага більше заданого. І максимальний зріст, і мінімальна вага були жорстко введені в бланк запиту за зразком, і звичайний користувач бази, що не мав відношення до її створення, не може змінити ці параметри.
У багатьох випадках користувачу треба надати можливість вибору того, що він хоче знайти в таблицях бази даних. Для цього існує спеціальний вид запиту - запит із параметром.
1. Припустимо, що в базі даних є таблиця, у якій містяться всі результати чемпіонатів світу з футболу. Наша задача: створити запит, за допомогою якого користувач може визначити, у якому році та або інша команда займала перше місце, причому вибір цієї команди - його особиста справа.
2. Для цієї мети служить спеціальна команда мови SQL, що виглядає так: LIКЕ [...]. У квадратних скобках можна записати будь-який текст, звернений до користувача, наприклад:
LIКЕ [Введіть назву країни]
3. Команду LIКЕ треба помістити в рядку “Условие отбора” і в те поле, по якому робиться вибір. У нашому випадку це стовпець збірних, що займали перші місця в чемпіонатах світу з футболу.
4. Після запуску запиту відкривається діалогове вікно, у якому користувачу пропонується ввести параметр.
5. Якщо в якості параметра ввести слово “Бразилія”, те видається результуюча таблиця, що містить запису по тим чемпіонатам, коли збірна Бразилії ставала чемпіоном.
6. Якщо в якості параметра ввести слово “Італія”, то результуюча таблиця буде іншою.
Зрозуміло, у нашій невеликій таблиці і без запиту неважко знайти збірні, що займали призові місця. Але без запиту не обійтися, якщо в базі містяться сотні тисяч записів, причому розташовані в різних таблицях.
5. Підсумкові запити
Запити дозволяють не тільки відбирати потрібну інформацію з таблиць і обробляти її шляхом створення нових полів, що обчислюються, але і робити так називані підсумкові обчислення.
Прикладом підсумкового обчислення може служити сума усіх значень в якійсь групі записів або їхнє середнє значення, хоча крім суми і середнього значення існують і інші підсумкові функції. Оскільки підсумкові функції для одного запису не мають змісту й існують тільки для групи записів, те попередньо записи треба згрупувати по певній ознаці.
1. Розглянемо роботу салону, що займається продажем потриманих автомобілів. Результати роботи салону за останній тиждень містяться в таблиці. У ній можна виділити декілька груп по різній ознаці. Записи можна згрупувати по моделях автомобілів (ВАЗ - окремо і БМВ - окремо) або по року випуску (1989, 1993 і т.д.). Для кожній із груп можна провести підсумкове обчислення по полю “Цена”.
2. Підсумкові запити створюють на основі відомого нам бланка запиту за зразком, тільки тепер у ньому з'являється додатковий рядок – “Группировка”.
3. Для введення цього рядка в бланк треба натиснути на кнопку “Групповые операции” на панелі інструментів програми Ассеss 9х.
Далі усі відбувається дуже просто.
4. У тих полях, по яких робиться групування, треба установити (або залишити) функцію “Группировка”.
5. У тих полях, по яких варто провести підсумкове обчислення, треба в рядку “Группировка” розкрити список і вибрати одну з декількох підсумкових функцій.
6. Натиснення лівої клавіші миші на кнопці “Вид” запускає запит і видає результуючу таблицю з необхідними підсумковими даними.
7. У рядку “Группировка” можна зазначити лише одну підсумкову функцію. А як бути, якщо треба знайти і суму, і середнє, і максимальне значення, і ще щось? Розв'язок простий: те саме поле можна включити в бланк запиту за зразком декілька разів.
6. Обчислення в запитах
Подивіться на таблицю результативності команд у фінальних іграх чемпіонатів світу з футболу. У ній є дані про те, скільки ігор зіграла та чи інша команда, є і дані про те, скільки вона забила м'ячів, але немає таких відомостей, як, наприклад, середнє число голів, що забиваються в одній зустрічі. Однак таке поле можна створити за допомогою запиту. Поле, вміст якого є результатом розрахунку по вмісту інших полів, називається полем, що обчислюється .Перед тим , як ми навчимося створювати і використовувати поля,що обчислюються, варто звернути увагу на те, що поле,що обчислюється, існує тільки в результуючій таблиці. У вихідних таблицях таке поле не створюється, і при роботі звичайного запиту таблиці не змінюються. Чи не правда, це дуже розумно? Кожний, хто звертається до бази, може за допомогою запитів як завгодно маніпулювати даними й одержувати будь-які результати, але при цьому вихідні таблиці залишаються незмінно однаковими для всіх користувачів.
1. Для створення запиту, що робить обчислення, служить той же самий бланк запиту за зразком. Різниця тільки в тому, що в одному із стовпців замість імені поля записують формулу. У формулу входять поміщені в квадратні дужки назви полів, що беруть участь у розрахунку, а також знаки математичних операцій, наприклад такі:
Результативність : [Забита] / [Гри]
тобто, назва нового поля : Поле_1 / Поле_2
2. У вузький стовпець непросто записати довгу формулу, але якщо натиснути комбінацію клавіш SHIFT+F2, то відкривається допоміжне діалогове вікно, що називається “Область ввода”. У ньому можна ввести яку завгодно довгу формулу, а потім натисненням лівої клавіші миші на кнопці ОК перенести її в бланк запиту за зразком.
3.Якщо включити відображення поля, що обчислюється, результати розрахунків будуть видаватися в результуючій таблиці.
4.Ніщо не перешкоджає зробити поле,що обчислюється, полем сортування, щоб не тільки одержувати нові результати, але й аналізувати їх. Подивіться, як змінюється положення російської збірної після сортування по полю, що обчислюється.
7. Запити на зміну
Вище ми говорили про те, що усі види запитів на вибірку створюють тимчасові результуючі таблиці. Базові таблиці при цьому не змінюються. Проте, спеціально для розробників баз даних існує особлива група запитів, що називаються запитами на зміну. Вони дозволяють автоматично створювати нові таблиці або змінювати вже наявні. Логіка використання запитів на зміну така:
• створюється запит на вибірку, що відбирає дані з різних таблиць або самий створює нові дані шляхом обчислень;
• після запуску запиту утвориться тимчасова результуюча таблиця;
• дані з цієї тимчасової таблиці використовують для створення нових таблиць або зміни існуючих.
Існує декілька видів запитів на зміну. Самий простий і зрозумілий - це запит на створення таблиці. Повернемося до прикладу з розрахунком середньої кількості забитих м'ячів.
1. Припустимо, що розробник таблиці “Підсумки по командах” захотів включити в неї поле “Результативнiсть”. Звичайно, він може розрахувати середню кількість м'ячів, забитих за гру кожній командою, але якщо ввести в таблицю таке поле, то доведеться заповнювати його вручну. Для таблиць, що містять багато записів, це рішення неприйнятне.
2. Простіше створити запит на вибірку, у який увійдуть усього поля базової таблиці плюс нове, яке обчислюється, поле.
3. Натиснення лівої клавіші миші на кнопці “Вид” дозволяє переконатися, що запит працює як треба і створює результуючу таблицю, більш повну ніж базова. Тепер можна дати команду на створення нової базової таблиці, рівній результуючій.
4. Ця команда знаходиться в меню “Зaпрос”, що доступно тільки в режимі “Конструктора”.
5. У тому ж меню присутні команда для створення запитів на відновлення даних, на додавання записів і на вилучення записів. Всі вони відносяться до запитів на зміну і працюють аналогічно, змінюючи базові таблиці відповідно до даних результуючих таблиць.