 |
Каскадное обновление и удаление связанных записей
|
|
|
|
Если для выбранной связи обеспечивается поддержание целостности, можно задать режим каскадного обновления связанных полей и режим каскадного удаления связанных записей.
В режиме каскадного обновления связанных полей при изменении значения поля связи в записи главной таблицы, 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, будут оправдываться в течение долгого времени, потому что быстро сходить со сцены этот язык не собирается.
|
|
|
