3.5. Побудова запиту: мови sql (structured query language), ddl (Data Definition Language), dml (Data Manipulation Language), dcl (Data Control Language), tcl (Transaction Control Language)

SQL (/ˌɛsˌkjuːˈɛl/ або інколи /ˈsiːkwəl/ "сіквел", англ. Structured query language — мова структурованих запитів) — декларативна мова програмування для взаємодії користувача з базами даних, що застосовується для формування запитів, оновлення і керування реляційними БД, створення схеми бази даних та її модифікації, системи контролю за доступом до бази даних. Сама по собі SQL не є ані системою керування базами даних, ані окремим програмним продуктом. На відміну від дійсних мов програмування (C або Pascal), SQL може формувати інтерактивні запити або, бувши вбудованою в прикладні програми, виступати як інструкції для керування даними. Окрім цього, стандарт SQL містить функції для визначення зміни, перевірки та захисту даних.

SQL — це діалогова мова програмування для здійснення запиту і внесення змін до бази даних, а також керування базами даних. Багато баз даних підтримує SQL з розширеннями до стандартної мови. Ядро SQL формує командна мова, яка дозволяє здійснювати пошук, вставку, оновлення і вилучення даних за допомогою використання системи керування і адміністративних функцій. SQL також включає CLI (Call Level Interface) для доступу і керування базами даних дистанційно.

Основу бази даних SQL Server утворює сервер або ядро бази даних.[джерело?] Ядро бази даних відповідає за обробку запитів, які надходять від клієнтів, і передачу відповідних результатів клієнтським компонентам.

Перша версія SQL була розроблена на початку 1970-х років у IBM. Ця версія мала назву SEQUEL і була призначена для обробки та пошуку даних, що містилися в реляційній базі даних IBM, System R. Мова SQL надалі була стандартизована Американськими Держстандартами (ANSI) в 1986. На початку SQL була запланована як мова запитів і управління даними, а пізніші модифікації SQL створені продавцями системи управління базами даних, які додали процедурні конструкції, control-of-flow команд і темпоральні розширення мов. З випуском стандарту SQL:1999 такі розширення були формально запозичені як частина мови SQL через Persistent Stored Modules (SQL/PSM).

Критика SQL включає відсутність крос-платформенності, невідповідну обробку відсутніх даних (дивіться Null (SQL)). Часто це неоднозначна граматика і семантика мови.

Мова SQL поділяється на кілька видів елементів:

• Пункти (диз'юнкти) (англ. Clauses), що є складовими частинами інструкцій та запитів. (Іноді вони не обов'язкові.)[3]

• Вирази (англ. Expressions), які можуть генерувати скалярні значення, або таблиці з стовпчиками і рядками даних

• Предикати (англ. Predicates), які описують умови, результатом яких є значення тризначної логіки SQL (true/false/unknown) або Булеві значення істинності і які використовуються для обмеження ефекту інструкцій та запитів, або для зміни потоку виконання програми.

• Запити (англ. Queries), які отримують дані на основі заданих критеріїв.

• Інструкції (англ. Statements), які чинять дію на схему даних чи самі дані, або контролюють транзакції, потік виконання програми, з'єднання, сесії, та виконують діагностику.

  • Інструкції SQL також включають крапку з комою («;») для позначення кінця інструкції. Хоча вона не є обов'язковою на кожній платформі, вона описується як стандартна частина граматики SQL.

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

SQL (Structured query language — мова структурованих запитів), складається з:

• DDL (Data Definition Language) — робота зі структурою бази,

• DML (Data Manipulation Language) — робота з рядочками,

• DCL (Data Control Language) — робота з правами,

• TCL (Transaction Control Language) — робота з транзакціями.

Data Definition Language (DDL) (Мова визначення даних) — це сімейство комп'ютерних мов, що використовуються в комп'ютерних програмах або користувачами баз даних для опису структури даних. Вперше термін було використано в моделі бази даних CODASYL, де структура бази була описана Data Definition Language та визначала записи, поля, та «набори» («sets»), що являють собою користувацьку модель даних. DDL мають свою функціональну здатність, організовану за початковим словом в заяві (запит), яке майже завжди є дієсловом. Дієслова:

