Каскадное обновление и удаление связанных записей

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

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

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

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

Язык SQL (аббревиатура Structured Query Language) – это язык структурированных запросов, стандартный язык, предназначенный для создания баз данных, добавления новых и поддержки имеющихся данных, а также извлечения требуемой информации. Язык SQL с самого начала был создан, чтобы работать с данными из тех баз, которые следуют реляционной модели.

Каждому запросу MS Access можно сопоставить эквивалентную инструкцию SQL. В MS Access пользователи, знакомые с языком SQL, могут использовать его для просмотра и изменения запросов в режиме конструктора, определения свойств форм и отчетов, создания специальных запросов (запросы объединения, запросы к серверу и управляющие запросы), создания подчиненных запросов.

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

Просмотрим или изменим инструкцию SQL:

1. Выполнив анализ предметной области, создадим групповой запрос «Список студентов группы ДФД-31» на основании таблиц «Студент» и «Группа» (рис. 21), в который включим список следующих полей таблиц:

Группа.[Обозначение группы], Студент.[Номер зачетной книжки], Студент.Фамилия, Студент.Имя, Студент.Отчество, Студент.Год рождения, Студент.Адрес, Студент.[Домашний телефон], Студент.[Балл при поступлении].

В поле Группа.[Обозначение группы] используем условие отбора по коду группы: «ДФД-31».

Групповой запрос «Список студентов группы ДФД-31» в MS Access в режиме конструктора

2. Выберем Режим SQLв меню Вид или через кнопку «Режим SQL» на панели инструментов. На экране появится текущий запрос в режиме SQL эквивалентный созданному в режиме конструктора (рис. 22).

Запрос «Список студентов группы ДФД-31» в MS Access в режиме SQL

3. В этот запрос можно внести изменения, и эти изменения будут отражены в бланке конструктора. Например, попробуем удалить в текстовом окне режима SQL записи Студент.Адрес, Студент.[Домашний телефон], Студент.[Балл при поступлении].

Результат выполнения измененного запроса «Список студентов группы ДФД-31»

Основной инструкцией языка SQL, всегда содержащейся в запросе, является команда SELECT [2].

В простейшей форме эта команда занимается поиском информации в таблице. Она имеет следующий формат:

SELECT field1, field 2, …

FROM Table;

Здесь field1, field 2,… – список столбцов таблицы Table, которые должны быть представлены в результате запроса.

Для получения всей таблицы вместо списка столбцов необходимо поставить символ «*» (звездочка).

Команда SELECT имеет следующие параметры:

A. DISTINCT (получить список без повторений)

Формат: SELECT DISTINCT field1, field2, …

FROM Table;

B. ALL (получить список со всеми повторениями)

Формат: SELECT ALL field1, field2, …

FROM Table;

C. WHERE (извлечь нужные строки)

Формат: SELECT field1, field2, …

FROM Table WHERE predicate;

Здесь predicate – логическое выражение, которое может быть истинно или ложно для каждой записи таблицы.

D. ORDER BY (рассортировать выходные данные)

Формат: SELECT field1, field2, …

FROM Table

ORDER BY field1 DESC;

Это означает, что выходные данные будут рассортированы по столбцу field1 в порядке убывания (порядок возрастания задается по умолчанию или с помощью слова ASC).

E. GROUP BY (группировать выходные данные)

Формат: SELECT field1, field2, …

FROM Table

GROUP BY [field1, field2, …]

ORDER BY field1 DESC;

Группировка – это объединение записей в соответствии со значениями некоторого заданного поля.

Агрегатные функции

Существуют следующие основные агрегатные функции:

· Count – определение численности;

· Sum – определение суммы;

· First/Last – определение первого/последнего значения;

· Min/Max – определение минимума/максимума;

· Avg – определение среднего значения.

Для обозначения связи двух таблиц дополнительно к команде FROM используются атрибуты INNER JOIN и ON.

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

С помощью атрибута INNER JOIN мы указали, что таблица «Студент» связана с таблицей «Группа». А с помощью атрибута ON мы указали, как именно связаны между собой две таблицы «Студент» и «Группа»: по полю Код группы (FROM Группа INNER JOIN Студент ON Группа.[Код группы] = Студент.[Код группы]).

С помощью атрибута WHERE мы указали, что нужно извлечь только строки, которые содержат запись в поле Группа.[Обозначение группы] «ДФД-31»: WHERE (((Группа.[Обозначение группы])="ДФД-31")).

ПРИМЕЧАНИЕ. Обратите внимание на то, что в качестве имени поля всегда используется то имя, которое было присвоено полю в процессе создания таблицы в режиме конструктора, а не надпись, которую мы видим на экране в таблице в режиме заполнения.

