ПЗ 11 исит. ПЗ-11_Запросы на изменение. Практическое задание 11. Запросы на изменение

• Название: Практическое задание 11. Запросы на изменение
• Анкор: ПЗ 11 исит
• Дата: 15.04.2021
• Размер: 489.98 Kb.
• Имя файла: ПЗ-11_Запросы на изменение.pdf
• Тип: Документы; #195039

1
ПРАКТИЧЕСКОЕ ЗАДАНИЕ 1
1. ЗАПРОСЫ НА ИЗМЕНЕНИЕ
В предыдущих лабораторных работах рассматривался только один вид запросов – запросы на выборку данных из таблиц. MS Access позволяет не только выбирать нужные данные, но и изменять их с помощью специальных запросов. Такие запросы называются запросами
на изменение. Существует четыре типа запросов на изменение:

запрос на создание таблицы создает новую таблицу на основе данных, содержащихся в результирующем множестве запроса. Этот тип используется для экспорта информации в другие приложения, для копирования таблицы в другую базу данных или повышения быстродействия форм и отчетов, базирующихся на одном сложном запросе;

запрос на добавление записей позволяет добавлять записи,
создаваемые запросом в существующую таблицу;

запрос на обновление изменяет значения полей в записях таблицы, отобранных с помощью запроса;

запрос
на
удаление удаляет из таблицы записи, соответствующие результирующему множеству запроса.
СОЗДАНИЕ ТАБЛИЦ С ПОМОЩЬЮ ЗАПРОСА
С помощью запроса на создание таблицы можно выбирать нужные данные из уже существующей таблицы с помощью обычного запроса на выборку, а затем помещать их в новую таблицу.
Процесс создания таблицы с помощью запроса состоит из трех шагов:

создать запрос на выборку;

преобразовать запрос на выборку в запрос на изменение, задав параметры размещения новой таблицы;

выполнить запрос на изменение, тем самым поместив отобранные записи в новую таблицу.
1.
Откройте файл Конфетная_фабрика.
2.
Создайте копию запроса на выборку Левое внешнее
соединение. Для этого:

щелкните на запросе правой кнопкой мыши и из контекстного меню выберите команду Копировать;

2
 снова щелкните правой кнопкой мыши и выполните команду
Вставить. Откроется диалоговое окно Вставка, в котором введите имя запроса: Отбор заказчиков без заказов;
 щелкните по кнопке .
3. На основе запроса на выборку Отбор заказчиков без заказов, постройте запрос на создание таблицы, в которой будет выводиться информация только о заказчиках, не сделавших заказ. Для этого:
 откройте запрос Отбор заказчиков без заказов в режиме
Конструктора;
 в поле Код заказа в строке Условие отбора введите критерий выборки Is Null;
 для поля Код заказа сбросьте флажок Вывод на экран. Это нужно сделать для того, чтобы это поле не попало в новую таблицу.
Это важно в случае, когда ключевое поле в таблице имеет Тип
данных – Счетчик, и поэтому не может иметь пустое значение. Это свойство наследуется новой таблицей, поэтому если флажок не сбросить, то при выполнении запроса будет выдаваться сообщение об ошибке
 теперь нужно преобразовать запрос Отбор заказчиков без
заказов из запроса на выборку в запрос на создание таблицы. Для этого:
 щелкните по кнопке Создание таблицы (вкладка
Конструктор, группа Тип запроса). Откроется диалоговое окно
Создание таблицы;
 в текстовое поле Имя таблицы введите имя для новой таблицы: Заказчики без заказов;
 оставьте активным переключатель в текущей базе
данных и щелкните по кнопке . В результате запрос на выборку преобразуется в запрос на создание таблицы;
 сохраните и закройте запрос Отбор заказчиков без
заказов. Теперь в окне базы данных ему соответствует другой значок, который сопровождается восклицательным знаком, это означает, что запрос является запросом на создание таблицы.
 теперь необходимо выполнить запрос на создание таблицы
Отбор заказчиков без заказов. Для этого:

3
 дважды щелкните по запросу на создание таблицы
Отбор заказчиков без заказов. В результате появится сообщение: Запрос на создание таблицы приведет к
изменению данных в таблице;
 щелкните по кнопке Да. Откроется следующее диалоговое окно с предложением: Подтвердить создание новой
таблицы с выбранными записями;
 нажмите кнопку Да. MS Access создаст новую таблицу
Заказчики без заказов. Откройте ее и просмотрите полученный результат: в таблице будут размещены данные только о заказчиках, не сделавших заказ;
 закройте таблицу Заказчики без заказов.
Запрос на создание таблицы может использоваться многократно для создания временных таблиц, при этом каждый раз будет создаваться новая таблица и удаляться старая.
СОЗДАНИЕ ЗАПРОСОВ НА ДОБАВЛЕНИЕ ДАННЫХ
Запрос на добавление данных добавляет набор записей (строк) из одной или нескольких исходных таблиц (или запросов) в одну или несколько результирующих таблиц.
Процесс создания запроса на добавление состоит из следующих основных шагов:
 создание запроса на выборку;
 преобразование запроса на выборку в запрос на добавление;
 выбор конечных полей для каждого столбца в запросе на добавление;
 выполнение запроса на добавление записей.
4. Создайте запрос на добавление данных, который будет выбирать из таблицы Заказы все заказы, оплаченные в 1998 году, и переносить их в другую таблицу Заказы_архив. Для выполнения задания:
 вначале создадим новую таблицу, в которую будут добавляться данные. Для чего:
 раскройте список таблиц в окне базы данных и выделите таблицу Заказы;

4
 скопируйте таблицу в буфер обмена, нажав комбинацию клавиш , ;
 вставьте таблицу из буфера обмена, нажав комбинацию клавиш , . Появится диалоговое окно Вставка
таблицы;
 в поле Имя таблицы введите текст: Заказы_архив;
 в группе Параметры вставки выберите переключатель
Только структура;
 нажмите кнопку <Оk>. В результате в списке таблиц появится новая пустая таблица Заказы_архив (т. к. была скопирована только структура таблицы Заказы).
 теперь необходимо создать запрос на добавление данных, который позволит перенести данные из таблицы Заказы в новую таблицу Заказы_архив. Для этого:
 вначале необходимо создать запрос на выборку нужных записей: во вкладке Создание в группе Другие щелкните по кнопке Конструктор запроса. Откроется Конструктор запроса вместе с диалоговым окном Добавление таблицы, в котором дважды щелкните по названию таблиц Заказы и Налоговый
статус заказов. Обе таблицы будут добавлены в верхнюю часть
Конструктора запроса. Закройте диалоговое окно Добавление
таблицы;
 перейдите в режим Конструктора и щелкните по кнопке Добавление (вкладка Конструктор, группа Тип запроса).
Откроется диалоговое окно Добавление;
 в поле Имя таблицы из списка выберите название таблицы, в которую необходимо добавлять записи:
Заказы_архив. Нажмите кнопку <Оk>. В нижней части бланка запроса на добавление появится дополнительная строка
Добавление;
 в строке Поле из списка выберите значение Заказы*, которое означает, что в запрос будут добавлены все поля таблицы Заказы;
 в следующем столбце строки Поле из списка выберите значение Дата оплаты;

5
 в строке Условие отбора столбца Дата оплаты введите выражение Between 01.01.1998 And 31.12.1998 (или используйте функцию Year([Дата оплаты])=1998);
 в строке Добавление столбца Дата оплаты удалите значение Дата оплаты, поскольку в первом столбце бланка запроса уже предусмотрен вывод всех полей таблицы Заказы, включая поле Дата оплаты;
 нажмите кнопку Выполнить. MS Access выдаст сообщение, что Будет добавлено следующее число записей: 4.
Нажмите кнопку Да. В результате записи будут добавлены в таблицу Заказы_архив;
 откройте таблицу Заказы_архив и просмотрите полученный результат;
 закройте таблицу Заказы_архив, сохранив изменения;
 сохраните созданный запрос, присвоив ему имя Сбор
архива заказов. Обратите внимание на значок в списке запросов, соответствующий запросу на добавление. Как и в любом запросе на изменение, он содержит восклицательный знак, но отличается от значка запроса на создание таблицы.
СОЗДАНИЕ ЗАПРОСОВ НА ОБНОВЛЕНИЕ ТАБЛИЦ
Запрос на обновление таблиц используется для обновления или изменения существующих данных в наборе записей.
5. Откройте таблицу Конфеты и в поле Орехи выполните сортировку по убыванию. Просмотрите, какие значения стоимости имеют конфеты, в состав которых входит орех Фундук.
6. Закройте таблицу Конфеты, сохранив изменения.
7. Создайте запрос на обновление, который в таблице Конфеты увеличит стоимость конфет, в состав которых входит орех Фундук, на
30%. Для этого:
 щелкните по кнопке Конструктор запросов (вкладка
Создание, группа Другие);
 в диалоговом окне Добавление таблицы дважды щелкните на таблице Конфеты и закройте диалоговое окно;
 перенесите в бланк запроса поля Стоимость и Сорт ореха;

6
 в строку Условие отбора в поле Сорт ореха введите значение Фундук и щелкните по кнопке Выполнить чтобы проверить как работает отбор;
 вернитесь в режим Конструктора;
 измените тип запроса на запрос на обновление. Для этого щелкните по кнопке Обновление (вкладка Конструктор, группа Тип
запроса). В результате в бланке запроса появится дополнительная строка Обновление. При этом исчезнут строки Сортировка и Вывод
на экран;
 в строку Обновление в поле Стоимость введите выражение, по которому будет вычисляться новая стоимость конфеты:
[Стоимость]+[Стоимость]*0,3;
 щелкните по кнопке Режим и выберите Режим таблицы. Вы увидите все записи, которые в дальнейшем будут обновляться (хотя данные в обновляемых полях останутся пока старые). Убедившись, что отобраны нужные записи, можно выполнять запрос;
 закройте и сохраните запрос под именем Вычисление
стоимости конфет;
 дважды щелкните по запросу Вычисление стоимости
конфет. MS Access выдаст сообщение о количестве обновляемых записей и запросит подтверждение на обновление. Щелкните по кнопке Да;
 откройте таблицу Конфеты и просмотрите полученный результат: для Сорта ореха: Фундук значения в поле Стоимость увеличились на 30%. Закройте таблицу Конфеты, сохранив изменения.
Запрос на обновление нельзя использовать для добавления новых записей в базу данных или удаления записей целиком из базы данных.
Тем не менее, запрос на обновление можно использовать для задания пустых значений для одного или нескольких полей. Результат такого действия равнозначен удалению части записи. Можно также использовать запрос на обновление для замены пустых значений другими данными.
Результат такого действия равнозначен добавлению данных.
8. Создайте копию запроса Левое внешнее соединение и сохраните ее под именем Удаление части данных заказчиков.

7 9. Выполните запрос Удаление части данных заказчиков и просмотрите полученный результат: первые 59 записей соответствуют случаю, когда заказчики не сделали ни одного заказа. Например, это заказчики в поле Код заказчика, имеющие значения 6, 17, 61, у которых заполнены поля Дата ответа, Предпочтения по шоколаду и Орехи.
10. На основе запроса Удаление части данных заказчиков создайте запрос на обновление, который в таблице Заказчики у заказчиков, не сделавших ни одного заказа, удалит данные в полях Дата
ответа и Предпочтения по шоколаду, а в поле Орехи введет значение
Нет. Для этого:
 откройте запрос Удаление части данных заказчиков в режиме Конструктора;
 в строке Условие отбора поля Код заказа введите значение
Is Null;
 удалите из бланка запроса поля Код заказчика, Фамилия и
Имя;
 из таблицы Заказчики во второй столбец бланка добавьте поле Дата ответа, в третий – поле Предпочтение по шоколаду, а в четвертый – Орехи;
 щелкните по кнопке Обновление (вкладка Конструктор, группа Тип запроса). В результате в бланке запроса появится дополнительная строка Обновление;
 в строку Обновление в поле Дата ответа введите выражение
([Дата ответа]-[Дата ответа])=Null;
 в строку Обновление в поле Предпочтения по шоколаду введите выражение Null, а в поле Орехи – число 0 (в логическом поле
Орехи число 0 соответствует значению Нет);
 щелкните по кнопке Режим и выберите Режим таблицы. Вы увидите все записи, которые будут в дальнейшем обновляться (хотя данные в обновляемых полях останутся старыми). Закройте созданный запрос, сохранив изменения;
 запустите запрос на обновление Удаление части данных
заказчиков. MS Access выдаст сообщение о количестве обновляемых записей и запросит подтверждение на обновление. Щелкните по кнопке Да;

8
 откройте таблицу Заказчики и просмотрите полученный результат;
 закройте таблицу Заказчики, сохранив изменения.
СОЗДАНИЕ ЗАПРОСОВ НА УДАЛЕНИЯ ЗАПИСЕЙ
Запросы на удаление записей позволяют отобрать требуемые записи и удалить их за один прием. Принцип создания такого запроса аналогичен созданию запроса на обновление, однако, удаляя записи из связанных таблиц, необходимо помнить о том, что при этом не должна нарушаться целостность данных.
Если две таблицы связаны отношением «один-ко-многим», то нельзя удалять записи из таблицы «один» если в таблице «многие» присутствуют соответствующие им записи. Сначала должны быть удалены записи в таблице «многие» и только потом – соответствующие им записи в таблице
«один».
11. Откройте Схему данных (вкладка Работа с базами данных, группа Показать или Скрыть), с помощью кнопки Отобразить таблицу добавьте таблицу Кредит заказчиков.
12. Создайте связь (типа «один-к-одному») между таблицами
Заказчики и Кредит заказчиков по общим полям Код заказчика и Код, учитывая, что главной является таблица Заказчики. При построении связи установите флажки Обеспечение целостности данных, Каскадное
обновление связанных полей и Каскадное удаление связанных
записей.
13. Закройте Схему данных, сохранив изменения.
14. Откройте таблицу Кредит заказчиков в режиме таблицы и просмотрите содержащиеся в ней данные: в таблице имеются заказчики, поле Задолженность которых имеет нулевое значение. Закройте таблицу.
15. Создайте запрос на удаление, который из таблицы Кредит
заказчиков отбирает записи, в которых поле Задолженность имеет нулевое значение и удаляет их. Имя запроса: Удаление записей из
таблицы. Для этого:
 во вкладке Создание в группе Другие щелкните по кнопке
Конструктор запросов. Откроется бланк запроса и диалоговое окно
Добавление таблицы;

9
 в бланк запроса добавьте таблицу Кредит заказчиков и закройте диалоговое окно Добавление таблицы;
 в первом столбце строки Поле из списка выберите значение
Кредит заказчиков*;
 во втором столбце строки Поле выберите имя поля
Задолженность;
 в строке Условие отбора в поле Задолженность введите значение =0 и снимите флажок Вывод на экран. Щелкните по кнопке
Выполнить, чтобы проверить как работает отбор;
 вернитесь в режим Конструктора и щелкните по кнопке
Удаление (вкладка Конструктор, группа Тип запроса). В результате в бланке запроса появится дополнительная строка Удаление. В первом столбце строки Удаление указано значение Из, которое показывает, что будут удаляться записи из таблицы Кредит
заказчиков. Во втором столбце строки Удаление появилось значение
Условие, что указывает на использование этого столбца для определения критерия отбора удаляемых записей. Кроме этого в бланке запроса исчезнут строки Сортировка и Вывод на экран;
 щелкните по кнопке Выполнить. MS Access откроет диалоговое окно с информацией о количестве записей, которые будут удалены. Щелкните по кнопке Да;
 закройте и сохраните запрос под именем Удаление записей
из таблицы;
 откройте таблицу Кредит заказчиков и просмотрите полученный результат. Заказчики, у которых поле Задолженность имело нулевое значение, будут удалены из таблицы;
 закройте таблицу Кредит заказчиков, сохранив изменения.
16. Закройте базу данных Конфетная_фабрика.
Контрольные вопросы:
 запросы на изменение данных и их типы;
 запрос на создание таблицы: его назначение, последовательность действий при создании таблицы, пример запроса;
 запрос на добавление данных: его назначение, основные шаги при создании запроса, пример запроса;

10
 запрос на обновление: принцип создания, примеры использования;
 запрос на удаление записей: принцип создания, назначение и пример.