Тема 6. Запросы к базе данных

Цель работы: ознакомиться со средствами поиска и выборки данных в Access, изучить основные принципы конструирования запросов, получить навыки создания запросов по образцу.

Теоретико-методологическая часть

Понятие запроса

Одним из основных назначений реляционных баз данных является быстрый поиск хранящейся в ней информации. Наиболее мощное средство поиска нужной информации в БД — запросы. Они позволяют выбрать данные из одной или нескольких взаимосвязанных таблиц БД, произвести над ними вычисления и получить результаты в виде таблиц.

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

Типы запросов

Запросы можно разделить на несколько типов: по способу формирования и по результатам действия.

По способу формирования различают следующие запросы:

Ø Запросы по образцу или QBE - запросы, для определения которых пользователь должен указать параметры запроса в окне конструктора, задавая образцы для поиска информации.

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

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

По результатам действия различают:

Ø Запросы на выборку ¾ стандартные запросы, которые позволяют отобрать данные из взаимосвязанных таблиц БД и других запросов. Результатом таких запросов является выборка. Выборкой называют динамическую таблицу с записями данных, которые удовлетворяют заданным условиям запроса. Выборка не сохраняется, она существует только до закрытия запроса и формируется каждый раз заново на основе реальных таблиц базы данных.

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

Ø Модифицирующие запросы ¾ это запросы на выполнение действий, в результате выполнения которых изменяется содержимое таблиц базы данных.

Существует четыре вида модифицирующих запросов:

¯ Запросы удаления позволяют удалить записи, удовлетворяющие определенным условиям, из таблицы БД.

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

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

¯ Запросы создания таблиц позволяют создать новые таблицы в базе данных.

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

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

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

Формирование запроса по образцу

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

· в окне базы данных выбрать вкладку Запрос;

· нажать кнопку Создать;

· в появившемся диалоговом окне Новый запрос выбрать из списка подходящее средство создания запроса и нажать кнопку ОК.

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

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

Для вызова конструктора запросов следует:

· в диалоговом окне Новый запрос выбрать Конструктор и нажать кнопку ОК;

· в появившемся диалоговом окне Добавление таблицы указать таблицы (или ранее созданные запросы), которые будут использоваться в новом запросе, и нажать кнопку Добавить;

· нажать кнопку Закрыть.

Окно конструктора запросов

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

При заполнении бланка запроса необходимо:

· в строку Поле: поместить имена полей, используемых в запросе;

· в строке Вывод на экран: отметить поля, которые должны быть включены в таблицу с результатом выполнения запроса;

· в строке Условие отбора: задать условия отбора записей;

· в строке Сортировка: выбрать порядок сортировки записей в результирующей таблице.

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

Условия отбора записей

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

Ø для поля Группа:

Сосиски

Ø для поля Цена:

> 10000

Параметры запроса

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

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

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

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

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

Имя вычисляемого поля указывается перед выражением и отделяется от него двоеточием. Например, Стоимость: [Цена] * [Количество]

Обратите внимание, что имена полей Цена и Количество заключены в квадратные скобки. Имя вычисляемого поля Стоимость становится заголовком столбца в таблице с результатами выполнения запроса.

Если необходимо указать поле в конкретной таблице, то перед именем поля ставится имя таблицы, также заключенное в квадратные скобки и отделенное от имени поля восклицательным знаком. Например, [Товары]! [Цена].

Мастера создания запросов

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

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

Ø простой запрос на выборку;

Ø перекрестный запрос;

Ø запрос для поиска повторяющихся записей;

Ø запрос для поиска записей, не имеющих подчиненных.

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

Запуск запроса

Для выполнения запроса используется команда Запрос—Запуск или кнопка на панели инструментов. Результат выполнения запроса отображается в режиме таблицы.

Сохранение запроса

Созданный запрос можно использовать и в дальнейшем. Для этого необходимо присвоить ему имя и сохранить. Сохранение запроса осуществляется командой Файл—Сохранить как/экспорт. Запросам рекомендуется присваивать информативные имена, несущие смысловую нагрузку.

Итоговые вычисления

Для выполнения итоговых вычислений следует дать команду Вид—Групповые операции или нажать кнопку на панели инструментов. При этом в бланке запроса появится новая строка с наименованием Групповая операция: . В этой строке указывается тип итоговой операции (табл. 3.17).

 

