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

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

Конструирование однотабличного запроса на вы-борку

Рассмотрим процесс конструирования однотабличного запроса на выборку на примере получения информации из таблицы ПРЕДМЕТ базы данных «Учебный процесс».

Использование логических операций в условии отбора

Пусть надо выбрать предметы, по которым общее число изучения не более 100, и есть лекции, а также выбрать предметы, по которым общее число часов боль-ше 150 и число семестров изучения не более двух. Результат должен содержать наименование предмета (НП), общее число часов по предмету (ЧАСЫ), количе-ство лекционных часов (ЛЕК) и число семестров (ЧС).

Для создания запроса в режиме конструктора в окне базы данных выбе-рем объект Запросы (Queries) и нажмем кнопку Создать (New). Можно также на панели инструментов нажать кнопку списка Новый объект (New Object) и в открывшемся списке выбрать Запрос (Query).

В окне Новый запрос (New Query) (см. рис. 5.15) выберем строку Кон-структор(Design View). После нажатия кнопки ОК появляется окно за-проса на выборку в режиме конструктора <Имя запроса>: запрос на выборку (Select Query) (рис. 5.30) и диалоговое окно Добавление таб-лицы (Show Table) (см. рис. 5.16). В диалоговом окне выберем таблицу ПРЕДМЕТ и нажмем кнопку Добавить (Add). Выбранная таблица будет отображена в области схемы данных запроса. Закроем окно Добавле-ние таблицы (Show Table).

В окне конструктора (рис. 5.30) перетащим из списка полей таблицы ПРЕДМЕТ поля НП, ЧАСЫ, ЛЕК и ЧС в столбцы бланка запроса в строку Поле (Field).

Рис. 5.30. Окно конструктора запроса на выборку с логическими операциями в условии отбора.

Сформулированные в задаче условия требуют формирования следующего логи-ческого выражения:

(ЧАСЫ<=100 AND ЛЕК<>0) OR (ЧАСЫ>150 AND ЧС<3)

Здесь ЛЕК<>0 (число лекций не равно нулю), соответствует заданному в задаче условию выбрать предметы, в которых есть лекции.

Условия из первых скобок запишем в соответствующих полях ЧАСЫ и ЛЕК первой строки Условия отбора (Criteria). Между условиями в раз-ных полях одной строки выполняется логическая операция . Условия из вторых скобок запишем в соответствующих полях ЧАСЫ и ЧС второй строки Условие отбора (Criteria). Между условиями, записанными в раз-ных строках, выполняется логическая операция .

Выполним запрос, нажав на панели конструктора запросов кнопку За-пуск (Run), или кнопку Вид (View). На экране появится окно запроса в режиме таблицы с записями из таблицы ПРЕДМЕТ, отвечающими за-данным условиям отбора.

Сохраним запрос, нажав кнопку Сохранить (Save) и задав нужное имя запроса. Закроем текущий запрос по команде меню Файл|Закрыть (File|Close).или, нажав кнопку окна запроса Закрыть (Close). Сохранен-ный запрос можно выполнить, выделив запрос в окне базы данных, на-жатием кнопки Открыть (Open).

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

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

Пусть необходимо проверить правильность задания общих часов в таблице ПРЕДМЕТ. По запросу должны отбираться только те записи, в которых значе-ние в поле ЧАСЫ не равно значению, получаемому при сложении значений по-лей ПР и ЛЕК.

Такое условие записывается в бланке запроса в столбце ЧАСЫ и в нем исполь-зуются имена полей [ПР] и [ЛЕК], как показано в бланке запроса на рис. 5.31.

Рис. 5.31. Окно запроса на выборку из таблицы ПРЕДМЕТ записей, в которых количе-ство часов не равно сумме часов лекций и практики

Конструирование многотабличного запроса на вы-борку

Рассмотрим технологию конструирования многотабличного запроса на выборку на примере получения информации об успеваемости студентов из БД "Учебный процесс".

Запрос на основе нескольких взаимосвязанных таблиц

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

Для создания запроса сначала в окне базы данных выберем вкладку За-просы(Queries) и нажмем кнопку Создать(New). В окне Новый запрос (New Query) (см. рис. 5.15) выберем строку Конструктор(Design View).

Замечание

Напомним, что можно сразу перейти к созданию нового запроса в режиме конструктора, если в окне базы данных при выбранном объекте Запросы щелкнуть на строке Создание запроса в режиме конструктора (Create query in Design view).

Формирование схемы данных запроса

В окне Добавление таблицы (Show Table) (см. рис. 5.16) выберем табли-цы:

r СТУДЕНТ- для выборки фамилия студента из поля ФИО

r УСПЕВАЕМОСТЬ- для определения кодов предметов (поле КП), по которым студент сдал экзамены, выборки оценок по предмету (из поля ОЦЕНКА).

r ПРЕДМЕТ- для выборки наименования предмета (из поля НП), пред-ставленного кодом КП в таблице УСПЕВАЕМОСТЬ.

Закроем окно Добавление таблицы (Show Table).

В окне конструктора запросов (рис. 5.32) представлена схема данных запроса, содержащая выбранные таблицы. Между таблицами автоматически установле-ны необходимые связи:

r Одно-многозначная связь между таблицами СТУДЕНТ и УСПЕВАЕМОСТЬ по составному ключу НГ+НС в соответствии с построенной ранее схемой данных.

r Связь-объединение между УСПЕВАЕМОСТЬ и ПРЕДМЕТ поскольку эти таблицы имеют поля с одинаковым именем КП и одинаковым типом данных.

Рис. 5.32. окно запроса об успеваемости студента с созданной схемой данных.

Подготовка бланка запроса

Поскольку в запросе используется несколько таблиц, в бланке запроса удобно видеть имя таблицы наряду с именем поля. Для отображения имен таблиц в бланке запроса (рис. 5.33) выполним команду Вид|Имена таблиц (View|Table Names) или нажмем соответствующую кнопку на па-нели инструментов.

Рис. 5.33. Запрос на получение информации о сдаче экзаменов студентками Боярской Н.П. и Маковой.

Перетащим с помощью мыши поля, включаемые в результат выполне-ния запроса, в строку бланка запроса Поле (Field):

r ФИО- из таблицы СТУДЕНТ

r НП- из таблицы ПРЕДМЕТ

r ОЦЕНКА- из таблицы УСПЕВАЕМОСТЬ

Ввод значений в условия отбора записей

Пусть необходимо получить информацию об успеваемости конкретных студен-тов: Боярской Н.П. и Маковой.

Зададим в строке Условие отбора (Criteria) их фамилии. Запишем фамилии сту-дентов в разных строках бланка запроса, поскольку необходимо выбрать записи со значением в поле ФИО- Боярская или Макова. Поскольку инициалы студент-ки Маковой неизвестны, ее фамилию зададим с использованием символа шаб-лона "*". Заметим, что фамилия с инициалами содержит точки, поэтому ее надо брать в кавычки. После ввода фамилии с символом шаблона система сама вставляет оператор Like, определяющий поиск по образцу. Заполненный бланк запроса представлен на рис. 5.33.

Выполним запрос, нажав на панели конструктора запросов кнопку За-пуск (Run) или Вид (View).

Замечание

Записи о заданном студенте появятся в результирующей таблице запроса только в том случае, если запись об этом студенте содержится в таблице СТУДЕНТ, а в таблице УСПЕВАЕМОСТЬ имеются записи, связанные с записью о студенте.

Формирование записей результата при выполнении за-проса

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

По заданной фамилии студента- Боярская Н.П.- в таблице СТУДЕНТ отыскива-ется запись. По значению ключа связи НГ+НС осуществляется выборка подчи-ненных записей из таблицы УСПЕВАЕМОСТЬ с оценками данного студента по разным предметам (в поле ОЦЕНКА). Для каждой из этих записей по значению ключа связи КП выбирается одна запись с наименованием предмета (НП) из таблицы ПРЕДМЕТ.

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

Ввод параметров в запрос

В предыдущем примере для задания фамилии конкретного студента необходи-мо было корректировать бланк запроса. Чтобы избежать этого, целесообразно использовать в запросе параметры. При этом Access перед выполнением запроса через диалоговое окно будет запрашивать у пользователя конкретные значения параметров и введет их в условия отбора.

Пусть необходимо получить информацию об оценке студента по заданному предмету.

В условие отбора поля ФИО вместо конкретной фамилии введем назва-ние параметра, по которому будет запрашиваться фамилия при выпол-нении запроса. Название параметра введем как текст, заключенный в квадратные скобки:

[Фамилия и инициалы студента]

Этот текст Access воспринимает как имя параметра (рис. 5.35). В условие отбо-ра поля НП введем второй параметр запроса:

[Наименование предмета]

Рис. 5.35. Бланк запроса с параметрами для ввода ФИО и НП.

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

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

Использование имен полей различных таблиц в условии отбора

Пусть необходимо выбрать записи из таблицы ИЗУЧЕНИЕ, в которых часы практических занятий по информатике не соответствуют равномерному распре-делению по семестрам всех часов практики.

