Информатика и базы данных. Создание базы данных в MS Access (тема № 3)

1. Информатика и базы данных

Москин Николай Дмитриевич
Институт математики и информационных
технологий, ПетрГУ
1

2. § 3 Создание базы данных в MS Access

Основными операциями с БД являются: создание,
первичное заполнение и последующее
редактирование таблиц, сортировка данных и
поиск. Для выполнения этих операций служат
инструментальные пакеты программ – системы
управления базами данных (СУБД).
MS Access является одной из самых популярных
СУБД и входит в состав Microsoft Office. При
создании новой БД или открытии готовой на
экране появляется основное окно, содержащее
вкладки, соответствующие типам объектов БД.
2

3. Основное окно

3

4. Способы создания таблиц

Мастер создания таблиц предлагает
стандартные варианты (шаблоны) таблиц с
готовыми списками полей.
Импорт таблиц из внешнего файла в текущую
базу данных.
Создание таблицы путем ввода данных (пустая
таблица с полями «Поле1», «Поле2» и т.д.).
Режим конструктора.
4

5. Окно конструктора таблиц

5

6. Ключевое поле

Обозначим поле
"Номер предприятия"
как ключевое. Для
этого надо
установить курсор в
соответствующую
этому полю строку и
щелкнуть по
графической
кнопке с
изображением ключа.
6

7. Типы данных

Текстовой тип используется для хранения
обычного неформатированного текста
ограниченного размера (до 255 символов);
Поле MEMO – для хранения больших объемов
текста (до 65535 символов);
Числовой – для хранения действительных чисел;
Дата/время – для хранения календарных дат и
текущего времени;
Денежный – для хранения денежных сумм.
7

8. Типы данных

Счетчик – для порядковой нумерации записей.
Это уникальные (не повторяющиеся натуральные
числа с автоматическим наращиванием).
Логический – для хранения логических данных
(истина/ложь).
Поле объекта OLE – для хранения
мультимедийных объектов: изображений,
документов, диаграмм и т.п. (объектов OLE).
Гиперссылка – для хранения URL-адресов для
Web-объектов (при щелчке на ссылке происходит
автоматический запуск браузера с этой
страницей).
8

9. Типы данных

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

10. Заполнение таблицы

Определив в режиме Конструктора структуру
таблицы, можно начать ее заполнение. Для этого
надо перейти в режим таблицы с помощью
кнопки «Режимы», расположенной слева на
вкладке «Главная» (тот же эффект достигается
при использовании команды «Режим таблицы»
контекстного меню).
В таблице «Предприятия» пока нет ни одной
записи, поэтому открывается окно таблицы с
одной пустой строкой, куда можно начать
вводить данные.
10

11. Схема данных

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

12. Окно связи

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

13. Обеспечение целостности

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

14. Схема данных

После завершения процедуры в окне Схемы
данных можно увидеть связь типа "один-ко-многим"
(см. символы "1" и "∞" со стороны главной и
подчиненной таблиц, соответственно).
14

15. Средства работы с таблицами

При работе с большими таблицами удобно
использовать простые средства упорядочения
информации, ее поиска и отбора:
Сортировка записей;
Контекстный поиск и замена (? – один символ, * любая последовательность символов, # - одна
цифра);
Фильтры – предназначены для отбора записей
таблицы при выводе на экран (Записи – Фильтры).
15

16. Фильтры

Пример 1. Вывести на экран данные только о тех
предприятиях, правления которых находятся в
Петербурге.
Выделим в поле "Местоположение правления" слово
Петербург в любой записи, где это слово есть,
затем щелкнем по кнопке («Выделение»)
в
группе «Сортировка и фильтр» на вкладке
«Главная» и выберем первый из двух предложенных
вариантов (Содержит "Петербург" / Не содержит
"Петербург") – на экране останутся только те записи,
которые содержат выделенное слово Петербург в
любом месте поля "Местоположение правления".
16

17. Обратный фильтр

