Мал. 3.1. Клієнт-серверна архітектура MYSQL

Інша перевага багатопотокової обробки властива всім багатопотоковим застосуванням. Не дивлячись на те, що потоки спільно використовують пам'ять процесу, вони виконуються окремо. Завдяки цьому розділенню виконання потоків на багатопроцесорних машинах може бути розподілене по декількох ЦП. На мал. 3-1 показана ця багатопотокова природа сервера MYSQL.

Окрім виграшу в продуктивності, отриманого завдяки багатопоточності, MYSQL підтримує велику підмножину мови запитів SQL. MYSQL підтримує більше десятка типів даних, а також функція SQL. Ваше застосування може дістати доступ до цих функцій через команди ANSI SQL. MYSQL фактично розширює ANSI SQL декількома новими можливостями. У їх числі нові функції (ENCRYPT, WEEKDAY, IF та інші), можливість инкрементування полів (AUTO_INCREMENT і LAST_INSERT ID), а також можливість розрізняти верхній і нижній регістри.

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

З 1996 року ТСХ використовує MYSQL в середовищі, де є більше 40 баз даних, що містять 10 000 таблиць. З цих 10 000 більше 500 таблиць мають, у свою чергу, більше 7 мільйонів записів - близько 100 Гбайт даних.

Система безпеки MYSQL

Вам не лише потрібно мати надійний доступ до своїх даних, але і бути упевненим, що в інших немає жодного доступу до них. MYSQL використовує власний сервер баз даних для забезпечення безпеки. При первинній установці MYSQL створюється база даних під назвою «mysql». У цій базі є п'ять таблиць: db, host, user, tables_priv, і columns_priv. Новіші версії MYSQL створюють також базу даних з назвою func, але вона не має відношення до безпеки. MYSQL використовує ці таблиці для визначення того, кому що дозволено робити. Таблиця user містить дані по безпеці, що відносяться до сервера в цілому. Таблиця host містить права доступу до сервера для віддалених комп'ютерів. І нарешті, db, tables_priv і соlumns_priv управляють доступом до окремих баз даних, таблиць і колонок.

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

Таблиця user. Таблиця user має вигляд, показаний в Таблиці 3.1:

Таблиця 3.1. Таблиця user

У колонках Host і User можна використовувати символ «%», замінюючий довільну послідовність символів. Наприклад, ім'я вузла «chem%lab» включає «chembiolab», «chemtestlab» і так далі Спеціальне ім'я користувача «nobody» діє як поодинокий «%», тобто охоплює всіх користувачів, не згаданих де-небудь у іншому місці. Нижче роз'яснюється сенс різних прав доступу:

Select_priv - Можливість виконувати команди SELECT.

Insert_priv - Можливість виконувати команди INSERT.

Update_priv - Можливість виконувати команди UPDATE.

Delete_priv - Можливість виконувати команди DELETE.

Create_priv - Можливість виконувати команди CREATE або створювати бази даних.

Drop_priv - Можливість виконувати команди DROP для видалення баз даних.

Reload_priv - Можливість оновлювати інформацію про доступ за допомогою mysqladmin reload.

Shutdown_priv - Можливість зупиняти сервер через mysqladmin shutdown.

Process_priv - Можливість управляти процесами сервера.

File_priv - Можливість читати і записувати файли за допомогою команд типа SELECT INTO OUTFILE і LOAD DATA INFILE.

Grant_priv - Можливість давати привілеї іншим користувачам.

Index_priv - Можливість створювати і знищувати індекси.

Alter_priv - Можливість виконувати команду ALTER TABLE.

У MYSQL є спеціальна функція, що дозволяє приховати паролі від цікавих очей. Функція password() зашифровує пароль. Нижче показано, як використовувати функцію password() в процесі додавання користувачів в систему.

INSERT INTO user (Host, User, Password, Select_priv, Insert_priv, Update_priv, Delete_priv)

VALUES ('%', 'bob', password('mypass'), 'Y', 'Y', 'Y', 'Y')

INSERT INTO user (Host, User, Password, Select_priv)

VALUES ('athens.imaginary.com', 'jane', '', 'Y')

INSERT INTO user(Host, User, Password)

VALUES ('%', 'nobody', '')

INSERT INTO user (Host, User, Password, Select_priv, Insert_priv, Update_priv, Delete_priv)

VALUES ('athens.imaginary.com', 'nobody', password('thispass'), 'Y', 'Y', 'Y', 'Y')

Імена користувачів MYSQL зазвичай не пов'язані з іменами користувачів операційної системи. За замовчуванням клієнтські засоби MYSQL використовують при реєстрації імена користувачів операційної системи, проте, обов'язкової відповідності не вимагається. У більшості клієнтських застосувань MYSQL можна за допомогою параметра -u підключитися до MYSQL, використовуючи будь-яке ім'я. Ваше ім'я користувача операційної системи не з'явиться в таблиці user, якщо не буде спеціально включено в неї з привласненням прав.

Перший створений нами користувач, «bob», може підключатися до бази даних з будь-якого комп'ютера і виконувати команди SELECT, INSERT, UPDATE і DELETE. Другий користувач, «jane», може підключатися з «athens.imaginary.com», не має пароля і може виконувати лише SELECT. Третій користувач - «nobody» - з будь-якої машини. Цей користувач взагалі нічого не може робити. Останній користувач - «nobody» - з машини «athens.imaginary.com», він може виконувати SELECT, INSERT, UPDATE і DELETE, як і користувач «bob».

Як MYSQL проводить зіставлення? Деяке ім'я може відповідати декільком записам. Наприклад, «nobody @athens.imaginary.com» відповідає і «nobody@%», і «nobody@athens.imaginary.com». Перш ніж здійснювати пошук в таблиці user, MYSQL сортує дані таким чином:

1. Спочатку шукається відповідність для вузлів, що не містять масок «%», при цьому порожнє поле Host трактується як «%».

2. Для одного і того ж вузла спочатку перевіряється відповідність імен, що не містять масок. Порожнє поле User трактується як «%», що містить.

3. Перша знайдена відповідність вважається остаточною.

У попередньому прикладі користувач спочатку порівнюватиметься з «nobody» з «athens.imaginary.com», оскільки «athens.imaginary.com» в порядку сортування стоїть вище «%». Оскільки імена комп'ютерів сортуються раніше імен користувачів, значення привілеїв для комп'ютера, з якого ви підключаєтеся, мають пріоритет перед будь-якими конкретними правами, які у вас можуть бути. Якщо таблиця user містить записи:

Якщо jane підключається з «athens.imaginary.com», то MYSQL використовуватиме привілеї, дані «athens.imaginary.com».

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

Таблиця 3.2. Таблиця db

Ця таблиця багато в чому схожа на таблицю user. Основна відмінність в тому, що замість колонки Password є колонка Db. Таблиця управляє правами користувачів відносно певних баз даних. Оскільки привілеї, вказані в таблиці user, відносяться до всього сервера в цілому, права, привласнені користувачеві в таблиці user, перекривають права, привласнені тому ж користувачеві в таблиці. Наприклад, якщо користувачеві в таблиці user дозволяють доступ типа INSERT, це право діє відносно всіх баз даних, незалежно від того, що вказане в таблиці db.

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

Ті ж правила, які діють відносно колонок User і Host в таблиці user, діють і в таблиці db, але з деякою особливістю. Порожнє поле Host вимушує MYSQL знайти запис, відповідний імені вузла користувача, в таблиці host. Якщо такому запису не знайдено, MYSQL відмовляє в доступі. Якщо відповідність знайдена, MYSQL визначає права як перехрещення прав, що визначаються таблицями host і db. Іншими словами, в обох записах дозвіл повинен мати значення «Y», інакше в доступі відмовляється.

Таблиця host. Таблиця host служить особливій меті. Її структура показана в таблиці 3.3:

Таблиця 3.3. Таблиця Host

Таблиця host дозволяє задати основні дозволи на міжкомп'ютерному рівні. При перевірці прав доступу MYSQL шукає в таблиці db відповідність імені користувача і його машині. Якщо він знаходить запис, відповідний імені користувача, поле host якій порожній, MYSQL звертається до таблиці host і використовує перехрещення обох прав для визначення остаточного права доступу. Якщо у вас є група серверів, які ви вважаєте менш захищеними, то ви можете заборонити для них всі права запису. Якщо «bob» заходить з однією з таких машин, і його запис в таблиці db містить порожнє поле host, йому буде заборонена операція запису, навіть якщо вона дозволена йому згідно таблиці db.

Таблиці tables_priv і columns_priv.
Ці дві таблиці, по суті, уточнюють дані, наявні в таблиці db. Саме, право на всяку операцію спочатку перевіряється по таблиці db, потім по таблиці tables_priv, потім по таблиці columns_priv. Операція вирішується, якщо одна з них дає дозвіл. За допомогою цих таблиць можна звузити зону дії дозволів до рівня таблиць і колонок. Управляти цими таблицями можна через команди SQL GRANT і REVOKE.

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

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

Якщо з'єднання встановлене, MYSQL переходить до етапу верифікації запиту. При цьому зроблені вами запити зіставляються з вашими правами. Ці права MYSQL перевіряє по таблицях user, db, host, tables_pnv і columns_priv. Як тільки знайдена відповідність в таблиці user з позитивним дозволом, команда негайно виконується. Інакше MYSQL продовжує пошук в наступних таблицях у вказаному порядку:

1. db

2. tables_priv

3. columns_priv

Якщо таблиця db містить дозвіл, подальша перевірка припиняється і виконується команда. Якщо немає, то MYSQL шукає відповідність в таблиці tables_priv . Якщо це команда SELECT, об'єднуюча дві таблиці, то користувач повинен мати дозвіл для обох цих таблиць. Якщо хоч би один із записів відмовляє в доступі або відсутній, MYSQL таким самим способом перевіряє всі колонки в таблиці columns_priv.

Зміна прав доступу.MYSQL завантажує таблиці доступу при запуску сервера. Перевагою такого підходу в порівнянні з динамічним зверненням до таблиць є швидкість. Негативна сторона полягає в тому, що зміни, зроблені в таблицях доступу MYSQL, не відразу починають діяти. Для того, щоб сервер побачив ці зміни, необхідно виконати команду mysqladmin reload. Якщо таблиці змінюються за допомогою SQL-команд GRANT або REVOKE, явно перенавантажувати таблиці не потрібно.

Утиліти MYSQL

ТСХ поширює MYSQL з великим набором допоміжних утиліт, проте набір утиліт, пропонованих сторонніми розробниками, ще багатше.

Утиліти командного рядка (Command Line Tools):

Isamchk - Проводить перевірку файлів, що містять дані бази. Ці файли називаються ISAM-файлами (ISAM — метод індексованого послідовного доступу). Ця утиліта може усунути велику частину пошкоджень ISAM-файлов.

Isamlog - Читає створювані журнали MYSQL, що відносяться до ISAM-файлам. Ці журнали можна використовувати для відтворення таблиць або відтворення змін, внесених до таблиць протягом деякого проміжку часу.

mysql - Створює пряме підключення до сервера баз даних і дозволяє вводити запити безпосередньо із запрошення MYSQL.

mysqlaccess - Модифікує таблиці прав доступу MYSQL і відображує їх в зручному для читання вигляді. Використання цієї утиліти - хороший спосіб вивчення структури таблиць доступу MYSQL.

Mysqladmin - Здійснює адміністративні функції. За допомогою цієї утиліти можна додавати і видаляти цілі бази даних, а також завершувати роботу сервера.

Mysqlbug - Складає для ТСХ звіт про неполадку, що виникла в MYSQL. Звіт буде також посланий в поштовий список розсилки MYSQL, і армія добровольців MYSQL досліджуватиме проблему.

Mysqldump - Записує весь вміст таблиці, включаючи її структуру, у файл у вигляді SQL-команд, якими можна відтворити таблицю. Вихідні дані цієї утиліти можна використовувати для відтворення таблиці в іншій базі або на іншому сервері. Синтаксис її вживання: mysqldump -u user -p dbname --tab=path, де path - шлях для збереження файлів.

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

Mysqlshow - Виводить на екран структуру баз даних, що є на сервері, і таблиці, з яких вони складаються.

Утиліти сторонніх розробників.Жоден постачальник або розробник не може самостійно надати всі необхідні для програмного продукту засоби під­тримки. За найсвіжішим списком зверніться на домашню сторінку MYSQL: http://www.mysql.com/Contrib.

Утиліти перетворення баз даних:

access_to_mysql - Перетворить бази даних Microsoft Access в таблиці MYSQL. Включається в Access у вигляді функції, що дозволяє зберігати таблиці у форматі, що дозволяє експортувати їх в MYSQL.

dbf2mysql - Конвертує файли dBASE (DBF) в таблиці MYSQL. Хоча dBASE втратив популярність, формат DBF встановився як найбільш поширений для передачі даних між різними застосуваннями баз даних. Всі головні настільні застосування баз даних можуть читати і писати DBF-файлы. Це застосування корисне для экспорта/импорта даних в комерційні настільні бази даних.

Exportsql/Importsql - Конвертує бази даних Microsoft Access в MYSQL і назад. Ці утиліти є функціями Access, які можна використовувати для експорту таблиць Access у форматі, придатному для читання MYSQL. З їх допомогою можна також перетворювати SQL-вихід MYSQL у вигляд, придатний для читання Access.

Інтерфейси CGI:

PHP - Створює HTML-страницы з використанням спеціальних тегів, розпізнаваних аналізатором РНР. РНР має інтерфейси до більшості основних баз даних, включаючи MYSQL і mSQL.

Mysql-webadmin - Здійснює веб-сервер-адміністрування баз даних MYSQL. Використовуючи цей засіб, можна переглядати таблиці і змінювати їх вміст за допомогою HTML-форм.

Mysqladm - Здійснює веб-сервер-адміністрування баз даних MYSQL. Ця CGI-програма дозволяє переглядати таблиці через WWW, добав­лять таблиці і змінювати їх вміст.

www-sql - Створює HTML-страницы з таблиць баз даних MYSQL. Ця програма здійснює розбір HTML-страниц у пошуках спеціальних тегів і використовує дані, що витягують, для виконання команд SQL на сервері MYSQL.

Клієнтські застосування:

Mysqlwinadmn - Дозволяє адмініструвати MYSQL з Windows. За допомогою цього засобу можна виконувати функції mysqladmin з графічного інтерфейсу.

Xmysql - Забезпечує повний доступ до таблиць баз даних MYSQL для клієнта X Window System. Підтримує групові вставки і видалення.

Xmysqladmin - Дозволяє здійснювати адміністрування MYSQL з X Window System. Це інструмент для графічного інтерфейсу, що дозволяє створювати і видаляти бази даних і управляти таблицями. З його допомогою можна також перевіряти, чи запущений сервер, перенавантажувати таблиці доступу і управляти потоками.

Інтерфейси програмування:

MYODBC - Реалізує ODBC API до MYSQL в Windows.

mm.mysql.jdbc - Реалізує стандартний API JDBC (Java Database Connectivity -доступ до баз даних з Java).

TwzJdbcForMysql - Реалізація JDBC API для Java.

Мова SQL.

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

SQL є різновидом «природної мови». Іншими словами, команда SQL повинна читатися, принаймні на перший погляд, як Програма англійською мовою. В такого підходу є як переваги, так і недоліки, але факт полягає в тому, що ця мова дуже несхожа на традиційні мови програмування, такі як З, Java або Perl. Тут ми розглянемо мову SQL, як він реалізований в MYSQL.

Основи SQL.SQL «структурований» в тому відношенні, що він слідує певному набору правил. Комп'ютерній програмі легко розібрати на частини сформульований запит SQL. Дійсно, в книзі видавництва O'Reilly «lex & уасс», написаною Дж. Лівайном, Т. Мейсоном и Д. Брауном (John Levine, Tony Mason, Doug Brown), реалізована граматики SQL для демонстрації процесу створення програми, що інтерпретує мову! Запит (query) - це повністю задана команда, що посилається серверу баз даних, який виконує запитану дію. Нижче наведений приклад SQL-запиту:

SELECT name FROM people WHERE name LIKE Stac%'

Як можна бачити, ця Програма виглядає майже як фраза на ламаній англійській мові: «Вибрати імена, список людей, де імена схожі на Stac». SQL в дуже незначній мірі використовує форматування і спеціальні символи, що зазвичай асоціюються з комп'ютерними мовами. Порівняєте, наприклад, «$++;($*++/$!);$&$",,;$!» у Perl і «SELECT value FROM table» в SQL.

Історія SQL.У IBM винайшли SQL на початку 1970-х, незабаром після введення д-ром Е. Ф. Коддом (Е. F. Codd) поняття реляційної бази даних. Із самого початку SQL був легким у вивченні, але потужною мовою. Він нагадує природну мову, таку як англійська, і тому не стомлює тих, хто не є технічним фахівцем.

SQL дійсно був настільки популярний серед користувачів, для яких призначався, що в 1980-х компанія Oracle випустила першу в світі загальнодоступну комерційну SQL-систему. Oracle SQL був хітом сезону і породив довкола SQL цілу індустрію. Sybase, Informix, Microsoft і ряд інших компаній вийшли на ринок з власними розробками реляційних систем управління базами даних (РСУБД), заснованих на SQL.

У той час коли Oracle і її конкуренти вийшли на сцену, SQL був новинкою, і для нього не існувало стандартів. Лише у 1989 році комісія із стандартів ANSI випустила перший загальнодоступний стандарт SQL. Сьогодні його називають SQL89. До нещастя, цей новий стандарт не дуже заглиблювався у визначення технічної структури мови. Тому, хоча різні комерційні реалізації мови SQL зближувалися, відмінності в синтаксисі робили завдання переходу з однієї реалізації мови на іншу нетривіальним. Лише у 1992 році стандарт ANSI SQL вступив в свої права.

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

SQL2 - не останнє слово в стандартах SQL. У зв'язку із зростанням популярності об'єктно-орієнтованих СУБД (ООСУБД) і об'єктно-реляційних СУБД (ОРСУБД) зростає тиск з метою прийняття об'єктно-орієнтованого доступу до баз даних як стандарт SQL. Відповіддю на цю проблему повинен послужити SQL3. Він ще не є офіційним стандартом, але в даний час сповна визначився і може стати офіційним стандартом.

З появою MYSQL з’вився новий підхід до розробки серверів баз даних. Замість створення черговий гігантською РСУБД з ризиком не запропонувати нічого нового порівняно з «великими братами», були запропоновані невеликі і швидкі реалізації найбільш часто використовуваних функцій SQL.

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

Більшість реалізацій SQL, включаючи MYSQL, нечутливі до регістра: неважливо, в якому регістрі ви вводите ключові слова SQL, якщо орфографія вірна. Наприклад, CREATE з верхнього прикладу можна записати і так:

cREatE ТАblЕ people (name cHaR(10))

Нечутливість до регістра відноситься лише до ключових слів SQL. У MYSQL імена баз даних, таблиць і колонок до регістра чутливі. Але це характерно не для всіх СУБД. Тому, якщо ви пишете застосування, яке повинне працювати з будь-якими СУБД, не слід використовувати імена, що розрізняються одним лише регістром.

Перший елемент SQL-запиту- завжди дієслово. Дієслово виражає дію, яка повинна виконати ядро бази даних. Хоча остання частина команди залежить від дієслова, вона завжди слідує загальному формату: вказується ім'я об'єкту, над яким здійснюється дія, а потім описуються використовувані при дії дані. Наприклад, в запиті CREATE TABLE people (char(10)) використовується дієслово CREATE, за яким слідує доповнення (об'єкт) TABLE. Частина запиту, що залишилася, описує таблицю, яку потрібно створити.

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

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

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

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

Загальний синтаксис для створення таблиць наступний:

CREATE TABLE table_name (column_name1 type [modifiers]

[, column_name2 type [modifiers]] )

Які ідентифікатори - імена таблиць і колонок - є допустимими, залежить від конкретної СУБД. У MYSQL довжина ідентифікатора може бути до 64 символів, допустимий символ «$», і першим символом може бути цифра. Важливіше, проте, що MYSQL допускає використання будь-яких символів зі встановленого в системі локального набору. Для хорошої переносимості SQL уникайте імен, що починаються не з допустимої букви.

Колонка - це окрема одиниця даних в таблиці. У таблиці може міститися довільне число колонок, але використання великих таблиць буває неефективним. Створивши правильно нормалізовані таблиці, можна об'єднувати їх («join») для здійснення пошуку в даних, розміщених в декількох таблицях. Механіку об'єднання таблиць ми обговоримо пізніше.

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

DROP TABLE table_name

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

У MYSQL можна однією командою видалити декілька таблиць, розділяючи їх імена комами. Наприклад, DROP TABLE people, animals, plants видалить ці три таблиці. Можна також використовувати модифікатор IF EXISTS для придушення помилки в разі відсутності таблиці, що видаляється. Цей модифікатор корисний у великих сценаріях, призначених для створення бази даних і всіх її таблиць. Перш ніж створювати таблицю, виконаєте команду DROP TABLE table_name IF EXISTS.

Типи даних в SQL.Кожна колонка таблиці має типи. Типи даних SQL схожі з типами даних традиційних мов програмування. Тоді як в багатьох мовах визначений самий мінімум типів, необхідних для роботи, в SQL для зручності користувачів визначені додаткові типи, такі як MONEY і DATE. Дані типа MONEY можна було б зберігати і як один з основних числових типів даних, проте використання типу, що спеціально враховує особливості грошових розрахунків, підвищує легкість використання SQL.

Таблиця 3.4. Найбільш споживані типи даних, підтримувані MYSQL

MYSQL підтримує атрибут UNSIGNED для всіх числових типів. Цей модифікатор дозволяє вводити в колонку лише позитивні (беззнакові) числа. Беззнакові поля мають верхню межу значень удвічі більший, ніж у відповідних знакових типів. Беззнаковий TINYINT - однобайтовий числовий тип MYSQL - має діапазон від 0 до 255, а не від -127 до 127, як в свого знакового аналога.

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

Числові типи даних.Перш ніж створювати таблицю, ви повинні добре уявити собі, якого роду дані ви в ній зберігатимете. Окрім очевидного рішення про те, будуть це числові або символьні дані, слід з'ясувати зразковий розмір даних, що зберігаються. Якщо це числове поле, то яким виявиться максимальне значення? Чи може воно змінитися в майбутньому? Якщо мінімальне значення завжди позитивне, слід розглянути використання беззнакового типа. Завжди слід вибирати найменший числовий тип, здатного зберігати найбільше гадане значення. Якби потрібно було зберігати в полі чисельність населення штату, слід було б вибрати беззнаковий INT. У штаті не може бути негативній чисельності населення, а аби беззнакове поле типу INT не могло вміщати число, населення, що представляє його, чисельність населення штату повинна приблизно дорівнювати чисельності населення всієї Землі.

Символьні типи.З символьними типами працювати трохи важче. Ви повинні подумати не лише про максимальну і мінімальну довжину рядка, але також про середній розмір, частоту відхилення від нього і необхідності в індексуванні. У даному контексті ми називаємо індексом поле або групу полів, в яких ви збираєтеся здійснювати пошук, — в основному, в вирази WHERE. Індексування, проте, значно складніше, ніж таке спрощене визначення, і ми займемося ним далі. Тут важливо лише відзначити, що індексування по символьних полях відбувається значно швидше, якщо вони мають фіксовану довжину. Якщо довжина рядків не дуже вагається або, що ще краще, постійна, то, ймовірно, краще вибрати для поля типа CHAR. Хороший кандидат для типу CHAR - код країни. Стандартом ISO визначені два символьні коди для всіх країн. CHAR(2) буде правильним вибором для даного поля.

Аби лічити для типу CHAR, поле необов'язково має бути фіксованої довжини, але довжина не повинна сильно мінятися. Телефонні номери, наприклад, можна сміливо зберігати в полі CHAR(13), хоча довжина номерів різна в різних країнах. Просто відмінність не настільки велика, тому немає сенсу робити поле для номера телефону змінним по довжині. Відносно поля типу CHAR важливо пам'ятати, що, незалежно від реальної довжини рядка, що зберігається, в полі буде рівно стільки символів, скільки вказано в його розмірі - не більше і не менше. Різниця в довжині між розміром тексту, що зберігається, і розміром поля заповнюється пропусками. Не варто турбуватися з приводу декількох зайвих символів при зберіганні телефонних номерів, але не хотілося б витрачати багато місця в деяких інших випадках. Для цього існують текстові поля змінної довжини.

Хороший приклад поля, для якого потрібний тип даних із змінною довжиною, дає URL Інтернет. Здебільшого адреси Web займають порівняно небагато місця - http://www.ora.com, http://www.hughes.com.au, http://www.mysql.com - і не представляють проблеми. Проте інколи можна наткнутися на адреси подібного вигляду:

http://www.winespectator.com/Wine/Spectator/notes\5527293926834323221480431354?XvlI=&Xr5=&Xvl =&type-region-search-code=&Xa14=flora+springs&Xv4=.

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

Поля змінної довжини.Перевагою текстових полів змінної довжини є те, що вони використовують рівно стільки місця, скільки необхідно для зберігання окремої величини. Наприклад, поле типа VARCHAR(255), в якому зберігається рядок «hello, world», займає лише дванадцять байтів (по одному байту на кожен символ плюс ще один байт для зберігання довжини). На відміну від стандарту ANSI, в MYSQL поля типа VARCHAR не доповнюються пропусками. Перед записом з рядка віддаляються зайві пропуски.

Зберегти рядки, довжина яких більша, ніж заданий розмір поля, не можна. У полі VARCHAR(4) можна зберегти рядок не довше за 4 символи. Якщо ви спробуєте зберегти рядок «Happy birthday», MYSQL скоротить її до «happ». Недоліком підходу MYSQL до зберігання полів змінної довжини є те, що не існує способу зберегти незвичайний рядок, довжина якого перевищує задане вами значення. У таблиці 3.5 показаний розмір простору, необхідного для зберігання 144-символьного URL, продемонстрованого вище, і звичайного, 30-символьного URL.

Таблиця 3.5. Простір пам'яті, необхідний для різних символьних типів MYSQL

Якщо через роки роботи зі своєю базою даних ви виявите, що світ змінився, і поле, що затишно відчувало себе в типові VARCHAR(25), повинне тепер вміщати рядки завдовжки 30 символів, не все втрачено. У MYSQL є команда ALTER TABLE, що дозволяє перевизначити розмір поля без втрати даних.

ALTER TABLE mytable MODIFY mycolumn LONGTEXT

Двійкові типи даних.У MYSQL є цілий ряд двійкових типів даних, відповідних своїм символьним аналогам. Двійковими типами, підтримуваними MYSQL, є CHAR BINARY, VARCHAR BINARY, TINYBLOB, BLOB, MEDIUMBLOB і LONGBLOB. Практична відмінність між символьними типами і їх двійковими аналогами заснована на принципі кодування. Двійкові дані просто є шматком даних, які MYSQL не намагається інтерпретувати. Навпаки, символьні дані передбачаються такими, що представляють текстові дані з використовуваних людиною алфавітів. Тому вони кодуються і сортуються, грунтуючись на правилах, відповідних даному набору символів. Двійкові ж дані MYSQL сортує в порядку ASCII без врахування регістра.

Перерахування і безліч.MYSQL надає ще двох особливих типів даних. Тип ENUM дозволяє при створенні таблиці вказати список можливих значень деякого поля. Наприклад, якби у вас була колонка з ім'ям «фрукт», в яку ви дозволили б поміщати лише значення «яблуко», «апельсин», «ківі» і «банан», їй слід було б привласнити типа ENUM:

CREATE TABLE meal(meal_id INT NOT NULL PRIMARY KEY

фрукт ENUM('яблуко', 'апельсин', 'ківі', 'банан'))

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

Тип MYSQL SET дозволяє одночасно зберігати в полі декілька значень.

Інші типи даних.Будь-які гадані дані можна зберігати за допомогою числових або символьних типів. В принципі, навіть числа можна зберігати в символьному вигляді. Проте те, що це можна зробити, не означає, що це потрібно робити. Розглянемо, наприклад, як зберігати в базі даних грошові суми. Можна робити це, використовуючи INT або REAL. Хоча інтуїтивно REAL може здатися більш відповідним - врешті-решт, в грошових сумах потрібні десяткові знаки, - насправді правильніше використовувати INT. У полях, що містять значення з плаваючою комою, таких як REAL, часто неможливо знайти число з точним десятковим значенням. Наприклад, якщо ви вводите число 0.43, яке повинне представляти суму $0.43, MYSQL може записати його як 0.42999998. Це невелика відмінність можевикликати проблеми при здійсненні великого числа математичних операцій.

MYSQL надає спеціальні типи даних для таких грошових сум. Одним з них є тип MONEY, іншим - DATE.

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

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

CREATE INDEX index_name ON tablename (column1, column2, columnN)

MYSQL дозволяє також створювати індекс одночасно із створенням таблиці, використовуючи наступний синтаксис:

CREATE TABLE materials (id INT NOT NULL

name CHAR(50) NOT NULL

resistance INT

melting_pt REAL

INDEX index1 (id, name)

UNIQUE INDEX index2 (name))

В даному прикладі для таблиці створюється два індекси. Перший індекс index1 складається з полів id і name. Другий індекс включає лише поле name і вказує, що значення поля name мають бути унікальними. Якщо ви спробуєте вставити в поле name значення, яке вже є в цьому полі в якому-небудь рядку, операція не буде здійснена. Всі поля, вказані в унікальному індексі, мають бути оголошені як NOT NULL .

Хоча ми створили окремий індекс для поля name, окремо для поля id ми не створювали ин­декса. Якщо такий індекс нам знадобиться, створювати його не потрібно - він вже є. Коли ин­декс містить більш за одну колонку (наприклад, name, rank, і serial_number), MYSQL читає колонки в порядку зліва направо. Завдяки використовуваній MYSQL структурі індексу всяка підмножина колонок з лівого краю автоматично стає індексом усередині «головного» індексу. Наприклад, коли ви створюєте індекс name, rank, serial_number, створюються також «вільні» індекси name і name разом з rank. Проте індекси rank або name і serial_number не створюються, якщо не зажадати цього явно.

MYSQL підтримує також семантикові ANSI SQL для особливого індексу, званого первинним ключем. У MYSQL первинний ключ - це унікальний індекс з ім'ям PRIMARY. Призначивши при створенні таблиці колонку первинним ключем, ви робите її унікальним індексом, який підтримуватиме об'єднання таблиць. У наступному прикладі створюється таблиця cities з первинним ключем id.

CREATE TABLE cities (id INT NOT NULL PRIMARY KEY

name VARCHAR(100)

pop MEDIUMINT

founded DATE)

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

Послідовності і автоінкрментування.Краще всього, коли первинний ключ не має в таблиці жодного іншого значення, окрім значення первинного ключа. Для достиже­ния цього кращим способом є створення числового первинного ключа, значення кото­рого збільшується при додаванні в таблицю нового рядка. Якщо повернутися наприклад з таблицею cities, то перше введене вами місто повинне мати id, рівний 1, другий, - 2, тре­тий - 3, і так далі Аби успішно управляти такою послідовністю первинних ключів, потрібно мати якийсь засіб, що гарантує, що в даний конкретний момент лише один клієнт може прочитати число і збільшити його на одиницю. У базі даних з транзакция­ми можна створити таблицю, скажімо, з ім'ям sequence, що містить число, що представляє черговий id. Коли необхідно додати новий рядок в таблицю, ви читаєте число з цієї таблиці і вставляєте число на одиницю більше. Аби ця схема працювала, потрібно бути упевненим, що ніхто інший не зможе провести читання з таблиці, поки ви не ввели но­ве число. Інакше два клієнти можуть прочитати одне і те ж значення і спробують використовувати його як значення первинного ключа в одній і тій же таблиці.

MYSQL не підтримує транзакції, тому описаний механізм не можна використовувати для генерації унікальних чисел. Використовувати для цих цілей команду MYSQL LOCK TABLE обтяжливо. Проте СУБД надає свій варіант поняття послідовності, що дозволяє генерувати унікальні ідентифікатори, не турбуючись про транзакції.

Послідовності.При створенні таблиці в MYSQL можна одну з колонок специфікувати як AUTO_INCREMENT. В цьому випадку, при додаванні нового рядка, що має значення NULL або 0 в даній колонці, автоматично відбуватиметься заміна на значення на одиницю більше, ніж найбільше поточне значення в колонці. Колонка з модифікатором AUTO_INCREMENT має бути індексована. Нижче наведений приклад використання поля типа AUTO_INCREMENT:

CREATE TABLE cities (id INT NOT NULL PRIMARY KEY AUTO_INCREMENT

name VARCHAR(100)

pop MEDIUMINT

founded DATE)

Коли ви перший раз додаєте рядок, поле id набуває значення 1, якщо в команді INSERT для нього використовується значення NULL або 0. Наприклад, наступна команда використовує можливість AUTO_INCREMENT:

INSERT INTO cities (id, name, pop)

VALUES (NULL, 'Houston', 3000000)

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

Іншим способом реалізації послідовностей є використання значення, повертаного функцією LAST_INSERT_ID:

UPDATE table SET id=LAST_INSERT_ID (id+1);

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

Додавання даних.Додавання даних в таблицю є одній з найбільш простих операцій SQL. Декілька прикладів цього ви вже бачили. MYSQL підтримує стандартний синтаксис INSERT:

INSERT INTO table_name (column1, column2 ..., columnN)

VALUES (value1, value2 ..., valueN)

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

INSERT INTO addresses (name, address, city, state, phone, age)

VALUES('Irving Forbush', '123 Mockingbird Lane', 'Corbin', 'KY' '(800) 555-1234', 26)

Крім того, символ, що керує, - за замовчуванням '\' - дозволяє вводити в літерали одиночні лапки і сам символ '\':

# Ввести дані в каталог Stacie's Directory, який знаходиться

# у c:\Personal\Stacie

INSERT INTO files (description, location)

VALUES ('Stacie\'s Directory', 'C:\\Personal\\Stacie')

MYSQL дозволяє опустити назви колонок, якщо значення задаються для всіх колонок і в тому порядку, в якому вони були вказані при створенні таблиці командою CREATE. Проте якщо ви хочете використовувати значення за замовчуванням, потрібно задати імена тих колонок, в які ви вводите значення, відмінні від встановлених за замовчуванням. Якщо для колонки не встановлено значення за замовчуванням, і вона визначена як NOT NULL, необхідно включити цю колонку в команду INSERT із значенням, відмінним від NULL. MYSQL дозволяє вказати значення за замовчуванням при створенні таблиці в команді CREATE.

Нові версії MYSQL підтримують INSERT для одночасної вставки декількох рядків:

INSERT INTO foods VALUES (NULL, 'Oranges', 133, 0, 2, 39),

(NULL, 'Bananas', 122, 0, 4, 29),

(NULL, 'Liver', 232, 3, 15. 10)

Хоча підтримуваний MYSQL нестандартний синтаксис зручно використовувати для швидкого виконання завдань адміністрування, не слід без крайньої потреби користуватися їм при написанні застосувань. Як правило, слід дотримуватися стандарту ANSI SQL2 настільки близько, наскільки MYSQL це дозволяє. Завдяки цьому ви дістаєте можливість перейти в майбутньому на яку-небудь іншу базу даних. Переносимість особливо важлива для тих, у кого потреби середнього масштабу, оскільки такі користувачі передбачають коли-небудь перейти на повномасштабну базу даних.

MYSQL підтримує синтаксис SQL2, що дозволяє вводити в таблицю результати запиту:

INSERT INTO foods (name, fat)

SELECT food_name, fat_grams FROM recipes

Зверніть увагу, що число колонок в INSERT відповідає числу колонок в SELECT. Крім того, типи даних колонок в INSERT повинні збігатися з типами даних у відповідних колонках SELECT. І, нарешті, Програма SELECT усередині команди INSERT не повинна містити модифікатора ORDER BY і не може проводити вибірку з тієї ж таблиці, в яку вставляються дані командою INSERT.

Зміна даних.Якщо ваша база не є базою даних «лише для читання», вам, ймовірно, знадобиться періодично змінювати дані. Стандартна команда SQL для зміни даних виглядає так:

UPDATE table_name

SET column1=value1, column2=value2 ..., columnN=valueN

[WHERE clause]

MYSQL дозволяє обчислювати привласнюване значення. Можна навіть обчислювати значення, використовуючи значення іншої колонки:

UPDATE years

SET end_year = begin_year+5

У цій команді значення колонки end_year встановлюється рівним значенню колонки begin_year плюс 5 для кожного рядка таблиці.

Програма WHERE.Можливо, ви вже звернули увагу на пропозицію WHERE. У SQL Програма WHERE дозволяє відібрати рядки таблиці із заданим значенням у вказаній колонці, наприклад:

UPDATE bands

SET lead_singer = 'Ian Anderson'

WHERE band_name = 'Jethro Tull'

Ця команда - UPDATE - вказує, що потрібно змінити значення в колонці lead_singer для тих рядків, в яких band_name збігається з «Jethro Tull». Якщо дана колонка не є унікальним індексом, Програма WHERE може відповідати декільком рядкам. Багато команд SQL використовують пропозицію WHERE, аби відібрати рядки, над якими потрібно зробити операції. Оскільки по колонках, що беруть участь у виразі WHERE, здійснюється пошук, слід мати індекси по тих їх комбінаціям, які зазвичай використовуються.

Видалення.Для видалення даних ви просто вказуєте таблицю, з якої потрібно видалити рядки, і в вирази WHERE задаючи рядки, які хочете видалити:

DELETE FROM table_name [WHERE clause]

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

Запити.Сама часто використовувана команда SQL - та, яка дозволяє переглядати дані в базі: SELECT. Введення і зміна даних прово­дяться лише від випадку до випадку, і більшість баз даних в основному зайнята тим, що надає дані для читання. Загальний вигляд команди SELECT наступний:

SELECT column1, column2 ..., columnN

FROM table1, table2 ..., tableN

[WHERE clause]

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

У першій частині команди SELECT перераховуються колонки, які ви хочете витягувати. Можна задати «*», аби вказати, що ви хочете витягувати всі колонки. У вирази FROM вказуються таблиці, в яких знаходяться ці колонки. Програма WHERE вказує, які саме рядки повинні використовуватися, і дозволяє визначити, яким чином повинні об'єднуватися дві таблиці.

Об'єднання.Об'єднання вносять «реляційність» до реляційних баз даних. Саме об'єднання дозволяє зіставити рядку однієї таблиці рядок інший. Основним виглядом об'єднання є те, що інколи називають внутрішнім об'єднанням. Об'єднання таблиць полягає в прирівнюванні колонок двох таблиць:

SELECT book, title, author. name

FROM author, book

WHERE book.author = author.id

Розглянемо базу даних, в якій таблиця book має вигляд, як в таблиці 3.6.

Таблиця 3.6. Таблиця книг

А таблиця авторів author має вигляд таблиці 3.7.

Таблиця 3.7. Таблиця авторів

В результаті внутрішнього об'єднання створюється таблиця, в якій об'єднуються поля обох таблиць для рядків, що задовольняють запиту в обох таблицях. У нашому прикладі запит вказує, що поле author в таблиці book повинне збігатися з полем id таблиці author. Результат виконання цього запиту представлений в таблиці 3.8.

Таблиця 3.8. Результати запиту з внутрішнім об'єднанням

У цих результатах немає автора з ім'ям Neil Gaiman, оскільки його author.id не знайдений в таблиці book.author. Внутрішнє об'єднання містить лише ті рядки, які точно відповідають запиту. Нижче ми обговоримо поняття зовнішнього об'єднання, яке виявляється корисним у разі, коли до бази даних внесений письменник, в якого немає в цій базі книг.

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

# Псевдонім колонки

SELECT long_field_names_are_annoying AS myfield

FROM table_name

WHERE myfield = 'Joe'

# Псевдонім таблиці в MYSQL

SELECT people.names, tests.score

FROM tests really_long_people_table_name AS people

Угрупування і впорядкування.За замовчуванням порядок, в якому з'являються результати вибірки, не визначений. На щастя, SQL надає деякі засоби наведення ладу в цій випадковій послідовності. Перший засіб - впорядкування. Ви можете зажадати від бази даних, аби результати, що виводяться, були впорядковані по деякій колонці. Наприклад, якщо ви вкажете, що запит повинен упорядкувати результати по полю last_name, то результати будуть виведені в алфавітному порядку за значенням поля last_name. Впорядкування здійснюється за допомогою вирази ORDER BY:

SELECT last_name, first_name, age

FROM people

ORDER BY last_name, first_name

В даному випадку впорядкування проводиться по двох колонках. Можна проводити впорядкування по будь-якому числу колонок, але всі вони мають бути вказані в вирази SELECT. Якби в попередньому прикладі ми не вибрали поле last_name, то не змогли б упорядкувати по ньому.

Угрупування - це засіб ANSI SQL, реалізований в MYSQL. Як і передбачає назва, угрупування дозволяє об'єднувати в одну рядки з аналогічними значеннями з метою їх спільної обробки. Зазвичай це робиться для застосування до результатів агрегатних функцій. Про функції ми поговоримо декілька пізніше.

Розглянемо приклад:

mysql> SELECT name, rank, salary FROM people\g

5 rows in set (0.01 sec)

Після угрупування по званню (rank) видача змінюється:

mysql> SELECT rank FROM people GROUP BY rank\g

3 rows in set (0.01 sec)

Після вживання угрупування можна, нарешті, знайти середню зарплату (salary) для кожного звання. Про функції, використовувані в даному прикладі, ми поговоримо пізніше.

mysql> SELECT rank, AVG(salary) AS income FROM people GROUP BY rank\g

3 rows in set (0 04 sec)

Впорядкування і угрупування у поєднанні з використанням функцій SQL дозволяє произ­водить великий об'єм обробки даних на сервері до їх витягання. Але цією потужністю потрібно користуватися з обережністю. Хоча може здатися, що перенесення максимального об'єму обробки на сервер бази даних дає виграш в продуктивності, насправді це не так. Ваше застосування-клієнт обслуговує потреби окремого клієнта, тоді як сервер використовується багатьма клієнтами. Із-за великого об'єму роботи на серве­ре, майже завжди ефективніше покласти на сервер мінімально можливе навантаження. MYSQL, можливо, найбільш швидка з наявних баз даних, але не потрібно використовувати цю швидкість для тієї роботи, до якої краще пристосовано клієнтське застосування.

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

Функції в MYSQL.MYSQL надає можливість роботи з функціями. Функції в SQL аналогічні функціям в інших мовах програмування, таких як З і Perl. Функція може приймати аргументи і повертає деяке значення. У MYSQL в команді SELECT функції можуть використовуватися в двох місцях:

Як витягувана величина

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

# Функція FROM_UnixTIME()

# перетворить стандартне значення часу Unix в читаний вигляд.

SELECT name, FROM_UnixTIME(date)

FROM events

# Функція LENGTH() повертає довжину заданого рядка в символах.

SELECT title, text, LENGTH(text)

FROM papers

WHERE author = 'Stacie Sheldon'

Як частина виразу WHERE

У цьому вигляді функція замінює місце константи при обчисленні в вирази WHERE. Значення функції використовується при порівнянні в кожному рядку таблиці. Наведемо приклад.

# Функція RAND() генерує випадкове число

# між 0 і 1 (умножається на 34, аби зробити його

# і 34, і збільшується на 1, аби зробити його між 1 і

# 35) Функція ROUND() повертає дане число закругленим

# до найближчого цілого, що наводить до цілого числа

# між 1 і 35 яке повинне відповідати одному з чисел ID

SELECT name

FROM entries

WHERE id = ROUND((RAND()*34) + 1 )

# Можна використовувати функції в списку значень і вирази WHERE

# Функція UNIX_TIMESTAMP()без аргументів повертає поточний час # у форматі Unix.

SELECT name, FROM_UnixTIME(date)

FROM events

WHERE time > (Unix_TIMESTAMP() - (60 * 60 * 24))

# Функція ENCRYPT() # повертає зашифровану в стилі пароля Unix

# заданий рядок, використовуючи 2-символьний ключ.

# Функція LEFT() повертає n лівих символів переданого рядка.

SELECT name

FROM people

WHERE password = ENCRYPT(name, LEFT(name, 2))

Деякі з функцій можуть повернути значення як число або як рядок, залежно від того, який формат необхідний користувачеві. Ця можливість називається «контекстом» функції. Коли вибрані значення виводяться на дисплей, використовується лише текстовою контекст, але при введенні вибраних даних в поля таблиць або при використанні їх як аргументи інших функцій контекст залежить від того, що очікується одержувачем даних. Зокрема, коли дані вибрані для їх подальшого введення в поля числового типа, контекст функції буде числовим.

Крім того, є агрегатні функції, що виконуються над набором даних. Зазвичай цей метод використовується для виконання деякої дії над всім набором повертаних даних. Наприклад, функція SELECT AVG(height) FROM kids повертала б середнє від значень поля height в таблиці kids.

Таблиця 3.9. Агрегатні функції MYSQL