Организация запросов

Практическая работа №10

Тема: Организация запросов

Цель: формирование умений создания различных видов запросов.

Время выполнения: 2 часа.

Теоретический материал

Запрос - это обращение к БД для поиска  или изменения в базе данных  информации, соответствующей заданным критериям.

С помощью Access могут быть созданы следующие типы запросов: запросы на выборку, запросы на изменение, перекрестные запросы, запросы с параметром, запросы – действия.

Для начала создания запроса следует открыть базу данных, и, перейдя на вкладку Запросы нажать кнопку Создать. Появится окно Новый запрос для выбора способа построения запроса.

В запрос не следует включать все поля выбранных таблиц. Добавить нужные поля в бланк запроса можно путем перетаскивания их имен из списка, находящегося в верхней части окна конструктора в строку бланка Поле. Еще один способ – двойной щелчок по имени поля.

Виды критериев

Для создания запроса  с несколькими критериями пользуются различными операторами(или, и, BETWEEN между), LIKE( с параметрами *,?,#)

Оператор Like полезен для поиска образцов в текстовых полях, причем можно использовать шаблоны:

* — обозначает любое количество (включая нулевой) символов;

?  — любой одиночный символ;

# — указывает, что в данной позиции должна быть цифра.

Сортировка данных в запросе

Данные можно упорядочить  по возрастанию или убыванию. Для упорядочения отобранных записей целесообразно воспользоваться возможностями сортировки самого запроса в строке Сортировка окна конструктора запроса.

Вычисляемые поля

Можно задать вычисления над любыми полями таблицы и сделать вычисляемое значение новым полем в запросе. Для этого в строке Поле  бланка QBE вводится формула для вычисления, причем имена полей заключаются в  квадратные скобки.

Например,  =[ Оклад]*0.15

В выражениях можно использовать следующие операторы:

-             арифметические: * умножение; + сложение; - вычитание; / деление; ^ возведение в степень;

-             соединение частей текста при помощи знака &, например, =[Фамилия] & “ “&[Имя]. В кавычки заключен пробел для того, чтобы запись не была слитной.

Использование построителя выражений

При создании выражений для вычисляемых полей можно использовать  Построитель выражений . Для этого нужно щелкнуть по пустому полю в бланке QBE, а затем по кнопке панели инструментов Построить,  при этом откроется окно Построитель выражений. В верхней части окна расположена пустая область ввода, в которой создается выражение. Можно самим ввести выражение, но проще использовать различные кнопки, расположенные под областью ввода.

Для начала  нужно щелкнуть дважды в левом списке по папке Таблицы, и выбрать саму таблицу ,а затем  в колонке Код  само поле и  щелкнуть по кнопке  Вставить.

Все имена объектов, из которых строится выражение для вычисления, заключены в квадратные скобки, причем перед именем поля может стоять  восклицательный знак  (!), разделяющий его и  имя таблицы. Ошибку при составлении выражения можно отменить, щелкнув, по кнопке Отмена.  В результате произведенных действий получится выражение.

Если щелкнуть по кнопке ОК, то полученный результат будет перенесен в бланк QBE.

Итоговые запросы

Итоговые запросы значительно отличаются от обычных. В них поля делятся на 2 типа: 

-            поля, по которым осуществляется группировка данных;

-            поля, для которых проводятся вычисления.

Для составления итогового запроса, находясь в режиме конструктора, следует нажать кнопку Групповые операции  на панели инструментов  или воспользоваться командой Групповые операции из меню Вид.

В результате чего в бланке запроса появится строка Групповая операция. Если  для соответствующего поля из списка выбрать функцию Группировка, то при выполнении запроса записи по этому полю группируются по значениям в этом поле, но итог не подводится. Группировка в итоговом запросе производится только по одному полю. Во всех остальных полях вводятся итоговые функции. Access предоставляет ряд функций, обеспечивающих выполнение групповых операций. Можно задать нужную функцию, набрав на клавиатуре ее имя, в строке Групповая операция или выбрав ее из раскрывающегося списка.

Понятие запросов-действий

Для быстрого изменения, вставки, создания или удаления наборов данных из базы данных используются управляющие запросы или запросы-действия.

Создание запросов-действий аналогично созданию запросов-выборок. Отличие лишь в том, что в режиме конструктора запросов необходимо указать тип действий над выбранными записями. Существует четыре типа запросов-действий: Создание таблицы, обновление, удаление, добавление.

Создание запросов на обновление, добавление и удаление невозможно без разрушения целостности данных, поэтому упражняться лучше на созданной копии таблице.

Запросы на создание таблицы

Для сохранения результатов выборки в реально существующей таблице используются запросы на создание таблицы. Это ускоряет доступ к данным, полученным по запросу. Для этого в режиме Конструктора запросов нужно выбрать команду Запрос/Создание таблицы. Access выведет на экран диалоговое окно «Создание таблицы», где нужно указать имя таблицы, в которой будут сохранены результаты выборки.

Запросы на обновление записей

Этот тип запросов-действий предназначен для изменения значений некоторых полей для всех выбранных записей. После входа в режим Конструктора запроса  нужно выбрать команду Запрос/Обновление. После этого в бланке запроса появится строка Обновление. Эта строка используется для того, чтобы задать новые значения для выбранных записей.

Запрос на удаление записей

Этот вид запросов служит для удаления группы записей базы данных, удовлетворяющих определенным условиям. Обычный запрос можно преобразовать в запрос на удаление с помощью команды Запрос/Удаление в режиме Конструктора запросов. При этом в бланке запроса появится строка Удаление, где можно ввести условие для удаления записей.

Запрос на добавление записей

Если необходимо добавить записи к таблице в другой базе данных, сначала следует присоединить таблицу-источник к базе, содержащей целевую таблицу, с помощью команды Файл/Внешние данные/Связь с таблицами. Для отбора добавляемых записей нужно составить запрос на выборку. Затем надлежит выполнить составленный запрос и оценить результат, переключившись в режим таблицы с помощью команды Вид/Режим таблицы. После этого необходимо вернуться в режим Конструктора и активизировать команду Запрос/Добавление.

Задания:

Упражнение №1. Поиск повторяющихся записей

Выберите объект базы – Запросы. Нажмите кнопку Создать, в открывшемся окне Новый запрос выберите вид запроса – «повторяющиеся записи» (рис. 17)

Рисунок 17 - Создание запроса поиска повторяющихся записей.

В качестве источника данных укажите таблицу «Сотрудники фирмы». В следующих диалоговых окнах выберите поле, по которому будет происходить поиск повторяющихся записей – Фамилия, в качестве дополнительных полей выберите Имя, Отчество. В результате работы будут отобраны записи повторяющихся фамилий, а к ним добавлены сведения об имени и отчестве сотрудников филиала. Сохраните запрос под именем «Повторяющиеся записи».

Упражнение №2. Запросы на выборку по условию

1. Выберите из таблицы «Сотрудники фирмы» фамилии и имена всех сотрудников, у которых фамилия начинается на букву «О».

Для этого выберите объект базы – Запросы. В режиме Конструктора создайте запрос на выборку (Создать/Конструктор). Добавьте таблицу «Сотрудники фирмы». Выберите выводимые поля Фамилия и Имя. В строке Условие отбора поля Фамилия бланка запроса наберите условие – О* (символ * свидетельствует о наличии произвольных символов за буквой О) (рис. 18). Поверьте, чтобы в в строке «Вывод на экран», отвечающий за вывод записей в динамическом наборе на экран компьютера, стояли галочки.

Рисунок 18 - Отбор сотрудников, фамилия которых начинается с «О»

После запуска на исполнение командой Запрос/запуск или кнопкой Запуск на панели инструментов ( «!»  -  восклицательный знак) произойдет отбор по условию. Сохраните запрос под именем «Фамилия О».

2. Выберите всех сотрудников с должностью «Бухгалтер» или «Главный бухгалтер».

Для этого создайте запрос (Создать/Конструктор). Добавьте таблицу «Сотрудники фирмы». Выберите выводимые поля Фамилия, Имя, Отчество, Должность. В строке условие отбора поля Должность бланка запроса наберите условие – «Бухгалтер» или «Главный бухгалтер». Для запуска запроса выберите команду Запрос/Запуск. Сохраните запрос под именем  «Запрос - Бухгалтер» (рис. 19).

Рисунок 19 - Отбор сотрудников по должности

3. Создайте запрос на выборку всех сотрудников, у которых ставка больше или равна 2000 р., но меньше 3000 р. (рис.20). При наборе условия используйте логический оператор AND. Условие данного запроса имеет вид «>= 2000 AND <3000». Сохраните запрос под именем «Запрос-зарплата».

Рисунок 20 - Запрос с логическим оператором

AND на выборку по условию

4. Выведите в запросе всех сотрудников с сортировкой по фамилиям с должностью «Бухгалтер» или «Главный бухгалтер», у которого зарплата превышает 3000 р. (рис.20) Сохраните запрос под именем «Запрос-Бухгалтер 3000».

Упражнение №3. Использование Построителя выражений.

1. Выбрать сотрудников в алфавитном порядке, у которых ставка меньше 1150 р.

Создайте запрос на выборку по таблицам  «Сотрудники фирмы» и  «Зарплата сотрудников», выберите поля Фамилия, Имя и Ставка. Для задания условия выборки установите курсор в строку Условие отбора поля Ставка и откройте окно Построитель выражений (нажмите на панели инструментов кнопку Построить – «волшебная палочка»). В окне Построитель выражений выберите таблицу «Зарплата сотрудников» и, используя поле Ставка, наберите соответствующее условие, пользуясь инструментами Построителя выражений (рис. 21)

Рисунок 21 - Ввод условия отбора с помощью Построителя выражений

Задайте сортировку по фамилиям. Созданный запрос имеет вид, как на рис. 22. Сохраните запрос под именем «Запрос 1150».

Рисунок 22 - Запрос с условием отбора, созданного Построителем выражений

Упражнение №4. Объединение текстовых полей.

Создать запрос на выборку, в котором представлено поле, содержащее объединение текстовых значений полей Фамилия, Имя и Отчество, разделенных пробелами.

Выражение для нового поля, объединяющего текстовые зна­чения других полей, в строке «Поле» бланка запроса должно иметь следующий вид:

[Фамилия]&" "&[Имя]&" "&[Отчество] (между кавычками на клавиатуре нажимается клавиша [Пробел]).

Для задания выражения удобно пользоваться Построителем выражений (рис.23).

Рисунок 23 - Использование Построителя выражений при объединении

Сохраните запрос под именем «Запрос-Объединение Полей».

Примечание. При наборе выражения между кавычками нажмите пробел, чтобы в выражении «Фамилия», «Имя» и «Отчество» не сливались друг с другом, а разделялись пробелами.

Упражнение №5. Расчет суммарного и среднего арифметического значений поля.

С помощью запроса подсчитайте суммарное значение по полю Ставка. Для этого создайте запрос в режиме Конструктор и в бланке запроса выберите поле Ставка. Нажмите кнопку Групповые операции на панели инструментов. В появившейся строке Групповые операции бланка запроса из раскрывающегося списка выберите функцию «Sum» (рис. 24).

Рисунок 24 - Расчет суммарного значения по полю Ставка

Запрос сохраните под именем «Запрос-Сумма».

Упражнение №6. Запрос с вычисляемыми полями

Произвести расчеты значений Премии и Зар­платы в таблице «Сотрудники фирмы». Премия составляет 27 % от Ставки, а Зарплата рассчитывается как сумма полей Премия и Ставка.

Для заполнения полей Премия и Зарплата выберите объект — Запросы, вызовите бланк запроса командой Создать/Конструктор. В меню Запрос выберите команду Обновление (рис. 25). Обратите внимание на изменения в бланке запроса (Сортировка изменилась на Обновление).

Рисунок 25 - Выбор запроса на обновление

Из списка полей в бланк запроса перетащите поля, которые нужно обновить — Премия и Зарплата; в строке Обновление введите расчетные формулы сначала для заполнения поля Премия, а затем — поля Зарплата (Премия составляет 27 % от Ставки, а Зарплата рассчитывается как сумма полей Премия и Ставка).

Для расчета Премии в строке «Обновление» наберите — [Премия] * 0,27;

Для расчета Зарплаты наберите — [Премия] + [Ставка] (рис. 26).

Рисунок 26 - Бланк запроса для расчета полей Премия и Зарплата

Сохраните запрос под именем «Премия и Зарплата».

Проведите обновление по запросу, для чего дважды запустите на исполнение запрос на обновление «Премия и Зарплата». При этом подтвердите выполнение запроса кнопкой Да в открывающемся диалоговом окне (рис. 27).

Рисунок 27 - Окно подтверждения выполнения запроса на обновление

Откройте таблицу «Сотрудники фирмы» и проверьте правильность расчетов. Если все сделано правильно, то поля Премия и Зарплата будут заполнены рассчитанными результатами.

Упражнение №7. Рассчитайте среднее арифметическое по полю Зарплата (Групповые операции — функция «Avg»). Запросу дайте имя «Запрос-Среднее».

Упражнение №8. Выбрать сотрудников по специальности «Менеджер», поступивших на работу до 10 октября 2000 г. Фамилии расположить в алфавитном порядке.

Упражнение №9. Выбрать сотрудников, поступивших на работу после 25 ноября 2001 г., у которых ставка превышает 1500 р. Ставки отсортировать в порядке убывания.

Упражнение №10. Подсчитать суммарные значения по полям Премия и Зарплата (использовать групповую операцию «Sum»).

Упражнение №11. Найти максимальное значение по полю Зарплата, и минимальное значение по полю Премия (использовать групповую операцию «Мах» и «Min»).

Упражнение №12. В  таблице «Зарплата сотрудников» добавить новые поля Доплата и Итого и произвести расчеты (созданием запроса на обновление) по формулам:

Доплата = 42 % от зарплаты (в строке «Обновление» поля Доплата наберите — [Зарплата] * 0,42);

Итого = Зарплата + Доплата (в строке «Обновление» поля Итого наберите — [Зарплата] + [Доплата]).

Контрольные вопросы:

1.        Что такое запрос?

2.        Назовите элементы окна конструктора запросов.

3.        Что такое бланк QBE? 

4.        Перечислите основные операторы, используемые в запросе.

5.        Опишите назначение итоговых запросов.

6.        Опишите назначение построителя выражений.

7.        Как осуществить сортировку записей в запросе?

8.        Как создать запрос на обновление части данных в таблице?

9.        Как удалить часть данных в таблице?

10.   Какие проблемы могут возникнуть при использовании запросов удаления?

11.   Как создать таблицу для постоянного хранения результатов определенного запроса?

Как посмотреть результат запроса?

Скачано с www.znanio.ru