Модификация БД с помощью запросов на изменение

Модификация БД с помощью запросов на изменение

 

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

 

Практическое задание 4

1. Откройте БД «Учет». В ней должны быть 4 таблицы: «Декларация», «Товары», «Транспорт», «Доставка».

Запрос на создание

2.    Создайте обобщенную таблицу «Движение товара», включив в нее следующие поля:

• из таблицы «Декларация»: «продавец»; «декларант»;
• из таблицы «Товары»: «наименование товара», «цена»;
• из таблицы «Транспорт» - «отметка о прохождении досмотра».

Для этого следует:

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

Введите 2 – 3 записи в обобщённую таблицу  и сохраните БД.

 

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

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

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

3. Измените цену товара с учётом  пошлины  на 5%, но цену одного  из товаров оставьте прежней.

Для этого:

• составьте новый запрос на выборку, включив в него поля «Наименование товара» и «Цена», в строке «Условие отбора» для поля «Наименование товара» установите «Not молоко» для товара, цену на который не поднимаем (например, командой контекстного меню Построить). Запустите запрос и проверьте правильность выборки, вернитесь в Конструктор;
• видоизмените запрос, установив ему статус «Обновление» (например, Запрос – Обновление). В появившейся строке «Обновление» для поля «цена» внесите выражение: «цена»*1,05;
• выполните запрос и подтвердите обновление; сохраните запрос для будущего использования, дав  ему имя и обратите внимание на значок карандаша с «!» у его имени; просмотрите результат.

 

Запрос на добавление (на примере  архивации)

 

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

4. Создайте путем копирования дубликат таблицы «Декларация» без данных, назвав ее «Архив». Для этого в контекстном меню для таблицы «Декларация» выберите Копировать, затем аналогично выполните команду Вставить, в параметрах вставки укажите «Только структуру».
5. В нее отберите записи с датой контракта до 1 января 2009 года. Для этого:

• создайте в Конструкторе запрос на выборку, включив в него 
  критерий по дате, и выполните его для проверки правильности; при этом обязательно используйте все поля таблицы;

• измените статус запроса на «Добавление», в появившемся окне задайте имя таблицы для добавления «Архив», обратите внимание на появление строки «Добавление» в бланке запроса;
• выполните запрос и подтвердите добавление; просмотрите результаты архивации и сохраните запрос, обратив внимание на значок «+» у его имени.

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

«Старые»  или неиспользуемые записи можно  удалить, но обязательно сначала произвести выборку и проверить ее. Целесообразно сделать копию.

6. Удалите из таблицы «Товары» записи по поводу одного из товаров, используя сохраненный запрос на добавление в архив, изменив его статус.
7. Сохраните БД в своей папке.

Обработка данных при помощи запросов на выборку

Параметрические, итоговые, перекрестные запросы

Условия запроса  могут быть включены непосредственно  в бланк запроса, но для того чтобы сделать его более универсальным, можно вместо конкретного значения отбора включить в запрос параметр (создать параметрический запрос). Для этого в строку «Условие отбора» вводится «имя параметра» — фраза в квадратных скобках, которая будет выводиться в качестве «подсказки» в процессе диалога. Для каждого параметра можно указать тип данных, который корректируется командой Запрос — Параметры. При задании нескольких параметров имя каждого должно быть уникальным.

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

Возможные операции в строке «Групповые операции»:

SUM	Сложение
AVG	Среднее значение
MIN	Минимальное значение
МАХ	Максимальное  значение
COUNT	Количество  записей со значениями (пропускает пустые значения)
STDEV	Стандартное отклонение
VAR	Дисперсия
FIRST	Значение  в первой записи
LAST	Значение  в последней записи

Особый тип  итоговых запросов, представляющих результаты поиска в виде электронной таблицы, называется перекрестным. Для его создания нужно в Конструкторе запроса выполнить команду Запрос — Перекрестный, что позволит добавить новую строку в бланк запроса «Перекрестная таблица». Для каждого поля такого запроса может быть выбрана одна из установок: «Заголовки строк», «Заголовки столбцов», «Значение» (выводится в ячейках таблицы) и «Не отображается». Для перекрестного запроса надо обязательно определить хотя бы по одному полю в качестве заголовка строк, заголовка столбцов и значения. Можно использовать дополнительные условия отбора и сортировку.

Практическое задание 5

Запрос на выборку

1. Откройте БД «Учет» и щелкните  на вкладке Запрос.

2. Запустите Конструктор запросов (Запросы — Создать — Конструктор запросов).
3. Добавьте таблицу «Движение товара».
4. Создайте следующие запросы на выборку данных:

• Условия поставки, место досмотра товара, товар, цена . Сохраните этот запрос с именем «Цена» (Файл — Сохранить как);

• Условия поставки, место досмотра товара, товар, цена, если цена больше заданной величины, сохраните этот запрос с именем «Цена 1/»;
• Условия поставки, место досмотра товара, товар, цена, если цена больше заданной величины (при этом цену не показывать), сохраните этот запрос с именем «Цена2»;
• Номер транспортного средства без отметки о прохождении досмотра. Для этого необходимо при формировании добавить еще таблицу «Транспорт» (Запрос — Добавить таблицу). Сохраните запрос с именем «Без досмотра».

5. Добавьте в запросы сортировку (строка «Сортировка») по;

• товарам;

• товарам и ценам.

6. При создании критерия можно использовать инструмент     
   (Построить), возможна такая же команда контекстного меню     
   для категории «Условие отбора» в нижней части Конструктора запроса.
7. Опробуйте этот инструмент при построении следующих запросов в сочетании с вводом критериев поиска вручную. Создайте запросы для извлечения данных:

• по дате контракта за три года,  используя в качестве критерия выражение: Between... and (категория «Сравнения»), а затем с помощью знаков «<» и «>»;

• по продавцам, фамилии которых начинаются с «Г»-«Я»;
• по покупателям, фамилии которых начинаются с «Н»—«Я» и с «А»—«В»;
• по фамилиям декларантов, которые имеют вторую букву «о»;
• по пяти фамилиям, которые начинаются с букв  
  «А»—«В». Используйте для этого инструмент панели инструментов Конструктора запросов.

8. Перейдите в окно БД и скопируйте запрос с новым именем.

Параметрические запросы

9. Сформируйте запрос для выборки информации по вводимому имени продавца.
10. Создайте запрос для получения данных на транспорт по запросу номера декларанта.

Итоговые запросы. Группировка данных.

Использование критериев в итоговых запросах

11. Щелкните на вкладке Запрос.

12. Создайте запрос для подсчета суммарной цены товаров. 
Для этого:

• добавьте таблицу «товары»;
• добавьте в бланк запроса поле «цена»;
• выберите команду Вид— Групповые операции и в выпадающем списке в строке «Группировка» выберите функцию (SUM);
• запустите запрос и просмотрите результаты.

13. Используя подходящие функции, найдите наибольшую и среднюю цену товара.

14.  Для объединения записей в группы и получения итоговых значений по каждой группе используется опция «Группировка». Создайте новый запрос в режиме Конструктора. Для этого:

• добавьте таблицу Декларация в окно запроса;
• в первый столбец поместите поле «Декларант»;
• во второй— «условия поставки»;
• установите для первого столбца в строке «Групповая операция»— «Группировка», для второго— COUNT;
• выполните запрос и прокомментируйте результаты.

15.  Можно объединять записи в группы по нескольким полям одновременно, а также создавать группы внутри групп. В БД «Борей» существует три вида доставки: «Ространс», «Самовывоз» и «Почта». Определите, сколько заказов осуществлено каждым видом доставки каждому покупателю.

Для этого:

• в окно Конструктора запросов добавьте таблицы «Заказы», 
  «Доставка» и «Клиенты»;
• поместите в бланк запроса поля «Название» из таблицы «Клиенты», «Название» из таблицы «Доставки» и «Код заказа»;
• задайте условия групповых операций: «Группировка» по первым двум полям и COUNT по «Коду заказа»;
• выполните запрос, объясните результаты.

16.  Дополните предыдущий запрос критерием, который включает в выборку только те заказы, которые оформлены в 1995 году и позже. (В строке «Групповая операция» укажите «Условие».) Все запросы должны быть сохранены с «прозрачными» именами.

17. Выберите записи, стоимость перевозок в которых превышает 
    заданное значение.
18. Найдите записи, в которых для каждого вида доставки было оформлено более 5 заказов («Доставка» — «Группировка», «Код заказа» — COUNT, «Условие отбора» в поле «Код заказа» >=5).

 

 Перекрестные запросы

19. Составьте запрос для выяснения: сколько продавцов и из каких городов (см. пример).
20. Придумайте свой вариант перекрестного запроса.

 

 

Формы в MS Access

Для организации  удобного интерфейса с БД используются формы. Форма позволяет вывести на экран одну запись в виде электронного бланка.

Формы могут создаваться:

• автоматически Вставка — Автоформат для открытой таблицы или вкладка Форма — Создать — Автоформа различных видов для выбранной таблицы;
• полуавтоматически — с помощью Мастера форм (вкладка Форма — Создать — Мастер форм);
• вручную — с помощью Конструктора форм (вкладка Форма — Создать — Конструктор форм или Вставка — Форма — Конструктор форм или инструментом Новый объект).

Для того чтобы  при открытии БД на экран выводилась конкретная форма (как это сделано в учебной БД «Борей»), ее имя следует указать в пункте меню Сервис — Параметры запуска — Форма.

Практическое задание 7

Создание Автоформ

1. Откройте таблицу «Заказы» БД «Борей».  Создайте для нее Автоформу (Вставка — Автоформа). Оцените результаты.
2. Измените содержание некоторых полей таблицы с помощью полученной формы, введите 1—2 новые записи.
3. Преобразуйте форму в таблицу (Вид — Таблица) и обратно и закройте ее с сохранением.
4. Последовательно сделайте три Автоформы с различным размещением полей (вкладка Формы — Создать — Автоформа: ленточная/ в столбец/ табличная). Сформулируйте особенности каждой.

Создание формы с помощью Мастера

5. Создайте с помощью Мастера форм (вкладка Формы — Создать — Мастер форм или Вставка — Форма — Мастер форм) новую форму «Заказ1» для таблицы «Заказы». Включите в нее поля:

«Код  заказа»;

«Дата размещения заказа»;

«Стоимость  доставки»;

«Тип  доставки».