Итоговые и модифицирующие запросы

Лекция 17. Итоговые и модифицирующие запросы

Итоговые запросы

Итоговые запросы отличаются от обычных запросов на выборку. В них поля делятся на 2 типа:

· поля, по значениям которых производится группировка данных (поле «Наименование», рисунок 17.1);

· поля, по значениям которых проводятся вычисления (поля «Количество», «Сумма», рисунок 17.1);

Поля, не относящиеся к вышеперечисленным типам (поля «Цена», «Единица» и «Дата», рисунок 17.1), исключаются из состава полей итогового запроса.

Целью итоговых запросов является вычисление значений для групп записей, например, определение суммы значений по заданному полю для каждой группы, определение количества записей в каждой группе, поиск максимума или минимума среди значений поля для каждой группы и т.д.

 Для вычисления итоговых значений используются агрегатные функции стандарта SQL, основными являются следующие:

§ SUМ(Имя поля) – сумма значений указанного поля;

Рекомендуемые материалы

§ AVG(Имя поля) – среднее значение для записей указанного поля;

§ MIN(Имя поля) – минимальное значение в определенном поле;

§ MAX(Имя поля)  – максимальное значение в определенном поле;

§ COUNT(*) – количество записей;

В MS Access могут быть использованы функции расширения, отличные от функций предусмотренных стандартом, например:

§ FIRST(Имя поля)  – первое значение в указанном поле;

§ LAST(Имя поля)   – последнее значение в указанном поле.

Рассмотрим пример группировки на основе таблицы «Продажи товаров» (рисунок 17.1), из которой необходимо получить информацию об итогах продаж каждого товара. Для каждой группы товаров должны быть рассчитаны: итоговая сумма продаж и количество единиц проданного товара. Таким образом, проводим группировку по полю «Наименование», вычисляем суммы для полей «Количество» и «Сумма» (рисунок 17.1). Прочерком помечены поля, которые не используются при подведении итогов и исключаются из запроса.

 

Рисунок 17.1 – Таблица «Продажи товаров»

В результате выполнения группировки получаем три группы, внутри каждой группы рассчитана сумма по количеству проданных товаров и общая сумма продаж (рисунок 17.2)

Рисунок 17.2 – Результат выполнения группировки

Итоговые запросы средствами MS Access могут быть созданы средствами мастера или конструктора.

Процесс создания итогового запроса с помощью средства «Простой запрос» сводится к следующим  шагам:

§ Запускается мастер создания простых запросов, например командой  – Создание запроса с помощью мастера

§ в раскрывающемся списке «Таблицы и запросы» последовательно выбираются таблицы или запросы, информация из которых необходима пользователю, а затем, из списка «Доступные поля» в список «Выбранные поля» перемещаются требуемые поля.

§ На втором шаге работы мастера определяется тип запроса «Итоговый». Нажимаем кнопку «Итоги…» и в окне диалога «Итоги» (рисунок 17.3), выбираем способы вычисления итогов:  Max, Min, Sum, Avg или Count (подсчет числа записей).

Рисунок 17.3 – Фрагмент окна диалога «Итоги»

§ На третьем шаге указываем имя запроса и один из вариантов действий: открыть запрос для просмотра данных или изменить макет запроса, в первом случае результаты запроса будут выданы на экран, а во втором, запрос откроется в режиме конструктора. 

Для создания итогового запроса в режиме конструктора можно использовать кнопку «Групповые операции»  на панели инструментов  или воспользоваться командой Вид - Групповые операции.

В результате, в бланке запроса появится новая строка – «Групповая операция». Если для соответствующего поля из списка выбрать функцию Группировка (рисунок 17.4), то при выполнении запроса записи будут объединяться по этому полю и вся группа будет представлена одной строкой.

Рисунок 17.4 - Строка «Групповая операция» в бланке QBE

Для всех остальных полей необходимо определить итоговые функции, как изображено на рисунке 17.5.

 

