Лабораторная работа №4

ФОРМИРОВАНИЕ ЗАПРОСОВ К БАЗАМ

ДАННЫХ В СУБД MS ACCESS 2010

Цель работы: изучение принципов построения реляционных баз данных, приобретение практических навыков создания и редактирования запросов (включение в запрос таблиц, включение в запрос полей, сортировка данных, вывод полей на экран, задание условий отбора, создание вычисляемых полей, выбор групп, выбор записей, формирующих группы и др.) в системе управления базами данных MS ACCESS 2010.

ТЕОРЕТИЧЕСКИЕ СВЕДЕНИЯ

Общие сведения о запросах

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

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

1. По способу описания:

– QBE-запросы (Query By Example – выборка по образцу) – вид запроса устанавливается в специальном окне конструктора запросов;

– SQL-запросы (Structured Query Language – структурированный язык запросов) – запрос описывается с помощью команд языка SQL.

2. По назначению:

– запросы на выборку;

– запросы на изменение.

3. По содержанию:

– обычные (подробные), содержащие информацию из отдельных записей, извлеченных из одной или нескольких таблиц;

– запросы с групповыми операциями (итоговые);

– перекрестные.

4. По виду описания условий обработки данных:

– фиксированные запросы, в которых условия обработки данных (т. е. их выборки или изменения) заданы полностью;

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

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

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

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

Запрос на обновление записей. Вносит общие изменения в группу записей одной или нескольких таблиц. Например, на 10 % поднимаются цены на все молочные продукты или на 5 % увеличивается зарплата сотрудников определенной категории. Запрос на обновление записей позволяет изменять данные в существующих таблицах.

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

1. Добавление полей на основе условий отбора. Например, необходимо добавить имена и адреса клиентов с очень крупными заказами.

2. Добавление записей, если некоторые поля из одной таблицы не существуют в другой. Например, в базе данных таблица «Клиенты» содержит 11 полей. Пусть требуется добавить записи из другой таблицы с полями, соответствующими 9 из 11 полей таблицы «Клиенты». Запрос на добавление добавит данные в совпадающие поля и пропустит остальные.

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

1. Создание таблицы для экспорта в другую базу данных Microsoft Access. Например, требуется создать таблицу, содержащую несколько полей из таблицы «Сотрудники», а затем экспортировать эту таблицу в базу данных, используемую отделом кадров.

2. Создание страниц доступа к данным, отображающих данные, соответствующие указанному моменту времени. Например, 15 мая 2015 г. требуется создать страницу доступа к данным, отображающую итоговые значения продаж за первый квартал на основании данных, содержавшихся в базовых таблицах на 9:00 1 апреля 2015 г. Страница доступа к данным, основанная на запросе или инструкции SQL, извлекает из таблиц последние данные (на 15 мая 2015 г.), а не данные на конкретную дату и время. Чтобы получить данные именно в том виде, который они имели на 9:00 1 апреля 2015 г., создайте запрос на создание таблицы, отбирающий данные на указанный момент времени и сохраняющий их в новой таблице. Затем используйте в качестве основы для страницы доступа к данным эту таблицу, а не запрос.

3. Создание резервной копии таблицы.

4. Создание архивной таблицы, содержащей старые записи. Например, можно создать таблицу, сохраняющую все старые заказы, прежде чем удалить их из текущей таблицы «Заказы».

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

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

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

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

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

Методика создания запросов в СУБД MS ACCESS

В MS ACCESS имеются два способа создания запросов:

– с помощью Мастера;

– с помощью Конструктора.

Мастер последовательно задает пользователю вопросы. На основе ответов строится законченный объект базы данных или выполняется определенная операция.

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

Обычно удобно создавать запрос с помощью Мастера, а затем вносить в него необходимые изменения с помощью Конструктора.

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

Создание нового запроса на выборку в режиме конструктора

Открыть вкладку Создание и выбрать пункт Конструктор запросов (рис. 4.1).

Рис. 4.1. Вызов конструктора запросов

В диалоговом окне Добавление таблицы выделить все таблицы, необходимые для создания запроса / кнопка Добавить / кнопка Закрыть (рис. 4.2).

В строку Поле бланка запроса перенести имена нужных полей из списка полей в верхней части окна конструктора в столбцы бланка запроса (рис. 4.3).

