MS Access Основные объекты Таблицы Запросы Инструмент

MS Access

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

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

Таблицы Данные в таблицах должны удовлетворять следующим принципам: • Каждое значение поля должно быть атомарным (не подлежащим разбиению на несколько значений) • Значения данных в одном и том же поле (колонке) должны принадлежать к одному типу • Каждая запись в таблице уникальна, в таблице не существует двух записей с полностью совпадающим набором значений ее полей • Каждое поле имеет уникальное имя • Последовательность полей в таблице любая, исключение – ключевые поля д. б. первыми • Последовательность записей любая

Таблицы • Каждое поле имеет имя и тип данных • Имя не более 64 символов, исключая ? ; * < > | , / т. п. • Тип данных определяет: – Размер (количество байтов) – Диапазон возможных значений – Множество операций и функций • От типа и размера поля зависит скорость доступа к БД и объем файла • Ключевое поле - поле, значение которого служит для однозначного определения (идентификации) записи в таблице • Ключ - одно или несколько ключевых полей, позволяющих идентифицировать записи таблицы и организовывать связи между таблицами

Тип данных • Текстовый -символьная строка длиной 1. . 255 символов (предназначено для хранения текста). Независимо от длины поля данные хранятся в записи переменной длины, т. е. MS Access не сохраняет пустые символы в неиспользуемой части поля • Поле МЕМО - символьная строка длиной до 65535 символов, отображаемых форме или таблице, максимальная длина 2 Гб. Может быть использовано для атрибута типа Характеристика сотрудника или иной описательной информации. Не может быть ключевым или индексированным • Числовой -используется в математических вычислениях. Числовое поле может иметь следующую длину: – Байт - целые числа от 0 до 256 – Целое - целые числа от -32768 до 32767 (2 байта) – Длинное целое - от -2147483648 до 2147483647 (4 байта) – С плавающей точкой -от -3. 402823 E 38 до 3. 402823 Е 38 (4 байта) – С плавающей точкой (8 байт)

Тип данных • Счетчик – числовой тип данных, имеет размер длинное целое (4 байта). Значения поля формируются автоматически возрастающей последовательностью или случайно. В таблице не может быть более одного такого поля. Обычно используется в качестве первичного ключа. Изменить значение поля типа счётчик нельзя • Денежный - данные о денежных суммах, хранящиеся с 4 знаками после запятой (8 байт) • Дата/Время - длина поля 8 байт (100. . 9999 год, датацелая часть, время-дробная) • Логический - логические данные (да/нет, истина/ложь, вкл/выкл, значение истина=[-1], ложь=[0]). Поле логического типа не может быть ключевым • Объект OLE - длинные тексты, картинки , диаграммы , звуки и т. д. не может быть ключевым

Формат поля • Определяет способ отображения содержимого поля на экране и при печати • Для текстовых, числовых, даты и времени, логических данных существуют встроенные форматы

Пользовательские форматы Числовые данные – 4 секции • Положительные; отрицательные; нулевые; пустые • Специальные символы – 0 заменяется цифрой или 0 – # цифра или пусто – $ выводится $ – % выводится % – Пробел выводится пробел – "символы в кавычках" выводится без изменения – ! выравнивание чисел по левому краю – * заполняет доступное пустое пространство следующим символом – [цвет] – выводит следующий символ как константу Формат # ##0"p. ", 00"к. отображается 34 p. , 67 к. # ##0"р. "; -# ##0"р. " отображается 34 р.

Пользовательские форматы Текстовые данные и MEMO 1 или 2 секции • Отображение текста; отображение строк нулевой длины • Специальные символы: –@ обязательный текстовый символ или пробел –& необязательный текстовый символ –< преобразует все символы в строчные –> преобразует все символы в прописные • Примеры (@@@)@@@-@@-@@ – При вводе 1234567 отображается ( )123 -45 -67 > – При вводе мария отображается МАРИЯ

Пользовательские форматы Логические данные – 3 секции ; для ИСТИНА; для ЛОЖЬ • 1 -ая секция не влияет на формат, но точка с запятой [; ] обязательна • Во второй и третей секции указываются строки символов, заключённые в кавычки, которые будут отображаться в случае ИСТИНА или ЛОЖЬ Пример ; "Счет выписан"[Красный]; "Счёт не выписан"[Синий]