Рисунок 17.5 – Определение итоговых функций для полей запроса

Группировка и итоговые выражения могут проводиться не только по реально существующим полям, но и по вычисляемым в запросе выражениям.  Например, если сумма продажи товара не задана явно, однако присутствуют поля «Количество» и «Цена единицы», то сумма может быть получена как:

[Цена] * [Количество]

В этом случае итоговую сумму продаж можно получить выражением:

Сумма: Sum (Количество] * [Цена])

На рисунке 17.5 поле «Сумма» получено с использованием вышеуказанного метода.

В случае необходимости, по вычисляемым значениям или результатам работы встроенных функций можно проводить группирование.

Модифицирующие запросы

В большинстве случаев запросы выполняют выборку тех или иных данных из таблиц БД, однако чаще всего этого бывает недостаточно,  например, приходится решать задачи связанные с модификацией множества записей на основе выбранных данных. Под словом модификация понимается изменение, добавление или удаление данных из таблиц БД.

Представим некоторую таблицу (прайс-лист), содержащую информацию о товарах и их ценах, пусть необходимо произвести наценку на импортные товары. Если товаров, на которые необходимо произвести наценку немного, то пользователя не затруднит внести изменения, если же таких около 20 это уже вызовет определенные проблемы. В реальных ситуациях подобных записей может оказаться сотни и тысячи. Похожие ситуации могут возникать при удалении и добавлении записей.

Для решения подобных задач используются модифицирующие запросы. Выделяют следующие модифицирующие запросы:

§ запросы на удаление;

§ запросы на обновление;

§ запросы на добавление;

§ запросы на создание таблицы (не является модифицирующим, но имеет подобный способ создания).

Внимание! При использовании модифицирующих запросов, желательно предусматривать меры по обеспечению сохранности информации. Например, при удалении (обновлении) неплохо было бы сохранить удаляемые (обновляемые) записи во временно созданную таблицу (при помощи запроса на создание таблицы), эта мера позволит восстановить данные при ошибочных модификациях.

Можно выделить несколько основных этапов присутствующих при создании всех модифицирующих запросов в СУБД MS Access:

1. Создание запроса на выборку с указанием условия отбора.

2. Запуск запроса на выборку и проверка полученных результатов. В результат запроса должны войти только те записи, которые должны быть подвергнуты изменениям. 

3. Преобразование запроса на выборку в соответствующий модифицирующий запрос (только в режиме конструктора запросов).

Создание запроса на удаление

Предположим, что необходимо удалить множество записей из одной таблицы, условие по которому вы будете отбирать записи на удаление известно. В этом случае удобнее не удалять записи с использованием клавиши Del, а применить запрос на удаление. Опишем последовательность создания запроса на удаление:

1. Создать запрос на выборку по таблице, из которой необходимо удалить записи. Указать то условие отбора, которое обеспечит выборку  записей, подлежащих удалению.

2. Выполнить запрос на выборку. В результате запроса должны быть получены только те записи, которые подлежат удалению!

3.  Преобразовать запрос на выборку в запрос на удаление командой Запрос – Удаление или Тип запроса – Удаление и из раскрывающегося списка выбрать запрос на удаление (только в режиме конструктора запросов).

Внимание! Удаление записей не всегда приводит к ожидаемому результату. Необходимо учитывать политику сохранения ссылочной целостности. Например, если таблица является частью отношения один-ко-многим и выступает в роли главной, при этом установлены параметры каскадного удаления, то в результате будут удалены записи не только из этой таблицы, но и все связанные записи дочерних таблиц.

Создание запроса на обновление

Запросы на обновление позволяют изменять значения выбранных полей таблицы по записям, удовлетворяющим определенным условиям. Опишем последовательность создания запроса на обновление:

1. Создать запрос на выборку по таблице, в которой необходимо обновить записи и указать то условие отбора, которое обеспечит выборку  записей, подлежащих обновлению.

