Отже, ми у загальних рисах познайомилися з основними поняттями теорії баз даних, встановили і налаштували для роботи 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.
Функції будь-якої СУБД включають:
створення, видалення, зміна бази даних (БД);
додавання, зміна, видалення, призначення прав користувача;
внесення, видалення і зміна даних в БД (таблиць і записів);
вибірку даних з БД.
До перших двох функцій мають доступ тільки адміністратори СУБД або привілейовані користувачі. Розглянемо, як розв'язуються останні дві задачі (насправді це сім задач).
Перш ніж що-небудь робити з даними, потрібно створити таблиці, в яких ці дані зберігатимуться, навчитися змінювати структуру цих таблиць і видаляти їх, якщо буде потрібно. Для цього в язиці SQL існують оператори CREATE TABLE, ALTER TABLE і DROP 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.
Замість переліку стовпців і їх властивостей в визначенні_колонки можна задавати списки ключових і індексних полів, обмеження і перевірки:
PRIMARY KEY (ім’я_індексованої_колонки ...)
або
KEY [ім’я_індекса] (ім’я_індексованої_колонки...)
або
INDEX [ім’я_індекса] (ім’я_індексованої_колонки...)
або
UNIQUE [INDEX] [ім’я_індекса] (ім’я_індексованої_колонки...)
або
FULLTEXT [INDEX] [ім’я_індекса] (ім’я_індексованої_колонки...)
або
[CONSTRAINT symbol] FOREIGN KEY [ім’я_індекса] (ім’я_індексованої_колонки...) [reference_definition]
або
CHECK (expr)
При завданні всіх цих елементів вказується список полів (стовпців), які входитимуть в індекс, ключ або обмеження, ім’я_індексованої_колонки записується таким чином:
ім’я_колонки [(довжина_індекса)]
FOREIGN KEY, CHECK і REFERENCES насправді нічого не роблять в MySQL. Вони додані тільки для сумісності з іншими SQL-серверами. Тому на них ми зупинятися не будемо.
Крім всього перерахованого, при створенні таблиці можна вказати деякі її властивості (опції_таблиці), наприклад такі:
тип таблиці: TYPE = {BDB | HEAP | ISAM | InnoDB | MERGE | MRG_MYISAM | MYISAM }
початкове значення лічильника автоінкременту: AUTO_INCREMENT = число
середня довжина рядків в таблиці: AVG_ROW_LENGTH = число
коментарі до таблиці (рядок з 60 символів): COMMENT = "рядок"
максимальне і мінімальне передбачуване число рядків: MAX_ROWS = число і MIN_ROWS = число
І останній (знову ж таки опціональний) елемент команди CREATE – цей вираз SELECT (select_вираз). Синтаксис такий:
[IGNORE | REPLACE] SELECT ... (будь-який коректний вираз SELECT)
Якщо при створенні таблиці в команді CREATE указується вираз SELECT, то всі поля, одержані вибіркою, додаються в створювану таблицю.
Приклад 10.1. Створимо таблицю Persons, структура якої була приведена на малюнку 10.1.
mysql>CREATE TABLE Persons (id INT PRIMARY KEY AUTO_INCREMENT
first_name VARCHAR(50), last_name
VARCHAR(100), death_date INT
description TEXT, photo INT
citizenship CHAR(50) DEFAULT 'Russia');
Приклад 10.1. Створення таблиці Persons
За допомогою специфічної для MySql команди SHOW можна проглянути існуючі бази даних, таблиці в базі даних і поля в таблиці.
Показати всі бази даних:
mysql>SHOW databases;
Зробити поточною базу даних book і показати всі таблиці в ній:
mysql>use book;
mysql>show tables;
Показати всі стовпці в таблиці Persons:
mysql> show columns from Persons;
Оператор DROP TABLE видаляє одну або декілька таблиць. Всі табличні дані і визначення віддаляються, так що при роботі з цією командою слід дотримуватися обережності.
Синтаксис:
DROP TABLE [IF EXISTS] ім’я_таблиці [, ім’я_таблиці...] [RESTRICT | CASCADE]
У версії MySQL 3.22 і більш пізніх можна використовувати ключові слова IF EXISTS, щоб попередити помилку, якщо вказані таблиці не існують.
Опції RESTRICT і CASCADE дозволяють спростити перенесення програми з інших СУБД. В даний момент вони не задіяні.
mysql> DROP TABLE IF EXISTS Persons Artifacts, test;
Приклад 10.2. Використовування оператора DROP TABLE
Оператор ALTER TABLE забезпечує можливість змінювати структуру існуючої таблиці. Наприклад, можна додавати або видаляти стовпці, створювати або знищувати індекси або перейменовувати стовпці або саму таблицю. Можна також змінювати коментар для таблиці і її тип.
Синтаксис:
ALTER [IGNORE] TABLE ім’я_таблиці alter_specification [, alter_specification ...]
Можна проводити наступні зміни в таблиці (всі вони записуються в alter_specification ):
додавання поля:
ADD [COLUMN] визначення_колонки [FIRST | AFTER ім’я_колонки ]
або
ADD [COLUMN] (визначення_колонки...)
Тут, як і далі, визначення_колонки записується так само, як при створенні таблиці.
Додавання індексів:
ADD INDEX [ім’я_індекса] (ім’я_індексованої_колонки...)
або
ADD PRIMARY KEY (ім’я_індексованої_колонки...)
або
ADD UNIQUE [ім’я_індекса] (ім’я_індексованої_колонки...)
або
ADD FULLTEXT [ім’я_індекса] (ім’я_індексованої_колонки...)
Зміна поля:
ALTER [COLUMN] ім’я_колонки {SET DEFAULT literal | DROP DEFAULT}
або
CHANGE [COLUMN] старе_ім’я_колонки визначення_колонки
або
MODIFY [COLUMN] визначення_колонки
Видалення поля, індексу, ключа:
DROP [COLUMN] ім’я_колонки
DROP PRIMARY KEY
DROP INDEX ім’я_індекса
Перейменування таблиці:
RENAME [TO] нове_ім’я_таблиці
Переупорядковування полів таблиці:
ORDER поле
Якщо оператор ALTER TABLE використовується для зміни визначення типу стовпця, але DESCRIBE ім’я_таблиці показує, що стовпець не змінився, то, можливо, MySQL ігнорує дану модифікацію по одній з причин, описаних в спеціальному розділі документації. Наприклад, при спробі змінити стовпець VARCHAR на CHAR MySQL продовжуватиме використовувати VARCHAR, якщо дана таблиця містить інші стовпці із змінною довжиною.
Оператор ALTER TABLE під час роботи створює тимчасову копію початкової таблиці. Необхідна зміна виконується на копії, потім початкова таблиця віддаляється, а нова перейменовується. Це робиться для того, щоб в нову таблицю автоматично потрапляли всі оновлення, окрім невдалих. Під час виконання ALTER TABLE початкова таблиця доступна для читання іншими клієнтами. Операції оновлення і запису в цій таблиці припиняються, поки не буде готова нова таблиця. Слід зазначити, що при використанні будь-якої іншої опції для ALTER TABLE, окрім RENAME, MySQL завжди створюватиме тимчасову таблицю, навіть якщо дані, строго кажучи, і не потребують копіювання (наприклад, при зміні імені стовпця).
Пример10.3. Додамо в створену таблицю Persons поле для запису року народження людини:
mysql> ALTER TABLE Persons ADD bday INTEGER AFTER last_name;
Приклад 10.3. Додавання в таблицю Persons поля для запису року народження людини
Отже, ми навчилися працювати з таблицями: створювати, видаляти і змінювати їх. Тепер розберемося, як робити те ж саме з даними, які в цих таблицях зберігаються.
Оператор SELECT застосовується для витягання рядків, вибраних з однієї або декількох таблиць. Тобто з його допомогою ми задаємо стовпці або вирази, які треба витягнути (select_вырази), таблиці (table_references ), з яких повинна проводитися вибірка, і, можливо, умову (where_definition), якій винні відповідати дані в цих стовпцях, і порядок, в якому ці дані потрібно видати.
Крім того, оператор SELECT можна використовувати для витягання рядків, обчислених без посилання на яку-небудь таблицю. Наприклад, щоб обчислити, чому рівно 2*2, потрібно просто написати
mysql> SELECT 2*2;
Спрощений структуру оператора SELECT можна представити таким чином:
SELECT select_вираз1, select_вираз2
[FROM table_references
[WHERE where_definition]
[ORDER {число | ім’я_колонки | формула} [ASC | DESC] ...]]
Квадратні дужки [ ] означають, що використання оператора, що знаходиться в них, необов'язкове, вертикальна межа | означає перелік можливих варіантів. Після ключового слова ORDER указують ім'я стовпця, число (ціле беззнакове) або формулу і спосіб впорядкування (за збільшенням – ASC, або по убуванню – DESC ). За умовчанням використовується впорядкування за збільшенням.
Коли в select_виразі ми пишемо " * ", це значить вибрати всі стовпці. Окрім " * " в select_вирази можуть використовуватися функції типу max, min і avg.
Приклад 10.4. Вибрати з таблиці Persons всі дані, для яких поле first_name має значення 'Олександр':
mysql> SELECT * FROM Persons
WHERE first_name='Олександр';
Приклад 10.4. Використовування оператора SELECT
Вибрати назву і опис ( title, description ) артефакту під номером 10:
mysql> SELECT title,description
FROM Artifacts WHERE id=10;
Оператор INSERT вставляє нові рядки в існуючу таблицю. Оператор має декілька форм. Параметр ім’я_таблиці у всіх цих формах задає таблицю, в яку повинні бути внесені рядки. Стовпці, для яких задаються значення, указуються в списку імен стовпців (ім’я_колонки) або в частині SET.
Синтаксис:
INSERT [LOW_PRIORITY | DELAYED] [IGNORE]
[INTO] ім’я_таблиці [(ім’я_колонки...)]
VALUES (вираз...),(...)...
Ця форма команди INSERT вставляє рядки відповідно до точно вказаних в команді значень. В дужках після імені таблиці перераховуються стовпці, а після ключового слова VALUES – їх значення.
Наприклад:
mysql> INSERT INTO Persons (last_name, bday) VALUES ('Іванов', '1934');
вставить в таблицю Persons рядок, в якому значення прізвища ( last_name ) і дати народження ( bday ) будуть задані відповідно як "Іванов" і "1934".
Наприклад:
mysql> INSERT INTO Artifacts (author)
SELECT id FROM Persons
WHERE last_name='Иванов'
AND bday='1934';
вставить в таблицю Artifacts в полі "автор" ( author ) значення ідентифікатора, вибраного з таблиці Persons по умові, що прізвище людини Іванов і рік народження 1934.
Діють наступні угоди.
Якщо не вказаний список стовпців для INSERT... VALUES або INSERT... SELECT, то величини для всіх стовпців повинні бути визначені в списку VALUES() або в результаті роботи SELECT . Якщо порядок стовпців в таблиці невідомий, для його отримання можна використовувати DESCRIBE ім’я_таблиці.
Будь-який стовпець, для якого явно не вказано значення, буде встановлений в своє значення за умовчанням. Наприклад, якщо в заданому списку стовпців не вказані всі стовпці в даній таблиці, то не згадані стовпці встановлюються в свої значення за умовчанням.
Вираз expression може відноситися до будь-якого стовпця, який раніше був внесений в список значень. Наприклад, можна вказати наступне: