Системы управления базами данных

Язык 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 эквивалентный созданному в режиме конструктора.

Запрос «Список студентов группы ДФД-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.

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

С помощью атрибута 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 Группа.[Обозначение группы], Предмет.[Наименование предмета], Преподаватель.Фамилия;

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

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

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

Использование форм и  отчетов для создания приложений в MS Access

Формы предназначены для вывода данных на экран в удобном виде, форма может использоваться для поиска данных. Если изъять формы из MS Access, то программа превратится в заурядную СУБД, каких множество. С одной стороны, формы позволяют пользователям вводить данные в таблицы базы данных без непосредственного доступа к самим таблицам. С другой стороны, они позволяют выводить результаты работы запросов не в виде скупых результирующих таблиц, а в виде красиво оформленных форм. В связи с таким разделением существует два вида формирования структуры форм: на основе таблицы и на основе запроса, хотя возможен и комбинированный подход, – это вопрос творчества.

Создадим форму «Список студентов  по группам» в режиме мастера форм (одиночная на основании таблицы  «Студент», в столбец). Перед тем, как создать эту форму, создадим запрос «Список студентов по группам»:

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

FROM Группа INNER JOIN Студент ON Группа.[Код  группы]=Студент.[Код группы]

ORDER BY Студент.[Номер зачетной книжки], Студент.Фамилия;

После создания, переименуйте эту  форму как «Список студентов  по группам» и просмотрите данные через форму.

Формы предназначены и для заполнения базы данных пользователями. Создадим в режиме автоформы формы «Группа» и «Студент» и введем в формы данные . В соответствующих таблицах базы данных появились новые, введенные нами, данные для группы ДХГ-31.

Аналогично в режиме автоформы следует создать формы «Кафедра», «Преподаватель», «Предмет», «План», «Успеваемость».

Заполнение таблицы «Группа» базы через автоформу «Группа»

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

Создадим отчет «Список студентов по группам» в режиме мастера отчетов на основании запроса «Список студентов по группам», выполненного ранее. Отредактируем в режиме конструктора отчет, если это требуется, чтобы привести отчет в пригодный для печати вид.

Аналогично, следует создать  отчет «Список преподавателей по кафедрам» в режиме мастера отчетов на основании запроса «Список преподавателей по кафедрам».

 

III. ПОСТРОЕНИЕ НЕБОЛЬШОЙ БАЗЫ ДАННЫХ

 

Для того, чтобы лучше понять Access, построим свою Базу данных. Сначала определимся, какую информацию будет содержать наша База данных. База данных может содержать информацию о чем угодно: о зарплате, о торговых поставках, о больных в поликлинике, о студентах ВУЗа, о друзьях, о книгах в вашей библиотеке. Мы построим самую простую - о студентах какого-нибудь ВУЗа. Эта База данных будет состоять из трех взаимосвязанных таблиц:

1. Список ВУЗов, который будут содержать такую информацию: