СУБД Microsoft Access 2003 РАЗРАБОТКА БАЗЫ ДАННЫХ (Запросы)

СУБД Microsoft Access 2003РАЗРАБОТКА БАЗЫ ДАННЫХ(Запросы)

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

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

Создание и изменение запросов Если строится запрос на основе нескольких несвязанных таблиц, результатом будет множество всех возможных комбинаций из записей всех таблиц • Запросы и связанные таблицы Такое множество называетсядекартовым произведением таблиц Определение связей между таблицами помогает мастеру ограничить результирующее множество только записями, которые связаны между собой Мастер запросов отказывается строить запросы по нескольким несвязанным таблицам

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

Создание и изменение запросов • Запросы и связанные таблицы Левый рисунок – результат запроса, построенного на основе связей между таблицами Правый – декартово произведение таблиц

Создание и изменение запросов Область таблиц • Конструктор запросов Конструктор – визуальное средство создания и редактирования запросов Разработчик указывает в конструкторе таблицы, которые участвуют в запросе и отбирает необходимые поля Бланк запроса Далее задаются условия отбора и сортировки данных

Создание и изменение запросов • Конструктор запросов. Пример Есть три связанных таблицы: Связи, указанные в схеме данных, автоматически отображаются в конструкторе

Создание и изменение запросов Построим простой запрос на выборку:требуется вывести фамилии клиентов и названия журналов, на которые они подписаны • Конструктор запросов. Пример После щелчка на кнопке «Создание запроса в режиме конструктора» появится диалоговое окно, предлагающее выбрать нужные таблицы. Выберем таблицы «Клиент», «Подписка» и «Журнал» и нажмем кнопку «Закрыть»

Создание и изменение запросов В области таблиц окна конструктора изображены отобранные таблицы и их связи • Конструктор запросов. Пример Символ «*» обозначает все поля таблицы Перетащим мышкой поля «Фамилия» и «Название» в бланк запроса

Создание и изменение запросов Запрос теперь выглядит так • Конструктор запросов. Пример Чтобы просмотреть результат, переключим запрос из режима конструктора в режим таблицы

Создание и изменение запросов Результат работы запроса Сохраним построенный запрос для последующего использования • Конструктор запросов. Пример В появившемся окне введем имя запроса и нажмем кнопку «OK» Сохраняется сам запрос, а не результат его работы. При каждом запуске запрос обращается к текущим данным таблиц

Создание и изменение запросов При построении запросов над данными можно выполнять более сложные действия. • Конструктор запросов. Пример 2 Построим другой запрос, подсчитывающий количество журналов, на которые подписан каждый из клиентов Снова нажмем «Создание запроса в режиме конструктора» Отберем в поле таблиц конструктора таблицы «Клиент» и «Подписка» Перетащим мышкой поля «Фамилия» и «Код журнала» в бланк запроса

Создание и изменение запросов Запрос теперь выглядит так • Конструктор запросов. Пример 2 Переключим запрос из режима конструктора в режим таблицы

Создание и изменение запросов • Конструктор запросов. Пример 2 Фамилия «Иванов» повторяется дважды, поскольку этот клиент подписан на два журнала Пока результат далек от желаемого: Нужно сгруппировать строки с одинаковыми фамилиями и для каждой фамилии подсчитать количество кодов журналов, сами коды в результате не нужны и фамилии не должны повторяться Вернемся в режим конструктора

Создание и изменение запросов Разрешим использование в запросе групповых операций • Конструктор запросов. Пример 2 При этом в бланке запроса появится новая строка

Создание и изменение запросов Для поля «Фамилия» выберем групповую операцию «Группировка», а для поля «КодЖурнала» - операцию «Count» (подсчет количества) • Конструктор запросов. Пример 2 Добавим сортировку по фамилии

Создание и изменение запросов Перейдем в режим таблицы • Конструктор запросов. Пример 2

