Создание и выполнение запросов в СУБД Access

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

С помощью Microsoft Access могут быть созданы следующие типы запросов:

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

2) запрос-изменение изменяет или перемещает данные. К этому типу относятся: запрос на добавление записей, запрос на удаление записей, запрос на создание таблицы, запрос на обновление.

3) перекрестные запросы предназначены для группирования данных и представления их в компактном виде.

4) запрос с параметром позволяет определить одно или несколько условий отбора во время выполнения запроса.

5) и другие.

В Microsoft Access имеется возможность создавать запросы самостоятельно при помощи конструктора или воспользоваться мастером по разработке запросов. Чаще всего запрос разрабатывается самостоятельно.

Рассмотрим пример составления запросов на основе созданной ранее базы данных Продажа компьютеров. Откройте её из своей папки. Сформируем запрос, который покажет нам данные о клиентах из Москвы, оформивших заказ позднее 01.08.02.

В левой части окна Продажа компьютеров: база данных выберете Запросы. В собственной панели инструментов активного окна базы данных нажмите кнопку Создать и в появившемся окне выберете пункт Конструктор и нажмите ОК. Появляется окно Конструктора Запросов и поверх него диалоговое окно Добавление таблицы, содержащее три вкладки: на одной отображается список таблиц, на другой – список всех запросов, а на третьей для удобства выведен перечень таблиц и запросов вместе. Выберем таблицы, которые должны участвовать в запросе, а именно для нашей задачи это одна таблица Клиенты. Затем нажимаем кнопку Добавить и закрываем окно Добавление таблицы. Теперь перед Вами окно, разделенное на две панели: в верхней панели находятся списки полей таблицы, выбранной для запроса, нижняя панель является бланком запроса. Переход между ними осуществляется клавишей <F6 > или щелчком мыши по соответствующему окну.

Чтобы добавить поле в запрос, необходимо в таблице-источнике в верхней части окна конструктора найти нужное поле и дважды щелкнуть на нём левой кнопкой мыши. При этом в бланке запроса появится столбец, соответствующий выбранному полю. Поместите описанным способом из верхней части окна поле Код модели в первую клетку строки Поле: бланка запроса. Аналогично поступите с полями Заказчик, ФИО, Город, Телефон, Дата заказа, разместив их в последующих клетках строки Поле:. На пересечении строки Сортировка: и столбца ФИО укажем параметр По возрастанию, который отсортирует данные в запросе в алфавитном порядке следования фамилий клиентов. В строке Вывод на экран: пометьте флажками поля (если они не помечены), которые будут выведены в набор записей.

В строке Условие отбора: можно определить критерии, по которым будут отбираться записи в запросе. Для нашего случая в строке Условие отбора: в столбце с полем Город введите " Москва ", а в столбце с полем Дата заказа, введите условие >01.08.02 для определения условия выбора записей. Войдите в меню Файл и выберите команду Сохранить. Введите с клавиатуры имя запроса Из Москвы с августа, щелкните по кнопке ОК. Закройте окно запроса на выборку.

Рис. 10. Конструктор запроса Из Москвы с Августа

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

Задание. 1) Создайте запрос (при сохранении название этого и последующих запросов придумываете самостоятельно): вывести всех клиентов из города Ельца, причем их фамилии должны быть расположены в порядке следования букв в алфавите. 2) Создайте запрос, который выводит модели компьютеров с ценой более 15000 руб., причем цены расположите в порядке убывания. 3) Создайте запрос: вывести все модели компьютеров, которые имеют лазерный диск, причем выводить только поля Модель и Цена. 4) Создайте запрос, выводящий на экран заказчиков, которые приобрели модель компьютера со следующими параметрами: объем жесткого диска больше 30 Gb, а цена меньше 25000 руб. 5) Создайте запрос: вывести всех заказчиков, их ФИО и Телефон, количество приобретенных компьютеров которых превышает 10 штук.

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

