Запросы

Запрос — это правило, по которому СУБД выбирает данные из базы и вы­дает их пользователю. В терминах ре­ляционных баз данных запрос состоит из набора полей, т.е. тоже является за­писью. Результат запроса — это сово­купность записей, заносимых во вре­менную таблицу, которая не хранится постоянно в базе, а создается при вы­полнении запроса и удаляется после закрытия его пользователем.

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

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

Назначение запросов:

В Access различают следующие типы запросов:

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

Для разработки запросов в Access предусмотрен специальный Кон­структор запросов. Чтобы его запус­тить, в окне ба­зы данных надо пе­рейти на вкладку Запро­сы с нажатием кнопки Создать.

Возможны следующие способы соз­дания запроса:

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

Окно Конструктора запросов раз­деляется на две области. В первой (верхней) изображаются таблицы и их связи, а нижняя представляет собой бланк QBE (Query By Example, в переводе с англ. — за­прос no образцу). Бланк QBE — это таблица, в столб­цах которой располагаются поля, а в строках задается способ их использования в запросе.

Первая строка — имя поля, вторая строка — имя таблицы, в которой находится это поле. Третья строка задает способ сортировки запи­сей после выполнения запроса — это комбинированный список со следую­щими значениями: По возрастанию, По убыванию, Отсутствует. Четвер­тая строка — это флажок просмотра. Если он установлен, то значения поля будут отображаться при просмотре ре­зультатов запроса. Остальные строки задают правила выборки записей.

Сознание запроса с помощью мастера

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

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

  1. В окне Access откройте нужную базу данных, а затем в окне базы слева выберите категорию объектов «Запросы».

  2. В окне базы данных справа дважды щелкните на задаче «Создание запроса с помощью мастера».

  3. В раскрывающемся списке «Таблицы и запросы» укажите таблицу, из которой требуется выбрать поля.

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

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

  6. Когда все нужные поля будут добавлены в список «Выбранные поля», щелкните на кнопке «Далее».

Следующее окно мастера предложит указать, какой запрос требуется создать: подробный или итоговый. Подробный запрос включает все поля каждой записи. А итоговый запрос позволяет определить для числовых полей сумму (Sum), среднее (Avg), минимальное (Min) и максимальное (Max) значения. Этот выбор (подробный или итоговый запрос) будет предоставлен пользователю только в случае, если среди добавленных в запрос полей имеется хотя бы одно, принадлежащее числовому типу.

  1. Чтобы создать итоговый запрос с использованием одного из перечисленных вычислений, щелкните на кнопке Итоги в окне мастера. В результате появится диалоговое окно «Итоги», в котором будут представлены все включенные в запрос поля, содержащие числовые значения.

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

  3. Щелкните на кнопке «Далее» в окне мастера.

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

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

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

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

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

  1. В окне базы данных слева выберите категорию объектов «Запросы».

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

  3. В окне «Добавление таблицы» щелкните на таблице, содержащей поля, которые требуется использовать в запросе, а затем щелкните на кнопке «Добавить».

  4. Закройте окно «Добавление таблицы». В результате на экране на переднем плане появится окно конструктора запросов.

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

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

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

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

  • в строке Поле поставить курсор в нужный столбец и дважды щелкнуть имя поля в списке. Нажать клавишу стрелка вправо или клавишу Tab, переместить курсор в следующий столбец и добавки» новое поле в запрос;

  • щелкнуть на кнопке раскрытия списка около правой границы ячейки в строке Поле или нажать клавишу F4, чтобы открыть список имен полей и выбрать требуемое поле;

  • перетащить имя ноля мышью из списка полей в верхней части окна.

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

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

Если вы хотите использовать текстовой критерий отбора, то введите необходимый текст в строку Условие отбора. После нажатия клавиши Enter слово будет помещено в кавычки. Для каждого поля, которое предполагается использовать как параметр, то надо ввести в ячейку строки Условие отбора текст приглашения, заключенный в квадратные скобки. Это приглашение будет выводиться при запуске запроса. Текст приглашения должен отличаться от имени поля, но может включать его.

Запуск запроса. В режиме конструктора запрос можно запустить нажав кнопку Запуск. Результаты ответа на запрос будут представлены в таблице. Чтобы прервать запуск запроса, нажмите клавиши Ctrl+Break.

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

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

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

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

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

Особенности выполнения групповых операций.

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

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

  • выбирается команда Групповые операции или нажимается одноименная кнопка на панели инструментов;

  • устанавливается курсор в поле, над значениями которого будут выполняться вычисления, и выбирается в ячейке Групповая операция требуемая функция;

  • выбирается команда Запуск.

С помощью запроса могут быть подсчитаны сумма (Sum) и среднее арифметическое (Avg), найдены минимальное (Min) и максимальное (Мах) значения в поле. Закончив работу с запросом, можно сохранить его под каким-нибудь именем.

Для расширения условий отбора можно использовать строку или (or) в бланке запроса. Например, вы хотите узнать объем продаж дорогих (дороже 2500 руб.) и дешевых (меньше 500 руб.) заказов. Для этого вводится в ячейку Условия отбора >2500.  в ячейку или <500.  Условия, заданные в ячейке или, будут восприниматься как дополнительные.

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

Например, если каждый заказ стоит 5 рублей, а вы хотите вычислить стоимость всех заказов, сделанных каждым покупателем, то введите в ячейку Поле выражение: Стоимость:[КоличествоЗаказов]*5.

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

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

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

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

  • запрос-объединение —  позволяет объединить поля из нескольких таблиц или запросов в один набор данных;

  • запрос к серверу — передает инструкции SQL удаленной базе данных;

  • управляющий запрос — создает, изменяет или удаляет таблицы или индексы базы данных Access.

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

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

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

Окно запроса может также находиться в режиме SQL, который используют для ввода или просмотра  инструкций SQL при создании или изменении запроса. При создании запроса в режиме  конструктора Access автоматически создает в режиме SQL эквивалентную инструкцию SQL.

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

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

Как правило, запросы с параметром создаются в тех случаях, когда предполагается выполнять этот запрос многократно, изменяя лишь условия отбора. В отличие от запроса на выборку, где для каждого условия отбора создается свой запрос, а также все эти запросы хранятся в БД, параметрический запрос позволяет создать и хранить один единственный запрос. При этом можно вводить условие отбора (значение параметра) при запуске этого запроса, каждый раз получая новый результат. В качестве параметра может быть любой текст, смысл которого определяет значение данных, которые будут выведены в запросе. Значение параметра задается в специальном диалоговом окне. В случае, когда значение выводимых данных должно быть больше или меньше указываемого значения параметра, в поле «Условие отбора» бланка запроса перед параметром, заключенным в квадратные скобки ставится соответствующий знак. Можно также создавать запрос с несколькими параметрами, которые связанны друг с другом логическими операциями И/ИЛИ. В момент запуска на выполнение MS Access отобразит на экране диалоговое окно для каждого из параметров. Помимо определения параметра в бланке запроса, необходимо указать с помощью команды Запрос/Параметры соответствующий ему тип данных:

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

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

  3. Выберите команду Запрос/Параметры.

  4. В появившемся окне Параметры запроса введите без квадратных скобок параметр (для точности ввода воспользуйтесь «быстрыми» клавишами копирования и вставки из буфера обмена) и укажите соответствующий ему тип данных. Нажмите «OK».

  5. Нажмите кнопку «Запуск» панели инструментов.

  6. В появившемся окне укажите значение параметра.

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

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

Для создания перекрестного запроса вручную необходимо:

  1. Выбрать в окне «Новый запрос» пункт Конструктор.

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

  3. На панели инструментов нажать кнопку «Тип запроса» и выбрать «Перекрестный». В бланке запроса появятся две дополнительные строки: «Групповая операция» и «Перекрестная таблица».

  4. Добавить поля в строку «Поле» в бланке запроса и создать, если нужно, дополнительно вычисляемые поля. Все поля вначале будут получать в строке «Групповая операция» значение «Группировка».

  5. Для полей, значения которых будут использованы в качестве заголовков строк, нужно в строке «Перекрестная таблица» указать значение «Заголовки строк» и оставить в строке «Групповая операция» значение «Группировка».

  6. Для поля, значения которого будут использованы в качестве заголовков столбцов, нужно в строке «Перекрестная таблица» задать значение «Заголовки столбцов» и оставить в строке «Групповая операция» значение «Группировка».

  7. Для поля, значения которого будут использованы при создании перекрестной таблицы, нужно в строке «Перекрестная таблица» задать значение «Значения», а в строке «Групповая операция» выбрать групповую функцию, используемую для вычисления значений перекрестной таблицы.

  8. Чтобы отобрать нужные заголовки строк или столбцов, следует ввести выражение, задающее условие отбора в строку «Условие отбора» для соответствующих полей-заголовков в ячейке строки «Перекрестная таблица».

Если нужно исключить некоторые записи до вычисления значений перекрестной таблицы, то следует добавить в бланк запроса поле или поля, которые будут использованы для отбора нужных записей. Для создания условия отбора по данному полю, нужно выбрать в строке «Групповая операция» значение «Условие» и ввести условие в строку «Условие отбора». Ячейка в строке «Перекрестная таблица» должна быть пустой. Если при отборе записей запроса используются параметры, то для них обязательно должен быть указан  их тип.

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