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

Программа работы

1. Назначение запросов.

2. Виды запросов.

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

4. Выражения. Использование построителя выражений.

5. Групповые операции.

Краткие сведения

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

Естественно, было бы совершенно невозможно оперировать данными без того, если бы не существовала какая-нибудь возможность обойти это препятствие. Для этого в СУБД существуют так называемые запросы. Собственно говоря, запросы — это те же самые таблицы, только они заполняются не вручную, а при помощи заранее заданных формул и прочих зависимостей. Таким образом, то, что нельзя в таблице, можно в запросе. Складывать. Вычитать. Делить. Выполнять прочие математические или логические операции. А свое название запросы получили оттого, что они схожи с широко распространенным естественным действием аналогичного назначения. Например, запросом является ну хотя бы такая конструкция: «Кто конкретно покупал автомобиль ВАЗ 21099 вишневого цвета в период с января по декабрь 1998 года и расплачивался при этом наличной иностранной валютой, в качестве которой использовались швейцарские франки?». Столкнувшись с подобным запросом, СУБД самостоятельно просмотрит соответствующие таблицы, в которых хранятся фактические данные, и отберет из них все строки, которые соответствуют требованиям запроса. Причем с самими таблицами ничего не происходит, в таблицу запроса передаются лишь копии этих записей.

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

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

Место запросов в БД показано на рис. 5.1.

Рис. 5.1. Место запросов в БД

Запрос позволяет выполнять:

· Просмотр и редактирование данных из нескольких таблиц.

· Выбор полей и записей для просмотра.

· Сортировку записей.

· Вычисления и групповые операции.

· Обновление, удаление или добавление групп записей в таблицах или создание новой таблицы.

Запрос по образцу (Query by Example, QBE) изначально предназначался для пользователей приложений баз данных на больших машинах, чтобы они могли осуществлять поиск данных без знания языков программирования. Многие СУБД пришли к использованию запросов по образцу в той или иной форме.

Пример инструкции QBE в командной строке:

LIST ALL lastnames LIKE Lin* WITH state=”IL” IN us_hist

По этой инструкции производится поиск имен, начинающихся с ”Lin” в поле lastnames таблицы us_hist, окончания строк в поле игнорируются, и затем отбираются те записи, которые содержат значение ”IL” в поле state.

В процессе развития дисплеев появился более удобный метод создания запроса – графические запросы по образцу (graphical QBE), имеющие вид таблицы, заголовками столбцов которой являются имена полей одной или нескольких таблиц БД. Чтобы создать запрос, пользователь вводил в эти столбцы части полной инструкции, или выражения (expression). Ввиду того, что во время появления графических QBE графические дисплеи были редкостью, использовался текстовый режим – 25 строк по 80 символов, т.е. термин «графический» применительно к QBE употреблялся неправильно. В первых версиях графического QBE пример, приведенный выше, мог выглядеть так:

 

LASTNAME	FIRSTNAME	ADDRESS	CITY	STATE
LIKE Lin*				IL

 

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

После проверки полей, для которых заданы условия отбора, и нахождения подходящих записей в указанном файле, эти записи выводятся на экран в виде таблицы:

 

LASTNAME	FIRSTNAME	ADDRESS	CITY	STATE
Lincoln	Abraham	123 Elm St.	SpringField	IL
Lincoln	Mary Todd	123 Elm St.	SpringField	IL

 

Вводить выражения в столбцы графического QBE для многих пользователей оказалось гораздо проще, чем задавать инструкции QBE в командной строке, т.к. для этого требовалось знать синтаксис очень немногих выражений. Использование механизма графического QBE было одним из достоинств СУБД Paradox фирмы Ansa Software, занимавшей первое место на рынке настольных СУБД для ПК, пока ее не вытеснили оттуда СУБД dBASE II и dBASE III.

Виды запросов

Основные виды запросов:

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

· запросы-действия (запросы на изменение):

· запрос на создание таблицы,

· запрос на добавление записей,

· запрос на обновление записей,

· запрос на удаление записей;

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

 

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

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

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

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

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

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

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

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

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

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

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

1. Перетаскиванием. Для этого мышкой выделяем нужное поле в таблице или запросе-источнике и перетаскиваем его в нужный столбец бланка в строку Поле.

2. Двойным щелчком. Для этого делаем двойной щелчок по требуемому полю в таблице или запросе-источнике. Имя этого поля появляется в строке Поле первого свободного столбца бланка.

3. Выбором из списка в строке Поле. Для этого щелкнем мышкой по строке Поле первого свободного столбца бланка. Появится кнопка выбора из списка. Теперь нажимаем эту кнопку или клавишу <F4> и выбираем соответствующее поле (рис. 5.5).

Рис. 5.5. Добавление поля Месяц в запрос выбором из списка

Все эти способы равноценны за одним исключением. Если Вы забыли вставить в бланк какое-либо поле и теперь желаете вставить его между другими, уже занесенными в бланк полями, то эту операцию можно сделать только способом перетаскивания. Например, Вы хотите вставить поле Название месяца между полями Месяц и Рабочие дни. Для этого в таблице Календарь в верхней части окна QBE захватите поле Название месяца и перенесите в бланк результата прямо на поле Рабочие дни. Когда Вы отпустите мышку, поле Рабочие дни и все поля справа от него подвинутся вправо, а на его место встанет поле Название месяца.

Указание порядка сортировки

Для задания порядка сортировки результатов нужно поместить курсор в строку Сортировка столбца с названием соответствующего поля, нажать кнопку раскрытия списка или клавишу <F4> и выбрать из списка требуемый порядок сортировки (рис. 5.6).

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

Задание условия отбора исходных данных

Часто бывает нужно просмотреть не все данные таблицы а лишь некоторые в соответствии с каким-то условием. Условие отбора задается в строке Условие отбора. Это может быть простое условие, набранное с клавиатуры и представляющее собой одну из возможных величин. Например, чтобы отобрать сотрудников, имеющих больше 3 детей, достаточно в столбце Количество детей указать в строке Условие отбора значение «>3». Это также может быть более сложное выражение, как набранное с клавиатуры, так и сконструированное при помощи специального инструмента – Построителя выражений (см. ниже).

Условие может начинаться с операторов сравнения: =, <, >, <=, >=, <>. Если оператор сравнения не указан, подразумевается =. Оператор сравнения обычно используется для чисел и дат. Применительно к текстовым полям сравнение ведется в алфавитном порядке. Для текстов значение в поле должно полностью совпадать с указанным в условии, но можно использовать символ «*», заменяющие любые символы в любом количестве в данной позиции, и символ «?», заменяющие один символ в данной позиции. Например, для поиска сотрудника по имени Иван в поле, содержащем фамилию, имя и отчество, нужно указать условие «* иван *» (пробелы вокруг имени тут используются, чтобы исключить фамилии и отчества типа Иванов, Иванович). Условие поиска при выполнении преобразовывается: даты обрамляются «#», текстовые фрагменты кавычками, а если они содержат символы «*» или «?», перед фрагментом вставляется оператор Like. При поиске в текстовом поле регистр игнорируется. (Подробнее о применении оператора Like см. стр. 16, пункт «Другие операторы»).

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

Флажок вывода на экран

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

Сохранение запроса

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

- Закрыть запрос, щелкнув по кнопке Закрыть . Access выдаст сообщение для сохранения запроса (рис. 5.8).

Рис. 5.8. Предупреждающее сообщение

- Нажмите кнопку Да. Появится диалоговое окно Сохранение (рис. 5.9). Если нажать кнопку Отмена или клавишу <Esc>, то запрос не будет закрыт. Если нажать кнопку Нет, запрос будет закрыт без сохранения, т.е. исчезнет.

Рис. 5.9. Присвоение имени запросу в диалоговом окне Сохранение

- В текстовом поле Имя запроса введите имя запроса, например Запрос Календарь, и нажмите кнопку ОК. Теперь запрос сохранен под этим именем.

Элементы выражений

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

· Операторы — обычные арифметические (+, –, *, /) и другие символы и аббревиатуры. Большинство других операторов Access эквивалентны операторам языков программирования типа Basic, кроме специфичных для Access или SQL операторов, например, Between, In, Is и Like.

· Константы состоят из значений, которые вы вводите, например: 12345 или ABCDE. Чаще всего константы применяются для создания значений по умолчанию и, в сочетании с идентификаторами полей, для сравнения значений в полях таблиц.

· Идентификаторы — это имена объектов в Access (таких, как поля таб­лиц), которые возвращают определенные числовые или текстовые значения. Термин возвращают в применении к выражениям означает подстановку в выражение текущего значения идентификатора вместо его имени. Например, идентификатор имени поля [Company Name] в выражении возвращает значение поля Company Name из текущей вы­деленной записи. В Access есть пять заранее предопределенных посто­янных именованных констант, которые также служат идентификато­рами: True, False, Yes, No и Null. Именованные константы и пе­ременные, создаваемые в Access VBA, — это тоже идентификаторы.

· Функции, такие как Date(), возвращают в выражение значение вместо име­ни функции. Большинство функций имеют аргументы, в качестве которых используются идентификаторы или значения. Аргументы заключаются в скобки и разделяются точкой с запятой.

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

Примечание

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

Операторы

Для создания выражений в Access существует шесть категорий операторов:

· Арифметическиеоператоры выполняют сложение, вычитание, умно­жение и деление.

· Операторы присваиванияи сравненияустанавливают и сравнивают значения,

· Логическиеоператоры работают со значениями, которые могут быть только истинными или ложными.

· Операторы слияния строковых значений (конкатенации)объединяют строки символов.

· Операторы идентификации "!" и "."создают однозначные имена для объектов баз данных, так что можно, например, присвоить одно и то же имя полю в нескольких таблицах и запросах.

· Другие операторы, такие как Like, Is и Between, упрощают создание выражений для выборки записей в запросах.

Арифметические операторы

Арифметические операторы оперируют только с числовыми значениями и должны иметь два числовых операнда, кроме случаев:

· Когда знак минус (–) меняет знак операнда. В этом случае знак ми­нус называется унарным минусом.

· Когда знак равенства (=) присваивает значение объекту Access или идентификатору переменной Access VBA.

В табл. 5.1 приведен список арифметических операторов для выражений Access.

Таблица 5.1. Арифметические операторы

Оператор	Описание	Пример
+	Складывает два операнда	[Subtotal] + [Tax]
–	Считает разность двух операндов	Date() – 30
– (унарный)	Меняет знак операнда	-12345
*	Перемножает два операнда	[Units] * [Unit Price]
/	Делит один операнд на другой	[Quantity] / 12.55
\	Делит один целый операнд на другой нацело	[Units] \ 2
Mod	Возвращает остаток от деления нацело	[Units] Mod 12
^	Возводит операнд в степень	[Value] ^ [Exponent]

 

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

\	Символ деления нацело. При использовании деления нацело операнды с десятичными дробями округляются до целого, а дробные части отбрасываются.
Mod	Этот оператор возвращает значение остатка от деления нацело. Например, 13 Mod 4 возвращает 1.
^	Оператор возведения в степень возводит первый операнд в степень с показателем, равным второму операнду.

Эти три оператора редко применяются в деловых приложениях, зато часто встречаются в программах Access.

Логические операторы

Логические операторы (также называемые Булевскими операторами) чаще всего используются для объединения результатов двух или более выражений сравнения в единое целое. Логические операторы могут соединять только выражения, возвращающие логические значения True, False или Null. Логические операторы всегда требуют двух операндов, за исключением Not — логического эквивалента унарного минуса.

В табл. 5.3 приведен список логических операторов Access.

Логические операторы And, Or и Not интенсивно используются в выражениях Access.

Таблица 5.3. Логические операторы

Оператор	Описание	Пример 1 Пример 2	Результат 1 Результат 2
And	Конъюнкция (Логическое И)	True And True True And False	True False
Or	Дизъюнкция (Логическое ИЛИ)	True Or False False Or False	True False
Not	Логическое отрицание	Not True Not False	False True
Xor	Исключающее ИЛИ	True Xor False True Xor True	True False

 

Другие операторы

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

Таблица 5.4. Другие операторы

Оператор	Описание	Пример
Is	При использовании вместе с Null определяет, является ли значение Null или Not Null	Is Null Is Not Null
Like	Определяет, начинается ли строко­вое значение с одного или более зна­ков (для правильной работы Like нуж­но добавить символ шаблона "*" или один или несколько символов "?")	Like "Jon*" Like "FILE????"
In	Определяет, является ли строковое значение элементом списка значений	In("СА", "OR", WA")
Between	Определяет, находится ли числовое значение в определенном диапазоне значений	Between 1 And 5

 

Применение знаков (символов шаблона) "*" и "?" с оператором Like такое же, как и в программе поиска файлов. Символ шаблона "*" замещает любое число знаков, символ шаблона "?" замещает только один знак. Например, выражение Like "Jon*" возвращает True для таких значений, как "Jones" или "Jonathan". Выражение Like "*on*" возвращает True для любого значения, содержащего "on". Выражение Like "file????" возвращает True для FILENAME, но не для FILE002 или FILENUMBER.

Символы шаблона "*" и "?" могут предшествовать вводимым знакам, например, как в Like "*son" или в Like "????NAME".

Операторы этой категории, кроме Is, эквивалентны зарезервированным словам SQL like, In и BETWEEN. Access включает эти операторы для совместимости с SQL. Любой из них можно создать с помощью других oneраторов и функций Access.

Like "Jon*" ó InStr(LeftS(FieldName,3),"Jon")

In("CA","OR","WA") ó InStr("CAORWA",FieldName),

но в первом случае исключено двусмысленное соответствие по АО и RW.

Between I And 5 ó >= 1 And <= 5.

Идентификаторы

Идентификатор — это обычно имя объекта. Базы данных, таблицы, поля, запросы, формы и отчеты — объекты Access. У каждого объекта есть имя, однозначно его идентифицирующее. Иногда при идентификации подобъекта имя идентификатора состоит из имени семейства (класс объекта), от­деленного от присвоенного имени (имени объекта) восклицательным знаком или точкой (операторами "!" и "."). В идентификаторе имя семейства идет первым, за ним — разделитель, затем присвоенное имя. В SQL разделитель имен объектов — точка. Вот пример идентификатора объекта в инструкции SQL:

Клиенты.Адрес

В этом примере идентификатор объекта поля "Адрес" содержится в объекте таблицы "Клиенты". Клиенты — имя объекта (таблицы), а Адрес — присвоеннoe имя подобъекта (поля). В Access для разделения имен таблиц и полей используется "!", а точка разделяет объекты и их свойства. Иденти­фикатор, содержащий пробел или иную пунктуацию, заключается в квадратные скобки, как в этом примере:

[Персональные мероприятия]![Код сотрудника]

Имена идентификаторов не должны содержать точки и восклицательные знаки, например, [PA!ID] — недопустимое имя.

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

Функции

Функции возвращают значения и в выражениях аналогичны идентификаторам. Очень часто в выражениях Access применяется функция Now(), воз­вращающая дату и время часов компьютера. Скобки аргумента функции Now() могут быть пустыми. Если ввести Now() в качестве значения свой­ства "Значение по умолчанию" поля типа Дата/Время в таблице, то при просмотре данных, например, в 9:00 утра 15 марта 1995 года в поле появится 15/03/95 9:00.

В Access и VBA определено около 140 различных функций. В приводимом списке эти функции сгруппированы по назначению:

· Функции даты и времени управляют значениями Даты/Времени в полях или вводимыми как константы. С их помощью и дату, и время можно извлекать по частям (год или день месяца, часы или минуты)..

· Текстовые функции используются для работы с текстом.

· Функции преобразования типов данных позволяют назначить наибо­лее подходящий тип данных для значений числовых полей..

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

· Финансовые функции похожи на функции Lotus 1-2-3 и Microsoft Excel. Они вычисляют амортизацию, платежи, банковскую учетную ставку и т. п.

· Функции смешанного типа не попадают в предшествующую классификацию, они нужны для создания запросов, форм и отчетов Access.

· Другие функции состоят из функций динамического обмена данных (DDE) между приложениями Windows, статистических функций над подмножествами записей и функций, используемых главным oбразом для программирования в Access VBA.

В следующем разделе эти функции описаны более подробно.

Пользователь может сам определить функции с помощью программного кода Access VBA.

Построитель выражений

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

Для открытия окна Построителя выражений необходимо:

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

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

· Щелкнуть правой кнопкой для вызова контекстного меню и выбрать в нем команду Построить…

· Нажать кнопку Построить (с волшебной палочкой) на панели инструментов

В результате откроется окно Построителя выражений (рис. 5.10).

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

Построитель выражений состоит из трех разделов (рис. 5.11).

Рис. 5.11. Назначение элементов построителя выражений

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

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

В нижней части окна построителя находятся три поля.

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

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

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

Примечания

· Чтобы новые имена полей текущего запроса появились в построителе выражений, следует предварительно сохранить этот запрос.

· Если функция или объект не выводятся в нижней части построителя выражений, то это значит, что их использование недопустимо в позиции, из которой был вызван построитель. Например, нельзя ссылаться на другое поле или элемент управления в условии на значение для поля в режиме конструктора таблицы, таким образом папки Таблицы, Запросы, Формы и Отчеты не доступны при запуске построителя выражений из ячейки для свойства поля Условие на значение в режиме конструктора таблицы.

Групповые операции

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

Группировка - понятие тонкое, но весьма важное и порою даже совершенно незаменимое. По умолчанию, в строке Групповые операции СУБД устанавливает в каждом столбце значение «Группировка», в соответствии с которым данные по каждому полю группируются, но результат не выводится. Если же нужен некий конкретный результат, то вместо «группировки» следует применить одну из девяти групповых функций (табл. 5.5).

Таблица 5.5. Функции, используемые в групповых операциях

Наимено­вание	Назначение
SUM	Вычисляет сумму всех значений заданного поля в каждой группе. Применяется только для числовых и денежных полей.
AVG	Вычисляет среднее арифметическое всех значений данного поля в каждой группе. Применяется только для числовых и денежных полей.
MIN	Возвращает наименьшее значение в рамках каждой группы. Применяется для любых полей, кроме логических и "мемо". В случае текстового содержания, возвращается наименьшее символьное значение независимо от регистра.
MAX	Возвращает наибольшее значение в рамках каждой группы. Применяется для любых полей, кроме логических и "мемо". В случае текстового содержания, возвращается наибольшее символьное значение независимо от регистра.
COUNT	Возвращает число записей, в которых значение данного поля отличается от Null. Хотя при использовании конструкции Count (*) можно подсчитать число всех записей, включая "нулевые".
STDEV	Подсчитывает статистическое стандартное отклонение для всех значений данного поля в каждой группе. Функция применяется только для числовых и денежных полей. Если в группе менее двух записей, возвращается значение Null.
VAR	Подсчитывает статистическую дисперсию для всех значений данного поля в каждой группе. Применяется только к числовым и денежным полям. Если в группе менее двух записей, возвращается значение Null.
FIRST	Возвращает первое значение этого поля в группе.
LAST	Возвращает последнее значение этого поля в группе.

 

Кроме стандартных, в Microsoft Access допустимы и два дополнительных варианта: "Выражение" и "Условие". Выбрав вариант "Выражение" в строке "Поле", можно задать конкретное выражение, например:

Max[(наименование поля)] - Min[(наименование поля)],

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

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

Содержание отчета

1. Указать цель работы.

2. Привести описание созданных запросов по следующей схеме:

· что требовалось найти,

· текст запроса в виде SQL-инструкций,

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

Контрольные вопросы

1. Что такое запрос?

2. Назовите цели применения запроса

3. Типы запросов.

4. Как создать запрос на выборку?

5. Как добавить таблицу в запрос?

6. Как удалить таблицу из запроса?

7. Как добавить поля в запрос?

8. Как удалить поля из бланка запроса?

9. Как ввести условия отбора в запрос?

10. Как задать параметры сортировки в запросе?

11. Как снять условия сортировки?

12. Как задать вычисляемое поле в запросе?

13. Как задать расчёт итоговых полей в запросе?

14. Как задать условие отбора в итоговом запросе?

15. Как скрыть поля в запросе?

16. Как задать свойства объектов запроса?

17. Как создать запрос с параметрами?

18. Как указать типы данных для параметров запроса?

 

 

Программа работы

1. Назначение запросов.

2. Виды запросов.

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

4. Выражения. Использование построителя выражений.

5. Групповые операции.

Краткие сведения

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

Естественно, было бы совершенно невозможно оперировать данными без того, если бы не существовала какая-нибудь возможность обойти это препятствие. Для этого в СУБД существуют так называемые запросы. Собственно говоря, запросы — это те же самые таблицы, только они заполняются не вручную, а при помощи заранее заданных формул и прочих зависимостей. Таким образом, то, что нельзя в таблице, можно в запросе. Складывать. Вычитать. Делить. Выполнять прочие математические или логические операции. А свое название запросы получили оттого, что они схожи с широко распространенным естественным действием аналогичного назначения. Например, запросом является ну хотя бы такая конструкция: «Кто конкретно покупал автомобиль ВАЗ 21099 вишневого цвета в период с января по декабрь 1998 года и расплачивался при этом наличной иностранной валютой, в качестве которой использовались швейцарские франки?». Столкнувшись с подобным запросом, СУБД самостоятельно просмотрит соответствующие таблицы, в которых хранятся фактические данные, и отберет из них все строки, которые соответствуют требованиям запроса. Причем с самими таблицами ничего не происходит, в таблицу запроса передаются лишь копии этих записей.

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

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

Место запросов в БД показано на рис. 5.1.

Рис. 5.1. Место запросов в БД

Запрос позволяет выполнять:

· Просмотр и редактирование данных из нескольких таблиц.

· Выбор полей и записей для просмотра.

· Сортировку записей.

· Вычисления и групповые операции.

· Обновление, удаление или добавление групп записей в таблицах или создание новой таблицы.

Запрос по образцу (Query by Example, QBE) изначально предназначался для пользователей приложений баз данных на больших машинах, чтобы они могли осуществлять поиск данных без знания языков программирования. Многие СУБД пришли к использованию запросов по образцу в той или иной форме.

Пример инструкции QBE в командной строке:

LIST ALL lastnames LIKE Lin* WITH state=”IL” IN us_hist

По этой инструкции производится поиск имен, начинающихся с ”Lin” в поле lastnames таблицы us_hist, окончания строк в поле игнорируются, и затем отбираются те записи, которые содержат значение ”IL” в поле state.

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