Методический материал для внеаудиторной работы студентов: Система управления базами данных

Государственное образовательное бюджетное учреждение

среднего профессионального образования Воронежской области

«Воронежский механический техникум»

(ГОБУ СПО ВО «ВМТ»)

Методический материал

для внеаудиторной работы студентов

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

подготовила

преподаватель математики и информатики

Жигалкина Яна Александровна

г. Воронеж

2014

Пояснительная записка

Методический материал содержит теоретические сведения, необходимые для выполнения практических работ: некоторые понятия и термины баз данных, технологию создания базы данных, способы создания таблиц, форм, запросов различных типов, способы распечатки запросов.

Текст снабжен многочисленными иллюстрациями, помогающими лучшему усвоению излагаемого материала.

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

При работе с системой управления базами данных (СУБД) Microsoft Access все подобные действия удобно выполнять с помощью так называемых запросов. Именно этим объектам уделено основное внимание в данном методическом материале.

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

Цель настоящего методического материала - помочь учащимся освоить работу с базами данных, в частности, с запросами в Microsoft Access.

СОДЕРЖАНИЕ

Базы данных - некоторые понятия и термины

Создание таблиц

1. Создание базы данных

2. Создание таблицы

Запрос-выборка

3.1. Создание запроса по одному условию

3.2. Выбор записей по нескольким условиям

3.3. Создание запроса по нескольким таблицам

Вычисляемые поля в запросе

Запрос с параметрами

Запрос с использованием групповых операций

Печать запросов

Создание формы

1. Базы данных - некоторые понятия и термины

База данных - это набор структурированных данных.

Система управления базой данных (СУБД) представляет собой комплекс языковых и программных средств, предназначенных для создания, ведения и совместного использования базы данных многими пользователями.

Microsoft Access создает файл базы данных, имеющий расширение имени .mdb или .accdb (в Microsoft Access 2007). В этот файл входят основные объекты Microsoft Access: таблицы, формы, запросы, отчеты, макросы, модули.

Напомним некоторые характеристики некоторых основных объектов базы данных.

Таблица - это объект, предназначенный для хранения данных в виде записей (строк) и полей (столбцов). Обычно каждая таблица используется для хранения сведений по одному конкретному вопросу.

Форма - объект Microsoft Access, предназначенный, в основном, для ввода данных. В форме можно разместить элементы управления, применяемые для ввода, изображения и изменения данных в полях таблиц, такие как раскрывающиеся списки, опции-флажки, селективные кнопки и т.д.

Запрос - объект, позволяющий получить нужные данные из одной или нескольких таблиц.

Отчет - объект Microsoft Access, предназначенный для печати данных.

Таблица - это базовый, первичный объект Microsoft Access. Создание базы данных начинают с создания таблиц, остальные объекты являются производными и создаются на базе ранее подготовленных таблиц.

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

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

Запись - полный набор данных об определенном объекте. В режиме таблицы запись изображается как строка.

С каждым объектом пользователь работает в отдельном окне, причем предусмотрено два режима работы:

1) оперативный режим - в окне решаются задачи информационно-поисковой системы, т.е. можно просматривать, изменять, выбирать информацию;

2) режим конструктора - здесь мы создаем или изменяем макет, структуру объекта.

Каждый объект сохраняется отдельно командой Файл - Сохранить как.

Кроме того, в файл базы данных входит еще один документ, имеющий собственное окно: Схема данных. В этом окне мы создаем, изменяем или разрываем связи между таблицами.

2. СОЗДАНИЕ ТАБЛИЦ

2.1. Создание базы данных

Для создания новой базы данных запустить приложение MS Access. В раскрывшемся окне щелкнуть по кнопке Новая база данных. Открыть нужную папку для размещения новой базы данных, щелкнув по кнопке , ввести имя файла, щелкнуть по кнопке Создать. В результате открывается окно созданной базы данных.

Создать базу данных с именем Студенты в своей папке на диске D:

2.2. Создание таблицы

Существует несколько способов создания таблиц:

1. импорт из Excel, из Access;

2. создание на основе шаблона таблицы;

3. определение полей непосредственно в новой таблице;

4. создание в режиме Конструктора.

Рассмотрим создание таблицы в режиме Конструктора:

1. на вкладке Создание щелкнуть кнопку Конструктор таблиц;

2. в открывшемся окне ввести имена полей;

3. для каждого поля выбрать тип данных, размер поля, формат поля и - при необходимости - другие свойства;

4. одно из полей сделать ключевым, щелкнув по кнопке Ключевое поле;

5. сохранить таблицу, задав для нее имя;

6. для ввода данных в таблицу перейти в режим Таблицы, щелкнув по кнопке Режим.

3. Запрос-выборка