В строке Сортировка выбрать тип сортировки для нужного поля (по возрастанию или по убыванию).

В строке Вывод на экран удалить флажки для тех полей, которые не должны отображаться на экране.

В строку Условие отбора ввести выражение для выбора записей по нужным полям.

Рис. 4.2. Окно Добавление таблицы

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

Сохранить запрос: Файл / Сохранить или кнопка , или щелкнуть правой кнопкой мыши на заголовке запроса и в появившемся меню выбрать Сохранить / в поле Имя запроса ввести требуемое имя запроса / ОК.

Для запуска запроса на выполнение необходимо высветить имя запроса в поле Все объекты Access и нажать кнопку Выполнить на вкладке Конструктор или дважды щелкнуть левой кнопкой мыши на имени запроса. Результаты выполнения запроса выводятся в созданную динамическую таблицу (рис. 4.4). После закрытия запроса эта таблица удаляется и в базе данных не сохраняется.

Рис. 4.4. Таблица результатов выполнения запроса на выборку

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

В строку Условие отбора нужного поля (или нескольких полей) ввести конкретное значение для выбора из таблицы тех записей, для которых это значение совпадает. Например, название города, наименование товара, название книги др. Если поле является текстовым, то значение вписывается в двойных кавычках – ”, при этом если в БД содержаться такие кавычки в качестве полезной информации, то в условии отбора они должны быть заключены в квадратные скобки. Например, [”]Фарм-плюс[”] Даты записываются в формате #01.01.1999#.

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

= – равный чему-либо;

> – больше чем;

< – меньше чем;

>= – больше чем или равно;

<= – меньше чем или равно,

а также логических функций:

And – логическое И;

Or – логическое ИЛИ;

Xor – логическое исключающее ИЛИ;

Not – логическое НЕ.

Для текстовых полей в строку Условие отбора нужного поля ввести выражение с помощью оператора Like по типу:

– Like”*ххххххх*” – для отбора записей по частичному совпадению значения данного поля;

– Like”[а-к]*” – для отбора записей по значению поля, которое начинается с букв в диапазоне от а до к, например, название города, наименование товара, название книги др.

–Like ”[H,U]*” – для отбора записей по значению поля, которое начинается с букв H или U.

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

Для исчисляемых полей (числовое, дата/время) для отбора записей по совпадению значения данного поля из какого-либо диапазона в строку Условие отбора нужного поля ввести выражение с помощью оператора Between по типу Between #ХХХХ# And #YYYY#. Здесь ХХХХ – начальное значение диапазона, YYYY – конечное значение диапазона, причем YYYY должно быть больше ХХХХ.

Создание вычисляемых полей

В пустую ячейку строки Поле бланка запросов ввести вычисляемое выражение, например [Стоимость]*1,2, и нажать кнопку Enter. После нажатия кнопки вычисляемому полю автоматически присваивается имя типа Выражение 1, 2 и т. д.

Указать, что поле должно выводиться на экран.

присвоить новое вычисляемому полю имя, например Новая цена:

– установить курсор в строку Поле для вычисляемого поля;

– удалить старое имя вычисляемого поля (Выражение 1);

– ввести новое имя Новая цена и поставить знак двоеточия (в результате получится Новая цена: [Стоимость]*1,2).

Для вычисляемого поля определить необходимый формат данных.

Создание вычисляемых полей с помощью построителя выражений

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

Рис. 4.5. Вызов построителя выражений

В окне Построитель выражений (рис. 4.6.) в виде списка доступны все операторы и функции MS ACCESS, а так же все объекты базы данных. В левой части окна необходимо выбрать таблицы, на которых построен запрос. Справа отобразится список их полей. Для использования поля в выражении достаточно дважды щелкнуть на его имени в списке. Знаки операций вводятся с клавиатуры. При этом в верхней части окна сформируется выражение.

Рис. 4.6. Окно Построитель выражений

После формирования нужного выражения нажатием кнопки OK введенное выражение помещается в бланк запроса и ему автоматически присваивается имя Выражение 1, 2 и т. д. (рис. 4.7). Для смены данного имени на новое необходимо выделить его и вписать новое имя.

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

Рис. 4.7. Полученное выражение

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

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

Определить таблицы, необходимые для создания итогового запроса.

Определить поля, необходимые для создания итогового запроса.

