Тема 16. Конструирование запросов

Основы конструирования QBE-запросов. Виды запросов на выборку и запросов-действий. Запросы с параметрами. Групповые запросы. Перекрестные запросы. Запросы с вычисляемыми полями. Работа с построителем выражений. Запросы на обновление, добавление и удаление записей и создание новых таблиц.

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

MS Access позволяет создавать запросы двух типов: QBE – запросы и SQL – запросы.

QBE – запросы (Query By Example – запрос по образцу) строится с помощью конструктора в окне конструктора запросов.

SQL – запросы. Запросы, представляющие собой последовательность операторов и функций языка SQL (Structured Query Language –структурированный язык запросов).

QBE – запросы в MS Access можно преобразовать в SQL – запрос.

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

MS Access позволяет создать запрос двумя способами: с помощью Конструктора или с помощью Мастеров.

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

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

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

Удалить таблицу можно клавишей «Del» или командой Запрос/Удалить таблицу – для предварительно выбранной таблицы.

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

Создать связь можно в окне конструктора запросов. Созданные в окне конструктора запросов связи функционируют только в запросе. Кроме того, MS Access позволяет изменить в окне запросов установленные в схеме данных связи.

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

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

запрос с параметром;

запрос с итогами;

запрос перекрестный;

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

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

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

Перекрестный запрос – это специальный тип группового запроса.

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

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

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

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

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

Вычисляемые поля позволяют:

рассчитывать числовые значения и даты.

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

создавать подчиненные запросы.

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

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

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

Функции – это ранее определенная последовательность действий (программа). В MS Access можно использовать функции двух видов: стандартные, входящие в состав MS Access или VBA (140 функций) и пользовательские, которые пользователь пишет сам на языке VBA. Все функции сгруппированы по категориям.

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

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

Технология объекта запроса «на действия»

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

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

Существует 4 типа запросов на изменение: запрос на добавление; запрос на обновление; запрос на удаление; запрос на создание таблицы.

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

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

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

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

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

Язык структурированных запросов - SQL, представляет собой всемирный стандарт на средства работы с данными на всех аппаратных платформах. Первые версии языка SQL появились в 1970-е годы. Наличие стандарта обеспечивает унификацию программного обеспечения и его независимость от сервера данных и аппаратной платформы.

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