ТЕМА № 3
Мови запитів до реляційних баз даних

Історія SQL починається Р· 70-С… СЂРѕРєС–РІ XX століття, коли РІ дослідницькій лабораторії IBM Сѓ штаті Каліфорнія було розроблено першу версію цієї РјРѕРІРё. Назва SQL С” абревіатурою РІС–Рґ Structured Query Language (структурована РјРѕРІР° запитів), Р№ С–РЅРѕРґС– С—С— вимовляють СЏРє ««sequel» (первісна назва). Спочатку ця РјРѕРІР° була реа볭зована РІ реляційній СУБД DB2 виробництва IBM. РќР° РІС–РґРјС–РЅСѓ РІС–Рґ РјРѕРІ третього покоління (COBOL, C), СЏРєС– Р·'явилися РІ той самий час, РјРѕРІР° SQL РЅРµ С” процедурною. Непроцедурна РјРѕРІР° — це РјРѕРІР°, РІ СЏРєС–Р№ описується, що потрібно одержати, Р° РЅРµ СЏРє це зробити.

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

Американський інститут національних стандартів (American National Stan­dards Institute - ANSI) та Міжнародна організація стандартів (International Standards Organization — ISO) займаються РѕРїРёСЃРѕРј С– підтримкою стандартів цієї РјРѕРІРё. РЈСЃС– сучасні СУБД підтримують певний стандарт, проте С” Р№ відхилення, СЏРєС– РІ РєРѕР¶­РЅРѕРјСѓ конкретному випадку специфікуються РІ документації програмного РїСЂРѕРґСѓРє­С‚Сѓ. РћРєСЂС–Рј того, Сѓ багатьох системах розроблено розширення SQL, що дають Р·РјРѕРіСѓ використовувати РјРѕРІСѓ запитів Сѓ середовищі програмування.

SQL надає такі можливості:

·         створювати Р№ видаляти таблиці бази даних, Р° також змінювати заголовки таблиць;

·         вставляти, змінювати Р№ видаляти СЂСЏРґРєРё РІ таблицях;

·         виконувати пошук даних Сѓ багатьох таблицях та впорядковувати результати цього пошуку;

·         описувати процедури підтримки цілісності;

·         визначати та змінювати інформацію РїСЂРѕ захист даних.

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

