Параметрический запрос в access. Как сделать параметрический запрос в access 2010?

Параметрические SQL запросы в СУБД Access

Параметрические SQL запросы либо запросы с параметрами в СУБД Access 2003 и 2007

Автор: Владимир Ткаченко

Обучение в интернет

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

Известно, что для извлечения инфы, лежащей в базе данных БД Access 2003 либо 2007, можно применить запрос SELECT на подборку данных из таблиц. Что касается запроса с параметрами, то он извлекает данные только в том случае, когда в диалоговом окне «Введите значение параметра» будет введено имя параметра отбора.

Составим параметрический SQL запрос (инструкцию SQL), для этого в открытой БД sql_training_st1_calcul.mdb на вкладке «Создание» выберем команду «Конструктор запросов». Раскроется активное окно диалога «Добавление таблицы» на фоне неактивного окна «Запрос1», дальше нужно закрыть окно диалога «Добавление таблицы». Затем на контекстной вкладке «Конструктор» выберем режим SQL, выполнив команду SQL, в итоге в окне редактирования будет отображаться оператор SELECT.

Вводим с клавиатуры последующую аннотацию SQL:
SELECT Группы.Название, Студенты.Фамилия, Дисциплины.Название, Успеваемость.Оценка
FROM (Группы INNER JOIN Студенты ON Группы.КодГруппы = Студенты.КодГруппы) INNER JOIN (Дисциплины INNER JOIN Успеваемость ON Дисциплины.КодДисциплины = Успеваемость.КодДисциплины) ON Студенты.КодСтудента = Успеваемость.КодСтудента
WHERE ((Студенты.Фамилия)=[Введите фамилию])
ORDER BY Студенты.Фамилия;

Эта аннотация состоит из 4 предложений «SELECT. . .», «FROM. . .», «WHERE…» и «ORDER BY…».
Первое предложение содержит оператор SELECT и идентификатор «Группы.Название, Студенты.Фамилия, Дисциплины.Название, Успеваемость.Оценка». Выбор данных осуществляется из 4 полей 4 таблиц. Чтобы однозначно идентифицировать поля в запросе, перед именами полей указаны имена таблиц (например, Группы.Название).

Второе предложение содержит оператор FROM и идентификатор «(Группы INNER JOIN Студенты ON Группы.КодГруппы=Студенты.КодГруппы) INNER JOIN (Дисциплины INNER JOIN Успеваемость ON Дисциплины.КодДисциплины=Успеваемость.КодДисциплины) ON Студенты.КодСтудента=Успеваемость.КодСтудента».

FROM — описывает таблицы «Группы», «Студенты», «Дисциплины» и «Успеваемость», которые содержат поля, указанные в предложении SELECT, и обеспечивает взаимосвязи таблиц через главные поля таблиц с помощью конструкции INNER JOIN … ON. Следует отметить, что операторы: SELECT и FROM постоянно находятся в запросах на выборку.

Третье предложение содержит оператор WHERE и идентификатор «((Студенты.Фамилия)=[Введите фамилию])», определяющий условия отбора.
Четвертое предложение содержит оператор ORDER BY и идентификатор «Студенты.Фамилия». ORDER BY не является неотклонимым оператором и применяется для сортировки выходных данных.

На рисунке 1 представлен скриншот параметрического SQL запроса на подборку данных, сохраненный с именованием «Запрос_с_параметрами».

Параметрический запрос в access. Как сделать параметрический запрос в access 2010?
Рис. 1. Параметрический SQL запрос на подборку данных

В итоге выполнения команды «Сохранить» в «Области переходов» покажется объект — «Запросы: Запрос_с_параметрами».

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

Параметрический запрос в access. Как сделать параметрический запрос в access 2010?
Рис. 2. Параметрический SQL запрос в режиме «Выполнить»

Для выполнения запроса введем имя параметра отбора (Воронина) в диалоговое окно «Введите значение параметра» и щелкнем на кнопочке «OK».

Результаты выполнения команды «Выполнить» представлены на рис. 3.

Параметрический запрос в access. Как сделать параметрический запрос в access 2010?
Рис. 3. Подборка данных на запрос с именованием параметра отбора — Воронина

Скачать sql_training_st1_param.mdb

Запросы с параметрами

