Первая страница
Наша команда
Контакты
О нас

    Головна сторінка



Ярцев в. П. Створення та обробка баз даних на пеом

Ярцев в. П. Створення та обробка баз даних на пеом




Сторінка11/20
Дата конвертації10.03.2017
Розмір2.36 Mb.
ТипПротокол
1   ...   7   8   9   10   11   12   13   14   ...   20




8. Обчислення в запитах. Створення запитів у Access




8.1. Обчислення в запитах

В операторі SELECT можна робити обчислення, результати яких будуть міститися в додатковому полі, яке включається у вихідний НД і називається полем, що обчислюється. Можливі два способи створення полів, що обчислюються:



  • шляхом використання виразу в інструкції SELECT;

  • з використанням групових (агрегатних) функцій.

Розглянемо кожний з цих способів.

8.1.1 Створення поля, що обчислюється, за допомогою виразу

Для створення поля, що обчислюється, можна в інструкції SELECT у списку полів записати вираз, наприклад, у такий спосіб:

SELECT ПІБ, 2003-РікНародж

FROM СТУДЕНТИ

У цьому операторі в результуючому НД поряд з полем ПІБ міститься поле, що обчислюється, у якому поміщується вік кожного студента. Створеному полю СУБД привласнить своє (внутрішнє) ім'я, що не буде відбивати змісту інформації, що міститься в ньому. Тому рекомендується створити для поля, що обчислюється, псевдонім, який потім використовується як ім'я поля. У нашому прикладі це можна зробити так:

SELECT ПІБ, 2003-РікНародж as Вік

FROM СТУДЕНТИ

Створюваному полю, що обчислюється, привласнюється ім'я Вік.



8.1.2. Застосування групових (агрегатних) функцій


Дуже часто потрібно зробити обчислення деяких підсумкових даних по всьому наборі даних чи по яких-небудь групах даних. У цьому випадку використовуються групові (агрегатні) функції: Max, Min, Count, Avg, Sum (див. розділ 8).

Нехай, наприклад, є таблиця СТУДЕНТИ, що має наступну структуру:

СТУДЕНТИ (КодСтуд, КодГр, ПІБ, …).

Підрахувати кількість усіх студентів, про яких зберігаються дані у таблиці СТУДЕНТИ, можна за допомогою наступного оператора:

SELECT Count(ПІБ) as Кільк_Студ

FROM СТУДЕНТИ

Очевидно, що даний запит повертає єдине значення – кількість студентів (поле Кільк_Студ), яка у даному випадку збігається з числом записів у таблиці.

Розглянемо більш цікавий приклад – обчислимо середній бал по кожному зі студентів. Нехай, наприклад, інформація про оцінки міститься в таблиці УСПІШНІСТЬ, що має структуру:

УСПІШНІСТЬ (КодСтуд, КодПр, Дата, Оцінка).

Текст запиту, що обчислює середній бал для кожного зі студентів, може мати наступний вигляд:

SELECT С.ПІБ, Avg(У.Оцінка) as Серед_Бал

FROM СТУДЕНТИ as С LEFT JOIN УСПІШНІСТЬ as У

ON С.КодСтуд = У.КодСтуд

GROUP BY С.ПІБ

Оскільки тут добір даних утворюється з двох таблиць (поле ПІБ береться з таблиці СТУДЕНТИ, а поле Оцінка, що бере участь в обчисленнях, - з таблиці УСПІШНІСТЬ), у запиті створюється з'єднання таблиць. Завдяки тому, що створюється ліве зовнішнє з'єднання, у результуючий набір даних будуть включені і прізвища студентів, для яких немає оцінок у таблиці УСПІШНІСТЬ.

В інструкцію GROUP BY повинні включатися всі поля, імена яких зазначені в інструкції SELECT і не беруть участь в агрегатних функціях. У нашому прикладі це поле С.ПІБ.

В інструкції SELECT для поля, що обчислюється, одержуваного як результат застосування групової (агрегатної) функції Avg до поля Оцінка, створюється псевдонім Серед_Бал. Цей псевдонім на замовчення буде відображатися в заголовку стовпця середнього бала.

Якщо в запиті, у якому застосовуються групові функції, ще потрібно зробити відбір записів по деякій умові, застосовуваній до підсумкових (групованих) даних, тоді для завдання умови потрібно використовувати інструкцію HAVING (а не WHERE).

Нехай у розглянутому прикладі потрібно в результуючий НД помістити тільки записи, що відносяться до студентів із середнім балом 3,6 і більше. Тоді приведений вище текст запиту потрібно доповнити інструкцією:

HAVING Avg(У.Оцінка) >= 3,6



8.2. Вкладені запити

Часто буває неможливо вирішити поставлену задачу шляхом використання тільки єдиного запиту. Наприклад, у випадку, коли в умові добору потрібно використовувати заздалегідь невідоме значення, з яким потрібно порівнювати дане значення.

Нехай, наприклад, вирішується задача визначення середнього бала для студента Іванова. Але при цьому в таблиці УСПІШНІСТЬ, у якій зберігаються дані про оцінки, немає поля, що містить прізвища, але є тільки поле КодСтуд, яке містить внутрішні коди студентів, що користувачу невідомі. З використанням вкладеного запиту вирішити дану задачу можна в такий спосіб:

