Цей урок містить досить багато теоретичного матеріалу, проте розуміння його досить важливе для ефективної роботи з базами даних, тому раджу пройти цей урок вдумливо та не поспішаючи. Деякі моменти можуть бути складними для розуміння, але при повторенні все має бути зрозумілим. До цих пір ми мали справу лише з одною таблицею у базі даних. Таблиця називалася mp3 та містила дані про mp3-файли деякої колекції. Я навмисне не став робити у цій таблиці поля, що містять ім’я виконавця або назву альбому для пісень, з одної причини.
Справа в тому, що таблиця бази даних має являти собою відображення деякої сутності реального світу. Такою сутністю є пісня у файлі mp3, виконавець або альбом. Тому має сенс виконавців та альбоми організувати у формі окремих таблиць та зв’язати з таблицею пісень через посилання.
Звичайно так будують схеми баз даних, я поясню що означають елементи цієї схеми. Кожен великий прямокутник являє собою таблицю. На цій схемі їх три – mp3 (пісні у файлах mp3), albums (альбоми) та performers (виконавці).
Рядки у прямокутниках, позначені як “column” являють собою поля таблиці. Для таблиці mp3 це вже знайомі нам поля filename, title, filesize, bitrate та year. Також додане поле id, зараз поясню навіщо.
Оскільки таблиці певною мірою являють собою сукупність предметів або явищ реального світу, то об’єкти, що містяться в них, тобто рядки, мають бути унікальними. Дійсно, які б два предмети або явища ми не розглядали, між ними завжди є різниця, інакше це були б не два об’єкти, а один. Так само немає сенсу і в двох однакових рядках у таблиці, хоча, звісно, практично це можна зробити.
Для забезпечення унікальності у таблиці виділяють одне поле, що гарантовано є унікальним для таблиці і фактично визначає рядок. Це поле називають первинним ключем (primary key). На схемі первинний ключ позначається підкреслюванням та позначкою *PK.
У багатьох випадках для таблиці важко або неможливо виділити первинний ключ із інформаційних полів. Як приклад можна розглянути базу даних співробітників якоїсь установи – якщо таблиця містить Ф.І.П. робітника, дату народження, відділ, спеціальність, то яке б поле ми не брали, воно не є гарантовано унікальним – цілком можливо, що у одному відділі будуть працювати двоє людей з однаковими даними. В такому випадку рядку таблиці присвоюють ідентифікатор – деяке число, унікальне для таблиці і роблять його первинним ключем. Так ми і зробили для таблиці mp3 – додали ще одне поле id та зробили його первинним ключем. Про поля perf_id та album_id – дещо пізніше.
Так само ми створили ще дві таблиці – виконавці performers з тим самим id та іменем чи назвою name і альбоми albums з id, назвою альбому title та роком виходу year.
На схемі також видно стрілки, що показують зв’язки (залежності) між таблицями. Звісно, якщо є пісня, то вона може належати до якогось альбому. Так само вона належить якомусь виконавцеві. Для простоти задачі ми вважаємо, що пісня входить тільки до одного альбому і належить тільки одному виконавцеві. Тож до кожної пісні (рядку у таблиці mp3) ми можемо додати посилання на рядок у таблиці performers та посилання на рядок у таблиці albums. Цими посиланнями і є поля perf_id та album_id.
Якщо поле одної таблиці є посиланням на рядок у іншій таблиці, то таке поле називається зовнішнім ключем (foreign key, FK). Ця взаємодія показана у вигляді стрілки – наприклад, взаємодія таблиці mp3 з таблицею albums показана стрілкою до albums (поле у mp3 посилається на рядок у albums). Біля стрілки підписані назви ключів – зовнішній ключ у таблиці mp3 – FK_mp3_albums та первинний ключ у albums – UQ_albums_id. UQ (unique) вказує на те, що це поле унікальне (що завжди так для первинного ключа).
Числа біля стрілки (1 та 0..*) показують порядок зв’язку. так, порядок 0..*-1 означає, що одному рядку у таблиці albums відповідають нуль або більше рядків у таблиці mp3.
Також біля стрілки вказано, по яких полях іде зв’язок (album_id=id).
Тож нижче полів у прямокутниках таблиць (чорним кольором) записані ключі – первинні та зовнішні, а також ознака унікальності для первинного ключа.
З ключами розібралися (якщо це не зовсім зрозуміло, раджу перечитати ще раз, тому що це досить важливо), тепер мова піде про індекси.
Уявіть собі, що вам потрібно працювати з деякою енциклопедією. Енциклопедія являє собою набір статей. Вам необхідно швидко знаходити потрібну статтю. У цьому випадку є сенс розташувати статті у алфавітному порядку. Так само якщо вам потрібно знаходити деяке слово всередині статті, то логічно мати у енциклопедії алфавітний покажчик.
Такі самі покажчики існують і в таблицях баз даних і звуться індексами. Іх дія прозора для користувача – індекс треба створити у таблиці тільки один раз, а далі сервер сам вирішує, як і коли ними користуватися. Розглянемо приклад с таблицею mp3. Для початку приведемо її до вигляду, що його ми розглядали на початку уроку. Додамо до таблиці поля id, album_id та perf_id у phpMyAdmin на закладці “Структура”.
Після створення полів видалимо всі записи з таблиці mp3 (закладка “очистити”). Після чого подумаємо, які індекси нам потрібні. Коли вирішують таке питання, то звичайно думають, які операції з виборки будуть проводитися над таблицею. По-перше, поле id є первинним ключем, а по ключовому полю завжди будується індекс, причому це особливий тип індексу – унікальний індекс. Якщо у таблиці є поле з унікальним індексом, то сервер не дозволить вставити у таблицю рядок зі значенням цього поля, що є у таблиці.
Оскільки ми маємо зв’язувати цю таблицю по полях album_id та perf_id з таблицями albums та performers, то нам необхідно буде робити виборки по цих полях. А отже, необхідні індекси для цих полів. Створення індексу по полю виконується у phpMyAdmin кліком по піктограммі зразу справа від піктограмми створення первинного ключа. Тож створимо індекси для цих двох полів. В результаті у списку індексів будемо мати: На перший погляд може здатися, що оскільки створення індексу значно підвищує швидкість виборки з таблиці, то можна відразу створити індекси по усіх полях. Насправді це не дуже гарна ідея. Якщо розглянути приклад з енциклопедією, то індексація (тобто алфавітний покажчик чи розташування статей у алфавітному порядку) безперечно збільшує швидкість пошуку. Проте якщо стоіть задача додавання до енциклопедії нових статей, їх видалення або редагування, то більша частина часу цих операцій буде припадати саме на оновлення індексу – наприклад, додавання номера сторінки до багатьох позицій алфавітного покажчика. Так само відбувається і з таблицями баз даних. Що більше індексів має таблиця, то більша швидкість пошуку в ній, але й менша швидкість додавання, видалення та зміни рядків. Тож індекси доцільно створювати лише по тих полях, по яких безпосередньо йде пошук, а саме по тим, що будуть вказуватися у умові WHERE sql-запитів. Насправді швидкість виборки з таблиці по проіндексованому полю майже не залежить від кількості рядків у ній.
Тепер розглянемо запити не по одній таблиці, а по двох чи більше. Для цього створимо вже описані таблиці albums та performers. Для цього перейдемо безпосередньо до бази multimedia та створимо таблиці так само, як ми створювали таблицю mp3 пару уроків тому. Створимо також первинні ключі з поля id у цих таблицях.
Для того, щоб було на чому тренуватися, додамо пару рядків у кожну таблицю. Зверніть увагу, що ключ id у нас унікальний, тому повторень у цьому полі не може бути. В поле id будемо записувати числа за порядком – 1, 2, і т.д. Виходячи з mp3, що є зараз на моєму комп’ютері, в мене вийшли такі таблиці (у вас, залежно від вашого музичного смаку, скоріш за все вийдуть інші):
Зверніть увагу, що хоча тут і є деяка відповідність – альбом з id=1 належить виконавцеві з id=1, і альбом з id=2 належить виконавцеві з id=2, проте це не є правило, а просто співпадіння через те, що у розгляд я взяв лише кілька пісень. Тобто id у таблиці albums та id у таблиці performers не пов’язані аж ніяк. Тут зверніть увагу на те, що значення у полях album_id та perf_id вказують на саме ті id у таблицях albums та performers, яким належать відповідні пісні, наприклад, перший рядок має album_id=2, це значить, що він належить до альбому з id=2, тобто Angel’s Egg.
Тепер, коли в нас є деякий матеріал для практики, розглянемо, які sql-запити ми можемо виконувати для цих даних. Згадаємо, як ми робили виборку (SELECT) з одної таблиці. Це було приблизно так:
SELECT filename FROM mp3
Такий запит значив виборку усіх полів filename з таблиці mp3. Тепер поставимо задачу вивести назву пісні та її виконавця. Я покажу формування запиту по кроках. По-перше, нам треба об’єднати у FROM дві таблиці: ... FROM mp3, performers ...
Тепер нам треба визначити які поля вибирати: SELECT title, name FROM mp3, performers ...
Тепер нам треба зв’язати ці дві таблиці: SELECT title, name FROM mp3, performers WHERE mp3.perf_id=performers.id
Тобто в перекладі на людську мову цей запит може звучати так: “вибрати поля title та name з таблиць mp3 та performers, причому так, щоб поле perf_id рядка таблиці mp3 дорівнювало полю id таблиці performers.”
Насправді умова mp3.perf_id=performers.id реалізує згаданий вище зовнішній ключ – зв’язок між таблицями mp3 та performers по полям id=perf_id.
Будь ласка, якщо цей матеріал хоч трохи незрозумілий – перечитайте ще раз та спробуйте зрозуміти його як можна глибше, тому що це базові речі, які бажано розуміти на рефлекторному рівні. Якщо ж це зрозуміло – розглянемо виборку з трьох таблиць. Виберемо всі назви пісень з іменами їхніх виконавців та альбомами, до яких вони належать. sql-запит буде виглядати так:
SELECT mp3.title, performers.name, albums.title
FROM mp3, performers, albums
WHERE mp3.album_id=albums.id AND mp3.perf_id=performers.id
Деяка складність тут полягає у тому, що деякі поля (наприклад, id або title) мають однакову назву у різних таблицях. Тому, щоб було точно відомо, які саме поля ми маємо на увазі, ми додаємо назву таблиці перед назвою поля через крапку. Якщо ми хочемо, щоб назви стовпчиків у таблиці результату мали іншу назву (тут ми не можемо сказати, який title що означає), ми можемо скористатися так званими аліасами (alias). Аліаси вказуються після назви поля чи таблиці зі словом AS:
SELECT mp3.title AS song, performers.name AS performer, albums.title AS album
FROM mp3, performers, albums
WHERE mp3.album_id=albums.id AND mp3.perf_id=performers.id
Звісно, ми можемо додавати і інші умови до цього запиту, наприклад виберемо те саме, але щоб пісні були не старшими 1990-го року: SELECT mp3.title AS song, performers.name AS performer, albums.title AS album
FROM mp3, performers, albums
WHERE mp3.album_id=albums.id AND mp3.perf_id=performers.id
AND mp3.year>1990
Тож що ми засвоїли на цьому уроці.
- Рядки у таблицях мають бути унікальними. Запорукою унікальності є первинні ключі
- Таблиці можуть мати зв’язки по деяких полях, ці зв’язки називаються зовнішніми ключами
- По полях таблиць можна створювати індекси, що збільшують швидкість пошуку в таблицях
- Можна будувати запити для кількох таблиць, об’єднуючи іхні рядки за допомогою зовнішніх ключів
Передерто з http://
www.ua-admin.com/