Таблица 3.17

Типы итоговых операций

Значение	Операция
Sum	Сложение
Avg	Среднее значение
Min	Минимальное значение
Max	Максимальное значение
Count	Количество записей, содержащих значения
StDev	Стандартное отклонение
Var	Дисперсия
First	Значение в первой записи
Last	Значение в последней записи

Группировка позволяет выполнить вычисления в группах записей. Для проведения группировки следует установить значение Группировка в строке Групповая операция: . Можно осуществлять группировку сразу по нескольким полям. Таким образом Вы можете создавать подгруппы внутри групп.

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

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

1. Разъясните понятие " запрос к БД".

2. Какие существуют типы запросов?

3. Что называется запросом по образцу?

4. Что такое SQL-запрос?

5. Какие запросы относятся к модифицирующим?

6. Как создается запрос по образцу?

7. Как задать условия отбора записей?

8. Что такое вычисляемое поле?

9. Какие мастера создания запросов Вам известны?

10. Как запустить запрос?

Практическая часть

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

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

Модуль 1 (рис. 3.15). Выборка записей из таблицы Накладная по заданному периоду и соединение[20] с записями таблицы Спецификация к накладной. Суммирование поставок каждого продукта по каждому цеху за каждый день.

Рис. 3.15. Схема Модуля 1

Модуль 2 (рис. 3.16). Выборка записей из таблицы План по заданному периоду и соединение с записями таблицы Спецификация к плану.

Модуль 3 (рис. 3.17). Соединение запросов Модуль 1, Модуль 2 и таблицы Готовая продукция. Расчет стоимости продукции, отклонений фактических показателей от плановых и процента выполнения плана. Суммирование поставок каждого продукта по каждому цеху за указанный период.

 

Рис. 3.16. Схема Модуля 2

	Рис. 3.17. Схема Модуля 3

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

· Открыть окно конструктора запросов.

· В схему запроса добавить таблицы Накладная и Спецификация к накладной.

· Поместить в бланк запроса поля: Цех, Код, Дата, Количество.

· В строке Условие отбора: для поля Дата задать параметры запроса для определения начальной и конечной дат заданного периода:

Between [Начальная дата] And [Конечная дата]

· Выполнить группировку данных по коду продукции по каждому цеху за каждый день. Для этого нажать кнопку на панели инструментов. В строке Групповая операция: для столбцов Цех, Код, Дата оставить значение Группировка, а для столбцаКоличество установить значение — Sum. Итоговому полю Количество присвоить новое имя –Факт.

· Выполнить запрос. В появившемся диалоговом окне задать начальную и конечную даты периода (например, в обоих случаях 10.02.02).

· Просмотреть результат запроса. Сохранить запрос под именем Модуль 1.

2. Для реализации Модуля 2 создать новый запрос в окне конструктора запросов. В схему запроса добавить таблицы План и Спецификация к плану. Поместить в бланк запроса поля: Цех, Код, Дата, Количество. Задать параметры запроса для определения заданного периода (аналогично Модулю 1 ). Полю Количество присвоить новое имя – План. Выполнить запрос. Сохранить запрос под именем Модуль 2.

3. Для реализации Модуля 3 создать третий запрос. Включить в схему запроса Модуль 1, Модуль 2 и таблицу Готовая продукция. Связать Модуль 1 и Модуль 2 по полям: Цех, Код и Дата. Перенести в бланк запроса следующие поля: Цех, Код, Группа, Наименование, Единица, Факт, План.

Добавить в бланк запроса вычисляемые поля:

Стоимость: [Факт] * [Цена]

Отклонение: [Факт] - [План]

Процент: [Факт] / [План] * 100

Выполнить группировку данных по коду продукции по каждому цеху. Для этого в строке Групповая операция: для столбцов Цех, Код, Группа, Наименование, Единица оставить значение Группировка, для столбцовФакт, План, Стоимость, Отклонение установить значение — Sum, а для столбца Процент — Avg.

Выполнить запрос.

Сохранить запрос под именем Модуль 3.

Дополнительные проверочные задания

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

2. Разработать запрос, позволяющий отобрать продукты с кодами, начинающимися с 02. В выборку включить следующие поля: Код, Наименование и Цех.

