Файл: Методическое пособие к выполнению самостоятельной и лабораторных работ и курсового проекта по дисциплине Организация баз данных для студентов направления подготовки бакалавров.pdf

Порядок выполнения работы
В ходе выполнения работы необходимо создать отчет, содержа- щий подчиненный отчет.
Для создания отчета в окне базы данных выберите вкладку «От- четы» и нажмите кнопку «Создание отчета с помощью мастера».
В первом окне мастера отчетов выберите имя таблицы или запро- са, содержащих данные, по которым строится отчет. MS Access по умолчанию использует эту таблицу или запрос как базовый источник данных для отчета. Для удобства используйте тот же источник запи- сей, что и для главной формы в предыдущей лабораторной работе.
В следующем окне (Рисунок 15) установите уровни группировки данных (если необходимо). Нажмите кнопку «Далее».
Рисунок 15 – Мастер отчетов, окно группировки

28
В следующем окне выберите способ сортировки данных в отчете, нажмите кнопку «Далее» и следуйте дальнейшим инструкциям масте- ра отчетов.
Для изменения макета отчета откройте его в режиме конструкто- ра (Рисунок 16).
Рисунок 16 – Отчет, открытый в режиме конструктора
Чтобы изменить внешний вид отчета, нажмите кнопку Автофор- мат на панели инструментов и выберите новый внешний вид для отчета.
Для изменения внешнего вида одного элемента управления, например надписи, выделите его, после чего на панели инструментов
Форматирование вы- берите другой шрифт, размер шрифта или другие параметры.
Для изменения формата отображения данных в элементе управ- ления, например в поле, убедитесь, что данный элемент выделен и нажмите кнопку Свойства на панели инструментов для вывода окна свойств.
При необходимости добавьте в отчет другие поля из таблицы, яв- ляющейся источником данных отчета следующим образом: нажмите кнопку Список полей на панели инструментов для отображения списка всех полей базовой таблицы и с помощью мыши «перенесите» выбранное поле в отчет.
Создайте заголовок и примечание отчета, куда добавьте надпись, содержащую Ваши ФИО и № группы. Чтобы создать надпись, нажми- те кнопку Надпись на панели элементов. Затем выберите в отчете место, куда ее следует поместить, введите нужный текст и нажмите клавишу Enter.

29
Поменяйте порядок сортировки и группировки данных в отчете, для этого нажмите кнопку Сортировка и группировка на панели инструментов для вывода диалогового окна (Рисунок 17).
Рисунок 17 – Окно сортировки и группировки данных
Добавьте новые поля для группировки и сортировки, создайте за- головки и примечание групп.
Для создания подчиненного отчета нажмите кнопку Подчиненная форма/отчет на панели элементов, затем установите указатель в отчете на том месте, куда требуется поместить подчиненный отчет, и нажмите кнопку мыши. Выполняйте инструкции, выводящиеся в диа- логовых окнах мастера.
После нажатия кнопки «Готово» элемент управления «Подчинен- ная форма/отчет» будет вставлен в главный отчет. Кроме того, будет создан отдельный отчет, выводящийся как подчиненный отчет.
При включении в главный отчет подчиненного отчета, содержа- щего данные, относящиеся к данным в главном отчете, необходимо установить связь подчиненного отчета с главным. Связь обеспечивает соответствие записей, выводящихся в подчиненном отчете, записям в главном отчете.
При создании подчиненного отчета с помощью мастера или пу- тем переноса с помощью мыши отчета или таблицы в другой отчет,
MS Access автоматически выполняет синхронизацию главного и под- чиненного отчета в следующих случаях:
Так же как и при создании подчиненных форм следует учитывать, что отчеты базируются на таблицах, между которыми установлены связи в окне Схема данных. Для подчиненного отчета используйте источник записей такой же, как и для подчиненной формы. Помните, что при создании отчетов на основе запроса или запросов синхрониза- ция отчета с подчиненным отчетом выполняется автоматически, если связи установлены для базовых таблиц запроса или запросов. Если связи базовых таблиц установлены корректно, MS Access выполнит синхронизацию главного и подчиненного отчетов автоматически.

