«Запросы в СУБД Access» Преподаватель: Французова Г.Н.

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

с помощью запросов по образцу (QBE Query By Example) с помощью инструкций SQL (Structured Query Language язык структурированных запросов). SQL специализированный язык, предназначенный для организации запросов, обновления и управления реляционными базами данных.

Практически все типы запросов в Access можно создать визуально. Исключение составляют сквозные запросы (SQL pass-through) это запросы из других приложений, запросы на изменение структуры данных и запросы объединения.

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

В Access может быть создано несколько видов запросов: запрос на выборку выбирает данные из взаимосвязанных таблиц и других запросов. Результатом его является таблица, которая существует до закрытия запроса. Таблицу с результатами запроса можно использовать для работы с данными в таблицах, на которых построен запрос;

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

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

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

Для создания запроса надо выбрать в окне базы данных закладку Запрос и нажать кнопку Создать. В открывшемся окне Новый запрос из предложенных вам типов запросов: конструктор, простой запрос, перекрестный запрос, повторяющиеся записи, записи без подчиненных, выбрать конструктор (рис. 2.4).

В окне Добавление таблицы выбрать используемые в запросе таблицы и нажать кнопку Добавить. Затем кнопкой Закрыть выйти из окна Добавление таблицы. В результате появится окно конструктора запросов : запрос на выборку.

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

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

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

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

Создание запроса с помощью Конструктора запросов

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

Бланк запроса по образцу Бланк запроса по образцу (QBE) представлен в виде таблицы в нижней панели окна запроса, которая имеет для заполнения следующие строки: Поле:, Имя таблицы:, Сортировка:, Вывод на экран:, Условие отбора:, или:. До формирования запроса эта таблица пуста.

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

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

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

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

В списке полей каждой таблицы на первом месте стоит символ *, который означает «все поля таблицы». Этот пункт выбирается, если в запрос включаются все поля.

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

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

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

Условия отбора записей, сортировка и фильтрация данных Условия отбора записей могут задаваться для одного или нескольких полей в соответствующей строке бланка запроса. Условием отбора является выражение, которое состоит из операторов сравнения и сравниваемых операторов. В качестве операторов сравнения и логических операторов могут использоваться: =,,, Between, In, Like, And, Or, Not, которые определяют операцию над одним или несколькими операндами.

Если условие отбора не содержит оператора, то по умолчанию используется оператор =. В качестве операндов могут использоваться: литералы, константы и идентификаторы (ссылки).

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

Константами являются неизменяющиеся значения, например True, Falls, Да, Нет, Null. Идентификатор представляет собой ссылку на значение поля, элемент управления или свойство. Идентификаторами могут быть имена полей, таблиц, запросов, форм, отчетов и т. д. Они должны заключаться в квадратные скобки.

Если необходимо указать ссылку на поле в конкретной таблице, форме, отчете, то перед именем поля ставится имя таблицы, также заключенное в квадратные скобки и отделенное от имени поля восклицательным знаком. Например, [Имя таблицы]! [Имя поля].

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

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

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

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

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

Пункт Назначения"Москва"Отображает заказы на доставку товаров в Москву Пункт Назначения"Москва" Or "Санкт-Пе­ тербург"Использует оператор Or для отображения заказов на доставку товаров в Москву или Санкт-Петербург

Дата ОтгрузкиBetween # * And # ** Использует оператор Between...And для отображения заказов на отгрузку товаров не ранее 5-янв-ОЗ и не позднее 10-янв-03

Дата Отгрузки #2/2/03# Отображает заказы на отгрузку товаров 2-фев- ОЗ Страна Доставки In ("Россия", "США") Использует оператор In для отображения заказов на доставку товаров в Россию или США

Страна ДоставкиNot "США Использует оператор Not для отображения заказов на доставку товаров во все страны, за исключением США

Имя КлиентаLike "С*" Заказы на доставку товаров клиентам, имена которых начинаются с буквы С Название>="Н Отображает заказы на доставку товаров в фирмы, названия которых начинаются с букв, находящихся в диапазоне от Н до Я

Дата Заказа< Date( )- 30 Использует функцию Date для отображе­ния заказов, сделанных ранее, чем 30 дней назад Дата Заказа Year ([Дата Заказа])=2003 Использует функцию Year для отображе­ ния заказов, сделанных в 2003 году

Дата Заказа Year ([Дата Заказа] )= Year(NowQ) And Мот 11([Дата Заказа])= Month(Now()) Использует функции Year и Month, а также оператор And для отображения заказов текущего года и месяца

Область Доставки Is Null Отображает заказы для клиентов, у кото­рых поле «Область Доставки» является пустым Область Доставки Is Not Null Отображает заказы для клиентов, у кото­рых поле «Область Доставки» содержит какое-либо значение

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

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

В Access существуют фильтры четырех типов: фильтр по выделенному фрагменту, обычный фильтр, расширенный фильтр фильтр по вводу.

Фильтрование данных в Access производится с помощью кнопок или (команда меню Записи, Фильтр, Изменить фильтр).

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

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

Изменение данных в БД средствами запроса Отбор заменяемых записей (полей) производится с помощью Запроса на выборку, а затем в окне конструктора за­ просов с помощью кнопки Обновление на панели инструментов или команды меню Запрос|Обновление превращается в запрос на обнов­ление.

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

После выполнения команды Запрос|3 апуск или нажатия соответствующей кнопки открывается диалоговое окно с сообщением о числе обнов­ляемых записей и вопросом о продолжении операции обновления.

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

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

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

В открывшемся окне Добавление в поле Имя таблицы вводится имя таблицы, в которую надо добавить записи.

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

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

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

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

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

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

После выполнения этой команды в бланке запроса появляется строка Удаление. Символ звездочка (*) из списка полей таблицы, записи которой требуется удалить, перемещается мышкой в бланк запроса. В строке Удаление в столбце этого поля появляется значение Из. Для полей, для которых это необходимо, заполняется строка Условие отбора.

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

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

Литература: А.В. Кузин «Разработка баз данных в системе Microsoft Access