Создание и изменение запросов Чтобы в результирующую таблицу попадали и те клиенты, у которых нет подписки, следует изменить параметры объединения • Конструктор запросов. Пример 2 Перейдя в режим конструктора, надо дважды щелкнуть по линии связи между таблицами

Создание и изменение запросов • Конструктор запросов. Пример 2 В появившемся диалоговом окне нужно выбрать второй тип объединения

Создание и изменение запросов • Конструктор запросов. Пример 2 Результирующая таблица теперь выглядит так

Создание и изменение запросов • Типы объединений таблиц Внутреннее объединение Внешнее левое объединение Внешнее правое объединение

Создание и изменение запросов Sum – сумма значений группы • Некоторые групповые операции Avg – среднее арифметическое Min – минимальное значение Max – максимальное значение Count – количество элементов First – первое значение Last – последнее значение Специальные(их применение будет показано ниже) Условие Выражение

Создание и изменение запросов В построенном ранее запросе второй столбец имеет название, сформированное автоматически. • Задание имен столбцов Зададим в конструкторе новое имя столбца Новый вид результата запроса

Создание и изменение запросов Количество данных, участвующих в создании результирующего набора, можно ограничить с помощью некоторых условий отбора • Условия отбора результатов Пример:Из таблицы «Клиент» берутся только данные для клиента, поле «Фамилия» которого имеет значение «Иванов» Результат Можно задать несколько условий для одного или нескольких столбцов.По горизонтали условия объединяются операцией «И», по вертикали – «ИЛИ»

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

Создание и изменение запросов Пример • Условия отбора и групповые операции Здесь добавлено условие отбора «=1» Результат имеет следующий вид: Отобраны те строки результирующего набора, в которых количество журналов подписчика равно одному

Создание и изменение запросов Пример • Условия отбора и групповые операции Допустим, требуется сделать отбор по определенному коду журнала, а уже после выполнять группировку и подсчет количества (т.е. отбор производится внутри группы) - Добавим в бланк запроса столбец «КодЖурнала»- Зададим для него групповую операцию «Условие»- Назначим критерий отбора «=1»

Создание и изменение запросов Пример • Условия отбора и групповые операции Теперь в результирующей таблице указаны фамилии клиентов, подписанных на журнал с кодом «1» и количество подписок каждого из них, оформленных именно на этот журнал

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

Создание и изменение запросов Функции обработки текста • Использование функций в выражениях Left(строка, n) – возвращает nлевых символов строки Right(строка, n) – возвращает nправых символов строки Mid(строка, n1, n2) – возвращает n2символов строки, начиная с позиции n1 InStr(строка1, строка2) – номер позиции, с которой строка2 входит в строка1 Ltrim(строка), Rtrim(строка) – удаляют пробелы из начала и конца строки соответственно Trim(строка) – удаляет пробелы из начала и конца строки

Создание и изменение запросов Функции обработки даты и времени • Использование функций в выражениях Date() – возвращает текущую дату Now() – возвращает текущую дату и время DateDiff(интервал, дата1, дата2) – определяет разницу между датами. Аргумент интервал определяет способ представления разницы:“yyyy” – год, “q” – квартал, “m” – месяц, “y” – день года, “d” – день, “w” – неделя, “h” – час, “n” – минута, “s” - секунда DateAdd(интервал, число, дата) – будущая дата, отстоящая от указанной на заданное число интервалов. Year(дата), Month(дата), Day(дата) – возвращают число - значение года, месяца и дня для указанной даты.

Создание и изменение запросов Функции преобразования (например, если требуется объединить фамилию и дату в одну строку, дата должна быть сначала преобразована в строковую переменную) • Использование функций в выражениях Str(аргумент) – преобразует значение аргумента в текстовую строку Val(строка) – преобразует строку в число Int(число) – возвращает целую часть числа Пример:"Сегодня: "+Str(Date())

