Выбор данных из одной таблицы
Лабораторная работа №4.
Работа с данными при помощи запросов
Цель лабораторной работы: изучить возможности работы с данными посредством запросов; научиться создавать запросы разных типов из одной и более таблиц.
Порядок выполнения работы:
Научиться создавать запросы на выбор из одной и нескольких таблиц.
Научиться задавать условия отбора.
3. Освоить использование вычисляемых полей и итоговых значений в запросах.
4. Изучить действие запросов на изменение.
Работа с данными при помощи запросов
В режиме таблицы доступны самые разные операции с данными – просмотр, сортировка, фильтрация, обновление и печать. Однако очень часто приходится проводить вычисления или просматривать данные из нескольких таблиц. Отобрать нужные данные можно с помощью запросов.
После выполнения запроса на выборку(который отбирает информацию из таблиц и других запросов базы данных, в то время как при выполнении запросов на изменение данные вставляются, обновляются или удаляются) Access создает набор записей, содержащий отобранные данные. В большинстве случаев с набором записей можно работать точно так же, как с таблицей: можно просматривать и выбирать информацию, печатать и даже обновлять данные. Однако в отличие от реальной таблицы, этот набор записей физически не существует в базе данных. Access создает набор записей из данных таблицы только во время выполнения запроса. Если вы измените данные в наборе записей, Access внесет соответствующие изменения в таблицы, на базе которой построен запрос.
Выбор данных из одной таблицы
Одним из преимуществ запросов является то, что они позволяют достаточно быстро отобрать необходимые данные из нескольких связанных таблиц. Но запросы полезны и при работе с одной таблицей. Все приемы, используемые при работе с единственной таблицей, годятся и для сложных многотабличных запросов, поэтому мы начнем с запросов на выборку данныходной таблицы.
На рис.1. показан запрос в режиме конструктора. Окно конструктора запросов разделено на две части. В верхней находятся списки полей таблиц или запросов, на основе которых создается новый запрос. В нижней располагается бланк QBE (Query By Example – запрос по образцу), в котором выполняется вся работа по созданию запроса. Каждый столбец бланка представляет одно поле, используемое в запросе. Поле может просто принадлежать одной из таблиц, быть вычисляемым (иными словами, его значение рассчитывается на основе одного или нескольких полей таблицы), или итоговым, то есть использующим одну из встроенных функций Microsoft Access.
Первая строка бланка запроса служит для выбора полей, которые должны присутствовать в наборе записей, используется для сортировки данныхили для выбора информации из таблицы. Полям запроса можно присвоить имена, которые будут отображаться в заголовках столбцов привыводенабора записей запроса, а для генерации вычисляемых полей можно использовать выражения любой степени сложности.
Если была выполнена команда Вид/Имена таблиц, во второй строке бланка запроса Access выведет имя таблицы, из которой выбрано поле. В третьей строке бланка можно задать сортировку по возрастанию или по убыванию.
Флажки в строке бланка Вывод на экранотвечают за вывод полей в наборе записей. По умолчанию выводятся все поля, включенные в бланк запроса.
Рис. 1. Запрос на основе таблицы тАбитуриенты в режиме
Для ввода условия отбора записей используется строка Условие отбора и строки или. На рис.1. показан запрос на выборку фамилий тех студентов, которые имеют оценки от 4 до 5 баллов.
Включение полей в запрос
Первым шагом при создании запроса является выбор полей, включаемых в набор записей. Это можно сделать несколькими способами. Можно просто перетащить поле с помощью мыши в нужный столбец бланка из списка полей верхней части окна. При перетаскивании поля указатель мыши превращается в маленький прямоугольник. Или произвести двойной щелчок на нужном поле в списке полей таблицы в верхней части окна.
В самом начале списка полей, находящегося в верхней половине окна запроса (а также в раскрывающихся списках в первой строке бланка QBE), находится специальный символ «*», означающий «Все поля». Если необходимо включить в запрос все поля таблицы, не надо определять каждое по отдельности в бланке QBE. Достаточно перетащить «*» из списка полей в бланк QBE. Учтите, что вы можете повторно включить столбец поля таблицы в бланк запроса, чтобы определить условия отбора для них. В этом случае, чтобы поля дважды не выводились в наборе записей запроса снимите флажки Вывод на экран в столбцах бланка, содержащих повторяющиеся поля.
В общем случае поля, выводимые в наборе записей запроса, наследуют свойства, заданные для соответствующих полей таблицы. Можно задать другие свойства с помощью команды Вид/Свойства(кнопка 
).
Выбор данных из нескольких таблиц производится аналогично. Для этого в окно конструктора запроса нужно вывести те таблицы, поля которых будут использоваться в запросе (это можно сделать в окне Добавление таблицы, которое можно всегда вызвать кнопкой 
), а затем включить нужные поля таблиц в запрос.
Ввод условий отбора
Ввод условия отбора в запросе аналогичен заданию условия на значение для поля таблицы. Когда вы вводите условия отбора для нескольких полей, то все выражения в строке Условие отбора или в строке или должны принимать значение Истина для любой записи, включаемой в набор записей запроса. Это означает, что Асcess выполняет логическую операцию AND над условиями отбора, находящимися в одной строке. В таблице приведены значения выражения, состоящего из двух условий, связанных оператором AND. Очевидно, чтобы результат операции AND имел значение Истина, оба условия должны быть истинными; только в этом случае запись отбирается запросом.
Когда вы задаете для некоторого поля несколько условий отбора, соединенных логическим оператором OR, то для того, чтобы запись была отобрана истинным должно быть хотя бы одно из них. Есть два способа задать несколько связанных оператором OR условий для одного поля. Можно ввести все условия в одну ячейку строки Условие отбора, соединив их оператором OR. Другой вариант: ввод каждого условия в отдельную ячейку строки или. При использовании нескольких строк или для отбора записи достаточно выполнения всех условий в одной из строк или.
Операторы и и или применяются как отдельно, так и в комбинации. Следует помнить, что условия связанные оператором и выполняются раньше условий, объединенных оператором или.
Результат применения к двум условиям логических операций AND и OR
|
|
Исключить группу данных из состава анализируемых запросом записей позволяет следующий критерий
< > 4
В этом случае можно не использовать кавычки.
Оператор Between позволяет задать диапазон значений, например:
between 10 and 20
Оператор Inпозволяет задавать используемый для сравнения список значений. Например:
in (“первый”,”второй”,”третий”)
Оператор Likeполезен для поиска образцов в текстовых полях, причем можно использовать шаблоны:
* – обозначает любое количество (включая нулевой) символов;
? – любой одиночный символ;
# – указывает что в данной позиции должна быть цифра.
Например: для выбора фамилии, начинающейся с буквы П и с окончанием “ов” можно записать
like П*ов
Можно ввести дату и время, при этом значения должны быть заключены между символами #. Например:
#10 мая 1998#
>#31.12.96#
В Access используется ряд других функций, которые помогут задать условия отбора для даты и времени, например:
Day(дата) – возвращает значение дня месяца в диапазоне от 1 до 31
Month(дата) – возвращает значение месяца года в диапазоне от 1 до 12
Year(дата) – возвращает значение года в диапазоне от 100 до 9999
Создать параметрический запрос.Запросы, представляющие собой варианты базового запроса и незначительно отличающиеся друг от друга, называются параметрическими. Результирующая таблица будет зависеть от того параметра, который пользователь введет при запуске запроса. На рис.2,а Представлен запрос, который позволяет выбрать из таблицы результатов сдачи экзамена группу студентов, которые сдали этот экзамен на ту или иную оценку. В поле Условие отбора в квадратных скобках указывается текст, который появляется в диалоговом окне при запуске запроса и приглашает ввести нужный параметр запроса рис.2,б. После ввода нужного значения нажмите ОК и перед Вами появится результирующая таблица рис.2,в. Очевидно, что если бы был введен другой параметр запроса (например оценка «2»), то список студентов был бы совершенно другой.
а)
б) в)
Рис.2. Параметрический запрос Балл.
Здание №1
1. Откройте БД «Отдел кадров +Ваша фамилия».
2. Создайте запрос «Зарплата», в котором выводятся Ф.И.О. сотрудника, пол, подразделение, должность, оклад и стаж работы. Список должен быть в алфавитном порядке.
3. Создайте запрос «Дети», позволяющий вывести в алфавитном порядке Ф.И.О. сотрудников и имена их детей.
4. Создайте запрос «Стаж», который выводит список сотрудников (мужчин) стаж работы которых больше 5 лет.
5. Создайте параметрический запрос «День рождения», позволяющий узнать дату рождения того или иного сотрудника.
Вычисляемые поля
С любыми полями таблицы можно выполнять вычисления и сделать вычисляемое выражение новым полем в наборе записей, при этом можно использовать любые из встроенных функций Access. Кроме того, поля запроса могут содержать данные, получаемые с помощью арифметических операций над полями таблицы. Например, ЦенаТовара*Количество.
Вычисляемое поле может содержать вызовы встроенных функций Access и следующие операторы:
+ –складывает два арифметических выражения.
- –вычитает из первого арифметического выражения второе.
* –перемножает два арифметических выражения.
/ –делит первое арифметическое выражение на второе.
\ –округляет два арифметических выражения до целых значений и делит первое на второе. Результат округляется до целого.
^ –возводит первое арифметическое выражение в степень, задаваемую вторым арифметическим выражением.
MOD –округляет оба арифметических выражения до целых значений, делит первое на второе и возвращает остаток.
& –создает текстовую строку как результат присоединения второй строки к концу первой. Если один из операндов является числом, то оно автоматически преобразуется в строку символов.
На рис. 3 показан пример вычисляемого поля для таблицы тАттестат. Вычисляемое поле Среднее выводит средний балл по трем предметам: Русский, Математика, Физика.
Рис. 3. Запрос на основе таблицы тАттестат с вычисляемым полем Среднее
Итоговые запросы
Для вычисления итоговых значений надо нажать кнопку Групповые операции( 
), чтобы в бланке QBE появилась строка Групповые операции. Access использует установку Группировка в строке Групповая операция для любого поля, занесенного в бланк запроса. Теперь записи по каждому полю группируются, но итог не подводится. Если выполнить запрос сейчас, вы получите набор записей, включающий по одной строке для каждого уникального значения поля запроса – но без итогов. Для получения итогов замените установку Группировка в строке Групповая операция на конкретные итоговые функции.
Access предоставляет девять функций, обеспечивающих выполнение групповых операций. Вы можете задать нужную вам функцию, введя ее имя с клавиатуры в строке Групповая операция бланка запроса или выбрав ее в раскрывающемся списке.
Итоговые функции Access:
Sum – вычисляет сумму всех значений заданного поля в каждой группе.
Avg –вычисляет среднее арифметическое всех значений данного поля в каждой группе.
Min – возвращает наименьшее значение, найденное в этом поле внутри каждой группы.
Max – возвращает наибольшее значение, найденное в этом поле внутри каждой группы.
Count – возвращает число записей, в которых значения данного поля отличны от Null.
StDev – стандартное отклонение всех значений данного поля в каждой группе.
Var –вычисляет дисперсию значений данного поля в каждой группе.
First –возвращает первое значение этого поля в группе.
Last – возвращает последнее значение этого поля в группе.
На рис.4 показан пример запроса с применением групповой операции Sum. Данный запрос является многотабличным и позволяет определить сумму выплат, сделанных каждым абитуриентом.
Рис. 4. Запрос с применением групповой операции Sum
Здание №2
- Откройте БД «Отдел кадров +Ваша фамилия».
- Создайте запрос «Зарплата на отдел», который выводит Название подразделения, вычисляемое поле Фонд заработанной платы отдела за месяц (ФЗПМ) и вычисляемое поле Фонд заработанной платы за год (ФЗПГ).
- Создайте запрос «Квалификация» позволяющий вывести в вычисляемом поле ФИО фамилию, имя и отчество сотрудников (в алфавитном порядке), название подразделения, должность и стаж работы.
- Создайте запрос «Ср Стаж», который позволяет вычислить средний стаж сотрудников.
- Создайте запрос «Сотрудники», вычисляющий число сотрудников.
- Создайте запрос «Средний оклад», который вычисляет средний оклад сотрудников.