7.7. Конструирование запросов

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

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

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

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

1. указать, какие поля и из каких таблиц мы хотим включить в запрос;

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

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

В СУБД Microsoft Access для создания запросов используется язык SQL.

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

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

Когда пользователь создает запрос в режиме Конструктора (Режим конструктора. Окно, в котором отображается макет следующих объектов базы данных: таблицы, запросы, формы, отчеты, макросы и страницы доступа к данным. В режиме конструктора пользователь создает новые объекты базы данных или изменяет макеты существующих.) запроса, Microsoft Access автоматически создает эквивалентную инструкцию SQL. При необходимости, пользователь имеет возможность просматривать и редактировать инструкции SQL в режиме SQL.

Например, запрос с вычисляемым полем для определения стоимости продукции в режиме Конструктора запросов выглядит так:

а на языке SQL – так:

А для обычных людей разработчики придумали упрощенный механизм создания запросов, называемый QBE (Query By Example – запрос по образцу). Вам предлагают бланк QBE – некую заготовку запроса, и на этом бланке, пользуясь определенными правилами, Вы сообщаете системе о своих планах: помечаете поля, вводите выражения, значения и т.п. На рис. 7.7.1 показан вид окна Access с запросом в режиме Конструктора.

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

Рис.7.7.1. Окно создания запроса в режиме Конструктора.

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

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

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

Запуск запроса на выполнение осуществляется нажатием кнопки на панели инструментов.

Сохранение запроса для дальнейшего использования производится командами главного меню ФайлСохранить как/Экспорт или нажатием кнопки (сохранить) на панели инструментов. Далее СУБД запросит имя сохраняемого запроса. Целесообразно, чтобы оно имело смысловую нагрузку, что облегчит дальнейшее использование запроса.

Существуют разные типы запросов.

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

Самое главное в запросе – возможность использования критериев выборки, которые вводятся в строку Условие отбора. Можно выделить следующие типы запросов на основе критериев выборки:

0. 1) Выборка по строгому совпадению. В строку Условие отбора для определенного поля вводится одно из значений, существующих в таблице. Например, название конкретного товара или название фирмы, города.

1. 2) Выборка по строгому несовпадению. В этом случае в выборку отбираются все записи таблицы, кроме записей, содержащих значение, указанное в строке Условие отбора. Для реализации данного запроса перед значением вводится префикс Not или <>.

2. 3) Выборка по неточному совпадению. Для выборки записей в условиях неполноты знаний о требуемых значениях используется оператор Like <условие>. Само <условие> образуется следующими подстановочными символами:

• ? - любой один символ;

• * - любое количество символов (0 - );

• # - любая одна цифра;

• [список_символов] - любой символ из списка;

• [!список_символов] - любой символ, не входящий в список;

0. 4) Выборка по диапазону. Для формирования данных условий выбора используются операторы сравнения >, >=, <, <= и <>. Операции сравнения могут связываться логическими операциями And (И) и Or (ИЛИ). Для этих же целей используется оператор диапазона Between <нижнее_значение> and <верхнее_значение>.

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

2. Для выбора записей с пустыми значениями в некотором поле надо в соответствующем поле бланка запроса указать оператор Is Null. Наоборот, записи с непустыми значениями в данном поле выбираются по оператору Is not Null.

Таблица 7.7.1

Примеры условных выражений

Условные выражения	Вывод записей, которые
Саратов	имеют значение Саратов
Not Саратов	не имеют значение Саратов
In (Саратов, Томск , Уфа)	имеют значение Саратов, Томск, Уфа
< М	начинаются с букв А-Л

Продолжение таблицы 7.7.1.

>= М	начинаются с букв М-Я
100	имеют числовое значение 100
<=20	имеют числовое значение, не большее 20
>01.04.06	имеют значение даты позднее 01.04.06
Beetween 01.01.06 And 31.12.06	имеют значение года 2006
*.02.*	имеют значение месяца Февраль
Null	содержат в поле пустое значение
Is not Null	имеют не пустое значение в поле
Like “Р*”	начинаются с буквы Р
Like ??????????	содержат ровно 10 символов
Like “В?????????”	содержат ровно 10 символов и начинаются с буквы В
Like “[ВД]*”	начинаются с букв В или Д. Длина записи произвольная
Like “[В-М]*”	начинаются с букв от В до М. Длина записи произвольная
Like “*П*”	имеют в записи букву П. Длина записи произвольная

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

[<текст приглашения>].

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

При запуске параметризованного запроса появляется диалоговое окно (рис. 9), в котором пользователь должен ввести собственно условие отбора и нажать кнопку <ОК>.

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

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

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

<Название_формируемого_поля>:<выражение>.

Для создания в запросе вычисляемого поля следует учесть, что:

• Имя поля дается произвольно и вводится вручную в строку Поле макета запроса.

• После имени поля следует ввести двоеточие.

• Имена полей, которые участвуют в вычислениях, вводятся в квадратных скобках, например:

Результативность: [Количество Побед] / [Количество Игр]

Здесь:

Результативность – имя нового вычисяемого поля.

Количество Побед и Количество игр – имена существующих в исходных таблицах полей.

При построении выражений в вычисляемых полях можно использовать следующие операторы:

Таблица 7.7.2

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

4.Запрос с групповыми операциями. Рассмотренные выше запросы анализируют отдельные записи таблицы. Вместе с тем, СУБД Access позволяет находить интегральные показатели для групп записей в таблице. Каждая такая группа характеризуется одинаковым значением по какому-то полю, например, одинаковым названием факультета или семейным положением. Для перехода в данный режим запросов необходимо на панели инструментов нажать кнопку , что приведет к появлению в бланке запроса новой строки с одноименным названием. В ячейках данной строки указывается или режим группировки по некоторому поля (опция Группировка), или название групповой операции:

• Sum - сумма значений

• Avg - среднее значение по данному полю для всей группы;

• Count - число записей в данной группе;

• Max - максимальное значение поля в каждой группе;

• Min - минимальное значение поля в каждой группе;

• First - первое значение данного поля в каждой группе;

• Last - последнее значение данного поля в каждой группе и др.

Опции выбора вызываются нажатием кнопки раскрытия в требуемой ячейке.

В Access приняты следующие соглашения:

• условные выражения, набранные в разных столбцах строки Условие отбора: по умолчанию соединяются между собой связкой AND (И).

• условные выражения, набранные в соседних строках одного и того же столбца по умолчанию соединяются между собой связкой OR (ИЛИ).

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

Запросы на изменение. Позволяют за одну операцию вносить изменения в несколько записей. Существует четыре типа запросов на изменение:

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

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

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

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

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

Рис. 7.7.3. Окно Построителя выражений

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

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

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

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

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