3. Создать запрос, в результате которого будут отобраны продукты с ценами в интервале от 5000 до 7000 руб. В выборку включить следующие поля: Код, Наименование, Цена и Единица.

4. Создать запрос, в результате которого будет получен список продуктов с наименованиями, начинающимися с букв: с М по Т. Запрос должен включать все поля таблицы Готовая продукция.

5. Разработать запрос на выборку всех колбас с ценами, превышающими 6000 руб. за 1 ц. В выборку включить следующие поля: Код, Группа, Наименование, Цена и Единица.

6. На основе таблицы Готовая продукция сформировать запрос на выборку:

Код продукции	Наименование продукции	Ед. изм.	Цена за ед., руб.	Цена за ед. с налогом, руб.

Поле Цена с налогом является вычисляемым полем. Величина налога должна задаваться по приглашению в специальном диалоговом окне.

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

Группа продукции	Наименование продукции	Начальник цеха	Количество, ед.

Дату определить в специальном диалоговом окне. В качестве параметра задать фразу: [Ввести дату]. Обеспечить группировку данных по наименованиям продукции.

8. Создать итоговый запрос для вычисления средней цены по каждой группе продукции. В выборку включить поля: Группа, Цена и Единица.

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

10. Создать итоговый запрос для определения суммарной стоимости колбас, поступивших на склад, по группам. В выборку включить поля: Группа и Стоимость. Поле Стоимость является вычисляемым полем.

11. Создать итоговый запрос для определения суммарной стоимости продукции, произведенной цехом 03. В выборку включить поля: Цех и Стоимость. Поле Стоимость является вычисляемым полем.

12.* Разработать модифицирующий запрос на создание архивной таблицы Архив, куда поместить все записи о поступлении продукции до 11.02.02. Для создания модифицирующего запроса воспользоваться кнопкой Тип запроса, находящейся на панели инструментов (команда — Создание таблицы ). Выполнить запрос. Открыть таблицу Архив и удостовериться в правильности создания таблицы.

13.* Разработать модифицирующий запрос на добавление в архивную таблицу Архив записей о поступлении продукции 11.02.02. Воспользоваться кнопкой Тип запроса (команда — Добавление ). Выполнить запрос. Открыть таблицу Архив и удостовериться в добавлении записей.

14.* Разработать модифицирующий запрос на обновление данных в таблице Готовая продукция . Увеличить цены всех продуктов на 10%. Воспользоваться кнопкой Тип запроса (команда — Обновление ). В строку Обновление ввести: [Цена]*1, 1. Выполнить запрос. Открыть таблицу Готовая продукции и удостовериться в обновлении данных.

15.^* Разработать модифицирующий запрос на уменьшение в таблице Готовая продукции цен всех видов колбас на 5 %. Выполнить запрос. Открыть таблицу Готовая продукции и удостовериться в изменении цен.

⇐ Предыдущая234567891011Следующая ⇒

Поделиться:

Популярное:

1. A.19. Противопожарная система
2. A.32.4.5.3. Система УСАВП: тест управления рекуперативным торможением
3. I ЭТАП: ОБСЛЕДОВАНИЯ (СБОР ДАННЫХ)
4. II. Поселение в Испании. Взаимоотношения вестготов и римлян. Королевская власть. Система управления. Церковная политика.
5. II. ТЕМАТИЧЕСКИЙ РАСЧЕТ ЧАСОВ
6. III. Вид работы: «Использование информационной базы данных»
7. IV. Анализ и обработка данных.
8. V. Письменно переведите текст и выпишите слова юридической тематики.
9. VII. ЛЕКСИКА ДРУГИХ АКТУАЛЬНЫХ ТЕМАТИЧЕСКИХ ГРУПП
10. АВАРИИ НА КОММУНАЛЬНЫХ СИСТЕМАХ ЖИЗНЕОБЕСПЕЧЕНИЯ
11. Автоматизированная система мониторинга вычислительной среды и обнаружения сетевых атак.
12. АВТОМАТИЧЕСКАЯ СИСТЕМА ОПОВЕЩЕНИЯ И ТУШЕНИЯ ПОЖАРА АСОТП ИГЛА-М.5К-Т И СКТБ