Поиск и выборка данных из базы данных

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

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

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

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

Рис. 8.9.Команды фильтрации

Пользователь может выделить фрагмент значения поля и использовать выделенный фрагмент в качестве условия фильтрации, может отфильтровать отдельные значения поля или задать условие отбора с применением логических операций. Если пользователь не владеет записью логических функций, можно воспользоваться бланком фильтра, задание условий на вкладках которого равносильно применению логических функций И/ИЛИ (рис.8.10).

Рис. 8.10.Возможности фильтрации данных и бланк фильтра

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

Рис. 8.11.Бланк расширенного фильтра

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

В запросах можно использовать данные одной таблицы или нескольких связанных таблиц. Если таблицы не связаны в схеме, их можно связать в запросе. Если таблицы связаны в схеме, то в запросе можно изменить свойства установленной связи. Например, в запросе Стоимость звонка установлена дополнительная связь между таблицами Абоненты и Контакты (рис.8.12).

Рис. 8.12.Окно конструктора запроса с добавленной связью

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

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

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

В процессе создания запроса формируется структура запроса, т. е. указывается, какие поля источников данных запроса должны выводиться в колонках таблицы запроса; задаются порядок вывода записей и условия вывода записей. На рис.8.12 показано окно конструктора запроса, а на рис.8.13 – окно просмотра запроса в режиме таблицы.

Рис. 8.13.Запрос в режиме таблицы

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

Важными отличиями между фильтрами и запросами являются следующие:

· фильтр всегда выводит все столбцы таблицы, а запрос – только те, которые явно указаны в бланке запроса;

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

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

Рис. 8.14.Запрос с группировкой в режиме конструктора

На рис.8.14 показано окно конструктора запроса, рассчитывающего суммарную стоимость звонков каждого абонента. При этом в бланк запроса включен запрос Стоимость звонков и таблица Абоненты, которые связаны в запросе по коду абонента. Операция суммирования значений нескольких записей запроса Стоимость звонков, осуществляемая для каждого абонента, определяется групповыми операциями Sum и Группировка, расположенными в одноименной строке. В процессе выполнения этого запроса суммируются стоимости звонков при постоянных значениях кода абонента и, соответственно, при постоянных значениях фамилии абонента, его имени и номера телефона. Результирующая запись – одна для каждого абонента (рис.8.15). При задании групповых операций доступны встроенные статистические функции.

Рис. 8.15.Запрос с группировкой в режиме таблицы

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

Рис. 8.16.Параметрический запрос

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

Рис. 8.17.Перекрестный запрос в режиме конструктора

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

Рис. 8.18.Выполненный перекрестный запрос

Мастер запросов Повторяющиеся записи позволяет установить, сколько раз встречаются те или иные значения в полях таблицы. Это тип запроса также относится к группирующим запросам. Для вывода повторов используется группирующая функция Count, а условие вывода записи – количество записей в группе превышает 1. Можно модифицировать запрос, чтобы искать любое количество повторений значений, например, превышающее 10.

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

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