10.2 Создание запросов
Главное назначение БД (хранение и предоставление информации) может потребоваться либо сразу после ввода данных, либо спустя некоторое время (месяцы, года).
Запрос - требование предоставить информацию, накопленную в таблицах. Информацию можно получить с помощью инструментов запроса. Запрос можетотноситься к одной или нескольким связанным таблицам. После создания и запуска запроса множество кортежей в MS Access можно отобразить в виде строк и столбцов.
Запросы достаточно гибки и позволяют просматривать данные так, как будет удобно конечному пользователю. Большинство СУБД постоянно совершенствуются, создавая все более мощные инструменты. С их помощью можно: 1) выбирать данных из таблиц; 2) выбирать поля; 3) выбирать записи; 4) сортировать записи; 5) выполнять вычисления; 6) создавать таблицы; 7) создавать формы и отчеты на основе запроса; 8) создавать диаграммы на основе запроса; 9) использовать запрос в качестве источника данных для других запросов (подзапрос); 10) вносить изменения в таблицы.
Результирующий набор данных является динамическим набором данных и не хранится в базе данных. При сохранении запроса сохраняется только структура запроса - перечень таблиц, полей таблиц, порядка сортировки, ограничения на значения полей, тип запроса и т.д. (экономия места на физическом носителе информации, учет внесенных изменений в записи).
Типы запросов:
Запрос на выборку. Самый распространенный. Извлекает данные из одной или нескольких таблиц, результаты отображает в режиме таблицы, в котором допускается изменение записей (при некоторых ограничениях). Используется для группировки записей, для вычислений (групповые операции), подсчета количества записей и расчета итоговых значений других типов. Важным умением построения эффективных запросов является умение задавать условия отбора записей.
Запрос с параметрами. При запуске такого запроса открывается одно или несколько диалоговых окон с приглашением ввести определенные сведения (условия отбора, значения для вставки в поля). Во время вывода отчета, основанного на запросе такого типа, также будет появляться диалоговое окно.
Перекрестный запрос. Отображает результаты статистических расчетов, выполненных по данным из одного поля. Эти результаты группируются по двум наборам данных в формате перекрестной таблицы: первый набор выводится в столбце слева и образует заголовки строк, второй выводится в верхней строке и образу ет заголовки столбцов.
SQL. Создается с помощью инструкций SQL: запросы на объединение, запросы к серверу, управляющий запрос и подчиненный запрос.
Запрос на изменения. Данный запрос позволяет, выполнив одну операцию, внести изменения во многие записи. Типы запросов на изменения: на создание таблицы, на удаление записей, на добавление записей и на обновление записей.
Операторы, используемые при создании запасов на выборку:
Математические
|
Описание
|
+
|
Складывает два числовых выражения
|
-
|
Вычитает из первого числового выражения второе
|
*
|
Перемножает два числовых выражения
|
/
|
Делит первое число на второе
|
\
|
Округляет два числовых выражения до целых значений и делит первое на второе.результат округляет до целого.
|
^
|
Возводит первое числовое выражение в степень, задаваемую вторым числовым выражением
|
MOD
|
Округляет ба числовых выражения до целых значений, делит первое на второе и возвращает остаток
|
&
|
Создает новую текстовую строку, присоединяя вторую строку к концу первой. Если один из операндов является числом, Access перед проведением конкатенации преобразует его в строку символов
|
Отношение
|
Логические
|
= (равно)
|
AND (и)
|
<> (не равно)
|
OR (или)
|
> (больше)
|
NOT (кроме)
|
< (меньше)
|
|
Вспомогательные
|
Описание
|
BETWEEN…AND
|
Определяет диапазон значений. Between 10 and 20 означает то же самое, что и >=10 and<=20
|
IN
|
Задает используемый для сравнения список значений. Выражение IN ("Понедельник", "Вторник", "Среда") означает то же самое, что и "Понедельник" or "Вторник" or "Среда"
|
IS NULL
|
Проверяет наличие пустых полей
|
LIKE
|
Полезен при поиске образов в текстовых полях. В образец поиска можно включить символы шаблона.
|
Символы шаблона, используемые в операторе LIKE:
Символ
|
Назначение
|
?
|
Любой символ (0-9, Аа – Zz, Аа - Яя)
|
*
|
Любое (включая нулевое) количество произвольных символов; используется для замены последовательности символов
|
#
|
Одна произвольная цифра
|
[список]
|
Допускает содержание символов из списка; можно указывать диапазон символов, например [А-Я], [A-Z], [3-10]
|
[!список]
|
Допускает содержание символов не из списка
|
Например: с помощью выражения Like "?[a-k]d[0-9]*" проверяет наличие произвольного символа в первой позиции, буквы от a до kво второй позиции, буквы dв третьей позиции, любого символа от 0 до 9 в четвертой позиции и произвольного числа символов в конце строки.
Условия отбора для дат и времени
MicrosoftAccess хранит значения дат и времени как десятичные числа длиной восемь байт. Значение слева от десятичной точки соответствует дате, а дробная часть числа представляет время суток с точностью до секунд. Чтобы сообщить Access о том, что вы вводите дату и время, заключайте значения в символы числа (#). Для указания конкретной даты используйте нотацию, удобную для вас. Например #15 апреля 2010#, #15/04/10# и #15-апр-2010# определяют одну и ту же дату.
Функции даты и времени
Функция
|
Описание
|
Day(дата)
|
Возвращает значение дня месяца в диапазоне от 1 до 31
|
Month(дата)
|
Возвращает значение месяца года в диапазоне от 1 до 12
|
Year(дата)
|
Возвращает значение года в диапазоне от 100 до 9999
|
Weekday(дата)
|
По умолчанию возвращает целое число от 1 (Воскресенье) до 7 (Суббота), соответствующее дню недели
|
Hour(дата)
|
Возвращает целое число от 0 до 23, представляющее значение числа
|
Datepart(интервал, дата)
|
Возвращает номер квартала или номер недели в зависимости от заданного кода интервала ("q" для определения квартала (значение от 1 до 4), "ww" - для определения порядкового номера недели в году (значение от 1 до 53)).
|
Date()
|
Возвращает текущую системную дату
|
Итоговыезапросы:групповыеоперации.
Иногда нас интересуют не отдельные записи в таблице, а итоговые значения по группам данных. Иногда необходимо узнать общее количество сотрудников фирмы или сумму фонда заработной платы. Ответы на подобные вопросы дает итоговый запрос. Для задания вычислений итоговых значений щелкните по кнопке Групповые операции на панели инструментов конструктора запросов, чтобы в бланке запроса появилась строка Групповая операция. Access представляет девять функций, обеспечивающих выполнение групповых операций. Вы можете задать нужную вам функцию, введя ее имя с клавиатуры или выбрав ее в раскрывающемся списке.
Итоговые функции.
Функция
|
Описание
|
Sum
|
Возвращает сумму всех значений данного поля в каждой группе. Используют только для числовых или денежных полей.
|
Avg
|
Возвращает среднее арифметическое всех значений данного поля в каждой группе. Используют только для числовых или денежных полей. При вычислении функции Access исключает значение Null.
|
Min
|
Возвращает наименьшее значение, найденное в данном поле внутри каждой группы. Для числовых полей возвращает наименьшее значение. Для текстовых полей - наименьшее из символьных значений независимо от регистра. Access игнорирует значение Null.
|
Max
|
Возвращает наибольшее значение, найденное в данном поле внутри каждой группы. Для числовых полей возвращает наибольшее значение. Для текстовых полей - наибольшее из символьных значений независимо от регистра. Access игнорирует значение Null.
|
Count
|
Возвращает число записей, в которых значения данного поля отличны от Null. Чтобы подсчитать число записей в каждой группе с учетом значений Null, введите в строку Поле специальное выражение Count(*)
|
StDev
|
Возвращает стандартное отклонение всех значений данного поля в каждой группе. Эта функция применяется только к числовым или денежным полям. Если в группе меньше двух строк, Access возвращает значение Null
|
Var
|
Возвращает дисперсию значений данного поля в каждой группе. Эта функция применима только к числовым или денежным полям. Если в группе менее двух записей, Access возвращает значение Null
|
First
|
Возвращает значение данного поля из первой записи, обнаруженной в группе
|
Last
|
Возвращает значение данного поля из последней записи, обнаруженной в группе
|
Использование параметров запроса.
На этапе создания запроса не всегда можно определить, какие именно значения должны использоваться при выполнении запроса.выходом из данной ситуации будет включение в запрос параметра, и при каждом выполнении запроса Access будет запрашивать конкретное условие отбора. Чтобы определить параметр, в строку Условие отбора вместо конкретного значения фразу, заключенную в квадратные скобки ([]). То, что заключено в квадратные скобки, Access рассматривает как имя параметра.
Для каждого параметра запроса можно установить тип данных. По умолчанию он текстовый, чтобы его изменить выполните команду Запрос->Параметры... И в появившемся диалоговом окне Параметры запроса установите необходимый тип данных.
ПРИМЕРЫ:
1. Выведите на экран окно «proba: База данных». Выберите режим "Запросы" и нажмите кнопку<Создать>. В окне «Новый запрос» выберите "Конструктор" и нажмите кнопку <ОК>.
2. В появившемся окне «Добавление таблицы» выберите раздел "Таблицы" и в нем название вашей таблицы из лабораторной работы №1 "Таблица1", нажмите кнопку<Добавить> и затем закройте окно.
3.Например, нам требуется составить выборку из таблицы базы данных, содержащую только данные офамилиях и годах рождения сотрудников. Тогда нужно ввести в нижней части окна "Запрос на выборку" в 1-ю колонку строки "Поле", слово "Фамилия", а во 2-ю колонку - слова "Год рождения". Для этого нужно нажать кнопку < >, а затем выбрать нужное поле.
4. Подайте команду Запрос/Запуск или нажмите кнопку  на панели инструментов. В результате появится окно «Запрос на выборку», содержащее таблицу сзапрашиваемыми данными.
5. Нажмите кнопкуЗакрыть. На вопрос «Сохранить?» ответьте "Да". Задайте логически значимое значение запроса.
2. ЗАПРОС НА ВЫБОРКУ С УСЛОВИЕМ:
|
1. Выполните шаги аналогичные 1 и 2 предыдущего запроса.
2.Например, требуется составить выборку из таблицы базы данных, содержащую фамилии тех сотрудников, которые родились позже 1950 г. и получают оклад менее 4000 тыс. руб.
3. Введите в нижней части окна в 1-ю колонку строки "Поле" слово "Фамилия".
4. Введите во 2-ю колонку в строку "Поле" слова "Год рождения", в строку "Условия отбора" – условие ">1950 " (вводите выражение в поле без кавычек).
5. Введите в 3-ю колонку в строку "Поле" слово Оклад", а в строку "Условия отбора" - условие " <4000 ".
6. Нажмите кнопкуЗакрыть. На вопрос "Сохранить?" ответьте "Да".
7. При открытии запроса появляется окно «Запрос на выборку», содержащее таблицу с запрашиваемыми данными.
3. ЗАПРОС С ВЫЧИСЛЯЕМЫМ ПОЛЕМ:
|
Предположим, что необходимо вывести информацию о надбавках для каждого сотрудника. Расчет надбавок производится в соответствии с формулой:
оклад сотрудника * коэффициент надбавок /100. Следовательно, для начала добавьте в таблице "Таблица 1" поле «Коэффициент надбавок» с числовым типом данных и заполнить таблицу значениями.
1. Создайте новый запрос с помощью конструктора и добавьте вашу таблицу "Таблица 1" в схему запроса.
2. Введите в нижней части окна в 1-ю колонку строки "Поле" слово "Фамилия", во 2-ю колонку - "Оклад", в 3-ю колонку - "Коэффициент надбавок".
3. Установите курсор в 4-ю колонку и вызовите Построитель выражений, нажав кнопку  на панели инструментов.
Примечание: Построитель выражений позволяет создавать сложные выражения. Рассмотрим структуру окна Построителя выражений:
В верхней части окна расположена пустая область ввода, предназначенная для создания выражения. Вы можете сами ввести выражение, но гораздо легче использовать кнопки операторов, расположенные прямо под областью ввода. В нижней части окна находятся три списка, предназначенные для поиска необходимых имен полей и функций.
4. Введите формулу необходимую для подсчета надбавок сотрудников:
Нажмите кнопку ОК. В строке Поле появится выражение вида: Выражение1: [Таблица1]![Оклад]*[Таблица 1]![Коэффициент надбавок]/100.
5. Запустите запрос и просмотрите результаты. В последнем столбе результатной таблицы отображается вычисленная сумма надбавок. Обратите внимание на заголовок столба.
6. Изменим подпись последнего столбца на Надбавки. Для этого перейдите в конструктор и вместо Выражение1 наберите Надбавки. Запустите запрос и посмотрите результат.
7. Сохраните запрос.
Примечания:
При просмотре этого запроса будут автоматически подсчитываться надбавки, в зависимости от введенных коэффициентов.
Обращаем ваше внимание на то, что данные, которые отображаются при выполнении запроса, не хранятся в БД.
4. ЗАПРОС С ПАРАМЕТРОМ:
Предположим, что иногда необходимо выводить списки сотрудников в зависимости от стажа работы на предприятии.
1. Добавьте в таблицу поле "Стаж работы", тип данных - числовой. Заполните его произвольными значениями.
2. Создайте новый запрос с помощью Конструктора.
3. Введите в нижней части окна в 1-ю колонку строки "Поле" слово "Фамилия", во 2-ю колонку - "Телефон", в 3-ю колонку - "Стаж работы".
4. В 3-е колонке в строку "Условие отбора" введите: [Укажите стаж работы].
5. Сохраните запрос и проверьте его работоспособность. При запуске данного запросы Access, будет выводить дополнительное диалоговое окно:
Введите любую цифру и просмотрите результат.
5. ИТОГОВЫЙ ЗАПРОС:
Предположим, что нам необходимо подсчитать количество сотрудников, которые родились позже 1950 г. и получают оклад менее 4000 тыс. руб. (запрос 2).
1. Выберите режим "Запросы" и нажмите кнопку Конструктор. В диалоговом окне Добавление таблицы перейдите на закладку Запросы и выберите Запрос 2.
2. Введите в 1-ю колонку строки "Поле" слово "Фамилия".
3. Щелкните по кнопке Групповые операции на панели инструментов конструктора запросов, чтобы в бланке запроса появилась строка Групповая операция.
4. Выберите из списка функций Count.
5. Сохраните запрос и проверьте его работоспособность.
|
