Технология разработки запросов назначение виды способы создания

Практикум 6.4.ИНФОРМАЦИОННАЯ ТЕХНОЛОГИЯ РЕАЛИЗАЦИИ БАЗ ДАННЫХ

Освоить технологию создания:

• запросов на выборку;
• запросов с параметром;
• запросов с вычисляемыми полями;
• перекрестных запросов;
• запросов на удаление;
• запросов на создание таблицы;
• запросов на изменение данных.

Оглавление

6.4.5. Запросы

Создание и использование запросов

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

Для расширения возможностей работы с данными в СУБД существует специальный объект – запрос .

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

Результатом работы является таблица данных, отвечающая запросу.

Эта таблица является динамической, она формируется по данным, присутствующим в базе данных на момент выполнения запроса.

Запрос можно создать с помощью мастера. Но наиболее универсальным является создание запроса с помощью конструктора.

Все созданные запросы сохраняются в окне Запросы и их можно выполнять по мере необходимости.

СУБД Access позволяет создать разнообразные виды запросов. Их можно разделить на две большие группы: запросы на выборку и специальные запросы.

Запросы на выборку

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

В таблице 1 приведена краткая характеристика видов запросов на выборку.

Таблица 1. Запросы на выборку

Тип

Возможности

Выборка
по всем записям

Выбрать конкретные поля из таблиц, расположить их в требуемом порядке, задать сортировку по нескольким критериям

Выборка с условием

Для некоторых полей добавляется условие в виде некоторого критерия для выборки записей

Запрос с параметрами

Условие отбора указывается в запросе неявно и формируется в момент обращения к запросу.

Запрос с вычисляемыми полями

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

В таблице 2 приведены примеры условий отбора

Таблица 2. Примеры условий отбора

Вид условия

Примечание

Отбор записей

Для текстовых полей кавычки обязательны

значение поля совпадает со словом «Информатика» (Иначе говоря, полное совпадение текстовой строки)

значение поля – текстовая строка, начинающаяся на букву П

значение поля – текстовая строка, содержащая букву п

значение поля равно 500

значение поля больше либо равно 16

значение поля не равно 0

вид записи условия отбора в запросе с параметром

Технология создания запроса на выборку

1. В окне Запросы запустить режим Создание запроса с помощью конструктора.
2. На бланк запроса из окна Добавление таблицы вставить таблицы или запросы, из которых будут включены в новый запрос данные, а также все промежуточные связанные таблицы.
3. Из таблиц и запросов двойным щелчком (или приемом «захватить и перетащить») выбрать поля в том порядке, как вы их хотите увидеть в таблице.
4. Задать сортировку, если требуется. Сортировку можно задать по нескольким полям. Она будет выполняться в порядке расположения полей.
5. Задать условия отбора.
6. Сохранить запрос.
7. Запрос выполняется из режима Конструктор с помощью кнопки Запуск или запускается из главного окна базы данных двойным щелчком.

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

Задание 1. Запрос на выборку

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

Технология работы

1. В главном окне базы данных перейдите в окно Запросы.
2. Выберите режим Создание запроса в режиме конструктора. Откроется бланк запроса. Он похож на бланк расширенного фильтра.
3. В верхней части бланка запроса добавьте таблицы Студент и Группа. Для этого

• щелкните правой кнопкой и из контекстного меню выберите команду Добавить таблицу.
• в открывшемся окне на вкладке Таблицы выберите требуемые таблицы. Закройте окно.

1. Двойным щелчком выберите

• из списка полей таблицы Группа поле Номер группы;
• из списка полей таблицы Студент поля Фамилия, Имя, Отчество. Выбранные поля появятся в столбцах нижней части бланка.

1. Задайте сортировку по полям Номер группы и Фамилия.
2. Выполните запрос. Для этого щелкните на кнопке Запуск (или меню Запрос/Запуск). Просмотрите результаты отбора. Вы получили список всех студентов, сгруппированный по номерам учебных групп, и в каждой группе фамилии студентов отсортированы по алфавиту.
3. Перейдите в режим конструктора (Вид/Конструктор).
4. Для поля Номер группы задайте условие отбора – ДО-11 (или другое значение). Отмените сортировку по группе – она теперь не нужна (Рис. 1).
5. Выполните запрос снова. Просмотрите результаты отбора. Вы получили список всех студентов указанной учебной группы.
6. Сохраните запрос (меню Файл/Сохранить или кнопка на панели инструментов). Для этого щелкните на кнопке. Задайте имя запроса – Список группы.
7. Измените в условии отбора номер группы – ДО-21. Просмотрите результаты запроса.
8. Закройте запрос. Убедитесь, что его имя появилось в окне Запросы.

Рис. 1. Бланк запроса на выборку

Задание 2. Запрос с параметром

Чтобы не создавать несколько запросов для выбора той или другой группы можно создать запрос с параметром, в котором номер группы можно вводить непосредственно уже при выполнении запроса. Такой запрос называется запрос с параметром . Запросы с параметром охватывают гораздо более широкий круг условий отбора.

