СУБД MS Access. Создание запросов

Тема:

СУБД MS Access. Создание запросов.

Запросы используются для сортировки, фильтрации, добавления, удаления или изменения информации в базах данных. С помощью запросов выбираются определенные записи, предназначенные для форм и отчетов, которые работают не со всеми имеющимися данными, а лишь с частью. В спроектированных нами таблицах Клиенты, Продукты и Поставки содержится вся информация, необходимая для учета поставок. Запрос же позволяет отобрать и сгруппировать данные так, чтобы узнать, например, общую стоимость нефтепродуктов, поставленных на бензоколонку Гранд-5 за март 2002 года или список клиентов, которым отправлялось дизельное топливо 2 февраля 2003 года.

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

Существуют следующие типы запросов:

Запрос на выборку. Эти запросы извлекают данные, соответствующие указанному условию. Они также группируют записи для просмотра итоговых данных и представляют вычисления, выполненные в полях данных.
Запрос на изменение. Эти запросы каким-либо образом изменяют данные в исходных таблицах. Они используются для удаления, обновления и добавления большого количества данных, а также для создания новых таблиц.
Запросы с параметрами. Эти запросы при запуске предлагают вам ввести какие-либо параметры (данные или условия).
Перекрестные запросы. Перекрестные запросы используются для расчетов и представления данных в структуре (типа электронной таблицы), облегчающей анализ.
SQL-запросы.

Запрос на выборку.

Общие положения.

Запрос-выборка – это производная таблица, которая содержит те же структурные элементы, что и обычная таблица (столбцы-поля и строки), и формируется на основе фактических данных системы.

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

При создании макета запроса (т.е. производной таблицы) в общем случае необходимо выполнение четырех базовых операций:

указать, какие поля и из каких таблиц надо включить в запрос;
описать вычисляемые поля, т.е. поля, значения которых являются функциями значений существующих полей (например, стоимость=цена*количество)
описать групповые операции над записями исходных таблиц (например, нужно ли объединить группу записей с одним и тем же кодом клиента в одну и просуммировать стоимость заказанной им продукции)%
указать условие отбора, т.е. сформулировать логическое выражение, которое позволит включить в выборку только записи, удовлетворяющие какому-либо условию (например, с датой поставки от 1 до 31 марта 2002 года).

При разработке конкретного запроса допускается любое сочетание базовых операций.

Создание запроса на выборку.

Мастер создания простых запросов.

Как и любой объект Access, можно создать запрос вручную или с помощью Мастера создания запросов (рекомендуется для начинающих пользователей). Процесс создания нового запроса аналогичен созданию таблицы, т.е. надо просто выбрать объект базы данных Запросы – Создать и далее в диалоговом окне Новый запрос выбрать режим создания запроса (рис 4.1).

Разберем создание простого запроса на выборку данных из таблицы Клиенты .

Рис. 4.1. Выбор режима создания нового запроса.

Рис. 4.2. Выбор полей, включаемых в запрос.

Определив способ создания запроса, выбираем поля, которые включаются в запрос (рис.4.2) из таблицы Клиенты - поля КодКлиента, Клиент, Телефон

На последней странице мастера запросу присваивается имя Телефоны (рис 4.3).

Рис. 4.3. Наименование запроса.

Результат выполнения запроса представлен на рис 4.4.

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

Рис. 4.4. Простой запрос возвращает из таблицы Клиенты только указанные в запросе поля.

Нахождение итоговых значений.

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

Создадим новый простой запрос из таблицы Поставки для подсчета суммарного объема каждой марки нефтепродуктов за весь период. Для этого из таблицы поставки выберем поля КодПродукта и Объем (рис. 4.5)

Рис. 4.5. Выбор полей, включаемых в запрос.

На следующем шаге выберем параметр Итоговый и, нажав кнопку Итоги …(рис 4.6), в диалоговом окне Итоги (рис 4.7) установим флажок Sum в Поставки.

Рис. 4.6. Выбор отчета с отображением Итогов.

Рис. 4.7. Выбор итоговой функции для поля Объем..

Результат выполнения запроса представлен на рис. 4.8.

Рис. 4.8. Запрос, созданный мастером, возвращает общий объем каждой марки нефтепродуктов.

