Система керування базами даних MySQL, Проектування інформаційних ресурсів Інтернет, ЗНТУ

2. Система керування базами даних MySQL

2.1. Загальні положення

MySQL – невеликий, компактний багатопоточний сервер баз даних. MySQL характеризується великою швидкістю, стійкістю й легкістю у використанні. MySQL є ідеальним рішенням для малих і середніх сайтів. Вихідні тексти сервера компілюються на різних платформах. Найбільш повно можливості сервера проявляються на Unix-Серверах, де є підтримка багатопоточності, що дає значний приріст продуктивності.

Мова серверних скприптів PHP має засоби взаємодії з MySQL. Важливим фактором є безкоштовність MySQL. Система розповсюджується на умовах загальної ліцензії GNU (GPL, GNU Public License). MySQL відноситься до категорії реляційних БД.

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

У реляційних БД дані зібрані в таблиці, які у свою чергу складаються зі стовпців і рядків, на перетині яких розташовані відповідні значення. Запит до таких баз даних повертає таблицю, що повторно може брати участь у наступному запиті. Дані в одних таблицях, як правило, пов'язані з даними інших таблиць, звідки й походить назва "реляційні".

Короткий перелік можливостей MySQL.

1. Підтримується необмежена кількість користувачів, що одночасно працюють із базою даних.

2. Кількість рядків у таблицях може досягати 50 млн.

3. Швидке виконання команд. MySQL - один з найшвидших серверів.

4. Проста й ефективна система безпеки.

5. Підтримується структурована мова запитів (SQL).

2.2. Установка MySQL 

Якщо ви скористалися пакетом DENWER, то встановлювати додатково MySQL буде не потрібно, оскільки DENWER уже містить у своєму складі MySQL і всі необхідні компоненти та настройки, тому даний розділ можна пропустити.

Для самостійної установки необхідно, насамперед, завантажити дистрибутив MySQL із сайту http://www.mysql.com або http://www.mysql.ru. Сервер MySQL можна встановлювати як на UNIX платформу, так і під Windows. Розглянемо процес установки під Windows.

Завантажений архів дистрибутива потрібно розархівувати і запустити файл setup.exe. Запускається Майстер установки. У вікні “Setup Type” (тип установки) вибираємо “Custom” і натискаємо “Next”. У наступному вікні вибираємо каталог установки . На наступному етапі пропонується створити новий обліковий запис на сайті mysql.com, натискаємо Skip (пропустити). Після закінчення установки пропонується настроїти MySQL сервер, відзначаємо чекбокс “Configure the MySQL” і натискаємо “Finish”. Запускається майстер конфігурації. У вікні, що з'явилося, відзначаємо “Standard Configuration”. На наступному етапі встановлюємо прапорець “Instal As Windows Servise”, якщо хочемо, щоб сервер запускався автоматично при завантаженні Windows. У наступному вікні натискаємо кнопку “Execute”. Конфігурація закінчена, і всі настройки внесені у файл MY.INI.

У папці C:\MYSQL\BIN\ розташовуються утиліти, для роботи із БД. Утиліта MYSQLD.EXE призначена для запуску сервера – це перше, що потрібно зробити для роботи з MySQL.

Для запуску інших утиліт перейдемо в режим командного рядка. Для цього натискаємо «Пуск», потім «Виконати» і у вікні, що з'явилося, уводимо команду “cmd”, потім ОК. Уведемо “cd\mysql\bin” у командному рядку вікна, що з'явилося, і натискаємо ENTER. Тепер можна запускати утиліти в командному рядку.

Утиліта mysqlshow дозволяє переглянути список баз даних на сервері. Після установки MySQL на сервері буде дві БД – mysql і test.

Для перегляду переліку таблиць, наприклад, у БД test, набираємо в командному рядку mysqlshow test.

Утиліта mysq.exe призначена для запуску консолі (командного рядка MySQL). Запускаємо її й уводимо команду help. Буде виведений список доступних команд. Кожна команда повинна закінчуватися символом крапки з комою (;). Для виходу з режиму консолі треба набрати команду exit.

Утиліта mysqladmin.exe надає адміністративні функції сервера. Повний список команд можна переглянути, запустивши утиліту без параметрів. Ось деякі з них:

• create ім'я БД - створити базу даних;

• drop ім'я БД - видалити базу даних;

• reload - запустити знову сервер;

• shutdown - зупинити сервер;

• status - інформація про стан сервера.

Утиліта mysqldump.exe призначена для резервного копіювання бази даних або її таблиці. Наприклад, якщо набрати в командному рядку mysqldump test > dump.txt, одержимо в каталозі C:\MYSQL\BIN\ файл dump.txt, що містить структуру й дані БД test. Цей файл тепер можна перенести на інший комп’ютер для створення на ньому такої ж БД. Для цього треба набрати команди

mysqladmin create ім'я БД

mysql ім'я БД < ім'я файлу із БД.

Створювати й редагувати бази даних можна також у режимі консолі. Наприклад, щоб створити базу даних library, треба в командному рядку набрати

mysql > create database library;

Інші команди по управлінню БД будуть розглянуті в наступних розділах.

Існують спеціальні програми, що надають зручний інтерфейс для створення та керування БД. Найбільш відома з них phpMyAdmin буде розглянута нижче. Пакет DENWER містить у своєму складі phpMyAdmin. 

2.3. Поля і їхні типи в MySQL 

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

Тип поля може бути:

• числовим цілим;

• числовим дійсним;

• рядковим;

• бінарним;

• дата й час;

• переліком і множиною. 

Цілочисельні типи даних 

Таблиця 2.1

Дійсні числа

Числові дійсні типи записуються у вигляді:

ТИП (ДОВЖИНА, ЗНАКИ) [UNSIGNED]

Тут і надалі у квадратних дужках необов'язкові параметри. ДОВЖИНА - це кількість позицій, у яких буде розташоване число, а ЗНАКИ – це кількість знаків після десяткової крапки, які будуть ураховуватися. Якщо присутній модифікатор UNSIGNED, знак числа враховуватися не буде.

 Таблиця 2.2

Рядкові типи

Будь-який рядок – це масив символів. У більшості випадків застосовується тип VARCHAR або просто CHAR, що дозволяє зберігати рядки, що містять до 255 символів. У дужках після типу вказується довжина рядка:

VARCHAR(48);

CHAR(73);

Якщо 255 символів для вашого завдання недостатньо, можна використовувати інші типи, наприклад, TEXT. 

 Таблиця 2.3

Бінарні типи даних

Бінарні типи даних також можна використовувати для зберігання тексту. У такому випадку при пошуку буде враховуватися регістр символів. До того ж, будь-який текстовий тип можна перетворити в бінарний, указавши модифікатор BINARY:

VARCHAR(30) BINARY; 

 Таблиця 2.4

Дата та час 

Таблиця 2.5

2.4. Оператори та команди MySQL 

Структурована мова запитів SQL дозволяє робити різні операції з базами даних: створювати таблиці, додавати, оновлювати й видаляти з них дані, створювати запити і т.ін. Надалі ми послідовно розглянемо всі ці оператори.

Примітка: Команди SQL не чутливі до регістра, але традиційно вони набираються малими буквами. 

Створення таблиць. Оператор CREATE

Створити таблицю через SQL-Запит дозволяє оператор CREATE. Його синтаксис: 

CREATE TABLE Ім'я_таблиці(Ім'я_поля1 Тип Модифікатор,

Ім'я_поляN Тип Модифікатор

[первинний ключ]

[зовнішній ключ]) 

Як модифікатори можна використовувати такі значення:

NOT NULL – поле не може містити невизначеного значення (NULL), тобто поле повинне бути явно ініціалізоване;

PRIMARY KEY – поле буде первинним ключем (ідентифікатором запису), за яким можна однозначно ідентифікувати запис;

AUTO_INCREMENT – при додаванні нового запису значення цього поля буде автоматично збільшено на одиницю, тому в таблиці не буде двох записів з однаковим значенням цього поля;

DEFAULT – задає значення, що буде використане за замовчуванням, якщо при додаванні запису поле не буде ініціалізоване явно.

Створимо для прикладу таблицю " clients ": 

CREATE TABLE clients (nk INT NOT NULL,
 fio VARCHAr(40) NOT NULL,
 addr VARCHAR(30) NOT NULL,
 city VARCHAR(15) NOT NULL,
 phone VARCHAR(11),
 gr INT NOT NULL); 

Таблиця clients містить поля nk (номер клієнта), fio (Прізвище, Ім'я, По батькові), addr (Адреса), city (Місто), phone (Телефон) і gr (Рік народження). Усі ці поля не можуть містити порожнього значення (NOT NULL). 

Додавання даних у таблицю. Оператор INSERT

Для додавання записів використовується оператор INSERT: 

INSERT INTO Ім'я_таблиці [(Список полів)]

VALUES (Список значень); 

Приклад: 

INSERT INTO clients VALUES (4, 'Лосєв Л.Л.', 'Нова 1', 'Запоріжжя', '1234321',1980); 

Якщо заповнюються всі поля, то їхні назви можна не вказувати. 

Оновлення записів. Оператор UPDATE

Синтаксис оператора UPDATE, що використовується для оновлення записів, виглядає так: 

UPDATE Ім'я_таблиці

SET Поле1 = Значення1, ... , ПолеN = ЗначенняN

[WHERE Умова]; 

Приклад: 

UPDATE clients SET city = 'Київ' WHERE nk = 1; 

Цей запит слід розуміти так: знайти запис, для якого поле nk = 1, і встановити в ньому значення city ="Київ". 

Видалення записів. Оператор DELETE

Якщо нам необхідно видалити всіх клієнтів, номера яких перевищують 3, створюємо такий запит: 

DELETE FROM clients WHERE nk> 3; 

За допомогою оператора DELETE можна видалити всі записи таблиці, вказавши умову, що підійде для всіх записів, наприклад: 

DELETE FROM clients; 

Відбір записів. Оператор SELECT

Оператор SELECT дозволяє вибирати інформацію із БД відповідно до визначених критеріїв. Синтаксис оператора: 

SELECT [DISTINCT|ALL] {*| [поле1 AS псевдонім] [,..., полеN AS псевдонім]}

FROM Ім'я_таблиці1 [,..., Ім'я_таблиціN]

[WHERE умова]

[GROUP BY список полів] [HAVING умова]

[ORDER BY список полів] 

На наступних прикладах розглянемо використання оператора SELECT для відбору записів з таблиці clients. 

SELECT * FROM clients; 

За цією командою вибираються всі записи з таблиці clients. У результаті одержимо таку відповідь сервера: 

Таблиця 2.6

Припустимо, потрібно вивести тільки прізвище й номер телефона клієнта, тоді використовуємо такий запит: 

SELECT fio, phone FROM clients; 

Якщо потрібно вивести прізвища й адреси клієнтів, які народилися після 1980 року, скористаємося таким запитом: 

SELECT fio, addr FROM clients WHERE GR > 1980; 

Ви можете використовувати такі оператори відношень: <, >, =, <>, <=, >= і логічні операції AND, OR, XOR, NOT.

Вибір клієнтів, які народилися в 1980 і 1985 роках: 

SELECT fio FROM clients WHERE gr IN( 1980,1985); 

Вибір клієнтів, рік народження яких не 1981 і не 1995: 

SELECT fio FROM clients WHERE gr NOT IN( 1981,1995); 

Вибір клієнтів, які проживають у Києві або Донецьку: 

SELECT fio FROM clients WHERE addr=”Київ” OR addr= “Донецьк” ; 

Відбір клієнтів, які народилися між 1979 і 1993 роком: 

SELECT fio FROM WHERE gr BETWEEN 1979 AND 1993;

Вибір клієнтів, які проживають у Запоріжжі й народилися до 1970 року або після 1990: 

SELECT fio FROM clients WHERE addr=”Запоріжжя” AND gr NOT BETWEEN 1970 AND 1990; 

Вибираємо з таблиці clients три записи, починаючи із другого: 

SELECT * FROM clients LIMIT 2,3; 

При формуванні умови відбору можна використовувати шаблони, при цьому знак “_” означає один будь-який символ, а “%” - будь-яку кількість будь-яких символів. Наприклад, наступний запит відбирає клієнтів, прізвища яких починаються на букву “К”, а закінчуються на “о”: 

SELECT FIO FROM clients WHERE fio LIKE “K%о ____”; 

Більш складні шаблони можна побудувати за допомогою регулярних виразів (REGEXP) (див. довідкову літературу). 

Сортування записів

Для впорядкування виведеної інформації по зростанню або по спаданню використовують команду ORDER. За замовчуванням впорядкування виконується по зростанню (ASC). Для сортування по спаданню додаємо DESC. Наприклад: 

SELECT fio, addr FROM clients ORDER BY fio DESC 

Внутрішні функції MIN, MAX, AVG, SUM

При роботі з оператором SELECT доступні досить корисні внутрішні функції MySQL, що обчислюють кількість елементів (COUNT), суму елементів (SUM), максимальне й мінімальне значення (MAX і MIN), а також середнє значення (AVG).

Наступні оператори виведуть, відповідно, кількість записів у таблиці clients та наймолодшого клієнта: 

SELECT COUNT(*) FROM clients;

SELECT MAX(gr) FROM clients; 

Групування записів

Створимо ще одну таблицюorders, у якій зберігається інформація про замовлення: nz – номер замовлення, date – дата замовлення, nk – номер клієнта, nt – номер товару, quantity- кількість одиниць замовленого товару, amount – вартість замовлення. 

CREATE TABLE orders
(nz INT NOT NULL,
date DATE NOT NULL,
nk INT NOT NULL,
nt INT NOT NULL,
quantity DOUBLE(9,2) NOT NULL,
amount DOUBLE(9,2) NOT NULL); 

Якщо в різних таблицях є поля з однаковими іменами, то в багатотабличних запитах перед ім'ям поля потрібно вказувати ім'я таблиці, наприклад, cliеnts.nk, orders.nk.

Оператор SELECT дозволяє групувати значення в запитах. Наприклад, клієнт Козлов (nk=1) кілька разів замовляв якийсь товар. Виходить, його номер зустрічається в таблиці orders кілька разів. Інший клієнт також міг зробити кілька замовлень. Ми можемо згрупувати всі записи по полю nk (номер клієнта), а потім вивести суму замовлення кожного клієнта. Псевдонім стовпця для загальної суми замовлення – totalsum. 

SELECT clients.fio, SUM(orders.amount) AS totalsum
 FROM clients, orders
 WHERE clients.nk = orders. nk
 GROUP BY orders.nk; 

Групування виконує оператор GROUP BY, що є частиною оператора SELECT. Оператор GROUP BY можна обмежити за допомогою HAVING. HAVING можна вважати аналогом WHERE, але тільки для GROUP BY: 

HAVING <умова> 

Наприклад, нас цікавлять тільки клієнти, які замовили товарів на загальну суму, що перевищує 1500: 

SELECT clients.fio, SUM(ORDERS.AMOUNT) AS totalsum
FROM clients, orders
WHERE clients.nk = orders.nk
GROUP BY orders.nk
HAVING totalsum > 1500; 

Ключі

Припустимо, що хтось додав у таблицю clients запис: 

2 Волков В.В. Волі 7 Донецьк 0112233 

У той же час, до цього номер 2 був закріплений за Барановим. Номер клієнта повинен бути унікальним. Щоб уникнути такої плутанини, необхідно оголосити поле nk як первинний ключ: 

ALTER TABLE clients ADD PRIMARY KEY (nk); 

Як первинний ключ не можна використовувати поле, що допускає значення NULL. Створити первинний ключ можна й простіше - при побудові таблиці в такий спосіб: 

CREATE TABLE clients
 (nk int NOT NULL,
 fio varchar(50) NOT NULL,
 addr varchar(55) NOT NULL,
 city varchar(20) NOT NULL,
 phone varchar(8) NOT NULL,
 gr INT NOT NULL,
 PRIMARY KEY (nk)); 

Таблиця orders містить відомості про замовлення. По полю nk у цій таблиці ідентифікується замовник. Припустимо, що в таблицю orders хтось увів значення, якого немає в таблиці clients. Щоб не допустити подібної ситуації, варто використовувати запит для створення зовнішнього ключа: 

ALTER TABLE orders ADD FOREIGN KEY(nk) REFERENCES clients; 

Такий зв'язок називається декларативною цілісністю бази даних. Команда ALTER використовується не тільки для додавання ключів, але й для реорганізації таблиці в цілому. Наприклад, можна додати в таблицю нове поле email: 

ALTER TABLE clients ADD email VARCHAR(20) NULL; 

Зверніть увагу, що ви не можете додати нове поле зі значенням NOT NULL у таблицю, у якій уже є дані.

За допомогою ALTER можна ввести список обмежень на значення, що вводяться. Наприклад, якщо компанія працює тільки із клієнтами Києва, Донецька і Запоріжжя, то доцільно ввести список припустимих значень для таблиці clients: 

ALTER TABLE clients
ADD CONSTRAINT INVALID_STATE SHECK (city IN ('Київ', 'Донецьк', 'Запоріжжя' )); 

Видалення полів і таблиць. Оператор DROP

Видалити поле можна командою ALTER. Наприклад, видалити поле email з таблиці clients можна командою: 

ALTER TABLE clients DROP email;

А видалити таблицю ще простіше:

DROP clients; 

Відключення від СУБД

Використовуючи запит DISCONNECT, можна відключитися від використовуваної бази даних, а потім, використовуючи запит CONNECT, підключитися до іншої бази даних. У деяких серверах SQL запит DISCONNECT не працює, а замість CONNECT застосовується запит USE.

При використанні PHP для відключення від сервера MySQL існує функція _close(), а для підключення до сервера MySQL використовується функція mysql_connect(). Ці функції будуть докладніше розглянуті в наступних розділах.

З повагою ІЦ "KURSOVIKS"!