• Create (Створити)

• Alter (Змінити)

• Drop (Видалити)

Data Manipulation Language (DML) (Мова маніпулювання даними) — це сімейство комп'ютерних мов, що використовуються в комп'ютерних програмах або користувачами баз даних для отримання, вставки, видалення або зміни даних в базах даних. Тепер найпопулярнішою мовою DML є SQL, що використовується для отримання і маніпулювання даними в реляційній базі даних. Інші форми DML використані в IMS/DL1, базах даних CODASYL (таких як IDMS), та інших. Мови DML спочатку використовувалися лише комп'ютерними програмами, але з появою SQL вони стали доступними і для людей. DML мають свою функціональну здатність, організовану за початковим словом в заяві (запит), яке майже завжди є дієсловом. У випадку з SQL ці дієслова:

• Select (Вибрати)

• Insert (Вставити)

• Update (Оновити/Модифікувати)

• Delete (Видалити)

Це перетворює природу мови в ряд обов'язкових тверджень (команд, запитів) до бази даних. Мови DML можуть істотно розрізнятися у різних виробників СУБД. Існує стандарт SQL, встановлений ANSI, але виробники СУБД часто пропонують свої власні «розширення» мови.

Мови DML розділяються в основному на два типи:

• Процедурні — описують дії над даними

• Декларативні — описують самі дані

Data Control Language (DCL) (Мова контролю даних) — комп'ютерна мова, також частина SQL, що використовуються в комп'ютерних програмах або користувачами баз даних для контролю доступу до даних в базах даних. DCL командами є:

• GRANT (надати) — дозволити визначеним користувачам виконувати визначені маніпуляції

• REVOKE (скасувати) — скасувати надані права

Наступні дії можуть бути GRANTED TO або ж REVOKED FROM користувача чи ролі: CONNECT, SELECT, INSERT, UPDATE, DELETE, USAGE

В Oracle виконання DCL команди створює примусовий неявний commit, тобто завершення транзакції. В PostgreSQL виконання DCL є частиною транзакції, що триває, та відповідно бути відкоченим (rolled back).

Transaction Control Language (TCL) (Мова керування транзакціями) — це сімейство комп'ютерних мов, що використовуються в комп'ютерних програмах або користувачами баз даних для керування транзакціями. Тепер найпопулярнішою мовою TCL є SQL, що використовується для отримання і маніпулювання даними в реляційній базі даних.

TCL у випадку з SQL включає в себе такі команди:

• Begin - служить визначенням початку транзакції;

• Commit - застосовує транзакцію;

• Rollback - відкочує всі зміни, зроблені у контексті поточної транзакції;

• Savepoint - встановлює проміжну точку збереження усередині транзакції.

3.6. Обробка запитів: основні операції реляційної алгебри: відбір (selection), проєкція (projection), об'єднання (union), перетин (intersection), різниця (difference), декартовий добуток (cartesian product), об'єднання за атрибутом (Join), ділення (Division)

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

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

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

Реляційна алгебра є мовою послідовного використання відношень, де усі кортежі, навіть узяті із різних відношень, обробляються однією командою без організації циклів. Для команд реляційної алгебри запропоновано кілька варіантів синтаксису. Далі ми скористаємося загальноприйнятими символічними позначеннями для цих команд і подамо їх у неформальному вигляді. Більш детально це питання для зацікавлених читачів розглянуто у роботах Ульмана (Ullman, 1988).

Існує декілька варіантів вибору операцій, які входять в реляційну алгебру. Спочатку Кодд запропонував вісім операторів, але згодом до них були додані ще деякі інші. П'ять основних операцій реляційної алгебри (рис. 5.1): вибірка (selection), проекція (projection), декартів добуток (cartesian product), об'єднання (union) і різниця (set difference) виконують більшість операцій витягу даних, які мають для нас інтерес. На базі п'яти основних операцій можна отримати додаткові (з'єднання (join), перетинання (intersection) і ділення (division) рис. 5.1).

