Практикум по Microsof access

Создание запросов.

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

 Select <Список_полей>

 From <Список_таблиц>

  [Where< спецификация_отбора_строк>]

 [Group by <Спецификация_группировки>]

  [Having <Спецификация_отбора_групп>]

 [Order by <Спецификация_сортировки>]

Список полей может содержать список имен столбцов таблиц или запросов базы данных или список выражений. Например, запрос, данные которого используются в списке «ВыборАктера» (рис.10), содержит выражение

[Актер].[Фамилия] & " " & left([Актер].[Имя],1) & "." & left([Актер].[Отчество],1) & ".", которое на основе полей таблицы «Актер» формирует фамилию актера с инициалами.

Предложение From указывает таблицы или запросы, которые содержат поля, перечисленные в инструкции Select. Если информация выбирается из нескольких связанных таблиц, то предложение From более сложным и включает в себя предложение inner join, с помощью которого осуществляется связь таблиц. Оно имеет вид:

FROM таблица_1 INNER JOIN таблица_2 ON таблица_1.поле_1 оператор таблица_2.поле_2

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

SELECT Роль.КодРоли, Роль.роль, Пьеса.Название, Пьеса.Автор

FROM Пьеса INNER JOIN Роль ON Пьеса.КодПьесы = Роль.КодПьесы;

Оператор описывает условие связывания таблиц. Операции JOIN могут быть вложенными; в таком случае используйте следующий синтаксис:

