База данных access. таблицы в субд microsoft access

Типы соединений

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

Внутренние соединения: объединены только связанные данные из обеих таблиц.

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

Полные внешние соединения: все данные, в сочетании с возможными возможностями

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

Перекрестные соединения: все данные объединены каждый из возможных способов

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

Объекты и ограничения DataRelation

DataRelationОбъект также используется для создания и принудительного применения следующих ограничений.

• Ограничение UNIQUE, которое гарантирует, что столбец в таблице не содержит дубликатов.

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

Ограничения, указываемые в DataRelation объекте, реализуются путем автоматического создания соответствующих объектов или настройки свойств. При создании ограничения внешнего ключа с помощью DataRelation объекта экземпляры ForeignKeyConstraint класса добавляются в DataRelation ChildKeyConstraint свойство объекта.

Ограничение UNIQUE реализуется либо путем простого присвоения Unique свойству столбца данных значения или путем добавления экземпляра UniqueConstraint класса к DataRelation ParentKeyConstraint свойству объекта. Сведения о приостановке ограничений в наборе данных см. в разделе Отключение ограничений при заполнении набора данных.

Правила ссылочной целостности

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

• При обновлении родительской записи

• При удалении родительской записи

• Когда изменение принято или отклонено

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

Дополнительные сведения об обновлениях в таблицах наборов данных см. в разделе Сохранение данных в базе данных.

Отношения только для ограничений

При создании DataRelation объекта можно указать, что отношение должно использоваться только для принудительного применения ограничений, то есть оно также не будет использоваться для доступа к связанным записям. Этот параметр можно использовать для создания набора данных, который является немного более эффективным и содержит меньше методов, чем один с возможностью связанных записей. Однако доступ к связанным записям будет невозможен. Например, отношение только с ограничением не позволяет удалить родительскую запись, которая по-прежнему имеет дочерние записи, и вы не сможете получить доступ к дочерним записям через родительский элемент.

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

2.4.3.2. Установка связей между таблицами в СУБД Access

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

Для создания связей необходимо закрыть все таблицы и выбрать команду «Схема данных» из меню Сервис, появится активное диалоговое окно «Добавление таблицы» на фоне неактивного окна Схема данных.

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

Следующий шаг — это установка связей между таблицами в окне Схема данных. Для этого в окне Схема данных необходимо отбуксировать (переместить) поле КодГруппы из таблицы Группы на соответствующее поле таблицы Студенты, в результате этой операции появится окно «Изменение связей».

В появившемся окне диалога «Изменение связей» необходимо активизировать флажки: «Обеспечить целостность данных», «каскадное обновление связанных полей» и «каскадное удаление связанных записей», убедиться в том, что установлен тип отношений один-ко-многим и нажать кнопку Создать.

В окне Схема данных появится связь один-ко-многим между таблицами Группы студентов и Студенты. Аналогичным образом надо связать поля КодСтудента в таблицах Студенты и Успеваемость, а затем поля КодДисциплины в таблицах Успеваемость и Дисциплины. В итоге получим Схему данных, представленную на рисунке.

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

Далее необходимо осуществить заполнение всех таблиц. Заполнение таблиц целесообразно начинать с таблицы Группы студентов, так как поле Код группы таблицы Студенты используется в качестве столбца подстановки для заполнения соответствующего поля таблицы Студенты.

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

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

На экране появится структура таблицы БД в режиме таблицы. Новая таблица состоит из одной пустой строки.

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

Для заполнения поля MEMO в таблице (колонка Место рождения) нажимаем комбинацию клавиш , предварительно установив курсор в поле MEMO. Открывается диалоговое окно Область ввода, после ввода или редактирования данных в этом окне щелкаем на кнопке ОК.

После заполнения таблица Студенты имеет следующий вид.

Аналогичным образом заполняются остальные таблицы: Группы Студентов, Успеваемость, Дисциплины.

В приложении Access применяются различные методы перемещения по таблице. Переходить от записи к записи можно с помощью: клавиш управления курсором; кнопки из области Запись, расположенной внизу таблицы в режиме таблицы; команды Правка — Перейти.. Для перемещения от поля к полю (слева направо) применяются клавиши Tab и Enter, а в обратном направлении Shift+Tab.

Поиск данных в таблице большого объема, который выполняется с помощью кнопок перехода, может занять много времени, поэтому для поиска и замены данных в полях необходимо использовать команду Правка — Найти. Откроется окно диалога. В поле Образец диалогового окна поиска указывается искомый объект и осуществляется поиск. Для замены данных в полях необходимо перейти на вкладку Замена.

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