ФАКУЛЬТЕТ(#F, Назва, Декан. Корпус. Фонд)

КАФЕДРА (#D, #F. Назва. #ЗАВІДУВАЧ. Корпус. Фонд)

ВИКЛАДАЧ(#Т. #D. Прізвище. Посада. Тел)

ГРУПА(#G. #D. Курс. Номер. Кількість, #КУРАТОР)

ПРЕДМЕТ (#S, Назва)

АУДИТОРІЯ (#R,. Номер. Корпус. Місткість)

ЛЕКЦІЯ(#Т. #G. #S. #R. Тип. День. Тиждень)

Засоби пошуку даних

Основні конструкції мови, призначені для вибирання даних

Основна конструкція, призначена у мові SQL для вибирання даних, складається з фраз SELECT і FROM. Фраза FROM вказує, з якої таблиці потрібно вибрати дані, а фраза SELECT — які саме атрибути (стовпці) з цієї таблиці мають бути вибрані. Запит

SELECT  Назва

FROM   ФАКУЛЬТЕТ

здійснює виведення назв факультетів. Ці дві фрази обов'язково мають бути в будь-якому запиті.

Виведення окремих стовпців

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

SELECT     Номер, РљСѓСЂСЃ, Кількість

FROM         ГРУПА

Виведення всіх стовпців

Якщо необхідно вивести всі стовпці таблиці, то у фразі SELECT використовується

СЃРёРјРІРѕР» *:

SELECT     *

FROM   КАФЕДРА

Неповторювані рядки

Хоча РІ реляційних відношеннях РЅРµ має бути повторюваних СЂСЏРґРєС–РІ (дублікатів), Сѓ SQL Р·Р° замовчуванням встановлено, що РІСЃС– дублікати СЂСЏРґРєС–РІ Сѓ таблиці-результаті виводяться. Щоб унаслідок виконання запиту одержати унікальні (неповторювані) значення, потрібно використовувати модифікатор DISTINCT (Р·Р° замовчу­РІР°РЅРЅСЏРј застосовується модифікатор ALL). Наприклад, щоб отримати СЃРїРёСЃРѕРє СѓСЃС–С… типів лекцій, СЏРєС– читаються Сѓ РІСѓР·С–, С– щоб кожен тип РІРёРІРѕРґРёРІСЃСЏ лише РѕРґРёРЅ раз, потрібно записати:

SELECT DISTINCT                     РўРёРї

FROM                                            ЛЕКЦІЯ

Без модифікатора DISTINCT ми одержали б список із кількох сотень рядків (його довжина дорівнювала б кількості всіх лекцій у вузі).

Зазначимо, що весь запит можна розмістити в одному рядку.

Перевизначення імен стовпців

Фраза SELECT надає можливість перевизначити імена стовпців кінцевої таблиці. Для цього після імені стовпця РІРёС…С–РґРЅРѕС— таблиці необхідно зазначити С–Рј'СЏ стовпця кінцевої таблиці (Р· використанням необов'СЏР·РєРѕРІРѕС— фрази AS). Наприклад, Сѓ наве­РґРµРЅРѕРјСѓ нижче запиті перевизначаються імена РѕР±РѕС… стовпців:

SELECT     Назва AS Назва_факультету, Декан AS Декан_факультету

FROM       ФАКУЛЬТЕТ

Умова вибирання

Для запису умови вибирання використовується фраза WHERE. У ній зазначено, якій умові мають відповідати вихідні дані. Алгоритм обробки запиту з фразою WHERE є таким:

♦       вибрати СЂСЏРґРѕРє С–Р· таблиці;

♦       перевірити Р№РѕРіРѕ відповідність вказаній СѓРјРѕРІС–;

♦      якщо СЂСЏРґРѕРє відповідає СѓРјРѕРІС–, то вивести значення стовпців, вказаних Сѓ фразі SELECT.

Цей запит виводить список усіх професорів вузу:

SELECT     Прізвище

FROM         ВИКЛАДАЧ

WHERE      Посада = "професор"

Вирази, умови та оператори

У мові SQL існує багато різновидів виразів, у яких використовуються дані різних типів - рядки, числа, логічні значення. Конструкція SELECT...FROM...WHERE також є виразом, не кажучи вже про просте згадування імені стовпця у фразі SELECT.

РЈРјРѕРІР° - це вираз, що повертає логічне значення - TRUE чи FALSE. РЈРјРѕРІРЅС– РІРёСЂР°­Р·Рё РѕР±РѕРІ'СЏР·РєРѕРІРѕ використовуються Сѓ фразі WHERE, Р° також можуть застосовуватися РІ інших фразах, наприклад SELECT. Прикладом СѓРјРѕРІРЅРѕРіРѕ виразу С” конструкція Посада = <професор>.

Оператори — це конструкції, що використовуються Сѓ виразах для означення певних РґС–Р№ над даними. Р„ кілька типів операторів: арифметичні, логічні, теоретико-множинні, оператори РїРѕСЂС–РІРЅСЏРЅРЅСЏ, оператори над рядками. РЈ конкретних СЃРёСЃ­С‚емах СЃРїРёСЃРєРё цих операторів можуть відрізнятися, зокрема бути ширшими, РЅС–Р¶ наведений далі СЃРїРёСЃРѕРє

♦      Арифметичні оператори: +, -, *, /.

♦      Оператор зчеплення СЂСЏРґРєС–РІ ||.

♦      Теоретико-множинні оператори: UNION, INTERSECT, EXCEPT (або MINUS).

♦      Логічні оператори: AND, OR, NOT.

♦      Оператори РїРѕСЂС–РІРЅСЏРЅРЅСЏ: >, <, -, <>, >=, <-.

Оператори порівняння, їхнє призначення та приклади використання наведені в табл. 4.1.

Таблиця 4.1. Оператори порівняння

Оператор	Призначення	Приклад
=	Перевірка на рівність	SELECT * FROM КАФЕДРА WHERE ФОНД=1500
!=, ^=,<>,¬=	Перевірка РЅР° нерівність	SELECT * FROM КАФЕДРА WHERE ФОНД!=1500
>,<	Перевірка, чи одне значення більше або менше іншого	SELECT * FROM КАФЕДРА WHERE ФОНД>1500 SELECT * FROM КАФЕДРА WHERE ФОНД<1500
>=,<=	Перевірка, чи одне значення не менше або не більше іншого	SELECT * FROM ФАКУЛЬТЕТ WHERE ФОНД>=1500 SELECT * FROM ФАКУЛЬТЕТ WHERE ФОНД<=1500
IN	Перевірка РЅР° належність елементу множині	SELECT * FROM ВИКЛАДАЧ WHERE ПОСАДА IN («РџР РћР¤Р•РЎРћР », «Р”ОЦЕНТ») SELECT * FROM ВИКЛАДАЧ WHERE ПОСАДА IN (SELECT ПОСАДА FROM ВИКЛАДАЧ WHERE tel=”123456”)
NOT IN	Еквівалентний !=ALL. Перевірка РЅР° неналежність елементу множині	SELECT * FROM ВИКЛАДАЧ WHERE ПОСАДА NOT IN («РџР РћР¤Р•РЎРћР », «Р”ОЦЕНТ») SELECT * FROM ВИКЛАДАЧ WHERE ПОСАДА NOT IN (SELECT ПОСАДА FROM ВИКЛАДАЧ WHERE tel=”123456”)
ANY, SOME	Використовуються разом із предикатом =, !=, >,<, <= або >=. Перевіряють, чи істинний цей предикат хоча б для одного елементу множини, заданої у правій частині оператора, відносно елементу, заданого в лівій частині	SELECT * FROM ФАКУЛЬТЕТ WHERE ФОНД= ANY (SELECT ФОНД FROM ФАКУЛЬТЕТ WHERE КОРПУС=7)
ALL	Використовується разом із предикатом =, !=, >,<, <= або >=. Перевіряють, чи істинний цей предикат для всіх елементів множини, заданої у правій частині оператора, відносно елементу, заданого в лівій частині	SELECT * FROM ФАКУЛЬТЕТ WHERE ФОНД>=ALL (1400.3000)
EXISTS	Повертає TRUE, якщо під запит, до якого застосовується оператор, містить хоча б один рядок	SELECT НАЗВА FROM ФАКУЛЬТЕТ WHERE EXISTS (SELECT * FROM КАФЕДРА WHERE КАФЕДРА.#F= ФАКУЛЬТЕТ.#F)
IS [NOT] NUUL	Перевірка на рівність значенню NUUL	SELECT НАЗВА FROM КАФЕДРА WHERE ФОНД IS NULL

Розглянемо на прикладах використання цих операторів.

Визначити групи, де кількість студентів становить від 40 до 50 осіб, а також курси, які їм відповідають.

SELECT  Номер. РљСѓСЂСЃ

FROM   ГРУПА

WHERE       Кількість >= 40 AND Кількість <= 50

Визначити прізвища й посади професорів та асистентів.

SELECT  Прізвище. Посада

FROM   ВИКЛАДАЧ

WHERE  Посада = "професор" OR Посада = "асистент"

Вибирання з кількох таблиць

Щоб вибрати дані з багатьох таблиць, необхідно перелічити їхні імена у фразі FROM. Якщо у фразі WHERE не зазначено умову з'єднання таблиць, то обчислюється декартів добуток усіх таблиць із фрази FROM. Наприклад, задано дві таблиці

Table 1
ROW	REMARK
row1	table1
row2	table1
row3	table1

Table 2
ROW	REMARK
row1	table2
row2	table2
row3	table2

SELECT     *

FROM         TABLE1.  TABLE2

буде таблиця

 

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

SELECT     ФАКУЛЬТЕТ.Назва. КАФЕДРА.Назва

FROM         ФАКУЛЬТЕТ.  КАФЕДРА

WHERE       ФАКУЛЬТЕТ.#F = КАФЕДРА.#F

Уточнення імен стовпців

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

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

Таблиці можуть мати довгі назви, тому мова надає можливість зв'язувати з кожною таблицею певний псевдонім і надалі посилатися на таблицю за ним. Зіставлення таблиці з псевдонімом здійснюється у фразі FROM. Наприклад, попередній запит можна записати в такий спосіб:

SELECT  f.Назва, d.Назва

FROM   ФАКУЛЬТЕТ f. КАФЕДРА d

WHERE   f.#F - d.#F

Фраза WHERE може використовуватися для зазначення способу з'єднання таблиць або умови відбору рядків до кінцевої таблиці. Зокрема, запит

SELECT  КАФЕДРА.Назва

FROM   КАФЕДРА. ВИКЛАДАЧ

WHERE   КАФЕДРА.#D = ВИКЛАДАЧ.#D

AND ВИКЛАДАЧ.Прізвище = "Іванов"

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

Стовпці, що обчислюються

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

SELECT    d.Назва. d.Фонд, (d.Фонд / f.Фонд) * 100

FROM         ФАКУЛЬТЕТ f, КАФЕДРА d

WHERE       f.#F = d.#F AND F.Назва = "інформатики"

Еквіз' єднання

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

SELECT     f.*, d.*

FROM   ФАКУЛЬТЕТ f, КАФЕДРА d

WHERE   f.#F = d.#F

Природне з'єднання

Операція природного з'єднання здійснюється з'єднанням двох чи кількох таблиць за рівністю значень в одній чи кількох парах стовпців із наступним видаленням повторюваних стовпців (необхідні стовпці вказуються у фразі SELECT). Наприклад:

SELECT     f.#F, f.Назва, f.Декан. f.РљРѕСЂРїСѓСЃ, f.Фонд, d.#D, d.Назва. d.#ЗАВІДУВАЧ, d.РљРѕСЂРїСѓСЃ, d.Фонд

FROM         ФАКУЛЬТЕТ f, КАФЕДРА d

WHERE       f.#F = d.#F

Хоча стовпці Назва, РљРѕСЂРїСѓСЃ, Фонд С” РІ РѕР±РѕС… таблицях, однак РІРѕРЅРё РЅРµ розглядають­СЃСЏ СЏРє повторювані, оскільки мають СЂС–Р·РЅРµ семантичне навантаження. Стовпцем, СЏРєРёР№ повторюється, С” той єдиний, Р·Р° СЏРєРёРј виконується Р·'єднання, — стовпець #F, що РІРёРєРѕРЅСѓС” роль зовнішнього ключа РІ таблиці КАФЕДРА.

θ-з'єднання

Це з'єднання за будь-якою умовою. θ-з'єднання виконується не за первинним і зовнішнім ключами, а за іншими стовпцями. Раніше наведені різновиди з'єднання є окремими випадками θ-з'єднання.

З'єднання таблиці зі своєю копією

Інколи необхідно Р·'єднати таблицю С–Р· самою СЃРѕР±РѕСЋ. Для цього Сѓ фразі FROM РїРѕ­С‚СЂС–Р±РЅРѕ двічі зазначити назву таблиці Р· СЂС–Р·РЅРёРјРё псевдонімами, щоб можна було

на кожен п екземпляр посилатися окремо. Розглянемо такий приклад. Необхідно перевірити, чи є в таблиці ФАКУЛЬТЕТ пари рядків, де назви факультетів збігаються, а ключі #F різні.

SELECT     fl.Назва,  fl.#F,  f2.#F

FROM         ФАКУЛЬТЕТ fl, ФАКУЛЬТЕТ f2

WHERE       fl.Назва = f2.Назва AND fl.#F != f2.#F

З'єднання можна виконувати безпосередньо у фразі FROM, скориставшись одним із різновидів оператора JOIN (наприклад, Fl INNER JOIN F2 ON F1.F# = F2.F#). Слід пам'ятати, що за певних обставин потрібно саме виконувати JOIN-з'єднання, а не обчислювати декартів добуток таблиць. Наприклад, декартів добуток двох таблиць з десятками тисяч рядків у кожній обчислюватиметься протягом кількох годин, а обсяг кінцевої таблиці буде вимірюватися сотнями гігабайтів!

Розглянемо кілька запитів. Запити будемо записувати як мовою SQL, так і за допомогою реляційної алгебри.

Запит 1

Визначити всі кафедри факультету інформатики.

 РњРѕРІР° SQL:

SELECT  КАФЕДРА.Назва

FROM   ФАКУЛЬТЕТ. КАФЕДРА

WHERE  ФАКУЛЬТЕТ.#F = КАФЕДРА.#F AND ФАКУЛЬТЕТ.Назва = "інформатики"

Реляційна алгебра:

((ФАКУЛЬТЕТ[#F = #F]KAФEДPA)[ФАКУЛЬТЕТ.Назва = "інформатики"])[КАФЕДРА.Назва]

Запит 2

Визначити всіх викладачів кафедри КСМ та їхні телефонні номери.

РњРѕРІР° SQL:

SELECT Прізвище, Тел

FROM   КАФЕДРА, ВИКЛАДАЧ

WHERE  КАФЕДРА.#D = ВИКЛАДАЧ.#D AND КАФЕДРА.Назва = «РљРЎРњ»

Реляційна алгебра:

((КАФЕДРА[#F = #D]ВИКЛАДАЧ)[КАФЕДРА.Назва = "КСМ"])[ВИКЛАДАЧ.Прізвище. Тел]

Запит 3

Вивести список усіх викладачів факультету інформатики разом з їхніми телефонними номерами.

РњРѕРІР° SQL:

SELECT  Прізвище, Тел

FROM   ФАКУЛЬТЕТ, КАФЕДРА, ВИКЛАДАЧ

WHERE  ФАКУЛЬТЕТ.#F = КАФЕДРА.#F AND КАФЕДРА.#D - ВИКЛАДАЧ.#D AND ФАКУЛЬТЕТ.Назва = "інформатики"

Реляційна алгебра:

(((ФАКУЛЬТЕТ[#F=#F]KAФЕДPA)[#D=#D]ВИКЛАДАЧ)

[ФАКУЛЬТЕТ.Назва = "інформатики"])

[ВИКЛАДАЧ.Прізвище, Тел]

Запит 4

Визначити номери груп першого курсу кафедри КСМ.

РњРѕРІР° SQL

SELECT     Номер

FROM         ГРУПА, КАФЕДРА

WHERE       ГРУПА.#D = КАФЕДРА.#D AND Назва = «РљРЎРњ» AND РљРЈР РЎ = 1

Реляційна алгебра:

((ГРУПА[#D = #D]КАФЕДРА)[Назва = "КСМ" & Курс = 1])[Номер, Прізвище]

Запит 5

Визначити номери груп першого курсу кафедри КСМ та прізвища їхніх кураторів.

РњРѕРІР° SQL

SELECT     Номер, Прізвище

FROM         ГРУПА, КАФЕДРА, ВИКЛАДАЧ

WHERE       ГРУПА.#D = КАФЕДРА.#D AND ГРУПА.#РљРЈР РђРўРћР  = ВИКЛАДАЧ.#Рў AND Назва = "РљРЎРњ" AND РљСѓСЂСЃ = 1

Реляційна алгебра:

(((ГРУПА [#D= #D]КАФЕДРА)[#КУРАТОР = #Т]ВИКЛАДАЧ)[Назва = "КСМ" & Курс= 1])[Номер, Прізвище]

Запит 6

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

РњРѕРІР° SQL

SELECT     АУДИТОРІЯ.Номер, ГРУПА.Номер, ПРЕДМЕТ.Назва, Тиждень. День

FROM         ЛЕКЦІЯ,  ГРУПА,  ПРЕДМЕТ, АУДИТОРІЯ

WHERE       ЛЕКЦІЯ.#G = ГРУПА.#G AND

ЛЕКЦІЯ.#S = ПРЕДМЕТ.#S AND

ЛЕКЦІЯ.#R = АУДИТОРІЯ.#R AND

ГРУПА.Кількість > АУДИТОРІЯ.Місткість

Реляційна алгебра:

((((ЛЕКЦІЯ #G= #G]ГРУПА)[#S = #$]ПРЕДМЕТ)[#R = #R] АУДИТОРІЯ) [ГРУПА.Кількість > АУДИТОРІЯ.Місткість])[АУДИТОРІЯ.Номер, ГРУПА.Номер, ПРЕДМЕТ.Назва, Тиж