5685
.pdf11
вите обеспечение целостности данных (рисунок 6). Повторите действие и установите связи между остальными таблицами. Сохраните схему данных.
Рисунок 5 – Добавление таблиц в схему данных базы
Рисунок 6 – Создание связей между таблицами
12
2. Типы данных и свойства полей таблиц БД
Втаблицах MS Access 2007 можно использовать десять типов данных:
текстовый – алфавитно-цифровые данные, максимальный объём 255 символов (255 байт);
поле МЕМО – длинный текст, числа (например, комментарии и пояснения), максимальный объём 63999 символа (64 Кбайта);
числовой – общий тип числовых данных (длина 1, 2, 4 или 8 байт) ;
дата/время – значения даты и времени (длина 8 байт);
денежный – денежные значения (длина 8 байт);
счётчик – уникальный номер (число), которое для каждой новой записи автоматически увеличивается на 1 (длина 4 байта);
логический – логические значения типа Да/Нет, Истина/Ложь или Вкл/Выкл (длина 1 бит);
поле объекта OLE – объект, созданный в другой программе (рисунок, таблица Excel, документ Word и т.д.);
гиперссылка – строка, состоящая из букв и цифр и представляющая адрес гиперссылки (объём до 2048 символов);
вложение – новый тип данных, позволяет хранить несколько документов (объектов) и двоичных файлов любых типов.
Каждому типу данных соответствует определённый набор свойств поля. Наиболее важными являются следующие:
размер поля – размер данных, которые будут храниться в поле, для текстового поля от 1 до 255 символов, для числового поля размер – это фактически тип числовых данных (Байт, Целое, Длинное целое,
Одинарное с плавающей точкой, Двойное с плавающей точкой);
формат поля – формат отображения его значений при выводе на экран или печать;
маска ввода – устанавливает символы форматирования для их автоматического заполнения во время ввода данных;
подпись – задаёт альтернативное имя, которое будет появляться в качестве заголовка поля при просмотре таблицы или в созданных на её основе запросах, формах, отчётах;
значение по умолчанию – задаёт значение, автоматически вводящееся в поле при создании новой записи;
условие на значение – логическое выражение, определяющее условие для ввода данных, позволяет осуществлять контроль ввода;
пустые строки – если установить значение этого свойства равным «Да», то для текстового поля или поля МЕМО будет разрешён ввод пустых строк (пустая строка – это “”);
индексированное поле – позволяет создать индекс по данному полю (индекс указывает местоположение записей таблицы и помогает быстрее находить нужные данные);
13
сообщение об ошибке – текст сообщения, которое будет выведено на экран при вводе в поле данных, нарушающих условие на значение;
обязательное поле – указывает, обязателен ли ввод данных в это поле или нет.
Типы данных и значения используемых свойств поля указываются в конструкторе таблиц. Свойства поля, соответствующие выбранному типу данных, находятся на вкладке Общие в нижней части конструктора (рисунок 7).
Рисунок 7 – Конструктор таблиц
На вкладке Подстановка находятся свойства, отвечающие за создание в поле таблицы раскрывающегося списка значений. Список может содер-
жать фиксированный набор значений или использовать данные из поля таблицы или запроса. В MS Access 2007 можно создавать списки, которые будут содержать более одного значения в поле. Для создания списка значений можно воспользоваться Мастером подстановок, который находится в списке типов данных. Основные шаги мастера показаны на рисунке 8.
Рисунок 8 – Создание списка значений Мастером подстановок
14
Практическое задание 2. Создание таблиц и межтабличных связей базы данных. Способы вода и редактирования данных
1.Запустите MS Access 2007, создайте и сохраните файл новой базы данных. Будьте внимательны! Эта база будет использоваться в дальнейшем
вследующих лабораторных работах.
2.С помощью конструктора таблиц создайте следующие таблицы базы данных по учёту поставок. Ключевое поле в таблицах Товар, Поставщики и Поставщики_контакты определите самостоятельно! В таблице Поставки ключевое поле можно не задавать.
Товар
Имя поля |
Тип данных |
Свойства поля |
|
|
|
|
|
Код_товара |
текстовый |
Размер – 7 символов |
|
|
|
|
|
Наименование |
текстовый |
Размер – 50 символов |
|
|
|
|
|
Цена |
денежный |
Число десятичных знаков – 2 |
|
|
|
|
|
Ед_измерения |
текстовый |
Размер – 10 символов |
|
|
|
|
|
|
|
Размер – одинарное с плавающей точкой; |
|
Ставка_НДС |
числовой |
Формат – процентный; |
|
Число десятичных знаков – 2; |
|||
|
|
||
|
|
Значение по умолчанию – 0,18 |
|
Наличие |
логический |
Формат – да/нет |
|
|
|
|
Поставщики
Имя поля |
Тип данных |
Свойства поля |
|
|
|
Номер_поставщика |
текстовый |
Размер – 6 символов |
|
|
|
Наименование |
текстовый |
Размер – 50 символов |
|
|
|
Регион |
текстовый |
|
|
|
|
Адрес |
текстовый |
|
|
|
|
Телефон |
текстовый |
Маска ввода – \(9000")"900\-00\-00;;* |
|
|
|
Примечание |
поле MEMO |
|
|
|
|
Поставщики_контакты
Имя поля |
Тип данных |
Свойства поля |
|
|
|
|
текстовый (с помощью Мастера подстановки создать рас- |
|
Номер_поставщика |
крывающийся список, содержащий данный из соответству- |
|
|
ющего поля таблицы Поставщики) |
|
Фам_конт_лица |
текстовый |
|
|
|
|
Имя_конт_лица |
текстовый |
|
|
|
|
Отч_конт_лица |
текстовый |
|
|
|
|
Адрес_конт_лица |
текстовый |
|
|
|
|
Телефон_конт_лица |
текстовый |
Маска ввода – 00\-00\-00;;* |
|
|
|
|
|
15 |
Поставки |
|
|
Имя поля |
Тип данных |
Свойства поля |
|
|
|
|
|
Формат – длинный формат даты; |
|
|
Условие на значение – первое полугодие теку- |
Дата_поставки |
дата/время |
щего года (>=#01.01.2011# And |
|
|
<#01.07.2011#); |
|
|
Сообщение об ошибке – Ошибка даты |
Количество |
числовой |
|
Стоимость |
денежный |
Число десятичных знаков – 2 |
|
текстовый (с помощью Мастера подстановки создать раскрыва- |
|
Номер_поставщика |
ющийся список, содержащий данный из соответствующего поля |
|
|
таблицы Поставщики) |
|
|
текстовый (с помощью Мастера подстановки создать раскрыва- |
|
Код_товара |
ющийся список, содержащий данный из соответствующего поля |
|
|
таблицы Товар) |
|
|
текстовый (с помощью Мастера подстановки создать раскрыва- |
|
№_склада |
ющийся список, содержащий фиксированный набор значений: |
|
|
Склад А, Склад Б, Склад В, Склад Г, Склад Д) |
3.Создайте межтабличные связи. Установите обеспечение целостности данных. Сохраните схему данных.
4.Откройте таблицы БД в режиме ввода и внесите данные. В таблицы
Товар, Поставщики и Поставщики_контакты – по 20 позиции. В таблицу
Поставки – 50 позиций (на данном этапе поле Стоимость не заполнять!).
5.В списке объектов базы данных выберите таблицу Товар. Откройте вкладку Создание и нажмите кнопку Форма. MS Access 2007 автоматически создаст составную форму по таблице Товар и связанной таблице Поставки. Форма будет открыта в режиме макета (рисунок 9). Перейдите в режим формы и дополните список товаров информацией о 10 новых товарах.
Рисунок 9 – Форма в режиме макета
16
3. Запросы на выборку к таблицам БД
Запросы на выборку (запросы на извлечение) позволяют искать и обрабатывать данные в базе, не изменяя её содержимого. Они могут быть следующих видов:
простые запросы на выборку;
запросы с вычисляемыми полями;
запросы с групповыми операциями (запросы с итогами);
перекрёстные запросы.
Запросы с вычисляемыми полями позволяют производить вычисления на основе данных таблиц. Результат работы запроса – новое виртуальное (динамическое) поле, с результатом вычислений.
Запрос с групповой операцией отличается от простого запроса на выборку тем, что позволяет группировать данные по заданному полю и вычислять групповые итоги (осуществлять групповые операции) по заданным поля в группе. Возможно применение условий отбора. В MS Access предусмотрены следующие групповые операции:
Sum – сумма значений группы;
Avg – среднее значение для группы;
Max, Min – максимальное или минимальное значение в группе;
Count – количество непустых значений в группе;
StDev – среднеквадратичное отклонение в группе;
Var – дисперсия значений поля в группе;
First, Last – значение поля из первой и последней записи в группе. Перекрёстный запрос отличается от запроса с групповыми операциями
тем, что результат выводится в виде, напоминающем сводную электронную таблицу. В этот запрос должно быть включено не менее трёх полей.
По способу создание запросы можно разделить на QBE-запросы (Query by Example – запросы по образцу) и SQL-запросы. Первые строятся с помощью конструктора запросов, вторые с помощью операторов и функций языка SQL (Structured Query Language – язык структурированных запросов). Дополнительным средством создания запросов в MS Access является мастер запросов. Запросы можно создавать к таблицам, к другим запросам на выборку, одновременно к таблицам и запросам.
Результатом выполнения запроса на выборку является новая виртуальная (временная) таблица, не сохраняемая в базе данных. В запросе хранится структура запроса: таблицы, список полей, условия отбора записей и т.д., то есть фактически инструкция по поиску и отбору записей.
Последовательность действий при создании простого запроса на выборку:
определить, в какой таблице (или таблицах) содержатся искомые данные;
определить, по каким полям, каких таблиц будет происходить отбор данных, сформулировать критерии отбора;
17
запустить конструктор запросов, добавить выбранные таблицы;
если запрос многотабличный убедится, что между таблицами (запросами, таблицами и запросами), включёнными в запрос существует связь;
указать в таблице конструктора запросов поля, содержащие искомые данных;
указать поля, по которым осуществляется отбор данных, ввести критерии отбора;
сохранить запрос под выбранным именем и запустить его.
Для создания запроса с групповыми операциями или перекрестного запроса необходимо дополнительно:
определить, по каким полям будет осуществляться группировка данных;
определить вид групповой операции и поле, к которому она будет применена.
Операторы, используемые для создания условий отбора записей в запросах, и примеры условий отбора представлены в таблице 2.
Таблица 2 – Условия отбора записей |
||
Оператор |
Пример |
|
|
“Пылесос” (знак = можно не указывать) |
|
|
<>”Иванов” |
|
|
“директор” Or “бухгалтер” Or “менеджер” Or “сторож” |
|
> |
450 |
|
< |
>= 1200 |
|
>= |
<> 420 |
|
<= |
>100 And <200 |
|
<> |
<=#01.12.2010# |
|
And |
>#01.07.2010# |
|
Or |
>=#01.02.2011# And <#01.03.2011# |
|
|
#12.10.2010# Or #12.11.2010# |
|
|
<>ложь |
|
|
Is Null – незаполненные ячейки |
|
Between |
|
|
проверка интервала для |
Between 375 And 750 |
|
числового, денежного |
Between #01.02.2011# And #01.03.2011# |
|
значения или даты |
|
|
In |
In (258;32;16) |
|
проверка на равенство |
||
In (“Иванов”;”Петров”;”Сидоров”;”Степанов”) |
||
любому значению из |
||
In (#12.06.2010#;#12.07.2010#;#12.08.2010#;#12.09.2010#) |
||
списка |
||
|
||
|
Like “A*” |
|
Like |
Like “A*ов” |
|
Like “*телевизор*” |
||
разрешает использо- |
||
Like “*ов” |
||
вать образцы и симво- |
||
Like “??????ов” |
||
лы шаблона: |
||
Like "[ИПС]*" (текстовое значение начинается с любого из |
||
* – любое количество |
||
символов; |
указанных символов) |
|
? – один любой символ; |
Like "[!ИПС]*" (текстовое значение не начинается с любого из |
|
# – одна любая цифра |
указанных символов) |
|
|
Like "[И-С]*" (текстовое значение начинается с букв от И до С) |
18
Практическое задание 3. Создание запросов на выборку к таблицам базы данных
Запустите MS Access 2007 и откройте базу данных по учёту торговли, созданную на предыдущем занятии. Создайте запросы к таблицам базы данных, позволяющие получить заданную информацию. Таблицы БД, включаемые в запрос, и критерии отбора записей определяйте самостоятельно.
Простые однотабличные запросы
1.Получите список товаров, которых нет в наличии.
2.Определите, какие товары имеют стоимость больше Y рублей (стоимость Y задайте самостоятельно).
3.Используя сортировку, определите, какой товар имеет наибольшую стоимость.
4.Получите список поставок в хронологическом порядке с указанием кода товара и номера склада.
5.Получите список поставщиков одного выбранного Вами региона с указанием адреса и телефона.
6.Определите номера телефонов поставщиков Х и Y (наименования поставщиков задайте самостоятельно)
7.Определите, на каких складах находятся товары, поставленные Y числа (дату поставки Y задайте самостоятельно).
8.Получите список поставок, выполненных в январе, марте и мае текущего года.
9.Выясните, делал ли поставки в первом квартале текущего года поставщик №… (номер поставщика задайте самостоятельно).
10.Выясните, поступал ли товар X на склад №… в феврале текущего года (код товара X и номер склада задайте самостоятельно).
Пример: Получить список товаров, которых нет в наличии.
Запрос однотабличный, все необходимые поля находятся в таблице Товар.
Выполняем команду вкладка Создание → Конструктор запросов. В
появившемся окне Добавление таблиц выбираем таблицу Товар и добавляем её в запрос (рисунок 10).
В таблице конструктора (она находится в нижней части) указываем поля Код_товара, Наименование, Цена (в них находятся необходимые данные) и поле Наличие, которое служит для задания условия отбора записей. Условие отбора – нет (это логическое значение, без кавычек). Указать необходимые поля можно двойным щелчком мыши по выбранному полю таблицы (в схеме данных запроса), или использовать раскрывающиеся списки
19
строк Имя таблицы и Поле таблицы конструктора. Созданный запрос сохраняем и запускаем.
Заполненный конструктор запросов представлен на рисунке 11.
Рисунок 10 – Добавление таблицы в Конструктор запросов
Рисунок 11 – Простой однотабличный запрос на выборку
Простые многотабличные запросы
1.Определите адреса и телефоны поставщиков, выполнивших поставки в течение одного (любого) месяца.
2.Получите список товаров поставленных поставщиками X, Y, Z в феврале и марте (наименования поставщиков задайте самостоятельно).
3.Определите адреса и номера телефонов контактных лиц поставщиков X, Y, Z (наименования поставщиков задайте самостоятельно).
4.Склады Б и В попали в зону стихийного бедствия. Какие товары пострадали?
20
5.Получите список товаров поставленных во втором квартале текущего года. Список должен содержать дату поставки, наименование поставщика, наименование товара, его код и цену.
6.Выясните, поставлял ли поставщик Х товар Y на склад №… в апреле – мае текущего года (наименования поставщика и товара, а также номер склада задайте самостоятельно).
7.Вы хотите приобрести товар X. К каким контактным лицам следует обратиться?
8.В данный момент некоторые товары отсутствуют. К каким поставщикам и контактным лицам поставщиков следует обратиться?
9.Получите список товаров поставки, которых не совершались (если будет получена пустая таблица – таких товаров нет).
10.Определите, есть ли поставщики, которые не совершали поставок.
Пример: Определить адреса и телефоны поставщиков, выполнивших поставки в феврале месяце.
Все необходимые поля находятся в таблицах Поставщики и Поставки. Запускаем Конструктор запросов, добавляем в запрос указанные таблицы. В схеме данных запроса проверяем наличие связей между таблицами. В таблице конструктора указываем поля Наименование, Адрес, Телефон из таблицы Поставщики (в них находятся необходимые данные) и поле
Дата_поставки из таблицы Поставки, оно необходимо для задания условия отбора записей. Условие отбора по дате – >=#01.02.2011# And <#01.03.2011#. Заполненный конструктор представлен на рисунке 12.
Созданный запрос сохраняем и запускаем его на выполнение.
Рисунок 12 – Простой многотабличный запрос на выборку