Задание. 1) Создайте запрос, выводящий номера и фамилии студентов, родившихся после 1 сентября 1983 года. 2) Создайте запрос: вывести фамилии и имена всех мальчиков экономического факультета в алфавитном порядке. 3) Создайте запрос, выводящий фамилию, имя, отчество, ученую степень и ученое звание всех преподавателей с ученой степенью доктор наук и ученым званием профессор. 4) Создайте запрос, в котором покажете список студентов физического факультета – их фамилии и имена, факультет, группу и преподавателя, который ведет на данном факультете. 5) Создайте запрос: вывести фамилию, имя, отчество всех преподавателей юридического факультета, у которых фамилия начинается на букву И (используйте ввод по маске (* - любое количество любых символов)).

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

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

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

Литерал – это точное значение, которое Access использует именно в том виде, как оно вводится. При записи литерала используются специальные символы-ограничители, которые указывают на тип данных литерала. Если литерал – число, то он вводится без ограничений. Например, 1437.94. Текстовый литерал должен иметь в качестве ограничителя “ или ‘. Например, “ Петров ” или ‘ Петров’. В литералах типа дата используется ограничитель #. Например, # 03.10.2003 #. В случае литерала типа поле или элемента управления вводятся ограничители [ ]. Например, [ Фамилия ].

Оператор указывает действие, которое должно быть выполнено с элементами выражения. Выделяют следующие группы операторов: арифметические (* умножение, + сложение, - вычитание, / деление, ^ возведение в степень); соединение частей текста &, например, =[Фамилия]&“ ”&[Имя]; сравнения (< меньше, <= меньше или равно, > больше, >= больше или равно, = равно, <> не равно); логические (And, Not, Or); операторы SQL, например, Between…And – для выбора значений из определенного интервала.

Константа – это неизменяемая величина. Например, Null (поле, не содержащее значений или символов), Истина, Ложь.

Идентификатор – это имя, введенное в выражении для резервирования места под значение, которое хранится в поле или элементе управления. На основе использования идентификаторов можно создавать выражения, которые используют информацию, хранящуюся в таблицах, формах, отчетах. Идентификаторы обычно заключаются в квадратные скобки [ ]. Например, [Пол] относится к значению поля «Пол» таблицы Студенты.

Функция – это специальное имя, которое используется для выполнения какой-либо операции и может применяться в выражениях. Аргументы функций должны заключаться в (). Скобки могут быть опущены только при нулевом аргументе. Например, Sum() – сумма всех записей; Avg() – среднее арифметическое значений; Count() – количество записей и другие.

Задание. В базе данных Университет создайте запрос, который выводит два поля - Студент (содержащее и фамилию, и имя студента в одном поле) и Дата рождения. Для выполнения задания откройте базу данных Университет. Создадим данный запрос в режиме конструктора (переходим на вкладку Запросы и нажимаем кнопку Создание запроса в режиме Конструктора). В окне Добавление таблиц выбираем Студенты и закрываем его. Затем установите курсор в первую строку первого столбца и нажмите комбинацию клавиш Shift+F2. Появится диалоговое окно Область ввода. Введите в область ввода строку Студент:[Фамилия]&“ ”&[Имя]. Первая часть до двоеточия будет именем нового поля. Вторая часть – выражение, значением которого является слияние полей Фамилия и Имя. Между ними нужно обязательно вставить пробел, иначе при выводе на экран имя и фамилия студента будут отображаться слитно. Квадратные скобки вокруг имен полей можно и не вводить – Access автоматически добавит их позже. Далее нажмите ОК – строка будет вставлена в бланк запроса. Установите в данном столбце галочку в поле Вывод на экран. В следующем столбце в строке Поле выберете Дата рождения и проверьте, чтобы стояла галочка в поле Вывод на экран. Сохраните и закройте запрос. Запустите его в режиме таблицы. Получим искомый результат.