30
Главный отчет основан на таблице, содержащей ключевое поле, а подчиненный отчет базируется на таблице, содержащей поле с тем же именем и с тем же или совместимым типом данных. Это же условие должно выполняться для базовых таблиц запросов, если отчеты бази- руются на запросах.
При связывании главного и подчиненного отчетов Microsoft
Access использует свойства Основные поля (LinkMasterFields) и Под- чиненные поля (LinkChildFields) элемента управления «Подчиненная форма/отчет». Если по каким-либо причинам Microsoft Access не свя- зывает главный и подчиненный отчет автоматически, пользователь имеет возможность задать значения этих свойств самостоятельно.
Для открытия отчета в сложной форме создайте кнопку открытия отчета для просмотра, используя для этого мастер кнопок, по методи- ке, изложенной в лабораторной работе № 3.

31
Лабораторная работа № 5 «Создание SQL-
запросов»
Тема: Создание SQL-запросов.
Раздел дисциплины: Реляционные языки.
Цель работы: создать SQL-запросы на создание таблицы, на
выборку с параметрами, на обновление записей, на удаление записей,
на добавление данных, на удаление таблицы, на создание индексов.
Основы SQL
Запрос SQL — это запрос, создаваемый при помощи инструкций
SQL. Язык SQL (Structured Query Language) используется при созда- нии запросов, а также для обновления и управления реляционными
БД.
В среде MS Access, когда пользователь создает запрос в режиме конструктора запроса (с помощью построителя запросов), MS Access автоматически создает эквивалентную инструкцию SQL. Фактически, для большинства свойств запроса, доступных в окне свойств в режиме конструктора, имеются эквивалентные предложения или параметры языка SQL, доступные в режиме SQL. При необходимости, пользова- тель имеет возможность просматривать и редактировать инструкции
SQL в режиме SQL. После внесения изменений в запрос в режиме SQL его вид в режиме конструктора может измениться.
Некоторые запросы, которые называют запросами SQL, невоз- можно создать в бланке запроса. Для запросов к серверу, управляю- щих запросов и запросов на объединение необходимо создавать ин- струкции SQL непосредственно в окно запроса в режиме SQL. Для подчиненного запроса пользователь должен ввести инструкцию SQL в строку Поле или Условие отбора в бланке запроса.
Синтаксиса написания SQL-предложений:

в описании команд слова, написанные прописными латински- ми буквами, являются зарезервированными словами SQL;

фрагменты SQL-предложений, заключенные в фигурные скоб- ки и разделенные символом «

», являются альтернативными. При формировании соответствующей команды для конкретного случая необходимо выбрать одну из них;

фрагмент описываемого SQL-предложения, заключенный в квадратные скобки [ ], имеет необязательный характер и может не ис- пользоваться;

многоточие …, стоящее перед закрывающейся скобкой, говорит о том, что фрагмент, указанный в этих скобках, может быть повторен;

32
Описание команд SQL
Выборка записей
Инструкция SELECT. При выполнении инструкции SELECT
СУБД находит указанную таблицу или таблицы, извлекает заданные столбцы, выделяет строки, соответствующие условию отбора, и сорти- рует или группирует результирующие строки в указанном порядке в виде набора записей.
Синтаксис команды:
SELECT [предикат] { * | таблица.* | [таблица.]поле_1
[AS псевдоним_2] [, [таблица.]поле_2[AS псевдоним_2] [, ...]]}
FROM выражение [, ...]
[WHERE... ]
[GROUP BY... ]
[HAVING... ]
[ORDER BY... ] где предикат — один из следующих предикатов отбора: ALL,
DISTINCT, DISTINCTROW, TOP. Данные ключевые слова использу- ются для ограничения числа возвращаемых записей. Если они отсут- ствуют, по умолчанию используется предикат ALL;
* указывает, что результирующий набор записей будет содержать все поля заданной таблицы или таблиц. Следующая инструкция отби- рает все поля из таблицы «Студенты»: SELECT * FROM Студенты; таблица — имя таблицы, из которой выбираются записи; поле_1, поле_2 — имена полей, из которых должны быть отобра- ны данные; псевдоним_1, псевдоним_2 — ассоциации, которые станут заго- ловками столбцов вместо исходных названий полей в таблице; выражение — имена одной или нескольких таблиц, которые со- держат необходимые для отбора записи; предложение GROUP BY в SQL-предложении объединяет записи с одинаковыми значениями в указанном списке полей в одну запись.
Если инструкция SELECT содержит статистическую функцию SQL, например Sum или Count, то для каждой записи будет вычислено ито- говое значение; предложение HAVING определяет, какие сгруппированные запи- си, выданные в результате выполнения запроса, отображаются при использовании инструкции SELECT с предложением GROUP BY. По- сле того как записи результирующего набора будут сгруппированы с помощью предложения GROUP BY, предложение HAVING отберет те

33
из них, которые удовлетворяют условиям отбора, указанным в пред- ложении HAVING; предложение ORDER BY позволяет отсортировать записи, полу- ченные в результате запроса, в порядке возрастания или убывания на основе значений указанного поля или полей.
Следует отметить, что инструкции SELECT не изменяют данные в базе данных. Приведем минимальный синтаксис инструкции
SELECT: SELECT поля FROM таблица.
Если несколько таблиц, включенных в предложение FROM, со- держат одноименные поля, перед именем такого поля следует ввести имя таблицы и оператор « . » (точка). Предположим, что поле «Но- мер_группы» содержится в таблицах «Студенты» и «Группы». Следу- ющая инструкция
SQL отберет поле
«Номер_группы» и
«ФИО_студента» из таблицы «Студенты» и «ФИО_куратора» из таб- лицы «Группы» при номере группы, равном 432-1:
SELECT Группы.Номер_группы, Группы.ФИО_куратора, Сту- денты.ФИО_студента
FROM Группы, Студенты
WHERE Группы.Номер_группы = Студенты.Номер_группы AND
На рисунке 17 приведен пример выполнения данного запроса.
Таблицы БД
СТУДЕНТЫ
Номер_зачет-
ной книжки
ФИО_студента
Дата
рождения
Место
рождения
Но-
мер_группы
1992412-11
Карасев А.А.
27.08.75 г. Чита
412-1 1992432-11
Данилов О. В.
27.08.75 г. Алматы
432-1 1992432-12
Раевский А. И.
20.05.75 г. Бишкек
432-1 1992432-22
Глазов О.А
04.07.75 г. Киров
432-1
ГРУППЫ
Номер_Группы
ФИО_куратора
412-1
Самойлов С.С.
432-1
Авдеев Р.М
Результат выполнения запроса
Номер_группы
ФИО_куратора
ФИО_студента
432-1
Авдеев Р.М
Данилов О. В.
432-1
Авдеев Р.М
Раевский А. И.
432-1
Авдеев Р.М
Глазов О.А
Рисунок 17 – Пример выполнения запроса на выборку

34
Помимо обычных знаков сравнения (=,<,>,<=,>=, <>) в языке SQL в условии отбора используются ряд ключевых слов:
Is not null — выбрать только непустые значения;
Is null — выбрать только пустые значения;
Between … And определяет принадлежность значения выражения указанному диапазону.
Синтаксис: выражение [Not] Between значение_1 And значение_2 , где выражение — выражение, определяющее поле, значение которого проверяется на принадлежность к диапазону; значение_1, значение_2 – выражения, задающие границы диапа- зона.
Если значение поля, определенного в аргументе выражение, по- падает в диапазон, задаваемый аргументами значение_1 и значение_2
(включительно), то оператор Between...And возвращает значение True; в противном случае возвращается значение False. Логический опера- тор Not позволяет проверить противоположное условие: что выраже- ние находится за пределами диапазона, заданного с помощью аргумен- тов значение_1 и значение_2.
Оператор Between...And часто используют для проверки: попадает ли значение поля в указанный диапазон чисел. В следующем примере выдается список студентов, получающих стипендию от 800 до 900 рублей:
SELECT ФИО_студента, Размер_стипендии
FROM Студенты
WHERE Размер_стипендии Between 800 And 900
На рисунке 18 приведен результат выполнения запроса.
СТУДЕНТЫ
Номер_зачетной книжки ФИО_студента
Размер_стипендии
1992412-11
Карасев А.А.
900 1992432-11
Данилов О. В.
800 1992432-12
Раевский А. И.
950 1992432-22
Глазов О.А
850
Результирующий набор данных
ФИО_студента
Размер_стипендии
Карасев А.А.
900
Данилов О. В.
800
Глазов О.А
850
Рисунок 18 – Результат выполнения запроса на выборку с использованием операторов Between...And

35
Если выражение, значение_1 или значение_2 имеет значение Null, оператор Between...And возвращает значение Null.
Оператор Like используется для сравнения строкового выражения с образцом.
Синтаксис: выражение Like «образец», где выражение — выражение SQL, используемое в предложении
WHERE; образец — строка, с которой сравнивается выражение.
Оператор Like используется для нахождения в поле значений, соответ- ствующих указанному образцу. Для аргумента образец можно задавать полное значение (например, Like «Иванов») или использовать подстано- вочные знаки для поиска диапазона значений (например, Like "Ив*").
Приведем перечень подстановочных символов и пример их ис- пользования в языке Jet SQL согласно документации Microsoft (рису- нок 19).
Тип совпадения
Образец
Совпадение
(True)
Несовпадение
(False)
Несколько символов a*a aa, aBa, aBBBa aBC
*ab* abc, AABB, Xab aZb, bac
Специальный символ a[*]a a*a aaa
Несколько символов ab* abcdefg, abc cab, aab
Одиночный символ a?a aaa, a3a, aBa aBBBa
Одиночная цифра a#a a0a, a1a, a2a aaa, a10a
Диапазон символов
[a-z] f, p, j
2, &
Вне диапазона
[!a-z]
9, &, % b, a
Не цифра
[!0-9]
A, a, &,
0, 1, 9
Комбинированное выражение a[!b-m]#
An9, az0, a99 abc, aj0
Рисунок 19 – Параметры оператора Like
Внутреннее соединение
Операция INNER JOIN объединяет записи из двух таблиц, если связующие поля этих таблиц содержат одинаковые значения.
Синтаксис операции:
FROM таблица_1 INNER JOIN таблица_2 ON таблица_1.поле_1 оператор таблица_2.поле_2 где таблица_1, таблица_2 — имена таблиц, записи которых подлежат объединению; поле_1, поле_2 — имена объединяемых полей. Поля должны иметь одинаковый тип данных и содержать данные одного рода,