На рис. 4.4 и 4.8 представлен просмотр результата запросов в режиме Таблица. Среда запросов позволяет просматривать (создавать) запросы еще в режиме Конструктор с бланком запроса и в режиме SQL.

Создание и изменение запросов в режиме Конструктор.

Вид запроса в режиме Конструктор.

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

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

Рис. 4.9. Запрос Телефоны в режиме Конструктор.

Вид запроса в SQL-режиме.

SQL-режим.

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

Рис. 4.10. Запрос Телефоны в режиме SQL.

На рис 4.10 представлен эквивалент запроса Телефоны в форме SQL (такое представление запроса можно установить, выбрав для открытой таблицы запроса в меню Вид режим SQL). Сравнение SQL-выражения и режима конструктора запроса является хорошим способом познакомиться с логикой построения запроса.

Реально в Access для построения запросов используется механизм QBE (Query By Example – Запрос по образцу) – метод создания запросов, изобретенный IBM еще в 70-е годы.

Тем не менее, полезно знать некоторые элементы языка SQL, так как с его помощью можно создавать запросы, которые невозможно создать с помощью бланка запроса.

Простые ключевые слова языка SQL.

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

Ключевые слова SQL обычно представлены строчными буквами. (У операторов строчной является только первая буква). Самыми распространенными ключевыми словами являются:

AS. Задает оператор, указывающий выражение или значение, а также имя поля, с которым оно связано (иногда называют псевдонимом);
DISTINCTROW. Предотвращает дублирование записей из запроса;
FROM. Задает оператор, указывающий таблицу или запрос, из которых извлекаются поля;
GROUP BY. Указывает поле, которое используется для группировки записей в итоговой и перекрестной таблице;
ORDER BY. Указывает поле, которое определяет порядок хранения записей.
SELECT. Задает оператор, содержащий список полей, включаемых в запрос;
UNION. Объединяет два набора записей в один;
WHERE. Задает оператор с условием (или набором условий) для фильтрации записей запроса.

Создание запроса в режиме Конструктор.

В конструкторе можно создать запрос вручную. Для создания нового запроса надо выбрать объект базы данных Запросы – Создать и далее в диалоговом окне Новый запрос выбрать режим создания запроса Конструктор (рис 4.1).

Добавление таблиц в запрос.

Открыв окно запроса, можно приступать к разработке нового запроса. Разберем создание запроса на список всех поставок из базы Поставки с полным названием продукта и клиента. Необходимо начать с добавления таблиц, из которых выбираются данные (рис. 4.11).

Рис. 4.11. Диалоговое окно Добавление таблицы.

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

В процессе создания запроса всегда можно добавить еще таблицы, открыв окно добавления таблиц снова (меню Запрос – Добавить таблицу…).

После закрытия диалогового окна Добавление таблицы, открывается окно конструктора. На рис. 4.12 показано такое окно с добавленными таблицами Клиенты, поставки и Продукты, с отображением установленных ранее связей.

Примечание. Если таблицы в запросе не объединены, то Access создаст полное объединение, содержащие все возможные комбинации, т.е. объединит все записи всех таблиц. Например, для двух несвязанных таблиц из 10 и 20 записей, запрос БУДЕТ содержать 200 записей.

Рис. 4.12. Создание нового Запроса в режиме Конструктор.

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

Добавление полей в запрос.

Для создания запроса нам потребуются следующие поля:

Из таблицы Поставки – КодПоставки, Дата, Объем
Из таблицы Продукты – Продукты
Из таблицы Клиенты – Клиент.

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

Рис. 4.12. Пример добавления полей в бланк запроса.

Добавление вычисляемых полей.

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

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

Добавим вычисляемое поле в запрос Поставки. Для вычисления стоимости заказа в поле последнего столбца введем (см. рис. 4.13):

Стоимость_заказа: [Цена]*[Объем]

Рис. 4.13. Пример добавления вычисляемого поля в бланк запроса.

Определение порядка сортировки.

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

Для задания порядка сортировки в строке Сортировка бланка запроса открывается список и выбирается По возрастанию или По убыванию (рис 4.14). Данные сортируются в соответствии со стандартными правилами.

Рис. 4.14. Задание порядка сортировки.

На рис. 4.15 приведен выполненный запрос с сортировкой поля Дата по возрастанию

Рис. 4.15. Запрос Поставки с сортировкой по возрастанию Даты.

При сортировке по нескольким полям порядок, в котором поля добавлены в бланк запроса, определяет приоритет полей. Крайнее слева поле с включенной сортировкой имеет наиболее высокий приоритет, а крайнее справа – наиболее низкий.

Выполнение запроса.

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

Для запуска запроса на выполнение можно использовать команду Режим таблицы. Для запросов на выборку оба метода идентичны; каждый выдает динамический набор с результатом запроса.

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

Сохранение и печать запросов.

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

Задание условий.

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

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

Чтобы ввести условия, щелкните в любом месте строки Условие отбора нужного поля и введите выражение (допускается ввод длинных выражений). Чтобы просмотреть все выражение, нажмите Shift+F2 для открытия окна Область ввода.

Операторы, используемые в выражениях.

Операция	Значение
Арифметические операторы
+	Сложение
*	Умножение
/	Деление
^	Возведение в степень
Сравнения операторы
=	Равно
<	Меньше
>	Больше
<=	Меньше или равно (не больше)
>=	Больше ли равно (не меньше)
<>	Не равно
Логические операторы
Or	Любое из условий
And	Все условия
Between	В диапазоне (между двумя крайними значениями, включая их)

Использование текстовых условий.

Допустим, надо модифицировать запрос Поставки так, чтобы отображались данные только для клиента Гранд 7. Тогда в поле условие надо ввести текст - Гранд 7.

Access допускает использование разных способов ввода текстовых выражений. Можно вводить любое из следующих выражений:

Гранд 7
"Гранд 7"
=Гранд 7
="Гранд 7"

В приведенном примере оператор это знак =, а операнд это текстовое значения "Гранд 7" (кавычки можно опустить).

Вместе с текстовыми условиями можно использовать оператор Like и символ *, который заменяет произвольную последовательность символов. Например, для отбора поставок нефтепродуктов марки Бензин автомобильный Аи 95 (96) можно записать в строке условий: Like *Аи-95*. Впрочем, оператор Like для текстовых условий тоже можно опустить.

Использование числовых условий.

Для полей, содержащих числовые или денежные значения, в выражениях можно использовать математические операции. Такие выражения, как =20.00 и between 5 and 12, являются типичными числовыми условиями.

Например, для вывода на экран заказа с определенным номером, в запросе Поставки можно для поля КодПоставки задать условие: =10.

Использование условий для дат.

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

02/02/02
#02/02/02#
2-Фев-02
02.02.02

В условиях дат можно использовать функцию Date() для отбора записей, содержащих текущую дату или попадающих в определенный временной интервал относительно текущей даты. Например, between date()-30 and date().

Использование условий с логическими операциями.

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

с помощью ячеек Условие отбора каждого поля (рис. 4.16))
- вводом всех условий в одну ячейку Условие отбора, воспользовавшись оператором And.

Рис. 4.16. Запрос Поставки: выбор заказов с датой = 02.03.02 и маркой бензина Аи-95.

Записи не обязательно должны удовлетворять всем условиям. Иногда должно выполняться хотя бы одно условие из имеющихся. Это достигается либо применением оператора Or, либо помещением второго условия в ячейку или (рис 4.17)

Рис. 4.17. Запрос Поставки: выбор заказов бензина Аи-92 или бензина Аи-95.

Группирование и вычисление итоговых значений.

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

Вычисление итоговых значений для сгруппированных записей.

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

Создать новый запрос Продажи на основе таблиц Поставки и Продукты
Добавить в бланк запроса поле Продукт из таблицы Продукты
Создать вычисляемое поле: Сумма Продаж: Цена*Объем
Добавить строку Групповые операции (Вид – Групповые операции)
В ячейке Групповые операции столбца СуммаПродаж выберите вариант Sum

Вид такого запроса в режиме Конструктор представлен на рис. 4.18, а результат выполнения запроса – на рис. 4.19

Рис. 4.18. Запрос Продажи: групповые операции в столбцах Продукт и СуммаПродаж.

Рис. 4.19. Запрос Продажи сгруппировал записи по каждому типу нефтепродуктов и вычислил суммы продаж для этого типа.

В строке Групповые операции можно выбрать различные функции:

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

Список функций обобщения:

Sum. Суммирует значения в каждой группе
Avg. Возвращает среднее значение для каждой группы.
Min. Возвращает наименьшее значение группы
Max. Возвращает наибольшее значение группы
Count. Возвращает число пунктов (записей) в группе
StDev. Возвращает среднеквадратическое отклонение группы.
Var. Возвращает значение дисперсии группы
First. Возвращает первое значение группы
Last. Возвращает последнее значение группы.

Вычисление конечной суммы.

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

Вид такого запроса в режиме Коструктор представлен на рис. 4.20, а результат выполнения запроса – на рис. 4.21

Рис. 4.20. Запрос Продажи: групповые операции в столбце СуммаПродаж.

Рис. 4.21. Запрос Продажи вычислил общую сумму продаж.

Работа со специализированными запросами.

К специализированным типам запросов относятся:

запросы на изменение
- перекрестные запросы
- запросы с параметрами

Запросы на изменение.

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

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

В Access можно создавать четыре типа запросов на изменение:

запросы на создание таблицы (создают новые таблицы, основанные на результатах запроса);
- запросы на добавление (добавляют записи в существующие таблицы);
- запросы на обновление (изменяют данные в существующих таблицах);
- запросы на удаление (удаляют записи, выбранные в результате выполнения запроса).

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

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

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

Например, создадим резервную копию данных о поставках за март 2002 года (используем базу Поставки).

Для этого:

Создадим простой запрос на выборку всех записей и всех полей из таблицы Поставки, указав в строке Условие отбора для поля Дата: between 01.03.02 and 31.03.02.
Зададим тип запроса – Создание таблицы (меню Запрос – Создание таблицы) и в открывшемся окне диалога (рис 4.22) присвоим имя таблице (Копия_март_02)
Выполним запрос (меню Запрос - Запуск) и убедимся в появлении новой таблицы с заданным именем.

Рис. 4.22. Диалоговое окно Создание таблицы для запроса на создание таблиц.

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

Запросы на обновление.

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

Рис. 4.23. Запрос на обновление в режиме Конструктор.

Например, изменим цены (повысим на 5%) на все марки бензина (используем базу Поставки).

Для этого:

Создадим простой запрос на выборку полей Продукт и Цена из таблицы Продукты;
Зададим тип запроса – Обновление (меню Запрос – Обновление). В бланк запроса добавится строка Обновление (рис 4.23).
Зададим условия отбора, указав в строке Условие отбора: для поля Продукт Like *Аи*
Зададим обновление, указав в строке Обновление: для поля Цена =[Цена]*1,05
Выполним запрос (меню Запрос - Запуск) и убедимся в изменении цены на указанные марки бензина.

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

Запросы на удаление.

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

Порядок создания запроса на удаление точно такой же, как и для запроса на обновление (только вместо строки Обновление в бланке запроса появляется строка Удаление).

Например, удалим данные о поставках за март 2002 года (используем базу Поставки).

Рис. 4.24. Запрос на удаление в режиме Конструктор.

Запросы на добавление.

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

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

Например, добавим данные о поставках за март 2002 года в таблицу Поставки (используем таблицу Копия_март_02).

Рис. 4.25. Запрос на добавление в режиме Конструктор.

Перекрестные запросы

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

На рис. 4.26 представлена созданная в мастере структура перекрестного запроса на основе запроса Поставки_Запрос (рис 4.15) и на рис. 4.27 – результат выполнения этого запроса.

Рис. 4.26. Диалоговое окно задания структуры перекрестного запроса.

Рис. 4.27.Результирующая перекрестная таблица.

Запросы с параметрами

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

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

Пример 1. Итоги поставок для определенного клиента (на основе запроса Поставки_Запрос - рис 4.15) – запрос на выборку.

Рис. 4.28. Запрос с параметром в режиме Конструктор.

Рис. 4.29. Задание параметра

Пример 2. Создание резервной копии данных о поставках за указанный месяц (используем базу Поставки) – запрос на изменение. В результате создается новая таблица с именем Копия_. После выполнения запроса ее обязательно надо переименовать (Копия_февраль), т.к. следующее выполнение этого запроса приведет к удалению таблицы Копия_.

Рис. 4.30. Запрос с параметром в режиме Конструктор.

Рис. 4.28. Задание параметра (ввод дат).

СУБД MS Access. Создание запросов