SELECT Avg(Оцінка)

FROM УСПІШНІСТЬ

GROUP BY КодСтуд

HAVING КодСтуд =

(SELECT КодСтуд

Вкладений FROM СТУДЕНТИ

запит WHERE Фам = “Іванов

Дану задачу можна було б вирішити і без вкладеного запиту, створивши в запиті з'єднання таблиць СТУДЕНТИ й УСПІШНІСТЬ.

Розглянемо приклад задачі, яку не можна вирішити інакше, як з використанням вкладених запитів. Нехай, наприклад, потрібно одержати список студентів, у яких середній бал більше, ніж у студента Іванова. Тому що середній бал Іванова заздалегідь невідомий, для його визначення використовується вкладений запит.

Оператор SELECT, що вирішує цю задачу, виглядає в такий спосіб:

SELECT Фам, Avg(Оцінка)

FROM СТУДЕНТИ С INNER JOIN УСПІШНІСТЬ У

ON С.КодСтуд = У.КодСтуд

GROUP BY Фам

HAVING Avg(Оцінка) >



(SELECT Avg(Оцінка)

FROM УСПІШНІСТЬ

Вкладений GROUP BY КодСтуд

запит HAVING КодСтуд =



(SELECT КодСтуд

Вклад. FROM СТУДЕНТИ

запит WHERE Фам = “Іванов” ))
Тут застосований вкладений запит з двома рівнями вкладеності. Взагалі кількість рівнів вкладених запитів не обмежується.

8.3. Створення запитів у Access

У Access маються могутні і зручні інструментальні засоби створення різних запитів. Для створення простих так званих “запитів за зразком” можна використовувати Майстер запитів, що послідовно запитує у користувача потрібну інформацію (найменування таблиць, які будуть використовуватися у запиті; імена полів, які потрібно включити в результуючий НД і інш.) і потім автоматично генерує SQL-запит. Створений за допомогою Майстра запит відразу можна виконати і зберегти для подальшої його доробки й удосконалення за допомогою Конструктора запитів.

Для створення більш складних запитів необхідно використовувати Конструктор запитів. Розглянемо більш добірно технологію створення запитів в Access за допомогою Конструктора запитів. Для створення запиту в режимі Конструктора потрібно виконати наступне:


  1. У вікні вашої БД відкрити вкладку “Запити” і натиснути кнопку Створити. Відкриється діалогове вікно “Новий запит”, у якому потрібно вибрати спосіб створення запиту. Виберіть спосіб “Конструктор” і натисніть кнопку ОК.

  2. Відкриється вікно Конструктора запитів і відразу ж на його фоні відкриється діалогове вікно “Додавання таблиці”. Виберіть кнопкою миші потрібні для запиту таблиці і натисніть кнопку Додати. Після цього закрийте вікно “Додавання таблиці” кнопкою Закрити.

  3. Вікно Конструктора запитів має вид, показаний на Рис. 8.1. Вікно розділене на 2 панелі: верхню і нижню. Верхня панель називається схемою даних запиту, на якій розміщені списки полів таблиць, що були додані в запит. У списках полів верхній рядок відзначений зірочкою *, що позначає всі поля. Access автоматично встановлює зв'язок між полями з однаковими іменами і типом даних.

Рис. 8.1. Вид вікна Конструктора запитів

Нижня панель називається бланком запиту, що заповнюється в такий спосіб:


  • у рядок “Поле” помістити імена полів, що повинні бути включені в запит. Зробити це можна, наприклад, способом перетаскування потрібного поля зі Схеми даних у рядок “Поле”;

  • у рядку “Сортування” вибрати порядок сортування для поля, по якому потрібно сортування;

  • у рядку “Вивід на екран” відзначити поля, що повинні бути виведені в результуючу таблицю;

  • у рядках “Умови відбору” і “чи” задати умови відбору записів.

Умови відбору даних (записів) задаються виразами, що можуть складатися з операндів і операторів (операцій) порівняння. У якості операндів можуть використовуватися:

  • літерали: “Іванов”, “СОБД”, “А*”, #1.09.99# ;

  • константи: True, False, Null ;

  • ідентифікатори – посилання на поля: [Ім'я таблиці] .[Ім'я поля].

У виразах можуть застосовуватися наступні операції і предікати:

= , < , >, <>, And , Or , Not , Between , In , Like.


  1. Зачинити вікно Конструктора запитів. При цьому відкривається діалогове вікно, у якому потрібно увести ім'я запиту, що був створюваний. Вид діалогового вікна зображено на Рис. 8.2. Після введення потрібного імені натисніть кнопку ОК.


Рис. 8.2. Вікно для введення імені запита
Створений запит можна запустити на виконання подвійним щигликом миші на значку запиту у вікні БД чи натисканням кнопки Відкрити.

У режимі Конструктора запит можна запустити на виконання командою меню ЗапитЗапуск чи натиснувши кнопку (“Запуск”) на панелі інструментів.




1   ...   7   8   9   10   11   12   13   14   ...   20



  • 8.1.1 Створення поля, що обчислюється, за допомогою виразу
  • 8.1.2. Застосування групових (агрегатних) функцій
  • 8.2. Вкладені запити
  • 8.3. Створення запитів у Access