Задание. В базе данных Продажа компьютеров создайте запрос, который подсчитывает сумму покупки клиента с учетом количества проданного товара, скидки и цены за единицу. Для этого создадим новый запрос в режиме конструктора, добавив в него таблицы Клиенты и Модели компьютеров. Заметим, что между таблицами автоматически образовалась связь Один-ко-многим по Коду модели. В бланке запроса отобразим следующие поля: Заказчик, Дата заказа, Количество, Скидка – из таблицы Клиенты; Цена – из таблицы Модели компьютеров. Теперь создадим вычисляемое поле Сумма покупки. В строке Поле следующего пустого столбца вызываем контекстное меню (правая кнопка мыши) и выбираем команду Построить. Запускается окно Построитель выражений. Верхняя часть Построителя выражений служит для визуализации создания вводимой формулы, с помощью нижней части мы конструируем нужные выражения. Требуется составить следующую формулу: «Количество умноженное на цену за единицу и умноженное на скобку – в скобке единица минус скидка». Реализуем эту запись на компьютере. Для этого в крайнем левом нижнем окне Построителя выражений двойным щелчком мыши открываем папку Таблицы и выберем таблицу Клиенты. В окне справа отображаются все поля выбранной таблицы. Двойным щелчком мыши поместим поле Количество в окно визуализации формулы. Затем поставим знак умножения * и аналогичными действиями закончим формулу. В результате должно получиться следующее (Рисунок 11).

Рис. 11. Окно построителя выражений.

Нажимаем ОК и в строке Вывод на экран данного столбца ставим галочку. В полученной формуле слово Выражение 1 заменим на Сумма покупки. Закроем окно конструктора запроса с сохранением, присвоив ему имя Сумма покупок клиентов. Запускаем полученный запрос из окна базы данных Запросы.

В запросе все данные отображаются в том формате, в котором они были в исходной таблице. Например, в запросе Сумма покупок клиентов данные в столбце Сумма покупки отображаются некорректно, т.е. слишком много чисел справа от запятой. К тому же, в некоторых столбцах нужно установить денежный формат. А также можно в качестве единицы измерения столбца Количество выводить штуки (шт.) и т.д. Для изменения формата данных в запросе используется свойство Формат поля. В режиме конструктора рассматриваемого запроса щелкните правой кнопкой мыши в строке Поле того поля, формат которого нужно изменить. Изменим формат столбца Сумма покупки. Вызываем на нём конт2耀стное меню и выбираем опцию Свойства. В качестве формата поля выбираем Денежный и закрываем окно. Просмотрите результат наших действий в режиме запроса. Аналогично задайте формат поля Цена.

Теперь поменяем свойства поля Количество. Для этого достаточно задать следующую маску ввода в режиме конструктора запроса: 000000“ шт.”. Данная маска ввода приписывает символы шт. ко всем числам, количество цифр в которых не более шести. Просмотрите результат Ваших действий в режиме запроса.

Задание. Самостоятельно в базе данных Продажа компьютеров создайте запрос, который бы отображал Сумму покупки только тех клиентов, количество заказанных штук которых превышает 10 (Отобразить поля: Ф.И.О. заказчика, Дата заказа, Количество, Сумма заказа).

В самом первом примере лабораторной работы №3 мы рассмотрели, как действует условие отбора (отбор клиентов из города Москва). Важно отметить существенный недостаток такого запроса: если пользователь базы данных работает с запросами, которые ему подготовил разработчик, и возникает необходимость отобрать клиентов из другого города, т.е. поменять условие отбора. В этом случае требуется работа пользователя в режиме конструктора, что часто бывает недоступным или нежелательным. Или если, например, разработчик предусмотрел запрос, отбирающий изделия, имеющие цену менее 150 условных единиц, то пользователь базы уже не в состоянии отобрать изделия, цена которых менее 140 условных единиц, поскольку у него нет соответствующего запроса. В таких случаях целесообразно использовать специальный тип запросов, называемый запросами «с параметром», который позволяет пользователю самому ввести критерий отбора данных на этапе запуска запроса. Этим приемом обеспечивается гибкость работы с базой.

