ЗАГАЛЬНА ХАРАКТЕРИСТИКА МОВИ SQL
Мова SQL використовується в багатьох відомих СУБД і
серверах баз даних, таких як Oracle, SQL Server, SQLBase, Ingres, Sysbase, OS/2
Extended Edition, Informix, dBase IV, FoxPro і т. ін. Цю мову було розроблено
фірмою IBM, щоб надати користувачам розвинені засоби формулювання запитів і
відображення результатів їх виконання. Хоча мова й стандартизована, але багато
розробників програмних продуктів розширюють її власними елементами.
Назва «SQL» є абревіатурою від Structured Query
Language (структурована мова запитів). Раніше використовувалася й інша назва —
SEQUEL (як вимова «S.Q.L»).
Це непроцедурна мова, що реалізує основні функції
реляційних СУБД:
-
визначення даних
(SQL дозволяє визначати структуру таблиць бази даних і відношення між ними);
-
доступ до даних
(SQL забезпечує доступ до даних, що зберігаються, з відображенням отриманих
даних і передаванням їх на подальшу обробку);
-
маніпулювання
даними (SQL дозволяє користувачеві або прикладній програмі змінювати вміст
бази даних);
-
управління
доступом (SQL забезпечує синхронізацію обробки даних бази);
-
розподіл даних
(SQL координує роботу конкуруючих користувачів і забезпечує тим самим їхню
одночасну роботу з базами даних);
-
забезпечення
цілосності даних (за допомогою SQL можна визначити обмеження на модифікацію
даних, додержання яких дасть змогу запобігти порушенню цілосності даних).
Команди SQL, які застосовуються для виконання
зазначених функцій, поділяють на такі групи:
-
команди
визначення даних (Data Definition Commands);
-
команди
маніпулювання даними (Data Manipulation Commands);
-
команди вибору
даних — утворення вибірки (Data Query Commands);
-
команди
управління транзакціями (Transaction Control Commands);
-
команди
управління даними (Data Control Commands).
Команди
визначення даних дозволяють визначати
структуру даних і організацію використовуваних реляційних відношень. До цих
команд належать:
Команда |
Призначення |
ALTER TABLE |
Змінює структуру таблиці |
CREATE TABLE |
Створює індекс |
CREATE INDEX |
Створює таблицю |
CREATE VIEW |
Створює уявлення
(в’ювер) |
DROP |
Вилучає таблицю, індекс,
уявлення |
Команди
маніпулювання даними дають змогу
користувачеві або програмі змінювати вміст бази даних вставлянням нових
записів, вилученням записів та модифікацією вмісту полів. Це такі команди:
Команда |
Призначення |
DELET |
Вилучає записи з таблиці |
INSERT |
Додає записи до таблиці |
UPDATE |
Змінює дані таблиці |
Вибір
різноманітної інформації з бази даних
(утворення вибірки) досягається застосуванням лише однієї команди SELECT.
До команд
управління транзакціями, які дозволяють запобігти порушенню цілосності бази
даних, належать команди:
Команда |
Призначення |
COMMIT |
Робить зміни, виконані з
початку транзакції, постійними |
ROLLBACK |
Відкочує всі проведені
зміни до точки зберігання або до початку транзакції |
SAVEPOINT |
Установлює контрольну
точку, до якої згодом можна буде виконати відкочування |
Основна
функція команд управління даними — надання
користувачам відповідного права доступу до даних. Це такі команди:
Команда |
Призначення |
GRANT |
|
REVOKE |
Знімає раніше надані
привілеї |
Головною функцією, що використовується при роботі з
базами даних, є швидкий пошук потрібних даних і отримання відповідей на
різноманітні запити. Результатом виконання запиту завжди є таблиця заданої структури
з потрібною інформацією. В окремих випадках вихідна таблиця запиту може
складатися з одного стовпця (колонки) і одного рядка, тобто являти собою одне
значення.
Команда SELECT
Для формування запитів і вибору потрібної
інформації у SQL використовується команда SELECT. Ця команда описує дані, що мають бути відібрані, але
не описує конкретних методів, за допомогою яких це слід зробити.
У найпростішій формі команда SELECT інструктує базу даних про те, як витягти інформацію з
таблиці. Наприклад, для виведення таблиці телефонів Phone слід вказати:
SELECT Abonent, Street, House, Flat, Phone
FROM Phone
Результат виконання цієї команди буде приблизно
таким:
Abonent |
Street |
House |
Flat |
Phone |
ЛНТУ |
Львівська |
75 |
000 |
746101 |
Гуртожиток ЛНТУ |
Даньшина |
8 |
000 |
60915 |
Іванов А.Г. |
Кравчука |
10 |
015 |
61115 |
... |
... |
... |
... |
... |
Тобто, ця команда виведе всі дані з таблиці.
Розглянемо кожну частину цієї команди докладніше:
SELECT |
Ключове слово, яке
повідомляє базі даних, що ця команда –запит. Усі запити починаються цим
словом, після нього ставиться пробіл. |
Abonent, Street, House, Flat, Phone |
Список стовпців з
таблиці, які вибирають шляхом виконання запиту. Будь-які стовпці, які не
перераховані тут, не будуть включені в результат команди. Слід відмітити, що
цей запит лише показує дані, і якщо якісь дані не відображені, то це не
означає, що їх немає в самій таблиці. |
FROM Phone |
Ключове слово, яке слід
використовувати в кожному запиті. Воно супроводжується пробілом і потім
вказується ім’я таблиці, що є джерелом даних. В нашому випадку – таблиця
телефонів (Phone). |
; |
Крапка з комою
використовується у всіх інтерактивних командах SQL, як символ закінчення команди. У деяких системах
похила риска вліво (\) у рядку є індикатором кінця команди. |
Використання розриву рядка (клавіша ENTER) є довільним. З тих пір, як SQL використовує крапку з комою, щоб вказувати на кінець
команди, більшість програм SQL опрацьовують розрив рядка (клавіша ENTER) як пробіл.
Тому користувач вибирає самостійно як йому зручніше скласти запит, у кілька
рядків чи в один. Проте, краще використовувати розриви рядків, пробіли й
табуляцію для вирівнювання, щоб зробити команди легшими для читання.
Якщо потрібно вивести вміст всієї таблиці, то можна
використовувати наступну команду:
SELECT * FROM Phone
Це призведе до того ж результату, що й попередня
команда.
Повний вигляд цієї команди:
SELECT [DISTINCT | ALL]
<список елементів таблиці запиту | *>
FROM <список вхідних таблиць>
[WHERE <умова вибору>]
[GROUP BY <список імен стовпців таблиці запиту>]
[HAVING <умова включення груп до таблиці запиту>]
[ORDER BY <умова впорядкування записів таблиці запиту>]
Результуюча таблиця запиту може мати рядки, які
повторюються. Параметр DISTINCT виключає появу однакових рядків. За замовчуванням
виконується режим ALL, тобто всі рядки, отримані при виконанні запиту,
будуть включені до вихідної таблиці.
Список елементів таблиці запиту визначає, які
колонки повинна мати вихідна таблиця запиту. Символ * означає, що вихідна таблиця повинна включати в себе
всі колонки (поля) усіх таблиць бази, на основі яких будується запит. Якщо
кілька таблиць бази мають однойменні колонки, то для ідентифікації потрібної
колонки необхідно вказати її повне ім’я, яке складається з імені таблиці (чи її
псевдоніму) та імені колонки, відокремлених одне від одного символом «крапка».
Полем вихідної таблиці запиту може бути:
-
поле будь-якої
таблиці бази, вказане у фразі FROM;
-
константа.
Вказане значення константи з’явиться у кожному рядку вихідної таблиці запиту;
-
вираз, який крім
полів бази може включати константи і стандартні функції. Якщо робота
виконується в режимі клієнт/сервер, вибір реалізується із сервера баз даних і
результат запиту зберігається в тимчасовому буфері сервера баз даних. Тому в
цьому режимі не можна використовувати функції користувачів та змінні, що
застосовуються у програмі.
Імені колонки вихідної таблиці запиту може бути
надане інше ім’я.
Фраза FROM визначає список таблиць бази даних, на основі яких
будується запит. Імена таблиць мають відокремлюватися одне від одного комою.
Для кожної таблиці можна вказати ім’я її локального псевдоніму, який у цьому
запиті можна використовувати замість імені відповідної таблиці.
Фраза WHERE дозволяє вказувати умови, що їх мають задовольняти
значення полів у рядках вихідної таблиці запиту, виконуючи таким чином роль
фільтра для добору даних із таблиць бази.
Конструкція GROUP BY
Фраза GROUP BY призначена для групування рядків. Тобто рядки, які
належать одній групі, у вихідній таблиці запиту будуть подані одним рядком. У
цьому разі використання стандартних функцій для формування поля вихідної
таблиці дозволяє визначити параметри груп (такі як кількість рядків у групі,
сумарне або середнє значення за вказаним полем у групі, мінімальне або
максимальне значення поля у групі). Імена стовпців (поля) вихідної таблиці
запиту, що вказуються в цій фразі, записуються через кому. Рядки з однаковими
значеннями в цих полях належать до однієї групи. Стандарт мови дозволяє
задавати поля групування не лише за іменами, а й числовим виразом, який вказує
на положення (порядковий номер) відповідного стовпця у вихідній таблиці запиту.
Конструкція GROUP BY дозволяє визначати підмножину значень і застосовувати
агрегатну функцію до цієї підмножини. Це дає можливість поєднувати поля й агрегатні функції в єдиній конструкції SELECT.
Наприклад, потрібно
визначити з таблиці film_list скільки фільмів вийшло
кожного року. Для цього можна, звичайно, виконати окремий запит для кожного року, вибравши COUNT (Name) з таблиці. Проте GROUP BY
дозволяє помістити все в один запит:
SELECT Year, COUNT(Name)
FROM film_list
GROUP BY Year
Результат виконання даного
запиту матиме вигляд:
Year |
COUNT(Name) |
0 |
5 |
1996 |
2 |
... |
... |
Можна використовувати
конструкцію GROUP BY одразу з кількома полями. Припустимо, потрібно дізнатися для кожної країни,
скільки фільмів вона випускала
щороку.
SELECT Country,
Year, COUNT(Name) FROM film_list
GROUP BY Country, Year
Звичайно порожні групи
(роки, коли певна країна не випустила жодного
фільму) не будуть показані в результаті.
Конструкція HAVING
Припустимо, з таблиці
фільмів потрібно вибрати тільки ті роки, в які було випущено більше 50-ти фільмів. Не можна використати WHERE, наприклад, так:
SELECT Year, COUNT(Name) FROM film_list
WHERE C0UNT(Name)>50
GROUP BY Year
Це буде суперечити строгій інтерпретації ANSI. Щоб побачити список усіх років,
кількість фільмів в яких перевищує 50, слід використати конструкцію HAVING. Конструкція HAVING визначає критерії, що
використовуються для видалення певних груп з виведення, так само, як
конструкція WHERE робить
це для окремих рядків. Правильний розв'язок:
SELECT Year, COUNT(Name) FROM film_list
GROUP BY Year
HAVING C0UNT(Name)>50
Результат виконання запиту:
Year |
COUNT(Name) |
1997 |
57 |
1999 |
62 |
... |
... |
Фраза HAVING дозволяє дібрати з множини груп лише ті, які
відповідають вказаним умовам. HAVING застосовується для фільтрації груп, як WHERE
застосовується для фільтрації запитів, і звичайно використовується разом з GROUP BY.
Таблиці — це
невпорядковані набори даних, і дані, які містяться в них, не обов'язково з'являються в якійсь певній
послідовності. SQL використовує команду ORDER BY, щоб впорядковувати результат виконання запиту. Ця команда впорядковує виведення запиту
відповідно до значень у тій або іншій
кількості обраних стовпців. Кілька стовпців
впорядковуються один усередині іншого. Можна визначати зростаючий (ASC) або спадний (DESC) порядок для кожного стовпця. За замовчуванням встановлено — зростаючий.
Наприклад, такий запит впорядковує
таблицю фільмів за полем країна.
За умови, що назви країн
співпадають, рядки розміщуються у
спадному порядку за роком випуску:
SELECT * FROM film_list
ORDER BY Country ASC, Year Desc
Замість імені поля може застосовуватися числовий
вираз, що вказує положення (порядковий номер) відповідного стовпця у вихідній
таблиці запиту. Для кожного з них можна вибрати впорядкування за зростанням або
спаданням.
Оператор LIKE
LIKE застосовують тільки до текстових полів, наприклад,
CHAR чи VARCHAR, з якими він використовується, щоб знаходити підстрічки. Тобто він шукає у текстовому полі, чи співпадає з
умовою його вміст. Як умову оператор
використовує групові символи (wildkards) — спеціальні символи,
які можуть відповідати чому-небудь. Є два типи групових символів, що використовуються з LIKE:
•
символ
підкреслення (_) замінює будь-який один символ. Наприклад, 'b_t' буде відповідати словам 'bat' або 'bit', але не буде відповідати 'brat'.
•
знак відсотка
(%)
замінює послідовність будь-якого числа символів
(включаючи символи нуля). Наприклад, '%p%t' буде відповідати
словам 'put', 'posit', 'opt', але не 'spite'.
Наприклад, нам потрібно вивести всіх
абонентів, прізвище яких починається
на "Гавр":
SELECT * from Phone where Abonent Like 'Гавр%'
Запити можуть видавати
узагальнене групове значення полів і значення
одного поля. Це робиться за допомогою агрегатних функцій. Агрегатні функції видають одне значення для всієї групи
таблиці. Список цих функцій:
Функція |
Обчислює
для кожної групи запитів |
Count |
Кількість рядків |
Sum |
Суму значень заданого параметра |
Avg |
Середнє значення заданого параметра |
Max |
Найбільше значення заданого параметра |
Min |
Найменше значення заданого параметра |
Всі агрегатні функції ігнорують порожні (NULL) значення полів. Виняток становить лише функція COUNT.
При
вказуванні як аргументу символа "*" (зірочка) вона видасть
кількість рядків незалежно від того, які значення в них містяться.
Наприклад, потрібно
визначити кількість фільмів в таблиці film_list:
SELECT COUNT(Name) FROM film_list
Вивести рік, коли
знімався найстаріший фільм з таблиці, враховуючи, що при невідомому році в таблицю заносились нулі.
SELECT MIN(Year)
FROM film_list
where year<>0
Агрегатні функції можуть
приймати як аргумент не тільки поля таблиці,
але й вирази. Наприклад, визначити максимальну різницю років між датою випуску фільму й датою його додавання до
таблиці:
SELECT (Year(‘AddDate’)-'уеаґ)
FROM film_list
where 'year'<>0
В
останньому запиті використано функцію Year(), яка виділяє з
дати рік. Оскільки в таблиці є поле з аналогічним
іменем, то ім'я поля взято у
зворотні лапки.
При створенні будь-якого запиту обов’язково
потрібно визначити:
-
поля вихідної
таблиці запиту (тобто поля таблиці, яка буде отримана внаслідок виконання
запиту);
-
вхідні таблиці,
з яких будуть вибиратися дані для формування вихідної таблиці.
-
Інші параметри
запиту не є обов’язковими і використовуються для визначення додаткових вимог до
даних, які мають бути відібрані, щоб сформувати вихідну таблицю.
Операції над схемою бази даних
Числові типи |
|
NUMERIC, DECIMAL (DEC) |
найчастіше
використовується для зберігання грошових значень (дійсні числа) |
INTEGER (INT) |
ціле число, займає 4
байти |
TINYINT( BIT, BOOL) |
ціле, 1 байт |
SMALLINT |
ціле, 2 байти |
MEDIUMINT |
ціле, 3 байти |
BIGINT |
ціле, 8 байт |
FLOAT |
дійсне число зі
звичайною точністю |
DOUBLE (REAL, DOUBLE
PRECISION) |
дійсне число з подвійною
точністю |
Текстові типи та
стрічки |
|
CHAR |
стрічка
фіксованої довжини (макс.255) |
VARCHAR |
стрічка
змінної довжини (макс.255) |
TEXT, BLOB |
стрічка
змінної довжини до 64 Кбайт (65535 символів) |
TINYTEXT, TYNYBLOB |
стрічка
змінної довжини (до 255 символів ) |
MEDIUMTEXT,
MEDIUMBLOB |
стрічка
змінної довжини до 16 Мбайт (16777215 символів) |
LONGTEXT,LONGBLOB |
стрічка
змінної довжини до 4 Gb |
ENUM |
цей тип
дозволяє перерахувати набір можливих значень (одне з перерахованих) |
SET |
множина
(кілька з перерахованих) |
Типи дати та часу |
|
DATE |
дата у
вигляді РРРР-ММ-ДД |
TIME |
час у
вигляді ГГ:ХХ:СС |
DATETIME |
комбінація
попередніх двох типів у форматі: РРРР-ММ-ДД ГГ:ХХ:СС |
TIMESTAMP |
в стовпець
буде записано дату створення або останнього редагування запису у форматі DATETIME (якщо не буде зазначено
іншої дати) |
YEAR |
рік, може
бути: |
YEAR(2) |
РР (для
дат з 1970 по 2069) |
YEAR(4) |
за
замовчуванням РРРР |
Операція |
Призначення |
CREATE DATABASE |
створення бази даних |
USE |
зробити базу поточною |
CREATE TABLE |
створення таблиці в базі
даних |
DROP DATABASE |
видалення бази даних |
DROP TABLE |
видалення таблиці з бази
даних |
SHOW TABLES |
перегляд списку таблиць
поточної бази даних |
ALTER TABLE |
зміна структури таблиці,
що вже існує |
SHOW DATABASES |
перегляд списку баз даних |
DESCRIBE |
отримання інформації про
конкретну таблицю |
Тип |
Опис |
MyISAM |
Приймається за
замовчуванням. Таблиці цього типу працюють дуже швидко. Тип підтримує
повнотекстовий пошук, але не підтримує обробку трансакцій. Похідний від ISAM. |
ISAM |
Старий тип таблиць
Подібний до MyISAM, але з
меншими можливостями. |
MEMORY |
Функціонально
еквівалентний MyISAM, але
дані зберігає в оперативній пам’яті (а не на диску), він надзвичайно швидкий
і є оптимальним для тимчасових таблиць |
InnoDB |
Механізм, що підтримує
трансакції, зовнішні ключі та блокування на рівні рядків, але не підтримує
повнотекстовий пошук по текстових полях. |
BDB |
Механізм, що підтримує
підтримку трансакцій і блокування на рівні сторінок. |
HEAP |
Таблиці цього типу
завжди зберігаються в пам’яті і ніколи не записуються на диск, тому працюють
дуже швидко, але обмежені в розмірах і не надають можливості відновлення у
випадку відмови системи. |
MERGE |
Таблиці цього типу
дозволяють об’єднати кілька таблиць MyISAM з одною
структурою щоб до них можна було звертатися як до однієї таблиці. Це можна
використати для того, щоб обійти обмеження операційної системи на
максимальний розмір файлів, а відповідно і таблиць. |