Щелкнуть правой кнопкой мыши на нижней части бланка запроса и в выпадающем меню выбрать пункт Итоги.

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

-Sum – сумма значений некоторого поля для группы;

-Avg – среднее от всех значений поля в группе;

-Max, Min – максимальное, минимальное значения поля в группе;

-Count – число значений поля в группе без учета пустых значений;

-Stdev – среднеквадратичное отклонение от среднего значения поля в группе;

-Var – дисперсия значений поля в группе;

-First и Last – значения поля из первой или последней записи в группе.

В строке Сортировка итогового поля выбрать необходимый тип сортировки.

присвоить вычисляемому полю новое имя:

– установить курсор в строке Поле для вычисляемого поля перед старым именем;

– ввести новое имя;

– поставить знак двоеточия (:) ( в результате получится, например Количество заказанных книг: Код книги).

При необходимости в строку Условие отбора ввести выражение для выбора данных по нужному полю.

Сохранить запрос: Файл / Сохранить или кнопка / в поле Имя запроса ввести требуемое имя запроса / ОК.

Например, для создания запроса для подсчета фактического числа студентов в группе сначала создадим запрос на выборку из таблицы СТУДЕНТ. Из списка таблицы СТУДЕНТ перетащим в бланк запроса поле НГ (номер группы). Таким образом, мы укажем, что по этому полю должна производиться группировка. Перетащим в бланк запроса поле НС (номер студента), по которому будет вычисляться функция Count для подсчета числа студентов в группе.

Нажмем кнопку Итоги. Заменим слово «Группировка» в столбце НС на функцию Count. Для этого вызовем список и выберем эту функцию. Бланк запроса примет вид, показанный на рис. 4.8. Присвоим вычисляемому полю имя «Фактическое число студентов». Результат запроса показан на рис. 4.9.

Рис. 4.8. Запрос для подсчета числа студентов в группе

Рис. 4.9. Результат подсчета числа студентов в группе

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

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

Определить таблицы, необходимые для создания перекрестного запроса.

Определить поля, необходимые для создания перекрестного запроса.

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

Указать, значения какого поля будут использоваться в качестве заголовков строк (например, поле НГ): в строке Перекрестная таблица бланка запроса для выбранного поля выбрать из списка Заголовки строк.

Указать, значения какого поля будут использоваться в качестве заголовков столбцов (например, поле НП): в строке Перекрестная таблица бланка запроса для выбранного поля выбрать из списка Заголовки столбцов.

Указать, значения какого поля будут использоваться для вычисления итогов в перекрестной таблице (например, поле часы для вычисления количества часов по изучаемым предметам):

– в строке Перекрестная таблица бланка запроса для выбранного поля выбрать из списка Значение;

– в строке Групповая операция бланка запроса для выбранного поля выбрать из списка функцию SUM.

Добавить в запрос столбец для вычисления общих итогов (например, для вычисления общего количества часов по всем изучаемым предметам):

– перенести еще раз в бланк запроса поле, которое выбрано для определения итогов (например, поле часы);

– в строке Перекрестная таблица бланка запроса для этого поля выбрать из списка Заголовки строк;

– в строке Групповая операция бланка запроса для этого поля выбрать из списка функцию SUM.

присвоить вычисляемому полю новое имя Всего часов:

– установить курсор в строке Поле для этого поля перед старым именем;

– ввести имя Итого;

– поставить знак двоеточия (:) (в результате получится, например Всего часов:часы).

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

– щелкнуть мышью в любом месте окна запроса вне бланка запроса и списка полей;

– из пункта головного меню Вид выбрать Свойства;

– в диалоговом окне Свойства запроса в строку Заголовки столбцов ввести постоянные заголовки столбцов (например, “история”; ”Математика” и т. д.);

– закрыть диалоговое окно Свойства запроса.

Определить условие для выбора записей:

– добавить (при необходимости) в бланк запроса поле, по которому будут выбираться записи;

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

– в строке Групповая операция бланка запроса для этого поля выбрать из списка Условие;

– в строке Условие отбора для поля, по которому будут выбираться записи, ввести условие отбора.

Сохранить запрос: Файл / Сохранить или кнопка / в поле Имя запроса ввести требуемое имя запроса / ОК.

Окончательно сформированный бланк перекрестного запроса приведен на рис. 4.10. Результат выполнения полученного перекрестного запроса приведен на рис. 4.11.