Преобразовать запрос Список группы в запрос с параметрами.

Технология работы

1. Откройте запрос Список группы в режиме конструктора.
2. В строке Условие отбора для поля Номер группы введите фразу [Введите номер группы] (фразу требуется вводить в КВАДРАТНЫХ СКОБКАХ) (Рис. 2).

Рис. 2. Бланк запроса с параметром

1. Закройте запрос и сохраните его со сделанными изменениями.
2. Двойным щелчком запустите запрос. При выполнении запроса с параметром появляется окно для ввода условия отбора. Введите номер группы и просмотрите результат выполнения запроса.

Задание 3. Запрос с двумя параметрами

Создать запрос с параметром Дисциплины-Оценки-Группы на основе четырех таблиц.

В этом запросе производится отбор сведений об оценках студентов конкретной группы по конкретной дисциплине. Номер группы и название дисциплины задаются параметрически.

Технология работы

1. Создайте новый запрос.
2. Добавьте в бланк запроса таблицы Группа, Студент, Дисциплина и Оценка.
3. Включите в запрос поля

• из таблицы Дисциплина – поле Название
• из таблицы Группа – поле Номер группы
• из таблицы Студент – поля Фамилия, Имя, Отчество студента
• из таблицы Оценка – поле Оценки

1. Задайте сортировку по фамилиям.
2. Задайте параметрическое условие отбора по полю Название в виде фразы [Введите название дисциплины] и параметрическое условие отбора по полю Номер группы – [Введите номер группы ] (Рис. 3).
3. Добавьте в запрос условие отбора только тех студентов, которые получили оценку 4 или 5. Для этого в строке Условие отбора для поля Оценка введите условие >3 (Рис. 3).

Рис. 3. Запрос с двумя параметрами

1. Выполните запрос. При выполнении запроса задайте конкретные значения названия дисциплины и номера группы. Просмотрите результат выполнения запроса.
2. Закройте запрос и сохраните его с именем Дисциплины-Оценки-Группы.

Задание 4. Вычисляемые поля в запросе

Создать запрос Студенты (выч-поля) по таблице Студент в котором будет вычисляться возраст студента.

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

Технология работы

1. Создайте новый запрос в режиме конструктора.
2. Включите в бланк запроса таблицу Студент .
3. Из таблицы выберите поля Фамилия, Имя, Отчество, Дата рождения.
4. Щелкните в верхней строке Поле следующего (пустого) столбца правой кнопкой и в контекстном меню выберите команду Построить. Откроется Построитель выражений.
5. Создайте поле Возраст с формулой

Возраст: Year(Now()-[Студенты]![Дата рождения])-1900

Для создания формулы проделайте следующие действия

• В верхней области окна введите название вычисляемого поля Возраст.
• После названия поставьте двоеточие (:).
• Слева в нижней части окна выберите Функции/Встроенные функции.
• В средней части выберите группу функций Дата/время.
• В правой части выберите функцию Year ().
• Удалите появившиеся вместе с функцией слова «Выражение» и « number ».
• В скобках функции Year () вставьте функцию Now ().
• После функции Now () поставьте знак минус (-).
• Слева в нижней части окна выберите Таблицы/Студент
• В средней части двойным щелчком выберите поле Дата рождения
• Остальные знаки в формуле введите вручную (Рис. 4).

Рис. 4. Формула в Построителе выражений

1. Закройте построитель выражений.
2. Выполните запрос. Проверьте правильность результатов вычисления.
3. Закройте запрос и сохраните его с именем Студенты (выч-поля).

Виды специальных запросов

• Перекрестный запрос – формирует таблицу, в которой заголовками столбцов назначаются, во-первых, как обычно, некоторые поля из таблиц базы данных, а, во-вторых, значения поля из какой-нибудь таблицы, а в ячейках таблицы под этими столбцами помещается некоторая сводная содержащую некоторые сводные данные (итоги) по двум или нескольким полям таблицы.
• Запрос на обновление – задается условие для отбора записей, которые надо обновить, и формула обновления, затем запросу присваивается специальный вид «Обновление». Каждое обращение к запросу производит очередное обновление таблицы, поэтому надо осторожно применять этот запрос.
• Запрос на удаление – задается условие для отбора записей, которые надо удалить из базы данных, затем запросу присваивается специальный вид «Удаление». Результаты запросы необратимы, поэтому надо осторожно применять этот запрос.
• Запрос на создание таблицы – в обычном запросе, как уже было сказано, таблица создается только в момент запроса и не сохраняется, а этот запрос сохраняет таблицу, но редактировать ее нельзя.
• Запрос на добавление – используется для добавления записей из одной таблицы в другую.

Перекрестные запросы

Перекрестный запрос – запрос, при котором на основе числовых значений некоторого поля создается итоговое поле (например, средняя оценка, максимальное значение и т.п.). Итоговые значения формируются в таблицу, где заголовками столбцов являются значения одного поля, а заголовками строк – значения другого поля. Таблица создается для удобного анализа итоговых значений.

Перекрестный запрос можно создать с помощью мастера или в режиме конструктора.

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