2. Выполнить запрос на выборку. В результате запроса должны быть получены только те записи, которые необходимо обновить!

3. Преобразовать запрос на выборку в запрос на обновление командой Запрос – Обновление или Тип запроса - Обновление. В результате в бланке запросов появится новая строка «Обновление».

4. В строке «Обновление» бланка запроса для поля, значения которого подлежат обновлению, указывается формула расчета новых значений или подставляются константы, которые будут замещать значения соответствующих полей отобранных записей.

Создание запроса на добавление

Данный тип запросов используется для добавления записей из одной или нескольких таблиц в другую. Результирующая таблица может являться частью текущей или другой БД. Количество и наименования полей исходной и результирующей таблиц может не совпадать, что учитывается при создании запроса.

Опишем последовательность создания запроса на добавление:

1. Создать запрос на выборку для таблицы, из которой необходимо отобрать записи и указать то условие отбора, которое обеспечит выборку  записей, подлежащих добавлению.

2. Выполнить запрос на выборку. В результате запроса должны быть получены только те записи, которые необходимо добавить!

3. Преобразовать запрос на выборку в запрос на обновление командой Запрос – Добавление или Тип запроса - Добавление. В результате в бланке запросов появится новая строка «Добавление», позволяющая произвести совмещение полей двух таблиц.

4. Если имена и типы полей обеих таблиц совпадают и необходимо перенести значения всех полей, то для строки «Поле» и для строки «Добавление» необходимо выставить значение «*».  Если известно, что имена полей не совпадают, то их можно назначить принудительно для каждого поля (строки «Поле») в строке «Добавление».

 

Запрос на создание таблицы

Запрос на создание таблицы позволяет создать таблицу и разместить в ней данные из одной или нескольких таблиц.

В некоторых случаях требуется создание временных таблиц, например, сохранить данные перед изменением для реализации возможности отката, т.е. отмены изменений. Опишем последовательность создания запроса на создание таблицы:

1. Создать запрос на выборку для таблицы (или нескольких таблиц), данные которой планируется поместить в новую таблицу и указать условие отбора, которое позволит выбрать необходимые записи.

2. Выполнить запрос на выборку. В результате запроса должны быть получены только те записи, которые необходимо поместить в новую таблицу!

3. Преобразовать запрос на выборку в запрос на создание таблицы командой Запрос – Создание таблицы или Тип запроса - Создание таблицы. В результате появиться окно диалога «Создание таблицы», в котором необходимо указать имя таблицы и ее местоположение (в этой или другой БД). После нажатия кнопки «ОК» будет создан запрос на создание таблицы.

Контрольные вопросы

1. Что такое итоговый запрос?

2. Как создается итоговый запрос средствами мастера?

3. Как создается итоговый запрос средствами конструктора?

4. Какие групповые функции вы знаете?

5. Что такое модифицирующий запрос?

6. Относится ли запрос на создание таблицы к модифицирующим?

7. Как создается запрос на удаление?

8. Как создается запрос на добавление?

9. Как создается запрос на обновление?

10. Что общего в механизме создания модифицирующих запросов?

Рекомендуем посмотреть лекцию "1.4. Инструкции и имена".

Задания для самостоятельной работы

Задание 1. Создайте запрос на получении информации о количестве книг на руках у каждого читателя.

Задание 2. Из запроса Просроченные книги извлеките информацию о должниках (Номер читательского билета, ФИО, Домашний и рабочий телефоны, а также количество просроченных книг).

Задание 3. Создайте запрос на списание книг, не пользующихся спросом. Можно списать только те книги, ни один экземпляр которых не  находятся на руках у читателей (запрос на удаление).

Задание 4. Создайте запрос на создание таблицы «Каталогизация». В запрос должны быть включены атрибуты, определяющие область знаний и книги ей принадлежащие.

Задание 5. Создайте запрос на создание таблицы «Плохие читатели». Таблица должна содержать информацию о читателях, не сдавших вовремя книги в библиотеку.