Рис. 4.10. Перекрестный запрос в режиме конструктора

Рис. 4.11. Результат выполнения перекрестного запроса

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

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

Определить таблицы, необходимые для создания параметрического запроса.

Определить поля, необходимые для создания параметрического запроса.

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

В строку Условие отбора ввести выражение для выбора данных по нужному полю по типу:

– Like [наименование товара], например для отбора записей о заказах по наименованию товара;

– Like [первая буква наименования товара] & “*”, например для отбора записей о заказах по первым буквам наименования товара;

– Between [начальная дата] And [конечная дата], например для отбора записей о заказах за определенный период.

Определить тип данных для каждого параметра:

– из пункта головного меню Запрос выбрать Параметры…;

– в первую строку столбца Параметр диалогового окна Параметры запроса ввести имя первого параметра (например начальная дата);

– в столбце Тип данных для этого параметра выбрать из списка нужный тип (например дата/время);

– во вторую строку столбца Параметр диалогового окна Параметры запроса ввести имя второго параметра (например конечная дата);

– в столбце Тип данных для этого параметра выбрать из списка нужный тип (например дата/время);

– закрыть диалоговое окно с сохранением введенной информации: ОК.

Сохранить запрос: Файл / Сохранить или кнопка / в поле Имя запроса ввести требуемое имя запроса / ОК.

На рис. 4.12 представлен бланк параметрического запроса для получения информации об оценке студента по заданному предмету. При выполнении запроса MS ACCESS выведет диалоговые окна, представленные на рис. 4.13, в которые пользователь сможет ввести нужные значения параметров.

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

Рис. 4.13. Диалоговые окна для ввода параметров запроса

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

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

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

Для создания запроса данного типа необходимо вначале в режиме конструктора создать запрос на выборку. Затем преобразовать этот запрос в запрос на создание таблицы, выбрав тип запроса на панели конструктора Создание таблицы (рис. 4.14). В окне Создание таблицы необходимо ввести имя создаваемой таблицы (рис. 4.15).

Рис. 4.14. Преобразование запроса на выборку в запрос на создание таблицы

Рис. 4.15. Определение имени таблицы, создаваемой в запросе

При выполнении запроса будет создана и сохранена в базе данных таблица с введенным именем. Теперь эту таблицу можно увидеть в списке таблиц окна БД.

ПОРЯДОК ВЫПОЛНЕНИЯ РАБОТЫ

1. Получить у преподавателя индивидуальное задание на выполнение лабораторной работы.

2. Загрузить MS Access. Открыть базу данных.

3. Создать в режиме конструктора запросы на выборку данных в соответствии с индивидуальным заданием.

4. Создать итоговые запросы в соответствии с индивидуальным заданием.

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

6. Создать перекрестные запросы в соответствии с индивидуальным заданием.

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

8. Завершить работу с базой данных. Завершить работу с MS ACCESS.

9. Оформить отчет по работе.

СОДЕРЖАНИЕ ОТЧЕТА

1. Цель работы.

2. Краткие сведения из теории.

3. Распечатка результатов выполнения запросов в виде таблиц.

4. Выводы.

# КОНТРОЛЬНЫЕ ВОПРОСЫ

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

2. Что такое запрос на выборку?

3. Что такое перекрестный запрос?

4. Что такое запрос на добавление?

5. Перечислить способы создания запросов.

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

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

8. Что такое подчиненный запрос?

9. Что такое запрос на удаление?

10. Что такое запрос на изменение?

ЛИТЕРАТУРА

1. Глушаков, С. В. Базы данных / С. В. Глушаков, Д. В. Ломотько. – Харьков : Фолино ; М. : АСТ, 2002.

2. Бутов, А. А. Технологии организации, хранения и обработки данных : лаб. практикум для студ. экон. спец. БГУИР всех форм обуч. / А. А. Бутов. – Минск : БГУИР, 2008.

3. Коренская, И. Н. Офисные и интернет-приложения : учеб.-метод. пособие / И. Н. Коренская, В. А. Полубок. – Минск : БГУИР, 2013.

4. Оскерко, В. С. Технологии баз данных : учеб. пособие / В. С. Оскерко, З. В. Пунчик, О. А. Сосновский. – Минск : БГЭУ, 2007.