В качестве примера, создадим простой запрос в базе данных Продажа компьютеров, позволяющий отбирать клиентов из города, который пользователь может задать сам при запуске запроса. Для этого создадим с помощью конструктора обычный запрос, выводящий Номере заказа, Заказчика, Город и Телефон. В строку Условия отбора для столбца Город введите вместо конкретного значения приглашение к вводу параметра [Город заказчика:]. Приглашение должно быть обязательно заключено в квадратные скобки. Запустите запрос. Появится диалоговое окно с введенным Вами приглашением к вводу параметра (Рисунок 12).

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

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

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

Задание. Самостоятельно создайте запрос с параметром в базе данных Продажа компьютеров, который выводит ФИО клиента, Количество заказа и Дату заказа за определенный период времени, т.е. при запуске запроса требуется ввести Начало периода и Конец периода. В качестве Условия отбора в столбце Дата заказа введите выражение Between [Начало периода:] And [Конец периода:]. При вводе значений параметров типа Дата желательно контролировать значения дат, для чего нужно указать тип данных для этого параметра.

Замечание. Between – оператор сравнения, означающий Между; And – логический оператор, означающий логическое И. Возможно вставить данные операторы через Построитель выражений.

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

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

Иногда при проектировании базы данных возникает необходимость создания новой таблицы из некоторого запроса. Сам же запрос лишь отображает данные из включенных в запрос таблиц в режиме таблицы. Исправление данных в запросе ведет за собой изменение данных стоящих за ним таблиц. А нам нужно, чтобы изменения данных в запросе не затронули исходные таблицы. Например, требуется создать таблицу в базе данных Продажа компьютеров, которая включает в себя следующие поля: ФИО клиента, Дата заказа, Сумма покупки. Для этого первоначально самостоятельно создайте соответствующий запрос в режиме конструктора, затем в меню Запрос выберете пункт Создание таблицы. В открывшемся окне укажите имя таблицы (Создана из запроса) и место создания – в текущей базе данных. Нажмите ОК и запустите запрос (меню Запрос – Запуск). В ответ на предложение создать таблицу щелкните на кнопке Да.

На закладке Таблицы базы данных просмотрите результат.

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

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

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

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

Для этого щелкните на кнопке Запросы в окне базы данных. В панели инструментов нажмите кнопку Создать. В окне Новый запрос выберете пункт Повторяющиеся записи и нажмите ОК, тем самым запустив Мастер поиска повторяющихся записей (Рисунок 13).

Рис. 13. Окно Мастера поиска повторяющихся записей

Выделите таблицу Студенты и нажмите кнопку Далее. Поместите в список Поля с повторами поля Фамилия, Имя из списка Доступные поля (Рисунок 14). Щелкните Далее.

Добавьте в список Дополнительные поля любые другие поля, которые Вы хотели бы видеть в запросе. В следующей стадии работы Мастера введите имя запроса и Дальнейшие действия – Просмотреть результаты запроса (Рисунок 15).

Рис. 14. Окно второго шага Мастера повторяющихся записей

Щелкнув на кнопке Готово, появится искомый запрос.

Задание. Аналогично создайте запрос в базе данных Университет на поиск повторяющихся записей в таблице Преподаватели по фамилии и имени.

Рис. 15. Заключительное окно Мастера повторяющихся записей