Любой объект можно создавать либо вручную, либо с помощью Мастера. Запрос удобнее создавать вручную, в режиме Конструктора. Он создается на базе одной или нескольких готовых таблиц и (или) запросов.

Запрос-выборка - это производная таблица, которая содержит те же структурные элементы, что и обычная таблица (столбцы-поля и строки), и формируется на основе фактических данных системы. При создании макета запроса необходимо в общем случае выполнить следующие базовые операции:

1. указать системе, какие поля и из каких таблиц требуется включить в запрос;

2. описать вычисляемые поля, т.е. поля, значения которых являются функциями существующих полей;

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

4. указать условие отбора, т.е. сформулировать логическое выражение, которое позволит включить в выборку только записи, удовлетворяющие заданному условию.

Запрос - это временная таблица. После закрытия в запросе хранятся не выбранные записи, а правило отбора записей.

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

Cуществует также упрощенный механизм создания запроса, называемый QBE (Query By Example - Запрос по образцу). В режиме Конструктора открывается бланк QBE - заготовка запроса, и на этом бланке, пользуясь определенными соглашениями, пользователь выбирает поля, вводит выражения и т.п. На основании заполненного бланка программа сама создает соответствующий оператор SELECT и сама выполняет его.

3.1. Создание запроса по одному условию

Идею создания запроса рассмотрим на следующем примере. Пусть имеется база данных с именем Студенты.accdb. Таблица с именем Сведения о студентах содержит основные сведения о студентах. Макет таблицы представлен на рис.1. Фрагмент таблицы - на рис.2.

Рис.1. Макет таблицы Сведения о студентах

Рис.2. Фрагмент таблицы Сведения о студентах

Из всего списка требуется выбрать только тех студентов, у которых год рождения - 1985; при этом вывести на экран столбцы - Номер, Фамилия, Имя, Год рождения.

1. Открыть базу данных Студенты.

2. Выбрать вкладку Создание (рис.3).

Рис.3

3. Щелкнуть мышью по кнопке Конструктор запросов

Рис.4

4. На экране появляется окно документа Запрос в режиме Конструктора и окно Добавление таблицы (рис.5). Если окна Добавление таблицы нет, то на вкладке Конструктор щелкнуть кнопку Отобразить таблицу.

Рис.5

5. Выбрать исходную таблицу для запроса (см. рис.5), в нашем случае - Сведения о студентах. Закрыть окно Добавление таблицы.

В результате в верхней части окна запроса отобразилась выбранная таблица с указанием имен всех полей. В нижней - бланк QBE, который представляет собой макет некоей таблицы (рис.6).

6. В строке Поле: указываются имена столбцов (полей) создаваемого запроса. Это можно сделать разными способами:

• либо двойным щелчком по имени нужного поля в таблице в верхней части окна (см. рис.6);

• либо перетащить в ячейку Поле: методом Drag-and-Drop из таблицы в верхней части окна;

• либо выбрать из раскрывающегося списка, щелкнув мышью в ячейке Поле: бланка QBE (см. рис.6).

Рис.6

В столбцы запроса мы поместили Номер, Фамилия, Имя, Год рождения. В ячейке Имя таблицы: появляется имя таблицы, которая содержит выбранное поле, в нашем случае - Сведения о студентах.

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

7. В строке Условие отбора задаем критерий выбора: в поле Год рождения вводим число 1985. Это означает, что в запрос из таблицы будут выбраны только записи, удовлетворяющие указанному условию (см. рис.6).

В качестве условия отбора могут быть заданы операции сравнения с использованием знаков:

• больше >,

• меньше <

• больше или равно >=

• меньше или равно <=

• не равно <>

Знак равенства не вводится, достаточно просто ввести значение, как было показано в данном пункте.

8. Сохраним запрос с именем Год рожд-1985 в базе данных Студенты,, выполнив для этого команды Файл / Сохранить как. Имя запроса вводим в текстовое поле Сохранение объекта "Запрос" в: (рис.7). Для просмотра выбранных записей перейдем в режим Таблицы (см. рис.7).

Рис.7

9. Как уже говорилось, на основании заполненного бланка программа сама создает соответствующий оператор SELECT на языке SQL. Для того, чтобы увидеть этот оператор, надо выполнить команду Режим / Режим SQL на вкладке Главная или Конструктор.

Наш запрос на языке SQL представлен на рис.8.

Рис.8

Именно в таком виде запрос хранится в базе данных после своего закрытия. А при каждом переходе в режим таблицы и при каждом открытии запроса в режиме таблицы в него выбираются конкретные записи из таблиц базы, удовлетворяющие условию, описанному в операторе SELECT.

10. Если какое-либо из выбранных полей (например, Год рождения) не требуется отображать в режиме таблицы, то в режиме конструктора для данного поля в строке Вывод на экран надо снять флажок (см. рис.6). В этом случае поле по-прежнему участвует в создании запроса (именно по этому полю задано условие отбора), но в таблице оно не отображается (рис.9).

Рис.9

11. Запрос можно упорядочить по возрастанию или убыванию значений любого поля, например, по возрастанию поля Фамилия. Для этого на бланке в строке Сортировка в нужном поле выбрать направление сортировки (рис.10).

Рис.10

Отсортированный запрос в режиме таблицы показан на рис.11.

Рис.11

3.2. Выбор записей по нескольким условиям

Для выбора записей по нескольким условиям в условии отбора используются логические операторы: AND (логическое И) и OR (логическое ИЛИ). Как работают логические операторы? Если требуется задать одновременное выполнение двух или более условий, то эти условия должны соединяться оператором AND; если же требуется выполнение хотя бы одного из двух или более условий, то эти условия должны соединяться оператором OR.

В Microsoft Access приняты следующие соглашения.

1) Условные выражения, набранные в разных полях строки Условие отбора, по умолчанию соединяются между собой оператором AND. Например, для выбора всех первокурсниц с именем Анна следует задать условие отбора, как показано на рис.13.

Рис.13

На языке SQL это будет выглядеть следующим образом (рис.14):

Рис.14

2) Условные выражения, введенные в разных строках, соединяются между собой оператором OR. Например, для выбора всех первокурсников, а кроме того, всех студентов по специальности ВТ (будут выбраны все первокурсники со всех специальностей, все студенты со специальности ВТ; в том числе, это могут быть и студенты первого курса специальности ВТ) следует задать условие отбора, как показано на рис.15.

Рис.15

Это условие на языке SQL примет вид:

([Сведения о студентах].Курс)=1 OR ([Сведения о студентах].Специальность)="ВТ"

Другой пример: выбрать из списка всех студентов с именем Анна и Ирина. Для каждой конкретной записи таблицы в поле Имя может быть только какое-либо одно значение. Требуется выбрать только те записи (студентов), у которых в поле Имя значение либо равно Анна, либо равно Ирина. Зададим условие отбора, как показано на рис.16.

Рис.16

Можно это же условие задать иначе: в ячейку Условие отбора в поле Имя ввести логическое выражение Анна OR Ирина (оператор OR с обеих сторон отделяется пробелами). После выхода из ячейки и редактирования программой условие в ячейке примет вид: "Анна" Or "Ирина" (текстовые значения взялись в кавычки).

3.3. Создание запроса по нескольким таблицам

Как говорилось ранее, запрос может быть создан на базе одной или нескольких готовых таблиц и (или) запросов.

Рассмотрим пример создания запроса по двум таблицам: Сведения о студентах и Города, - содержащимся в базе данных Студенты.

В таблице Города названия населенных пунктов закодированы числовыми кодами (рис.17).

Рис.17

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

1. В окне MS Access выбрать вкладку Создание.

2. Щелкнуть мышью по кнопке Конструктор запросов.

3. Выбрать исходные таблицы для запроса: Сведения о студентах и Города (выделить таблицы в окне Добавление таблицы и щелкнуть по кнопке Добавить). Закрыть окно Добавление таблицы.

4. Из таблицы Сведения о студентах выбрать поля Фамилия, Имя, Курс, Код города. Из таблицы Города выбрать поле Наименование города (рис.18).

Рис.18

5. В столбце Курс задаем условие 1 OR 2

6. В качестве условия отбора в столбце Код города вводим выражение: 3 OR 5 , т.к. 3 - это код Железноводска, 5 - код Минеральных Вод.

7. Снимаем флажок вывода на экран для Кода города

8. Выбираем сортировку по возрастанию в столбце Фамилия (см. рис 18).

9. Переходим в режим таблицы (рис.19).

Рис.19

4. Вычисляемые поля в запросе

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

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

1. В окне MS Access выбрать вкладку Создание.

2. Щелкнуть мышью по кнопке Конструктор запросов.

3. Выбрать таблицу для запроса: Сведения о студентах (выделить таблицу в окне Добавление таблицы и щелкнуть по кнопке Добавить). Закрыть окно Добавление таблицы.

4. Из таблицы Сведения о студентах выбрать поля Номер, Фамилия.

5. В третьем столбце в строке Поле ввести следующую формулу:
   Общий доход: [Средний доход]+[Стипендия]

6. Бланк запроса будет выглядеть, как показано на рис.20.

Рис.20

7. Текст Общий доход и будет именем вычисляемого поля.

8. На рис.21 представлены фрагменты запроса в режиме таблицы и таблицы Сведения о студентах.

Рис.21

9. Для вычисляемого поля можно изменить формат вывода числа, например, выбрать денежный формат. Для этого на бланке QBE ( в режиме таблицы) выделить вычисляемый столбец, щелкнуть по нему правой кнопкой мыши и из открывшегося контекстного меню выбрать команду Свойства. В открывшемся окне Свойства поля щелкнуть на строке Формат поля и из раскрывшегося списка выбрать нужный формат, в нашем случае - денежный (рис 22).

