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

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

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

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

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

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

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

Создать запрос можно двумя способами:

1) создание запроса с помощью мастера;

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

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

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

Определение 4. Выражение – это предложение, содержащее операторы и операнды. Результатом вычисления выражения является только одно значение.

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

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

Арифметические	Сравнения	Логические
Оператор	Значение	Оператор	Значение	Оператор	Значение
^	Возведение в степень	=	Равно	Or	Одно из условий отбора должно принимать значение Истина
*	Умножение	<>	Не равно	And	Все условия отбора должны принимать значение Истина
/	Деление	>	Больше	Not	Условие отбора не должно принимать значение Истина
Mod	Остаток от деления	<	Меньше	Xor	Только одно из условий отбора должно принимать значение Истина
+	Сложение	>=	Больше или равно
-	Вычитание	<=	Меньше или равно

Кроме обычных операторов сравнения Access предоставляет три специальных оператора, полезных для отбора данных:

1. Between – определяет диапазон значений, включая крайние значения. Выражение Between 10 And 20 означает то же самое, что и выражение (>=10) And (<=20).

2. In – задает список значений для сравнения. Выражение In (“Алексей”; “Анна”) означает то же самое, что и выражение “Алексей” Or “Анна”.

3. Like – оператор поиска образцов в текстовых полях. В образец поиска можно включить символы шаблона:

-? обозначает любой одиночный символ в данной позиции;

- * обозначает любое количество любых символов в данной позиции;

- # указывает, что в данной позиции должна стоять цифра;

- [0-9] – в этой позиции должна стоять цифра;

- [ a-n ] – в этой позиции должна стоять буква от а до n;

- [!0-9] – любой символ, кроме цифры.

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

Например, с помощью выражения Like “?[a-n]t[0-9]*” проверяется наличие произвольного символа в первой позиции, буквы от a до n во второй позиции, буквы t в третьей позиции, любой цифры в четвертой позиции и произвольного числа символов в конце строки.

Строковый оператор + используют для объединения двух любых строк. В Access также имеется оператор &, который используется только для объединения текстовых строк.

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

Определение 7. Константы – это постоянные, предопределенные значения.

Ограничители для констант не используются. True, False и Null (отсутствие значения) – примеры констант, автоматически определяемых в Access.

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

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

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

Функции для работы с датами и временем:

1. Date() возвращает текущую системную дату;

2. Now() возвращает текущие время и дату;

3. Day(дата) возвращает значение дня месяца в диапазоне от 1 до 31;

4. Month(дата) возвращает значение месяца года в диапазоне от 1 до 12;

5. Year(дата) возвращает значение года в диапазоне от 100 до 9999;

Ввод условий отбора записей

Выражения можно создавать самостоятельно или с помощью построителя выражений.

Создание выражения без построителя выражений

Выражение вводят в ячейку строки Условие отбора для выбранного поля бланка запроса (рис. 1). Увеличить размер области ввода выражения можно с помощью клавиш Shift+F2.

Рис. 1. Бланк запроса на выборку.

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

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

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

o Новогодний;

o =Новогодний;

o ”Новогодний”;

o =”Новогодний”.

Для получения списка вкладов, открытых, начиная с четвертого квартала 2003 года, можно использовать выражение >=#1/10/2003#.

Чтобы отобрать вклады, открытые в заданный промежуток времени:

Between #1/10/2003# And #1/102004#.

Для поиска вкладов с минимальной суммой в определенном диапазоне, необходимо ввести в это поле условие отбора >10 And < 100.

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

Year([ДатаОткрытияВклада])=2004.

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

Month([ДатаЗакрытияВклада])=Month(Now()).

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

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

Создание запросов интересно не только тем, что вы можете в виде одной таблицы представить данные из нескольких связанных таблиц и отобрать нужные записи из этих таблиц. Вы можете создавать столбцы в запросе, которые являются результатом вычислений над значениями других столбцов. Такие столбцы называются вычисляемыми. Это существенно расширяет возможности запросов. Простейшим примером вычисляемого поля в запросе может быть поле, которое объединяет имя и фамилию человека. Чтобы создать такое поле, нужно ввести новое имя поля и через двоеточие выражение, которое вычисляет требуемое значение, в строку Поле свободного столбца бланка запроса. В данном примере это выражение представляет собой объединение полей, содержащих имя и фамилию вкладчика, с пробелом между ними. В этом выражении мы используем ссылки на поля таблицы, которые в выражении заключаются в квадратные скобки (рис. 2).

ВАЖНО: имена полей нужно вводить абсолютно точно так как они заданы в таблицах!

Полное имя:[Вкладчики банка]![Фамилия] & " " & [Вкладчики банка]![Имя]

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

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

Если выражение длинное, его неудобно писать в строке Поле. Нажмите комбинацию клавиш Shift+F2. Появится диалоговое окно Область ввода, в котором удобнее вводить длинное выражение (рис. 3).

Рис. 3. Диалоговое окно Область ввода.

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

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

Запрос в Access является объектом, который сохраняется в файле базы данных и может многократно повторяться. Все запросы, которые мы создавали до сих пор, содержали конкретные значения дат, названий, имен и т. д. Если требуется повторить такой запрос с другими значениями в условиях отбора, его нужно открыть в режиме Конструктора, изменить условие и выполнить. Чтобы не делать многократно этих операций, можно создать запрос с параметрами. При выполнении такого запроса выдается диалоговое окно Введите значение параметра (рис. 4), в котором пользователь может ввести конкретное значение и затем получить нужный результат.

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

Рис.4. Диалоговое окно Введите значение параметра.

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

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

Примеры выражений с параметрами (параметры выделены):

Условие отбора: = [Введите фамилию вкладчика];

Условие отбора: = [Введите номер вклада];

Вычисляемое поле:

= [Введите сумму вклада] *СрокВклада*Процент/36500.

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

Итоговый запрос является одной из разновидностей запроса на выборку, поэтому в нем можно использовать все возможности обычных запросов-выборок. Итоговые запросы группируют данные только по столбцу. Например, можно подсчитать количество элементов в одном или нескольких полях таблицы с помощью функции Count. Можно также вычислять средние значения, находить наибольшее и наименьшее значения, самую давнюю и самую последнюю дату. Кроме того, в Microsoft Office Access 2007 предусмотрена строка итогов, с помощью которой можно производить вычисления с данными в таблице, не изменяя структуру запроса.

В Office Access 2007 можно добавлять к запросу статистические функции двумя способами.

· Откройте запрос в режиме таблицы и добавьте строку итогов: Главная – Записи –Итоги .

· Создайте итоговый запрос. Для этого откройте запрос в режиме конструктора и отобразите строку итогов с помощью кнопки Итоги на вкладке Конструктор - Показать или скрыть - Итоги или выбрать команду Итоги из контекстного меню, щелкнув правой кнопкой на пустом поле. В запросе появится строка Групповая операция (рис. 6). Выберите нужную функцию для подведения итогов.

Рис. 6. Список операций для итогового запроса.

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

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

Создание перекрестного запроса с помощью мастера

1. Выбрать в окне базы данных в группу Запросы и нажать кнопку Создать.

2. Выбрать строку Перекрестный запрос в окне Новый запрос.

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

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

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

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

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

2. На вкладке Конструктор в группе Тип запроса выбрать Перекрестный. В бланк запроса будут добавлены строки Групповая операция и Перекрестная таблица (рис. 7).

Рис. 7. Бланк перекрестного запроса.

3. Ячейки строки Перекрестная таблица могут содержать одно из четырех значений: Заголовки строк, Заголовки столбцов, Значение, Не отображается. Для перекрестного запроса надо определить, по крайней мере, одно поле в качестве заголовков строк, одно поле для заголовков столбцов и одно поле значений. Пункт Значение можно выбрать только для одного поля.

4. Строка Групповая операция используется для группировки данных и для итоговых вычислений. Выбрать в этой строке:

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

- Группировка для поля, значения которого должны быть заголовками столбцов;

- итоговую функцию или ввести выражение, содержащее итоговую функцию для поля с установкой Значение.

5. Для отбора данных, включаемых в набор записей запроса, можно использовать дополнительные поля. Для них необходимо выбрать Условие в строке Групповая операция и Не отображается в строке Перекрестная таблица. Эти поля не выводятся в результатах запроса.

6. Можно определить для каждого поля запроса условие отбора данных.

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

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