SELECT поля
FROM таблица_1 INNER JOIN (таблица_2 INNER JOIN

(таблица 3 ON таблица_2.поле_2 оператор таблица_3.поле_3) 
ON таблица_1.поле_1 оператор таблица_2.поле_2;

Предложение WHERE определяет, какие записи из таблиц, перечисленных в предложении From, следует включить в результат выполнения инструкции Select. После предложения WHERE должны стоять условия отбора записей. Например, выбрать всех актеров, участвующих в спектаклях в текущий день. Такой запрос может быть записан так:

SELECT Актер.Фамилия, Актер.Имя, Актер.Отчество, Роль.роль, Пьеса.Автор, Пьеса.Название, Спектакль.Дата_постановки

FROM (Пьеса INNER JOIN Роль ON Пьеса.КодПьесы = Роль.КодПьесы) INNER JOIN (Актер INNER JOIN Спектакль ON Актер.КодАктера = Спектакль.КодАктера) ON Роль.КодРоли = Спектакль.КодРоли

WHERE (((Спектакль.Дата_постановки)=Date()));

 В качестве примера организации запросов выберем раздел Актер и потребуем, чтобы при таком выборе была предоставлена возможность выбора нескольких актеров, для того чтобы посмотреть их занятость в спектаклях. Это означает, что при выборе раздела Актер нажатие кнопки Просмотр, стоящей слева от группы, на экран будет выведена форма, позволяющая сделать множественный выбор актеров. После выбора актера (или группы) кнопка «Просмотр» позволяет вывести информацию о всех выделенных актерах. Форма «Занятость актеров в спектаклях» представлена на рис.11.

 


 Обработка события: нажатие клавиши «Просмотр», приведена ниже

Private Sub Просмостр_Click()

Dim strWhere As String, varItem As Variant

‘ Если ни одна запись в списке lstAName не выбрана, то число выбранных элементов

‘ свойство ItemsSelected.Count, то есть число выбранных элементов, будет равен 0

‘ и сообщение об этом будет выведено с иконкой предупреждения - vbExclamation

 If lstAName.ItemsSelected.Count = 0 Then

 MsgBox "Вы не выбрали актеров", vbOKOnly + vbExclamation

  Exit Sub

 End If

‘ Организовывается цикл для формирования условий открытия формы «Актер»

‘ только для выбранных элементов списка lstAName, свойство ItemsSelected

‘ Свойство Column(0, varItem) списка lstAName содержит информацию о коде выбранного

‘ актера Нулевой столбец указанной строки. Код актера в список не выводится

‘ так как его ширина при построении задавалась 0

 For Each varItem In lstAName.ItemsSelected

 strWhere = strWhere & lstAName.Column(0, varItem) & ","

 Next varItem

‘  Убираем последнюю лишнюю запятую

 strWhere = Left$(strWhere, Len(strWhere) - 1)

‘ Формируем условия открытия формы «Актер»

 WhereNstb = "[КодАктера] IN (" & strWhere & ")"

‘ Открытие формы «Актер» только для выделенных элементов.

 DoCmd.OpenForm FormName:=”Актер”, WhereCondition:=WhereNstb

End Sub

 Вид формы «Актер», открытый для выделенных на рисунке 11 записей (их 3), представлен на рис. 12.

 Литература.

Петров В.Н. Информационные системы. С.-П., Питер, 2002.

Вейскас Дж. Эффективная работа с Microsoft Access 2000. С.-П., Питер, 2001.

Новалис С. Access 2000. Руководство поVBA. М., Лори, 2001.

Лекция Проектирование БД в MS Access. Структура таблиц. Типы данных. Схема данных. Модификация структуры данных.

СУБД Access оперирует реляционными базами данных. На этапе проектирования базы данных должна быть определена логическая структура базы данных для выбранной предметной области. Проект логической структуры БД устанавливает состав реляционных таблиц, их структуру и логические связи между таблицами. При формировании структуры каждой таблицы определяется совокупность атрибутов (полей, столбцов), для каждого из которых даются описание типа, размера данных и других свойств. Кроме того, должен быть указан уникальный ключ таблицы, который может состоять из одного или нескольких полей.

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

Этапы проектирования и создания базы данных

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

Определение состава и структуры данных, которые должны быть загружены в базу данных, осуществляется на основе анализа предметной области. Структура данных предметной области может отображаться информационно-логической моделью (ИЛМ). Если при построении такой модели обеспечены требования нормализации данных и она, соответственно, представлена в каноническом виде, далее легко определяется проект логической структуры нормализованной базы данных. На основе канонической модели можно создать реляционную базу без дублирования данных.

При разработке модели данных предметной области могут использоваться два подхода. В первом подходе (аналитическом или процессном) сначала формулируются основные задачи, для решения которых строится база, выявляются информационные потребности задач приложения пользователя, и, соответственно, определяются состав и структура информационных объектов модели, а также связи между ними. При втором подходе (интуитивном) сразу устанавливаются типовые объекты предметной области и их взаимосвязи. Наиболее рационально сочетание обоих подходов. Это связано с тем, что на начальном этапе, как правило, нет исчерпывающих сведений обо всех задачах. Использование такой технологии тем более оправдано, что гибкие средства создания реляционной базы данных в Access позволяют на любом этапе разработки внести изменения в базу данных и модифицировать ее структуру без ущерба для введенных ранее данных.

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

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

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

Для определения поля таблицы задаются Имя поля (Field Name), Тип данных (Data Type), Описание (Description) -— краткий комментарий, а также свойства поля в разделе Свойства поля (Field Properties), представленные на двух вкладках: Общие (General) и Подстановка (Lookup). К общим относятся такие свойства поля, как максимальный размер, формат, подпись, которая выводится в заголовке столбца таблицы, значение по умолчанию и др. На вкладке Подстановка (Lookup) выбирается Тип элемента управления (Display Control): поле, список фиксированных значений или поле со списком.

Имена полей и типы данных

Имя поля (Field Name). Каждое поле в таблице должно иметь уникальное имя, удовлетворяющее соглашениям об именах объектов в Access. Оно является комбинацией из букв, цифр, пробелов и специальных символов, за исключением точки (.), восклицательного знака (!), надстрочного знака (~) и квадратных скобок ([ ]). Имя не может начинаться с пробела и содержать управляющие символы с кодами ASCII от 0 до 31. Максимальная длина имени 64 символа.

Тип данных (Data Type), Тип данных определяется значениями, которые предполагается хранить в поле, и операциями, которые будут выполняться с этими значениями. В Access допускается использование десяти типов данных и мастера подстановок. Список возможных типов данных вызывается нажатием кнопки списка при выборе типа данных каждого поля. Рассмотрим вкратце назначение и допустимые размеры всех типов данных, которые могут назначаться полям таблицы в Access.

Текстовый (Text) — используется для хранения текста или комбинаций алфавитно-цифровых знаков, не применяемых в расчетах (например, код товара). Максимальная длина поля 255 знаков.

Поле МЕМО (Memo) — используется для хранения обычного текста или комбинаций алфавитно-цифровых знаков длиной более 255 знаков. Поля с этим типом данных в базах данных формата Access 2007 поддерживают также форматирование текста. Это единственный в Access тип данных, обеспечивающий встроенную поддержку отображения и хранения форматированного текста. Максимальный размер поля 1 Гбайт знаков или 2 Гбайт памяти (2 байта на знак) при программном заполнении полей, и 65 535 знаков при вводе данных вручную в поле и в любой элемент управления, связанный с этим полем.

Числовой (Number) — служит для хранения числовых значений (целых или дробных), предназначенных для вычислений, исключением являются денежные значения, для которых используется тип данных Денежный. Размер поля 1, 2, 4 и 8 байтов, или 16 байтов (если используется для кода репликации) зависит от типа чисел, вводимых в поле.

Дата/время (Date/Time) — используется для хранения значений даты и времени в виде 8-байтовых чисел двойной точности с плавающей запятой. Целая часть значения, расположенная слева от десятичной запятой, представляет собой дату. Дробная часть, расположенная справа от десятичной запятой,— это время. Хранение значений даты и времени в числовом формате позволяет выполнять различные вычисления с этими данными.

Денежный (Currency) — используется для хранения денежных значений в виде 8-байтовых чисел с точностью до четырех знаков после запятой. Этот тип данных применяется для хранения финансовых данных и, в тех случаях, когда значения не должны округляться.

Счетчик (AutoNumber) — используется для уникальных числовых 4-байтовых значений, которые автоматически вводит Access при добавлении записи. Вводимые числа могут последовательно увеличиваться на указанное приращение или выбираться случайно. Обычно используются в первичных ключах.

Логический (Yes/No)— применяется для хранения логических значений, которые могут содержать одно из двух значений: Да/Нет, Истина/Ложь или Вкл/Выкл. (8 битов = 1 байт). Используется 1 для значений Да и 0 для значений Нет. Размер равен 1 биту.

Поле объекта OLE (OLE Object) — используется для хранения изображений, документов, диаграмм и других объектов из приложений Ms Office и других программ Windows в виде растровых изображений, которые затем отображаются в элементах управления форм или отчетов, связанных с этим полем таблицы.

Чтобы в Access просмотреть эти изображения, необходимо, чтобы на компьютере, использующем базу данных, был зарегистрирован OLE-сервер (программа, поддерживающая этот тип файлов). Если для данного типа файлов OLE-сервер не зарегистрирован, отображается значок поврежденного изображения.

В accdb-файлах вместо типа данных Поле объекта OLE используется тип Гиперссылка (Hyperlink).

Гиперссылка (Hyperlink) — применяется для хранения ссылок на узлы (URL-адреса), на узлы или файлы интрасети или локальной сети (UNC-адреса — стандартного формата записи пути), а также на узлы или файлы локального компьютера. Кроме того, можно использовать ссылку на объекты Access, хранящиеся в базе данных. Может хранить до 1 Гбайт данных.

Вложение (Attachment) — используется для вложения в поле записи изо­бражений, файлов электронных таблиц, документов, диаграмм и других файлов поддерживаемых типов точно так же, как в сообщения электронной почты. Вложенные файлы можно просматривать и редактировать в соответствии с заданными для поля параметрами. Эти поля не имеют ограничений, связанных с отсутствием зарегистрированных OLE -серверов. Ьолее рационально используют место для хранения, чем поля с типом данных Поле объекта OLE (OLE Object), поскольку не создают растровые изображения исходного файла. Максимальная длина поля для сжатых вложений — 2 Гбайт, для несжатых — примерно 700 Кбайт в зависимости от степени возможного сжатия вложения.

Мастер подстановок (Lookup Wizard) — появляется в списке типов данных только в режиме конструктора. Фактически типом данных не является, а вызывает мастер подстановок, с помощью которого можно создать поле, позволяющее выбрать значения из списка, построенного на основе значений поля другой таблицы, запроса или фиксированного набора значений. Такое поле отображается как поле со списком. Если список построен на основе поля таблицы или запроса, тип данных и размер создаваемого поля определяется типом данных и размером привязанного столбца, если на основе набора значений — размером текстового поля, содержащего значение.

Замечание. Код репликации (Replication ID). Глобальный уникальный идентификатор (Globally unique identifier, GUID), занимает 16 байтов. Эти длинные генерируемые случайным образом значения обеспечивают малую вероятность их совпадения. Поля такого типа используются Access для создания системных уникальных идентификаторов реплик, наборов реплик, таблиц, записей и других объектов при репликации баз данных. Могут быть использованы в приложениях пользователя, например, для идентификации товаров

Информатика лекции и контрольные