Для решения этой задачи необходимо использовать таблицы:

r ИЗУЧЕНИЕ, в которой содержатся сведения о плановых занятиях в группах (в текущем семестре), в том числе о продолжительности (поле ЧАСЫ) каж-дого вида занятия (поле ВИД3).

r ПРЕДМЕТ, в которой содержатся сведения о наименовании (поле НП), об-щей продолжительности изучения предмета (поле ЧАСЫ), числа часов прак-тики (ПР) и числе семестров изучения (ЧС).

Для отбора записей о практических занятиях по информатике из таблицы ИЗУЧЕНИЕ надо в строке Условие отбора (Criteria) для поля НП (ТАБ-ЛИЦА ПРЕДМЕТ) задать значение "Информатика", а для поля ВИД3 (таблицы ИЗУЧЕНИЕ) задать значение "пр" (практическое занятие).

При равномерном распределении практики по семестрам общее число часов практических занятий по предмету (ПР) должно равняться произ-ведению часов практики (ЧАСЫ) из таблицы ИЗУЧЕНИЕ на число семе-стров (ЧС) из таблицы ПРЕДМЕТ. Для решения рассматриваемой задачи надо включить в результат только те записи, для которых число часов не соответствует этому произведению. Для этого запишем в Условие от-бора (Criteria) поля ПР (таблицы ПРЕДМЕТ) выражение:

[ИЗУЧЕНИЕ] ! [ЧАСЫ]*[ЧС]

Замечание

Указывать таблицу ИЗУЧЕНИЕ для поля ЧАСЫ обязательно, потому что поле с таким же именем имеется и в таблице ПРЕДМЕТ.

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

Результаты выполнения запроса приведены на рис. 5.38.

Замечание

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

Рис. 5.37. Запрос с использованием в условиях отбора имен полей из разных таблиц.

Рис. 5.38. Записи о практических занятиях по информатике, в которых число часов не отвечает заданным условиям

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

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

Рассмотрим технологию создания запроса с вычисляемым полем на примере таблицы ПРЕДМЕТ.

Пусть необходимо найти записи о предметах, в которых общее число часов по предмету не совпадает с суммой часов лекций и практики. Для решения этой за-дачи рассчитаем разность между общим числом часов по предмету (поле ЧА-СЫ) и суммой часов лекций (поле ЛЕК) и практики (поле ПР). в ответ включим только те записи, для которых эта разность не равна нулю.

Создадим запрос на выборку для таблицы ПРЕДМЕТ. Перетащим в бланк за-проса поля НП, ПР, ЛЕК, ЧАСЫ (рис. 5.39).

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

Для получения разности создадим вычисляемое поле в пустой ячейке строки Поле (Field), записав туда выражение:

[ЧАСЫ] - [ПР] - [ЛЕК]

Для отбора записей с ненулевым значением разности в вычисляемом поле в строку Условие отбора (Criteria) введем <>0 (не равно 0).

Рис. 5.39. Запрос с вычисляемым полем и условием отбора по его значению.

После ввода выражения система формирует имя вычисляемого поля по умолчанию- "Выражение 1 (ExprN:)". Это имя вставится перед выраже-нием (на рис. 5.39 скрыто). Для изменения имени установим курсор мы-ши в вычисляемом поле бланка запроса и нажмем правую кнопку мыши. В контекстно- зависимом меню выберем Свойства (Properties) поля, а в строку Подпись поля (Caption) введем новое имя поля- "ЧАСЫ не рав-ны ПР+ЛЕК". Имя поля может быть исправлено также непосредственно в бланке запроса.

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

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

Вызовем построитель выражений, нажав кнопку Построить (Build) пане-ли конструктора запросов (см. рис. 5.12) или выбрав команду Построить (Build…) в контекстно- зависимом меню (курсор мыши должен быть уста-новлен на строке Поле (Field) вычисляемого поля.

В левой части окна Построитель выражений (Expression Builder) (рис. 5.40) выберем таблицу ПРЕДМЕТ, на которой построен запрос. Справа отобразится список ее полей. Последовательно выберем нужные поля, нажимая кнопку Вставить (Paste), и знаки операций. При этом в верхней части окна сформируется выражение.

Рис. 5.40. Окно построителя выражений при формировании вычисляемого поля.

Сохраним запрос под именем "Разность часов по предмету".Сохранить. Запрос может быть выполнен в любой момент нажатием кнопки От-крыть (Open) в окне базы данных.

Построенный запрос может быть использован для проверки правильности за-полнения поля ЧАСЫ в таблице ПРЕДМЕТ.