Фильтр по выделенному фрагменту можно
“включать” и “выключать” также с помощью
контекстного меню. Дополнительная полезная
функция фильтра по выделенному – не
разрешать, а исключать вывод на экран
записей, удовлетворяющих поставленному
условию.
Более широкие возможности дает команда
«Параметры расширенного фильтра»,
доступная при нажатии графической кнопки.
17

18. Запросы в СУБД MS Access

Запросы являются универсальным средством поиска
и анализа данных, хранящимся в таблицах БД.
Запросы позволяют производить отбор записей
таблицы и вывод на экран, группировать данные,
выполнять вычисления, используя одновременно
несколько связанных таблиц.
Чтобы начать работу по созданию запроса, надо
перейти в группу команд «Запросы» на вкладке
«Создание». Два основных способа создания
запросов: Мастер запросов и Конструктор запросов.
18

19. Окно конструктора запроса

19

20. Окно конструктора запроса

Окно запроса разделено на две области: в
верхней отображается схема данных, состоящая
из добавленных в запрос таблиц с
установленными связями, а в нижней находится
бланк запроса:
Строка «Поле» предназначена для ввода имени
поля таблицы-источника данных («*» обозначает сразу все поля одной таблицы);
«Имя таблицы» содержит имя таблицы, которой
принадлежит выбранное поле;
20

21. Окно конструктора запроса

«Сортировка» - порядок сортировки поля;
«Вывод на экран» - флажок, указывающий, будет
ли выводится соответствующее поле запроса на
экран;
«Условие отбора» - логическое выражение,
например:
>30;
=10;
=«Москва» OR «Санкт-Петербург».
21

22. Запросы на выборку

Рассмотрим таблицу
«Предприятия» с
полями: Номер
предприятия, Название,
Год открытия,
Местоположение,
Основной капитал.
а) Получить алфавитный список предприятий с
указанием года основания каждого из них.
22

23. Запросы на выборку

б) Найти «возраст» каждого предприятия в 2019
году. Добавим поле «Длительность работы к 2019
году: 2019 – [Год открытия действия]».
в) Выборка предприятий, основанных после 1999
года (условие в «Год открытия»: Условие отбора
> 1999).
г) Выборка предприятий, правления которых
расположены в Санкт-Петербурге. В поле
«Местоположение правления»: условие *С.Петербург*. Затем программа автоматически
добавит Like.
23

24. Сложные условия

д) Выборка предприятий, расположенных в СанктПетербурге, Москве или Варшаве. Условие отбора:
«*C.-Петербург*» Or «*Москва*» Or «*Варшава*».
Или включить три условия, занимающие три
строки, т.е. неявно соединенные оператором ИЛИ.
24

25. Сложные условия

е) Изменим условия предыдущего запроса: год
основания должен быть больше 1999 и меньше
2005. Добавим условие отбора: >1999 and <2005.
ж) Также можно изменить «Местоположение
предприятия»: Not *С.-Петербург*.
з) Выборка членов правления, где не указан
возраст. Вывести на экран поля "Имя", "Возраст" и
"Номер предприятия". Условие отбора: Is Null (Is
not null).
25

26. Запросы с групповыми операциями

Рассмотрим таблицу «Члены правления» с полями
Номер, Имя, Должность, Возраст, № предприятия.
Подсчитать число членов правления каждого
предприятия в базе данных.
Добавим "Групповые операции", для чего перейдем
в группу «Показать или скрыть» на вкладке
«Конструктор» (работа с запросами), которая
появляется в режиме Конструктор.
26

27. Запросы с групповыми операциями

Установим в поле
«Имя» (Групповая
операция) – Count,
а в поле «Номер
предприятия» Группировка.
27

28. Запросы с групповыми операциями

Усложним запрос. Подсчитаем не только количество
членов правления каждого предприятия, но и их
средний возраст.
Добавим поле «Возраст» и в строке «Групповые
операции» для этого поля выберем операцию Avg
(от англ. Average).
28

29. Запросы с групповыми операциями

