Тема лекції: Система управління базами даних Microsoft Access
План лекції 1. 2. 3. 4. 5. 6. Загальні відомості про бази даних і СУБД Access Проектування баз даних. Задання структури таблиць Встановлення зв’язків між таблицями. Сортування, фільтрування і пошук даних Оператори та вирази Microsoft Access Формування запитів і робота з ними Формування форм і робота з ними
1. Загальні відомості про бази даних і СУБД Access База даних (БД) – це єдине, централізоване сховище даних певної предметної області, до якої мають доступ багато програм. Програми, що забезпечують доступ до БД, називаються системами управління базами даних (СУБД). Для ПК використовуються реляційні БД. В основі реляційної БД лежить математичне відношення (від англ. Relation). Відношення у БД представляються у вигляді двовимірних таблиць. Елементами цих таблиць є записи і поля. Запис – це один рядок таблиці, а поле – один стовпець таблиці.
Дана СУБД забезпечує виконання таких функцій: створення таблиць даних; Ø додавання, вилучення даних; Ø зміна структури БД без втрати інформації; Ø моделювання логічних структур даних; Ø впорядкування (сортування та індексування) даних за певними критеріями (ключами); Ø ведення розрахунків із застосуванням функцій тощо. Ø
Під час запуску СУБД Access на екрані з’явиться вікно діалогу, яке запропонує створити нову БД або відкрити попередньо створену. При відкриванні попередньо створеної із наявного переліку необхідно вибрати ім’я БД і натиснути кнопку “ОК”. Якщо ж необхідно створити нову БД, то у даному вікні необхідно відмітити рядок “Новая база данных” і також натиснути “ОК”. Ці самі дії можна виконати, виконавши відповідно одну із команд меню програми Access: Файл/Создать – для створення нової або Файл/Открыть – для відкривання існуючої БД. У результаті виконання описаних дій на екрані з’явиться основне вікно програми, а в ньому відкриється вікно нової чи вже створеної БД. Структура основного вікна Access аналогічна до Word і Excel. Основне вікно нової (чи створеної) бази даних містить усі складові частини БД – таблиці, запити, форми, звіти, макроси, модулі.
Рис. 1.
№ ЖЕК Будинок у Дохід, грн. Прізвище Квартплат а, Дата грн. 1512 5 Титаренко П. В. 1000, 5 110 10. 01 1213 10 Карась А. Р. 500 90 01. 05. 01 1213 10 Котко М. С. 800, 4 110 05. 10. 01 1213 12 Бучма К. А. 560 85 02. 09. 00 1213 12 Шульга В. Д. 270, 7 130 25. 00 1512 59 Вакуленко С. І. 940, 1 90 15. 06. 00
Проектування баз даних. Задання структури таблиць У попередньому питанні вже згадувалося, що таблиці БД складаються і записів і полів. Інформація в полях (комірках) може мати строго визначені типи. Існують наступні типи полів даних: Ø текстовый – містить до 255 символів, включаючи літери, цифри, пропуски, спеціальні символи. За замовчуванням поле може мати до 50 символів. Для того, щоб змінити кількість символів у полі, необхідно в режимі конструктора таблиць у розділі Свойства поля на закладці Общие відкрити список Размер поля і встановити потрібний розмір;
Ø Ø поле мемо – використовується для збереження тексту великого обсягу (від 255 до 64000 символів). Це може бути додаткова інформація до тієї, що є в таблиці; числовой – для збереження числових значень, з якими планується виконувати розрахунки (в інших випадках краще застосовувати текстові поля); дата/время – для збереження значень у вигляді дати і часу. З полями такого типу можна виконувати розрахунки – сумувати дати, додавати до дати числа, порівнювати між собою тощо; денежный – використовується для введення даних із грошовими одиницями.
При роботі із таблицями БД можливі два режими: режим таблиці і режим конструктора. У режимі таблиці на екрані відображається сама таблиця із вмістом її комірок. У цьому ж режимі можна редагувати дані, вставляти нові, вилучати існуючі записи. У режимі конструктора на екрані відображаються лише назви стовпців і властивості полів. У цьому режимі можна задати тип для вибраного поля, а також і його конкретні властивості: кількість знаків після коми для числових даних, кількість символів для текстових даних, формат представлення дати, маску її вводу, умову введення числових даних, повідомлення при неправильному введенні даних тощо. Переключення між режимами здійснюється за допомогою команд: Вид/Конструктор або Вид/Режим таблицы. Переключення між режимами відображення таблиці також можна здійснювати за рахунок кнопки “Вид” на панелі інструментів: або.
Кожна таблиця БД може мати ключове поле, яке містить унікальні значення (коди, номери, шифри). Значення таких полів використовуються для створення зв’язку між двома таблицями. Ключ є ідентифікатором кожного запису БД і не може мати нульових значень або таких, що повторюються. Якщо ключове поле не створене користувачем, то під час запису таблиці на диск ПК програма запропонує створити таке поле автоматично. У такому випадку Access створить полелічильник, тобто кожний запис матиме свій порядковий номер.
Для визначення ключового поля користувачем необхідно таблицю відкрити в режимі конструктора. Далі необхідно виділити поле, яке має бути ключовим і виконати команду Правка/Ключевое поле або натиснути на панелі інструментів кнопку “Ключевое поле”. У результаті зліва від назви поля на екрані з’явиться зображення ключа. Ключ може складатися з декількох полів, тоді він називається складеним. Наприклад, у таблиці “Адреси” ключ мітить значення полів № ЖЕКу і Будинок. Для створення такого ключа у вікні конструктора спочатку виділяються необхідні поля, утримуючи натиснутою клавішу [Ctrl], після чого виконується команда Правка/Ключевое поле.
Кожна таблиця БД складається із полів даних, що мають імена і типи, а також можуть мати описи. Ім’я поля може мати довжину до 64 символів і може складатися з літер, чисел, знаків пунктуації і пропусків. Для створення нової таблиці в основному вікні БД відкривають закладку Таблицы, після чого використовується один із способів: 1) на панелі інструментів натискається кнопка “Создать”, після чого у вікні Новая таблица двічі натискається запис “Конструктор”; 2) виконується команда меню Вставка/Таблица у результаті виконання якої з’явиться те ж саме вікно, у якому знову ж таки необхідно натиснути на записі “Конструктор”; 3) в основному вікні БД двічі натискають на команду Создание таблицы в режиме конструктора.
У вікні конструктора таблиць визначають імена полів та їх типи відповідно до проекту БД. Після введення імені поля у стовпці “Имя поля” перехід до стовпця “Тип данных” здійснюється натискування клавіші або за допомогою лівої мишки. Встановивши курсор у відповідне поле у стовпці “Тип данных”, у ньому з’явиться кнопка списку, що розкривається. Натиснувши цю кнопку, з’явиться список можливих типів поля (текстове, числове тощо), із якого мишкою вибирається необхідне. При необхідності в нижній частині вікна конструктора (Свойства поля) можна задати конкретні властивості поля. Перехід до введення наступного імені поля здійснюється знову натискування клавіші або за допомогою лівої мишки. Ø Після створення структури таблиці в режимі конструктора її необхідно закрити кнопкою або використати комбінацію клавіш на клавіатурі [Ctrl+F 4]. У результаті на екрані дисплея з’явиться запит для визначення імені таблиці. Ø
Наступним етапом створення таблиці є внесення даних у відповідні поля, тобто її заповнення. Для цього в основному вікні БД виділяється назва таблиці і на панелі інструментів натискається кнопка “Открыть” або двічі натискається ліва мишка на назві таблиці. У результаті описаних дій таблиця буде відкрита в режимі таблиці. Вона матиме стільки полів (стовпців), скільки було задано імен на попередньому етапі в режимі конструктора. Заповнення полів таблиці здійснюється з клавіатури, а перехід між полями одного запису за допомогою клавіш або [Tab]. Переходити між полями також можна за допомогою мишки.
Редагування таблиці. Для вилучення із таблиці певного поля чи запису їх необхідно виділити в режимі таблиці: натиснути лівою мишкою зліва від запису чи зверху над полем. Далі для видалення запису (рядка) використовується команда Правка/Удалить запись або клавіша на клавіатурі [Del]. У результаті на екрані з’явиться вікно із попередженням про наступне видалення запису. У цьому вікні необхідно натиснути кнопку “Да” для видалення або “Нет” для відміни видалення. Для видалення поля (стовпця) використовується команда Правка/Удалить столбец. На екрані також з’явиться попередження про можливе видалення. Видалити поле можна ще в режимі конструктора, виділивши його мишкою зліва і натиснувши на клавіатурі клавішу [Del]. Для добавлення нового поля в режимі таблиці виділяється те поле, перед яким необхідно вставити нове і виконується команда Вставка/Столбец, а для добавлення нового запису – виділяється той, перед яким буде вставлений новий, і виконується команда Вставка/Новая запись.
Для зміни ширини поля необхідно перейти в режим таблиці, підвести мишку до межі полів (при цьому вказівник мишки прийме вигляд ), захопити її лівою клавішею і перетягнути на необхідну ширину. Ширину поля можна також автоматично вирівняти, двічі натиснувши на його межі ліву мишу – ширина поля стане рівною довжині найбільшого запису в комірках даного поля.
3. Встановлення зв’язків між таблицями. Сортування, фільтрування і пошук даних Між даними різних таблиць можна встановити зв’язок, використовуючи однакові значення їхніх полів. Функція зв’язку дає змогу користуватися даними кількох таблиць одночасно. Поля, що застосовуються для встановлення зв’язку між таблицями, повинні бути однакового типу і мати однакові значення.
Міжтабличні зв’язки можуть бути трьох типів: відношення один до одного. При такому зв’язку кожному запису першої таблиці відповідає не більше як один запис другої. І, навпаки, один запис другої таблиці відповідає одному запису першої. Відношення такого типу встановлюється при співпаданні значень ключових полів обох таблиць; Ø відношення один до багатьох – тип зв’язку, що використовується найчастіше. При такому зв’язку кожному запису першої таблиці можуть відповідати кілька записів другої, але один запис другої таблиці не може мати зв’язок більше як одним записом першої таблиці; Ø відношення багато до багатьох – тип зв’язку, що дає змогу встановити відношення між кількома записами першої таблиці та кількома записами другої, і навпаки. Ø
Для встановлення зв’язків в основному вікні БД треба активізувати закладку Таблицы і виконати команду Сервис/Схема данных або натиснути кнопку Схема данных. У результаті на екрані з’явиться однойменне вікно, в якому будуть представлені схематично таблиці із встановленими між ними зв’язками. Для добавлення у схему даних нових таблиць необхідно при відкритому вікні Схема данных виконати команду Связи/Добавить таблицу або на вікні натиснути праву мишку і в контекстному меню вибрати команду Добавить таблицу. У результаті виконання однієї з цих команд на екрані з’явиться вікно, в якому будуть зазначені всі таблиці БД. З наявного переліку вибирається потрібна і натискається кнопка “Добавить”.
Для зміни (або встановлення) зв’язку між полями двох таблиць у вікні Схема данных виділяється необхідне поле лівою мишкою і перетягується в таблицю, з якою потрібно встановити зв’язок. У вікні Изменение связей, що з’явиться на екрані, відмічається знаком параметр Обеспечение целостности данных і натискається кнопка “Объединение”. Збереження цілісності даних дає змогу уникнути запитів-сиріт, тобто таких, що не мають зв’язку з основною таблицею. У випадку наявності у таблиці складеного ключа потрібно у вікні Изменение связей визначити (вказати) кожне ключове поле в окремому рядку. У цьому випадку у вікні Схема данных між таблицями зв’язок встановлюється за значеннями двох полів.
Для видалення зв’язку між таблицями необхідно виконати одну із наступних дій: 1) виділити лінію зв’язку лівою мишкою і виконати команду Правка/Удалить; 2) виділити лінію зв’язку правою мишкою і натиснути клавішу [Del]; 3) виділити лінію зв’язку правою мишкою, а в контекстному меню вибрати команду Удалить. Для сортування даних в полях таблиці необхідно її спочатку відкрити за допомогою основного вікна БД. Далі активізується довільна комірка поля, по якому повинна бути відсортована таблиця і виконується одна з команд: Записи/Сортировка по возрастанию або Записи/Сортировка по убыванию. Теж саме можна виконати за допомогою однойменних кнопок на панелі інструментів Access: (аналогія – сортування записів в Excel).
Для фільтрування записів у таблиці БД вона повинна бути відкритою. Для фільтрування необхідно спочатку задати фільтр, виконавши команду Записи/ Фильтр/Изменить фильтр. У результаті виконання цієї команди на екрані в активній таблиці залишиться лише один рядок із пустими комірками. Активізуючи комірку поля, по якому необхідно фільтрувати таблицю, в ній з’являтиметься кнопка списку, що розкривається. У цьому списку буде перелік всіх елементів поля, з якого можна вибрати той, що є умовою фільтрування. Таким чином фільтр можна задати по одному або одночасно по декількох полях. Задавши умови фільтрування, далі для застосування фільтру необхідно виконати команду Фильтр/Применить фильтр. Для виходу із режиму зміни фільтру без його застосування використовується кнопка “Закрыть” на панелі інструментів. Після фільтрування записів у таблиці для її відновлення (для відображення всієї таблиці) виконується команда Записи/Удалить фильтр (аналогія з Excel – команда Данные/Фильтр/Отобразить все). Створений фільтр можна застосувати в довільний момент часу, виконавши для цього команду Записи/Применить фильтр. Якщо в таблиці необхідно відобразити лише ті записи, що задовольняють одному критерію, то можна виділити вміст комірки, який є цим критерієм, і виконати команду Записи/Фильтр по выделенному. Якщо ж необхідно тимчасово сховати записи, що задовольняють певному критерію, то виділяється комірка, що містить даний критерій, і виконується команда Записи/Фильтр/Исключить выделенное.
Застосування команди Записи/Фильтр/Расширенный фильтр дозволяє проводити сортування таблиці (в алфавітному чи зворотному порядках) одночасно по декількох полях (аналогія з Excel – вікно сортування одночасно по трьох параметрах), а також задавати умову по кожному полю для відображення необхідних записів (аналогія з Excel – застосування команди Данные/Фильтр/Расширенный фильтр).
Оператори та вирази Microsoft Access Вирази для проведення розрахунків використовуються лише при створенні запитів, форм і звітів. При роботі із звичайними таблицями розрахунки проводити не можна. Вираз – це інструкція для виконання розрахунків, що складається з одного або декількох операторів, ідентифікаторів, функцій.
Оператори. Для створення різних виразів використовуються наступні типи операторів: Арифметичні оператори Ø “+” – додавання (Кількість+на. Складі); Ø “–“ – віднімання (Дата. Замовлення– 10); Ø “*” – множення (Ціна*Кількість); Ø “/” – ділення (Сума/2); Ø “” – цілочисельне ділення (254; =6); Ø “Mod” – визначення остачі при цілочисельному діленні (25 Mod 4; =1); Ø “^” – піднесення в степінь (Сума^2).
Ø Ø Ø Оператори присвоєння і порівняння (=, <, >, <=, >=, <>) – використовуються для присвоєння значення і порівняння двох значень при створенні правил перевірки, умов відбору записів. Результатом виконання операторів порівняння є два можливі варіанти: True (Істина) або False (Фальш). Логічні оператори – для створення комбінації результатів двох або більше операцій порівняння. Таких операторів є три: And (логічне “І”), Or (логічне “АБО”) і Not (логічне “НЕ”). Оператор конкатенації (& – знаходиться над клавішею “ 7”) – для створення комбінації (об’єднання) рядків символів.
Ідентифікатори та функції. Ідентифікатор – це ім’я об’єкта (БД, таблиці, запиту, форми, звіту) та ім’я поля. Існують прості імена і складні. Останні складаються з імені об’єкта й імені поля, яке відокремлюються один від одного символом “!”. Наприклад: [Адреси]![Будинок].
Функції включають у вирази для виконання обчислень. В Access існує близько 140 функцій. Всі вони розділені на чотири групи: l функції обробки тексту – використовуються для роботи із символьними рядками: • chr(код) – повертає символ, що відповідає введеному в аргумент коду символу ANSI; наприклад: chr(100) – літера d; • format(ім’я, формат) – повертає відформатоване значення. Наприклад, format(date(), «dd-mm-yy» ) відповідає 08 -03 -02 (8 березня 2002 року); • val(вираз) – перетворює текст в число. Наприклад, val(34, 6) = 34, 6. l функції дати і часу – використовуються для роботи з полями типу дата/час: • date – повертає поточну дату; • day(дата) – повертає числове значення дня вказаної в аргументі дати; • time – повертає поточний час; • year(дата) – повертає числове значення року вказаної в аргументі дати. l математичні і тригонометричні функції: • abs(число) – модуль числа; • fix(число) – повертає цілу частину числа (відкидає дробову частину). Наприклад, fix(4, 6) = 4; • sqr(число) – обчислює значення квадратного кореня з числа. l фінансові функції.
Формування запитів і робота з ними Запити використовуються для відбирання записів, що задовольняють поставленій умові, а також для відновлення, добавлення і вилучення записів із таблиць. За допомогою запитів можна об’єднувати інформацію, вміщену в кількох зв’язаних таблицях. Результатом виконання запиту є набір даних, зібраних у таблиці. Є кілька типів запитів, з яких найчастіше використовується запит на вибірку. Для його створення в основному вікні БД активізується закладка Запросы і натискається кнопка “Создать”, у результаті чого на екрані з’явиться вікно Новый запрос. У цьому вікні виділяється команда Конструктор для створення структури запиту користувачем. Після натискування кнопки “ОК” на екрані з’явиться нове вікно Добавление таблицы із переліком наявних в БД таблиць. Для добавлення у запит певної таблиці її назва виділяється мишкою і натискається кнопка “Добавить”. Після добавлення у запит таблиць його вікно матиме вигляд аналогічний до вікна схеми даних при їх зв’язуванні (рис. 2).
Рис. 2.
Вікно конструктора запитів розділене на дві горизонтальні частини: у верхній схематично відображаються добавлені таблиці, а в нижній – представлена структура майбутнього запиту. У нижній частині вікна запиту є декілька наступних рядків: Поле (назва поля майбутнього запиту), Имя таблицы (ім’я таблиці, з якої вибирається назва поля), Сортировка (задається тип сортування записів), Вывод на экран (значком відмічається необхідність відображення на екрані даного поля), Условие отбора (при необхідності задається умова для фільтрування записів таблиці).
Для того, щоб у перший рядок з назвою Поле у відповідні комірки добавити імена майбутніх полів запиту, необхідно виконати одну із дій: Ø встановити курсор в необхідному стовпці даного рядка, відкрити список за допомогою кнопки і з наявного переліку вибрати ту назву, що потрібно; Ø виділити необхідне поле у вікні таблиці у верхній частині вікна запиту і перетягнути це поле на необхідну комірку в нижню частину вікна запиту. Ø Створений таким чином запит можна переглянути у вигляді звичайної таблиці, виконавши команду Вид/Режим таблицы. Ø Після завершення роботи із запитом його закривають з допомогою кнопки управління вікном. Перед остаточним закриванням на екрані з’явиться вікно повідомлення, у якому необхідно вказати ім’я нового запиту для його збереження. Зберегти запит можна також за допомогою команди Файл/Сохранить как.
Відбирання записів за умовою та їх сортування в запитах. Для створення умови відбирання записів запит відкривається в режимі Конструктора. Нехай, наприклад, потрібно відібрати лише ті записи, де значення доходу менше 800. для цього в полі Условие отбора під відповідним іменем поля (в даному випадку Дохід) з клавіатури вводять умову <800. Якщо потрібно впорядкувати записи за певним полем в порядку зростання чи спадання, то для цього в рядку Сортировка під іменем потрібного поля встановлюють курсор, розкривають список і вибирають умову сортування (по зростанню чи спаданню). Якщо в таблиці не потрібно відображати деякі стовпці, то в рядку Вывод на экран під іменем відповідного поля лівою мишкою знімають знак . Створення розрахункового поля. У запиті можна створити нове поле, значення якого буде розраховуватися на основі значень інших полів (значення цих полів повинні мати числовий формат). Наприклад, для кожного з мешканців, прізвища яких приводяться в базі даних, необхідно розрахувати 20% від доходу. Для цього курсор встановлюють у вільний стовпець рядка Поле, вводять формулу (у даному випадку =Дохід*0, 2), натискають клавішу , у результаті чого у полі з’явиться наступний вираз: Выражение 1: [Дохід]*0, 2. Після цього назву Выражение 1 можна замінити, наприклад, на 20%_дохід.
Ø Запит на створення нової таблиці. Запит такого типу використовується для створення нової таблиці, значення якої відповідають значенням запиту на відбирання. Для виконання таких дій запит відкривається в режимі Конструктора, з рядка головного меню виконується команда Запрос/Создание таблицы. У результаті на екрані з’явиться вікно Создание таблицы, в якому вводиться ім’я нової таблиці та місце її розташування. Далі – кнопка “ОК” і запит закривається. Наступним кроком є запуск запиту на виконання, для чого виконується команда Запрос/Запуск. Результат виконання цієї команди – вікно із повідомлення про кількість записів, що будуть включені в нову таблицю.
Формування форм і робота з ними Форми у СУБД Access використовуються для виведення значень певної таблиці у вигляді, що відповідає вимогам поставленої задачі. Форми також надають додаткові можливості для роботи з даними: відкривання інших форм або звітів у вигляді, зручному для користувача, з використанням графічних елементів (командні кнопки, закладки, кнопки-списки тощо); створення верхніх та нижніх колонтитулів; розрахунок загальних функцій і виразів. Створювати форму можна за допомогою майстра або власноручно. Майстра можна використати для створення заготовки (основи) форми, а потім провести її редагування в режимі Конструктора. Для створення форми в основному вікні БД необхідно відкрити закладку Формы, натиснути кнопку “Создать”, а в наступному вікні вибрати команду Мастер форм. У списку Выберите в качестве источника данных таблицу или запрос вказується таблиця (запит), для якої буде створена форма, а далі – кнопка “ОК”. Наступний крок – вибір мишкою імен тих полів, які увійдуть у форму. Наступні кроки побудови форми – вибір зовнішнього вигляду форми і її стилю. Задавши ім’я форми, натискається кнопка “Готово”, у результаті чого на екрані з’явиться вікно форми (по аналогії до вікна форми в Excel.
Скачать презентацию Тема лекції Система управління базами даних Microsoft Access
Access.ppt