MS Access. Работа с данными при помощи запросов
Понятие запроса. Основные типы запросов
Создание запросов в режиме конструктора
Вычисления в запросах
Параметрические запросы
Перекрестный запрос
Запрос на изменение
Запросы в СУБД Access
Запрос — объект базы данных, который используется для извлечения информации из одной или нескольких таблиц или для выполнения определенных действий с данными.
По способу формирования запросы можно разделить на два вида:
- запросы по образцу, или QBE-запросы (Query By Example), при создании которых необходимо указать параметры запроса в окне конструктора, задавая образцы для поиска информации;
- структурированные запросы, или SQL-запросы (Structured Query Language), для создания которых необходимо описать запрос с помощью языка запросов SQL.
В действительности любой запрос в Microsoft Access реализуется с помощью языка SQL. И хотя большинство запросов можно создавать в режиме конструктора, используя возможности запроса по образцу, каждый созданный запрос хранится в виде инструкции SQL. При создании запроса по образцу Microsoft Access автоматически формирует соответствующий SQL-запрос. Можно просмотреть инструкцию SQL для существующего запроса и внести в нее изменения. В этом случае автоматически будет обновляться определение соответствующего запроса по образцу в режиме конструктора.
По результатам действий и особенностям выполнения запросы можно разделить следующим образом:
- запросы на выборку, используемые для того, чтобы ото брать и представить в удобном виде данные из одной или нескольких таблиц (или запросов);
- параметрические запросы, которые позволяют задавать конкретные условия отбора непосредственно при выполнении запроса;
- перекрестные запросы, которые позволяют провести группировку и вычисления, а также представить данные в компактном виде, близком к сводной таблице Microsoft Excel;
- запросы на изменение, которые позволяют изменять таблицы базы данных: обновлять их, дополнять новыми записями, удалять некоторые записи. Они могут использоваться для создания новых реальных таблиц, которые,в отличие от обычных выборок, в дальнейшем существуют уже независимо от тех таблиц базы данных, которые были использованы для их построения.
Создание запросов в режиме конструктора
Для создания запросов по образцу используется режим конструктора. После определения таблиц (или запросов), на основе которых будет создаваться новый запрос, он будет открыт в режиме конструктора. Окно конструктора запросов состоит из двух частей.
В верхней части окна находятся списки полей тex таблиц или запросов, на основе которых строится данный запрос, в нижней части окна располагается бланк запроса.
Каждая строка бланка запроса выполняет определенную функцию:
- строка Поле исползуется для выбора полей;
- строка Имя таблицы позволяет определить, какой таблице принадлежит поле;
- в строке Сортировка указывается тип сортировки записей;
- строка Вывод на экран позволяет задать те поля, которые должны быть выведены в динамической таблице;
- строка Условие отбора используется для ввода условия выбора данных.
Первым шагом построения запроса является выбор полей. Необходимые поля можно выбрать несколькими способами:
- перенести мышью поле из списка полей в верхней части окна конструктора в нужный столбец бланка запроса;
- выбрать нужное поле двойным щелчком мыши в списке полей соответствующей таблицы (при этом оно будет помещено в следующий свободный столбец бланка запроса);
- выбрать нужное поле из раскрывающегося списка строки Поле.
Иногда требуется включить в запрос все поля исходной таблицы. Для этого необходимо сделать двойной щелчок мышыо по строке заголовка соответствующего списка полей, выделяя таким образом сразу все поля, и перенести их одновременно в бланк запроса. При этом каждое поле будет помещено в отдельный столбец. Можно воспользоваться другим способом. В начале каждого списка полей, приведенных в верхней полонине окна, находится символ *, который означает «все поля». Для того чтобы включить в запрос все поля таблицы, можно просто перенести * в бланк запроса. В этом случае имя поля в бланке запроса будет содержать имя таблицы, за которым следует точка, а затем — символ * (например, Заказы.*), что означает выбор всех полей исходной таблицы.
У метода переноса * есть одно существенное достоинство. Если в структуре исходной таблицы производятся какие-либо изменения, например, добавляется новое поле, запрос будет автоматически изменен (при использовании первого способа запрос выбирает только те поля, которые были перенесены в бланк запроса. Однако если необходимо определить условие отбора, придется дополнительно добавить отдельные поля в бланк запроса, а чтобы они дважды не включались в выберу снять для соответствующих полей флажки Вывод на экран.
При выполнении запроса в качестве заголовков столбца динамической таблицы используются имена выбранных полей. Для переименования поля в результирующей динамической таблице необходимо установить текстовый курсор перед именем поля в строке Поле бланка запроса и ввести новое имя с двоеточием. В результате получится следующая конструкция: Новое имя: Старое имя поля.
Обычно Microsoft Access выводит записи в том порядке, в каком они выбираются из базы данных. Можно изменить последовательность вывода данных, определив ее порядок в строке Сортировка. При сортировке по нескольким полям порядок обработки полей определяется их положением в бланке запроса: сначала сортируются значения в крайнем левом поле и далее слева направо.
По умолчанию Microsoft Access выводит все поля, вклкюченные в бланк запроса. Если поле используется только для оп ределения условия выбора данных, для него необходимо снять флажок в строке Вывод на экран, щелкнув мышью в соотвествующей ячейке.
Выражение, которое указывает, какие записи необходимо включить в динамическую таблицу при выполнении запроса вводится в строку Условие отбора для поля, по которому это условие необходимо проверить. Для задания условий отбора можно использовать операторы меньше ( ), больше или равно (>=), не равно (<>), равно (=), Like (выбор по маске), Between (между), In (в интервале), And (и), Or (или) и другие, а также имена обьектов, константы и функции.
Завершение ввода условия выполняется нажатием клавиши Enter или просто переходом к другой ячейке бланка запроса с помощью клавиш управления курсором или мыши. Мicrosoft Access проводит синтаксический анализ заданного выражения. Например, если было введено значение текстового поля то это выражение будет представлено в кавычках. Если выражение не содержит никакого оператора, Microsoft Access будет исходить из того, что подразумевается оператор = или Like.
Допускается использование нескольких условий отбора, которые можно задать как для разных полей, так и для одного поля. Для создания сложных условий выбора данных используются логические операторы And и Or. Если условия отбора связаны оператором And, запись выбирается только в случае выполнения всех условий. Если же условия отбора связаны оператором Or, запись выбирается при выполнении хотя бы одного из всех условий. При определении нескольких условий отбора, связанных оператором And, для различных полей необходимо просто задать условие в строке Условие отбора для каждого из полей, образующих критерий выбора данных. Если же при определении нескольких условий поместить их в различные строки — строку Условие отбора и строку или — Microsoft Acces будет использовать Or-связь. В результате условия, расположенные в одной строке, связываются оператором And, в разных строках — оператором Or.
Т.о., при формировании условия отбора в запросах могут использоваться:
Подстановочные символы:
Источник
Способы создания таблиц запросов
7. Формирование запросов в СУБД Access
7.1. Возможности , типы и способы создания запросов
Запрос – это важнейший инструмент для извлечения информации из одной или нескольких таблиц БД. Посредством запроса можно вносить изменения в саму БД. Запрос может служить источником данных для форм, отчетов и страниц доступа к данным. Его результатом является новая таблица , которая может быть просмотрена, проанализирована, а затем сохранена или не сохранена.
Запросы позволяют решать многие задачи, не прибегая к программированию. Например, представлять данные в агрегированном виде, производить вычисления над полями БД, группировать записи и находить для полей итоговые значения с помощью статистических функций: Sum , Avg ( соответственно сумма, среднее значений поля); Max , Min (соответственно максимальное, минимальное значение поля); Count (число значений поля) и др.
СУБД Access позволяет создавать запросы трех типов: запросы выбора, перекрестные запросы, запросы действия.
Запрос выбора является наиболее часто используемым типом запроса. Он дает возможность: выбирать записи, удовлетворяющие условиям отбора; включать в результирующую таблицу поля из одной или нескольких таблиц в нужном порядке; осуществлять вычисления над полями БД; выполнять статистические расчеты для групп записей. Разновидностью запроса выбора является запрос с параметрами — это запрос, при выполнении отображающий в собственном диалоговом окне приглашение ввести интересующее пользователя значение критерия отбора записей.
Перекрестный запрос представляет собой специальный запрос итогового типа. Он отображает результаты итоговых статистических расчетов над значениями некоторого поля в виде перекрестной таблицы. В ней значения одного или нескольких столбцов слева образуют заголовки строк, верхняя строка – заголовки столбцов из значений определенного поля, а на пересечении строк и столбцов – итоговые значения.
Запрос действия – это запрос , который вносит изменения в саму БД. Существует четыре типа запросов действия:
1) запрос на удаление — удаляет группу записей из одной таблицы или нескольких взаимосвязанных таблиц БД, для которых задано каскадное удаление связанных записей;
2) запрос на обновление — служит для изменения информации в полях таблицы БД;
3) запрос на добавление — производит добавление записей из таблицы с результатами запроса в таблицу БД;
4) запрос на создание таблицы — создает новую таблицу на основе всех или части данных из одной или нескольких таблиц БД. Этот запрос полезен в случае: создания таблицы для экспорта в другую БД Access; создания страниц доступа к данным, отображающих данные соответственно указанному моменту времени; создания резервной копии таблицы; создания архивной таблицы, содержащей старые записи.
СУБД Access позволяет создавать запросы с помощью Мастеров и с помощью Конструктора. Мастера используются для создания следующих запросов:
· простого запроса на выборку полей из источника запроса и подведение итогов;
· запроса на поиск повторяющихся записей в таблице;
· запроса на поиск записей, не имеющих подчиненных им записей в другой таблице.
С помощью Конструктора можно создать любой запрос выбора, перекрестный запрос, запрос действия.
7.2. Создание запроса выбора
Для создания запроса выбора с помощью Конструктора необходимо открыть окно Конструктора запроса, выполнив действия:
Окно БД Þ объект Запросы Þ [Создать] Þ
окно Новый запрос Þ выбрать Конструктор Þ
окно Добавление таблицы Þ выбрать таблицы-источники запроса Þ
Окно Конструктора запроса имеет вид как на рис. 1.
Рис. 1. Окно Конструктора запроса
Оно разделено на две панели.
Верхняя панель содержит схему данных запроса. В ней представлены списки полей, выбранных в качестве источника запроса таблиц и запросов. Если ранее была создана связь между этими таблицами, то она показывается на схеме данных. В противном случае может отображаться связь, автоматически созданная системой Access . Пользователь может сам установить новую связь между таблицами.
Нижняя панель есть бланк запроса по образцу ( QBE -запроса). Он представлен в виде таблицы, предназначенной для определения структуры результирующей таблицы запроса и задания условий отбора данных из таблиц. Каждый столбец бланка относится к одному полю таблицы. Строки бланка имеют следующее назначение:
· Поле – указывает имена полей , участвующих в формировании запроса;
· Имя таблицы — указывает имена таблиц, которым принадлежат эти поля;
· Сортировка – дает возможность отсортировать записи в результирующей таблице запроса;
· Вывод на экран – позволяет управлять отображением полей в этой таблице;
· Условие отбора – служит для задания условий отбора записей;
· или – позволяет объединять условия отбора логической операцией ИЛИ. При этом условия отбора могут указываться в нескольких строках бланка запроса.
Включение отдельных полей в бланк запроса можно выполнить одним из следующих способов:
· перетащить поле из списка полей в крайнюю слева свободную клетку строки Поле;
· дважды щелкнуть по имени поля в списке полей ;
· щелкнуть в клетке строки Поле и из раскрывающегося списка выбрать нужное поле.
Включение в бланк запроса всех полей таблицы можно выполнить, если:
· дважды щелкнуть по имени таблицы и перетащить все выделенные поля в клетку строки Поле ;
· перетащить звездочку , стоящую под заголовком таблицы, в клетку строки Поле . При этом в клетке отобразится только имя таблицы со звездочкой, но в результат запроса будут включены все поля таблицы.
Для очистки отдельных столбцов бланка запроса используется команда
Правка / Удалить столбцы
Для очистки всего бланка запроса служит команда
Правка / Очистить бланк
Формирование запроса на вывод полей из одной или нескольких таблиц
Перетаскиваются из списков полей в строку Поле только те поля, которые должны присутствовать в результирующей таблице запроса, или все поля всех таблиц, а затем в сроке Вывод на экран отмечаются флажками только нужные.
Формирование запроса с применением сортировки
Сортировка возможна по одному или нескольким полям одновременно. Для сортировки по нескольким полям поля располагаются в бланке запроса в том порядке, в котором требуется выполнять сортировку. В Access записи сортируются сначала по самому левому полю, затем по полю, расположенному в следующем столбце справа и т.д. Для задания порядка сортировки необходимо щелкнуть в строке Сортировка для поля, по которому сортируются записи, и из раскрывающегося списка выбрать – по возрастанию или по убыванию.
Формирование запроса с условиями отбора
Условия отбора — это ограничения, накладываемые на запрос для определения записей, включаемых в результирующую таблицу запроса. Они задаются выражениями в строках Условие отбора , или .
Если выражения вводятся в несколько клеток одной строки Условие отбора , то они автоматически объединяются с помощью логического оператора And. Если выражения вводятся в разные строки бланка запроса, то Access объединяет их логическим оператором Or.
В условии отбора может быть использована конструкция Between (между). Например , Between 100 and 200.
Формирование запроса с вычисляемым полем
Для создания вычисляемого поля в пустую клетку строки Поле вводится имя вычисляемого поля с двоеточием, после которого – выражение. Например,
Если выражение сложное, то для его создания целесообразно использовать построитель выражений.
Формирование запроса с группировкой
Часто требуется в таблице видеть не все записи, а только итоговые значения по группам записей. Расчет итогов для некоторых полей групп производится с помощью статистических функций, которые были описаны выше.
Для создания запроса с группировкой выполняется следующее:
· перетаскивается в первую клетку строки Поле то поле, по которому производится группировка записей. Затем перетаскиваются в последующие клетки поля, по которым подводятся итоги;
· вводится команда Вид / Групповые операции. В бланке запроса появляется новая строка Групповая операция , в которой для всех полей указано Группировка;
· в строке Групповая операция для полей, по которым подводятся итоги, производятся щелчки и из раскрывающегося списка выбирается требуемая статистическая функция.
Формирование запроса с параметрами
Если необходимо часто выполнять один и тот же запрос выбора, меняя только в условиях отбора значения полей, то целесообразно создать запрос с параметрами. В строках Условие отбора, или для полей, играющих роль параметров, вводится в квадратных скобках текст приглашения на ввод интересующих пользователя значений этих полей. Этот текст будет выводиться в диалоговом окне Введите значение параметра при выполнении запроса.
7. 3. Создание перекрестного запроса
Создание перекрестного запроса с помощью Конструктора начинается с открытия окна Конструктора запроса. В этом окне в бланк запроса последовательно перетаскиваются:
· поля, значения которых будут заголовками строк перекрестной таблицы;
· поле, значения которого будут заголовками столбцов перекрестной таблицы;
· поле, по которому подводится итог с использованием статистической функции.
Затем выполняется команда Запрос / Перекрестный. В результате в бланке запроса появятся две новые строки: Групповая операция и Перекрестная таблица. В строке Перекрестная таблица для полей со значениями в роли заголовков строк выбирается из раскрывающегося списка значение Заголовки строк, а для поля со значениями в роли заголовков столбцов – Заголовки столбцов. В строке Групповая операция для поля, по которому подводится итог, из раскрывающегося списка выбирается необходимая статистическая функция.
7.4. Создание запросов действия
Формирование запроса на создание таблицы БД
После открытия окна конструктора запроса вводится команда
Запрос / Создание таблицы
В появившемся окне Создание таблицы указывается имя создаваемой таблицы и куда ее следует поместить – в текущую БД или в другую БД. Затем из списков полей перетаскиваются в бланк запроса поля, которые должны быть в этой таблице, и при необходимости задаются условия отбора записей. После выполнения запроса новая таблица будет в списке таблиц окна БД.
Формирование запроса на обновление полей таблицы БД
После открытия окна конструктора запроса вводится команда
В бланке запроса появится новая строка Обновление. Затем в бланк запроса перетаскиваются все поля таблицы, и для полей, подлежащих обновлению, в строке Обновление задаются выражения, значения которых будут новыми значениями обновляемых полей. При необходимости обновления значений полей только в некоторых записях задаются условия отбора записей.
Формирование запроса на добавление записей к таблице БД
При открытии окна конструктора запроса в качестве источника запроса указывается таблица, из которой добавляются записи в другую таблицу. Записи таблицы-источника должны содержать такие же поля, что и пополняемая таблица БД. В окне конструктора запроса вводится команда Запрос / Добавление. Появится диалоговое окно Добавление, в котором требуется указать имя пополняемой таблицы и где эта таблица находится – в текущей БД или в другой БД. Кроме того, в бланке запроса появится новая строка Добавление.
Затем перетаскиваются те поля из списка полей таблицы-источника, которые совпадают с полями пополняемой таблицы. Их имена Access автоматически укажет в строке Добавление как имена полей пополняемой таблицы.
Формирование запроса на удаление записей из таблицы БД
Если между таблицами установлена связь с обеспечением целостности данных, но без каскадного удаления записей, то прежде составляется запрос на удаление записей из подчиненной таблицы, а затем – из главной.
В этом случае при удалении записей из подчиненной таблицы в окне конструктора запроса вводится команда Запрос / Удаление . В результате в бланке запроса появится новая строка Удаление. Тогда из списка полей подчиненной таблицы перетаскивается в бланк запроса символ звездочки (*), после чего в строке Удаление для этого поля отобразится значение Из. Затем из списка полей главной таблицы перетаскиваются поля, участвующие в условии отбора удаляемых записей, и для них в строке Удаление появится значение Условие. После этого задаются условия отбора удаляемых записей.
Если связь между таблицами с обеспечением целостности данных и с каскадным удалением записей, то создается запрос на удаление записей только из главной таблицы.
При составлении запроса на удаление записей из главной таблицы (в обоих случаях) в окне конструктора запроса вводится команда Запрос / Удаление.
В бланк за п роса перетаскиваются поля, участвующие в условиях отбора записей на удаление, и задаются условия отбора.
7.5. Выполнение и сохранение запроса
После формирования запроса его необходимо выполнить. Из окна Конструктора запроса это можно сделать, введя одну из команд:
Вид / Режим таблицы
Если результаты выполнения запроса не удовлетворяют, то можно вернуться к окну Конструктора запроса для его модификации по команде Вид / Конструктор . В противном случае результат запроса можно сохранить с помощью команды Файл / Сохранить или произведя закрытие окна Конструктора запроса.
7 . Формирование запросов в СУБД Access
Тренировочные задания
1. Создать многотабличный запрос на вывод из БД ДЕКАНАТ-БУХГАЛТЕРИЯ сведений о студентах следующей структуры:
В запросе записи рассортировать по алфавитному порядку ФИО.
2. Создать запрос на вывод из БД сведений о семейных студентах (которые женаты или замужем). Результирующая таблица запроса должна иметь следующую структуру:
3. Создать запрос на вывод из БД сведений о неуспевающих студентах (у которых оценка и по информатике, и по математике 2). Результирующая таблица запроса должна быть следующей структуры:
| НОМ_ЗАЧ | ФИО | ГРУП | ОЦ_ПО_ИНФ | ОЦ_ПО_МАТЕМ |
4. Создать параметрический запрос, дающий возможность выводить из БД сведения об успеваемости студентов любой группы. Результирующая таблица запроса должна быть следующей структуры:
| ГРУП | НОМ_ЗАЧ | ФИО | ОЦ_ПО_ИНФ | ОЦ_ПО_МАТЕМ |
5. Создать запрос, в котором рассчитать для каждого студента средний балл, полученный им в сессию. Результирующая таблица запроса должна быть следующей структуры:
| ГРУП | ФИО | ОЦ_ПО_МАТЕМ | ОЦ_ПО_ИНФ | СРЕДН_БАЛЛ |
6. Создать итоговый запрос, позволяющий для каждого студента рассчитать сумму всех начислений. Результирующая таблица запроса должна иметь следующую структуру:
7. Создать перекрестный запрос, в котором для каждой группы получить суммы начислений по их видам. Результирующая таблица запроса должна иметь следующую структуру:
| ГРУП | МАТ_ПОМ | НАДБАВ | ПРЕМИЯ | СТИП |
Запрос сохранить с именем К_ОТЧЕТУ.
8. Создать запрос действия , позволяющий в таблице УСПЕВАЕМОСТЬ обновить поле КУРС: для студентов, не имеющих оценок 2, увеличить его значение на единицу.
7. Формирование запросов в СУБД Access
1 . Каково назначение запросов выбора ?
2 . Когда создается параметрический запрос ?
3 . Что собой представляет перекрестный запрос ?
4 . Перечислите типы запросов действия .
5 . Назовите возможные способы создания запросов в СУБД Access.
6. В виде чего выводится результат запроса?
7. Какую структуру имеет бланк запроса, отображаемый в окне Конструктора запроса
8. Как создается вычисляемое поле в запросе?
9. Какая команда используется для выполнения запроса из окна Конструктора запроса?
10. Можно ли сохранить результат запроса?
Источник
