Лабораторная работа №4 Работа с данными при помощи запросов

Лабораторная работа №4 Работа с данными при помощи запросов 1 Цель   лабораторной   работы:  изучить   возможности   работы   с   данными   посредством запросов; научиться создавать запросы разных типов из одной и более таблиц. Работа с данными при помощи запросов В   режиме   таблицы   доступны   самые   разные   операции   с  данными   –  просмотр,   сортировка, фильтрация,   обновление   и   печать.   Однако   очень   часто   приходится   проводить   вычисления   или просматривать данные из нескольких таблиц. Отобрать нужные данные можно с помощью запросов. После выполнения  запроса на выборку  (который отбирает информацию из таблиц и других запросов базы данных, в то время как при выполнении запросов на изменение данные вставляются, обновляются или удаляются) Access создает набор записей, содержащий отобранные данные. В большинстве случаев с набором записей можно работать точно так же, как с таблицей: можно просматривать и выбирать информацию, печатать и даже обновлять данные. Однако в отличие от реальной таблицы, этот набор записей физически не существует в базе данных. Access создает набор записей из данных таблицы только во время выполнения запроса. Если вы измените данные в наборе записей, Access внесет соответствующие изменения в таблицы, на базе которой построен запрос. Выбор данных из одной таблицы Одним из преимуществ запросов является то, что они позволяют достаточно быстро отобрать необходимые данные из нескольких связанных таблиц. Но запросы полезны и при работе с одной таблицей. Все приемы, используемые при работе с единственной таблицей, годятся и для сложных многотабличных запросов, поэтому мы начнем с запросов на выборку данных одной таблицы. На рис.1. показан запрос в режиме конструктора. Окно конструктора запросов разделено на две части. В верхней находятся списки полей таблиц или запросов, на основе которых создается новый запрос. В нижней располагается бланк  QBE (Query By Example – запрос по образцу),  в котором выполняется вся работа по созданию запроса. Каждый столбец бланка представляет одно поле,   используемое   в   запросе.   Поле   может   просто   принадлежать   одной   из   таблиц,   быть вычисляемым   (иными   словами,   его   значение   рассчитывается   на   основе   одного   или   нескольких полей   таблицы),   или   итоговым,   то   есть   использующим   одну   из   встроенных   функций   Microsoft Access. Первая строка бланка запроса служит для выбора полей, которые должны присутствовать в наборе записей, используется для сортировки данных  или для выбора информации из таблицы. Полям запроса можно присвоить имена, которые будут отображаться в заголовках столбцов при выводе  набора   записей   запроса,   а   для   генерации   вычисляемых   полей   можно   использовать выражения любой степени сложности. Если была выполнена команда Вид/Имена таблиц, во второй строке бланка запроса Access выведет имя таблицы, из которой выбрано  поле. В третьей строке бланка можно задать сортировку по возрастанию или по убыванию. Флажки в строке бланка  Вывод на экран  отвечают за вывод полей в наборе записей. По умолчанию выводятся все поля, включенные в бланк запроса. 2 Рис. 1. Запрос на основе таблицы тАбитуриенты в режиме Для ввода условия отбора записей используется строка  Условие отбора  и строки  или. На рис.1. показан запрос на выборку фамилий тех студентов, которые имеют оценки от 4 до 5 баллов.  Включение полей в запрос Первым шагом при создании запроса является выбор полей, включаемых в  набор записей. Это можно   сделать   несколькими   способами.   Можно   просто   перетащить   поле   с   помощью   мыши   в нужный столбец бланка из списка полей верхней части окна. При перетаскивании поля указатель мыши превращается в маленький прямоугольник. Или произвести двойной щелчок на нужном поле в списке полей таблицы в верхней части окна.  В самом начале списка полей, находящегося в верхней половине окна запроса (а также в раскрывающихся   списках   в   первой   строке   бланка   QBE),   находится   специальный   символ  «*», означающий «Все поля». Если необходимо включить в запрос все поля таблицы, не надо определять каждое по отдельности в бланке QBE. Достаточно перетащить «*» из списка полей в бланк QBE. Учтите,   что   вы   можете   повторно   включить   столбец   поля   таблицы   в   бланк   запроса,   чтобы определить условия отбора для них. В этом случае, чтобы поля дважды не выводились в наборе записей запроса снимите флажки Вывод на экран в столбцах бланка, содержащих повторяющиеся поля. В общем случае поля, выводимые в наборе записей запроса, наследуют свойства, заданные для соответствующих   полей   таблицы.   Можно   задать   другие   свойства   с   помощью   команды Вид/Свойства (кнопка  ). Выбор данных из нескольких таблиц производится аналогично. Для этого в окно конструктора запроса  нужно вывести  те таблицы,  поля которых будут использоваться  в запросе  (это можно сделать   в   окне  Добавление   таблицы,   которое   можно   всегда   вызвать   кнопкой   ),   а   затем включить нужные поля таблиц в запрос.   Ввод условий отбора  Ввод условия отбора в запросе аналогичен заданию условия на значение для поля таблицы. Когда вы вводите  условия  отбора для нескольких полей, то все выражения  в  строке  Условие отбора  или в строке  или  должны принимать значение Истина для любой  записи, включаемой в набор   записей   запроса.   Это   означает,   что   Асcess   выполняет   логическую  операцию  AND  над условиями   отбора,   находящимися   в   одной   строке.   В   таблице   приведены   значения   выражения, 3 состоящего из двух условий, связанных оператором AND. Очевидно, чтобы результат операции AND  имел значение Истина, оба условия должны быть истинными; только в этом случае  запись отбирается запросом. Когда вы задаете для некоторого поля несколько условий отбора, соединенных логическим оператором OR, то для того, чтобы запись была отобрана истинным должно быть хотя бы одно из них.   Есть   два   способа   задать   несколько   связанных   оператором   OR   условий   для   одного   поля. Можно ввести все условия в одну ячейку строки Условие отбора, соединив их оператором OR. Другой   вариант:   ввод   каждого   условия   в   отдельную   ячейку  строки  или.   При   использовании нескольких строк или для отбора записи достаточно выполнения всех условий в одной из строк или.  Операторы  и  и  или  применяются как отдельно, так и в комбинации. Следует помнить, что условия связанные оператором и выполняются раньше условий, объединенных оператором или. Результат применения к двум условиям логических операций AND  и  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 4 Создать   параметрический   запрос.  Запросы,   представляющие   собой   варианты   базового запроса   и   незначительно   отличающиеся   друг   от   друга,   называются   параметрическими. Результирующая   таблица   будет   зависеть   от   того   параметра,   который   пользователь   введет   при запуске   запроса.     На   рис.2,а   Представлен   запрос,   который   позволяет   выбрать   из   таблицы результатов сдачи экзамена группу студентов, которые сдали этот экзамен на ту или иную оценку. В   поле  Условие   отбора  в   квадратных   скобках   указывается   текст,   который   появляется   в диалоговом окне при запуске запроса и приглашает ввести нужный параметр запроса рис.2,б. После ввода  нужного значения нажмите ОК и перед Вами появится результирующая  таблица рис.2,в. Очевидно, что если бы был введен другой параметр запроса (например оценка «2»), то список студентов был бы совершенно другой. а)         б) Рис.2. Параметрический запрос Балл. в) Здание №1 1. Откройте БД «Отдел кадров +Ваша фамилия».   2. Создайте запрос «Зарплата», в котором выводятся  Ф.И.О. сотрудника, пол, подразделение, должность, оклад и стаж работы. Список должен быть в алфавитном порядке. 3. Создайте запрос «Дети», позволяющий вывести  в алфавитном порядке Ф.И.О. сотрудников и имена их детей. 4. Создайте   запрос   «Стаж»,   который   выводит   список   сотрудников   (мужчин)   стаж   работы которых больше 5 лет. 5. Создайте параметрический запрос «День рождения», позволяющий узнать дату рождения того или иного сотрудника. 5 Вычисляемые поля С любыми полями таблицы можно выполнять вычисления и сделать вычисляемое выражение новым   полем   в   наборе   записей,   при   этом   можно   использовать   любые   из   встроенных   функций Access.   Кроме   того,   поля   запроса   могут   содержать   данные,   получаемые   с   помощью арифметических операций над полями таблицы. Например, ЦенаТовара*Количество. Вычисляемое   поле  может   содержать   вызовы   встроенных   функций   Access   и   следующие операторы:  +  – складывает два арифметических выражения. ­   – вычитает из первого арифметического выражения второе. *  – перемножает два арифметических выражения. /   – делит первое арифметическое выражение на второе. \  – округляет два арифметических выражения до целых значений и  делит первое на второе.  Результат округляется до целого. ^   –  возводит   первое   арифметическое   выражение   в   степень,   задаваемую   вторым арифметическим выражением. MOD – округляет оба арифметических выражения до целых значений, делит первое на второе и  возвращает остаток. & – создает текстовую строку как результат присоединения второй строки к концу первой. Если один из операндов является числом, то оно автоматически преобразуется в строку символов.  На рис. 3 показан пример вычисляемого поля для таблицы тАттестат. Вычисляемое поле Среднее выводит средний балл по трем предметам: Русский, Математика, Физика. Рис. 3. Запрос на основе таблицы тАттестат с вычисляемым полем Среднее Итоговые запросы Для вычисления итоговых значений надо нажать кнопку  Групповые операции( ), чтобы в бланке QBE появилась строка  Групповые операции.  Access использует установку  Группировка  в строке  Групповая   операция  для   любого   поля,   занесенного   в   бланк   запроса.   Теперь   записи   по каждому полю группируются, но итог не подводится. Если выполнить запрос сейчас, вы получите набор записей, включающий по одной строке для каждого уникального значения поля запроса – но без итогов. Для получения итогов замените установку Группировка в строке Групповая операция на конкретные итоговые функции.  Access предоставляет девять функций, обеспечивающих выполнение групповых операций. Вы 6 можете задать нужную вам функцию, введя ее имя с клавиатуры в строке Групповая операция бланка запроса или выбрав ее в раскрывающемся списке. Итоговые функции Access: Sum – вычисляет сумму всех значений заданного поля в каждой группе. Avg – вычисляет среднее арифметическое всех значений данного поля в каждой группе. Min – возвращает наименьшее значение, найденное в этом поле внутри каждой группы. Max – возвращает наибольшее значение, найденное в этом поле внутри каждой группы. Count  – возвращает число записей, в которых значения данного поля отличны от Null. StDev – стандартное отклонение всех значений данного поля в каждой группе. Var –вычисляет дисперсию значений данного поля в каждой группе. First – возвращает первое значение этого поля в группе. Last – возвращает последнее значение этого поля в группе. На рис.4   показан пример запроса с применением групповой операции  Sum. Данный  запрос является   многотабличным   и   позволяет   определить   сумму   выплат,   сделанных   каждым абитуриентом. Рис. 4. Запрос с применением групповой операции Sum   Здание №2 1. Откройте БД «Отдел кадров +Ваша фамилия».   2. Создайте   запрос   «Зарплата   на   отдел»,   который   выводит   Название   подразделения, вычисляемое поле Фонд заработанной платы отдела за месяц (ФЗПМ)   и вычисляемое поле Фонд  заработанной платы за год (ФЗПГ). 3. Создайте   запрос   «Квалификация»   позволяющий   вывести   в   вычисляемом   поле   ФИО фамилию, имя и отчество сотрудников (в алфавитном порядке),   название подразделения, должность и стаж работы. 4. Создайте запрос «Ср Стаж», который позволяет вычислить средний стаж сотрудников. 5. Создайте запрос «Сотрудники», вычисляющий число сотрудников. 6. Создайте запрос «Средний оклад», который вычисляет средний оклад сотрудников.  Модификация данных с помощью запросов на изменение. 7 В режиме конструктора в меню Запрос присутствуют команды для четырех типов запросов на   изменение:  Создание   таблицы,   Обновление,   Добавление,   Удаление.  Однако   прежде   чем приступить к созданию и выполнению запроса на изменение записей в базе данных, создайте запрос на выборку с условием отбора,  позволяющим найти все подлежащие обновлению записи. После того, как Access выберет нужные записи,  можно преобразовать запрос на выборку в запрос на обновление или в другой запрос на изменение. Запрос на Создание таблицы используется для сохранения извлекаемых с помощью запроса на выборку данных в новой таблице. Запрос на Обновление используется для замены в отобранных записях существующих данных. С помощью запроса на  Добавление  можно скопировать выбранные записи и вставить их в другую таблицу. Кроме того, запрос на добавление можно использовать для перенесения данных из другого источника в свою базу данных (например, списка фамилий и адресов из списка рассылки фирмы), чтобы затем отредактировать полученные данные и вставить их в существующую таблицу. Запрос   на   добавление,   подобно   запросу   на   создание   таблицы,   позволяет   собрать   вычисленные итоговые   данные   и   сохранить   их   в   определенной   таблице.   Одно   из   преимуществ   запроса   на добавление состоит в том, что вы имеете возможность полностью определить поля и задать их свойства перед вставкой данных в конечную таблицу. Недостатком этого типа запросов является большая   вероятность   ошибок,   поскольку   добавляемые   данные   могут  не   соответствовать   типам полей, определенным в конечной таблице, или нарушить уникальность первичного ключа.  Перед выполнением запроса на  Удаление  необходимо проверить его работу как запроса на выборку. Это необходимо для того, чтобы убедиться в том, что удаляются нужные записи.  Типовые ошибки и проблемы, возникающие при выполнении запросов на изменение Во время выполнения запроса на изменение Access различает четыре категории ошибок: 1. Дубликаты первичного ключа. Эта категория ошибок возникает при попытке добавления или   изменения   некоторой   записи   в   таблице,   приводя   к   появлению   дублирующего   значения первичного ключа  или  уникального  индекса.  Access не будет обновлять или  добавлять  записи, создающие   такие   значения.   Во   избежание   конфликта   перед   попыткой   добавить   такие   записи следует изменить значения первичного ключа в исходной таблице. 2. Ошибки преобразования данных. Ошибки этой категории возникают в тех случаях, когда вы вставляете данные в существующую таблицу и при этом оказывается, что тип данных полей­ получателей не совпадает с типом данных полей­источников (и данные полей­источников не могут быть   преобразованы  к  типу  данных  полей­получателей).   Например,   ошибка  возникает,  если   вы добавляете текстовые значения  к полю, содержащему  целочисленные данные, а текстовое поле содержит буквенные символы  или слишком длинную строку цифровых символов. В запросе на обновление  ошибка  преобразования может возникнуть, если вы  используете  формулу,   которая пытается произвести вычисления над полем, содержащим буквы. 3.   Заблокированные   записи.  Ошибки   этой   категории   возникают,   когда   при   выполнении запроса на удаление или на обновление вы используете  таблицу, к которой имеют доступ другие пользователи   сети.   Access   не   может   произвести   обновление   записей,   которые   в   это   время обновляются   другими   пользователями.   Вам   следует   подождать   некоторое   время   и   попытаться снова выполнить обновление или удаление в тот момент, когда данные записи больше никто не использует. 4.   Нарушение   условий   на   значение.  Если   вставляемые   или  обновляемые  записи   не удовлетворяют условию на значение для некоторого  поля  для таблицы, Access сообщает вам об ошибке и не производит вставки или обновления подобных записей. Контрольные вопросы 1. Что называется запросом? 2. Какими способами можно включить поля таблиц в запрос? 3. Как задается условие отбора информации с помощью оператора Like? 4. Каким образом можно добавить в бланк запроса строку Групповые операции. 5. Какие типы запросов на изменение Вы знаете? Для чего они используются? 6. Опишите основные проблемы и ошибки при модификации данных с помощью запросов на изменение.   8