Часто при работе с базой данных возникает ситуация, когда необходимо полностью обновить все записи таблицы. Для этого нужно использовать запрос на обновление. Самый простой тип запроса на обновление используется для поиска определенного значения и замены его другим значением. Например, в базе данных Университет требуется заменить математический факультет на физико-математический во всех записях поля Факультет таблицы Студенты. Прежде всего, необходимо добавить в таблицу Справочник факультетов новую запись – Физико-математический. Затем приступаем к построению запроса. Выделим таблицу Студенты в окне базы данных и на панели инструментов раскроем список кнопки Новый объект  и выберем пункт Запрос. В диалоговом окне Новый запрос выбираем Конструктор и нажимаем ОК. В результате таблица Студенты уже будет добавлена в запрос. Затем в режиме конструктора в меню Запрос выберете команду Обновление. В строке Поле выбираем Факультет, в строке Имя таблицы – Студенты. В строку Обновление поместим значение Физико-математический, а условие отбора – Математический (Рисунок 16). Запустите запрос и подтвердите обновление записей. После этого можно закрыть запрос на обновление без сохранения. Результат своих действий посмотрите в таблице Студенты, открыв ее в режиме Таблицы.

Задание. Самостоятельно замените в базе данных Университет в таблице Преподаватели все значения Математический на Физико-математический.

В процессе редактирования таблицы в режиме таблицы Вы имеете возможность удалять записи по одной или по несколько, если записи смежные. Во многих случаях этот прием работает, но он оказывается неэффективным, когда необходимо удалить целую группу записей, которые в таблице расположены непоследовательно. Например, пусть нам требуется удалить в базе данных Университет из таблицы Студенты все записи о студентах, годом рождения которых является 1980 (если таких записей нет у Вас в таблице – измените имеющиеся или добавьте новые; обратите внимание – сколько записей с таким условием). Для этого аналогично предыдущему примеру создайте запрос, в режиме конструктора в пункте меню Запрос выберете Удаление. В строках Поле и Имя таблицы соответственно задайте значения Дата рождения и Студенты. В строке Удаление по умолчанию оставьте значение Условие, а в строке Условие отбора введите *80.

Рис. 16. Пример конструктора запроса на обновление

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

Рис. 17. Пример конструктора запроса на удаление

Замечание. Запрос на удаление позволяет удалять записи из связанных таблиц. Для этого в режиме конструктора запроса в строке Удаление выбирается опция Из.

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

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

Приведем описание некоторых из них:

Avg() – вычисляет арифметическое среднее набора чисел, содержащихся в указанном поле запроса.

Count() – вычисляет количество непустых записей, возвращаемых запросом.

Max() – возвращает максимальное значение из набора, содержащегося в указанном поле.

Min() – возвращает минимальное значение из набора, содержащегося в указанном поле.

Sum() – возвращает сумму набора значений, содержащихся в заданном поле.

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

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

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

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

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

Авторы (Код автора, Фамилия, Имя, Отчество)

Издательства (Код издательства, Издательство)

Тематические разделы (Код раздела, Раздел)

Издания (Код издания, Код раздела, Код автора, Название книги, Год издания, Код издательства, Число страниц, Цена) – 15 записей!

Покупатели (Код покупателя, Покупатель, Скидка)

Заказы (Код заказа, Дата заказа, Код издания, Код покупателя, Количество)

Установите связи между таблицами. Используйте подстановки!!!

Создайте и сохраните следующие запросы:

- вывести данные о всех издательствах (Код издательства, Издательство);

- вывести данные об изданиях, цена которых менее 100 руб. (Код издания, Раздел, Фамилия автора, Название книги, Цена);

- вывести данные о всех имеющихся в магазине изданиях (Код издания, Раздел, Ф.И.О. автора, Название книги, Издательство, Год издания, Число страниц, Цена);

- вывести данные о покупателях, которые не имеют скидки (Код покупателя, Покупатель);

- вывести данные о всех заказах, которые были оформлены с 01.10.2002 по 01.11.2002 (Код заказа, Дата заказа, Название книги, Автор, Количество, Покупатель);

- вывести данные о сумме покупок клиентов (Код заказа, Покупатель, Название книги, Фамилия автора, Цена, Количество, Скидка, Сумма покупки);

- вывести данные о заказах за определенную дату, которая вводится при запуске запроса (запрос с параметром) (Код заказа, Дата заказа, Фамилия автора, Название книги, Покупатель, Количество);

- вывести данные о количестве книг по разделам (Раздел, Количество книг).