Подібно до інших алгебр, деякі оператори є примітивними, а інші, будучи визначені через примітивні, є похідними від них. В реляційній алгебрі Кодда визначено такі шість примітивних операторів: вибірка, проєкція, декартів добуток, об'єднання та різниця і перейменування (насправді, Кодд відмовився від включення оператора перейменування, однак, розробники ISBL навели приклади необхідності його включення). Шість операторів є фундаментальними в тому сенсі, що жоден із них не можна відкинути без втрати потужності. Багато інших операторів було визначено комбінацією цих шести. Серед найважливіших можна назвати: перетин множин, ділення та природне об'єднання. Насправді ISBL дала підстави для заміни декартового добутку природним об'єднанням, окремим випадком якого є декартів добуток.

Операція вибірки (або обмеження) працює з одним відношенням R і визначає результуюче відношення, яке містить тільки ті кортежі (рядка) відношення R, які задовольняють заданій умові (предикату). Прикклад: Складіть список бібліотекарів, у яких табельний номер перевищує 80. σClockNumber > 80 (Librarians)

Тут вихідним є відношення Librarians, а предикатом – вираз ClockNumber>80. Операція вибірки визначає нове відношення, що містить тільки ті кортежі відношення Librarians, де значення атрибута ClockNumber перевищує 80 (табл. 5.1). Більш складні предикати можуть бути створені за допомогою логічних операторів AND, OR і NOT.

Операція проекції працює з одним відношенням R і визначає нове, яке має вертикальну підмножину відношення R, що створено за допомогою витягу значень указаних атрибутів та виключення з результату рядків-дублікатів. Прикклад: Складіть список бібліотекарів із зазначенням табельного номера (ClockNumber), прізвища (FamilyNamе), імені (Name), по батькові (Patronymic) та номера домашнього телефона (HomePhone). ПClockNumber, FamilyName, Name, Patronymic, HomePhone (R). У цьому прикладі операція проекції визначає нове відношення, яке буде містити тільки атрибути ClockNumber, FamilyNamе, Name, Patronymic і HomePhone відношення Librarians, розміщені в зазначеному порядку.

Операція об'єднання відношень R і S з кортежами I та J відбувається в результаті їх конкатенації з утворенням одного відношення з максимальною кількістю кортежів (I + J), якщо кортежі-дублікати виключені. При цьому відношення R і S мають бути сумісними за об'єднанням.

Об'єднання відношень можливо тільки у тому разі, коли збігаються їхні схеми, тобто коли вони мають однакову кількість атрибутів зі співпадаючими доменами (типами даних). Такі відношення є сумісними за об'єднанням. Відзначимо, що в деяких випадках для одержання двох сумісних за об'єднанням відношень може бути використана операція проекції. Прикклад: Наведіть прізвища всіх користувачів, занесених в БД. П_FamilyName(Readers) U П_FamilyName(Librarians). Для створення сумісних за об'єднанням відношень спочатку слід застосувати операцію проекції, щоб виділити з відношень Readers і Librarians стовпці з атрибутами FamilyName, виключаючи у разі потреби дублікати. Потім для комбінування отриманих проміжних відношень слід використовувати операцію об'єднання.

Операція перетинання визначає відношення, яке містить кортежі, які є, як у відношенні R, так і у відношенні S. Відношення R й S мають бути сумісними за об'єднанням. Операцію перетинання можна записати, використовуючи операції різниці між відношеннями: R ∩ S= R – (R – S). Прикклад: Створіть список бібліотекарів, які є одночасно читачами. Укажіть їх код паспорта, прізвище, ім’я та по батькові.

(П_{PasportCode, FamilyName, Name, Patronymic}(Readers)) ∩ (П_{PasportCode, FamilyName, Name, Patronymic(}Librarians))

Для виконання умови сумісності відношень за об’єднанням були виконані відповідні операції проекції. Дані, які входять до відношень «ЧИТАЧИ» та «БІБЛІОТЕКАРІ» свідчать, що результатом їх перетинання є порожня множина, тобто жоден з бібліотекарів не є читачем.

Різниця двох відношень R і S складається з кортежів, які є у відношенні R, але відсутні у відношенні S. Причому відношення R й S мають бути сумісними за об'єднанням. Прикклад: Визначіть індивідуальні коди читачів, які жодного разу не брали книжок у бібліотеці.

П_Code(Readers) – П_ReaderCode(BookGiveOutRecord)

У цьому випадку аналогічно попередньому прикладу слід створити сумісні за об'єднанням відношення Readers і BookGiveOutRecord, виконавши їх проекцію за атрибутами Code і ReaderCode. Далі слід використати операцію різниці.

Операція декартового добутку визначає нове відношення, яке є результатом конкатенації (зчеплення) кожного кортежу відношення R з кожним кортежем відношення S. Операції вибірки та проекції дозволяють витягти інформацію тільки з одного відношення. Можливе виникнення і таких ситуацій, коли потрібна деяка комбінація даних з декількох відношень. Операція декартового добутку помножує два відношення. У результаті утворюється інше відношення, де є усі можливі пари кортежів обох відношень. Отже, якщо одне відношення має I кортежів та N атрибутів, а інше – J кортежів і M атрибутів, то відношення з їх декартовим добутком буде містити (I × J) кортежів та (N + M) атрибутів. Вихідні відношення можуть мати атрибути з однаковими іменами. У такому випадку імена атрибутів будуть містити назви відношень у вигляді префіксів. Це забезпечить унікальність імен атрибутів у результуючому відношенні. Прикклад: Складіть список усіх читачів, які коли-небудь брали книжки у бібліотеці використовуючи такі атрибути відношення Readers: Code, FamilyName, Name та відношення BookGiveOutRecord: Code, ReaderCode, InventoryCode.

(П_{Code, FamilyName, Name} (Readers)) × (П_{Code, ReaderCode, InventoryCode}(BookGiveOutRecord))

Результатом виконання такої операції буде відношення, що містить 120 кортежів та 6 атрибутів. Наводити його у повному обсязі нема сенсу. Подивиться на його перші 12 кортежів (табл. 5.3). Перші 10 – усі можливі комбінації першого кортежу відношення Readers з десятьма кортежами відношення BookGiveOutRecord. Останні два наведені для того, щоб Ви порівняли їх значення з першими двома. Це перші дві можливі комбінації другого кортежу відношення Readers з першими двома кортежами відношення BookGiveOutRecord. Наступною буде третя можлива комбінація другого кортежу відношення Readers з третім кортежем відношення BookGiveOutRecord. І так до сто двадцятого кортежу.

Операції з'єднання. Як правило, користувачів цікавить лише деяка частина всіх комбінацій кортежів декартового добутку, яка задовольняє заданим умовам. Тому замість операції декартового добутку зазвичай використовується одна з найважливіших операцій реляційної алгебри – операція з'єднання. У результаті її виконання на базі двох вихідних відношень створюється нове. Операція з'єднання є похідною від операції декартового добутку. Вона еквівалентна операції вибірки з декартового добутку двох відношень тільки тих кортежів, які задовольняють умові, що наведена в предикаті з'єднання. Предикат з’єднання є еквівалентом. Реалізація операції з'єднання у реляційних СУБД – надскладне питання. У більшості випадків вона стає основною проблемою з підвищення продуктивності, що властива усім реляційним системам.

Є такі види операції з'єднання:

1. Тета-з'єднання (Θ-join).

2. З'єднання за еквівалентністю (equi-join) - окремий вид тета-з'єднання.

3. Натуральне з'єднання (natural join).

4. Зовнішнє з'єднання (outer join).

5. Напівз'єднання (semi-join).

Операція тета-з'єднання визначає відношення, яке містить кортежі з декартового добутку відношень R і S, що задовольняють предикату F. Предикат F має вигляд R.aiΘS.bi, де замість символу Θ може бути використаний один з операторів порівняння (<, <=,>, >=, =, ≈).

Операцію тета-з'єднання з урахуванням базових операцій вибірки й декартового добутку можна записати так: R►◄FS = σF(R × S). Як і у разі з операцією декартового добутку, степенем тета-з'єднання називається сума степеней операндів-відношень R і S. Якщо предикат F містить тільки оператор рівності (=), то це – з'єднання за еквівалентністю. Прикклад: Створіть список читачів, які коли-небудь брали книжки у бібліотеці. Щоб одержати цей список, використовують операції декартового добутку й вибірки. Однак такий самий результат можна одержати за допомогою операції з'єднання за еквівалентністю.

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

