Материал: 5685

16

3. Запросы на выборку к таблицам БД

Запросы на выборку (запросы на извлечение) позволяют искать и обрабатывать данные в базе, не изменяя её содержимого. Они могут быть следующих видов:

простые запросы на выборку;

запросы с вычисляемыми полями;

запросы с групповыми операциями (запросы с итогами);

перекрёстные запросы.

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

Запрос с групповой операцией отличается от простого запроса на выборку тем, что позволяет группировать данные по заданному полю и вычислять групповые итоги (осуществлять групповые операции) по заданным поля в группе. Возможно применение условий отбора. В MS Access предусмотрены следующие групповые операции:

Sum – сумма значений группы;

Avg – среднее значение для группы;

Max, Min – максимальное или минимальное значение в группе;

Count – количество непустых значений в группе;

StDev – среднеквадратичное отклонение в группе;

Var – дисперсия значений поля в группе;

First, Last – значение поля из первой и последней записи в группе. Перекрёстный запрос отличается от запроса с групповыми операциями

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

По способу создание запросы можно разделить на QBE-запросы (Query by Example – запросы по образцу) и SQL-запросы. Первые строятся с помощью конструктора запросов, вторые с помощью операторов и функций языка SQL (Structured Query Language – язык структурированных запросов). Дополнительным средством создания запросов в MS Access является мастер запросов. Запросы можно создавать к таблицам, к другим запросам на выборку, одновременно к таблицам и запросам.

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

Последовательность действий при создании простого запроса на выборку:

определить, в какой таблице (или таблицах) содержатся искомые данные;

определить, по каким полям, каких таблиц будет происходить отбор данных, сформулировать критерии отбора;

17

запустить конструктор запросов, добавить выбранные таблицы;

если запрос многотабличный убедится, что между таблицами (запросами, таблицами и запросами), включёнными в запрос существует связь;

указать в таблице конструктора запросов поля, содержащие искомые данных;

указать поля, по которым осуществляется отбор данных, ввести критерии отбора;

сохранить запрос под выбранным именем и запустить его.

Для создания запроса с групповыми операциями или перекрестного запроса необходимо дополнительно:

определить, по каким полям будет осуществляться группировка данных;

определить вид групповой операции и поле, к которому она будет применена.

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

18

Практическое задание 3. Создание запросов на выборку к таблицам базы данных

Запустите MS Access 2007 и откройте базу данных по учёту торговли, созданную на предыдущем занятии. Создайте запросы к таблицам базы данных, позволяющие получить заданную информацию. Таблицы БД, включаемые в запрос, и критерии отбора записей определяйте самостоятельно.

Простые однотабличные запросы

1.Получите список товаров, которых нет в наличии.

2.Определите, какие товары имеют стоимость больше Y рублей (стоимость Y задайте самостоятельно).

3.Используя сортировку, определите, какой товар имеет наибольшую стоимость.

4.Получите список поставок в хронологическом порядке с указанием кода товара и номера склада.

5.Получите список поставщиков одного выбранного Вами региона с указанием адреса и телефона.

6.Определите номера телефонов поставщиков Х и Y (наименования поставщиков задайте самостоятельно)

7.Определите, на каких складах находятся товары, поставленные Y числа (дату поставки Y задайте самостоятельно).

8.Получите список поставок, выполненных в январе, марте и мае текущего года.

9.Выясните, делал ли поставки в первом квартале текущего года поставщик №… (номер поставщика задайте самостоятельно).

10.Выясните, поступал ли товар X на склад №… в феврале текущего года (код товара X и номер склада задайте самостоятельно).

Пример: Получить список товаров, которых нет в наличии.

Запрос однотабличный, все необходимые поля находятся в таблице Товар.

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

появившемся окне Добавление таблиц выбираем таблицу Товар и добавляем её в запрос (рисунок 10).

В таблице конструктора (она находится в нижней части) указываем поля Код_товара, Наименование, Цена (в них находятся необходимые данные) и поле Наличие, которое служит для задания условия отбора записей. Условие отбора – нет (это логическое значение, без кавычек). Указать необходимые поля можно двойным щелчком мыши по выбранному полю таблицы (в схеме данных запроса), или использовать раскрывающиеся списки

19

строк Имя таблицы и Поле таблицы конструктора. Созданный запрос сохраняем и запускаем.

Заполненный конструктор запросов представлен на рисунке 11.

Рисунок 10 – Добавление таблицы в Конструктор запросов

Рисунок 11 – Простой однотабличный запрос на выборку

Простые многотабличные запросы

1.Определите адреса и телефоны поставщиков, выполнивших поставки в течение одного (любого) месяца.

2.Получите список товаров поставленных поставщиками X, Y, Z в феврале и марте (наименования поставщиков задайте самостоятельно).

3.Определите адреса и номера телефонов контактных лиц поставщиков X, Y, Z (наименования поставщиков задайте самостоятельно).

4.Склады Б и В попали в зону стихийного бедствия. Какие товары пострадали?

20

5.Получите список товаров поставленных во втором квартале текущего года. Список должен содержать дату поставки, наименование поставщика, наименование товара, его код и цену.

6.Выясните, поставлял ли поставщик Х товар Y на склад №… в апреле – мае текущего года (наименования поставщика и товара, а также номер склада задайте самостоятельно).

7.Вы хотите приобрести товар X. К каким контактным лицам следует обратиться?

8.В данный момент некоторые товары отсутствуют. К каким поставщикам и контактным лицам поставщиков следует обратиться?

9.Получите список товаров поставки, которых не совершались (если будет получена пустая таблица – таких товаров нет).

10.Определите, есть ли поставщики, которые не совершали поставок.

Пример: Определить адреса и телефоны поставщиков, выполнивших поставки в феврале месяце.

Все необходимые поля находятся в таблицах Поставщики и Поставки. Запускаем Конструктор запросов, добавляем в запрос указанные таблицы. В схеме данных запроса проверяем наличие связей между таблицами. В таблице конструктора указываем поля Наименование, Адрес, Телефон из таблицы Поставщики (в них находятся необходимые данные) и поле

Дата_поставки из таблицы Поставки, оно необходимо для задания условия отбора записей. Условие отбора по дате – >=#01.02.2011# And <#01.03.2011#. Заполненный конструктор представлен на рисунке 12.

Созданный запрос сохраняем и запускаем его на выполнение.

Рисунок 12 – Простой многотабличный запрос на выборку