Создание и изменение запросов • Использование функций в выражениях Условная функция IIf(выражение, если истинно, если ложно) – вычисляет значение аргумента выражение. Если значение истинно, возвращает значение второго аргумента, если ложно – значение третьего аргумента. Пример:IIf([количество]>0, “Есть в наличии”, “Отсутствует”)

Создание и изменение запросов • Использование функций в выражениях При построении запроса функции могут быть использованы не только в строке «Поле» конструктора, но и в строке «Условие отбора» Также функции могут быть использованы при построении форм и отчетов Объекты базы данных «Модули» позволяют пользователю определять собственные функции, которые могут быть использованы наравне со встроенными

Создание и изменение запросов Построитель выражений – инструмент, помогающий создавать выражения, используя объекты БД, операторы, встроенные и пользовательские функции • Построитель выражений Для запуска построителя выражений следует в контекстном меню соответствующей строки бланка запроса выбрать пункт «Построить»

Создание и изменение запросов Вид окна построителя выражений • Построитель выражений

Создание и изменение запросов В запросах могут использоваться параметры. • Параметры запросов Каждый параметр запроса должен иметь уникальное (в пределах запроса) имя. Имя параметра может участвовать в выражениях, входящих в запрос. При вызове запроса на выполнение открывается диалоговое окно с предложением указать значения его параметров. Для просмотра и изменения списка параметров следует выбрать пункт «Параметры» в контекстном меню окна конструктора

Создание и изменение запросов Вид окна редактирования параметров запроса • Параметры запросов

Создание и изменение запросов Если при запуске запроса в его теле встречается некоторое, неизвестное системе имя, оно автоматически считается именем параметра и выдается окно с предложением ввести его значение • Параметры запросов Создадим запрос на выборку, аналогичный построенному ранее и в качестве условия отбора укажем=[Введите фамилию]

Создание и изменение запросов Запустим созданный запрос • Параметры запросов В появившемся окне введем «Иванов» и нажмем «OK» В результирующей таблице выводятся названия журналов подписчика с фамилией «Иванов»

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

Создание и изменение запросов Рассмотрим процесс построения запроса, результат работы которого был приведен ранее • Перекрестные запросы С помощью конструктора построим запрос на выборку Вот результат его работы

Создание и изменение запросов В контекстном меню конструктора изменим тип запроса на «Перекрестный» • Перекрестные запросы

Создание и изменение запросов В бланке запроса появились строки «Групповая операция» и «Перекрестная таблица» • Перекрестные запросы

Создание и изменение запросов Укажем, что значения поля «Фамилия» образуют заголовки строк, поля «Название» - заголовки столбцов, а поля «КодЖурнала» - значения в таблице • Перекрестные запросы Для первых двух полей следует задать операцию «Группировка» (названия строк и столбцов не будут повторяться). При этом ячейке на пересечении строки и столбца могут соответствовать несколько значений «КодЖурнала». Для этого поля зададим групповую операцию «Count».

Создание и изменение запросов Здесь приведен результат работы созданного запроса • Перекрестные запросы Внесем в запрос некоторое «эстетическое» усовершенствование для демонстрации применения групповой операции «Выражение» и функции IIF() Пусть в ячейках таблицы отображается не количество подписок клиента на журнал, а знак «+», если это количество больше нуля

Создание и изменение запросов Заменим в третьей колонке групповую операцию «Count»на «Выражение» • Перекрестные запросы В первой строке вместо имени поля напишем выражение, в котором используется операция «Count» и условная функция IIf() IIf( Count( Подписка.КодЖурнала )>0; "+"; "") Операция «Выражение» используется, если необходимо выполнить более сложную обработку, чем вычисление стандартной групповой операции

Создание и изменение запросов • Перекрестные запросы Результат работы усовершенствованного запроса

СУБД Microsoft Access 2003 РАЗРАБОТКА БАЗЫ ДАННЫХ (Запросы)