Практические работы СУБД Microsoft Access
Содержание Практическая работа № 3 Запросы 2
Практическая работа № 3 Запросы Цель работы: изучение и закрепление на практике методов формирования и использования запросов для выборки и/или модификации данных в таблицах. 3
Методические указания Назначение и создание запросов Вопросы, которые формируются средствами СУБД к одной или нескольким таблицам, называются запросами. Запросы позволяют: Ш формировать сложные критерии для выбора записей из одной или нескольких таблиц; Ш указывать поля, которые должны быть отображены для выбранных записей; Ш редактировать группы записей, удовлетворяющих определенным критериям; Ш выполнять вычисления с использованием выбранных данных. Очевидно, что запросы перекрывают возможности фильтрации, т. е. являются более мощным инструментом обработки данных в БД. Переход к формированию запроса реализуется путем перехода на вкладку «Создание" конкретной БД и нажатия кнопок Мастер или Конструктор запросов, после чего СУБД предлагает различные варианты реализации запросов. Наиболее эффективным является режим конструктора. 4
5 Создание запросов (продолжение) Далее выбирается требуемая таблица (рис. 2). На экране появляется бланк запроса (рис. 3), представляющий набор пустых столбцов, каждый из которых содержит фиксированный набор ячеек. Ячейки первой строки бланка предназначены для отображения имен полей, включаемых в выборку, второй - имен таблиц, из которых выбираются поля (т. е. запрос может быть обращен сразу к нескольким таблицам), рис. 2 рис. 3 третьей - порядка сортировки полей в выборке (можно выбрать сортировку по возрастанию, по убыванию или в произвольном порядке), четвертой - разрешения на вывод на экран данного поля путем установки или снятия мышью соответствующего флажка , пятой - установки условий (критериев) на вывод строк соответствующих полей. Для этих же целей служит и последняя строка. Над строкой Поле находится зона выделения столбца.
6 Создание запросов (продолжение) Исходно бланк запроса пуст. Для занесения конкретного поля в бланк необходимо: - двойным щелчком левой клавиши мыши поместить его в первый свободный столбец бланка, или - перетащить его мышью (при нажатой левой клавише мыши). Полная очистка бланка при его переформировании может быть выполнена командой главного меню. Сохранение запроса для дальнейшего использования производится нажатием кнопки на панели инструментов (Сохранить). Далее СУБД запросит имя сохраняемого запроса. Целесообразно, чтобы оно имело смысловую нагрузку, что облегчит дальнейшее использование запроса. Очистка конкретного столбца проводиться путем его выделения щелчком левой клавиши мыши в зоне выделения и нажатия клавиши Delete на клавиатуре или нажатия кнопки (Удалить в буфер) на панели инструментов.
7 Создание запросов (продолжение) После того как запрос сформирован, необходимо оценить результат его выполнения в Режиме таблицы! К заданию
Параметризованный запрос 8 Самое главное в запросе - возможность использования критериев выборки, которые вводятся в строку Условие отбора. Можно выделить следующие типы запросов на основе критериев выборки: Выборка по строгому совпадению. В строку Условие отбора для определенного поля вводится одно из значений, существующих в таблице. Например, название конкретного товара или название фирмы, города. рис. 9 Данные запросы можно параметризировать, т. е. вводить условия отбора в виде параметра при каждом запуске запроса, что устраняет необходимость предварительно его модифицировать. Для параметризации необходимо в строке Условие отбора вместо самого условия ввести текст приглашения на его ввод по формату [текст приглашения]. При запуске параметризованного запроса появляется диалоговое окно (рис. 9), в котором пользователь должен К заданию ввести собственно условие отбора и нажать клавишу ОК.
Выборка по строгому несовпадению В этом случае в выборку отбираются все записи таблицы, кроме записей, содержащих значение, указанное в строке Условие отбора. Для реализации данного запроса перед значением вводится префикс Not или <>. Например, Not «рекламный" в поле отдел его работы запроса к таблице служащий приведет к выборке всех служащих, кроме служащих рекламного отдела. К заданию 9
10 Выборка по неточному совпадению Для выборки записей в условиях неполноты знаний о требуемых значениях используется оператор Like <условие>. Само <условие> образуется следующими подстановочными символами: ? - любой один символ; * - любое количество символов (0 - ); # - любая одна цифра; [список_символов] - любой символ из списка; [!список_символов] - любой символ, не входящий в список; В списке можно указывать сразу диапазон символов, Например, [Г-Л] или [г-л. Г-Л]. Примеры использования оператора Like в поле ФИО таблицы СТУДЕНТ: Like ? ? ? ? ? - выбираются все студенты, ФИО которых содержит 10 символов; Like "В? ? ? ? ? " - выбираются все студенты, ФИО которых содержит 10 символов и начинается на букву "В"; Like "В*" - выбираются все студенты, ФИО которых начинается на букву "В". Длина ФИО произвольная; Like "[ВД]*" - выбираются все студенты, ФИО которых начинается на буквы "В" или "Д". Длина ФИО произвольная; Like "[В-М]*" - выбираются все студенты, ФИО которых начинается на буквы от "В" до "М". Длина ФИО произвольная. К заданию
Выборка по диапазону 11 Для формирования данных условий выбора используются операторы сравнения >, >=, <, <= и <>. Операции сравнения могут связываться логическими операциями And (И) и Or (ИЛИ). Для этих же целей используется оператор диапазона Between <нижнее_значение> and <верхнее_значение>. Например, выбор книг стоимостью от 100 до 200 рублей может быть реализован через ввод в запросе условия в поле Стоимость в виде >=100 and <=200 или Between 100 and 200. К заданию
Запрос с вычислениями 12 Такой запрос позволяет получить дополнительную информацию в процессе выборки, например, стоимость всей партии товара при хранимой в таблице информации о количестве товара и стоимости единицы его продукции. Для этого в строку Поле пустого столбца заносят выражение для вычисления по следующему формату: <Название_формируемого_поля>: <выражение>. В <выражении> можно использовать знаки арифметических операций, круглые скобки и имена полей в [ ]. Например, стоимость партии можно вычислить по выражению Стоимость партии: [количество товара]*[стоимость единицы товара]. Если используется поле другой таблицы, то в префиксе через ! указывается имя данной таблицы. Например Стоимость партии: [Товар]![количество товара]*[стоимость единицы товара]. К заданию
Запрос с групповыми операциями 13 Рассмотренные запросы анализируют отдельные записи таблицы. Вместе с тем, СУБД Access позволяет находить интегральные показатели для групп записей в таблице. Каждая такая группа характеризуется одинаковым значением по какому-то полю, например, одинаковым названием отдела или семейным положением. Для перехода в данный режим запросов необходимо Итоги в панели инструментов нажать , что приведет к появлению в бланке запроса новой второй строки с названием Групповые операции. В ячейках данной строки указывается или режим группировки по некоторому полю (опция Группировка), или название групповой операции: Sum - сумма значений Avg - среднее значение по данному полю для всей группы; Count - число записей в данной группе; Max - максимальное значение поля в каждой группе; Min - минимальное значение поля в каждой группе; First - первое значение данного поля в каждой группе; Last - последнее значение данного поля в каждой группе и др. К заданию
Создание запросов с помощью 14 Мастера запросов Указываем таблицу из которой будем делать выборку.
Выбираем нужные в запросе поля Выбираем тип отчёта 15
16 Задаём имя запроса Переходим в конструктор для изменения макета запроса
Для реализации изменений содержимого исходной таблицы СУБД Access использует четыре следующих запроса: 17 Запрос-создание новой таблицы Предназначен для сохранения результатов запроса в виде новой таблицы. Исходно формируется обычный запрос на выборку необходимой информации из таблицы. После проверки результатов его выполнения производится возврат в режим конструктора запросов. Далее выбираем Тип запроса Создание таблицы на панели инструментов. После чего СУБД запрашивает её имя. Непосредственно запрос на создание запускается нажатием кнопки на панели инструментов. В окне Таблицы БД появляется пиктограмма созданной таблицы. К заданию !!! ПРОВЕРИТЬ!!!
Запрос-добавление выборки в другую таблицу 18 Выборку можно добавить к другой таблице, однотипной по структуре или с изменением структуры выборки. Для этого необходимо сформировать обычный запрос и оценить результаты его выполнения. Далее следует вернуться в режим конструктора запроса. Нажимается кнопка Тип запроса Добавление. После чего СУБД запрашивает имя таблицы, к которой будет добавлена выборка. Последний шаг - нажатие кнопки ОК. Выборку можно добавлять и к таблицам других БД, что определяется установкой соответствующих переключателей в окне ввода имени целевой таблицы. Если структура выборки и целевой таблицы не совпадают, то в целевую таблицу добавляются значения только тех полей выборки, имена которых совпадают с именами полей целевой таблицы. К заданию
Запрос-удаление 19 С помощью запросов можно удалить часть или все записи из таблицы. Для этого необходимо сформировать обычный запрос и оценить результаты его выполнения. Далее следует вернуться в режим конструктора запроса. Нажимается кнопка Тип запроса Удаление. После чего в бланке запроса появляется новая третья строка с именем Удаление, куда можно вводить дополнительные условия на выборку удаляемых записей. Последний шаг - нажатие кнопки ОК. К заданию
Запрос-обновление 20 С помощью запросов можно обновлять в единой операции некоторые или все значения выбранных полей. Для этого необходимо сформировать обычный запрос и оценить результаты его выполнения. Далее следует вернуться в режим конструктора запроса. Нажимается кнопка Тип запроса Обновление. После чего в бланке запроса появляется новая третья строка с именем Обновление. В ней задаются новые значения полей таблицы, в том числе и вычисляемые выражения. Далее запрос запускается на выполнение. СУБД указывает число модифицируемых записей и просит подтвердить изменения кнопкой ОК. Пользователь на этом этапе еще может отказаться от модификации значений в таблице. К заданию
21 Итог Проверьте сколько и какие запросы у вас получились!
Выполнение практической работы № 3 а Запросы Для таблицы СЛУЖАЩИЙ БД Фирма сформировать, проверить корректность и сохранить следующие запросы: 1. Отображающий в выборке поля: – – 2. 3. ФИО; дату рождения; отдел; оклад. Отображающий в выборке все поля таблицы для работников одного отдела. Сделать данный запрос параметризованным; Отображающий в выборке поля: – ФИО; – отдел; – количество детей, для семейных сотрудников, фамилии которых начинаются на буквы от "Г" до "Ш“. 4. Отображающий в выборке поля: – ФИО; – должность; – оклад, для холостых сотрудников с окладом больше 1000 руб. 5. Рассчитывающий для каждого сотрудника налог в виде выражения налог = (0. 99*зарплата - (количество_детей + 1) * 83. 49) * 0. 12 В выборке выводятся: – – ФИО; отдел; зарплата; налог. Работу запросов продемонстрировать преподавателю.
Выполнение практической работы № 3 б Запросы I. Для таблицы СЛУЖАЩИЙ БД Фирма сформировать, проверить корректность и сохранить следующие запросы: 1) Вычисляющий сумму окладов для каждого отдела; 2) Средний оклад по всей фирме; II. Выбрать из таблицы СЛУЖАЩИЙ всех женщин и сохранить выборку в виде новой таблицы ЖЕНЩИНЫ; III. Выбрать из таблицы СЛУЖАЩИЙ всех холостых мужчин и добавить выборку к таблице ЖЕНЩИНЫ; IV. Удалить из таблицы ЖЕНЩИНЫ служащих одного определенного отдела; V. Увеличить всем женщинам, имеющим детей, в таблице ЖЕНЩИНЫ оклад в 1. 5 раза. Работу запросов продемонстрировать преподавателю.
Скачать презентацию Практические работы СУБД Microsoft Access Содержание Практическая
Access Лабораторная 3.ppt