Создание запросов в БД
Запрос строится на основе одной или нескольких взаимосвязанных таблиц, позволяя комбинировать содержащуюся в них информацию. При этом могут использоваться как таблицы базы данных, так и сохраненные таблицы, полученные в результате выполнения других запросов. Кроме того, запрос может строиться непосредственно на другом запросе с использованием его временной таблицы с результатами. Запрос QBE содержит схему данных, включающую используемые таблицы и бланк запроса. При конструировании запроса достаточно выделить и перетащить с помощью мыши необходимые поля из таблиц, представленных в схеме данных запроса, в бланк запроса и ввести условия отбора записей.
Назначение и виды запросов
Запрос позволяет выбрать необходимые данные из одной или нескольких взаимосвязанных таблиц, произвести вычисления и получить результат в виде виртуальной таблицы. Полученная таблица может использоваться в качестве источника данных в следующих запросах, формах, отчетах, страницах доступа к данным. Через запрос можно производить обновление данных в таблицах, добавление и удаление записей.
С помощью запроса можно выполнить следующие виды обработки данных:
- выбрать записи, удовлетворяющие условиям отбора;
- включить в результирующую таблицу запроса заданные пользователем поля;
- произвести вычисления в каждой из полученных записей;
- сгруппировать записи с одинаковыми значениями в одном или нескольких полях в одну запись с одновременным выполнением над другими полями групповых функций;
- произвести обновление полей в выбранном подмножестве записей;
- создать новую таблицу базы данных, используя данные из существующих таблиц;
- удалить выбранное подмножество записей из таблицы базы данных; добавить выбранное подмножество записей в другую таблицу.
Многотабличный запрос позволяет сформировать записи результата путем объединения взаимосвязанных записей из таблиц БД и включения нужных полей из нескольких таблиц. В частности, при объединении двух нормализованных связанных одно-многозначными отношениями таблиц результирующая запись образуется на основе записи подчиненной таблицы, в которую добавляются поля из связанной записи в главной таблице. Заметим, что подобное объединение формирует новую таблицу, которая не является нормализованной. Выбранный тип объединения таблиц задается при установлении связи между таблицами и определяет способ формирования записей запроса. По умолчанию связи устанавливаются с параметром объединения первого типа: объединение только тех записей, в которых значения связанных полей обеих таблиц совпадают.
Последовательное выполнение ряда запросов по образцу позволяет решать достаточно сложные задачи, не прибегая к программированию.
В Access может быть создано несколько видов запроса.
- Запрос на выборку — выбирает данные из взаимосвязанных таблиц и других запросов. Результатом его является таблица, которая существует до закрытия запроса. На основе этого вида запроса могут строиться запросы других видов.
- Запрос на создание таблицы — также выбирает данные из взаимосвязанных таблиц и других запросов, но, в отличие от запроса на выборку, сохраняет результат в новой постоянной таблице.
- Запросы на обновление, добавление, удаление — являются запросами действия, в результате выполнения которых изменяются данные в таблицах.
Создание запроса
Лучшим способом создания запроса является использование графического конструктора — одного из наиболее мощных средств Access. Основные принципы конструирования различных запросов заложены в технике конструирования запроса на выборку, являющегося основой всех видов запроса.
Запрос на выборку позволяет достаточно просто выбрать данные из одной или нескольких взаимосвязанных таблиц. Результаты выполнения запроса отображаются в виде временной таблицы, существующей до закрытия запроса. Поля, составляющие записи этой таблицы, указываются пользователем в запросе. Записи таблицы результатов запроса формируются на основе записей в исходных таблицах и связей между этими таблицами и фильтруются в соответствии с заданными в запросе условиями отбора.
Таблица результатов запроса может применяться при дальнейшей обработке данных. В запросе на выборку могут использоваться не только таблицы базы данных, но и ранее созданные запросы, а вернее таблицы, являющиеся результатом их выполнения. При этом нет необходимости сохранять таблицы, получаемые в результате выполнения ранее созданных запросов.
Однако в ряде случаев непосредственное использование в запросе другого запроса невозможно. Тогда необходимо преобразовать включаемый запрос в запрос на создание таблицы. Этот запрос, в отличие от запроса на выборку, сохраняет результат в новой таблице БД, после чего эта таблица может включаться в состав таблиц для построения запроса.
Результаты выполнения запроса выводятся в режиме таблицы. Окно запроса в режиме таблицы аналогично окну просмотра таблицы базы данных. В этом режиме становится активной панель инструментов Запрос в режиме таблицы.
Таблица в режиме таблицы
Несмотря на то, что поля результирующей таблицы принадлежат, как правило, нескольким таблицам базы данных, с ними можно работать так, как если бы они принадлежали одной таблице. Можно изменить данные в таблице результатов запроса на выборку, и сделанные изменения будут внесены в базовые таблицы. Особенно важно, что, несмотря на дублируемость данных, возникающую в результате объединения записей таблиц, изменение одного данного в таблице запроса автоматически приводит к изменению всех повторяющихся в таблице запроса значений. Это определяется тем, что через таблицу запроса меняется значение в исходной таблице, где оно представлено один раз.
Для выполнения необходимых действий при создании запросов используются команды меню или панель инструментов Конструктор запросов. Окно конструктора запросов разделено на две панели. Верхняя панель содержит схему данных запроса, которая включает выбранные поля данного запроса таблицы. Таблицы представлены списками полей. Нижняя панель является бланком запроса по образцу – QBE, который необходимо заполнить.
Схема данных запроса
В окне конструктора запроса отображаются выбранные таблицы со списком полей и одно-многозначные связи между ними, имеющиеся в схеме БД. Первая строка в списке полей, отмеченная звездочкой (*), обозначает все множество полей таблицы. Кроме того, если таблицы имеют поля с одинаковыми именами и типами данных, Access автоматически устанавливает связи для объединения таких таблиц, даже когда связи не были установлены в схеме данных. Пользователь может самостоятельно установить не установленные автоматически связи, переместив с помощью мыши, задействованные в связи поля из одного списка полей в другой.
При использовании в запросе других запросов или таблиц, не представленных в схеме данных базы, с ними также могут быть установлены связи-объединения.
Бланк запроса по образцу
Бланк запроса по образцу представлен в виде таблицы на нижней панели окна запроса. Такая таблица предназначена для конструирования структуры таблицы результата запроса и условий выборки данных из исходных таблиц. Первоначально эта таблица пуста.
Каждый столбец бланка относится к одному запрашиваемому полю. Поля могут использоваться для включения в таблицу запроса, для задания сортировки, для задания условий отбора записей, а также для выполнения вычислений в записях таблицы.
При заполнении бланка запроса:
- в строку Поле включаются имена используемых в запросе полей;
- в раскрывающемся списке Сортировка выбирается порядок сортировки записей результата;
- в строке Вывод на экран устанавливаются флажки для полей, которые должны быть включены в результирующую таблицу;
- в строке Условие отбора задаются условия отбора записей;
- в строке или задаются альтернативные условия отбора записей.
В ряде случаев в бланке запроса наряду с именем поля необходимо отображать имя соответствующей таблицы, например, когда поля имеют одинаковые имена в разных таблицах. Для отображения имен таблиц в строке бланка следует выбрать команду Вид | Имена таблиц или нажать соответствующую кнопку на панели конструктора запросов. В результате выполнения команды в бланке появится строка Имя таблицы.
Поля бланка запроса
Каждый столбец бланка запроса соответствует одному из полей таблиц, на которых строится запрос. Кроме того, здесь может размещаться вычисляемое поле, значение которого вычисляется на основе значений других полей записи результата, или итоговое поле для групп записей, использующее одну из встроенных групповых функций Access. Для включения требуемых полей таблиц в соответствующие столбцы запроса можно воспользоваться следующими приемами:
- в первой строке бланка запроса Поле щелчком мыши вызвать появление кнопки списка и выбрать из списка нужное поле. Список содержит все поля таблиц, представленных в бланке запроса;
- переместить с помощью мыши требуемое поле из списка полей таблицы в схеме данных запроса в первую строку бланка запроса;
- дважды щелкнуть на имени поля таблицы в схеме данных запроса;
- для включения в запрос всех полей таблицы можно переместить с помощью мыши в соответствующую строку бланка запроса все поля из списка полей таблицы в схеме данных запроса или дважды щелкнуть на символе * (звездочка) в этом списке.
Модификация запроса
Добавление таблицы в схему данных запроса осуществляется с помощью команды меню Запрос | Добавить таблицу или нажатием соответствующей кнопки панели Конструктор запросов. Команда добавления может быть выполнена также через контекстное меню, вызываемое в режиме схемы данных запроса.
Добавление поля в бланк запроса осуществляется посредством одного из рассмотренных выше действий, например, перемещением с помощью мыши имени поля из таблицы в схеме данных в нужное место бланка. Все столбцы полей справа от него передвинутся на один столбец вправо.
Удаление поля в бланке запроса требует предварительного выделения соответствующего столбца. Для этого следует переместить курсор в область маркировки столбца, где он примет вид направленной вниз черной стрелки, и щелкнуть кнопкой мыши. Далее нажмите клавишу или выберите пункт меню Правка | Удалить столбцы.
Для перемещения поля в бланке запроса выделите с помощью мыши соответствующий столбец и переместите его на новую позицию. Столбец, на место которого перемещен новый, и все столбцы справа от него будут сдвинуты вправо.
Источник
Формирование запросов в СУБД Access
Возможности, типы и способы создания запросов
Запрос – это важнейший инструмент для извлечения информации из одной или нескольких таблиц БД. Посредством запроса можно вносить изменения в саму БД. Запрос может служить источником данных для форм, отчетов и страниц доступа к данным. Его результатом является новая таблица, которая может быть просмотрена, проанализирована, а затем сохранена или не сохранена.
Запросы позволяют решать многие задачи, не прибегая к программированию. Например, представлять данные в агрегированном виде, производить вычисления над полями БД, группировать записи и находить для полей итоговые значения с помощью статистических функций: Sum, Avg (соответственно сумма, среднее значений поля); Max, Min (соответственно максимальное, минимальное значение поля); Count (число значений поля) и др.
СУБД Access позволяет создавать запросы трех типов: запросы выбора, перекрестные запросы, запросы действия.
Запрос выбора является наиболее часто используемым типом запроса. Он дает возможность: выбирать записи, удовлетворяющие условиям отбора; включать в результирующую таблицу поля из одной или нескольких таблиц в нужном порядке; осуществлять вычисления над полями БД; выполнять статистические расчеты для групп записей. Разновидностью запроса выбора является запрос с параметрами — это запрос, при выполнении отображающий в собственном диалоговом окне приглашение ввести интересующее пользователя значение критерия отбора записей.
Перекрестный запрос представляет собой специальный запрос итогового типа. Он отображает результаты итоговых статистических расчетов над значениями некоторого поля в виде перекрестной таблицы. В ней значения одного или нескольких столбцов слева образуют заголовки строк, верхняя строка – заголовки столбцов из значений определенного поля, а на пересечении строк и столбцов – итоговые значения.
Запрос действия – это запрос, который вносит изменения в саму БД. Существует четыре типа запросов действия:
1) запрос на удаление — удаляет группу записей из одной таблицы или нескольких взаимосвязанных таблиц БД, для которых задано каскадное удаление связанных записей;
2) запрос на обновление — служит для изменения информации в полях таблицы БД;
3) запрос на добавление — производит добавление записей из таблицы с результатами запроса в таблицу БД;
4) запрос на создание таблицы — создает новую таблицу на основе всех или части данных из одной или нескольких таблиц БД. Этот запрос полезен в случае: создания таблицы для экспорта в другую БД Access; создания страниц доступа к данным, отображающих данные соответственно указанному моменту времени; создания резервной копии таблицы; создания архивной таблицы, содержащей старые записи.
СУБД Access позволяет создавать запросы с помощью Мастеров и с помощью Конструктора. Мастера используются для создания следующих запросов:
ü простого запроса на выборку полей из источника запроса и подведение итогов;
ü перекрестного запроса;
ü запроса на поиск повторяющихся записей в таблице;
ü запроса на поиск записей, не имеющих подчиненных им записей в другой таблице.
С помощью Конструктора можно создать любой запрос выбора, перекрестный запрос, запрос действия.
Создание запроса выбора
Для создания запроса выбора с помощью Конструктора необходимо открыть окно Конструктора запроса, выполнив действия:
Окно БД объект Запросы [Создать]
окно Новый запрос выбрать Конструктор
окно Добавление таблицы выбрать таблицы-источники запроса
Окно Конструктора запроса имеет вид как на рис. 1.
Рис. 1. Окно Конструктора запроса
Оно разделено на две панели.
Верхняя панель содержит схему данных запроса. В ней представлены списки полей, выбранных в качестве источника запроса таблиц и запросов. Если ранее была создана связь между этими таблицами, то она показывается на схеме данных. В противном случае может отображаться связь, автоматически созданная системой Access. Пользователь может сам установить новую связь между таблицами.
Нижняя панель есть бланк запроса по образцу (QBE-запроса). Он представлен в виде таблицы, предназначенной для определения структуры результирующей таблицы запроса и задания условий отбора данных из таблиц. Каждый столбец бланка относится к одному полю таблицы. Строки бланка имеют следующее назначение:
ü Поле – указывает имена полей, участвующих в формировании запроса;
ü Имя таблицы — указывает имена таблиц, которым принадлежат эти поля;
ü Сортировка – дает возможность отсортировать записи в результирующей таблице запроса;
ü Вывод на экран – позволяет управлять отображением полей в этой таблице;
ü Условие отбора – служит для задания условий отбора записей;
ü или – позволяет объединять условия отбора логической операцией ИЛИ. При этом условия отбора могут указываться в нескольких строках бланка запроса.
Включение отдельных полей в бланк запроса можно выполнить одним из следующих способов:
ü перетащить поле из списка полей в крайнюю слева свободную клетку строки Поле;
ü дважды щелкнуть по имени поля в списке полей;
ü щелкнуть в клетке строки Поле и из раскрывающегося списка выбрать нужное поле.
Включение в бланк запроса всех полей таблицы можно выполнить, если:
ü дважды щелкнуть по имени таблицы и перетащить все выделенные поля в клетку строки Поле;
ü перетащить звездочку, стоящую под заголовком таблицы, в клетку строки Поле. При этом в клетке отобразится только имя таблицы со звездочкой, но в результат запроса будут включены все поля таблицы.
Для очистки отдельных столбцов бланка запроса используется команда
Правка / Удалить столбцы
Для очистки всего бланка запроса служит команда
Правка / Очистить бланк
Формирование запроса на вывод полей из одной или нескольких таблиц
Перетаскиваются из списков полей в строку Поле только те поля, которые должны присутствовать в результирующей таблице запроса, или все поля всех таблиц, а затем в сроке Вывод на экран отмечаются флажками только нужные.
Формирование запроса с применением сортировки
Сортировка возможна по одному или нескольким полям одновременно. Для сортировки по нескольким полям поля располагаются в бланке запроса в том порядке, в котором требуется выполнять сортировку. В Access записи сортируются сначала по самому левому полю, затем по полю, расположенному в следующем столбце справа и т.д. Для задания порядка сортировки необходимо щелкнуть в строке Сортировка для поля, по которому сортируются записи, и из раскрывающегося списка выбрать – по возрастанию или по убыванию.
Формирование запроса с условиями отбора
Условия отбора — это ограничения, накладываемые на запрос для определения записей, включаемых в результирующую таблицу запроса. Они задаются выражениями в строках Условие отбора, или.
Если выражения вводятся в несколько клеток одной строки Условие отбора, то они автоматически объединяются с помощью логического оператора And. Если выражения вводятся в разные строки бланка запроса, то Access объединяет их логическим оператором Or.
В условии отбора может быть использована конструкция Between (между). Например, Between 100 and 200.
Формирование запроса с вычисляемым полем
Для создания вычисляемого поля в пустую клетку строки Поле вводится имя вычисляемого поля с двоеточием, после которого – выражение. Например,
Если выражение сложное, то для его создания целесообразно использовать построитель выражений.
Формирование запроса с группировкой
Часто требуется в таблице видеть не все записи, а только итоговые значения по группам записей. Расчет итогов для некоторых полей групп производится с помощью статистических функций, которые были описаны выше.
Для создания запроса с группировкой выполняется следующее:
ü перетаскивается в первую клетку строки Поле то поле, по которому производится группировка записей. Затем перетаскиваются в последующие клетки поля, по которым подводятся итоги;
ü вводится команда Вид / Групповые операции.В бланке запроса появляется новая строка Групповая операция, в которой для всех полей указано Группировка;
ü в строке Групповая операция для полей, по которым подводятся итоги, производятся щелчки и из раскрывающегося списка выбирается требуемая статистическая функция.
Формирование запроса с параметрами
Если необходимо часто выполнять один и тот же запрос выбора, меняя только в условиях отбора значения полей, то целесообразно создать запрос с параметрами. В строках Условие отбора, или для полей, играющих роль параметров, вводится в квадратных скобках текст приглашения на ввод интересующих пользователя значений этих полей. Этот текст будет выводиться в диалоговом окне Введите значение параметра при выполнении запроса.
Источник