Лекция "Access"

2 семестр. Лекция 3. СУБД Microsoft Access 2003. Запросы. Формы и отчеты. 1. Запросы............................................................................................................................................................................1 2. Формы и отчеты..............................................................................................................................................................9 1. Запросы Запросы позволяют извлекать данные из одной или нескольких таблиц в соответствии с заданными условиями отбора, производить при этом обработку и сортировку данных. Кроме запросов на выборку, встречаются запросы на обновление, добавление и удаление данных, а также на создание таблиц. Наиболее часто применяются запросы на выборку (извлечение данных).   Ниже   будет  рассмотрена   задача   создания   именно   таких   запросов.   Запрос   на выборку должен иметь один или несколько источников данных. Источниками могут быть таблицы или другие запросы. Общие   принципы   построения   запросов.  При   выполнении   запроса   на   основе нескольких   источников,   над   данными   из   этих   источников   выполняются  две   операции: умножение и сужение.  Первая   строит   все   возможные   комбинации   из   всех   записей   всех   источников.   Такое множество называется декартовым произведением источников.  Вторая операция (сужение) отсекает «лишние» строки, используя заданные ограничения. Первоначальные ограничения  образуются связями, определенными между источниками. При этом множество  комбинаций  всех записей  «сужается» до множества  комбинаций записей, связанных между собой. Дальнейшее сужение выполняется за счет ограничений, наложенных на значения выбранных полей. Инструменты создания запросов.  Для создания запросов в  Access  используются мастер или конструктор. Мастер (рис. 1) позволяет создавать несложные запросы на основе одного   или   нескольких   источников   данных.   Мастер  отказывается   строить   запросы   при отсутствии связей между источниками. Рис. 1. Мастер запросов Конструктор запросов (рис. 2) предоставляет значительно более широкие возможности. В верхней части окна конструктора находится область таблиц. В ней размещаются источники данных,   участвующие   в   запросе.   В   нижней   части   ­  бланк   запроса,   в   который   заносятся выбираемые поля, указываются операции над ними (математические, логические, текстовые и т.п.) и задаются ограничения (условия отбора). Рис. 2. Конструктор запросов В учебных целях запросы лучше готовить вручную, с помощью Конструктора. Как и в   случае   с   таблицами,   для   этого   есть   специальный   значок   в   окне   База   данных.   Он называется  Создание  запроса в режиме конструктора  и открывает  специальный  бланк, называемый   бланком   запроса   по   образцу.   За   этим   длинным   названием   скрывается   тот приятный факт, что, хотя запросы к таблицам баз данных пишутся на специальном языке программирования – SQL, пользователям Microsoft Access изучать его не обязательно, а большинство   операций   можно   выполнить   щелчками   кнопок   мыши     и   приемом перетаскивания в бланке. Идея   формирования   запроса   по   образцу   чрезвычайна   проста.   С   помощью контекстного   меню   на   верхней   половине   бланка   открывают   те   таблицы,   к   которым обращен   запрос.   Затем   в   них   щелкают   двойными   щелчками   на   названиях   тех   полей, которые   должны   войти   в   результирующую   таблицу.   При   этом   автоматически заполняются   столбцы   в   нижней   части   бланка.   Сформировав   структуру   запроса,   его закрывают, дают ему имя и в дальнейшем запускают двойным щелчком на значке в окне База данных. Порядок действий, рассмотренный выше, позволяет создавать простейший запрос, называемый запрос на выборку. Он позволяет выбрать данные из полей таблиц, на основе которых запрос сформирован. Упорядочение записей в результирующей таблице.  Если необходимо, чтобы данные, отобранные в результате работы запроса на выборку, были упорядочены по какому­ либо   полю,   применяют   сортировку.   В   нижней   части   бланка   имеется   специальная   строка Сортировка.   При   щелчке   на   этой   строке   открывается   кнопка   раскрывающего   списка,   в котором   можно   выбрать   метод   сортировки:   по   возрастанию   или   по   убыванию.   В результирующей таблице данные будут отсортированы по тому полю, для которого задан порядок сортировки. Возможна   многоуровневая   сортировка   –   сразу   по   нескольким   полям.   В   этом   случае данные сначала сортируются по тому полю, которое в бланке запроса по образцу находится левее, затем по следующему полю, для которого включена сортировка, и так далее слева направо. Соответственно, при формировании запроса надо располагать поля результирующей таблицы не как попало, а с учетом будущей сортировки. Управление   отображением   данных   в   результирующей   таблице.  В   нижней части бланка запроса имеется строка Вывод на экран. По умолчанию предполагается, что все поля, включенные в запрос, должны выводиться на экран, но это не всегда целесообразно. Например,   бывают   случаи,   когда   некое   поле   необходимо   включить   в   запрос,   например потому,   что   оно   является   полем   сортировки,   но   в   то   же   время   нежелательно,   чтобы пользователь   базы   видел   его   содержимое.   В   таких   случаях   отображение   содержимого   на экране подавляют сбросом флажка Вывод на экран. Групповые   операции.  Групповые   операции   предназначены   для   статистической обработки данных. Имеется в виду, что одна строка результирующего набора может быть сформирована   на   основе   нескольких   строк   исходных   данных.   Иначе   говоря,   группа исходных строк порождает одну результирующую  строку. Группа формируется на основе равенства значений некоторых полей.  Подключить возможность использования групповых операций в запросе можно нажатием  на панели инструментов либо через Меню «Вид – Групповые операции». При этом кнопки  в бланке запроса появится строка «Групповая операция» (рис.3).  Рис. 3. Групповые операции в запросе Для  всех   полей,  формирующих   группу,  должна  быть   задана   операция  «Группировка». Поскольку   значения   в   этих   полях   равны   между   собой,   они   могут   быть   представлены единственной строкой. Значения в остальных полях могут не совпадать и для того, чтобы из наборов значений каждого столбца сформировать единственное результирующее значение, над этими столбцами выполняются некоторые групповые операции (например, вычисление суммы или среднего арифметического).  Ниже перечислены основные групповые операции, используемые в Access: • Sum ­ сумма значений группы • Avg ­ среднее арифметическое • Min ­ минимальное значение • Max ­ максимальное значение • Count ­ количество элементов в группе • First ­ первое значение из группы • Last ­ последнее значение из группы Кроме того, есть две специальные групповые операции, применение которых будет  показано ниже: • Условие • Выражение Условия отбора  результатов.  Состав данных, входящих в результирующий  набор, может быть ограничен путем задания дополнительных условий отбора. Условия помещаются в специально отведенные поля в бланке запроса. На рис. 4 показан пример запроса с условием отбора   и   результат   работы   этого   запроса   (отображается   только   клиент   с  фамилией «Иванов»). Рис. 4. Запрос с условием отбора В бланке запроса можно задавать несколько условий. При этом условия, размещенные в разных   столбцах   одной   строки,   объединяются   операцией   «И»  (одновременно   должны выполняться   условия,   заданные   в  каждом   из   столбцов).  Условия,   размещенные   в   разных строках, объединяются операцией «Или» (должны соблюдаться условия хотя бы в одной из строк).  Использование   выражений   в   запросах.  При   формировании   результирующих наборов   могут   использоваться   не   только   поля   таблиц,   но   и   построенные   на   их   основе выражения.   Выражения   следует   вписывать   в   строку   «Поле»  бланка   запроса.   На   рис.   5 показан запрос, формирующий список клиентов с их инициалами и результат его работы. В запросе   используется   операция   сложения   строк   и   функция  Left().   Для   избегания неоднозначного   толкования   выражений,   имена   полей   заключаются   в   квадратные   скобки. Ниже   перечислены   некоторые   функции,   которые   могут   быть   полезны   при   построении запросов. Следует иметь в виду, что при использовании функций в конструкторе запросов их аргументы отделяются не запятой, а точкой с запятой.  Функции обработки текста: • Left(строка, n) ­ возвращает n левых символов строки. • Right(строка, n) ­ возвращает n правых символов строки. • Mid(строка, n1, n2) ­ возвращает n2 символов строки, начиная с позиции n1. • • Ltrim(строка), InStr(строка1, строка2) ­ номер позиции, с которой строка2 входит в строка1. соответственно.  Rtrim(строка)   ­   удаляют   пробелы   из   начала   и   конца   строки • Trim(строка) ­ удаляет пробелы из начала и конца строки. Рис. 5. Выражение в запросе Функции обработки даты и времени: • Date() ­ возвращает текущую дату. • Now() ­ возвращает текущую дату и время. • DateDiff(интервал,   дата1,   дата2)   ­   определяет   разницу   между   датами.   Аргумент интервал задает способ представления разницы: "yyyy" – год, "q" – квартал, "m" ­ месяц, "y" ­ день года, "d" ­ день, "w" ­ неделя, "h" ­ час, "n" ­ минута, "s" ­ секунда • DateAdd(интервал, число, дата) ­ будущая дата, отстоящая от указанной на заданное число интервалов. • Year(дата), Month(дата), Day(дата) ­ возвращают число ­ значение года,  месяца и дня для указанной даты. Функции преобразования типов: • Str(аргумент) ­ преобразует значение аргумента в текстовую строку • Val(строка) ­ преобразует строку в число • Int(число) ­ возвращает целую часть числа Функции преобразования нужны, если выполняются операции над данными разных типов. Например, если требуется объединить фамилию  и дату в одну  строку, дата должна быть сначала преобразована в текстовую переменную. Условная функция:  IIf(выражение, если истинно, если ложно) ­ вычисляет  значение аргумента выражение. Если значение истинно, возвращает значение второго аргумента, если ложно   ­   значение   третьего   аргумента.  Пример:  IIf([количество]>0,   "Есть   в   наличии", "Отсутствует") Если   выражение,   указанное   в   строке   «Поле»   бланка   запроса   должно   играть   роль групповой   (статистической)   операции   в   запросе   с   группировкой,   в   строке  «Групповая операция» бланка запроса следует вместо конкретной операции выбрать пункт «Выражение» (этот специальный тип групповой операции).  При построении запроса выражения и функции могут  быть использованы не только в строке «Поле» бланка конструктора, но и в строке «Условие отбора». Кроме того, объекты базы данных «Модули» позволяют пользователю определять собственные функции, которые могут быть использованы наравне с встроенными. В   некоторых   случаях   может   оказаться   полезным   входящий   в  Access  построитель выражений   ­   инструмент,   помогающий   создавать   выражения,   используя   объекты   БД, операторы, встроенные и пользовательские функции. Для   запуска   построителя   выражений   следует   в   контекстном   меню   соответствующей строки бланка запроса выбрать пункт «Построить» (рис. 6). Рис. 6. Запуск построителя выражений  Вид окна построителя выражений показан на рис. 7. Рис. 7. Окно построителя выражений Другие виды запросов. Мы рассмотрели запросы на выборку. Это самые простые и в то же время наиболее распространенные виды запросов. Однако существуют и другие виды запросов, некоторые из которых выполняются на базе предварительно созданного запроса на выборку. К ним относятся прежде всего:  Запросы с параметром (интересны тем, что критерий отбора может задать сам пользователь, введя нужный параметр при вызове запроса);  Итоговые   запросы,   назначение   которых   отдаленно   напоминает   итоговые функции   электронных   таблиц   (производят   математические   вычисления   по заданному полю и выдают результат);  Запросы на изменение – позволяют автоматизировать заполнение полей таблиц;  Перекрестные   запросы,   позволяющие   создавать   результирующие   таблицы   на основе результатов расчетов, полученных при анализе группы таблиц;  Специфические запросы  SQL  – запросы к серверу базы данных, написанные на языке запросов SQL. Параметры   запросов.  В   запросах   могут   присутствовать   параметры.   Они используются для передачи в запрос некоторых данных в момент его выполнения. Один и тот же   запрос,   выполненный   с   разными   значениями   параметров,  может   создавать   различные результирующие наборы. Каждый параметр запроса должен иметь уникальное (в пределах запроса)   имя.   Имя   параметра   участвует   в   выражениях,   входящих   в   запрос.   При   вызове запроса на выполнение открывается диалоговое окно с предложением указать значения его параметров.   Для   просмотра   и   изменения   списка   параметров   следует   выбрать   пункт «Параметры»   в   контекстном   меню   окна   конструктора   (рис.   8).   При   этом   будет   открыто диалоговое окно со списком параметров запроса. Рис. 8. Редактирование списка параметров запроса В некоторых случаях нет необходимости задавать имя параметра явно. Если при запуске запроса  в его теле  встречается некоторое неизвестное имя,  оно автоматически  считается именем параметра и  Access  выдает окно с предложением ввести его значение. На рис. 9 приведен   пример   запроса,   содержащего  параметр   с   именем   «Введите   фамилию»   (имя параметра   заключено   в   квадратные   скобки).   Это   имя   воспринимается   как   параметр, поскольку полей с таким именем в выбранных таблицах не существует. Рис. 9. Пример запроса с параметром При запуске этого запроса на выполнение будет выдано окно для ввода значения этого параметра. Формируемый результирующий набор зависит от введенного значения (рис. 10). Рис. 10. Выполнение запроса с параметром Перекрестные   запросы.  Перекрестный   запрос   в   некоторых   случаях   позволяет отобразить данные в удобной для анализа форме. Для построения перекрестного запроса, данные   предварительно   должны   быть   приведены   к   определенному   виду:   следует сформировать   три   колонки   данных.   Перекрестный   запрос  будет   использовать   данные   из одной колонки в качестве названий строк таблицы, другой ­ в качестве названий столбцов, а данные из третьей колонки будут  размещены в таблице на пересечении  соответствующих строк и столбцов. На  рис. 11 показано, как выглядят запрос на выборку и перекрестный запрос, построенные на основе одних и тех же данных. Рис. 11. Сравнение запроса на выборку и перекрестного 2. Формы и отчеты Инструменты   создания   форм.  Для   создания   форм   в  Access  существуют следующие инструменты: •   Мастер форм. Позволяет создавать несложные формы на основе одной или нескольких связанных таблиц или запросов. Автоформа.  Средство   автоматического   создания   форм.   Может   создавать  простые формы нескольких разновидностей: ­ Форма в столбец ­ одновременно на экран выводятся данные одной записи таблицы или запроса, расположенные в столбец; ­ Ленточная форма ­ одновременно на экран выводятся данные нескольких  записей, каждая запись занимает одну строку; ­ Табличная форма ­ данные отображаются в виде таблицы. • Конструктор.  Позволяет вручную создавать и редактировать любые формы. Иногда удобно   сперва   создать   форму   в   виде   автоформы   или   с  помощью   мастера,   а   затем «довести до ума» конструктором. Конструктор форм. Конструктор форм включает в себя следующие средства: • Бланк формы. В нем располагаются элементы, предназначенные для отображения в  форме. В основном, именно в бланке формируется внешний вид формы. •   Панель элементов. Содержит стандартный набор визуальных элементов, которые могут быть помещены в бланк формы.  Редактор   свойств.   Позволяет   задавать   свойства   формы   и   размещенных   в   ней элементов (объектов). Для формы  и всех  элементов определены  наборы свойств. Каждое свойство определяет какой­то аспект поведения или способ отображения объекта. В окне редактора свойств показываются свойства элемента, который в настоящий момент активен (выделен). В заголовке окна редактора отображается название этого элемента. Отчеты.  Способы   построения   отчетов   и   форм   очень   близки.   Различия   между   ними связаны с тем, что формы предназначены для просмотра и ввода данных, а отчеты ­ для просмотра   и   вывода   на   печать.   В   отчетах   присутствуют   некоторые   дополнительные возможности по форматированию данных в документах.  Средством   автоматизированного   создания   отчетов   является   Мастер   отчетов.   Мастер отчетов работает в шесть этапов: 1)выбор базовых таблиц или запросов, на которых отчет базируется 2)выбор полей, отображаемых в отчете 3)выбор полей группировки 4)выбор полей и методов сортировки 5)выбор формы печатного макета 6)выбор стиля оформления. Структура   готового   отчета   отличается   от   структуры   формы   только   увеличенным количеством   разделов.   Кроме   разделов   заголовка,   примечания   и   данных,   отчет   может содержать   разделы   верхнего   и   нижнего   колонтитулов.   Если   отчет   занимает   более   одной страницы, эти разделы необходимы для печати служебной информации, например номеров страниц.   Если   для   каких­то   полей   отчета   применена   группировка,   количество   разделов отчета увеличивается, поскольку оформление заголовков групп выполняется в отдельных разделах. Редактирование   структуры   отчета   выполняется   в   режиме   Конструктора.   Приемы редактирования те же, что и для форм. Важной особенностью отчетов является наличие средства для вставки в область верхнего или нижнего колонтитула текущего номера страницы и полного количества страниц.