Обучение в интернет, . Обратная связь

Свойства таблиц и полей в Access

У полей и таблиц есть свойства, позволяющие управлять их работой и характеристиками:

1. Это свойства таблицы.
2. Это свойства поля.

В БД Access под свойствами таблицы понимают атрибуты, которые определяют её работу и внешний вид. Свойства таблицы задают в «Конструкторе» на странице свойств таблицы. Допустим, вы можете задать свойство «Режим по умолчанию», если хотите указать, как она должна по умолчанию отображаться.

Свойство поля применимо к конкретному полю в таблице, определяя его характеристики либо какой-нибудь поведенческий аспект. Некоторые свойства можно задать в режим таблицы Access. Кроме того, можно настроить любые свойства в «Конструкторе», используя область «Свойства поля».

Ограничения

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

Существует два типа ограничений: ограничение на уровне одного поля или поля и ограничение на уровне нескольких полей или таблиц. Оба типа ограничений можно использовать в заявлении CREATE TABLE или ALTER TABLE.

Ограничение на одно поле, также известное как ограничение на уровне столбцов, объявляется с самим полем после того, как было объявлено поле и тип данных. Используйте таблицу клиентов и создайте основной ключ с одним полем в поле CustomerID. Чтобы добавить ограничение, используйте ключевое слово CONSTRAINT с именем поля.

Обратите внимание, что имя ограничения дано. Можно использовать ярлык для объявления основного ключа, полностью отметаемого в пункте CONSTRAINT

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

Чтобы снять ограничение, используйте пункт DROP CONSTRAINT с заявлением ALTER TABLE и укай имя ограничения.

Ограничения также могут использоваться для ограничения допустимых значений для поля. Можно ограничить значения NOT NULL или UNIQUE или определить ограничение проверки, которое является типом бизнес-правила, которое можно применить к полю. Предположим, что необходимо ограничить (или ограничить) значения полей имени и фамилии уникальными, что означает, что никогда не должно быть сочетания имени и фамилии, одинаковой для любых двух записей в таблице. Поскольку это ограничение с несколькими полями, оно объявляется на уровне таблицы, а не на уровне поля. Используйте пункт ADD CONSTRAINT и определите список с несколькими полями.

Ограничение проверки — это мощная SQL, которая позволяет добавлять проверку данных в таблицу, создавая выражение, которое может ссылаться на одно поле или несколько полей в одном или нескольких таблицах. Предположим, что необходимо убедиться, что суммы, вписаные в запись счетов, всегда больше $ 0,00. Для этого используйте ограничение проверки, объявив ключевое слово CHECK и выражение проверки в пункте ADD CONSTRAINT из заявления ALTER TABLE.

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

Предположим, что необходимо проверить кредитный лимит каждого клиента, прежде чем он будет добавлен в таблицу клиентов. Используя заявление ALTER TABLE с оговорками ADD COLUMN и CONSTRAINT, создайте ограничение, которое будет проверять значение в таблице CreditLimit для проверки кредитного лимита клиента. Используйте следующие SQL для создания таблицы tblCreditLimit, добавьте поле CustomerLimit в таблицу tblCustomers, добавьте ограничение проверки в таблицу tblCustomers и проверьте ограничение проверки.

Обратите внимание, что при выполнении заявления UPDATE TABLE вы получаете сообщение о том, что обновление не удалось, так как оно нарушило ограничение проверки. Если вы обновим поле CustomerLimit до значения, равного или менее 100, обновление будет успешным

Поддержка и обратная связь

Есть вопросы или отзывы, касающиеся Office VBA или этой статьи? Руководство по другим способам получения поддержки и отправки отзывов см. в статье Поддержка Office VBA и обратная связь.

Как определить связи между таблицами

При создании связи между таблицами связанные поля не должны иметь одни и те же имена. Однако связанные поля должны иметь один и тот же тип данных, если только поле первичного ключа не является полем AutoNumber. Вы можете сопоставить поле AutoNumber с полем Number, только если свойство FieldSize обоих совпадающих полей совпадает. Например, можно сопоставить поле AutoNumber и поле Number, если свойство theFieldSizeproperty обоих полей имеет значение Long Integer. Даже если оба совпадающих поля являются числовыми полями, они должны иметь параметр sameFieldSizeproperty.

Как определить связи «один ко многим» или «один к одному»

Чтобы создать связь «один ко многим» или «один к одному», выполните следующие действия.

1. Закройте все таблицы. Нельзя создавать или изменять связи между открытыми таблицами.