Средний возраст
для предприятий с
номерами 8 и 9 не
найден, поскольку в
исходных данных
сведения о возрасте
членов правлений
этих предприятий
отсутствуют.
29

30. Комбинирование групповых операций и условий отбора

Подсчитать количество членов правления на
предприятиях, где в правлении было больше
четырех человек. В строке «Условия отбора» для
первого столбца надо поставить условие: >4.
30

31. Запросы с параметрами

Запрос с параметрами – это запрос, при
выполнении которого появляется диалоговое
окно, в котором пользователю предлагается
ввести данные, которые подставляются в запрос
вместо определенных в нем параметров.
Условие отбора: > [Введите возраст:]
31

32. Перекрестные запросы

Перекрестные запросы используются для
расчета и представления данных в структуре,
облегчающей их анализ.
Такие запросы подсчитывают сумму, среднее,
число значений или выполняют другие
статистические расчеты, после чего результаты
группируются в виде таблицы по двум наборам
данных: один определяет заголовки столбцов,
другой – строк.
32

33. Перекрестные запросы

Создать – Перекрестный запрос
1. Выбор таблицы/запроса
2. Выбор полей для заголовков строк (<=3)
3. Выбор поля для заголовков столбцов
4. Выбор функции для выполнения значений на
пересечении строк и столбцов (максимум,
дисперсия, среднее, сумма и т.д.) + вывод
итогового значения.
33

34. Перекрестные запросы

Фамилия
Имя
Отчество
л/ц
мех/ц
Агапитов
Николай
Григорьевич
Агеев
Павел
Федорович
Аксентьев
Владимир
Константинович
Акулов
Андрей
Иванович
слесарь
Антипов
Александр
Павлович
токарь
…
…
…
ст/ц
плотник
слесарь
ученик
…
…
…
34

35. Запросы на выполнение действий

Запросы на выполнение действий предназначены
для автоматизации переноса данных из одного
файла (базы данных, таблицы, запроса) в другой.
В результате их выполнения исходные данные,
хранящиеся в БД, определенным образом
модифицируются.
Меню Запрос/Тип запроса: создание таблицы,
запрос на обновление, запрос на добавление, запрос
на удаление.
35

36. Создание таблицы (на основе другой таблицы)

В поле «Имя таблицы» вводится имя таблицы,
которую требуется создать или заменить. Далее
устанавливается параметр «в текущей БД» или «в
другой БД».
Перетащите в бланк запроса те поля, которые нужно
добавить в новую таблицу и введите условия отбора.
36

37. Запрос на обновление

Перетащите в
бланк запроса
поля, которые
нужно обновить
или для которых
следует указать
условия отбора.
Поле:
Цена
Имя таблицы:
Товары
Обновление: [Цена]*1,1
Условия
отбора:
Введите в ячейку «Обновление» выражение или
значения, которые используются для изменения
полей (в примере цена увеличивается на 10%).
37

38. Запрос на добавление

В поле «Имя таблицы» введите имя таблицы, в
которую нужно добавить записи.
Перетащите из списка полей в бланк запроса
поля, которые нужно добавить или которые будут
использоваться при определении условий
отбора.
В строке «Добавление» указываются имена
полей таблицы-получателя.
38

39. Запрос на удаление

Перетащите знак «*» из списка полей таблицы,
из которой необходимо удалить записи. В ячейке
«Удаление» появится значение «Из».
Чтобы задать условия отбора удаляемых
записей, перетащите соответствующие поля в
бланк запроса и установите условия. В строке
«Удаление» появится надпись «Условие».
39

40. Запрос на удаление

Поле:
Заказы*
Код заказа
Имя таблицы:
Заказы
Заказы
Из
Условие
Удаление:
Условие
отбора:
<10050
Из таблицы «Заказы» будут удалены записи,
код которых меньше 10050.
40

41. Структурированный язык запросов SQL

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

42. Описание таблицы

CREAT TABLE WORKERS
(NUMBER NUMERIC (3),
NAME CHAR (15),
AGE NUMERIC (2),
NATION CHAR (20),
….
B_PLACE CHAR (49));
42

43. Таблица с данными

NUMBER
NAME
AGE
NATION
1
Ахмед И.
21
иранский татарин
184
Бакулин И.Я.
19
русский
381
Гасан А.
26
иранский татарин
514
Джават М.
41
иранский татарин
577
Исабек Б.А.
33
лезгин
…
…
…
…
43

44. Типы данных

Другие типы данных:
DATE – дата
LOGICAL – логические данные (T – истина, F ложь)
MEMO – вспомогательная текстовая информация
44

45. Просмотр, поиск и редактиро-вание записей БД

Просмотр, поиск и редактирование записей БД
Просмотр столбцов,
содержащих имя и
возраст:
SELECT NAME, AGE
FROM WORKERS;
Результат
представлен в
таблице.
NAME
AGE
Ахмед И.
21
Бакулин И.Я.
19
Гасан А.
26
…
…
45

46. Просмотр, поиск и редактиро-вание записей БД

Просмотр, поиск и редактирование записей БД
Выбор строк.
Например, рабочие
старше 30 лет:
SELECT NAME, AGE
FROM WORKERS
WHERE AGE>30.
Результат
представлен в
таблице.
NAME
AGE
Джават М.
41
Исабек Б.А.
33
Ильичев Е.В.
31
…
…
46

47. Сортировка записей БД

SELECT *
FROM WORKERS
ORDER BY AGE;
NUMBER
NAME
AGE
NATION
686
Курмакаев Х.М.
18
казанский татарин
184
Бакулин И.Я.
19
русский
1
Ахмед И.
21
иранский татарин
…
…
…
…
47

48. Вставка, редактирование и удаление записей в БД

INSERT INTO WORKERS (NUMBER, NAME)
VALUES (974, ‘Прохоров В.А.’);
UPDATE WORKERS
SET AGE=28, NATION=“русский”
WHERE NUMBER=974;
DELETE FROM WORKERS
WHERE NUMBER=974;
48

49. Формы в MS Access

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

50. Способы создания форм

Автоформа (указывается только таблица, остальное
выбирается автоматически);
Мастер форм (отбор записей; внешний вид: в один
столбец, ленточный, табличный и т. д.; стиль; имя
формы).
Конструктор (вид/свойства – источник записей).
Здесь можно изменить свойства формы: полосы
прокрутки, область выделения, разделительный
линии, кнопки окна и т. д. При выбора источника
записей должен появиться список полей, которые
можно перенести на форму.
50

51. Формы в MS Access

51

52. Панель элементов и разделы формы

Вид/Панель элементов. Содержит надпись, поле,
группа переключателей, флажок, выключатель,
переключатель, список, поле со списком, кнопка,
рисунок, вкладка, линия, прямоугольник,
подчиненная форма и др. Также можно включить
мастер элементов.
Разделы формы: заголовок формы, область данных,
примечание формы. В каждый из разделов можно
поместить любые элементы управления,
графические и текстовые элементы.
52

53. Вставка подчиненной формы

Создать подчиненную форму отдельно (например,
ленточного типа);
В главной форме в режиме конструктора выбрать
элемент «подчиненная форма/отчет» и указать
область размещения подчиненной формы;
Выбрать форму из списка (также можно выбрать
таблицу или запрос), указать связи между главной и
подчиненной формами через самостоятельное
определение.
Для быстрого просмотра полученной формы: меню
Вид/Конструктор - Режимы формы - Режим таблицы.
53

54. Отчеты в MS Access

Отчет – это данные, подготовленные для печати.
Отчет состоит из нескольких разделов:
Заголовок (выводится только на первой странице);
Верхний колонтитул (вверху страницы);
Заголовок группы (в начале каждой группы данных);
Область данных;
Примечание группы (в конце группы данных);
Нижний колонтитул (в конце страницы);
Примечание отчета (на последней странице).
54

55. Создание отчета

Режим автоотчета;
Мастер (можно добавить уровни группировки,
отсортировать информацию);
Модификация с помощью конструктора.
55