Маска ввода • Маска ввода используется в полях (в таблицах и запросах), в текстовых полях и в полях со списком (в формах) для форматирования данных и управления вводимыми значениями • Значение свойства Маска ввода состоит из текстовых знаков (таких как пробелы, точки, тире, скобки) и специальных символов, определяющих тип значений, которые могут быть введены в данную позицию Описание маски ввода может состоять из 3 -х разделов • 1 - сама маска • 2 – указывает следует ли сохранять текстовые символы. 0 – сохранять, 1 или ПУСТО – сохранять только введённые символы • 3 – символ, отображаемый на месте пустых символов. Для отображения пробела указывается " ", иначе символ подчёркивания _

Специальные символы маски ввода 0 9 # Цифра (от 0 до 9, ввод обязателен; знаки плюс [+] и минус [-] не допускаются) Цифра или пробел (ввод не обязателен; знаки плюс и минус не допускаются) Цифра или пробел (ввод не обязателен; пустые знаки преобразуются в пробелы, допускаются знаки плюс и минус) L Буква (от A до Z или от А до Я, ввод обязателен) ? Буква (от A до Z или от А до Я, ввод необязателен) A Буква или цифра (ввод обязателен) а Буква или цифра (ввод необязателен) & Любой знак или пробел (ввод обязателен) C Любой знак или пробел (ввод необязателен). , : ; - / Десятичный разделитель и разделители тысяч, значений дат и времени. (Отображаемый знак зависит от настроек языка и стандартов на панели управления Microsoft Windows. ) < Указывает перевод всех следующих знаков на нижний регистр > Указывает перевод всех следующих знаков на верхний регистр ! Указывает заполнение маски ввода справа налево, а не слева направо. Заполнение маски знаками всегда происходит слева направо. Восклицательный знак в маске ввода можно помещать в любую позицию Указывает ввод любого следующего знака в качестве текстовой константы. Используется для отображения всех перечисленных в данной таблице знаков как текстовых констант (например, A выводится как знак «A» ) Пароль Значение Пароль, заданное для свойства Маска ввода (Input. Mask), создает поле для ввода пароля. Любой знак, введенный в поле, сохраняется как знак, но отображается как звездочка (*).

