→ Пошук по сайту       Увійти / Зареєструватися
Знання Система керування базами даних MySQL

СКБД MySQL і доступ до БД в PHP

Компетенція Розробка БД на MySQL

Вступ

MySQL– вільна реляційна система керування базами даних (СКБД), яка працює як сервер для забезпечення багатокористувацького доступу до великої кількості баз даних.

Проекти на основі безкоштовного ПЗ, які вимагають повнофункціональної системи керування базами даних часто використовують MySQL. До таких проектів відносяться, наприклад, WordPress, phpBB, Drupal та інше програмне забезпечення, побудоване на стеку продуктів LAMP (Linux, Apache, MySQL, PHP/Perl/Python). MySQL також використовується в багатьох гучних великомасштабних Web-продуктах, включаючи Wikipedia, Google (для програми AdWords),  Facebook, YouTube, Flickr, Yahoo!, Digg, LiveJournal, Nokia тощо.

MySQL— компактний багато потоковий (багатонитевий) сервер баз даних. Характеризується великою швидкістю, стійкістю і простотою використання.

Для некомерційного використання MySQL є безкоштовним. Можливості сервера MySQL:

  • простота у встановленні та використанні;
  • підтримується необмежена кількість користувачів, що одночасно працюють із БД;
  • кількість рядків у таблицях може досягати 50 млн.;
  • висока швидкість виконання команд;
  • наявність простої і ефективної системи безпеки. 

1. Історія

mSQL (вона ж MiniSQL) – легка клієнт-серверна реляційна СУБД, що випускається компанією Hughes Technologies. Вперше випущена в 1994 році, вона заповнила вакуум утворився між вбудованими настільними СУБД типу Microsoft Accessі такими комерційними СУБД рівня підприємства як Oracleі DB2. З 1994 по 1997 рік її популярність росла і mSQL стала популярною серед open source розробників. При цьому початковий код самої mSQL не є відкритим.

З 1996 року розвиток mSQL загальмувалося, внаслідок чого її місце зайняла MySQL. У 1999 році MySQL обігнала mSQL за популярністю. Остання версія mSQL- 3.8 - була випущена 9 червня 2006. 

MySQL виникла як спроба застосувати mSQL до власних розробок компанії: таблицям, для яких використовувалися ISAM — підпрограми низького рівня. У результаті був вироблений новий SQL-інтерфейс, але API-інтерфейс залишився в спадок від mSQL.

В січні-лютому 2008 Sun Microsystems придбала виробника системи керування базами данних MySQL за $1 млрд. Після поглинання у 2009 році Sun Microsystems з боку Oracle Corporation MySQL стала власністю Oracle.

2. Функціональність MySQL

Станом на квітень 2009 року, MySQL пропонує версію MySQL 5.1 в двох різних варіантах: з відкритим вихідним кодом MySQL Community Server і комерційний Server Enterprise. Вони мають спільний програмний код і включають в себе серед іншого наступні можливості:

  • Крос-платформна підтримка
  • Збережувані процедури та функції
  • Тригери
  • Курсори
  • Оновлювані подання (представлення)
  • Інформаційна схема (так званий системний словник, що містить метадані).
  • Підтримка SSL
  • Кешування запитів
  • Вкладені запити SELECT
  • Підтримка реплікації
  • Повноцінна підтримка Юнікоду (UTF-8 і UCS2)
  • Сегментування таблиць
  • тощо.

3. Типи даних і індекси MySQL

Текстові типии даних

Для позначення рядкового типу даних використовується тип VARCHAR із зазначенням необхідної кількості символів (від 1 до 255).

Тип TEXT  служить для зазначення поля, що містить великий об’єм тексту.

Автоінкрементні поля

Поля цілочисленого типу: INT.

При зазначенні параметрів поля в стовпці «Додатково» можна вказати auto_increment.

Ключі та індекси

Кожна «нормальна» таблиця повинна мати ключове поле.

Існують такі типи індексів:

  • Ключове поле
  • індексоване
  • унікальне  
  • а також повнотекстове (для текстових полів)

4. phpMyAdmin – інструмент адміністрування через веб-інтерфейс

phpMyAdmin– веб-застосунок з відкритим кодом, написаний на мові PHP, представляє собою веб-інтерфейс для адміністрування СКБД MySQL. phpMyAdmin дозволяє через браузер здійснювати адміністрування сервера MySQL, запускати команди SQL  та переглядати вміст таблиць і баз даних. Система користується великою популярністю у веб-розробників, оскільки дозволяє керувати СКБД MySQL без безпосереднього вводу SQL команд, надаючи дружній інтерфейс. 

Програма розповсюджується під ліцензією GNU General Public License і тому інші розробники інтегрують його у свої проекти, у тому числі у XAMPP та Denwer.

Дамп БД

Експорт БД виконується за допомогою вкладки Експорт. Результатом буде дамп БД, який являє собою набір SQL (DDL) інструкцій.

Щоб перенести БД на інший сервер необхідно виконати SQL-запит, тілом якого буде дамп бази даних.

5. Моделювання предметної області за допомогою реляційної моделі даних

Більшість баз даних призначені для моделювання певної частини реального світу, яку називають предметної областю (ПрО). На логічному рівні об'єкти в ПрО є сутностями (entities) і пов'язані між собою через відношення (relationships). На фізичному рівні СКБД подає сутності у вигляді таблиць, а відношення – у вигляді обмежень зовнішнього ключа.

Основні типи відношень

Існує три типи відношень: один-до-одного, при якому кожний рядок таблиці пов'язаний з нулем або одним рядком іншої таблиці; один-до-багатьох, при якому кожний рядок таблиці пов'язаний з нулем, однією або декількома рядками іншої таблиці, і багато-до-багатьох, при якому кожний рядок першої таблиці пов'язаний з нулем, однією або декількома рядками другої таблиці, а кожен рядок другої таблиці може бути пов'язаний з нулем, однією або декількома рядками першої таблиці.

Відношення «один-до-одного»

Відношення один-до-одного зустрічаються досить рідко. Як правило, вони використовуються у разі, коли набір атрибутів застосовується до невеликої кількості примірників сутності. Наприклад, лише мала частина колективу співробітників компанії може бути учасниками команди з софтболу. Проектувальник бази даних може помістити всі атрибути софтбольного команди в окрему таблицю, а потім створити відношення один-до-одного між нею і таблицею співробітників.

Відношення «один-до-багатьох»

Відношення один-до-багатьох досить широко поширені. При побудові інтерфейсів це відношення також називають «головний-детальний» (master-detailed) (головний – «один», детальний – «багато»). 

Приклади

Місце роботи — Люди… (для списку жильців будинку, наприклад)

Викладач – студенти

Тема форуму – коментарі

Типові SQL-запити для відношення «один-до-багатьох»

Приклад таблиць:

Головний

 

groups

code (ПК)

groupName

Детальний

 

students

id (ПК)

name

surname

groupCode («зовнішній ключ», індексоване)

Зауваження

На практиці в MySQL не завжди застосовують інструкцію FOREIGN KEY для зовнішнього ключа, а лише передбачають відповідну роль такого поля на рівні логіки програми. У такому випадку для підвищення продуктивності SQL-запитів поле, що грає роль зовнішнього ключа повинно бути індексованим (INDEX).

Про зовнішні ключі в MySQL докладно:
http://dev.mysql.com/doc/refman/5.1/en/innodb-foreign-key-constraints.html

Отримати інформацію з детальної таблиці таким чином, щоб до кожного її запису також було додано відповідну інформацію з головної таблиці (тут же використовується групування GROUP BY). 

select * from groups, students where students.groupCode=groups.code 

Отримати усі записи детальної таблиці, які стосуються деякого запису головної таблиці.

select * from groups, students where groupCode=code and code=’ТВ-72’

Отримати докладну інформацію про запис детальної таблиці (додавши до нього інформацію з головної таблиці)

select * from groups, students where groupCode=code and id=’Петренко’

Відношення «багато-до-багатьох»

Для реалізації відношення багато-до-багатьох використовується таблиця-посередник (вузлова таблиця). Вузлова таблиця складається з первинних ключів таблиць з кожної зі сторін відносини. Відношення один-до-багатьох встановлюється між вузловою таблицею і кожною з оригінальних таблиць. Тож відношення  багато-до-багатьох реалізується з використанням вузлової таблиці.

Приклади

Користувачі – Ролі (права доступу)

Співробітники – Проекти

Замовлення – Товари

 

Популярні ситуації для Web-проектів:

  • Сторінки – Теги (мітки з «хмарки тегів»)
  • Користувачі – Групи (в соціальних мережах)

Типові SQL-запити для відношення «багато-до-багатьох»

Приклад таблиць:

groups

id

groupName

 

users

id

name

surname

groupUsers

id(ключ)

groupId (індекс)

userId (індекс)

Усі користувачі групи:

select * from users where id in (select userId from groupUsers where groupId=12)

Або без вкладеного запиту (як правило, працює швидше):

select users.* from users, groupUsers whereusers.id=groupUsers.userId  and groupUsers.groupId=12 

Усі групи користувача:

select * from groups where id in (select groupId from groupUsers where userId=55)

Або без вкладеного запиту (як правило, працює швидше):

select groups.* from groups, groupUsers wheregroup.id=groupUsers.groupId  and groupUsers.userId=55 
 

Зворотні відношення і моделювання ієрархій

Більшість відношень у базі даних встановлюють між двома різними таблицями. Проте буває, коли таблиця зв'язується сама з собою або через відношення один-до-одного, або через відношення один-до-багатьох. Подібні відношення називаються зворотними відношеннями.

  Зворотні відношеннянайчастіше застосовуються для реалізації ієрархій. Типовий приклад – ієрархія співробітників в організації. Співробітник може бути підпорядкований менеджеру, а менеджер, у свою чергу, може як співробітник мати вищестоящого менеджера.

Це відношення оформляється в межах однієї таблиці шляхом додавання поля, яке посилатиметься на ключове поле цієї ж таблиці, але на інший запис (читати речення 5 разів!).

Зворотні відношення можуть бути використані для реалізації ієрархії контенту сайту.

Типові SQL-запити для зворотних відношень

Приклад таблиці:

Pages (веб-сторінки)

id

parentId (у якості зовнішнього ключа, для посилання на id, індексоване)

title

content

Отримати дочірні сторінки:

SELECT * from pages where parentId=’articles’

Зауваження

Відповідним чином для отримання усіх нащадків деякого запису необхідно організувати рекурсивні запити на отримання дочірніх елементів.

6. Доступ до БД MySQL зо допомогою PHP

Етапи роботи з БД в PHP:

  1. З’єднання з БД
    • mysql_connect()– з’єднання з MySQL
    • mysql_select_db()– вибір БД
  2. Доступ до БД і отримання необхідних даних
    • mysql_query()– виконати SQL-запит
    • mysql_fetch_array(),mysql_fetch_assoc() – видобути наступний запис
  3. Звільнення пам’яті
    • mysql_free_result()– звільнення пам’яті, відведеної для зберігання  результату запита
  4. Закриття з’єднання
    • mysql_close()– закриття з’єднання;  виконується автоматично після завершення виконання скрипта.

Приклади

Доступ до полів по імені

<?php
$link = mysql_connect('localhost', 'mysql_user', 'mysql_password');
if (!$link) {
    die('Could not connect: ' . mysql_error());
}
mysql_select_db('my_database') or die('Could not select database');

$query = 'SELECT * FROM people';
$result = mysql_query($query);

if($result){
  while ($row = mysql_fetch_assoc($result)) {// або mysql_fetch_array($result)
      echo $row['firstname'].'<br />';
      echo $row['lastname'].'<br />';
      echo $row['address'].'<br />';
      echo $row['age'].'<br />';
      echo '<br /><br />';
  }
  mysql_free_result($result);
}

mysql_close($link);
?> 

Друк таблиці

<?php
// Connecting, selecting database
$link = mysql_connect('mysql_host', 'mysql_user', 'mysql_password')
    or die('Could not connect: ' . mysql_error());
echo 'Connected successfully';
mysql_select_db('my_database') or die('Could not select database');
// Performing SQL query
$query = 'SELECT * FROM my_table';
$result = mysql_query($query) or die('Query failed: ' . mysql_error());
// Printing results in HTML
echo "<table>\n";
while ($line = mysql_fetch_array($result, MYSQL_ASSOC)) {
    echo "\t<tr>\n";
    foreach ($line as $col_value) {
        echo "\t\t<td>$col_value</td>\n";
    }
    echo "\t</tr>\n";
}
echo "</table>\n";
// Free resultset
mysql_free_result($result);
// Closing connection
mysql_close($link);
?> 

Локалізація

Починаючи з версії 4.1 в СУБД MySQL впроваджена нова система кодувань і сортувань. При використанні кодування Windows-1251, перед виконанням SQL-інструкцій необхідно налаштувати кодування з'єднання за допомогою оператору:

SET  NAMES 'cp1251'

При використанні Юнікоду UTF-8цей оператор виглядає наступним чином:

SET NAME S 'utf8'

Кодування ISO 8859-5 не підтримується.

Налагодження PHP-MySQL програми (debug)

Дуже часто помилки в скриптах із доступом до БД виникають у зв’язку з некоректно сформованим SQL-виразом. Тому першим кроком у налагодженні PHP-програми, що виконує запити до MySQL повинна бути перевірка правильності SQL-інструкцій. Для цього слід вивести SQL-запит на сторінку, і, скопіювавши його, виконати безпосередньо в phpMyAdmin:

 $query = 'SELECT * FROM people where id='.$someValue;
print $query;
$result = mysql_query($query);

© Титенко С.В. З конспекту лекцій. НТУУ "КПІ". Кафедра АПЕПС.

загрузка...
Теми розділу
Сторінки, близькі за змістом