Лабораторная работа -15

Лабораторная работа № 15

Тема: Основные средства работы в СУБД MS Access.

Цель работы: познакомиться с основными средствами работы в СУБД Microsoft Access, такими как создание запросов, работа с формами и создание отчётов.

1.Теоретические сведения

1.1Понятие запроса. Типы запросов.

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

Типы запросов:

1)Запросы на выборку и перекрестные запросы

2)Запросы на изменение, в том числе:

Запросы на создание таблицы;

Запросы на удаление;

Запросы на добавление;

Запросы на обновление.

3)Запросы с параметрами

4)SQL-запросы, предназначенные для решения более сложных задач,

1.Запросы на выборку и перекрестные запросы используются для извлечения и представления данных, а также для передачи данных в формы и отчеты. При выполнении запроса на выборку или перекрестного запроса в программе Microsoft Office Access 2007 результаты запроса отображаются в режиме таблицы.

2.Существует четыре типа запросов на изменение:

запрос на добавление,

запрос на удаление,

запрос на обновление

запрос на создание таблицы.

Кроме запроса на создание таблицы (который создает новые таблицы), остальные запросы на изменение вносят изменения в данные в таблицах, на основе которых они созданы. Эти изменения не так просто отменить, например нажатием клавиш CTRL+Z. Если при помощи запроса на изменение внесены изменения, которые впоследствии оказались не нужны, восстановить прежнее состояние данных можно только из резервной копии. По этой причине рекомендуется перед тем, как запускать запрос на изменение, убедиться, что сделана свежая резервная копия исходных данных.

3.Запрос с параметрами при выполнении требует ввода некоторых значений.

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

Запрос с параметрами одновременно принадлежит и к другому типу запроса. Большинство запросов с параметрами являются также запросами на выборку или перекрестными запросами. Запрос на добавление, запрос на создание таблицы и запрос на обновление также могут быть запросами с параметрами.

4.Существует три основных типа запросов SQL:

запрос на объединение,

запрос к серверу

управляющий запрос.

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

Запросы к серверу не выполняются самим ядром базы данных Microsoft Access. Вместо этого они перенаправляются на удаленный сервер базы данных, где происходит их обработка, а результаты отправляются обратно в Microsoft Access.

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

Запросы SQL нельзя открыть в режиме конструктора. Их можно открыть только в режиме SQL или запустить. Кроме управляющих запросов все остальные запросы SQL при выполнении открываются в режиме таблицы.

1.2. Создание запросов.

Запрос можно создать тремя способами:

1)С помощью мастера запросов

2)С помощью конструктора запросов

3)В режиме SQL

Пример: создать запрос для вывода на экран товаров, одновременно количеством больше 10 и ценой больше 8000. Имеется исходная таблица – Таблица.

1.

Рисунок 1 – Таблица 1.

На вкладке Создание в подменю Другие нажимаем кнопку Конструктор запросов (см. рисунок 2).

Рисунок 2. – Вкладка конструктор запросов

Появляется окно Добавление таблицы. На вкладке Таблица выбираем требующуюся нам таблицу (в данном примере Таблица 1) и жмем кнопку Добавить. При этом в окне Запрос 1 появится структура данной таблицы (рис.3).

Рисунок 3 – Внешний вид конструктора запросов.

Далее закрываем окно Добавление таблицы и работаем непосредственно с конструктором запроса, выставляя требуемые нам параметры. В первом столбце строки Поле в раскрывающемся списке выбираем «Марка», при этом автоматически установятся значения в поле Имя таблицы и Вывод на экран. То же самое проводим со столбцами «Цена» и «Количество», определяя условие отбора для них (для Цены > 8000, для Количества >10) (рис.4).

Рисунок 4. Конструктор запросов

Далее жмем кнопку Выполнить и получаем требуемый нам результирующий набор.

Рисунок 5 – Результирующий набор, соответствующий запросу

Также этот запрос можно выполнить в режиме SQL(рис.6):

Рисунок 6 – Введенный запрос в режиме SQL.

Наиболее распространенные операторы и ключевые слова SQL:

Оператор SELECT — один из самых важных операторов языка, применяемый для выбора данных.

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

Предложение WHERE используется для фильтрации результатов, возвращаемых оператором SELECT.

Оператор ALL применяется совместно с операторами сравнения при сравнении со списком значений

Оператор ANY применяется совместно с операторами сравнения при сравнении со списком значений

Оператор BETWEEN применяется при проверке нахождения значения внутри заданного интервала (включая его границы)

Оператор IN применяется для проверки наличия значения в списке

Оператор LIKE применяется при проверке соответствия значения заданной маске

Предложение ORDER BY (необязательное) применяется для сортировки результирующего набора данных по одной или нескольким колонкам. Для определения порядка сортировки используются ключевые слова ASC (по возрастанию) или DESC (по убыванию). По умолчанию данные сортируются по возрастанию.

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

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

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

Для удаления и вставки записей в таблицу используются операторы DELETE и INSERT соответственно.

Пример: из таблицы с полями Автор, Название книги, Тираж, Стоимость, Количество страниц произвести выборку, чтобы полученный результирующий набор содержал только авторов, чья фамилия начинается на букву Г, тираж книг которых больше 1000 и стоимость одного экземляра книги меньше 150, причем значения стоимости будут отсортированы по убыванию.

Рисунок 7 – запрос, созданный с помощью команд SQL.

2.1 Работа с формами в MS Access.

Форма — это объект базы данных, который можно использовать для ввода, изменения или отображения данных из таблицы или запроса. Формы могут применяться для управления доступом к данным: с их помощью можно определять, какие поля или строки данных будут отображаться. Например, некоторым пользователям достаточно видеть лишь несколько полей большой таблицы. Если предоставить им форму, содержащую только нужные им поля, это облегчит для них использование базы данных. Для автоматизации часто выполняемых действий в форму можно добавить кнопки и другие функциональные элементы.

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

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

Самый простой способ создать форму – при помощи инструмента «Форма».

Рисунок 8 – Создание формы с помощью инструмента «Форма».

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

Приложение Access создаст форму и отобразит ее в режиме макета. В режиме макета можно внести изменения в структуру формы при одновременном отображении данных. Например, при необходимости можно настроить размер полей в соответствии с данными.

Рисунок 9 – Внешний вид формы, созданной с помощью инструмента «Форма».

Если Access обнаруживает одну таблицу, связанную отношением «один-ко- многим» с таблицей или запросом, который использовался для создания формы, Access добавляет таблицу данных в форму, основанную на связанной таблице или запросе. Например, если создается простая форма, основанная на таблице «Сотрудники», и между таблицами «Сотрудники» и «Заказы» определено отношение «один-ко-многим», то в таблице данных будут отображаться все записи таблицы «Заказы», относящиеся к текущей записи сотрудника. Если таблица данных в форме не нужна, ее можно удалить. Если существует несколько таблиц, связанных отношением «один-ко-многим» с таблицей, которая использовалась для создания

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

инструмента «Разделенная форма»;

инструмента «Несколько элементов»;

мастера форм;

инструмента «Пустая форма»;

Разделенная форма — это новая возможность в Microsoft Office Access 2007, позволяющая одновременно отображать данные в двух представлениях — в режиме формы и в режиме таблицы. Эти два представления связаны с одним и тем же источником данных и всегда синхронизированы друг с другом. При выделении поля в одной части формы выделяется то же поле в другой части. Данные можно добавлять, изменять или удалять в каждой части формы (при условии, что источник записей допускает обновление, а параметры формы не запрещают такие действия).

Рисунок 10 – использование инструмента Разделенная форма.

В форме, созданной с помощью средства «Форма», одновременно отображается только одна запись. Если нужна форма, в которой отображается сразу несколько записей, и при этом требуются более широкие возможности настройки, чем у таблицы, можно воспользоваться инструментом «Несколько элементов»:

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

Рисунок 11 – внешний вид формы, созданной инструментом «Разделенная форма».

В форме, созданной с помощью средства «Форма», одновременно отображается только одна запись. Если нужна форма, в которой отображается сразу несколько записей, и при этом требуются более широкие возможности настройки, чем у таблицы, можно воспользоваться инструментом «Несколько элементов»:

Рисунок 12. – Создание формы с несколькими элементами

Рисунок 13 – форма, созданная инструментом «Несколько элементов».

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

3.1 Создание отчётов в MS Access.

Отчет – объект базы данных, основное назначение которого – описание и вывод на печать документов созданных на основе данных базы.

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

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

Создать отчёт можно несколькими способами:

1.с помощью средства «Отчет»

2.с помощью мастера отчетов

3.с помощью средства «Пустой отчет»

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

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

Рисунок 14 – создание отчёта с помощью средства «Отчет»

Рисунок 15 – внешний вид отчета, созданного с помощью средства «Отчет».

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

Если по каким-либо причинам для построения отчета не подходят ни средство «Отчет», ни мастер отчетов, можно создать отчет с нуля при помощи средства «Пустой отчет». Это очень быстрый способ, особенно если в отчете должно содержаться небольшое количество полей. Чтобы воспользоваться средством «Пустой отчет», выполните следующие действия.

На вкладке Создание в группе Отчеты щелкните Пустой отчет.

В режиме макета появится пустой отчет, а в правой части окна Access

— область Список полей.

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

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

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

Разделы отчета

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