ВУЗ: Не указан
Категория: Не указан
Дисциплина: Не указана
Добавлен: 24.05.2024
Просмотров: 127
Скачиваний: 0
2 Ключі
Спершу давайте подумаємо над таким питанням: яку інформацію потрібно дати про людину, щоб співбесідник точно сказав, що це саме та людина, сумнівів бути не може, іншого такого нема? Повідомити прізвище, очевидно, недостатньо, оскільки існують однофамільці. Якщо співбесідник людина, то ми можемо приблизно пояснити, про кого мова, наприклад пригадати вчинок, який здійснила та людина, або ще якось. Комп'ютер же такого пояснення не зрозуміє, йому потрібні чіткі правила, як визначити, про кого йде мова. В системах управління базами даних для вирішення такої задачі ввели поняття первинного ключа.
Первинний ключ (primary key, PK) – мінімальний набір полів, унікально ідентифікуючий запис в таблиці. Значить, первинний ключ – це в першу чергу набір полів таблиці, по-друге, кожний набір значень цих полів повинен визначати єдиний запис (рядок) в таблиці і, по-третє, цей набір полів повинен бути мінімальним зі всіх володіючих такою ж властивістю. Оскільки первинний ключ визначає тільки один унікальний запис, то ніякі два записи таблиці не можуть мати однакових значень первинного ключа .
Наприклад, в нашій таблиці (див. вище) ПІБ і адресу дозволяють однозначно виділити запис про людину. Якщо ж говорити загалом, без зв'язку з вирішуваною задачею, то такі знання не дозволяють точно вказати на єдину людину, оскільки існують однофамільці, що живуть в різних містах за однією адресою. Вся справа в межах, які ми самі собі задаємо. Якщо вважаємо, що знання ПІБ, телефону і адреси без вказівки міста для наших цілей достатньо, то все чудово, тоді поля ПІБ і адреса можуть утворювати первинний ключ. У будь-якому випадку проблема створення первинного ключа лягає на плечі того, хто проектує базу даних (розробляє структуру зберігання даних). Рішенням цієї проблеми може стати або виділення характеристик, які природним чином визначають запис в таблиці (завдання так званого логічного, або природного, PK), або створення додаткового поля, призначеного саме для однозначної ідентифікації записів в таблиці (завдання так званого сурогатного, або штучного, PK). Прикладом логічного первинного ключа є номер паспорта в базі даних об паспортних даних жителів або ПІБ і адресу в телефонній книзі (таблиця вище). Для завдання сурогатного первинного ключа в нашу таблицю можна додати поле id (ідентифікатор), значенням якого буде ціле число, унікальне для кожного рядка таблиці. Використовування таких сурогатних ключів має сенс, якщо природний первинний ключ є великим набором полів або його виділення нетривіальне.
Окрім однозначної ідентифікації запису, первинні ключі використовуються для організації зв'язків з іншими таблицями.
Наприклад, у нас є три таблиці: що містить інформацію про історичних осіб (Persons), що містить інформацію про їх винаходи (Artifacts) і що містить зображення як осіб, так і артефактів (Images) (рис 10.1).
Первинним ключем у всіх цих таблицях є поле id (ідентифікатор). В таблиці Artifacts є поле author, в якому записаний ідентифікатор, привласнений автору винаходу в таблиці Persons. Кожне значення цього поля є зовнішнім ключем для первинного ключа таблиці Persons. Крім того, в таблицях Persons і Artifacts є поле photo, яке посилається на зображення в таблиці Images. Ці поля також є зовнішніми ключами для первинного ключа таблиці Images і встановлюють однозначний логічний зв'язок Persons-Images і Artifacts-Images. Тобто якщо значення зовнішнього ключа photo в таблиці особи рівне 10, то це значить, що фотографія цієї особи має id=10 в таблиці зображень. Таким чином, зовнішні ключі використовуються для організації зв'язків між таблицями бази даних (батьківськими і дочірніми) і для підтримки обмежень посилальної цілісності даних.
Мал. 10.1. Приклад використовування первинних ключів для організації зв'язків з іншими таблицями
120
3 Індексація
Одна з основних задач, що виникають при роботі з базами даних, – це задача пошуку. При цьому, оскільки інформації в базі даних, як правило, міститься багато, перед програмістами встає задача не просто пошуку, а ефективного пошуку, тобто пошуку за порівняно невеликий час і з достатньою точністю. Для цього (для оптимізації продуктивності запитів) проводять індексацію деяких полів таблиці. Використовувати індекси корисно для швидкого пошуку рядків з вказаним значенням одного стовпця. Без індексу читання таблиці здійснюється по всій таблиці, починаючи з першого запису, поки не будуть знайдені відповідні рядки. Чим більше таблиця, тим більше невигідні витрати. Якщо ж таблиця містить індекс по даних стовпцях, то база даних може швидко визначити позицію для пошуку в середині файлу даних без проглядання всіх даних. Це відбувається тому, що база даних поміщає проіндексовані поля ближче в пам'яті, так, щоб можна було пошвидше знайти їх значення. Для таблиці, що містить 1000 рядків, це буде як мінімум в 100 разів швидше в порівнянні з послідовним перебором всіх записів. Проте у разі, коли необхідний доступ майже до всіх 1000 рядків, буде швидше послідовне читання, оскільки при цьому не вимагається операцій пошуку по диску. Отже іноді індекси бувають тільки перешкодою. Наприклад, якщо копіюється великий об'єм даних в таблицю, то краще не мати ніяких індексів. Проте в деяких випадках вимагається задіювати відразу декілька індексів (наприклад, для обробки запитів до таблиць, що часто використовуються).
Якщо говорити про MySQL, то там існує три види індексів: PRIMARY, UNIQUE, і INDEX, а слово ключ (KEY) використовується як синонім слова індекс (INDEX). Всі індекси зберігаються в пам'яті у вигляді B-деревьев.
PRIMARY – унікальний індекс (ключ) з обмеженням, що всі індексовані їм поля не можуть мати порожнього значення (тобто вони мають позначку NOT NULL). Таблиця може мати тільки один первинний індекс, але він може складатися з декількох полів.
UNIQUE – ключ (індекс), задаючий поля, які можуть мати тільки унікальні значення.
INDEX – звичайний індекс (як ми описали вище). В MySqL, крім того, можна індексувати рядкові поля по заданому числу символів від початку рядка.
4 СУБД MySQL
MySQL – це реляційна система управління базами даних. Тобто дані в її базах зберігаються у вигляді логічно зв'язаних між собою таблиць, доступ до яких здійснюється за допомогою язика запитів SQL. MySQL – вільно поширювана система, тобто платити за її використання не потрібно. Крім того, це досить швидка, надійна і, головне, проста у використанні СУБД, цілком відповідна для не дуже глобальних проектів.
Працювати з MySQL можна не тільки в текстовому режимі, але і в графічному. Існує дуже популярний візуальний інтерфейс (до речі, написаний на PHP) для роботи з цією СУБД. Називається він PhpMyAdmin. Цей інтерфейс дозволяє значно спростити роботу з базами даних в MySQL .
В текстовому режимі робота з базою даних виглядає просто як введення команд в командний рядок (рис 10.2), а результати вибірок повертаються у вигляді своєрідних таблиць, поля в яких налізають один на одного, якщо дані не поміщаються на екран (рис 10.3).
Мал. 10.2. Робота з MySQL в командному рядку. Команда show databases — вивести всі наявні бази даних
PhpMyAdmin дозволяє користуватися всіма перевагами браузера, включаючи прокрутку зображення, якщо воно не уміщається на екран. Багато які з базових SQL-функцій роботи з даними в PhpMyAdmin зведені до інтуїтивно зрозумілих інтерфейсів і дій, що нагадують перехід по посиланнях в Internet. Але, проте, варто все ж таки попрацювати і в текстовому режимі.
121
Мал. 10.3. Робота з MySQL в командному рядку. Результат обробки команди show databases
Перш ніж переходити до детального вивчення язика SQL, декілька слів про установку MySQL і підготовку до роботи. Якщо ви не збираєтеся займатися адмініструванням серверу, то інформація, приведена нижче, стане в нагоді вам тільки для загального розвитку. Отже, встановлюється MySQL дуже просто – автоматично, пару раз натискуйте OK, і все. Після цього ви можете зайти в директорію, де лежать файли типу mysql.exe, mysqld.exe і т.п. (у нас під Windows XP це С:\mysql\bin ) Останній файл запускає Mysql-сервер. В деяких системах сервер запускається у вигляді сервісу. Після запуску серверу слід запустити mysql-клієнт, запустившися програму mysql.exe. Тут навіть пароля не запитають. Більш того, якщо ви наберете
shell> mysql.exe -u root
або
shell>mysql -u root mysql
то отримаєте всі права адміністратора mysql серверу. До речі, виконувати ці команди треба, знаходячись в тій директорії, де лежать файли mysql.exe.
Спершу, не вдаючись в подробиці команд, виправимо ці два недоліки (відсутність пароля у адміністратора і можливість входу анонімним користувачам):
shell> mysql -u root mysql
mysql> UPDATE user SET Password=PASSWORD('new_password') WHERE user='root';
mysql> DELETE FROM user WHERE user =''; mysql> FLUSH PRIVILEGES;
Всі дані про користувачів MySQL зберігає в таблиці user в спеціальній базі даних mysql, доступ до якої має тільки адміністратор серверу. Тому, щоб змінити який-небудь пароль, потрібно змінити цю таблицю. Пароль задається за допомогою функції PASSWORD, яка кодує введені дані. Окрім зміни пароля адміністратора, потрібно ще видалити всіх користувачів, що не мають логіна (команда DELETE ). Команда Flush Privileges примушує вступити в дію зміни, що відбулися в системній базі даних (mysql).
Тепер створимо базу даних, з якою працюватимемо (ми все ще працюємо як адміністратор серверу):
mysql>create database book;
Якомога помітити, всі команди в MySQL закінчуються крапкою з комою. Якщо ви забули поставити цей знак, то видається запрошення його поставити до тих пір, поки це не буде зроблено:
mysql> show tables -> ->
Тепер остання дія – створимо простого користувача, надамо йому доступ до створеної бази даних, і почнемо працювати.
122
mysql> GRANT ALL PRIVILEGES ON book.* TO vasia@localhost IDENTIFIED '123';
Команда GRANT наділює користувача vasia, що зайшов на сервер з цієї ж машини (з localhost) і що ідентифікується паролем "123", певними правами (в даному випадку всіма) на всі таблиці бази даних book. Тепер ми можемо вийти і зайти як користувач vasia з відповідним паролем:
shell>mysql -u vasia -p Enter password: ***
Welcome to MySQL monitor!...
mysql>
Якщо ви збираєтеся користуватися базою даних на чужому сервері, то його адміністратор виконає всі описані вище дії за вас, тобто все набудує і створить користувача і базу даних. В наступному розділі описані команди язика SQL, які стануть в нагоді для роботи з даними, що зберігаються в СУБД MySQL.
5 Язик SQL
Отже, ми у загальних рисах познайомилися з основними поняттями теорії баз даних, встановили і налаштували для роботи MySQL. Тепер самий час навчитися маніпулювати даними, що зберігаються в базах даних. Для цього нам знадобиться SQL – структурований язик запитів. Цей язик дає можливість створювати, редагувати і видаляти інформацію, що зберігається в базах даних, створювати нові бази даних і багато що інше. SQL є стандартом ANSI (Американський національний інститут стандартів) і ISO (Міжнародна організація по стандартизації).
Трохи історії
Перший міжнародний стандарт язика SQL був прийнятий в 1989 р., його часто називають SQL/89. Серед недоліків цього стандарту виділяють в першу чергу те, що багато важливих властивостей він встановлював як визначувані в реалізації. Звідси відбулася безліч розбіжностей в реалізаціях язика різними виробниками. Крім того, висловлювалися претензії з приводу відсутності в цьому стандарті згадок про практичні аспекти язика, таких як його вбудовування в язик програмування Сі.
Наступний міжнародний стандарт язика SQL був прийнятий в кінці 1992 р. І став називатися SQL/92. Він вийшов набагато більш точним і повним, ніж SQL/89, хоча і не був позбавлений недоліків. В даний час більшість систем майже повністю реалізує цей стандарт. Проте, як відомо, прогрес не зупиниш, і в 1999 році з'явився новий стандарт SQL:1999, також відомий як SQL3. SQL3 характеризується як "об'єктно-орієнтований SQL " і є основою декількох об'єктно-реляційних систем управління базами даних (наприклад, ORACLE8 компанії Oracle, Universal Server компанії Informix і DB2 Universal Database компанії IBM). Цей стандарт є не просто злиттям SQL-92 і об'єктної технології. Він містить ряд розширень традиційного SQL, а сам документ складений так, щоб добитися більш ефективної роботи в області стандартизації в майбутньому.
Якщо говорити про MySQL, то вона відповідає початковому рівню SQL92, містить декілька розширень цього стандарту і прагне повної підтримки стандарту ANSI SQL99, але без збитку для швидкості і якості коду.
Далі, кажучи про основи язика SQL, дотримуватимемося його реалізації в СУБД MySQL.
6 Основні оператори язика SQL
Функції будь-якої СУБД включають:
створення, видалення, зміна бази даних (БД);
додавання, зміна, видалення, призначення прав користувача;
внесення, видалення і зміна даних в БД (таблиць і записів);
вибірку даних з БД.
До перших двох функцій мають доступ тільки адміністратори СУБД або привілейовані користувачі. Розглянемо, як розв'язуються останні дві задачі (насправді це сім задач).
123
Перш ніж що-небудь робити з даними, потрібно створити таблиці, в яких ці дані зберігатимуться, навчитися змінювати структуру цих таблиць і видаляти їх, якщо буде потрібно. Для цього в язиці SQL існують оператори CREATE TABLE, ALTER TABLE і DROP TABLE .
6.1 Оператор CREATE TABLE
Оператор CREATE TABLE створює таблицю із заданим ім'ям в поточній базі даних . Правила для допустимих імен таблиці приведені в документації. Якщо немає активної поточної бази даних або вказана таблиця вже існує, то виникає помилка виконання команди.
Уверсії MySQL 3.22 і більш пізніх ім'я таблиці може бути вказано як ім’я_бази_даних.ім’я_таблиці. Ця форма запису працює незалежно від того, чи є вказана база даних поточною.
Уверсії MySQL 3.23 при створенні таблиці можна використовувати ключове слово TEMPORARY. Тимчасова таблиця автоматично віддаляється після закінчення з'єднання, а її ім'я дійсне тільки протягом даного з'єднання. Це означає, що в двох різних з'єднаннях можуть використовуватися тимчасові таблиці з однаковими іменами без конфлікту один з одним або з існуючою таблицею з тим же ім'ям (існуюча таблиця прихована, поки не видалена тимчасова таблиця).
Уверсії MySQL 4.0.2 для створення тимчасових таблиць необхідно мати привілеї CREATE TEMPORARY TABLES.
Уверсії MySQL 3.23 і більш пізніх можна використовувати ключові слова IF NOT EXISTS для того, щоб не виникала помилка, якщо вказана таблиця вже існує. Слід враховувати, що при цьому ідентичність структур цих таблиць не перевіряється.
Кожна таблиця представлена набором певних файлів в директорії бази даних.
Синтаксис
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] ім’я_таблиці [(визначення_колонки...)] [опції_таблиці] [select_вираз]
У виразі визначення_колонки перераховують, які стовпці повинні бути створені в таблиці. Кожний стовпець таблиці може бути порожнім (NULL), мати значення по замовчанню, бути ключовим або автоінкрементним. Крім того, для кожного стовпця обов'язково указується тип даних, які будуть в ньому зберігатися. Якщо не указується ні NULL, ні NOT NULL, то стовпець інтерпретується так, як ніби вказане NULL. Якщо поле позначають як автоінкрементне (AUTO_INCREMENT), то його значення автоматично збільшується на одиницю кожного разу, коли відбувається додавання даних в таблицю і в це поле записується порожнє значення (NULL, тобто нічого не записується) або 0. Автоінкремент в таблиці може бути тільки один, і при цьому він обов'язково повинен бути проіндексирований. Послідовність AUTO_INCREMENT починається з 1. Наявність автоінкременту є однією з особливостей MySQL. Формально опис стовпця (визначення_колонки) виглядає так:
ім’я_колонки тип [NOT NULL | NULL]
[DEFAULT значение_по_умолчанию] [AUTO_INCREMENT][PRIMARY KEY] [reference_definition]
Тип стовпця (тип у виразі визначення_колонки) може бути одним з наступних:
цілий: INT[(length)] [UNSIGNED] [ZEROFILL]
дійсний: REAL[(length,decimals)] [UNSIGNED] [ZEROFILL]
символьний: CHAR(length) [BINARY] і VARCHAR(length) [BINARY]
дата і час: DATE і TIME
для роботи з великими об'єктами: BLOB
текстовий: TEXT
множина: ENUM(value1,value2,value3...) і SET(value1,value2,value3...)
Повний список типів дивитеся в документації MySQL.
124