2. В Access 2002 и Access 2003 выполните следующие действия.

   1. Нажмите F11, чтобы переключиться в окно базы данных.
   2. В меню Инструменты выберите Связи.

   В Access 2007, Access 2010 или Access 2013 нажмите Связи в группе Показать/Скрыть на вкладке Инструменты базы данных.

3. Если вы еще не определили какие-либо связи в базе данных, автоматически отобразится диалоговое окно Показать таблицу. Если вы хотите добавить таблицы, которые нужно связать, но диалоговое окно Добавление таблицы не отображается, нажмите Добавить таблицу в меню Связи.

4. Дважды щелкните имена таблиц, которые необходимо связать, а затем закройте диалоговое окно Добавление таблицы. Чтобы создать связь между одной и той же таблицей, добавьте эту таблицу два раза.

5. Перетащите поле, которое вы хотите связать, из одной таблицы в связанное поле в другой таблице. Чтобы перетащить несколько полей, нажмите Ctrl, нажмите на каждое поле, а затем перетащите их.

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

6. Откроется диалоговое окно Изменение связей. Убедитесь, что имена полей, отображаемые в двух столбцах, верны. Вы можете изменить имена, если это необходимо.

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

7. Нажмите кнопку Создать, чтобы создать связь.

8. Повторите шаги с 4 по 7 для каждой пары таблиц, которые вы хотите связать.

   При закрытии диалогового окна Изменение связей Access спрашивает, хотите ли вы сохранить макет. Сохраняете ли вы макет или не сохраняете макет, созданные вами связи сохраняются в базе данных.

   Примечание

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

Как определить связь «многие ко многим«

Чтобы создать связь «многие ко многим», выполните следующие действия.

1. Создайте две таблицы, которые будут иметь связь «многие ко многим».

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

3. В связующей таблице установите первичный ключ, чтобы включить основные ключевые поля из двух других таблиц. Например, в связующей таблице «TitleAuthors» первичный ключ будет состоять из полей OrderID и ProductID.

   Примечание

   Чтобы создать первичный ключ, выполните следующие действия:

   1. Откройте таблицу в Конструкторе.

   2. Выберите поле или поля, которые вы хотите определить в качестве первичного ключа. Чтобы выбрать одно поле, нажмите на селектор строки для нужного поля. Чтобы выбрать несколько полей, удерживайте клавишу Ctrl, а затем нажмите селектор строки для каждого поля.

   3. В Access 2002 или в Access 2003 нажмите на Первичный ключ на панели инструментов.

      В Access 2007 нажмите на Первичный ключ в группе Инструменты на вкладке Дизайн.

      Примечание

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

4. Определите связь один-ко-многим между каждой основной и связующей таблицами.

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

Для того чтобы было удобно просматривать, создавать, удалять и модифицировать связи между таблицами, в Microsoft Access используется схема данных (Relationships) (рис. 2.27).

Чтобы открыть схему данных, необходимо выполнить команду Сервис, Схема данных (Tools, Relationships). По умолчанию схема будет содержать все таблицы со связями. Поэтому сюда будет включена, например, наша новая таблица «Список рассылки оповещений», которая была связана с таблицей «Клиенты» (Customers) при помощи Мастера подстановок (см. разд. «Использование Мастера подстановок» выше в данной главе).

Чтобы убрать какую-либо таблицу из схемы данных, необходимо щелкнуть правой кнопкой мыши на любом месте этой таблицы и из контекстного меню выбрать команду Скрыть таблицу (Hide table). Например, на рис. 2.27 таблица «Список рассылки оповещений» скрыта.

Удаление таблицы из схемы данных не означает удаление ее из базы данных, просто она не присутствует в схеме.

Чтобы вновь добавить в эту схему свернутую таблицу («Список рассылки оповещений») или таблицу, у которой связи еще не установлены, необходимо:

1. Щелкнуть правой кнопкой мыши на свободном пространстве схемы данных и из контекстного меню выбрать команду Добавить таблицу (Show table).
2. В диалоговом окне Добавление таблицы (Show table) раскрыть вкладку Таблицы (Tables), выбрать из списка таблицу «Список рассылки оповещений», и нажать кнопку Добавить (Add) (рис. 2.28).

Если нужно добавить к схеме несколько таблиц, придется повторить эту операцию для каждой таблицы.

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

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

Если нужная связь автоматически не создана, ее можно создать двумя путями. Первый путь — графический. Следует выбрать в главной таблице поле для связи, нажать левую кнопку мыши и перетащить поле во вторую таблицу. Отпустить левую кнопку мыши над тем полем подчиненной таблицы, с которым устанавливается связь. После этого появится диалоговое окно Изменение связей (Edit Relationships) (рис. 2.29).

Альтернативный вариант — выбрать команду Связи, Изменить связь (Relationships, Edit Relationships) из главного меню Access. Эта команда появляется в меню, когда открыто окно Схема данных (Relationships).

1. В диалоговом окне

Изменение связей (Edit Relationships) можно выбрать из списков названия связанных таблиц и полей для связывания. Если отношение между таблицами «один-ко-многим», то слева из списка Таблица/запрос (Table/ Query) выбирается главная таблица и поле в этой таблице, а справа из списка Связанная таблица/запрос — подчиненная и соответственно поле в ней. Если отношение «один-к-одному», то порядок таблиц значения не имеет. Если вы устанавливали связь графически, то все поля в списках уже выбраны, и нужно только определить правила ссылочной целостности.

По умолчанию устанавливается первый переключатель. Нажать кнопку ОК.

Когда создается новая связь, можно также воспользоваться кнопкой Новое (Create New) и в окне Создание (Create New) ввести имена связываемых таблиц и имена полей, используемых для связи. Нажать кнопку ОК.

После установки всех параметров связи необходимо нажать кнопку ОК в окне Параметры объединения (Edit Relationship) и закрыть окно Схема данных (Relationship), нажав на кнопку Закрыть в правом верхнем углу окна.

Связывание с данными Excel. Копирование связанных данных.

Данные Excel не обязательно импортировать в Access. В некоторых случаях с таблицей Excel можно создать связь. Такая связь является динамической. Это значит, что при внесении изменений в источник данных, т.е. в Excel, они изменяются и в Access, и наоборот. Чтобы продемонстрировать описываемый эффект на практике, настроим связь с файлом КаталогиЕхса!.xls, выполнив следующие действия.

1. Выберите в Access команду Файл › Внешние данные › Связь с таблицами.
2. В списке Тип файла выберите элемент Microsoft Excel (*.xls), укажите в списке файлов таблицу КаталогиЕхсеl.xls и щелкните на кнопке Связь. Кроме того, на файле можно дважды щелкнуть (для указания файлов в других таблицах используется элемент управления Папка, но в данном примере он не потребуется). Будет запущен мастер Связь с электронной таблицей.
3. В первую очередь следует указать, что именно будет связываться – вся таблица или только именованный диапазон. Оставьте выбранным переключатель Листы и щелкните на кнопке Далее.
4. В следующем окне можно указать заголовки столбцов Excel в качестве имен полей. Рекомендуется всегда устанавливать флажок Первая строка содержит заголовки столбцов. В противном случае имена полей будут обработаны Excel как обычные записи. Установив флажок, щелкните на кнопке Далее.
5. В последнем окне введите имя новой связанной таблицы СвязанныеКаталогиИзЕхсеl, щелкните на кнопке Готово, а затем на кнопке ОК в окне предупреждения. Среди других записей таблиц в окне базы данных появится запись новой таблицы, обладающая в отличие от записей всех других таблиц значком Excel, как показано на рис. 16.4.

Рис. 16.4. Связанный источник данных обрабатывается Access как файл Excel

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

Определения основных понятий

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

1. Предметная область – множество созданных таблиц в базе данных, которые связаны между собой с помощью первичных и вторичных ключей.
2. Сущность – отдельная таблица базы данных.
3. Атрибут – заголовок отдельного столбца в таблице.
4. Кортеж – это строка, принимающая значение всех атрибутов.
5. Первичный ключ – это уникальное значение (id), которое присваивается каждому кортежу.
6. Вторичный ключ таблицы «Б» – это уникальное значение таблицы «А», использующееся в таблице «Б».
7. SQL запрос – это специальное выражение, выполняющее определенное действие с базой данных: добавление, редактирование, удаление полей, создание выборок.

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

INNER JOIN

Прежде чем идти дальше и рассматривать другие типы связей, стоит изучить ещё один оператор SQL — INNER JOIN. Он используется для объединения строк из двух и более таблиц, основываясь на отношениях между ними. Для запроса используется следующий синтаксис:

Чтобы получить всех пользователей вместе с их профилями нам нужно выполнить следующий запрос:

Каждая строка из левой таблицы, сопоставляется с каждой строкой из правой таблицы, после этого проверяется условие.

Если мы хотим выбрать только некоторые столбцы, то после оператора SELECT нужно перед именем поля явно указать название таблицы, из которой оно берется: