Елементи мови SQL

У даному розділі розглядаються елементи мови SQL (Structured Query Language). Поточна версія стандарту мови SQL прийнята в 1992 р. (Офіційна назва стандарту - Міжнародний стандарт мови баз даних SQL (1992) (International Standart Database Language SQL), неофіційна назва - SQL/92, або SQL-92, або SQL2). Документ, що описує стандарт, містить більше 600 сторінок. Ми дамо тільки деякі поняття мови.
Мова SQL стала фактично стандартною мовою доступу до баз даних. Всі СУБД, що претендують на назву "реляційні", реалізують той або інший діалект SQL. Багато нереляційних систем також мають в даний час засоби доступу до реляційних даних. Метою стандартизації є переносимість додатків між різними СУБД.
Потрібно помітити, що в даний час, жодна система не реалізує стандарт SQL в повному об'ємі. Крім того, у всіх діалектах мови є можливості, що не є стандартними. Таким чином, можна сказати, що кожний діалект - це надмножина деякої підмножини стандарту SQL. Це утрудняє переносимість додатків, розроблених для одних СУБД в інші СУБД.
Мова SQL оперує термінами, дещо відмінними від термінів реляційної теорії, наприклад, замість "відносин" використовуються "таблиці", замість "кортежів" - "рядка", замість "атрибутів" - "колонки" або "стовпці".
Стандарт мови SQL, хоча і заснований на реляційній теорії, але в багатьох місцях відходить він її. Наприклад, відношення в реляційній моделі даних не допускає наявності однакових кортежів, а таблиці в термінології SQL можуть мати однакові рядки. Є і інші відмінності.
Мова SQL є реляційно повною. Це означає, що будь-який оператор реляційної алгебри може бути виражений відповідним оператором SQL.
Оператори SQL
Основу мови SQL складають оператори, умовні розбиті не декілька груп по виконуваних функціях.
Можна виділити наступні групи операторів (перераховані не всі оператори SQL):
Оператори DDL (Data Definition Language) - оператори визначення об'єктів бази даних
CREATE SCHEMA - створити схему бази даних
DROP SHEMA - видалити схему бази даних
CREATE TABLE - створити таблицю
ALTER TABLE - змінити таблицю
DROP TABLE - видалити таблицю
CREATE DOMAIN - створити домен
ALTER DOMAIN - змінити домен
DROP DOMAIN - видалити домен
CREATE COLLATION - створити послідовність
DROP COLLATION - видалити послідовність
CREATE VIEW - створити представлення
DROP VIEW - видалити представлення
Оператори DML (Data Manipulation Language) - оператори маніпулювання даними
SELECT - відібрати рядки з таблиць
INSERT - додати рядки в таблицю
UPDATE - змінити рядки в таблиці
DELETE - видалити рядки в таблиці
COMMIT - зафіксувати внесені зміни
ROLLBACK - відкотити внесені зміни
Оператори захисту і управління даними
CREATE ASSERTION - створити обмеження
DROP ASSERTION - видалити обмеження
GRANT - надати привілеї користувачу або додатку на маніпулювання об'єктами
REVOKE - відмінити привілеї користувача або додатки
Крім того, є групи операторів установки параметрів сеансу, отримання інформації про базу даних, оператори статичного SQL, оператори динамічного SQL.
Найважливішими для користувача є оператори маніпулювання даними (DML).
Приклади використовування операторів маніпулювання даними
INSERT - вставка рядків в таблицю
Приклад 1. Вставка одного рядка в таблицю:
INSERT INTO
P (PNUM, PNAME)
VALUES (4, "Іванов");
Приклад 2. Вставка в таблицю декількох рядків, вибраних з іншої таблиці (в таблицю TMP_TABLE вставляються дані про постачальників з таблиці P, що мають номери, великі 2):
INSERT INTO
TMP_TABLE (PNUM, PNAME)
SELECT PNUM, PNAME
FROM P
WHERE P.PNUM>2;
UPDATE - оновлення рядків в таблиці
Приклад 3. Оновлення декількох рядків в таблиці:
UPDATE P
SET PNAME = "Хутровиків"
WHERE P.PNUM = 1;
DELETE - видалення рядків в таблиці
Приклад 4. Видалення декількох рядків в таблиці:
DELETE FROM P
WHERE P.PNUM = 1;
Приклад 5. Видалення всіх рядків в таблиці:
DELETE FROM P;
Приклади використовування оператора SELECT
Оператор SELECT є фактично найважливішим для користувача і найскладнішим оператором SQL. Він призначений для вибірки даних з таблиць, тобто він, власне, і реалізує одне їх основних призначення бази даних - надавати інформацію користувачу.
Оператор SELECT завжди виконується над деякими таблицями, що входять в базу даних.
Зауваження. Насправді в базах даних можуть бути не тільки постійно бережені таблиці, а також тимчасові таблиці і так звані представлення. Представлення - це просто дані SELECT-вирази, що зберігаються в базі. З погляду користувачів представлення - це таблиця, яка не зберігається постійно в базі даних, а "виникає" у момент звернення до неї. З погляду оператора SELECT і постійно збережені таблиці, і тимчасові таблиці і представлення виглядають абсолютно однаково. Звичайно, при реальному виконанні оператора SELECT системою враховуються відмінності між збереженими таблицями і представленнями, але ці відмінності приховані від користувача.
Результатом виконання оператора SELECT завжди є таблиця. Таким чином, за наслідками дій оператор SELECT схожий на оператори реляційної алгебри. Будь-який оператор реляційної алгебри може бути виражений відповідним чином сформульованим оператором SELECT. Складність оператора SELECT визначається тим, що він містить в собі всі можливості реляційної алгебри, а також додаткові можливості, яких в реляційній алгебрі немає.
Відбір даних з однієї таблиці
Приклад 6. Вибрати всі дані з таблиці постачальників (ключові слова SELECT. FROM.):
SELECT *
FROM P;
Зауваження. В результаті одержимо нову таблицю, що містить повну копію даних з початкової таблиці P.
Приклад 7. Вибрати всі рядки з таблиці постачальників, що задовольняють деякій умові (ключове слово WHERE.):
SELECT *
FROM P
WHERE P.PNUM > 2;
Зауваження. Як умова в розділі WHERE можна використовувати складні логічні вирази, що використовують поля таблиць, константи, порівняння (> = D.DSTATUS;
У результаті одержимо наступну таблицю:
PNUM | PNAME | PSTATUS | DNUM | DNAME | DSTATUS
1 | Іванов | 4 | 1 | Болт | 3
1 | Іванов | 4 | 2 | Гайка | 2
1 | Іванов | 4 | 3 | Гвинт | 1
2 | Петров | 1 | 3 | Гвинт | 1
3 | Сидоров | 2 | 2 | Гайка | 2
3 | Сидоров | 2 | 3 | Гвинт | 1
Використовування імен кореляції (аліасів, псевдонімів)
Іноді доводиться виконувати запити, в яких таблиця з'єднується сама з собою, або одна таблиця з'єднується двічі з іншою таблицею. При цьому використовуються імена кореляції (аліаси, псевдоніми), які дозволяють розрізняти копії таблиць, що сполучаються. Імена кореляції вводяться в розділі FROM і йдуть через пропуск після імені таблиці. Імена кореляції повинні використовуватися як префікс перед ім'ям стовпця і відділяються від імені стовпця крапкою. Якщо в запиті вказуються одні і ті ж поля з різних екземплярів однієї таблиці, вони повинні бути перейменовані для усунення неоднозначності в іменуючі колонок результуючій таблиці. Визначення імені кореляції діє тільки під час виконання запиту.
Приклад 19. Відібрати всі пари постачальників так, щоб перший постачальник в парі мав статус, більший статусу другого постачальника:
SELECT
P1.PNAME AS PNAME1
P1.PSTATUS AS PSTATUS1
P2.PNAME AS PNAME2
P2.PSTATUS AS PSTATUS2
FROM
P P1, P P2
WHERE P1.PSTATUS1 > P2.PSTATUS2;
У результаті одержимо наступну таблицю:
PNAME1 | PSTATUS1 | PNAME2 | PSTATUS2
Іванов | 4 | Петров | 1
Іванов | 4 | Сидоров | 2
Сидоров | 2 | Петров | 1
Приклад 20. Розглянемо ситуацію, коли деякі постачальники (назвемо їх контрагенти) можуть виступати як як постачальники деталей, так і як одержувачі. Таблиці, що бережуть дані можуть мати наступний вигляд:
Номер контрагента
NUM | Найменування контрагента
NAME
1 | Іванов
2 | Петров
3 | Сидоров
Таблиця 3 Відношення CONTRAGENTS
Номер деталі
DNUM | Найменування деталі
DNAME
1 | Болт
2 | Гайка
3 | Гвинт
Таблиця 4 Відношення DETAILS (Деталі)
Номер постачальника
PNUM | Номер отримувача
CNUM | Номер деталі
DNUM | Поставляемое количество
VOLUME
1 | 2 | 1 | 100
1 | 3 | 2 | 200
1 | 3 | 3 | 300
2 | 3 | 1 | 150
2 | 3 | 2 | 250
3 | 1 | 1 | 1000
Таблиця 5 Відношення CD (Поставки)
У таблиці CD (поставки) поля PNUM і CNUM є зовнішніми ключами, що посилаються на потенційний ключ NUM в таблиці CONTRAGENTS.
Відповідь на питання "хто кому що в якій кількості поставляє" дається наступним запитом:
SELECT
P.NAME AS PNAME
C.NAME AS CNAME
DETAILS.DNAME
CD.VOLUME
FROM
CONTRAGENTS P
CONTRAGENTS З
DETAILS
CD
WHERE
P.NUM = CD.PNUM AND
C.NUM = CD.CNUM AND
D.DNUM = CD.DNUM;
У результаті одержимо наступну таблицю:
Найменування постачальника
PNAME | Найменування одержувача
CNAME | Найменування деталі
DNAME | Кількість, що поставляється
VOLUME
Іванов | Петров | Болт | 100
Іванов | Сидоров | Гайка | 200
Іванов | Сидоров | Гвинт | 300
Петров | Сидоров | Болт | 150
Петров | Сидоров | Гайка | 250
Сидоров | Іванов | Болт | 1000
Зауваження. Цей же запит може бути виражений дуже великою кількістю способів, наприклад, так:
SELECT
P.NAME AS PNAME
C.NAME AS CNAME
DETAILS.DNAME
CD.VOLUME
FROM
CONTRAGENTS P
CONTRAGENTS З
DETAILS NATURAL JOIN CD
WHERE
P.NUM = CD.PNUM AND
C.NUM = CD.CNUM;
Використовування агрегатних функцій в запитах
Приклад 21. Одержати загальну кількість постачальників (ключове слово COUNT):
SELECT COUNT(*) AS N
FROM P;
У результаті одержимо таблицю з одним стовпцем і одним рядком, що містить кількість рядків з таблиці P:
N
3
Приклад 22. Одержати загальне, максимальне, мінімальне і середнє кількості деталей, що поставляються (ключові слова SUM, MAX, MIN, AVG):
SELECT
SUM(PD.VOLUME) AS SM
MAX(PD.VOLUME) AS MX
MIN(PD.VOLUME) AS MN
AVG(PD.VOLUME) AS AV
FROM PD;
У результаті одержимо наступну таблицю з одним рядком:
SM | MX | MN | AV
2000 | 1000 | 100 | 333.33333333
Використовування агрегатних функцій з групуваннями
Приклад 23. Для кожної деталі одержати сумарну кількість, що поставляється (ключове слово GROUP.):
SELECT
PD.DNUM
SUM(PD.VOLUME) AS SM
GROUP PD.DNUM;
Цей запит виконуватиметься таким чином. Спочатку рядки початкової таблиці будуть згруповані так, щоб в кожну групу потрапили рядки з однаковими значеннями DNUM. Потім усередині кожної групи буде підсумовано поле VOLUME. Від кожної групи в результуючу таблицю буде включений один рядок:
DNUM | SM
1 | 1250
2 | 450
3 | 300
Зауваження. В списку відбираних полів оператора SELECT, що містить розділ GROUP можна включати тільки агрегатні функції і поля, які входять в умову угрупування. Наступний запит видасть синтаксичну помилку:
SELECT
PD.PNUM
PD.DNUM
SUM(PD.VOLUME) AS SM
GROUP PD.DNUM;
Причина помилки в тому, що в список відбираних полів включено поле PNUM, яке не входить в розділ GROUP. І дійсно, в кожну одержану групу рядків може входити декілька рядків з різними значеннями поля PNUM. З кожної групи рядків буде сформовано по одному підсумковому рядку. При цьому немає однозначної відповіді на питання, яке значення вибрати для поля PNUM в підсумковому рядку.
Зауваження. Деякі діалекти SQL не рахують це за помилку. Запит буде виконаний, але передбачити, які значення будуть внесені в полі PNUM в результуючій таблиці, неможливо.
Приклад 24. Одержати номери деталей, сумарна кількість яких, що поставляється, перевершує 400 (ключове слово HAVING.):
Зауваження. Умова, що сумарна кількість, що поставляється, повинна бути більше 400 не може бути сформульовано в розділі WHERE, оскільки в цьому розділі не можна використовувати агрегатні функції. Умови, що використовують агрегатні функції повинні бути розміщені в спеціальному розділі HAVING:
SELECT
PD.DNUM
SUM(PD.VOLUME) AS SM
GROUP PD.DNUM
HAVING SUM(PD.VOLUME)> 400;
У результаті одержимо наступну таблицю:
DNUM | SM
1 | 1250
2 | 450
Зауваження. В одному запиті можуть зустрітися як умови відбору рядків в розділі WHERE, так і умови відбору груп в розділі HAVING. Умови відбору груп не можна перенести з розділу HAVING в розділ WHERE. Аналогічно і умови відбору рядків не можна перенести з розділу WHERE в розділ HAVING, за винятком умов, що включають поля із списку угрупування GROUP.
Використовування підзапитів
Дуже зручним засобом, що дозволяє формулювати запити більш зрозумілим чином, є можливість використовування підзапитів, вкладених в основний запит.
Приклад 25. Одержати список постачальників, статус яких менше максимального статусу в таблиці постачальників (порівняння з підзапитом):
SELECT *
FROM P
WHERE P.STATYS 3
UNION
SELECT P.PNAME
FROM P, PD
WHERE P.PNUM = PD.PNUM AND
PD.DNUM = 2;
Зауваження. Результуючі таблиці об'єднуваних запитів повинні бути сумісні, тобто мати однакову кількість стовпців і однакові типи стовпців в порядку їх переліку. Не вимагається, щоб об'єднувані таблиці мали б однакові імена колонок. Це відрізняє операцію об'єднання запитів в SQL від операції об'єднання в реляційній алгебрі. Найменування колонок в результуючому запиті будуть автоматично узяті з результату першого запиту в об'єднанні.
Приклад 31. Одержати імена постачальників, що мають статус, більший 3 і одночасно поставляючих хоча б одну деталь номер 2 (перетин двох підзапитів - ключове слово INTERSECT):
SELECT P.PNAME
FROM P
WHERE P.STATUS > 3
INTERSECT
SELECT P.PNAME
FROM P, PD
WHERE P.PNUM = PD.PNUM AND
PD.DNUM = 2;
Приклад 32. Одержати імена постачальників, що мають статус, більший 3, за винятком тих, хто поставляє хоча б одну деталь номер 2 (різниця двох підзапитів - ключове слово EXCEPT):
SELECT P.PNAME
FROM P
WHERE P.STATUS > 3
EXCEPT
SELECT P.PNAME
FROM P, PD
WHERE P.PNUM = PD.PNUM AND
PD.DNUM = 2;
Синтаксис оператора вибірки даних (SELECT)
BNF-нотация
Опишемо синтаксис оператора вибірки даних (оператора SELECT) більш точно. При описі синтаксису операторів звичайно використовуються умовні позначення, відомі як стандартні форми Бекуса-Наура (BNF).
У BNF позначеннях використовуються наступні елементи:
Символ "::=" означає рівність за визначенням. Зліва від знаку стоїть визначуване поняття, справа - власне визначення поняття.
Ключові слова записуються прописними буквами. Вони зарезервовані і складають частину оператора.
Мітки-заповнювачі конкретних значень елементів і змінних записуються курсивом.
Необов'язкові елементи оператора укладені в квадратні дужки [].
Вертикальна межа | указує на те, що всі попередні їй елементи списку є необов'язковими і можуть бути замінені будь-яким іншим елементом списку після цієї межі.
Фігурні дужки {} указують на те, що що все знаходиться усередині них є єдиним цілим.
Трикрапка "." означає, що попередня частина оператора може бути повторена будь-яка кількість раз.
Багатокрапка, усередині якої знаходиться кома ".,.." указує, що попередня частина оператора, що складається з декількох елементів, розділених комами, може мати довільне число повторень. Кому не можна ставити після останнього елементу. Зауваження: дана угода не входить в стандарт BNF, але дозволяє більш точно описати синтаксис операторів SQL.
Круглі дужки є елементом оператора.
Синтаксис оператора вибірки
У досить сильно спрощеному вигляді оператор вибірки даних має наступний синтаксис (для деяких елементів ми дамо не BNF-визначення, а словесний опис):
Оператор вибірки ::=
Табличний вираз
[ORDER{{Ім'я стовпця-результату [ASC | DESC]} | {Позитивне ціле [ASC | DESC]}}.,..];
Табличний вираз ::=
Select-вираз
[
{UNION | INTERSECT | EXCEPT} [ALL]
вираз | TABLE Ім'я таблиці | Конструктор значень таблиці}
]
Select-вираз ::=
SELECT [ALL | DISTINCT]
{{{Скалярний вираз | Функція агрегації | Select-вираз} [AS Ім'я стовпця]}.,..}
| {Ім'я таблицы|Ім’я кореляції}.*}
| *

Зауваження. Select-вираз в розділі SELECT, що використовується як значення для відбираного стовпця, повинен повертати таблицю, що складається з одного рядка і одного стовпця, тобто скалярний вираз.
Зауваження. Умовний вираз в розділі WHERE повинен обчислюватися для кожного рядка, що є кандидатом в результуючу множину рядків. В цьому умовному виразі можна використовувати підзапити. Синтаксис умовних виразів, допустимих в розділі WHERE розглядається нижче.
Зауваження. Розділ HAVING містить умовний вираз, обчислюваний для кожної групи, визначуваної списком угрупування в розділі GROUP. Цей умовний вираз може містити функції агрегації, обчислювані для кожної групи. Умовний вираз, сформульований в розділі WHERE, може бути перенесене в розділ HAVING. Перенесення умов з розділу HAVING в розділ WHERE неможливе, якщо умовний вираз містить агрегатні функції. Перенесення умов з розділу WHERE в розділ HAVING є поганим стилем програмування - ці розділи призначені для різних по значенню умов (умови для рядків і умови для груп рядків).
Зауваження. Якщо в розділі SELECT присутні агрегатні функції, то вони обчислюються по-різному залежно від наявності розділу GROUP. Якщо розділ GROUP відсутній, то результат запиту повертає не більше одного рядка. Агрегатні функції обчислюються по всіх рядках, що задовольняють умовному виразу в розділі WHERE. Якщо розділ GROUP присутній, то агрегатні функції обчислюються по окремості для кожної групи, визначеної в розділі GROUP.
Скалярний вираз - як скалярні вирази в розділі SELECT можуть виступати або імена стовпців таблиць, що входять в розділ FROM, або прості функції, що повертають скалярні значення.
Функція агрегації ::=
COUNT (*) |
{{COUNT | MAX | MIN | SUM | AVG} ([ALL | DISTINCT] Скалярний вираз)
}
Конструктор значень таблиці ::=
VALUES Конструктор значень рядка...
Конструктор значень рядка ::=
Елемент конструктора | (Елемент конструктора...) | Select-вираз
Зауваження. Select-вираз, що використовується в конструкторі значень рядка, зобов'язаний повертати рівно один рядок.
Елемент конструктора ::=
Вираз для обчислення значення | NULL | DEFAULT
Синтаксис сполучених таблиць
У розділі FROM оператора SELECT можна використовувати сполучені таблиці. Хай в результаті деяких операцій ми одержуємо таблиці А і B. Такими операціями можуть бути, наприклад, оператор SELECT або інша сполучена таблиця. Тоді синтаксис сполученої таблиці має наступний вигляд:
Сполучена таблиця ::=
Перехресне з'єднання
| Природне з'єднання
| З'єднання за допомогою предиката
| З'єднання за допомогою імен стовпців
| З'єднання об'єднання
Тип з'єднання ::=
INNER
| LEFT [OUTER]
| RIGTH [OUTER]
| FULL [OUTER]
Перехресне з'єднання ::=
Таблиця А CROSS JOIN Таблиця В
Природне з'єднання ::=
Таблиця А [NATURAL] [Тип з'єднання] JOIN Таблиця В
З'єднання за допомогою предиката ::=
Таблиця А [Тип з'єднання] JOIN Таблиця В ON Предикат
З'єднання за допомогою імен стовпців ::=
Таблиця А [Тип з'єднання] JOIN Таблиця В USING (Ім'я стовпця...)
З'єднання об'єднання ::=
Таблиця А UNION JOIN Таблиця В
Опишемо терміни, що використовуються.
CROSS JOIN - Перехресне з'єднання повертає просто декартовий твір таблиць. Таке з'єднання в розділі FROM може бути замінено списком таблиць через кому.
NATURAL JOIN - Природне з'єднання проводиться по всіх стовпцях таблиць А і В, має однакові імена. В результуючу таблицю однакові стовпці вставляються тільки один раз.
JOIN . ON - З'єднання за допомогою предиката сполучає рядки таблиць А і В за допомогою вказаного предиката.
JOIN . USING - З'єднання за допомогою імен стовпців сполучає відносини подібно природному з'єднанню по тих загальних стовпцях таблиць А і б, які вказані в списку USING.
OUTER - Ключове слово OUTER (зовнішній) не є обов'язковими, воно не використовується ні в яких операціях з даними.
INNER - Тип з'єднання "внутрішнє". Внутрішній тип з'єднання використовується за умовчанням, коли тип явно не заданий. В таблицях А і В з'єднуються тільки ті рядки, для яких знайдений збіг.
LEFT (OUTER) - Тип з'єднання "ліве (зовнішнє)". Ліве з'єднання таблиць А і У включає всі рядки з лівої таблиці А і ті рядки з правої таблиці В, для яких знайдений збіг. Для рядків з таблиці А, для яких не знайдено відповідності в таблиці В, в стовпці, витягувані з таблиці В, заносяться значення NULL.
RIGHT (OUTER) - Тип з'єднання "праве (зовнішнє)". Праве з'єднання таблиць А і У включає всі рядки з правої таблиці В і ті рядки з лівої таблиці А, для яких знайдений збіг. Для рядків з таблиці В, для яких не знайдено відповідності в таблиці А, в стовпці, витягувані з таблиці А заносяться значення NULL.
FULL (OUTER) - Тип з'єднання "повне (зовнішнє)". Це комбінація лівого і правого з'єднань. В повне з'єднання включаються всі рядки з обох таблиць. Для співпадаючих рядків поля заповнюються реальними значеннями, для неспівпадаючих рядків поля заповнюються відповідно до правил лівого і правого з'єднань.
UNION JOIN - З'єднання об'єднання є зворотним по відношенню до внутрішнього з'єднання. Воно включає тільки ті рядки з таблиць А і В, для яких не знайдено збігів. В них використовуються значення NULL для стовпців, одержаних з іншої таблиці. Якщо узяти повне зовнішнє з'єднання і видалити з нього рядки, одержані в результаті внутрішнього з'єднання, то вийде з'єднання об'єднання.
Використовування сполучених таблиць часто полегшує сприйняття оператора SELECT, особливо, коли використовується природне з'єднання. Якщо не використовувати сполучені таблиці, то при виборі даних з декількох таблиць необхідно явно указувати умови з'єднання в розділі WHERE. Якщо при цьому користувач указує складні критерії відбору рядків, то в розділі WHERE змішуються семантично різні поняття - як умови зв'язку таблиць, так і умови відбору рядків (див. приклади 13, 14, 15 даного розділу).
Синтаксис умовних виразів розділу WHERE
Умовний вираз, що використовується в розділі WHERE оператора SELECT повинне обчислюватися для кожного рядка-кандидата, відбираного оператором SELECT. Умовний вираз може повертати одне з трьох значень істинності: TRUE, FALSE або UNKNOUN. Рядок-кандидат відбирається в результуючу множину рядків тільки в тому випадку, якщо для неї умовний вираз повернув значення TRUE.
Умовні вирази мають наступний синтаксис (в цілях спрощення викладу приведені не всі можливі предикати):
Умовний вираз ::=
[ ( ] [NOT]
{Предикат порівняння
| Предикат between
| Предикат in
| Предикат like
| Предикат null
| Предикат кількісного порівняння
| Предикат exist
| Предикат unique
| Предикат match
| Предикат overlaps}
[| OR}
[IS [NOT] {TRUE | FALSE | UNKNOWN}]
Предикат порівняння ::=
Конструктор значень рядка {= | | = | <>} Конструктор значень рядка
Приклад 33. Порівняння поля таблиці і скалярного значення:
POSTAV.VOLUME > 100
Приклад 34. Порівняння двох сконструйованих рядків:
(PD.PNUM, PD.DNUM) = (1, 25)
Цей приклад еквівалентний умовному виразу
PD.PNUM = 1 AND PD.DNUM = 25
Предикат between ::=
Конструктор значень рядка [NOT] BETWEEN
Конструктор значень рядка AND Конструктор значень рядка
Приклад 35. PD.VOLUME BETWEEN 10 AND 100
Предикат in ::=
Конструктор значень рядка [NOT] IN{(Select-вираз) | (Вираз для обчислення значення...)}
Приклад 36.
P.PNUM IN (SELECT PD.PNUM FROM PD WHERE PD.DNUM=2)
Приклад 37.
P.PNUM IN (1, 2, 3, 5)
Предикат like ::=
Вираз для обчислення значення рядка-пошуку [NOT] LIKEВираз для обчислення значення рядка-шаблона [ESCAPE Символ]
Зауваження. Предикат LIKE проводить пошук рядка-пошуку в рядку-шаблоні. В рядку-шаблоні дозволяється використовувати два трафаретні символи:
Символ підкреслення "_" може використовуватися замість будь-якого одиничного символу в рядку пошуку
Символ відсотка "%" може замінювати набір будь-яких символів в рядку пошуку (число символів в наборі може бути від 0 і більш).
Предикат null ::=
Конструктор значень рядка IS [NOT] NULL
Зауваження. Предикат NULL застосовується спеціально для перевірки, чи не рівний вираз null-значенню, що перевіряється.
Предикат кількісного порівняння ::=
Конструктор значень рядка {= | | = | <>}
{ANY | SOME | ALL} (Select-вираз)
Зауваження. Квантори ANY і SOME є синонімами і повністю взаємозамінні.
Зауваження. Якщо вказаний один з кванторів ANY і SOME, то предикат кількісного порівняння повертає TRUE, якщо порівнюване значення співпадає хоча б з одним значенням, що повертається в підзапиті (select-виразі).
Зауваження. Якщо вказаний квантор ALL, то предикат кількісного порівняння повертає TRUE, якщо порівнюване значення співпадає з кожним значенням, що повертається в підзапиті (select-виразі).
Приклад 38.
P.PNUM = SOME (SELECT PD.PNUM FROM PD WHERE PD.DNUM=2)
Предикат exist ::=
EXIST (Select-вираз)
Зауваження. Предикат EXIST повертає значення TRUE, якщо результат підзапиту (select-вирази) не порожній.
Предикат unique ::=
UNIQUE (Select-вираз)
Зауваження. Предикат UNIQUE повертає TRUE, якщо в результаті підзапиту (select-вирази) немає співпадаючих рядків.
Предикат match ::=
Конструктор значень рядка MATCH [UNIQUE]
[PARTIAL | FULL] (Select-вираз)
Зауваження. Предикат MATCH перевіряє, чи буде значення, визначене в конструкторі рядка співпадати із значенням будь-якого рядка, одержаного в результаті підзапиту.
Предикат overlaps ::=
Конструктор значень рядка OVERLAPS Конструктор значень рядка
Зауваження. Предикат OVERLAPS, є спеціалізованим предикатом, дозволяючому визначити, чи буде вказаний період часу перекривати інший період часу.
Порядок виконання оператора SELECT
Для того, щоб зрозуміти, як виходить результат виконання оператора SELECT, розглянемо концептуальну схему його виконання. Ця схема є саме концептуальною, оскільки гарантується, що результат буде таким, наче він виконувався крок за кроком відповідно до цієї схеми. Насправді, реально результат виходить більш витонченими алгоритмами, якими "володіє" конкретна СУБД.
Стадія 1. Виконання одиночного оператора SELECT
Якщо в операторі присутні ключові слова UNION, EXCEPT і INTERSECT, то запит розбивається на декілька незалежних запитів, кожний з яких виконується окремо:
Крок 1 (FROM). Обчислюється прямий декартовий добуток всіх таблиць, вказаних в обов'язковому розділі FROM. В результаті кроку 1 одержуємо таблицю А.
Крок 2 (WHERE). Якщо в операторі SELECT присутній розділ WHERE, то сканується таблиця А, одержана при виконанні кроку 1. При цьому для кожного рядка з таблиці А обчислюється умовний вираз, приведений в розділі WHERE. Тільки ті рядки, для яких умовний вираз повертає значення TRUE, включаються в результат. Якщо розділ WHERE опущений, то відразу переходимо до кроку 3. Якщо в умовному виразі беруть участь вкладені підзапити, то вони обчислюються відповідно до даної концептуальної схеми. В результаті кроку 2 одержуємо таблицю B.
Крок 3 (GROUP). Якщо в операторі SELECT присутній розділ GROUP, то рядки таблиці B, одержаної на другому кроці, групуються відповідно до списку угрупування, приведеного в розділі GROUP. Якщо розділ GROUP опущений, то відразу переходимо до кроку 4. В результаті кроку 3 одержуємо таблицю З.
Крок 4 (HAVING). Якщо в операторі SELECT присутній розділ HAVING, то групи, що не задовольняють умовному виразу, приведеному в розділі HAVING, виключаються. Якщо розділ HAVING опущений, то відразу переходимо до кроку 5. В результаті кроку 4 одержуємо таблицю D.
Крок 5 (SELECT). Кожна група, одержана на кроці 4, генерує один рядок результату таким чином. Обчислюються всі скалярні вирази, вказані в розділі SELECT. За правилами використовування розділу GROUP, такі скалярні вирази повинні бути однаковими для всіх рядків усередині кожної групи. Для кожної групи обчислюються значення агрегатних функцій, приведених в розділі SELECT. Якщо розділ GROUP був відсутній, але в розділі SELECT є агрегатні функції, то вважається, що є всього одна група. Якщо немає ні розділу GROUP, ні агрегатних функцій, то вважається, що є стільки груп, скільки рядків відібрано до даного моменту. В результаті кроку 5 одержуємо таблицю E, що містить стільки колонок, скільки елементів приведено в розділі SELECT і стільки рядків, скільки відібрано груп.
Стадія 2. Виконання операцій UNION, EXCEPT, INTERSECT
Якщо в операторі SELECT були присутні ключові слова UNION, EXCEPT і INTERSECT, то таблиці, одержані в результаті виконання 1-й стадії, об'єднуються, віднімаються або перетинаються.
Стадія 3. Впорядкування результату
Якщо в операторі SELECT присутній розділ ORDER, то рядки одержаної на попередніх кроках таблиці упорядковуються відповідно до списку впорядкування, приведеному в розділі ORDER.
Як насправді виконується оператор SELECT
Якщо уважно розглянути приведений вище концептуальний алгоритм обчислення результату оператора SELECT, то відразу зрозуміло, що виконувати його безпосередньо у такому вигляді надзвичайно накладний. Навіть на найпершому кроці, коли обчислюється декартовий твір таблиць, приведених в розділі FROM, може вийти таблиця величезних розмірів, причому практично більшість рядків і колонок з неї буде відкинута на наступних кроках.
Насправді в РСУБД є оптимізатор, функцією якого є знаходження такого оптимального алгоритму виконання запиту, який гарантує отримання правильного результату.
Схемно роботу оптимізатора можна представити у вигляді послідовності декількох кроків:
Крок 1 (Синтаксичний аналіз). Запит, що поступив, піддається синтаксичному аналізу. На цьому кроці визначається, чи правильно взагалі (з погляду синтаксису SQL) сформульований запит. В ході синтаксичного аналізу виробляється деяке внутрішньо представлення запиту, що використовується на подальших кроках.
Крок 2 (Перетворення в канонічну форму). Запит у внутрішньому уявленні піддається перетворенню в деяку канонічну форму. При перетворенні до канонічної форми використовуються як синтаксичні, так і семантичні перетворення. Синтаксичні перетворення (наприклад, приведення логічних виразів до кон’юнктивної або диз'юнктивної нормальної форми, заміна виразів "x AND NOT x" на "FALSE", і т.п.) дозволяють одержати нове внутрішньо представлення запиту, синтаксично еквівалентне початковому, але стандартне в деякому розумінні. Семантичні перетворення використовують додаткові знання, якими володіє система, наприклад, обмеження цілісності. В результаті семантичних перетворень виходить запит, синтаксично не еквівалентний початковому, але даючий той же самий результат.
Крок 3 (Генерація планів виконання запиту і вибір оптимального плану). На цьому кроці оптимізатор генерує безліч можливих планів виконання запиту. Кожний план будується як комбінація низькорівневих процедур доступу до даних з таблиць, методів з'єднання таблиць. Зі всіх планів, що згенерували, вибирається план, що володіє мінімальною вартістю. При цьому аналізуються дані про наявність індексів біля таблиць, статистичних даних про розподіл значень в таблицях, і т.п. Вартість плану це, як правило, сума вартостей виконання окремих низькорівневих процедур, які використовуються для його виконання. У вартість виконання окремої процедури можуть входити оцінки кількості звернень до дисків, ступінь завантаженості процесора і інші параметри.
Крок 4. (Виконання плану запиту). На цьому кроці план, вибраний на попередньому кроці, передається на реальне виконання.
Багато в чому якість конкретної СУБД визначається якістю її оптимізатора. Хороший оптимізатор може підвищити швидкість виконання запиту на декілька порядків. Якість оптимізатора визначається тим, які методи перетворень він може використовувати, якої статистичної і іншою інформацією про таблиці він розташовує, які методи для оцінки вартості виконання плану він знає.
Реалізація реляційної алгебри засобами оператора SELECT (Реляційна повнота SQL)
Для того, щоб показати, що мова SQL є реляційно повною, потрібно показати, що будь-який реляційний оператор може бути виражений засобами SQL. Насправді достатньо показати, що засобами SQL можна виразити будь-якого з примітивних реляційних операторів.
Оператор декартового добутку
Реляційна алгебра:
Оператор SQL:
SELECT A.Поле1, A.Поле2 ., B.Поле1, B.Поле2 .
FROM А, B;
або
SELECT A.Поле1, A.Поле2 ., B.Поле1, B.Поле2 .
FROM А CROSS JOIN B;
Оператор проекції
Реляційна алгебра:
Оператор SQL:
SELECT DISTINCT X, Y, _, Z
FROM А;
Оператор вибірки
Реляційна алгебра: ,
Оператор SQL:
SELECT *
FROM А
WHERE з;
Оператор об'єднання
Реляційна алгебра:
Оператор SQL:
SELECT *
FROM А
UNION
SELECT *
FROM B;
Оператор віднімання
Реляційна алгебра:
Оператор SQL:
SELECT *
FROM А
EXCEPT
SELECT *
FROM B
Реляційний оператор перейменування RENAME виражається за допомогою ключового слова AS в списку відбираних полів оператора SELECT. Таким чином, мова SQL є реляційно повною.
Решта операторів реляційної алгебри (з'єднання, перетин, поділ) виражається через примітивні, отже, можуть бути виражені операторами SQL. Проте, для практичних цілей приведемо їх.
Оператор з'єднання
Реляційна алгебра:
Оператор SQL:
SELECT A.Поле1, A.Поле2 ., B.Поле1, B.Поле2 .
FROM А, B
WHERE c;
або
SELECT A.Поле1, A.Поле2 ., B.Поле1, B.Поле2 .
FROM А CROSS JOIN B
WHERE c;
Оператор перетину
Реляційна алгебра:
Оператор SQL:
SELECT *
FROM А
INTERSECT
SELECT *
FROM B;
Оператор поділу
Реляційна алгебра:
Оператор SQL:
SELECT DISTINCT A.X
FROM А
WHERE NOT EXIST
(SELECT *
FROM B
WHERE NOT EXIST
(SELECT *
FROM А A1
WHERE
A1.X = A.X AND
A1.Y = B.Y));
Зауваження. Оператор SQL, що реалізовує розподіл відносин важко запам'ятати, тому дамо приклад еквівалентного перетворення виразів, що представляють суть запиту.
Хай відношення А містить дані про поставки деталей, відношення B містить список всіх деталей, які можуть поставлятися. Атрибут X є номером постачальника, атрибут Біля є номером деталі.
Розділити відношення А на відношення B означає в даному прикладі "відібрати номери постачальників, які поставляють всі деталі".
Перетворюваний текст виразу:
"Відібрати номери постачальників, які поставляють всі деталі" еквівалентно
"Відібрати ті номери постачальників з таблиці А, для яких не існує деталей, що не поставляються, в таблиці B" еквівалентно
"Відібрати ті номери постачальників з таблиці А, для яких не існує тих номерів деталей з таблиці B, які не поставляються цим постачальником" еквівалентно
"Відібрати ті номери постачальників з таблиці А, для яких не існує тих номерів деталей з таблиці B, для яких не існує записів про поставки в таблиці А для цього постачальника і цієї деталі".
Останній вираз дослівно перекладається на мову SQL. При перекладі виразу на мову SQL потрібно врахувати, що у внутрішньому підзапиті таблиця А повинна бути перейменована, для того, щоб відрізняти її від екземпляра цієї ж таблиці, що використовується в зовнішньому запиті.
Висновки
Фактично стандартною мовою доступу до баз даних в даний час стала мова SQL (Structured Query Language).
Мова SQL оперує термінами, дещо відмінними від термінів реляційної теорії, наприклад, замість "відносин" використовуються "таблиці", замість "кортежів" - "рядка", замість "атрибутів" - "колонки" або "стовпці".
Стандарт мови SQL, хоча і заснований на реляційній теорії, але в багатьох місцях відходить він її.
Основу мови SQL складають оператори, умовні розбиті не декілька груп по виконуваних функціях:
Оператори DDL (Data Definition Language) - оператори визначення об'єктів бази даних.
Оператори DML (Data Manipulation Language) - оператори маніпулювання даними.
Оператори захисту і управління даними, і ін.
Одним з основних операторів DML є оператор SELECT, що дозволяє витягувати дані з таблиць і одержувати відповіді на різні запити. Оператор SELECT містить в собі всі можливості реляційної алгебри. Це означає, що будь-який оператор реляційної алгебри може бути виражений за допомогою відповідного оператора SELECT. Цим доводиться реляційна повнота мови SQL.
Розрізняють концептуальну схему виконання оператора SELECT і фактичну схему його виконання. Концептуальна схема описує, в якій логічній послідовності повинні виконуватися операції, щоб одержати результат. При реальному виконанні оператора SELECT на перший план виступає досягнення максимальної швидкості виконання запиту. Для цього використовується оптимізатор, який, аналізуючи різні плани виконання запиту, вибирає якнайкращий з них.

Внимание, отключите Adblock

Вы посетили наш сайт со включенным блокировщиком рекламы!
Ссылка для скачивания станет доступной сразу после отключения Adblock!

Скачать