|
Методические указания к выполнению лабораторной работы СУБД ACCESS 97 “Создание запросов на выборку к однотабличным и многотабличным БД.”
1. Цель работы Освоить принципы создания запросов выборки.
2. Задачи работы Создать запросы выборки и получить сведения о данных с использованием различных критериев. 3. Содержание работы 3.1.Создать простые запросы к ранее созданным таблицам (можно использовать таблицы из предыдущей лаб.раб.) по предложенным преподавателем критериям. 3.2. Создать запросы с условием отбора. 3.3. Создать запросы с вычисляемыми полями. 4. Требования к отчету Отчет о проделанной работе должен содержать: - название работы, цель, последовательность выполнения; - ответы на контрольные вопросы методических указаний (п.8). К отчету прилагается дискета с выполненным заданием. 5. Рекомендуемая литература
5.1. Конспект лекций по курсу "Информатика". 5.2. Информатика: Учебник под редакцией Н.В.Макаровой - М.: Финансы и статистика, 1997 - С. 330-333,344...346. 5.3. Информатика: Практикум по технологии работы на компьютере под редакцией Н.В.Макаровой - М.: Финансы и статистика, 1997 - С. 330-333,344...346. 5.4.Виктор Пасько “ACCESS 97 для пользователя. Русифицированная версия.” - М.: Киев, BHV, 1997 – С.35-47,141-150.
6. ОБЩИЕ ПОЛОЖЕНИЯ 6.1. Понятие запроса При работе с таблицами можно в любой момент выбрать из базы данных необходимую информацию с помощью запросов. Запрос - это обращение к БД для поиска или изменения в базе данных информации, соответствующей заданным критериям. С помощью Access могут быть созданы следующие типы запросов: запросы на выборку, запросы на изменение, перекрестные запросы, запросы с параметром. Одним из наиболее распространенных запросов является запрос на выборку, который выполняет отбор данных из одной или нескольких таблиц по заданным пользователем критериям, не приводящий к изменениям в самой базе данных.
6.2. Создание запроса Для начала создания запроса следует открыть базу данных, и, перейдя на вкладку Запросы нажать кнопку Создать. Появится окно Новый запрос для выбора способа построения запроса ( рис 1).
Рис. 1.Диалоговое окно Новый запрос Конструктор - создает запрос на основе пустого бланка запроса. Простой запрос - создает простой запрос из определенных полей. Перекрестный запрос - создает запрос, данные в котором имеют компактный формат, подобный формату сводных таблиц в Excel. Повторяющиеся записи – создает запрос, выбирающий повторяющие записи из таблицы или простого запроса. Записи без подчиненных - создает запрос, выбирающий из таблицы записи, не связанные с записями из другой таблицы. При выборе Конструктора через диалоговое окно Добавление таблицы (рис. 2).добавляются имена таблиц в окно конструктора запроса
Рис 2. Окно Добавление таблицы
Окно Добавление таблицы состоит из трех вкладок, содержащих перечни объектов, предлагаемых программой для проектирования запроса: Таблицы, Запросы и Таблицы и запросы. При выборе вкладки Таблицы следует выделить нужную нам таблицу из предложенного списка и с помощью кнопки Добавить можно добавить несколько таблиц. Например, на рис.2 выбрана таблица Студенты. Имена таблиц должны быть представлены в окне конструктора запроса (рис 3). 6.2.1. Окно конструктора запроса Окно конструктора (рис.3) разделено на две части. В верхней части находятся окна таблиц со списками полей. Имя каждой таблицы отображается в строке заголовка такого окна.
Рис 3. Окно конструктора запроса. Пример ввода условия.Например, на рис.3 представлены таблицы Студенты и Студенты и занятия. Нижняя часть является бланком запроса, или, как его называют, QBE – областью (Query by Example – запрос по образцу). Здесь указываются параметры запроса и данные, которые нужно отобрать, а также определяется способ их отображения на экране. Для перемещения из верхней панели окна в нижнюю и обратно используется клавиша F6. 6.2.2. Включение полей в запрос В запрос не следует включать все поля выбранных таблиц. Добавить нужные поля в бланк запроса можно путем перетаскивания их имен из списка, находящегося в верхней части окна конструктора в строку бланка Поле. Еще один способ – двойной щелчок по имени поля. Например, на рис.3 в бланк запроса включены поля Фамилия, Имя и Город из таблицы Студент.Примечание: Если был установлен флажок Имена таблиц из меню Вид, то во второй строке бланка QBE выйдет на экран имя таблицы, из которой выбрано поле (см. рис.3). В строке Вывод на экран флажком помечаются те поля, которые должны быть выведены на экран. В общем случае поля, вводимые в наборе записей запроса, наследуют свойства, заданные для соответствующих полей таблицы. Можно определить другие значения свойств, выполнив команду Свойства из меню Вид: Описание (текст, содержащий описание объекта), Формат поля (представление данных на экране), Число десятичных знаков (для числовых данных, Маска ввода, Подпись (заголовок столбца). Удалить поле из бланка запроса можно клавишей [Delete] или через меню Правка командой Удалить столбцы. Чтобы удалить таблицу, следует маркировать ее в верхней части окна конструктора запроса, выполнив щелчок по имени, и нажать [Delete] или в меню Правка командой Удалить. 6.2.3.Установка критериев отбора записей При создании запроса можно задать критерии, вследствие чего по запросу будет осуществлен отбор только нужных записей. Чтобы найти записи с конкретным значением в каком либо поле, нужно ввести это значение в данное поле в строке бланка QBE Условие отбора (см. рис.3). Критерии, устанавливаемые в QBE – области, должны быть заключены в кавычки. Если ACCESS 97 идентифицирует введенные символы как критерии отбора, то заключает их в кавычки автоматически, а если нет, то сообщает о синтаксической ошибке. Например, как показано на рис.3, построен запрос, по которому из данных по баллам будут выбраны фамилии и имена студентов с оценками только 4 и 5. 6.3. Виды критериев Для создания запроса с несколькими критериями пользуются различными операторами. 6.3.1. Логическая операция или Можно задать несколько условий отбора, соединенных логическим оператором или (or), для некоторого поля одним из двух способов: 1) можно ввести все условия в одну ячейку строки Условие отбора, соединив их логическим оператором или (or). В этом случае будут выбраны данные, удовлетворяющие хотя бы одному из условий. Например, запись 4 or 5 соответствует тому, что будут выбраны фамилии с оценками 4 или 5. 2) ввести второе условие в отдельную ячейку строки или. И если используется несколько строк или, то чтобы запись была выбрана, достаточно выполнения условий хотя бы в одной из строк или, как, например, показано на рис. 4.
Рис 4. Пример записи условия с использованием оператора или (or). При такой записи условия также будут выбраны фамилии с оценками 4 или 5. 6.3.2. Логическая операция и Логическая операция и (and) используется в том случае, когда должны быть выполнены оба условия и только в этом случае запись будет выбрана. Например, записав условие >2 and <5 будут выбраны только оценки 3 и 4. Чтобы объединить несколько условий отбора оператором и (and), следует привести их в одной строке. Например, на рис 5 показано как можно задать условие для выбора фамилий студентов, проживающих в городе Уфе, которые учатся только на 5.
Рис 5. Пример записи условия с использованием оператора и (and ).
Исключить группу данных из состава анализируемых запросом записей позволяет следующий критерий < > 4 В этом случае можно не использовать кавычки. Операторы и и или применяются как отдельно, так и в комбинации. Следует помнить, что условия связанные оператором и выполняются раньше условий, объединенных оператором или. 6.3.3.Оператор Between Оператор Between позволяет задать диапазон значений, например: between 10 and 20 Оператор In позволяет задавать используемый для сравнения список значений. Например: in (“первый”,”второй”,”третий”) 6.3.4. Оператор Like Оператор Like полезен для поиска образцов в текстовых полях, причем можно использовать шаблоны: * — обозначает любое количество ( включая нулевой) символов; ? — любой одиночный символ; # — указывает что в данной позиции должна быть цифра. Например: для выбора фамилии, начинающейся с буквы П и с окончанием “ов” можно записать like П*ов 6.3.5.Операторы для даты и времени Можно ввести дату и время, при этом значения должны быть заключены между символами #. Например: #10 мая 1998# >#31.12.96# В Access используется ряд других функций, которые помогут задать условия отбора для даты и времени, например: Day(дата) – возвращает значение дня месяца в диапазоне от 1 до 31 Month(дата) – возвращает значение месяца года в диапазоне от 1 до 12 Year(дата) – возвращает значение года в диапазоне от 100 до 9999 6.4.Сортировка данных в запросе Данные можно упорядочить по возрастанию или убыванию. Для упорядочения отобранных записей целесообразно воспользоваться возможностями сортировки самого запроса в строке Сортировка окна конструктора запроса (см рис 3). 6.5. Вычисляемые поля Можно задать вычисления над любыми полями таблицы и сделать вычисляемое значение новым полем в запросе. Для этого в строке Поле бланка QBE вводится формула для вычисления, причем имена полей заключаются в квадратные скобки. Например: =[ Оклад]*0.15 В выражениях можно использовать следующие операторы: - арифметические: * умножение; + сложение; - вычитание; / деление; ^ возведение в степень; - соединение частей текста при помощи знака & , например: =[ Фамилия] & “ “&[Имя] В кавычки заключен пробел для того, чтобы запись не была слитной. 6.5.1. Использование построителя выражений При
создании выражений для вычисляемых полей
можно использовать
Рис.6. Окно построителя выражений В верхней части окна расположена пустая область ввода, в которой создается выражение. Можно самим ввести выражение, но проще использовать различные кнопки, расположенные под областью ввода. Для начала нужно щелкнуть дважды в левом списке по папке Таблицы, и выбрать саму таблицу ,а затем в колонке Код само поле и щелкнуть по кнопке Вставить. Например, как показано на рис 6, была выбрана таблица Сотрудники. Все имена объектов, из которых строится выражение для вычисления, заключены в квадратные скобки, причем перед именем поля может стоять восклицательный знак ( ! ) разделяющий его и имя таблицы. Ошибку при составлении выражения можно отменить , щелкнув по кнопке Отмена. В результате произведенных действий получится выражение, например как показано на рисунке. 6 задано выражение для поля, которое будет состоять из суммы оклада и премии сотрудника. Если щелкнуть по кнопке ОК , то полученный результат будет перенесен в бланк QBE. 6.6. Итоговые запросы Итоговые запросы значительно отличаются от обычных. В них поля делятся на 2 типа: - поля, по которым осуществляется группировка данных; - поля, для которых проводятся вычисления. Для
составления итогового запроса, находясь в
режиме конструктора, следует нажать кнопку Групповые
операции В результате чего в бланке запроса появится строка Групповая операция. Если для соответствующего поля из списка выбрать функцию Группировка (рис 7), то при выполнении запроса записи по этому полю группируются по значениям в этом поле , но итог не подводится. Группировка в итоговом запросе производится только по одному полю. Во всех остальных полях вводятся итоговые функции.
Рис 7. Строка Групповая операция в бланке QBE Access предоставляет ряд функций, обеспечивающих выполнение групповых операций. Можно задать нужную функцию, набрав на клавиатуре ее имя в строке, Групповая операция или выбрав ее из раскрывающегося списка. Основные групповые функции, которыми можно воспользоваться: SUМ - вычисляет сумму всех значений заданного поля (для числовых или денежных полей), отобранных запросом; AVG - вычисляет среднее значение в тех записях определенного поля, которые отобраны запросом ( для числовых или денежных полей); MIN - выбирает минимальное значение в записях определенного поля, отобранных запросом; MAX – выбирает максимальное значение в записях определенного поля, отобранных запросом; COUNT – вычисляет количество записей, отобранных запросом в определенном поле, в которых значения данного поля отличны от нуля; FIRST - определяет первое значение в указанном поле записей; LAST -. определяет последнее значение в указанном поле записей.
6.7. Выполнение запроса Готовый
запрос выполняется после щелчка по кнопке
Рис.8. Результат выполненного запроса 6.8. Запросы к нескольким таблицам Запросы можно создавать для отбора данных как из одной, так и из нескольких таблиц. Запросы к нескольким таблицам производятся аналогично запросам к однотабличным БД с той лишь разницей, что в окно конструктора запроса добавляются все таблицы, данные которых нужны в запросе. При этом следует учитывать наличие связей между таблицами (см. лаб. раб. по созданию многотабличной БД ). На рис. 3 представлены две таблицы Студенты и Студенты и занятия, где показана связь один-ко-многим. 7. МЕТОДИЧЕСКИЕ РЕКОМЕНДАЦИИ 7.1. Создать простой запрос - выбрать несколько произвольных полей из таблицы Студенты. 7.2. С помощью Конструктора создать запросы, удовлетворяющие условиям: - единственное значение факультета; - два различных факультета; - фамилии студентов, начинающиеся с определенной буквы (использовать шаблоны); - фамилии студентов, заканчивающиеся на “ов”; - фамилии студентов одного факультета и одного курса; - фамилии и имена студентов, проживающие в одном из городов или обучающиеся на одном из факультетов; - фамилии студентов, у которых стипендия больше 400 рублей; - фамилии студентов, занимающиеся не в 1-ой группе и стипендия которых в пределах от 200 до 500 р. Примечание: В запрос должны быть включены поля Фамилия , Имя, Отчество и те поля, где вводятся критерии. После того как был задан критерий для запроса, запрос нужно выполнить и сохранить под именем, подходящим по смыслу. 7.3. Для запросов с полем типа Дата/время добавить поле Дата рождения и выбрать записи, удовлетворяющие условиям: - дата больше 1.1.80; - дата в интервале значений и задан факультет; - фамилии и имена студентов, родившихся в 80-х годах; - вычислить возраст студентов; - фамилии и имена студентов, родившихся в первой половине месяца;
7.4. Создать итоговый запрос: - оставить в запросе поля Факультет, Стипендия, Номер зачетки, вычислить максимальное значение стипендии для каждого факультета и подсчитать количество студентов на каждом факультете (используя Count).
7.5. Запрос с вычисляемыми полями: - включить в запрос вычисляемое поле, которое является результатом сцепления текстовых полей Фамилия, Имя, Отчество. Назваить поле Ф. И. О. студента. - используя построитель выражений, подсчитать надбавку студентам, равную 15% от стипендии; 7.6. Создать запрос, в котором используются поля из двух ранее созданных и связанных таблиц, задав ему имя Запрос для 2-х таблиц – убрать несколько полей таблицы Студенты и добавить поля Предмет и Оценка из таблицы Успеваемость; – выбрать поле Фамилия, предмет и Оценка, вычислить минимальное значение по полю Оценка; – сгруппировать по номеру зачетки и вычислить среднюю оценку для каждого студента. 8. Контрольные вопросы 8.1.Что такое запрос ? 8.2.Назовите элементы окна конструктора запросов. 8.3.Что такое бланк QBE ? 8.4.Где записываются критерии условия выбора для запроса? 8.5.Как удалить таблицу из запроса? 8.6.Как удалить поле из запроса? 8.7.Перечислите основные операторы, используемые в запросе. 8.8.В каких случаях используется оператор OR? 8.9.В каких случаях используется оператор AND? 8.10.В чем различие между операторами OR и AND ? 8.11.Назначение итоговых запросов. 8.12.Назначение построителя выражений. 8.13.Как вычислить сумму значений заданного поля? 8.14. Как осуществить сортировку записей в запросе? 8.15. Какие виды вычислений можно произвести в итоговых полях?
|
||||||||||||
