ТЕМА № 3
Мови запитів до реляційних баз даних
Історія SQL
починається з 70-х років XX століття, коли в дослідницькій
лабораторії IBM у штаті Каліфорнія було розроблено першу версію
цієї мови. Назва SQL є абревіатурою від Structured Query Language
(структурована РјРѕРІР° запитів), Р№ С–РЅРѕРґС– С—С— вимовляють СЏРє ««sequel»
(первісна назва). Спочатку
ця мова була реалізована
в реляційній СУБД DB2 виробництва IBM. На відміну від мов третього покоління (COBOL, C), які з'явилися в той самий час, мова
SQL РЅРµ С”
процедурною. Непроцедурна мова — це
мова, в якій описується, що потрібно одержати, а не як це зробити.
Особливість реляційних СУБД полягає у
тому, що вони надають множинно-орієнтовану мову маніпулювання базами даних,
тобто результатом дії мовного оператора є таблиця, яка містить множину даних.
Більшість сучасних реляційних СУБД використовують саме мову SQL.
Американський інститут національних
стандартів (American National Standards 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. Прикладом
умовного виразу є конструкція Посада = <професор>.
Оператори — це конструкції, що використовуються у
виразах для означення певних дій над даними. Є кілька типів операторів:
арифметичні, логічні, теоретико-множинні,
оператори порівняння, оператори над рядками. У конкретних системах списки цих
операторів можуть відрізнятися, зокрема бути ширшими, ніж наведений далі список
♦
Арифметичні
оператори: +, -, *, /.
♦
Оператор
зчеплення рядків ||.
♦
Теоретико-множинні
оператори:
♦ Логічні оператори: 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=” |
NOT IN |
Еквівалентний
!=ALL. Перевірка
на неналежність елементу множині |
SELECT * FROM Р’РКЛАДАЧ WHERE ПОСАДА NOT IN («РџР ОФЕСОР», «Р”ОЦЕНТ») SELECT * FROM Р’РКЛАДАЧ WHERE ПОСАДА NOT IN
(SELECT ПОСАДА
FROM Р’РКЛАДАЧ
WHERE tel=” |
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
буде таблиця
Table1 .ROW |
Table1.REMARK |
Table2.ROW |
Table2.REMARK |
row1 |
table1 |
row1 |
table2 |
row1 |
table1 |
row2 |
table2 |
row1 |
table1 |
row3 |
table2 |
row2 |
table1 |
row1 |
table2 |
row2 |
table1 |
row2 |
table2 |
row2 |
table1 |
row3 |
table2 |
row3 |
table1 |
row1 |
table2 |
row3 |
table1 |
row2 |
table2 |
row3 |
table1 |
row3 |
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] РђРЈР”РТОРІЯ) [ГРУПА.Кількість >
РђРЈР”РТОРІЯ.Місткість])[РђРЈР”РТОРІЯ.Номер, ГРУПА.Номер, ПРЕДМЕТ.Назва, РўРёР¶