Степенем натурального з'єднання є сума степеней операндів-відношень R і S при відніманні кількості спільних атрибутів x. У прикладі з операцією тета-з'єднання для складання цього списку використовувалася операція з'єднання за еквівалентністю. У ньому були присутні два атрибути Readers.Code і ReaderCode, що містять коди читачів бібліотеки. Коли б вони мали однакове ім'я, наприклад ReaderCode, то для усунення одного з них можна скористатися операцією натурального з'єднання. Прикклад: Створіть список читачів, які коли-небудь брали книжки в бібліотеці.

Операція зовнішнього з'єднання. Найчастіше при з'єднанні двох відношень кортеж з одного відношення не знаходить відповідного кортежу в іншому відношенні. Інакше кажучи, в атрибутах, за якими відбувається операція з'єднання, виявляються незбіжні значення. Може знадобитися, щоб кортеж з одного або двох відношень був поданий у результаті з'єднання, навіть якщо у відповідних атрибутах немає співпадаючих значень. Ця мета може бути досягнута за допомогою операції зовнішнього з'єднання. Відсутні значення у результуючому відношенні позначаються за допомогою визначника NULL. Перевагою операції зовнішнього з'єднання є зберігання вихідної інформації: кортежі, які втрачаються при використанні інших типів з'єднань.

R⊃◄S Лівим зовнішнім з'єднанням називається операція, коли кортежі відношення R, які не мають співпадаючих значень у спільних стовпцях з відношенням S, також включаються в результуюче відношення. Прикклад: Визначить індивідуальні коди читачів, їх прізвища, ім’я, по батькові та інформацію про книжки, які вони брали у бібліотеці. Точно кажучи, у прикладі розглянута операція лівого (натурального) зовнішнього з'єднання, оскільки в результуючому відношенні присутні всі кортежі лівого відношення. Існує також праве зовнішнє з'єднання, назване так тому, що в результуючому відношенні присутні всі кортежі правого відношення. Крім того, існує й повне зовнішнє з'єднання, у результуючому відношенні якого містяться всі кортежі обох відношень, де для позначення незбіжних значень кортежів використовується визначник NULL.

Операція напівз'єднання визначає відношення, яке містить ті кортежі відношення R, які входять у з'єднання відношень R і S. Перевага операції напівз'єднання полягає в тому, що вона дозволяє зменшити число кортежів, які потрібно обробити для одержання з'єднання. Це особливо важливо при обчисленні з'єднань у розподілених системах. Операцію напівз'єднання можна сформулювати й за допомогою операторів проекції й з'єднання: R►_FS = П_A(R►◄_FS).

Тут A – набір усіх атрибутів у відношенні R. Насправді, це напівтета-з'єднання, причому слід зазначити, що існують напівз'єднання за еквівалентністю та напівнатуральне з'єднання. Прикклад: Створіть повний звіт, про всіх читачів на ім'я «Дмитро», які коли-небудь брали книжки в бібліотеці.

Операція ділення може бути корисною у запитах особливого типу, які досить часто виконуються до БД. Припустимо, що відношення R визначене на множині атрибутів A, а відношення S – на множені атрибутів B, причому B ⊆ A (тобто B є підмножиною A). Нехай C =A - B, тобто C є множиною атрибутів відношення R, яка не є атрибутами відношення S. Тоді визначення операциъ ділення буде таким:

R ÷ S Результатом операції ділення є набір кортежів відношення R, визначених на множині атрибутів C, які відповідають комбінаціям усіх кортежів відношення S.

Операція ділення еквівалентна низці інших основних операцій:

T1 = ПC(R)

T2 = ПC((S × T1) – R)

T = T1 – T2

Прикклад: Створіть список імен, прізвищ, по батькові та кодів паспортів читачів, які народилися після 1 грудня 1960 року.

Для вирішенню поставленого завдання спочатку необхідно отримати відношення R. Для цього виконана проекція відношення Readers (табл. 5.10). Потім визначити відношення S. Для цього зроблена проекція відношення PasportData. У ньому за допомогою оператора вибірки знайдені всі коди паспортів з датою народження після 31 грудня 1960 року (табл. 5.11). Тепер отримаємо результат ділення відношення R на S.

25