Лекция # 1 -5 СУБД Microsoft Access 2000 РАЗРАБОТКА БАЗЫ ДАННЫХ (Запросы) Электронная Россия (2002 -2010), ЭР-2003
Создание и изменение запросов Назначение запросов Запросы позволяют извлекать данные из одной или нескольких таблиц в соответствии с заданными условиями отбора, производить при этом обработку и сортировку данных Кроме запросов на выборку, существуют запросы на обновление, добавление и удаление данных, а также на создание таблицы Электронная Россия (2002 -2010), ЭР-2003
Создание и изменение запросов Создание запроса с помощью мастера Мастер позволяет создавать простые запросы на основе одного или нескольких источников данных (таблиц или запросов) Следует выбрать поля, которые должны быть отображены в запросе Электронная Россия (2002 -2010), ЭР-2003
Создание и изменение запросов Запросы и связанные таблицы Если строится запрос на основе нескольких несвязанных таблиц, результатом будет множество всех возможных комбинаций из записей всех таблиц Такое множество называется декартовым произведением таблиц Определение связей между таблицами помогает мастеру ограничить результирующее множество только записями, которые связаны между собой Мастер запросов отказывается строить запросы по нескольким несвязанным таблицам Электронная Россия (2002 -2010), ЭР-2003
Создание и изменение запросов Запросы и связанные таблицы В данном примере есть две связанные между собой таблицы. При построении запроса для отображения отобраны вторые поля. В результирующий набор входят комбинации полей из связанных записей Электронная Россия (2002 -2010), ЭР-2003
Создание и изменение запросов Запросы и связанные таблицы Левый рисунок – результат запроса, построенного на основе связей между таблицами Правый – декартово произведение таблиц Электронная Россия (2002 -2010), ЭР-2003
Создание и изменение запросов Конструктор запросов Область таблиц Конструктор – визуальное средство создания и редактирования запросов Разработчик указывает в конструкторе таблицы, которые участвуют в запросе и отбирает необходимые поля Далее задаются условия отбора и сортировки данных Бланк запроса Электронная Россия (2002 -2010), ЭР-2003
Создание и изменение запросов Конструктор запросов. Пример Есть три связанных таблицы: Связи, указанные в схеме данных, автоматически отображаются в конструкторе Электронная Россия (2002 -2010), ЭР-2003
Создание и изменение запросов Конструктор запросов. Пример Построим простой запрос на выборку: требуется вывести фамилии клиентов и названия журналов, на которые они подписаны После щелчка на кнопке «Создание запроса в режиме конструктора» появится диалоговое окно, предлагающее выбрать нужные таблицы. Выберем таблицы «Клиент» , «Подписка» и «Журнал» и нажмем кнопку «Закрыть» Электронная Россия (2002 -2010), ЭР-2003
Создание и изменение запросов Конструктор запросов. Пример В области таблиц окна конструктора изображены отобранные таблицы и их связи Символ «*» обозначает все поля таблицы Перетащим мышкой поля «Фамилия» и «Название» в бланк запроса Электронная Россия (2002 -2010), ЭР-2003
Создание и изменение запросов Конструктор запросов. Пример Запрос теперь выглядит так Чтобы просмотреть результат, переключим запрос из режима конструктора в режим таблицы Электронная Россия (2002 -2010), ЭР-2003
Создание и изменение запросов Конструктор запросов. Пример Результат работы запроса В появившемся окне введем имя запроса и нажмем кнопку «OK» Сохраним построенный запрос для последующего использования Сохраняется сам запрос, а не результат его работы. При каждом запуске запрос обращается к текущим данным таблиц Электронная Россия (2002 -2010), ЭР-2003
Создание и изменение запросов Конструктор запросов. Пример 2 При построении запросов над данными можно выполнять более сложные действия. Построим другой запрос, подсчитывающий количество журналов, на которые подписан каждый из клиентов Снова нажмем «Создание запроса в режиме конструктора» Отберем в поле таблиц конструктора таблицы «Клиент» и «Подписка» Перетащим мышкой поля «Фамилия» и «Код журнала» в бланк запроса Электронная Россия (2002 -2010), ЭР-2003
Создание и изменение запросов Конструктор запросов. Пример 2 Запрос теперь выглядит так Переключим запрос из режима конструктора в режим таблицы Электронная Россия (2002 -2010), ЭР-2003
Создание и изменение запросов Конструктор запросов. Пример 2 Фамилия «Иванов» повторяется дважды, поскольку этот клиент подписан на два журнала Пока результат далек от желаемого: Нужно сгруппировать строки с одинаковыми фамилиями и для каждой фамилии подсчитать количество кодов журналов, сами коды в результате не нужны и фамилии не должны повторяться Вернемся в режим конструктора Электронная Россия (2002 -2010), ЭР-2003
Создание и изменение запросов Конструктор запросов. Пример 2 Разрешим использование в запросе групповых операций При этом в бланке запроса появится новая строка Электронная Россия (2002 -2010), ЭР-2003
Создание и изменение запросов Конструктор запросов. Пример 2 Для поля «Фамилия» выберем групповую операцию «Группировка» , а для поля «Код. Журнала» операцию «Count» (подсчет количества) Добавим сортировку по фамилии Электронная Россия (2002 -2010), ЭР-2003
Создание и изменение запросов Конструктор запросов. Пример 2 Перейдем в режим таблицы Электронная Россия (2002 -2010), ЭР-2003
Создание и изменение запросов Конструктор запросов. Пример 2 Чтобы в результирующую таблицу попадали и те клиенты, у которых нет подписки, следует изменить параметры объединения Перейдя в режим конструктора, надо дважды щелкнуть по линии связи между таблицами Электронная Россия (2002 -2010), ЭР-2003
Создание и изменение запросов Конструктор запросов. Пример 2 В появившемся диалоговом окне нужно выбрать второй тип объединения Электронная Россия (2002 -2010), ЭР-2003
Создание и изменение запросов Конструктор запросов. Пример 2 Результирующая таблица теперь выглядит так Электронная Россия (2002 -2010), ЭР-2003
Создание и изменение запросов Типы объединений таблиц Внутреннее объединение Внешнее левое объединение Внешнее правое объединение Электронная Россия (2002 -2010), ЭР-2003
Создание и изменение запросов Некоторые групповые операции Sum – сумма значений группы Avg – среднее арифметическое Min – минимальное значение Max – максимальное значение Count – количество элементов First – первое значение Last – последнее значение Специальные (их применение будет показано ниже) Условие Выражение Электронная Россия (2002 -2010), ЭР-2003
Создание и изменение запросов Задание имен столбцов В построенном ранее запросе второй столбец имеет название, сформированное автоматически. Зададим в конструкторе новое имя столбца Новый вид результата запроса Электронная Россия (2002 -2010), ЭР-2003
Создание и изменение запросов Условия отбора результатов Количество данных, участвующих в создании результирующего набора, можно ограничить с помощью некоторых условий отбора Пример: Из таблицы «Клиент» берутся только данные для клиента, поле «Фамилия» которого имеет значение «Иванов» Результат Можно задать несколько условий для одного или нескольких столбцов. По горизонтали условия объединяются операцией «И» , по вертикали – «ИЛИ» Электронная Россия (2002 -2010), ЭР-2003
Создание и изменение запросов Условия отбора и групповые операции Рассмотрим приведенный ранее пример запроса. В нем производится группировка данных по фамилиям клиентов и для каждой группы подсчитывается число ее элементов Если во втором столбце добавить некоторое условие отбора, оно будет применяться после операции группировки и подсчета количества, к результирующему набору Электронная Россия (2002 -2010), ЭР-2003
Создание и изменение запросов Условия отбора и групповые операции Пример Здесь добавлено условие отбора «=1» Результат имеет следующий вид: Отобраны те строки результирующего набора, в которых количество журналов подписчика равно одному Электронная Россия (2002 -2010), ЭР-2003
Создание и изменение запросов Условия отбора и групповые операции Пример Допустим, требуется сделать отбор по определенному коду журнала, а уже после выполнять группировку и подсчет количества (т. е. отбор производится внутри группы) - Добавим в бланк запроса столбец «Код. Журнала» - Зададим для него групповую операцию «Условие» - Назначим критерий отбора «=1» Электронная Россия (2002 -2010), ЭР-2003
Создание и изменение запросов Условия отбора и групповые операции Пример Теперь в результирующей таблице указаны фамилии клиентов, подписанных на журнал с кодом « 1» и количество подписок каждого из них, оформленных именно на этот журнал Электронная Россия (2002 -2010), ЭР-2003
Создание и изменение запросов Выражения в запросах Для формирования результирующих наборов могут использоваться не только поля таблиц, но и построенные на их основе выражения. Пример: Запрос, выводящий список клиентов с их инициалами Результат работы запроса Электронная Россия (2002 -2010), ЭР-2003
Создание и изменение запросов Использование функций в выражениях Функции обработки текста Left(строка, n) – возвращает n левых символов строки Right(строка, n) – возвращает n правых символов строки Mid(строка, n 1, n 2) – возвращает n 2 символов строки, начиная с позиции n 1 In. Str(строка 1, строка 2) – номер позиции, с которой строка 2 входит в строка 1 Ltrim(строка), Rtrim(строка) – удаляют пробелы из начала и конца строки соответственно Trim(строка) – удаляет пробелы из начала и конца строки Электронная Россия (2002 -2010), ЭР-2003
Создание и изменение запросов Использование функций в выражениях Функции обработки даты и времени Date() – возвращает текущую дату Now() – возвращает текущую дату и время Date. Diff(интервал, дата 1, дата 2) – определяет разницу между датами. Аргумент интервал определяет способ представления разницы: “yyyy” – год, “q” – квартал, “m” – месяц, “y” – день года, “d” – день, “w” – неделя, “h” – час, “n” – минута, “s” - секунда Date. Add(интервал, число, дата) – будущая дата, отстоящая от указанной на заданное число интервалов. Year(дата), Month(дата), Day(дата) – возвращают число - значение года, месяца и дня для указанной даты. Электронная Россия (2002 -2010), ЭР-2003
Создание и изменение запросов Использование функций в выражениях Функции преобразования (например, если требуется объединить фамилию и дату в одну строку, дата должна быть сначала преобразована в строковую переменную) Str(аргумент) – преобразует значение аргумента в текстовую строку Val(строка) – преобразует строку в число Int(число) – возвращает целую часть числа Пример: "Сегодня: "+Str(Date()) Электронная Россия (2002 -2010), ЭР-2003
Создание и изменение запросов Использование функций в выражениях Условная функция IIf(выражение, если истинно, если ложно) – вычисляет значение аргумента выражение. Если значение истинно, возвращает значение второго аргумента, если ложно – значение третьего аргумента. Пример: IIf([количество]>0, “Есть в наличии”, “Отсутствует”) Электронная Россия (2002 -2010), ЭР-2003
Создание и изменение запросов Использование функций в выражениях При построении запроса функции могут быть использованы не только в строке «Поле» конструктора, но и в строке «Условие отбора» Также функции могут быть использованы при построении форм и отчетов Объекты базы данных «Модули» позволяют пользователю определять собственные функции, которые могут быть использованы наравне со встроенными Электронная Россия (2002 -2010), ЭР-2003
Создание и изменение запросов Построитель выражений – инструмент, помогающий создавать выражения, используя объекты БД, операторы, встроенные и пользовательские функции Для запуска построителя выражений следует в контекстном меню соответствующей строки бланка запроса выбрать пункт «Построить» Электронная Россия (2002 -2010), ЭР-2003
Создание и изменение запросов Построитель выражений Вид окна построителя выражений Электронная Россия (2002 -2010), ЭР-2003
Создание и изменение запросов Параметры запросов В запросах могут использоваться параметры. Каждый параметр запроса должен иметь уникальное (в пределах запроса) имя. Имя параметра может участвовать в выражениях, входящих в запрос. При вызове запроса на выполнение открывается диалоговое окно с предложением указать значения его параметров. Для просмотра и изменения списка параметров следует выбрать пункт «Параметры» в контекстном меню окна конструктора Электронная Россия (2002 -2010), ЭР-2003
Создание и изменение запросов Параметры запросов Вид окна редактирования параметров запроса Электронная Россия (2002 -2010), ЭР-2003
Создание и изменение запросов Параметры запросов Если при запуске запроса в его теле встречается некоторое, неизвестное системе имя, оно автоматически считается именем параметра и выдается окно с предложением ввести его значение Создадим запрос на выборку, аналогичный построенному ранее и в качестве условия отбора укажем =[Введите фамилию] Электронная Россия (2002 -2010), ЭР-2003
Создание и изменение запросов Параметры запросов Запустим созданный запрос В появившемся окне введем «Иванов» и нажмем «OK» В результирующей таблице выводятся названия журналов подписчика с фамилией «Иванов» Электронная Россия (2002 -2010), ЭР-2003
Создание и изменение запросов Перекрестные запросы Перекрестный запрос позволяет представлять извлекаемые данные в удобной для анализа форме Пусть имеется три колонки данных. Перекрестный запрос позволяет использовать данные из одной колонки в качестве названий строк таблицы, другой – в качестве названий столбцов, а данные из третьей колонки будут размещены в таблице на пересечении соответствующих строк и столбцов Пример: по одним и тем же данным построены запрос на выборку (слева) и перекрестный (справа) Электронная Россия (2002 -2010), ЭР-2003
Создание и изменение запросов Перекрестные запросы Рассмотрим процесс построения запроса, результат работы которого был приведен ранее С помощью конструктора построим запрос на выборку Вот результат его работы Электронная Россия (2002 -2010), ЭР-2003
Создание и изменение запросов Перекрестные запросы В контекстном меню конструктора изменим тип запроса на «Перекрестный» Электронная Россия (2002 -2010), ЭР-2003
Создание и изменение запросов Перекрестные запросы В бланке запроса появились строки «Групповая операция» и «Перекрестная таблица» Электронная Россия (2002 -2010), ЭР-2003
Создание и изменение запросов Перекрестные запросы Укажем, что значения поля «Фамилия» образуют заголовки строк, поля «Название» - заголовки столбцов, а поля «Код. Журнала» - значения в таблице Для первых двух полей следует задать операцию «Группировка» (названия строк и столбцов не будут повторяться). При этом ячейке на пересечении строки и столбца могут соответствовать несколько значений «Код. Журнала» . Для этого поля зададим групповую операцию «Count» . Электронная Россия (2002 -2010), ЭР-2003
Создание и изменение запросов Перекрестные запросы Здесь приведен результат работы созданного запроса Внесем в запрос некоторое «эстетическое» усовершенствование для демонстрации применения групповой операции «Выражение» и функции IIF() Пусть в ячейках таблицы отображается не количество подписок клиента на журнал, а знак «+» , если это количество больше нуля Электронная Россия (2002 -2010), ЭР-2003
Создание и изменение запросов Перекрестные запросы Заменим в третьей колонке групповую операцию «Count» на «Выражение» В первой строке вместо имени поля напишем выражение, в котором используется операция «Count» и условная функция IIf() IIf( Count( Подписка. Код. Журнала )>0; "+"; "") Операция «Выражение» используется, если необходимо выполнить более сложную обработку, чем вычисление стандартной групповой операции Электронная Россия (2002 -2010), ЭР-2003
Создание и изменение запросов Перекрестные запросы Результат работы усовершенствованного запроса Электронная Россия (2002 -2010), ЭР-2003
Литература 1. 2. 3. 4. Microsoft Access 2000. Шаг за шагом: Практ. пособ. // М. : Издательство ЭКОМ, 2002. Карпов Б. Microsoft Access 2000: Справочник. // СПб: Питер, 2001. Форт С. , Хоуи Т. , Релстон Дж. Программирование в среде Access 2000. Энциклопедия пользователя: Киев: Издательство «Диа. Софт» , 2000. Справка по Microsoft Access (входит в состав пакета Access). Электронная Россия (2002 -2010), ЭР-2003