4. Запрос может быть создан также только исключительно через Режим SQL в конструкторе.

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

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

Результат запроса для получения списков всех предметов и их кодов

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

Рассмотрим запрос «План проведения занятий в группе», созданный на основании анализа предметной области:

SELECT Группа.[Обозначение группы], Предмет.[Наименование предмета], Преподаватель.Фамилия, Преподаватель.Имя, Преподаватель.Отчество, Преподаватель.[Табельный номер], [Учебный план].Часы, [Учебный план].[Вид занятия], [Учебный план].Семестр

FROM (Группа INNER JOIN Студент ON Группа.[Код группы] = Студент.[Код группы]) INNER JOIN (Преподаватель INNER JOIN (Предмет INNER JOIN ([Учебный план] INNER JOIN Успеваемость ON [Учебный план].[Код учебного плана] = Успеваемость.[Код учебного плана]) ON Предмет.[Код предмета] = [Учебный план].[Код предмета]) ON Преподаватель.[Код преподавателя] = [Учебный план].[Код преподавателя]) ON Студент.[Код студента] = Успеваемость.[Код студента]

GROUP BY Группа.[Обозначение группы], Предмет.[Наименование предмета], Преподаватель.Фамилия, Преподаватель.Имя, Преподаватель.Отчество, Преподаватель.[Табельный номер], [Учебный план].Часы, [Учебный план].[Вид занятия], [Учебный план].Семестр

ORDER BY Группа.[Обозначение группы], Предмет.[Наименование предмета], Преподаватель.Фамилия;

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

Аналогично, следует подготовить запрос «Экзаменационная ведомость», созданный на основании анализа предметной области (см. рис. 5):

SELECT Предмет.[Наименование предмета], Группа.[Обозначение группы], Студент.[Номер зачетной книжки], Студент.Фамилия, Студент.Имя, Студент.Отчество, Успеваемость.Оценка, Успеваемость.[Дата сдачи], [Учебный план].Семестр, [Учебный план].[Вид сдачи]

FROM (Группа INNER JOIN Студент ON Группа.[Код группы] = Студент.[Код группы]) INNER JOIN (Преподаватель INNER JOIN (Предмет INNER JOIN ([Учебный план] INNER JOIN Успеваемость ON [Учебный план].[Код учебного плана] = Успеваемость.[Код учебного плана]) ON Предмет.[Код предмета] = [Учебный план].[Код предмета]) ON Преподаватель.[Код преподавателя] = [Учебный план].[Код преподавателя]) ON Студент.[Код студента] = Успеваемость.[Код студента]

WHERE ((([Учебный план].[Вид сдачи])="экзамен" Or ([Учебный план].[Вид сдачи])="Зачет"))

ORDER BY Предмет.[Наименование предмета], Группа.[Обозначение группы], Студент.Фамилия;

Рассмотрим для примера запрос «Успеваемость студентов в группах по предметам у преподавателей». Запишем текст запроса на языке SQL в окно Режим SQL:

SELECT Группа.[Обозначение группы], Студент.[Номер зачетной книжки], Студент.Фамилия, Студент.Имя, Студент.Отчество, Преподаватель.Фамилия, Преподаватель.Имя, Преподаватель.Отчество, Предмет.[Наименование предмета], Avg(Успеваемость.Оценка) AS [Avg-Оценка]

FROM (Группа INNER JOIN Студент ON Группа.[Код группы] = Студент.[Код группы]) INNER JOIN (Преподаватель INNER JOIN (Предмет INNER JOIN ([Учебный план] INNER JOIN Успеваемость ON [Учебный план].[Код учебного плана] = Успеваемость.[Код учебного плана]) ON Предмет.[Код предмета] = [Учебный план].[Код предмета]) ON Преподаватель.[Код преподавателя] = [Учебный план].[Код преподавателя]) ON Студент.[Код студента] = Успеваемость.[Код студента]

GROUP BY Группа.[Обозначение группы], Студент.[Номер зачетной книжки], Студент.Фамилия, Студент.Имя, Студент.Отчество, Преподаватель.Фамилия, Преподаватель.Имя, Преподаватель.Отчество, Предмет.[Наименование предмета]

HAVING (((Группа.[Обозначение группы])="ДФД-31"));

Результатом запроса будут сведения о средней успеваемости студентов в групп ДФД-31 у всех преподавателей во всех семестрах.

Для задания ограничений на создаваемые группы совместно с ключевым словом GROUP BY может использоваться предложение HAVING. Оно должно следовать после GROUP BY, но до предложения ORDER BY (если оно присутствует в запросе).

Сохраним запрос и просмотрим результаты этого запроса.

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