Рис.22

9. При вводе формулы можно воспользоваться Построителем выражений (рис.23). Для этого щелкнуть по текущей ячейке правой кнопкой мыши и из открывшегося контекстного меню выбрать команду Построить.

Рис.23

5. Запрос с параметрами

Можно создать запрос, который будет осуществлять выборку записей при каждом открытии по-разному, в зависимости от вводимого пользователем параметра.

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

1. Открыть запрос в режиме конструктора.

2. В качестве третьего столбца вставить поле Курс.

3. Можно погасить флажок выдачи этого столбца на экран (это не обязательно).

4. В строке Условие отбора в поле Курс ввести в квадратных скобках текст приглашения: [Введите номер курса]

5. Сохранить запрос.

6. При открытии этого запроса в режиме таблицы на экране появится диалоговое окно с указанным приглашением (см. рис.24).

Рис.24

7. Надо ввести номер курса, например, 4 и нажать клавишу Enter или щелкнуть по кнопке OK. В запрос выберутся данные о студентах только указанного (четвертого) курса.

6. Запрос с использованием

групповых операций

В запросе можно выполнять групповые операции, т.е. объединять записи в группы. Группировка означает, что записи с одинаковым значением в каком-либо поле будут объединены в одну запись.

Если требуется задать групповые операции, то необходимо в режиме Конструктора на вкладке Конструктор щелкнуть по кнопке Итоги . На бланке появляется строка Групповая операция:. После щелчка на любом поле этой строки появится список типов групповых операций: Группировка, Sum, Avg, Min, Max, Count и т.д.

Рассмотрим пример. Создадим запрос, подсчитывающий количество учащихся на каждом курсе.

1. В окне MS Access выбрать вкладку Создание.

2. Щелкнуть мышью по кнопке Конструктор запросов.

3. Выбрать таблицу для запроса: Сведения о студентах (выделить таблицу в окне Добавление таблицы и щелкнуть по кнопке Добавить). Закрыть окно Добавление таблицы.

4. Из таблицы Сведения о студентах дважды выбрать поле Курс.

5. На вкладке Конструктор щелкнуть по кнопке Итоги, в результате в бланке QBE появится строка Групповая операция:

6. Для первого столбца выбрать операцию Группировка, для второго столбца - операцию Count. Для этого щелкнуть в строке Групповая операция во втором столбце и из раскрывшегося списка выбрать Count (рис.25).

7. Перейти в режим таблицы. Вид полученного запроса показан на рис.26.

8. Для второго столбца можно вместо Count-Курс задать другую подпись, например, Количество студентов. Для этого выделить второй столбец, щелкнуть по нему правой кнопкой и из раскрывшегося меню выбрать опцию Свойства (рис.27).

Рис.25

Рис.26

Рис.27

В открывшемся окне Свойства полей на вкладке Общие в строке Подпись ввести текст Количество студентов. Закрыть окно Свойства полей.

9. Перейти в режим таблицы. Запрос примет вид (рис. 28):

Рис.28

7. Печать запросов

Запрос можно распечатать разными способами.

Во-первых, используя меню Файл / Печать.

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

В-третьих, на основании запроса можно создать и затем распечатать отчет (один из объектов базы данных).

8. Создание ФОРМЫ

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

Создавать формы можно различными способами. Наиболее удобно создавать макет формы в режиме Мастера форм, а редактировать макет - в режиме Конструктора.

Технология создания формы в режиме Мастера форм:

1. В окне MS Access выбрать вкладку Создание.

2. Щелкнуть мышью по кнопке Другие формы .

3. Из открывшегося меню выбрать опцию Мастер форм.

4. В открывшемся окне выбрать нужную таблицу, затем выбрать нужные поля этой таблицы, щелкнуть мышью по кнопке Далее.

5. Выбрать внешний вид формы: в один столбец, ленточный, табличный или выровненный; щелкнуть мышью по кнопке Далее.

6. Выбрать из готовых шаблонов стиль оформления формы, щелкнуть мышью по кнопке Далее.

7. Задать имя формы, выбрать вариант «Открыть форму для просмотра и ввода данных», щелкнуть мышью по кнопке Готово.

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

Список литературы

1. Карпова Т. С. Базы данных: модели, разработка, реализация : учебное пособие / Т. С. Карпова. - СПб. : Питер, 2001. - 304 с.

2. Кренке Д. Теория и практика построения баз данных. - 9-е изд. - СПб. : Питер, 2005. - 859 с.

3. Золотова С.И. Практикум по Access./ Золотова С.И. //Информатика и образование. - 2002. - №12. - с.13-23.