Практическая работа № 5

Практическая работа № 5.

Тема: «Запросы-действия.»

1. Цель работы: 1.1. Научиться создавать новые таблицы с помощью запроса.

1.2. Научиться обновлять записи в таблицах с помощью запроса.

1.3. Научиться добавлять записи в таблицах с помощью запроса.

1.4. Научиться удалять записи в таблицах с помощью запроса.

2. Задание:

2.1. Ознакомиться со сведениями о запросах.

2.2. Создать новую таблицу с помощью запроса. Упраж.1.

2.3. Создать запрос на обновление. Упраж. 2 ,3.

2.4. Создать запрос на добавление. Упраж. 4.

2.5. Создать запрос на удаление. Упраж.5.

Создание новой таблицы с помощью запроса

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

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

Упражнение 1. Конструирование запроса на создание таблицы

Сконструируем запрос на создание таблицы на примере ранее полученного; запроса на выборку с групповыми вычислениями Число студентов в группах

1.  В окне базы данных выделите названный запрос и нажмите кнопку Кон­структор.

2.  Преобразуйте этот запрос на выборку в запрос на создание таблицы, вы­брав на панели конструктора тип запроса Создание таблицы.

3.  В окне Создание таблицы введите имя создаваемой табли­цы — Число студентов (рис. 1).

Рис1. Определение имени таблицы, создаваемой в запросе

4.  Чтобы просмотреть, какие записи будут помещены в новую таблицу, щелкните на кнопке панели инструментов Вид.

5.  Выполните запрос, чтобы таблица Число студентов была сохранена в базе данных. Теперь эту таблицу можно увидеть в списке таблиц окна базы данных.

Обновление группы записей

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

Упражнение 2. Конструирование запроса на обновление

Рассмотрим технологию создания запроса на обновление на примере обновления поля КОЛ (количество студентов группы в таблице ГРУППА). Коли­чество студентов в группах ранее было подсчитано с помощью статистиче­ской функции count в запросе на выборку Число студентов в группа. Запрос на обновление непосредственно на таком запросе построить нельзя. Поэтому используйте для обновления не сам запрос, а таблицу Число студентов, полученную по запросу на создание таблицы в предыдущем упражнении.

1. Для формирования запроса на обновление сначала создайте Запрос на вы­борку на основе двух таблиц: обновляемой таблицы ГРУППА и таблицы Число студентов, содержащей данные для обновления.

Замечание

В подсхеме данных запроса автоматически установится связь этих таблиц по полю с одинаковым именем (НГ).

2. Преобразуйте запрос на выборку в Запрос на обновление, выбрав Обновление из списка Тип запроса на панели конструктора или команду меню Запрос | Обновление. После выполнения этой команды в бланке запроса появляется строка Обновление (рис. 2).

3.  Заполните бланк запроса. Перетащите обновляемое поле КОЛ из списка таблицы ГРУППА в строку Поле. В строку Обновление введите имя поля Count_HC (таблицы Число студентов), из которого выбираются значения для обновления. Имя поля заключите в квадратные скобки.

Рис. 2. Запрос на обновление таблицы ГРУППА

4.  Просмотрите содержимое обновляемого поля КОЛ перед выполнением запроса, нажав кнопку на панели конструктора запросов Вид.

5.  Выполните запрос.

6.  Просмотрите содержимое обновляемого поля КОЛ в таблице после вы­полнения запроса.

7.  Для последующего использования подготовленного запроса сохраните его под именем Обновление ГРУППА_КОЛ.

Упражнение 3. Обновление поля таблицы вычисляемым значением

Рассмотрим формирование запроса на обновление с использованием выра­жения на примере заполнения поля ЧАСЫ для лекционных занятий в таб­лице ИЗУЧЕНИЕ. Пусть обновление поля ЧАСЫ должно производиться с помощью данных, вычисляемых на основе полей ЛЕК (часы лекций) и ЧС (число семестров) из таблицы ПРЕДМЕТ. Расчетное число часов по лекци­ям определим по формуле ЛЕК/ЧС.

В соответствии с задачей в записях лекционных занятий таблицы ИЗУЧЕ­НИЕ необходимо обновить поле ЧАСЫ расчетным числом часов. Выбрать записи о лекционных занятиях можно по значению поля ВИДЗ этой табли­цы, так как в нем указан вид занятия. Данные для расчета среднего числа часов содержатся в таблице ПРЕДМЕТ. Таким образом, запрос должен строиться на основе таблиц ИЗУЧЕНИЕ и ПРЕДМЕТ.

1.  Создайте запрос на выборку на основе таблиц ИЗУЧЕНИЕ и ПРЕДМЕТ.

2.  Преобразуйте его в запрос на обновление, нажав соответствующую кноп­ку на панели инструментов.

3.  Включите в бланк запроса обновляемое поле ЧАСЫ таблицы ИЗУЧЕНИЕ. В строке Обновление для этого поля введите выражение [ЛЕК]/[ЧС]. Имена полей обязательно заключите в квадратные скобки.

4.  Для отбора в таблице ИЗУЧЕНИЕ обновляемых записей о лекционных к занятиях в бланк запроса включите поле ВИДЗ и укажите в строке Условие отбора значение — лек.

Окончательно сформированный запрос показан на рис.3.

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

6.  Просмотрите содержимое поля ЧАСЫ до и после обновления, нажав кнопку Вид.

7.  Чтобы видеть результаты обновления в таблице ИЗУЧЕНИЕ, откройте ее одновременно с запросом

Рис.3. Запрос на обновление поля значениями, вычисляемыми по данным из другой таблицы

ВНИМАНИЕ