Задание 5. Перекрестный запрос с помощью мастера

Создать перекрестный запрос Средние оценки, который формирует таблицу средних оценок по учебным группам с помощью мастера.

Технология работы

1-й этап. Создание вспомогательного запроса

1. В окне Запросы выберите режим создания запроса с помощью мастера.
2. На первом шаге работы мастера из таблицы Студент выберите поле Код студента, из таблицы Группа – поле Номер группы, из таблицы Дисциплина – поле Название, из таблицы Оценка – поле Оценки.
3. На следующем шаге установите переключатель подробный (он, как правило, уже установлен).
4. Завершите создание запроса и введите имя Оценки по предметам.

2-й этап. Создание перекрестного запроса

1. В окне Запросы щелкните на кнопке Создать.
2. Выберите Перекрестный запрос и нажмите Ok.
3. На первом шаге работы мастера установите переключатель Запросы, выберите запрос Оценки по предметам.
4. На следующем шаге выберите поле Название и перенесите его в правое окно (Рис. 5).
5. На следующем шаге выберите поле Номер группы (Рис. 6).
6. На следующем шаге выберите поле Оценка и функцию Среднее.
7. На следующем шаге введите название запроса Средние оценки. Готово. Откроется таблица перекрестного запроса. Обратите внимание на то, что Access создает еще общее итоговое значение средних оценок по каждой группе.
8. Если итоговые значения средних оценок имеют большое количество десятичных знаков, откройте запрос в режиме конструктора, щелкните правой кнопкой на название итогового поля, выберите в контекстном меню пункт Свойства. На вкладке Общие установите Формат поля – Фиксированный, Число знаков ? 2. Просмотрите результаты запроса (Рис. 7).

Рис. 5. 2-й шаг мастера перекрестного запроса

Рис. 6. 3-й шаг мастера перекрестного запроса

Рис. 7. Результат перекрестного запроса

1. Закройте запрос и сохраните его.

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

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

Создать запрос, который отбирает студентов отличников.

На основе этого запроса создать таблицу Студенты-отличники.

Технология работы

1. Создайте запрос в режиме конструктора.
2. Включите в него таблицы Группа, Студент и Оценка.
3. Выберите поля Фамилия, Имя, Отчество и Оценки.
4. Выполните команду Вид/Групповые операции. В нижней части бланка запроса появится еще одна строка – Групповая операция.
5. Для поля Оценки установите в этой строке функцию Sum (Рис. 8) и введите в строке Условие отбора значение 20 (это максимальная сумма всех оценок по 4-м дисциплинам).

Рис. 8 Бланк запроса с групповой операцией

1. В меню Запрос выберите тип – Создание таблицы. Введите имя таблицы – Студенты-отличники .
2. Выполните запрос (кнопка Запуск).
3. Закройте запрос и сохраните его с именем Отличники
4. Перейдите в окно Таблицы и убедитесь, что там появилась новая таблица.

Задание 7. Запрос на изменение (обновление)

Создайте запрос Изменение оплаты, в котором для групп, которые еще не закончили обучение автоматически будет увеличена оплата за обучение на 10%.

Технология работы

1. Создайте новый запрос.
2. Включите в запрос таблицу Группа .
3. Выберите поля Номер группы, Оплата за семестр, Обучение закончено.
4. Выполните запрос и просмотрите результаты. Будут отобраны все группы.
5. Перейдите в режим конструктора (Вид/Конструктор).
6. В строке Условие отбора для поля Обучение закончено введите значение Ложь.
7. Выполните запрос. Будут отобраны группы, которые не закончили обучение.
8. Перейдите в режим конструктора (Вид/Конструктор).
9. В меню Запрос выберите пункт Обновление. На бланке запроса появится строка Обновление.
10. В строке Обновление для поля Оплата за семестр введите выражение – условие увеличения оплаты на 10%, которое имеет вид [Оплата за семестр]*1,1. Это выражение можно ввести вручную или использовать построитель выражений.
11. Выполните запрос ТОЛЬКО 1 раз. Во время выполнения подтвердите согласие на обновление данных.
12. Закройте запрос и сохраните его с именем Изменение оплаты.
13. Откройте таблицу Группа.
14. Просмотрите результаты работы запроса – оплата увеличилась, но только для тех групп, которые не закончили обучение.

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

Создайте запрос на удаление из базы данных студента Перлова.

Технология работы

1. Создайте запрос в режиме конструктора.
2. Добавьте в запрос таблицу Студент.
3. Выберите поля Фамилия, Имя, Отчество. В строке условие отбора для поля Фамилия введите значение – Перлов.
4. Выполните запрос и просмотрите результаты отбора. Должен быть отобран указанный студент.
5. Перейдите в режим конструктора.
6. В меню Запрос выберите тип – Удаление.
7. Сохраните запрос с именем Удаление студента.
8. Выполните запрос (ВНИМАНИЕ! Результаты запроса необратимы).
9. Убедитесь, что из базы данных удален студент и все полученные им оценки.

Задание 9. Формы для запросов

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

Источник