Маска ввода Примеры маски ввода (000") "000-0000 отображается (123) 456 -5788 при вводе(___) 778 -1265 отображается сообщение об ошибке (###)#99-99 отображается (812)123 -45 -67 (231) 23 -56 -41 (###)###-#99!-99! отображается -123 -65 -48 (+7 )921 -456 -87 -95 >L<? ? ? ? ? ; 1; " "; ; как в предложении осипенков отображается Осипенков

Если для поля определены как ФОРМАТ отображения, так и МАСКА ВВОДА, то • при добавлении и редактировании данных используется Маска ввода • параметр ФОРМАТ поля (Format) определяет отображение данных после сохранения записи

Свойства поля • Подпись – текст отображаемый в заголовке столбца таблицы, подпись присоединённого элемента надпись в формах и отчётах • Максимальное количество символов 2048 • Если подпись не задана, то отображается имя поля

Свойства поля • Значение по умолчанию – значение автоматически подставляемое в поле для каждой записи. Может быть изменено пользователем • Для поля внешнего ключа значение по умолчанию должно соответствовать одному из значений ключевого поля главной таблицы

Свойства поля • Условие на значение – ограничение, накладываемое на вводимые в поле данные • Несоответствие вводимых данных условию на значение вызывает вывод сообщения об ошибке • Сообщение об ошибке – текст, выводимый при не соблюдении условия на значения

Свойства поля Обязательное поле – допустимые значения: ДА / НЕТ • ДА – MS Access не позволяет перейти к другой записи не заполнив данными поле • НЕТ – данные можно не вводить. Пустому полю присваивается значение NULL Индексированное поле – допустимые значения: • ДА (совпадения допускаются) • ДА (совпадения не допускаются) • НЕТ Индекс – упорядоченный список значений со ссылками на записи в которых хранятся значения Индексы ускоряют поиск и сортировку данных Максимальное количество индексов в таблице 32

Свойства поля Сжатие Юникод - значение по умолчанию Да для полей типа «Текстовый» , «Поле MEMO» и «Гиперссылка» Все знаки, первый байт которых равен 0, будут сжиматься при сохранении и восстанавливаться при выборке Режим IME (Input Method Editor) – обеспечивает ввод текста на восточных языках путём преобразования нажатий клавиш в сложные знаки этих языков. IME рассматривается как дополнительный вид раскладки клавиатуры

Свойство поля - Подстановка Применяется с текстовым, числовым и логическим полям Тип элемента управления - ПОЛЕ СО СПИСКОМ Тип источника строк - СПИСОК ЗНАЧЕНИЙ - СПИСОК ПОЛЕЙ - ТАБЛИЦА/ЗАПРОС Источник строк – определяет источник данных элемента управления Если тип источника строк – список значений, то источник строк – список значений, разделённых точкой с запятой [; ]

Свойство поля - Подстановка Тип источника строк – список полей, то Источник строк - имя таблицы - имя запроса - инструкция SQL

Определение ключевых полей В MS Access можно выделить три типа ключевых полей: • простой ключ (поле с неповторяющимися значениями) • составной ключ (комбинация из нескольких полей ) • счетчик (последовательные значения - увеличиваются на 1 при создании новой записи или случайное число ) Если Вы забыли самостоятельно определить ключевое поле MS Access предложит Вам задать ключевое поле сам. Не отказывайтесь от этого сразу, а лучше подумайте какое поле с не повторяющими значениями можно выбрать в качестве ключевого или согласитесь на счетчик

Определение ключевых полей

Relation – Отношение или Связь • Достоинство РЕЛЯЦИОННЫХ баз данных состоит в том , что можно хранить логически сгруппированные данные в разных таблицах и задавать связи между ними, объединяя их в единую базу • Для задания связи таблицы должны иметь поля с одинаковым типом данных • Связь между таблицами устанавливает отношение между совпадающими значениями в этих полях • Такая организация данных позволяет уменьшить избыточность хранимых данных , упрощает их ввод, удаление, поиск

Типы связей • Отношение (связь) один - ко – многим (1: M или M: 1) • Отношение ( связь) один - к – одному (1: 1) • Отношение ( связь) многие ко многим (N: M). Схема данных – графическое представление связей между таблицами

Отношение (связь) один - ко – многим (1: M или M: 1) • Каждой записи в таблице А (значению первичного ключа) соответствует несколько записей в таблице В (внешний ключ), а запись в таблице В не может иметь более одной соответствующей ей записи в таблице А • Таблица А называется главной , а таблица В подчиненной таблицей • Таблицу А называют родительской таблицей, а В – дочерней

Внимание! Первичный ключ главной таблицы Клиент внедряется в подчиненную таблицу Заказ. На рисунке вторичный или чужой ключ обозначен символом FK (Foreign Key)

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

Отношение ( связь) один - к – одному (1: 1) • Запись в таблице А может иметь не более одной связанной записи в таблице В и наоборот • Содержимое таких таблиц как правило можно объединить в одну. Этот тип связи используется довольно редко • Такие связи имеет смысл устанавливать для разделения таблиц с очень большим количеством полей, для отделения части таблицы по соображениям защиты или если не все данные из одной таблицы используются в другой

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

Отношение ( связь) многие ко многим (N: M) У одного автора может быть несколько книг, а у книги может иметься несколько авторов Такую связь в MS Access реализовать невозможно. Такая связь реализуется только при помощи третьей таблицы – таблицы связки, имеющей составной первичный ключ

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

Установление связи между таблицами Условия для создания связи: • Связываемые поля имеют одинаковый тип данных • Исключение составляет поле счетчик, так как поле счетчик не может быть использовано в качестве вторичного ключа. Поле счетчик может быть связано с числовым полем, имеющим размер длинное целое • Таблицы хранятся внутри одной БД • Главная таблица связывается с подчиненной по ключу

Поле подстановки Содержит значение первичного ключа главной таблицы Способы создания • Мастер подстановки • Вручную Результат создания

Мастер подстановки Выбрать Мастер подстановок в столбце Тип Данных

Создание вручную 1. 2. 1 Выбрать для поля Тип данных Перейти на вкладку Подстановка 2 3 5 4 Поля, отмеченные заполняются вручную

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

Создание вручную В построителе запросов • Выключить в бланк запроса необходимые поля • Указать условия отбора • Сортировку • Отображение на экране Поля, отмеченные заполняются вручную

Создание связи • Осуществляется в окне СХЕМЫ ДАННЫХ • Выделить ключевое поле в главной таблице • Перетащите его на соответствующее поле подчиненной таблицы • Автоматически открывается окно Изменение связей Переместить поле Код. Отдела

Параметры связи

Результат создания связи

Обеспечение целостности • Условие целостности данных обеспечивает соответствие каждого значения, которое вводится в поле внешнего ключа, существующему значению в связанном поле первичного ключа • Нельзя в поле внешнего ключа связанной таблицы ввести значение, отсутствующее в ключевом поле главной таблицы • Нельзя удалить запись в главной таблице, если есть связанные с ней записи в подчинённой таблице Главная таблица Код. Д Название. Долж 1 Менеджер 2 Кассир 3 Водитель Подчинённая таблица Код. С Должность 102 Менеджер 202 Кассир 302 Менеджер 402 Секретарь 502 Менеджер

Каскадное обновление связанных полей • Изменение значения ключевого поля в главной таблице автоматически обновляет значения связанных полей подчинённых таблиц Каскадное удаление связанных записей • Удаление записи из главной таблицы автоматически приводит к удалению связанных с ней записей в подчинённых таблицах

Создание БД • Создание базы данных начинается с работы с листом бумаги и карандашом • Прежде, чем начать создавать БД необходимо ее спроектировать • Профессиональные разработчики БД используют CASE средства проектирования БД • Термин CASE ( Computer Aided System Engineering) дословно переводится как разработка программного обеспечения с помощью компьютера

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

Диаграмма прецедентов

Создание БД Шаг 1 – определение объектов • Для определения объектов существует множество формальных и неформальных подходов. Простейший способ – взять описание предметной области(возможно, полученное из интервью с заказчиком) и выделить в нем существительные – Товар – Клиент – Заказ 1. Товар – Прайс лист 2. Клиент – Накладная 3. Заказ 4. Вид – Вид 5. Сотрудник – Сотрудник 6. Должность – Должность 7. Отдел – Отдел

Создание БД Шаг 2 – определение атрибутов (свойств объектов)

Создание БД Шаг 3 – определение ключевых полей

Создание БД Шаг 4 – определение связей

Создание БД Шаг 5 – создание БД

Открыть окно схемы данных Окно БД Объекты БД

Режим таблицы • Число столбцов 20 • Число строк 30 • Тип данных для каждого поля определяется автоматически исходя из введённых значений • Требуется доработка структуры таблицы в режиме конструктора

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

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

Конструктор таблиц

Мастер таблиц

Импорт таблиц MS Access обеспечивает импорт таблиц из файлов различных форматов • Других баз данных • Электронных таблиц • Текстовых файлов Результатом импорта является создание таблицы в формате MS Access и заполнением её данными из исходной таблицы На вкладке таблицы выбрать Создать – Импорт таблиц

Импорт таблиц из баз данных MS Access Шаг 1 выбор исходного файла

Импорт таблиц из баз данных MS Access Шаг 2

Результат импорта Импортированная таблица в окне БД

Результат импорта Импортированная таблица с данными

Результат импорта Импортированная таблица в режиме конструктора

Импорт таблиц из баз данных Excel Шаг 1 выбор исходного файла

Импорт таблиц из баз данных Excel Шаг 2

Импорт таблиц из баз данных Excel Шаг 3

Импорт таблиц из баз данных Excel Шаг 4

Импорт таблиц из баз данных Excel Шаг 5

Импорт таблиц из баз данных Excel Шаг 6

Импорт таблиц из баз данных Excel Шаг 7

Импортированная таблица режим Конструктор

Импорт таблиц из текстовых файлов • MS Access необходима информация для определения начала и конца полей в каждой текстовой строке • Разделителями полей могут быть [, ] [; ] [Tab] [пробел] • Если специальный символ необходимо включить в данные, то все строки должны быть заключены в апострофы или двойные кавычки. Стиль всех записей один 'конфеты "Весна" ' или "конфеты 'Весна' " • Импорт таблиц из MS Word без предварительного преобразования не возможен 1 способ - Сохранить таблицу в формате RTF 2 способ - Преобразовать таблицу в текст - Сохранить как текст с разбивкой на строки