При использовании в запросе на обновление таблиц, находящихся в отношении 1:М, обновлять можно только содержимое столбцов таблицы со стороны "многие".

Добавление данных из другой таблицы

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

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

Упражнение 4 . Создание запроса на добавление

Пусть в некотором подразделении создается таблица об успеваемости сту­дентов УСПВРЕМ и необходимо ее данными дополнять таблицу УСПЕВАЕМОСТЬ, сохраняемую в централизованной базе данных. Структу­ра таблицы УСПВРЕМ аналогична структуре таблицы УСПЕВАЕМОСТЬ, но имеет и некоторые дополнительные поля.

1. Первоначально создайте Запрос на выборку на таблице УСПВРЕМ.

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

3.  В этом окне в поле имя таблицы введите или выберите из списка имя таблицы УСПЕВАЕМОСТЬ, в которую надо добавить записи.

4.  Если таблица УСПВРЕМ размещена в одной открытой базе данных с таблицей-приемником добавляемых записей, отметьте в текущей базе данных. Для таблицы, находящейся в другой базе дан­ных, надо отметить в другой базе данных ввести имя файла базы данных. При необходимости надо указать путь. Можно также указать базу данных Microsoft FoxPro, Paradox, dBASE или ввести строку
подключения для базы данных SQL.

5.  После преобразования запроса в запрос на добавление в его бланке появ­ляется строка Добавление (рис. 4).

6.  Для формирования добавляемых записей обязательно включите в бланк запроса поля, оставляющие ключ таблицы, в которую будет произво­диться добавление, и поле ОЦЕНКА.

7.  Поскольку в данном примере имена полей обеих таблиц совпадают, Access автоматически сформирует в строке Добавление имена полей таблицы-получателя, в которые будут добавляться значения из соответствующих полей таблицы запроса. Если эти имена не совпадают для указа­ния имен полей таблицы-получателя, соответствующих полям таблицы за­проса, откройте в каждой ячейке список полей и выберите нужное имя.

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

Рис. 4. Пример бланка запроса на добавление

9.  Для добавления записей нажмите кнопку Запуск (Run) на панели инст­рументов.

Замечание

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

Если в схеме данных БД установлен параметр Обеспечение целостности дан­ных (Enforce Referential Integrity), то добавление подчиненных записей возможно только в том случае, когда в главных таблицах уже имеются связанные записи.

Удаление группы записей

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

Упражнение 5. Создание запроса на удаление

Рассмотрим формирование запроса на удаление записей о группах, где ко­личество студентов менее 5. Таблицы ГРУППА и СТУДЕНТ находятся в отношении типа один-ко-многим и между ними установлена связь по полю НГ и параметр обеспечения целостности данных, но не установлен параметр каскадного удаления связанных записей. Для связи таблиц СТУДЕНТ и УСПЕВАЕМОСТЬ по полям НГ и НС параметр каскадного удаления свя­занных записей установлен.

Замечание

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

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

2.  Сначала создайте Запрос на выборку , в схему данных которого включите обе взаимосвязанные таблицы. Из таблицы СТУДЕНТ требуется удалить записи, а для поля таблицы ГРУППА задаются условия отбора.

Рис. 5. Запрос на удаление записей из двух взаимосвязан­ных таблиц

3. Затем в режиме конструктора преобразуйте запрос в Запрос на удалении, выбрав на панели инструментов тип запроса Удаление выполнив команду меню Запрос | Удалении. После преобразования запроса в запрос на удаление в его бланке появляется строка Удаление (рис. 6).

Рис. 6. Запрос на удаление записей подчиненной таблицы

4. Теперь приступайте к формированию бланка запроса. Переместите символ звездочки (*) из списка полей таблицы СТУДЕНТ в бланк запроса. В стро­ке Удаление в столбце этого поля появляется значение Из..

Замечание

Если в запросе объединяется две или более таблиц, находящихся в отношении 1:М, то удалить можно только записи одной из таблиц на стороне «многие».

5.  Чтобы задать условия отбора удаляемых записей, переместите с помощью мыши в бланк запроса поле КОЛ из таблицы ГРУППА. В строке Удале­ние под именем этого поля появится значение Условии.
Заполните строку Условие отбора для этого поля условием < 5.

6.  Для предварительного просмотра удаляемых записей нажмите на панели инструментов кнопку Вид . Для возврата в режим конструктора за­ проса также используйте эту кнопку.

7.  Для удаления записей подчиненной таблицы СТУДЕНТ нажмите кнопку Запуск на панели инструментов.

8.  После удаления записей подчиненной таблицы приступайте к удалению записей главной таблицы. Модифицируйте запрос. Удалите из схемы за­проса таблицу СТУДЕНТ. Запрос будет иметь вид, представленный на рис. 7

Рис7. Запрос на удаление записей главной таблицы

9. Нажмите кнопку Запуск. Записи главной таблицы ГРУППА будут удалены.

Результаты работы запроса на удаление зависят от установленных в схеме базы данных отношений между таблицами и параметров целостности.

Так как задан параметр Обеспечение целостности данных и параметр каскадное удаление связанных записей не установлен, для удаления записей групп и связанных с ними записей о студентах необходимо в запросе сначала удалить записи подчиненной таблицы СТУДЕНТ и только затем главной таблицы ГРУППА. Если бы параметр каскадного удаления был задан, то достаточно было бы удалить записи главной таблицы ГРУППА, а связанные записи подчинен­ной таблицы СТУДЕНТ удалялись бы автоматически. Когда установлен именно этот параметр, при удалении записей таблицы СТУДЕНТ удаляются и все связанные записи подчиненной таблицы УСПЕВАЕМОСТЬ.