prosdo.ru
добавить свой файл
1
Основы разработки запросов

Запросы являются одним из основных инструментов выборки данных в СУБД. Запрос строится: на основе одной или нескольких взаимосвязанных таблиц, на основе таблиц, полученных в результате выполнения других запросов, на основе другого запроса с использованием его временной таблицы с результатами. В СУБД имеется удобное средство формирования запроса по примеру QBE (Query By Example). Запрос по примеру содержит схему данных, включающую используемые таблицы и бланк запроса. При конструировании запроса достаточно, работая мышью, выделить и перетащить необходимые поля из таблиц, представленных в схеме данных, в бланк запроса и ввести условия отбора записей.

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


  • Выбрать записи, удовлетворяющие условиям

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

  • Произвести вычисления в каждой из полученных записей

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

  • Произвести обновление полей в выбранном подмножестве записей

  • Создать новую таблицу БД, имея данные из существующих таблиц

  • Удалить/добавить выбранное подмножество записей из таблицы БД и пр.

В Access можно создать следующие запросы: запрос на выборку, запрос на создание таблицы, запрос на обновление, добавление, удаление.

Для создания запроса надо в окне БД перейти вкладке Запросы, нажать Создать, выбрать режим Конструктора. В окне Добавление таблицы выбираем используемые в запросе таблицы и нажимаем Добавить, затем-Закрыть. В результате получим окно Конструктора запросов, содержащее схему данных (вверху) и бланк запроса (внизу). Первоя строка в списке полей, отмеченная звездочкой, обозначает все множество полей таблицы.

Бланк запроса по примеру

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


При заполнении бланка:


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

  • В строке Сортировка выбирается порядок сортировки записей результата

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

  • В строке Условие отбора задаются условия отбора записей

  • В строке или задаются альтернативные условия отбора записей.

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

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

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

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

  • Литералы – конкретные значения, воспринимаемые СУБД так, как они записаны. Это могут быть: числа, текстовые строки, даты. Текстовые строки заключаются в двойные кавычки, даты – в символы #.

  • Константы – не изменяющиеся значения, которые определены в Access, например: True, false, null и пр.

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

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

=, <, >, <>, Between, in, like, and, or, not.


Between – позволяет задать интервал для числового значения, например:

Between 10 and 100 задает интервал от 10 до 100.

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

In(«Математика», «Информатика», «История»)

Like позволяет использовать образцы, использующие символы шаблона, при поиске в текстовых полях, например:

Like «Иванов*»

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

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

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

Бланк запроса:

Схема данных




Поле

Имя таблицы

Сортировка

Вывод на экран

Условия отбора

или


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

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

Арифметические выражения вводятся в бланк запроса в пустую ячейку строки Поле, нажимаем ввод или перевод курсора, перед выражением в этой ячейке добавляется имя поля Выражение N, где N – целое число, увеличивающееся на единицу для каждого нового создаваемого вычисляемого поля в запросе. Имя вычисляемого поля, стоящее перед выражением, отделяется от него двоеточием. Например:


<выражение1>: [цена]*[количество], гдецена и количество – имена полей.

Имя вычисляемого поля Выражение1 становится заголовком столбца в таблице с результатами выполнения запроса.

В Access имеются встроенные функции, которые можно использовать в вычисляемых полях:


  • Date формирует текущую дату

  • Month выделяет месяц из значения поля, содержащего дату

  • Dlookup возвращает значение конкретного поля из записи связанной таблицы, не участвующей в запросе

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

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

Month ([студент]![датар]) ,

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

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

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

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

Например: если в условии отбора рассмотренного выше запроса номер месяца (5) заменить на имя параметра <номер месяца>, то при выполнении запроса будет выводиться диалоговое окно, позволяющее ввести значение этого параметра запроса.

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


Например: для отбора записей по двум группам в условии отбора поля НГ можно записать два параметра, связанных логической операцией or:<номер группы> or<еще один номер> .

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

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

Year([Студент]![датар])<[Год рождения]

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

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

Для того, чтобы создать такой запрос первоначально создается запрос на выборку, а затем в окне конструктора запросов он преобразуется в запрос на обнавление выбором пункта Обновление из списка Тип запроса или команды Запрос/Обновление. После выполнения этой команды в бланке запроса появится строка Обновление.

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


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

С помощью запроса на добавление производится добавление записей из таблицы результата запроса в таблицу БД. Поэтому надо, чтобы в запросе были сформированы записи с полями, соответствующими полям в дополняемой записями таблице БД.

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

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

Запрос на удаление.