Запрос в Access является объектом, который сохраняется в файле базы данных и может многократно повторяться. Все запросы, которые мы показывали до сих пор, содержали определенные значения дат, заглавий, имен и т. д. Ежели требуется повторить таковой запрос с иными значениями в критериях отбора, его необходимо открыть в режиме Конструктора, поменять условие и выполнить. Чтоб не делать многократно этих операций, можно сделать запрос с параметрами. При выполнении такового запроса выдается диалоговое окно Введите значение параметра (Enter Parameter Value), в котором юзер может ввести конкретное значение и потом получить подходящий результат.

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

  1. Откройте данный запрос в режиме Конструктора.
  2. Чтобы найти параметр запроса, введите в строчку Условие отбора (Criteria) для столбца "Название" (CompanyName) заместо определенного значения слово либо фразу и заключите их в квадратные скобки, к примеру [Поставщик:]. Эта фраза будет выдаваться в виде приглашения в диалоговом окне при выполнении запроса.
  3. Если вы желаете, чтоб Access инспектировала данные, вводимые в качестве параметра запроса, необходимо указать тип данных для этого параметра. Традиционно в этом нет необходимости при работе с текстовыми полями, т.к. по умолчанию параметру присваивается тип данных Текстовый (Text). Ежели же данные в поле запроса представляют собой даты либо числа, рекомендуется тип данных для параметра определять. Для этого щелкните правой клавишей мыши на вольном поле в верхней части запроса и выберите из контекстного меню команду Характеристики (Parameters) либо выполните команду меню Запрос, Характеристики (Query, Parameters). Возникает диалоговое окно Характеристики запроса (Query Parameters), представленное на рис. 4.31.
Читайте также  Включается и отключается монитор. Монитор включается на 2 секунды и гаснет: что делать?

Параметрический запрос в access. Как сделать параметрический запрос в access 2010?

Рис. 4.31. Диалоговое окно Характеристики запроса

  1. В столбец Параметр (Parameter) необходимо ввести заглавие параметра точно так, как он определен в бланке запроса (легче всего это сделать методом копирования через буфер обмена), лишь можно не вводить квадратные скобки. В столбце Тип данных (Data Type) выберите из раскрывающегося перечня нужный тип данных. Нажмите клавишу ОК.
  2. Нажмите клавишу Пуск (Run) на панели инструментов, чтоб выполнить запрос. При выполнении запроса возникает диалоговое окно Введите значение параметра (Input Parameter Value) (рис. 4.32), в которое необходимо ввести значение, к примеру Tokyo Traders. Итог выполнения запроса представлен на рис. 4.33. В него попадают лишь те продукты, которые поставляются данным поставщиком.

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

Параметрический запрос в access. Как сделать параметрический запрос в access 2010?

Рис. 4.32. Диалоговое окно Введите значение параметра

Параметрический запрос в access. Как сделать параметрический запрос в access 2010?

Рис. 4.33. Итог выполнения запроса с параметром

Совет

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

MS Access. Работа с данными при помощи запросов

Понятие запроса. Главные типы запросов

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

Вычисления в запросах

Параметрические запросы

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

Запрос на изменение

Запросы в СУБД Access

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

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

  • запросы по образцу, либо QBE-запросы (Query By Example), при разработке которых нужно указать характеристики запроса в окне конструктора, задавая эталоны для поиска информации;
  • структурированные запросы, либо SQL-запросы (Structured Query Language), для сотворения которых нужно обрисовать запрос с помощью языка запросов SQL.

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

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

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

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

Для сотворения запросов по эталону употребляется режим конструктора. Опосля определения таблиц (или запросов), на базе которых будет создаваться новейший запрос, он будет открыт в режиме конструктора. Окно конструктора запросов состоит из 2-ух частей.

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

Каждая строчка бланка запроса выполняет определенную функцию:

  • строка Поле исползуется для выбора полей;
  • строка Имя таблицы дозволяет найти, какой таблице принадлежит поле;
  • в строке Сортировка указывается тип сортировки записей;
  • строка Вывод на экран дозволяет задать те поля, которые должны быть выведены в динамической таблице;
  • строка Условие отбора употребляется для ввода условия выбора данных.

Первым шагом построения запроса является выбор полей. Необходимые поля можно выбрать несколькими способами:

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

Время от времени требуется включить в запрос все поля начальной таблицы. Для этого нужно сделать двойной щелчок мышыо по строке заголовка соответственного перечня полей, выделяя таковым образом сходу все поля, и перенести их сразу в бланк запроса. При этом каждое поле будет помещено в отдельный столбец. Можно пользоваться иным методом. В начале каждого перечня полей, приведенных в верхней полонине окна, находится знак *, который значит «все поля». Для того чтоб включить в запрос все поля таблицы, можно просто перенести * в бланк запроса. В этом случае имя поля в бланке запроса будет содержать имя таблицы, за которым следует точка, а потом — знак * (например, Заказы.*), что значит выбор всех полей начальной таблицы.

Читайте также  Создаем электронную почту на gmail.com

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

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

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

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