36
однако эти поля могут иметь разные имена; оператор — любой оператор сравнения: "=," "<," ">," "<=," ">=," или "<>".
Операцию INNER JOIN можно использовать в любом предложе- нии FROM. Это самые обычные типы связывания. Они объединяют записи двух таблиц, если связующие поля обеих таблиц содержат оди- наковые значения. Предыдущий пример использования команды
SELECT можно записать с использованием конструкции INNER JOIN следующим образом:
SELECT Группы.Номер_группы, Группы.ФИО_куратора, Сту- денты.ФИО_студента
FROM Группы INNER JOIN Студенты
ON Группы.Номер_группы = Студенты.Номер_группы;
Внешнее соединение
Операции LEFT JOIN, RIGHT JOIN объединяют записи исход- ных таблиц при использовании в любом предложении FROM.
Операция LEFT JOIN используется для создания внешнего со- единения, при котором все записи из первой (левой) таблицы включа- ются в результирующий набор, даже если во второй (правой) таблице нет соответствующих им записей.
Операция RIGHT JOIN используется для создания внешнего объ- единения, при котором все записи из второй (правой) таблицы вклю- чаются в результирующий набор, даже если в первой (левой) таблице нет соответствующих им записей.
Синтаксис операции:
FROM таблица_1 [ LEFT | RIGHT ] JOIN таблица_2
ON таблица_1.поле_1 оператор таблица_2.поле_2
Например, операцию LEFT JOIN можно использовать с таблица- ми «Студенты» (левая) и «Задолженность_за_обучение» (правая) для отбора всех студентов, в том числе тех, которые не являются задолж- никами:
SELECT Студенты.ФИО_студента,
Задолженность_за_обучение.Сумма_задолженности
FROM Студенты LEFT JOIN Задолженность_за_обучение
ON Студенты.Номер_зачетной_книжки =
Задолженность_за_обучение.Номер_зачетной_книжки;
Поле «Номер_зачетной_книжки» в этом примере используется для объединения таблиц, однако, оно не включается в результат вы-

37
полнения запроса, поскольку не включено в инструкцию SELECT.
Чтобы включить связующее поле (в данном случае поле «Но- мер_зачетной_книжки») в результат выполнения запроса, его имя необходимо включить в инструкцию SELECT.
Важно отметить, что операции LEFT JOIN или RIGHT JOIN мо- гут быть вложены в операцию INNER JOIN, но операция INNER JOIN не может быть вложена в операцию LEFT JOIN или RIGHT JOIN.
Перекрестные запросы
В некоторых СУБД (в частности в MS Access) существует такой вид запросов как перекрестный. В перекрестном запросе отображают- ся результаты статистических функций — суммы, средние значения и др., а также количество записей. При этом подсчет выполняется по данным из одного полей таблицы. Результаты группируются по двум наборам данных, один из которых расположен в левом столбце табли- цы, а другой в заголовке таблицы. Например, при необходимости вы- числить средний балл студентов за семестр, обучающихся на разных кафедрах, необходимо реализовать перекрестный запрос, в результате выполнения которого будет создана таблица, где заголовками строк будут служить номер семестра, заголовками столбцов — названия ка- федр, а в полях таблицы будет рассчитан средний балл.
Для создания перекрестного запроса необходимо использовать следующую инструкцию:
TRANSFORM статистическая_функция инструкция_SELECT
PIVOT поле [IN (значение_1[, значение_2[, ...]])], где статистическая_функция — статистическая функция SQL, обраба- тывающая указанные данные; инструкция_SELECT — запрос на выборку; поле — поле или выражение, которое содержит заголовки столб- цов для результирующего набора; значение_1, значение_2 — фиксированные значения, используе- мые при создании заголовков столбцов.
Составим SQL-запрос, реализующий описанный выше пример. В качестве исходного набора данных используется таблица Успевае- мость (Рисунок 20).

38
Рисунок 20 – Таблица УСПЕВАЕМОСТЬ
В результате выполнения нижеприведенного перекрестного SQL- запроса формируется следующая таблица (Рисунок 20):
TRANSFORM AVG(Успеваемость.Оценка) AS Сред_балл
SELECT Успеваемость.Семестр
FROM Успеваемость
GROUP BY Успеваемость.Семестр
PIVOT Успеваемость.Кафедра
Рисунок 21 – Результат выполнения перекрестного запроса
Таким образом, когда данные сгруппированы с помощью пере- крестного запроса, можно выбирать значения из заданных столбцов или выражений и определять как заголовки столбцов. Это позволяет просматривать данные в более компактной форме, чем при работе с запросом на выборку.

1   2   3   4   5   6