Выражение, которое показывает, какие записи нужно включить в динамическую таблицу при выполнении запроса вводится в строчку Условие отбора для поля, по которому это условие нужно проверить. Для задания критерий отбора можно применять операторы меньше (<), меньше либо равно (<=), больше (>), больше либо равно (>=), не равно (<>), равно (=), Like (выбор по маске), Between (между), In (в интервале), And (и), Or (или) и остальные, а также имена обьектов, константы и функции.

Завершение ввода условия выполняется нажатием клавиши Enter либо просто переходом к иной ячейке бланка запроса с помощью кнопок управления курсором либо мыши. Мicrosoft Access проводит синтаксический анализ данного выражения. К примеру, ежели было введено значение текстового поля то это выражение будет представлено в кавычках. Ежели выражение не содержит никакого оператора, Microsoft Access будет исходить из того, что предполагается оператор = либо Like.

Допускается внедрение пары критерий отбора, которые можно задать как для различных полей, так и для 1-го поля. Для сотворения сложных критерий выбора данных употребляются логические операторы And и Or. Ежели условия отбора соединены оператором And, запись выбирается лишь в случае выполнения всех критерий. Ежели же условия отбора соединены оператором Or, запись выбирается при выполнении хотя бы 1-го из всех критерий. При определении пары критерий отбора, связанных оператором And, для разных полей нужно просто задать условие в строке Условие отбора для каждого из полей, образующих аспект выбора данных. Ежели же при определении пары критерий поместить их в разные строчки — строчку Условие отбора и строчку или — Microsoft Acces будет употреблять Or-связь. В итоге условия, расположенные в одной строке, связываются оператором And, в различных строчках — оператором Or.

Т.о., при формировании условия отбора в запросах могут использоваться:

Подстановочные символы:

Соответствует хоть какому количеству символов. Может употребляться в качестве первого либо крайнего знака текстовой строки wh* — поиск слов what, white и why.
Соответствует хоть какому текстовому знаку B?ll — поиск слов Ball, Bell и Bill.
Соответствует хоть какому одному знаку из заключенных в скобки. B[ae]ll — поиск слов Ball и Bell, но не Bill.
Соответствует хоть какому одному знаку, не считая заключенных в скобки. b[!ae]ll — поиск слов bill и bull, но не bell либо ball.
Соответствует хоть какому знаку из спектра. Нужно указывать этот спектр по возрастанию (от A до Z, но не от Z до A). b[a-c]d — поиск слов bad, bbd и bcd.

Операторы, используемые в выражениях

Арифметические

целая часть от деления с остатком

Логические

"Минск" Or "Брест"

Сравнения

between #02.02.04# and #05.02.05#

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

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

Вычисления в запросе

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

Для выполнения вычислений в запросе нужно в бланке запроса сделать новое вычисляемое поле. Для этого следует в пустую ячейку строчки Поле ввести вычисляемое выражение, в котором могут употребляться имена полей таблиц, константы и функции, связанные с помощью арифметических операторов. Для конфигурации порядка вычислений и группировки данных в выражениях употребляются круглые скобки, а имена полей таблицы должны быть заключены в квадратные скобки. Ежели в запросе употребляется несколько таблиц, во избежание ошибок следует указывать полное имя поля, помещая перед именованием поля имя таблицы. В этом случае для разделения имен объектов используется !. Таковым образом, запись полного имени поля таблицы смотрится последующим образом: [Имя таблицы]![Имя поля]. Имя вычисляемого поля выводится перед выражением и отделяется от него двоеточием. Например,

Выражение1: [Цена]*[Количество], где Цена и Количество — имена полей.

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

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

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

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

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

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

  • групповая функция Sum вычисляет сумму всех значений данного поля в каждой группе;
  • групповая функция Avg вычисляет среднее арифметическое всех значений данного поля в каждой группе;
  • групповая функция Min (Max) возвращает меньшее (наибольшее) значение, отысканное в этом поле в каждой группе;
  • групповая функция Count описывает количество записей в каждой группе и др.

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

Параметрические запросы

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

Дополнительно, выполнив команду Запрос / Параметры, в диалоговом окне Параметры запроса можно перечислить имена всех характеристик, используемых в запросе, и для каждого параметра найти тип данных. Следует иметь в виду, что текст, введенный в качестве имени параметра в бланке запрос должен в точности соответствовать тексту, введенному в диалоговое окно Параметры запроса при определении типа данных характеристик. В неприятном случае Microsoft Access принимает их как два различных параметра. При удалении параметра из бланка запроса нужно удалить его и из диалогового окна Параметры запроса.

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

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

Оставьте комментарий

Adblock
detector