Если у вас возникли сложности с курсовой, контрольной, дипломной, рефератом, отчетом по практике, научно-исследовательской и любой другой работой - мы готовы помочь.
Предоплата всего
Подписываем
Григорович В.Г. SQL: Команди опрацювання даних
Дрогобицький державний педагогічний університет імені Івана Франка
Григорович В.Г.
SQL: Команди опрацювання даних
Навчально-методичний посібник
для студентів спеціальності „Інформатика”
Дрогобич 2004
УДК
Григорович В.Г. SQL: Команди опрацювання даних. Навчально-методичний посібник для студентів спеціальності „Інформатика”. Дрогобич, ДДПУ. – 2004. –
148 с.
Навчальний посібник написано відповідно до програм навчальних дисциплін „Автоматизовані інформаційні системи” та „Бази даних” для підготовки фахівців освітньо-кваліфікаційного рівня „Бакалавр” спеціальності „Інформатика”, затверджених Вченою радою Дрогобицького державного педагогічного університету імені Івана Франка.
У посібнику вміщено матеріали для лекцій та практичних занять із розділів, пов’язаних з опрацюванням баз даних клієнт-серверної архітектури, створенням робочого місця клієнта. Основні положення кожної теми розкриваються через численні приклади. Запропонована у посібнику система питань та вправ підібрана відповідно до вимог програми з метою забезпечити високий рівень сформованості знань, умінь і навичок студентів.
Бібліографія 7 назв.
Рекомендовано до друку Вченою радою Дрогобицького державного педагогічного університету імені Івана Франка
(протокол № 5 від 20 „травня” 2004 р.)
Відповідальний за випуск: Григорович Віктор Геннадійович
Редактор: Невмержицька Ірина Михайлівна
Рецензенти: Пасічник Володимир Володимирович, професор, доктор технічних наук, завідувач кафедри „Інформаційні системи та мережі” Національного університету „Львівська політехніка”;
Дорошенко Микола Васильович, доцент, кандидат фізико-математичних наук, доцент кафедри інформатики та обчислювальної математики Дрогобицького державного педагогічного університету імені Івана Франка.
Зміст
|
[1]
[2] [2.1] 1. Самий простий вигляд оператора SELECT [2.2] 2. Використання секції WHERE [2.2.1] 2.1. Порівняння значення стовпчика із константою [2.2.2] 2.2. Правила виконання однотабличних запитів на вибірку [2.3] 3. Багатотабличні запити [2.3.1] 3.1. Правила виконання багатотабличних запитів на вибірку [2.4] 4. Використання псевдонімів таблиць [2.5] 5. Секція ORDER BY – визначення порядку сортування [2.6] 6. Розрахунок значень обчислювальних стовпчиків. Призначення стовпчику альтернативного імені [2.7] 7. Агрегатні функції [2.7.1] 7.1. Опрацювання унікальних записів агрегатними функціями [2.7.2] 7.2. Агрегатні функції і значення NULL [2.8] 8. Групування записів [2.8.1] 8.1. Правила виконання SQL–запиту на вибірку (з врахуванням секції GROUP BY) [2.8.2] 8.2. Кілька стовпчиків групування [2.8.3] 8.3. Обмеження на запити з групуванням [2.8.4] 8.4. Значення NULL в стовпчиках групування [2.9] 9. Секція HAVING – умова відбору груп [2.9.1] 9.1. Правила виконання SQL–запиту на вибірку (з врахуванням секції HAVING) [2.9.2] 9.2. Обмеження на умову відбору груп [2.9.3] 9.3. Значення NULL і умови відбору груп [2.9.4] 9.4. Секція HAVING без секції GROUP BY [2.10] 10. Складні умови відбору [2.10.1] 10.1. Використання логічних виразів [2.10.2] 10.2. Порівняння [2.10.3] 10.3. Перевірка на належність діапазону значень (BETWEEN…AND…) [2.10.4] 10.4. Перевірка на належність множині значень (IN) [2.10.5] 10.5. Перевірка на рівність значенню NULL (IS NULL) [2.10.6] 10.6. Перевірки літерних значень. [2.10.6.1] 10.6.1. Перевірка, чи літерний рядок починається з певного значення (STARTING WITH) [2.10.6.2] 10.6.2. Перевірка, чи літерний рядок містить певне значення (CONTAINING) [2.10.6.3] 10.6.3. Перевірка на відповідність шаблону (LIKE) [2.10.7] 10.7. Перетворення даних [2.10.7.1] 10.7.1. Типи даних [2.10.7.2] 10.7.2. Функції [2.10.7.3] 10.7.3. Використання функцій UPPER та LOWER [2.10.7.4] 10.7.4. Використання функції CAST [2.10.7.5] 10.7.5. Використання операції склеювання літерних рядків || [2.11] 11. Запити на об’єднання: об’єднання результатів кількох запитів (операція UNION) [2.11.1] 11.1. Запити на об’єднання і рядки, що повторюються [2.11.2] 11.2. Запити на об’єднання і сортування [2.11.3] 11.3. Вкладені запити на об’єднання [2.11.4] 11.4. Виконання запитів на об’єднання [2.12] 12. Багатотабличні запити на вибірку (з’єднання) [2.12.1] 12.1. Приклад двотабличного запиту. [2.12.2] 12.2. Просте з’єднання таблиць (з’єднання за рівністю) [2.12.2.1] 12.2.1. Запити з використанням відношення „головна – підлегла” таблиці (предок – нащадок) [2.12.2.2] 12.2.2. Умова для відбору рядків [2.12.2.3] 12.2.3. Кілька зв’язаних стовпчиків [2.12.2.4] 12.2.4. Запити на вибірку до трьох і більше таблиць [2.12.2.5] 12.2.5. Інші з’єднання таблиць за рівністю [2.12.3] 12.3. - з’єднання. З’єднання таблиць за нерівністю [2.12.4] 12.4. Особливості багатотабличних запитів [2.12.4.1] 12.4.1. Повні імена стовпчиків [2.12.4.2] 12.4.2. Вибірка всіх стовпчиків [2.12.4.3] 12.4.3. Самоз’єднання [2.12.4.4] 12.4.4. Псевдоніми таблиць [2.12.5] 12.5. Ефективність опрацювання багатотабличних запитів [2.12.6] 12.6. Внутрішня структура з’єднання таблиць [2.12.6.1] 12.6.1. Декартовий добуток таблиць [2.12.6.2] 12.6.2. Вибірка (вилучення тих рядків, які не задовольняють умові відбору) [2.12.7] 12.7. Зовнішнє з’єднання таблиць [2.12.7.1] 12.7.1. Повне зовнішнє з’єднання [2.12.7.2] 12.7.2. Ліве і праве зовнішнє з’єднання [2.12.7.3] 12.7.3. Системи запису зовнішнього з’єднання [2.12.8] 12.8. З’єднання і стандарт SQL2 [2.12.8.1] 12.8.1. Внутрішні з’єднання в стандарті SQL2 [2.12.8.2] 12.8.2. Зовнішні з’єднання в стандарті SQL2 [2.12.8.3] 12.8.3. Перехресні з’єднання і розширені запити на з’єднання в SQL2 [2.12.8.4] 12.8.4. Багатотабличні з’єднання в стандарті SQL2. [2.13] 13. Підлеглі запити на вибірку [2.13.1] 13.1. Застосування підлеглих запитів [2.13.1.1] 13.1.1. Що таке підлеглий запит? [2.13.1.2] 13.1.2. Підлеглі запити в секції WHERE [2.13.1.3] 13.1.3. Зовнішні звертання [2.13.2] 13.2. Умови відбору з підлеглими запитами [2.13.2.1] 13.2.1. Порівняння з результатом підлеглого запиту [2.13.2.2] 13.2.2. Перевірка на належність множині результатів підлеглого запиту [2.13.2.3] 13.2.3. Перевірка на існування (предикат EXISTS) [2.13.2.4] 13.2.4. Багатократне порівняння (предикати ANY та ALL) [2.13.3] 13.3. Підлеглі запити і з’єднання [2.13.4] 13.4. Рівні вкладеності запитів [2.13.5] 13.5. Корельовані підлеглі запити [2.13.6] 13.6. Підлеглі запити в секції HAVING [2.13.7] 13.7. Підсумки. Ефективність підлеглих запитів
[3] [3.1] 1. Внесення змін до бази даних. [3.1.1] 1.1. Добавлення нових даних. SQL–оператор INSERT [3.1.1.1] 1.1.1. Однорядковий оператор INSERT [3.1.1.2] 1.1.2. Багаторядковий оператор INSERT [3.1.2] 1.2. Вилучення існуючих даних. SQL-оператор DELETE [3.1.2.1] 1.2.1. Оператор DELETE [3.1.2.2] 1.2.2. Вилучення всіх рядків [3.1.2.3] 1.2.3. Оператор DELETE з підлеглим запитом [3.1.3] 1.3. Оновлення існуючих даних. SQL-оператор UPDATE [3.1.3.1] 1.3.1. Оператор UPDATE [3.1.3.2] 1.3.2. Оновлення всіх рядків [3.1.3.3] 1.3.3. Оператор UPDATE з підлеглим запитом [3.1.4] 1.4. Підсумки
[4]
[5] |
Будемо вивчати створення І.С. професійного рівня. (Література)
Архітектура БД.
(Картинки)
Локальні, файл - сервісні – вивчали в курсі АІС. Цей курс почнемо з вивчення основ клієнт – серверних технологій, які застосовуються до локальних БД.
Модульна система.
1-й модуль: повторення технологій роботи з локальними БД засобами Delphi.
(лаб.)
Завдання: написати І.С. – в кожного своя.
1 – ОО технологія розробки інтерфейсу форм редагування даних
2 – вікна редагування даних
3 – вікна пошуку даних
4 – + фільтрація
5 – вивід звітів.
⇒ перші кілька лабораторних занять.
Опис БД. (с. 284)
„Облік товару на складі”
Найчастіші звертання до БД – з метою отримати деяку інформацію (вибірка).
Є мова SQL
Structured Query Language
Це не алгоритмічна мова (вона не описує як потрібно виконати дії). Вона визначає, що потрібно отримати (форму, зовнішній вигляд) в результаті.
Оператори мови SQL поділяються на дві категорії
SQL
DDL DML
Data Data
Definition Manipulation
Language Language
мова мова
визначення маніпулювання
даних даними
оператор вибірки (SELECT) належить до DML.
- вибирає всю інформацію із вказаної таблиці.
Результат оператора SELECT – являє собою набір даних.
Нехай
|
Товари |
||
|
Назва |
Одиниці |
Ціна |
|
Цукор |
кг. |
2.60 |
|
Молоко |
л. |
1.00 |
|
Хліб |
бух. |
1.30 |
|
Дріжджі |
кг. |
3.60 |
|
Ковбаса |
кг. |
17.50 |
Оператор
1). SELECT * FROM Tovary
- поверне Н.Д., який співпадає з таблицею БД “Товари”
* - означає “всі стовпчики таблиці”.
Є можливість виводити вміст не всіх, а лише деяких стовпчиків:
Вибрати значення стовпчиків: одиниці вимірювання, ціна.
2). SELECT Odynyci, Cina
FROM Tovary
|
Товари –2) |
|
|
Одиниці |
Ціна |
|
кг. |
2.60 |
|
л. |
1.00 |
|
бух. |
1.30 |
|
кг. |
3.60 |
|
кг. |
17.50 |
Вибрати назви одиниць вимірювання всіх товарів, які містяться на складі:
3). SELECT Odynyci FROM Tovary
|
Товари –3) |
|
Одиниці |
|
кг. |
|
л. |
|
бух. |
|
кг. |
|
кг. |
ALL – (по замовчуванню) – виводить всі значення записів;
DISTINCT - виводить лише унікальні значення записів.
В нашому випадку потрібно:
4). SELECT DISTINCT Odynyci
FROM Tovary
|
Товари –4) |
|
Одиниці |
|
кг. |
|
Л. |
|
бух. |
- оператор SELECT DISTINCT реалізує реляційну операцію проекції (вертикальний фільтр).
Секція WHERE дозволяє накласти горизонтальний фільтр:
(секція WHERE – не обов’язкова)
– визначає критерій відбору тих записів, які попадуть у результуючий Н.Д.
= < = ! < (не менше)
< > = ! > (не більше)
> <> != (не дорівнює)
Вибрати всю інформацію про наявні на складі товари, які коштують понад 3 грн.:
5). SELECT *
FROM Tovary
WHERE Cina > 3
|
Товари –5) |
||
|
Назва |
Одиниці |
Ціна |
|
дріжджі |
кг. |
3.60 |
|
ковбаса |
кг. |
17.50 |
Можна порівнювати значення стовпчика із значенням іншого стовпчика (тої самої або іншої таблиці).
Далі буде описана процедура генерації результату SQL – запита на вибірку (потім вона буде доповнюватися). Ці результати отримуються після почергового застосування секцій, які входять в оператор SELECT.
Спочатку виконується секція FROM, потім секція
WHERE, потім секція
SELECT
Дії:
Нехай
|
Відпуск |
||||
|
№ |
Дата |
Кількість |
Товар |
Покупець |
|
1 |
2.09. |
150 |
цукор |
Журавель |
|
2 |
3.09. |
3 |
дріжджі |
Герило |
Вибрати всю інформацію про відпуск товарів (із таблиці “Відпуск”) і для кожного товару вказати його ціну із таблиці “Товари”.
потрібно виводити значення всіх стовпчиків із таблиці “Відпуск”. Якщо просто після SELECT вказати: * - то буде системі не відомо, з якої саме таблиці потрібно вибрати значення усіх стовпчиків – чи із таблиці “Відпуск”, чи із таблиці “Товари”. В таких випадках використовуються уточнені імена:
або, - щоб вибрати всі поля:
6). SELECT Vidpusk. , Tovary. Cina
FROM Vidpusk, Tovary
WHERE Vidpusk. Tovar = Tovary. Nazva
Перед тим як продовжувати, розглянемо.
Потрібно замінити дію 1. на таку:
Розглянемо, як поетапно буде виконуватися запит
6). SELECT Vidpusk . , Tovary . Cina
FROM Vidpusk, Tovary
WHERE Vidpusk. Tovar . = Tovary. Nazva
де
|
Tovary |
||
|
Nazva |
Odynyci |
Cina |
|
цукор |
кг. |
2.60 |
|
молоко |
л. |
1.00 |
|
хліб |
Бух. |
1.30 |
|
дріжджі |
кг. |
3.60 |
|
ковбаса |
кг. |
17.50 |
а
|
Vidpusk |
||||
|
№ |
Data |
Kilkist |
Tovar |
Pokupets |
|
1 |
2.09. |
150 |
цукор |
Журавель |
|
2 |
3.09. |
3 |
дріжджі |
Герило |
дія 1 – декартовий добуток. Таблиця – результат: (8 стовпчиків, 10 рядків).
|
Tovary Vidpusk |
|||||||
|
T. Nazva |
T. Odynyci |
T. Cina |
V. № |
V. Data |
V. Kilkist |
V. Tovar |
V. Pokupets |
|
(цукор) |
кг. |
2.60 |
1 |
2.09 |
150 |
(цукор) |
Журавель |
|
молоко |
л. |
1.00 |
1 |
2.09 |
150 |
цукор |
Журавель |
|
хліб |
бух. |
1.30 |
1 |
2.09 |
150 |
цукор |
Журавель |
|
дріжджі |
кг. |
3.60 |
1 |
2.09 |
150 |
цукор |
Журавель |
|
ковбаса |
кг. |
17.50 |
1 |
2.09 |
150 |
цукор |
Журавель |
|
цукор |
кг. |
2.60 |
2 |
3.09 |
3 |
дріжджі |
Герило |
|
молоко |
л. |
1.00 |
2 |
3.09 |
3 |
дріжджі |
Герило |
|
хліб |
бух. |
1.30 |
2 |
3.09 |
3 |
дріжджі |
Герило |
|
(дріжджі) |
кг. |
3.60 |
2 |
3.09 |
3 |
(дріжджі) |
Герило |
|
ковбаса |
кг. |
17.50 |
2 |
3.09 |
3 |
дріжджі |
Герило |
дія 2 – відкидаються закреслені рядки.
Після виконання дії 2 – результуючий НД отримає вигляд:
|
Tovary Vidpusk |
|||||||
|
T. Nazva |
T. Odynyci |
T. Cina |
V. № |
V. Data |
V. Kilkist |
V. Tovar |
V. Pokupets |
|
цукор |
кг. |
2.60 |
1 |
2.09. |
150 |
цукор |
Журавель |
|
дріжджі |
кг. |
3.60 |
2 |
3.09. |
3 |
дріжджі |
Герило |
Дія 3 – відкидаються непотрібні стовпчики, а потрібні – впорядковуються у належному порядку. Після виконання дії 3:
|
6) |
|||||
|
Vidpusk, № |
Vidpusk, Data |
Vidpusk, Kilkist |
Vidpusk, Tovar |
Vidpusk, Pokupets |
Tovary, Cina |
|
1 |
2.09. |
150 |
цукор |
Журавель |
2.60 |
|
2 |
3.09. |
3 |
дріжджі |
Герило |
3.60 |
Для 4 – не виконується, бо в секції SELECT немає ключового слова DISTINCT.
Уточнені імена полів (які, як правило, використовуються в багатотабличних запитах на вибірку) бувають дуже громіздкими.
Це можна усунути, використовуючи псевдоніми таблиць: в уточненому імені поля спочатку вказується ім’я або псевдонім таблиці, а потім (після крапки) – ім’я поля:
синтаксична діаграма уточненого імені поля.
Псевдоніми таблиць визначаються в секції FROM згідно наступної синтаксичної діаграми.
Ключові слова, імена та псевдоніми відокремлюються пробілами. Той самий оператор SELECT (приклад №6) можна записати з використанням псевдонімів таблиць:
7). SELECT V. , T. Cina
FROM Vidpusk V, Tovary T
WHERE V. Tovar. = T. Nazva
(результат буде той самий, що і результат прикладу №6).
Результуючий набір даних можна відсортувати за допомогою секції
ASC (по замовчуванню) – сортування в порядку зростання значень поля.
DESC – сортування в порядку спадання значень поля.
- це остання секція в операторі SELECT (записується після секції WHERE).
Правила виконання запитів на вибірку доповнюються дією:
Приклад: (відсортувати результат 7, по прізвищах покупців).
8). SELECT V. , T. Cina
FROM Vidpusk V, Tovary T
WHERE V. Tovar = T. Nazva
ORDER BY V.Pokupets
|
8) |
|||||
|
V. № |
V.Data |
V. Kilkist |
V. Tovar |
V. Pokupets |
T. Cina |
|
2 |
3.09. |
3 |
дріжджі |
Герило |
3.60 |
|
1 |
2.09. |
150 |
цукор |
Журавель |
2.60 |
Результуюча таблиця відсортована по значенню цього поля.
В результуючий НД можуть входити не лише стовпчики, які відповідають полям фізичних таблиць БД, а і обчислювальні поля, які визначаються виразами в секції SELECT:
Вираз будується із констант, імен полів, знаків операцій та деяких функцій
(ці функції повинні бути: 1) визначені в стандарті SQL; 2) допускатися в секції SELECT.)
Приклад:
9). вивести ще і вартість замовлення.
9). SELECT V. , T. Cina, V. Kilkist * T. Cina
FROM Vidpusk V, Tovary T
WHERE V. Tovar = T. Nazva
|
9) |
|||||||
|
V. № |
V. Data |
V. Kilkist |
V. Tovar |
V. Pokupets |
T. Cina |
Column 7 |
|
|
1 |
3.09. |
3 |
дріжджі |
Герило |
2.60 |
390.00 |
|
|
2 |
2.09. |
150 |
цукор |
Журавель |
3.60 |
10.80 |
результат виконання приклада 9).
Дію 3. в правилах виконання запитів на вибірку слід трактувати так:
“3. Для кожного рядка, що залишився (після застосування критерію відбору секції WHERE) – обчислити значення кожного елемента із списку полів секції SELECT (тобто, значення стовпчиків, що відповідають полям фізичних таблиць БД, доповнити значеннями обчислювальних полів) і утворити рядок результуючої таблиці”. При цьому: у виразі, який визначає обчислювальний стовпчик, - звертання до поля ТБД відповідає значенню цього поля у даному біжучому рядку. Система автоматично призначає обчислювальним стовпчикам імена COLUMN<№>, де <№> - порядковий номер даного стовпчика в результуючій таблиці (нумерація починається з одиниці) – це не дуже зручно – краще завжди знати явно, яким є ім’я стовпчика. Будь-якому полю можна призначити альтернативне ім’я:
10). SELECT V., T. Cina, V. Kilkist * T. Cina AS Vartist
FROM Vidpusk V, Tovary T
WHERE V. Tovar = T. Nazva
|
10) |
||||||
|
V. № |
V.Data |
V. Kilkist |
V. Tovar |
V. Pokupets |
T. Cina |
Vartist |
|
1 |
2.09. |
150 |
цукор |
Журавель |
2.60 |
390.00 |
|
2 |
3.09. |
3 |
дріжджі |
Герило |
3.60 |
10.80 |
COUNT ( ) – обчислює кількість входжень значення виразу у всі записи чи у групу запитів результуючого НД (тобто, скільки рядків результуючої таблиці містить значення виразу).
SUM ( )– обчислює суму значень виразу по всіх рядках чи по групі рядків результуючої таблиці.
AVG ( ) – обчислює середнє арифметичне значення виразу по всіх рядках чи по групі рядків результуючої таблиці.
MAX ( ) – обчислює максимальне значення виразу по всіх рядках чи по групі рядків результуючої таблиці.
MIN ( ) – обчислює мінімальне значення виразу по всіх рядках чи по групі рядків результуючої таблиці.
11). SELECT COUNT ()
FROM Vidpusk V, Tovary T
WHERE V. Tovar = T. Nazva
|
11) |
|
COLUMN 1 |
|
2 |
12). SELECT SUM (V. Kilkist * T. Cina)
FROM Vidpusk V, Tovary T
WHERE V. Tovar = T. Nazva
|
12) |
|
COLUMN 1 |
|
400.80 |
13). SELECT AVG (V. Kilkist * T. Cina)
FROM Vidpusk V, Tovary T
WHERE V. Tovar = T. Nazva
|
13) |
|
COLUMN 1 |
|
200.40 |
14). SELECT MAX (V. Kilkist * T. Cina)
FROM Vidpusk V, Tovary T
WHERE V. Tovar = T. Nazva
|
14) |
|
COLUMN 1 |
15). SELECT MIN (V. Kilkist * T. Cina)
FROM Vidpusk V, Tovary T
WHERE V. Tovar = T. Nazva
|
15) |
|
COLUMN 1 |
- в усіх випадках результуюча таблиця містить один стовпчик і один рядок. Агрегатні функції також називають – статистичними.
Для агрегатних функцій: якщо із групи однакових записів потрібно враховувати лише одну, то перед виразом в дужках записують ключове слово DISTINCT :
Приклад:
|
Tovary |
||
|
Nazva |
Odynyci |
Cina |
|
цукор |
кг. |
2.60 |
|
молоко |
л. |
1.00 |
|
хліб |
бух. |
1.30 |
|
дріжджі |
кг. |
3.60 |
|
ковбаса |
кг. |
17.50 |
16). Вибрати і вивести кількість різних видів одиниць вимірювання наявних на складі товарів.
16). SELECT COUNT (DISTINCT Odynyci)
FROM Tovary
|
16) |
|
COLUMN 1 |
|
3 |
NULL – відповідає невідомому або не визначеному значенню. В стандарті SQL сказано : “Значення NULL агрегатними функціями ігноруються.”
Нехай:
|
Персонал (Personal) |
|||
|
Tab. № |
Prizv |
Plan |
Prodano |
|
101 |
Дід |
350 |
367 |
|
102 |
Баба |
300 |
392 |
|
103 |
Внучка |
75 |
|
|
104 |
Жучка |
300 |
186 |
|
105 |
Кішка |
275 |
286 |
|
106 |
Мишка |
350 |
361 |
треба: залишити порожню клітинку.
17). SELECT COUNT (*),
COUNT (Plan),
COUNT (Prodano)
FROM Personal
|
17) |
||
|
COUNT (*) |
COUNT (Plan) |
COUNT (Prodano) |
|
6 |
5 |
6 |
COUNT (*) – обчислює загальну кількість рядків у таблиці.
COUNT( ) - обчислює кількість значень у відповідному стовпчику, які не дорівнюють NULL.
Ігнорування значень NULL – не впливає на результати функцій MIN ( ) та MAX ( ). Але це ігнорування може привести до проблем при використанні функцій SUM ( ) і AVG ( ), наприклад:
18). SELECT SUМ (Prodano), SUМ (Plan),
(SUМ (Prodano) – SUM(Plan)),
SUМ (Prodano - Plan)
FROM Personal
|
18) |
|||
|
SUМ (Prodano) |
SUМ (Plan) |
(SUМ (Prodano) – SUM(Plan)) |
SUM (Prodano Plan) |
|
1667 |
1575 |
92 |
17 |
Можна було сподіватися, що вирази
(SUМ (Prodano) – SUM(Plan))
та SUМ (Prodano - Plan)
дадуть ті самі результати, але це не так. Причиною різних результатів є рядок із значенням NULL в стовпчику Plan.
Вираз SUM (Prodano)
- обчислює суму продаж для всіх шести працівників, а вираз
SUМ (Plan)
- обчислює суму тільки п’ять значень і не враховує значення NULL.
Наступний вираз обчислює різницю між цими двома сумами:
SUМ (Prodano) – SUM (Plan)
З іншої сторони, вираз
SUМ (Prodano - Plan)
приймає в якості аргументів лише п’ять значень, які не дорівнюють NULL. В рядку, де значення запланованого об’єму продаж дорівнює NULL (для Внучки), будь-який вираз, що містить поле Plan, отримає значення NULL (а отже, Prodano – Plan NULL), і це значення функції SUМ ( ) проігнорує. Отже, результати цього вирази не враховують фактичні продажі працівника, для якого ще не встановлено плану, хоча вони ввійшли в результат попереднього виразу. Яка ж відповідь є “вірною”? Обидві! Перший вираз обчислює саме те, що і означає, тобто “сума по Prodano мінус сума по Plan” і другий вираз також обчислює саме те, що означає, тобто “сума по різницях між Prodano і Plan”, але при наявності значень NULL результати виразів – відрізняються.
Ось точні правила опрацювання значень, NULL статистичними функціями:
то
COUNT ( )
COUNT (*) .
Комерційні (промислові) СУБД можуть по-іншому опрацьовувати NULL – значення статистичними функціями, ніж це описано в стандарті SQL - треба дивитися опис конкретної СУБД.
Інколи потрібно отримати агрегатні значення (мінімум, максимум, середнє арифметичне тощо) не по всьому результуючому НД, а лише по кожній із груп записів, що входять в цей результуючий НД. Кожна група записів характеризується однаковим значенням якогось стовпчика.
Наприклад, задача:
вивести загальну кількість відпущеного товару по кожному товару.
- потрібно обчислювати суму кількостей відпуску товарів і групувати по назві товару.
Для цього в оператор SELECT після секції WHERE добавляється секція GROUP BY (якщо є секція ORDER BY, то секція GROUP BY записується після секції WHERE, але перед секція ORDER BY)
Якщо є секція GROUP BY, то обов’язково потрібно, щоб один із стовпчиків результуючої таблиці представлявся агрегатною функцією!
- тобто, щоб в секцію SELECT входив вираз, що містить агрегатну функцію.
Групування відбувається по не - агрегатним полям, отже для групування (тобто, для обчислення агрегатної функції не по всім рядкам таблиці, а лише по групам) – потрібно, щоб у секцію SELECT входило, по крайній мірі, одне не - агрегатне поле (групування здійснюється по одиноким значенням у цьому не - агрегатному стовпчику).
Всі не - агрегатні поля секції SELECT повинні бути вказані в секції GROUP BY!
синтаксична діаграма оператора SELECT:
ASC – в порядку зростання (по замовчуванню)
DESC – в порядку спадання.
Це лише та частина, яку ми вже вивчили. Повна синтаксична діаграма – приблизно вдвічі громіздкіша.
Приклади:
# Вивести загальну кількість відпусків по кожному із товарів:
19). SELECT V. Tovar, SUM (V. Kilkist) AS Vidp
FROM Vidpusk V
GROUP BY V. Tovar
|
19) |
|
|
Tovar |
Vidp |
|
…… |
……. |
|
…… |
……. |
# Вивести загальну вартість для кожного із відпущених товарів:
20). SELECT V. Tovar,
SUM (V. Kilkist) AS Vart
FROM Vidpusk V, Tovary T
WHERE V. Tovar = T.Nazva
GROUP BY V. Tovar
|
20) |
|
|
Tovar |
Vart |
|
…… |
……. |
|
…… |
……. |
1. Сформувати декартовий добуток таблиць, вказаних в секції FROM.
Якщо в секції FROM вказана лише одна таблиця, то декартовим добутком буде вона сама.
2. Якщо є секція WHERE - застосувати умову секції WHERE до кожного рядка таблиці, утвореної в результаті декартового добутку і залишити тільки ті рядки, для яких ця умова виконується, тобто має значення TRUE; рядки, для яких умова має значення FALSE або NULL – відкидаються.
3. Якщо секція GROUP BY, - розділити рядки, що залишилися в результ. таблиці, на групи таким чином, щоб рядки в кожній групі мали одинакові значення по всіх стовпчиках групування. Стовпчик групування – це той стовпчик, який визначає групу рядків. В межах групи всі рядки мають в стовпчику групування одне і те саме значення.
|
Таблиця |
||
|
...... |
стовпчик групування |
...... |
|
значення 1 значення 1 .................. значення 1 |
||
|
значення N .................. значення N |
||
4. Для кожного рядка, що залишився (або для кожної групи рядків) – обчислити значення кожного елемента із списку полів секції SELECT і утворити один рядок таблиці результату запиту. При будь-якому звертанні до стовпчика береться значення стовпчика для біжучого рядка (або групи рядків).
В якості аргументу статистичної функції використовуються значення стовпчика із всіх рядків, які входять у групу, - якщо вказана секція GROUP BY; якщо ж секції GROUP BY – немає, то використовуються значення стовпчика із усіх рядків таблиці результатів запиту.
5. Якщо є ключове слово DISTINCT – вилучити із таблиці результатів запиту всі рядки – дублікати.
6. Якщо є секція ORDER BY – відсортувати результати запиту.
SQL дозволяє групувати результати запиту на основі двох чи більшої кількості стовпчиків.
Наприклад, потрібно згрупувати замовлення по працівникам та клієнтам.
Нехай
|
Zamovlennia |
|||
|
Pracivnyk |
Klient |
Kilkist |
..... |
|
........... |
............. |
............. |
........... |
Тоді, запит:
21). Обчислити загальну кількість замовлень по кожному клієнту для кожного працівника:
21). SELECT Pracivnyk,
Klient,
SUM (Kilkist)
FROM Zamovlennia
GROUP BY Pracivnyk,
Klient
Навіть при групуванні по двом стовпчикам SQL забезпечує лише один рівень групування.
Запит 21) генерує лише один підсумковий рядок для кожної пари працівник – клієнт.
За допомогою SQL неможливо створити групи і підгрупи з двома ( чи більшою кількістю) рівнями підсумкових результатів.
Найкраще, що можна зробити – це відсортувати дані таким чином, щоб рядки в таблиці результатів запиту розміщувалися в потрібному порядку.
В багатьох СУБД при використанні GROUP BY сортування виконується автоматично, але автоматичний порядок сортування можна поміняти за допомогою секції ORDER BY:
22). Підрахувати загальну кількість замовлень по кожному клієнту для кожного працівника, відсортувати результати запиту по клієнтам і працівникам:
22). SELECT Klient,
Pracivnyk,
SUM (Kilkist)
FROM Zamovlennia
GROUP BY Klient,
Pracivnyk
ORDER BY Klient,
Pracivnyk
За допомогою одного SQL – запиту неможливо отримати як детальні, так і проміжні підсумкові результати.
Для того, щоб отримати детальні результати з підсумками по групам – необхідно написати програму, яка обчислює ці проміжні підсумкові результати (це може робити Quick Report)
На запити, в яких використовується групування, накладаються додаткові обмеження. Стовпчики групування повинні бути реальними стовпчиками таблиць, вказаних в секції FROM – це повинні бути фізичні поля таблиць, не можна групувати рядки на основі значення обчислювального виразу.
Крім того, існують обмеження на елементи списку полів секції SELECT. Всі елементи цього списку повинні мати одне значення для кожної групи рядків. Це означає, що елементом списку полів секції SELECT може бути:
На практиці в список полів секції SELECT запита з угрупуванням завжди входять стовпчик групування і статистична функція. Якщо не вказано статистичної функції – значить запит можна зробити простішим за допомогою ключового слова DISTINCT без використання секції GROUP BY. Якщо ж не включати в результати запиту стовпчик групування, то не можна визначити, до якої групи належить кожний рядок результатів!
Коли в стовпчику групування міститься значення NULL, виникають додаткові ускладнення: якщо значення стовпчика невідоме, то до якої групи його потрібно віднести?
В секції WHERE при порівнянні двох значень NULL результат має значення NULL (а не TRUE) тобто два значення NULL не вважається однаковими! Якщо таке правило застосовувати в секції GROUP BY, то це приведе до того, що кожний рядок із значенням NULL в стовпчику групування буде занесений в окрему групу, яка складається із одного цього рядка.
На практиці – це дуже незручно. Тому в стандарті SQL визначається, що два значення NULL в секції GROUP BY – співпадають! Тобто, якщо два рядка мають значення NULL в однакових стовпчиках групування та ідентичні значення у всіх інших стовпчиках групування, то вони заносяться в одну групу.
Хоча такий принцип опрацювання значень NULL визначений в стандарті SQL, він реалізований не для всіх діалектів SQL - потрібно перевірити комерційну СУБД.
Нехай
|
People |
||
|
Name |
Hair |
Eyes |
|
Cindy Louise Harry Samantha Joanne George Mary Paula Kevin Joel Susan Marie |
Brown NULL NULL NULL NULL Brown Brown Brown Brown Brown Blonde Blonde |
Blue Blue Blue NULL NULL NULL NULL NULL NULL Brown Blue Blue |
(при виконанні таких прикладів на ЕОМ значення NULL – не вводяться, - треба замість них залишати порожні клітинки!)
23). SELECT Hair, Eyes, COUNT (*)
FROM People
GROUP BY Hair, Eyes
результат виконання запиту №23:
|
23) |
||
|
Hair |
Eyes |
COUNT (*) |
|
Brown NULL NULL Brown Brown Blonde |
Blue Blue NULL NULL Brown Blue |
1 2 2 4 1 2 |
Якщо в результуючому НД потрібно виводити підсумкові значення не для всіх груп, а лише для тих, які задовольняють певній умові, то після секції GROUP BY (перед секцією ORDER BY) вказується секція
Як правило, умова секції HAVING має вигляд:
Формат секції HAVING відповідає формату секції WHERE, за одним важливим винятком.
!в секції WHERE не можна вказувати агрегатних функцій!
Приклади
24) # Яка середня вартість замовлення для кожного працівника із тих, у яких загальна вартість замовлень перевершує 3000?
24). SELECT Pracivnyk, AVG (Vartist)
FROM Zamovlennia
GROUP BY Pracivnyk,
HAVING SUM (Vartist) > 3000
25). # Вивести загальну кількість купленого товару (в одиницях вимірювання) для всіх покупців, яких мінімальна кількість купленого товару не менша 100 шт.
25). SELECT Pokupets,
SUM (Kilkist)
FROM Vidpusk
GROUP BY Pokupets
HAVING MIN (Kilkist) > =100
В якості аргумента статистичної функції використовуються значення стовпчика із всіх рядків, які входять у групу (якщо є секція GROUP BY); якщо ж секції GROUP BY – немає, то використовуються значення стовпчика із всіх рядків таблиці результатів запиту.
Нехай є таблиці
|
Товари |
||||
|
Код виробника |
Код товару |
Назва |
Ціна |
Кількість наявного |
|
Замовлення |
||||||
|
№ замовл. |
Дата |
Код клієнта |
Код працівника |
Код виробника |
Код товару |
Кількість |
Приклад
26). Вивести ціну, кількість наявних на складі товарів та загальну кількість замовлених одиниць для кожної назви товару, якщо для нього загальна кількість замовлених одиниць для кожної назви товару, якщо для нього загальна кількість замовлених одиниць більша, ніж 75% від кількості наявних на складі товарів.
26). SELECT
T. Nazva,
T. Cina,
T. Najavna_Kilkist,
SUM (Z.Kilkist)
FROM Tovary T,
Zamovlennia Z
WHERE
Z. Kod Vyrobn = T. Kod Vyrobn
AND Z. Kod Tovaru = T. Kod Tovaru
GROUP BY
T. Kod Vyrobn,
T. Kod Tovaru,
T. Nazva,
T. Cina,
T. Najavna_Kilkist
HAVING
SUM (Z.Kilkist) > (0.75 * T. Najavna_Kilkist)
ORDER BY
T. Najavna_Kilkist DESC
При реалізації цього запиту СУБД виконує дії:
Як було сказано раніше, стовпчики T. Nazva, T. Cina, T. Najavna_Kilkist повинні бути вказані в списку стовпчиків групування секції GROUP BY – бо вони є в списку результуючих стовпчиків секції SELECT.
Але фактично вони не приймають участі в процесі групування, бо стовпчики T. Kod Vyrobn та T. Kod Tovaru повністю визначають рядок таблиці Tovary і стовпчики T. Nazva, T. Cina, T. Najavna_Kilkist автоматично мають в групі одне значення.
Секція HAVING використовується для того, щоб включати і виключати групи рядків із результатів запиту, тому умова відбору застосовується не до окремих рядків, а до групи в цілому.
Це означає, що в умову відбору груп може входити:
На практиці умова відбору в секції HAVING завжди повинна містити як мінімум одну статистичну функцію.
Якщо це не так – то таку умову можна перемістити в секцію WHERE. Щоб визначити, де слід вказувати умову відбору – в секції WHERE чи в секції HAVING – треба згадати як вони застосовуються:
Як і в секції WHERE, умова відбору в секції HAVING може дати один із наступних результатів:
Правила опрацьовування значень NULL в умовах відбору для секції HAVING – ті самі, що і для секції WHERE.
Секції HAVING майже завжди використовується разом із секцією GROUP BY, але синтаксис оператора SELECT цього не вимагає.
Якщо секції HAVING використовується без секції GROUP BY, СУБД вважає всі результати запиту однією групою. Тобто, статистичні функції, вказані в секції HAVING, застосовуються до однієї і лише однієї групи і ця група складається із усіх рядків.
На практиці секція HAVING дуже рідко використовується без відповідної секції GROUP BY.
Складні логічні вирази будуються за допомогою операцій AND,OR і NOT.
Їх використання, а також побудова за їх допомогою складних виразів визначається стандартними правилами, які прийняті в більшості алгоритмічних мов (в т.ч. – Object Pascal для Delphi).
В стандарті SQL є один дуже важливий виняток з цих правил: операції відношення в SQL мають більший пріоритет, ніж логічні операції, - це звільняє від необхідності розставляти багато дужок.
*! Значення NULL впливають на інтерпретацію складних умов відбору, внаслідок чого результати останніх стають не такими очевидними.
Операція OR використовується для об’єднання двох умов, із яких як мінімум одна повинна бути істинною.
Для об’єднання двох умов, обидві із яких повинні бути істинними, потрібно використовувати операцію AND.
Операція NOT використовується для вибору рядків, для яких умова відбору хибна.
Таким, чином: результатом виразу NULL OR TRUE – буде TRUE, а не NULL:
Таблиця істинності операції AND
|
AND |
TRUE |
FALSE |
NULL |
|
TRUE |
TRUE |
FALSE |
NULL |
|
FALSE |
FALSE |
FALSE |
FALSE |
|
NULL |
NULL |
FALSE |
NULL |
Таблиця істинності операції OR
|
OR |
TRUE |
FALSE |
NULL |
|
TRUE |
TRUE |
TRUE |
TRUE |
|
FALSE |
TRUE |
FALSE |
NULL |
|
NULL |
TRUE |
NULL |
NULL |
Таблиця істинності операції NOT
|
NOT |
TRUE |
FALSE |
NULL |
|
FALSE |
TRUE |
NULL |
Якщо за допомогою операцій AND, OR і NOT об’єднуються більш, ніж дві умови відбору, то згідно стандарту SQL: операція NOT має найвищий пріоритет, наступний пріоритет має операція AND, операція OR має самий низький пріоритет. Але, щоб гарантувати правильне виконання SQL – запиту на будь-якій платформі, потрібно використовувати круглі дужки – це дозволить усунути всі можливі неоднозначності.
Коли порівнюються два вирази, результатом може бути одне з трьох:
Синтаксична діаграма виразу:
На практиці найчастіше використовуються такі порівняння:
або
27). Вивести дані про всі відпуски товару, у яких кількість (в одиницях) відпущеного товару лежить в діапазоні від 1000 до 3000:
27). SELECT *
FROM Vidpusk
WHERE Kilkist BETWEEN 1000 AND 3000
Типи виразу, нижнього та верхнього значень – повинні бути сумісними.
Перевірка
X BETWEEN A AND B
повністю еквівалентна перевірці
( X > = A ) AND ( X <= B)
Правила опрацювання значень NULL в перевірці BETWEEN…AND…
2) Якщо вираз, що визначає нижню границю діапазону має значення NULL, то перевірка BETWEEN ... AND… дає результат FALSE у випадку, коли вираз, що перевіряється, має значення, більше ніж значення верхньої границі діапазону; і дає результат NULL в іншому випадку:
3) Якщо вираз, що визначає верхню границю діапазону, має значення NULL, то перевірка BETWEEN ... AND… дає результат FALSE у випадку, коли вираз, що перевіряється, має значення, менше ніж значення нижньої границі діапазону; і дає результат NULL в іншому випадку:
При перевірці BETWEEN ... AND… верхня і нижня межа вважаються частинами діапазону. Вираз, що перевіряється, нижня межа та верхня межа діапазону – можуть вказуватися виразами, але на практиці, як правило:
вираз що перевіряється – вказується стовпчик;
нижня та верхня межа – вказуються константи.
Хоча і вираз, що перевіряється, і нижня та верхня межа – можуть бути виразами, на практиці, як правило, вираз, що перевіряється – представляє собою стовпчик, а нижня та верхня межа – визначається константами.
28). Вивести дані про всі відпуски товару, в яких кількість відпущеного товару (в одиницях) дорівнює 100, 1000 до 3000:
28). SELECT *
FROM Vidpusk
WHERE Kilkist IN (100, 1000, 3000)
Перевірка
X IN (A, B, C)
повністю еквівалентна перевірці
( X = A ) OR ( X = B) OR (X = C),
але перевірка IN – набагато ефективніша.
Рекомендується уникати множин, які складаються лише із одного елемента
X IN (A)
- таку перевірку краще замінювати звичайним порівнянням:
X = A
Якщо вираз, що перевіряється, має значення NULL, то результатом перевірки IN буде NULL:
NULL IN (...) → NULL
Всі елементи в списку значень множини повинні бути того самого типу, який повинен бути сумісний із типом виразу, що перевіряється.
Зауваження:
Є ще одна форма перевірки IN, в який список значень формується вкладеним оператором SELECT.
Цей варіант – розглядається в розділі “Вкладені запити”.
Значення NULL дозволяють застосовувати трьохзначну логіку в умовах відбору. Для будь-якого заданого рядка результатом умови відбору може бути TRUE, FALSE або NULL (якщо в якомусь стовпчику цього рядка міститься значення NULL).
Інколи буває потрібно явно перевіряти значення стовпчика на рівність NULL і безпосередньо опрацювати такі значення.
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
NULL – означає невідоме або не визначене значення.
Не можна явно записувати у клітинку ′ NULL′ - це літерна величина.
Щоб клітинка отримала значення NULL при заповненні таблиці – треба її залишити порожньою.
В Delphi є метод Clear – очищає поле, робить відповідну клітинку таблиці – порожньою (<=> заносить в неї значення NULL)
Приклад
|
Table 1 |
||
|
Field A |
Field B |
…… |
потрібно очистити це поле
Table 1 Field B . Clear ;
ім’я об’єкта – поля
Але використання метода Clear об’єктів - полів - не відповідає технологіям принципам побудови клієнт-серверних програм – бо цей спосіб очистки поля розрахований на локальні набори даних.
В стандарті SQL є оператор UPDATE, за допомогою якого можна очистити поле – будемо розглядати пізніше.
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Для безпосереднього опрацювання NULL – значень в SQL є перевірка IS NULL:
29). Вивести всі дані про відпуски товарів зі складу, для яких не вказаний покупець:
29). SELECT *
FROM Vidpusk
WHERE Pokupets IS NULL
Обернена форма перевірки на NULL (IS NOT NULL) дозволяє вибрати рядки, які не містять значення NULL:
30). Вивести всі дані про відпуски товарів зі складу, для яких вказаний покупець:
30). SELECT *
FROM Vidpusk
WHERE Pokupets IS NOT NULL
На відміну від всіх інших перевірок, перевірка на NULL не може повертати NULL в якості результату. Вона завжди повертає TRUE або FALSE.
Може здаватися дивним, що не можна перевірити значення на рівність NULL за допомогою порівняння, наприклад:
31). (неправильно!)
SELECT *
FROM Vidpusk
WHERE Pokupets = NULL
Ключове слово NULL тут не можна використовувати, бо насправді – це не справжнє значення, - це – просто сигнал, що значення – невідоме. Навіть якби порівняння
Pokupets = NULL
було можливим, правила опрацювання значень NULL в порівняннях привели би до того, щоб результат був би зовсім не такий, як очікується:
Коли СУБД виявить рядок, в якому поле Pokupets містить значення NULL, виконалася би наступна перевірка:
NULL = NULL
Що буде в результаті: TRUE чи FALSE?
Оскільки значення по обидві сторони від знаку = - невідомі, то згідно правил виконання порівнянь – повинні отримати NULL.
Оскільки умова відбору має значення, яке не співпадає із TRUE – то цей рядок вилучається із результуючої таблиці – результат – прямо протилежний тому, якого ми прагнули!
Тому, в силу правил логіки SQL та правил опрацювання значень NULL, в таких випадках необхідно виконувати перевірку IS NULL.
10.6.1. Перевірка, чи літерний рядок починається з певного значення (STARTING WITH)
Щоб перевірити, чи значення літерного стовпчика починалось з певної підстрічки. Для такої перевірки потрібно використовувати:
32). Отримати список товарів, які починаються із літери “С”.
32). SELECT *
FROM Tovary
WHERE Tovar STARTING WITH “С”
10.6.2. Перевірка, чи літерний рядок містить певне значення (CONTAINING)
Щоб перевірити, чи значення літерного стовпчика містить певний підрядок символів (не має значення, з якої саме позиції) – треба використовувати
33). Отримати список покупців, чия адреса містить підрядок “Стр”:
33). SELECT *
FROM Pokupci
WHERE Adresa CONTAINING “Стр”
10.6.3. Перевірка на відповідність шаблону (LIKE)
Це – більш загальний спосіб, який дозволяє замінити і
STARTING WITH
і
CONTAINING
Перевірка на відповідність шаблону дозволяє встановити, чи відповідає значення даних шаблону.
Шаблон – це літерний рядок, в який може входити один або більше підстановочних символів.
Ці символи трактуються особливим чином.
Підстановочні символи
34). Вивести всю інформацію про товари, назви яких починаються із літери “М”, а закінчуються літерою “О”.
34). SELECT *
FROM Tovary
WHERE Nazva LIKE “М%О”
|
Nazva |
...... |
|
Масло |
...... |
|
Молоко |
....... |
2). Підстановочний символ _ (підкреслення) означає рівно один і лише один будь-який символ.
35). Вивести всю інформацію про товари, назви яких починаються із літери “М”, закінчуються літерою “О” і складаються рівно із 5-ти символів.
35). SELECT *
FROM Tovary
WHERE Nazva LIKE “М_ _ _ О”
|
Nazva |
...... |
|
Масло |
...... |
Підстановочні символи можна записувати в будь-якому місці шаблону; в одному шаблоні може використовуватися кілька підстановочних символів.
36). Наступний запит допускає назву компанії як “Smithson”, так і “Smithsen”, а також будь-яке закінчення назви компанії: “Co”, “Corp”, “Inc.” тощо:
36). SELECT Company
FROM Clienty
WHERE Company LIKE “Smтiths_n %”
За допомогою перевірки NOT LIKE можна вибирати рядки, які не задовольняють шаблону.
Перевірку LIKE (так само, як і STARTING WITH та CONTAINING) можна застосовувати лише до стовпчиків, які мають літерний тип даних. Якщо стовпчик містить значення NULL, то результатом перевірок LIKE, STARTING WITH, CONTAINING – буде NULL.
Щоб подібне є в операційній системах MS DOS, UNIX: в шаблоні можна вказувати групу імен файлів:
* ( % - аналог) - кількість символів
? ( _ - аналог) - 1 символ
Символ пропуску
При перевірці літерних рядків на відповідність шаблону може виявитися, що підстановочні символи входять у літерний рядок в якості значущих символів.
Наприклад, не можна перевірити, чи міститься знак % у літерному рядку, просто помістивши його у шаблон, бо СУБД буде вважати цей знак підстановочним символом.
В таких випадках використовуються символи пропуску.
Коли в шаблоні зустрічається символ пропуску, то символ, записаний безпосередньо після нього, вважається не підстановочним символом, а значущим (відбувається пропуск символа).
Безпосередньо після символа пропуску може бути записаний: або один із двох підстановочних символів, або сам символ пропуску, оскільки він також отримує в шаблоні особливе значення.
Символ пропуску визначається у вигляді літерної константи, яка складається із одного символа і ключового слова ESCAPE (див. синтаксичну діаграму).
Приклад використання знаку “$” в якості символа пропуску:
37). Вивести товари, коди яких починаються з чотирьох символів: “A%BC”:
37). SELECT *
FROM Tovary
WHERE Kod T LIKE “A $ % BC %”
ESCAPE “$”
- перший символ „%” в шаблоні, який записаний після символа пропуску, вважається значущим, а другий „%” – підстановочним символом.
38). Вивести товари, коди яких починаються із такої послідовності символів:
38). SELECT *
FROM Tovary
WHERE Kod T LIKE “$_A $ % $$ B_C %”
ESCAPE “$”
В перевірках є можливість перетворювати дані одного типу до іншого.
10.7.1. Типи даних
В стандарті SQL1 був описаний лише мінімальний набір типів даних. Вони підтримуються всіма промисловими СУБД.
Стандарт SQL2 добавив у цей набір літерні рядки змінної довжини, значення дати і часу, тощо.
Сучасні СУБД дозволяють опрацьовувати дані самих різних типів, серед яких найбільш поширеними є такі типи:
INT цілі числа (4 байти)
INTEGER - 2147483648. +2147483647
SMALLINT малі цілі числа (2 байти)
- 32768..+32767
В таких стовпчиках, як правило, зберігаються дані про кількість, вік співробітників, тощо. Також вони використовуються для зберігання ідентифікаторів: код клієнта, працівника чи замовлення.
2. Десяткові числа.
DECIMAL ( , )
DEC ( , )
NUMERIC ( , )
- загальна кількість знаків у числі (максимум 15)
- кількість знаків у дробовій частці.
- може бути = 0
- повинна бути <
- в таких стовпчиках зберігаються числа, які мають дробову частину, і які необхідно обчислювати точно, наприклад: курси валют; проценти; крім того, в таких стовпчиках часто зберігаються грошові величини.
3. Числа з плаваючою крапкою.
FLOAT
REAL - до 7 знач. цифр, (4 байти) 3,4·10-38... 3,4·10+38
DOUBLE PRECISION - до 15 знач. цифр (8 байт) 1,7·10-308... 1,7·10+308
- використовуються для зберігання величин, які можна обчислювати наближено: значення ваги і відстані. Числа з плаваючою крапкою представляють більший діапазон значень, ніж десяткові числа, але при обчисленнях можливі похибки заокруглення.
4. Рядки символів постійної довжини.
CHAR ( )
CHARACTER ( )
- 0..32767 байт
- імена людей, назви компаній і товарів, адреси, тощо.
5. Рядки символів змінної довжини.
VARCHAR ( )
- 0..32767 байт
6. Грошові величини.
MONEY
CURRENCY - такий окремий тип є далеко не у всіх СУБД. Як правило, ці дані зберігаються у вигляді десяткового числа.
7. Дата і час.
DATE SQL2 - дата і час, від 1.01.0100 до 11.12.5941 (8 байт)
(TIME) - час
TIME ( )
TIMESTAMP ( ) - реалізація дати і часу дуже відрізняється у різних СУБД.
8. Булівські величини.
деякі СУБД явно підтримують (informix) логічні значення TRUE чи FALSE, інші – лише дозволяють виконувати логічні операції над даними в інструкціях SQL.
9. Довгий текст (SQL2).
деякі СУБД підтримують стовпчики, в яких можуть зберігатися довгі літерні рядки (як, правило до 32 000 або 65 000 символів, а в деяких випадках і >). Це дозволяє зберігати в БД цілі документи.
10. Неструктуровані потоки байтів. (SQL2)
Сучасні СУБД дозволяють зберігати неструктуровані потоки байтів змінної довжини. Такі стовпчики, як правило, використовуються для зберігання графічних та відео-зображень, виконуваних файлів, тощо. Тип IMAGE в SQL Server – дозволяє зберігати до 2 млрд. байтів.
Відмінності в реалізації типів даних у різних СУБД суттєво заважають в роботі – не можна переносити програми із однієї платформи на іншу.
Причини таких відмінностей – в самому шляху розвитку реляційних баз даних:
1). Виробник СУБД добавляє в свій продукт підтримку нового типу даних, який забезпечує нові корисні можливості для певної групи користувачів.
2). Інший виробник, оцінивши ідею, вводить підтримку цього типу даних в свій продукт, але вносить деякі модифікації, - щоб його не можна було звинуватити в сліпому копіюванні.
3). Якщо ідея виявилася вдалою, то через деякий час цей тип даних з’явиться в більшості СУБД.
4). Далі такою ідеєю починають цікавитися комітети по стандартизації, задача яких – усунення відмінностей в реалізації ідеї у ведучих СУБД.
Але чим більше таких відмінностей, тим важче знайти компроміс. Як правило, результати роботи комітету буде варіант, який не співпадає із жодною з реалізацій.
5). Виробники СУБД починають впроваджувати підтримку отриманого стандартизованого типу даних, але, оскільки вже є дуже багато інстальованих попередніх версій СУБД, - то вони вимушені підтримувати і старий варіант типу даних (для сумісності програм).
6). Через дуже значний період часу користувачі, нарешті, повністю переходять до використання стандартного варіанту типу даних і виробники СУБД починають вилучати підтримку старого варіанту із своїх продуктів.
SQL 1 - в суч. СУБД
SQL 2 – в повному варіанті – жодною суч СУБД.
10.7.2. Функції
Хоча вбудовані функції не були визначені в стандарті SQL 1, в більшості промислових СУБД вони реалізовані.
( )
( )
39). Вибрати працівників, які народилися восени.
39). SELECT *
FROM Pracivnyky
WHERE MONTH (Data Narod)
BETWEEN 9 AND 11
40). Вибрати студентів, які поступили у 1999 р.
40). SELECT *
FROM Students
WHERE YEAR (Data Vstupu) = 1999
Багато функцій виконують форматування даних
СУБД oracle:
41). Приклад: вивід прізвищ і дат народження.
41). SELECT Prizv,
TO_CHAR (Data Nar, DAY, DD MONTH YYYY)
FROM Students
Функцію можна використовувати скрізь, де дозволяється використовувати константу того ж самого типу даних. Тут немає можливості розглянути всі функції, які підтримуються промисловими СУБД – бо їх дуже багато:
DB2 20 шт.
Oracle 20 шт.
SQL Server – ще більше.
В стандарт SQL 2 увійшли найбільш корисні функції із різних СУБД.
Ось вони:
|
Функція |
Результат |
|
кількість бітів в стрічці символів |
|
|
значення, перетворене до вказаного типу даних |
|
|
довжина стрічки символів |
|
|
стрічка, перетворена згідно вказаної функції |
|
|
біжуча дата |
|
|
біжучий час із заданою точністю |
|
|
біжучі дата і час із заданою точністю |
|
|
вказана частина (DAY, HOUR і т.д.) із значення типу DATE TIME |
|
|
стрічка переведена в нижній регістр |
|
|
номер місяця |
|
|
кількість байт в стрічці символів |
|
|
позиція, починаючи з якої входить у |
|
|
частина , яка починається із заданої і має вказану |
|
|
стрічка, яка зображує дату у вказаному форматі |
|
|
стрічка, трансльована за допомогою функції |
|
|
стрічка, із якої вилучені ведучі і замикаючі вказані символи |
|
|
стрічка, із якої вилучені ведучі вказані символи |
|
|
стрічка, із якої вилучені ведучі замикаючі символи |
|
|
стрічка, переведена у верхній регістр |
|
|
число = рік |
більш детально – див. документацію по конкретній СУБД.
Зауваження: 1). Деякі СУБД не мають всіх цих функцій ( бо стандарт SQL 2 ніде ще повністю не реалізований). 2). У деяких СУБД відповідні функції – є, але можуть називатися інакше.
10.7.3. Використання функцій UPPER та LOWER
UPPER ( ) – до верхнього регістру
LOWER ( ) – до нижнього регістру
- м. бути стовпчик або вираз літерного типу.
42).
43).
42). SELECT *
FROM Clients
WHERE Misto = Львів
43). SELECT *
FROM Clients
WHERE UPPER (Misto) = Львів
10.7.4. Використання функції CAST
Інколи буває потрібно трактувати значення одного типу як значення іншого. Наприклад, використовувати число як літерний рядок або навпаки.
В цьому випадку використовують функцію
Функція CAST робить копію , перетворюючи його до вказаного . При цьому треба враховувати допустимі типи, до яких можна перетворити дане значення.
|
Тип даних значення |
Можна перетворити до типу |
|
числовий |
CHARACTER |
|
NUMERIC |
DATE |
|
CHARACTER |
INTEGER, SMALLINT, NUMERIC, DATE |
|
DATE |
CHARACTER, NUMERIC |
Функція CAST можна використовувати скрізь, де допускаються вирази, що повертають скалярні значення, - і в секції SELECT і в умовах відбору.
44). Приклад.
Нехай потрібно знайти покупців, які придбали таку кількість товару, яка закінчується цифрами 09 (тобто, або 109, або 209, або 309 і т.д.) – для цього приводимо значення стовпчика до типу CHARACTER і до результату застосовуємо перевірку LIKE:
44). SELECT *
FROM Vidpuck
WHERE CAST (Kilkist AS CHAR (4))
LIKE “%09”
Зауваження: в СУБД InterBase значення типу DATE не потрібно приводити до стрічкового тип, бо ці два типи – сумісні, і значення DATE в InterBase може трактуватися як стрічкове.
45). Приклад.
Вивести інформацію про працівників, які народилися 29 жовтня. В InterBase немає вбудованих функцій для розділення дати на число, місяць, рік. Є такі шляхи розв’язку:
-1). в клієнтській програмі аналізувати дати за допомогою стандартної процедури Decode Date.
-2). написати UDF (User Defined Function – функцію, визначену користувачем), яка реалізує виділення номера дня із дати і використати цю функцію в операторі SELECT.
-3). привести значення дати до типу CHAR ( або, ще краще, трактувати значення дати як літерне значення – InterBase) і застосовувати до отриманого значення перевірку LIKE, CONTAINING або STARTING WITN. Скористаємося останнім способом:
45). SELECT *
FROM Pracivnyky
WHERE CAST (Data Nar AS CHAR (8))
STARTING WITN “29.10”
або, для InterBase, - без непотрібного перетворення типів
46). SELECT *
FROM Pracivnyky
WHERE Data Nar LIKE “29.10%”
На практиці перетворення CAST найчастіше використовується:
1 – для перетворення значень, отриманих від стовпчика таблиці з непідходящим типом даних.
Наприклад, стовпчик визначений як стрічковий, але ми знаємо, що насправді він містить зображення чисел (тобто, послідовності символів – цифр) або дат (тобто, стрічки, які можна трактувати як “день/місяць/рік”
2 – для приведення даних, що повертаються запитом до типу, який підтримується мовою програмування клієнтської програми.
Наприклад, більшість мов програмування не підтримує спеціальних типів даних для дати і часу, - і для опрацювання таких значень програмою їх необхідно перетворити до літерних стрічок.
3 – для приведення значень із однієї таблиці до типу даних, аналогічних значень іншої таблиці.
Наприклад, якщо в таблиці замовлень дата замовлення зберігається у вигляді значення типу DATE, а в таблиці наявності товару на складі дата зберігається у вигляді літерного рядка, то для їх порівняння потрібно: або перетворити дату замовлення у стрічку, або стрічку із таблиці “Наявні товари” привести до типу DATE.
Ще один приклад:
При об’єднанні результатів двох вибірок за допомогою операції UNION стовпчики, що об’єднуються, повинні бути однакового типу. Тому для використання такого об’єднання може бути необхідно привести стовпчики однієї вибірки до типу даних стовпчиків іншої.
10.7.5. Використання операції склеювання літерних рядків ||
Операція || з’єднує два літерних значення, які можуть бути результатами виразів:
Цю операцію можна використовувати як в секції SELECT, так і в умовах відбору.
47). Наступний запит виводить в одному стовпчику прізвища покупців і назви їх міст:
47). SELECT Prizv ||′(′||Misto||′)′
FROM Pokupci
Ще приклад:
48). SELECT “Прізвище:”||
Prizv ||
“(дата народження:”||CAST(Data Nar AS CHAR(8))||”)”
FROM Pracivnyky
Інколи буває необхідно об’єднати результати двох або більшої кількості операторів SELECT в одну таблицю.
SQL забезпечує таку можливість за допомогою операції UNION.
Розглянемо використання операції UNION для виконання наступного запиту на об’єднання:
Вивести список всіх товарів, ціна яких перевищує 200 грн. або тих, яких було замовлено більш, ніж на 3000 грн. за один раз.
Схема виконання операції UNION
Таблиця
Tovary
|
Kod_Vyrobnyka |
Kod_Tovary |
|
101 204 101 204 |
4100 2844 4101 2845 |
|
101 204 101 204 355 |
4100 2844 4101 2845 7755 |
|
Vyrobnyk |
Tovar |
|
204 204 355 |
2844 2845 7755 |
Першій частині основного запиту відповідає запит, зображений у верхній частині схеми:
49). Вивести список всіх товарів, ціна яких перевищує 200 грн.:
49). SELECT Kod_Vyrobnyka, Kod_Tovaru
FROM Tovary
WHERE Cina > 200
|
Kod_Vyrobnyka |
Kod_Tovary |
|
101 204 101 204 |
4100 2844 4101 2845 |
Аналогічно, другу частину основного запиту можна виконати за допомогою запита, зображеного в нижній частині схеми:
50). Вивести список всіх товарів, яких було замовлено більш, чим на 3000 грн. за один раз:
50). SELECT DISTINCT Vyrobnyk, Tovar
FROM Zamovlennia
WHERE Vartist > 3000
|
Vyrobnyk |
Tovar |
|
204 204 355 |
2844 2845 7755 |
Операція UNION створює одну таблицю результатів запиту, в якій містяться рядки результатів як першої, так і другої частини.
В запитах операції UNION використовується таким чином:
51). Вивести список всіх товарів, ціна яких перевищує 200 грн. або тих, яких було замовлено більш, ніж на 3000 грн. за один раз:
51). SELECT Kod_Vyrobnyka, Kod_Tovaru
FROM Tovary
WHERE Cina > 200
UNION
SELECT DISTINCT Vyrobnyk, Tovar
FROM Zamovlennia
WHERE Vartist > 3000
|
101 204 101 204 355 |
4100 2844 4101 2845 7755 |
Для того, щоб таблиці результатів вибірок можна було об’єднати за допомогою операції UNION, вони повинні задовольняти наступним 3-ом вимогам:
Зауваження:
Імена стовпчиків в двох запитах, які об’єднуються за допомогою операції UNION не обов’язково повинні співпадати:
1-ша таблиця: Kod_Vyrobnyka, Kod_Tovaru
2-га таблиця: Vyrobnyk, Tovar.
Оскільки стовпчики в двох таблицях можуть мати різні імена, - то стовпчики результатів запиту на об’єднання, що повертаються операцією UNION,- безіменні.
Стандарт SQL 1 накладає додаткові обмеження на оператори SELECT, які приймають участь в операції UNION: дозволяються використовувати в списку результуючих стовпчиків секції SELECT лише імена стовпчиків або вказівник на всі стовпчики таблиці (SELECT *) і забороняється використовувати вирази. Але, в більшості сучасних СУБД, це обмеження знято, і в списку результуючих стовпчиків можна використовувати вирази.
Проте в багатьох СУБД не можна включати в оператори SELECT секції GROUP BY або HAVING, а в деяких – не можна використовувати в списку результуючих стовпчиків статистичні функції.
Крім того, є такі СУБД (наприклад, SQL Server), які не підтримують саму операцію UNION.
Оскільки операція UNION об’єднує рядки із двох (чи > кількості) запитів на вибірку, цілком ймовірно, що в результуючій таблиці будуть рядки, що повторюються.
В попередньому прикладі товари 204-2844 та 204-2845 мають ціну понад 200 грн. та їх було замовлено на суму понад 3000 грн. за один раз, тому вони будуть серед результатів як першої, так і другої частини запиту на об’єднання.
За замовчуванням, операція UNION вилучає рядки–дублікати, що повторюються, тому в об’єднаних результатах запиту міститься тільки по одному рядку для товарів 204-2844 та 204-2845.
Якщо в таблиці результатів операції UNION необхідно залишити рядки–дублікати, то одразу після ключового слова UNION потрібно вказати предикат ALL. Тоді цей запит поверне таблицю з рядками–дублікатами:
52). SELECT Kod_Vyrobnyka, Kod_Tovaru
FROM Tovary
WHERE Cina > 200
UNION ALL
SELECT DISTINCT Vyrobnyk, Tovar
FROM Zamovlennia
WHERE Vartist > 3000
|
101 204 101 204 204 204 355 |
4100 2844 4101 2845 2844 2845 7755 |
Зауваження: Опрацювання рядків–дублікатів в операції UNION та в інструкції SELECT здійснюється по-різному.
Оператор SELECT по замовчуванню залишає такі рядки (SELECT АLL). Щоб їх вилучити, необхідно явно вказати предикат DISTINCT.
Операція UNION по замовчуванню вилучає рядки–дублікати. Щоб їх залишити, необхідно явно вказати предикат ALL.
Причина цього полягає в тому, що в SQL в якості установок по замовчуванню вибрані найбільш часто використані варіанти:
Вилучення рядків–дублікатів із таблиці результатів запиту займає багато часу, особливо якщо таблиця містить багато рядків.
Якщо відомо, що операція UNION не поверне рядків–дублікатів, необхідно явно вказувати предикат ALL – тоді запит буде виконуватися набагато швидше.
Секцію ORDER BY не можна використовувати в жодній інструкції SELECT, об’єднаних операцією UNION: немає змісту сортувати результати таких запитів, бо користувач все рівно не побачить їх окремо, в чистому вигляді.
А ось об’єднані результати запитів, які повертаються операцією UNION – можна відсортувати за допомогою секції ORDER BY, яка вказується після останньої інструкції SELECT. Оскільки стовпчики таблиці результатів запиту на об’єднання не мають імен, то в секції ORDER BY треба вказувати номера стовпчиків.
53). Вивести список товарів, ціна яких перевищує 200грн. або тих, яких було замовлено більш ніж на 3000 грн. за один раз; список відсортувати по коду виробника і коду товару:
53). SELECT Kod_Vyrobnyka, Kod_Tovaru
FROM Tovary
WHERE Cina > 200
UNION
SELECT DISTINCT Vyrobnyk, Tovar
FROM Zamovlennia
WHERE Vartist > 3000
ORDER BY 1, 2
|
101 101 204 204 355 ....... |
4100 4101 2844 2845 7755 ......... |
Операція UNION можна використовувати багатократно, щоб об’єднати результати двох або більше запитів:
Таблиця А
Таблиця В
Таблиця С
Таблиця А
|
Дід |
|
Кішка |
|
Мишка |
|
Ріпка |
|
Дід |
|
Кішка |
|
Мишка |
|
Ріпка |
|
Баба |
|
Внучка |
|
Жучка |
Таблиця В
|
Дід |
|
Баба |
|
Внучка |
|
Жучка |
|
Дід |
|
Баба |
|
Внучка |
|
Жучка |
|
Кішка |
|
Мишка |
Таблиця С
|
Дід |
|
Кішка |
|
Мишка |
|
Жучка |
54). SELECT *
FROM А
UNION (SELECT *
FROM В
UNION
SELECT *
FROM С)
Дужки в запиті показують, яка операція UNION повинна виконуватися першою.
Незалежно від того, чи вилучають всі операції UNION рядки–дублікати, чи залишають їх, - порядок виконання інструкцій SELECT не має значення.
Наступні вирази повністю еквівалентні:
Аналогічно, наступні три вирази повністю еквівалентні і повертають дванадцять рядків в таблиці результатів, бо рядки–дублікати залишаються:
Але якщо в запит на об’єднання входить як операція UNION, так і операція UNION ALL, то порядок виконання цих інструкцій SELECT має значення. Якщо вираз
трактувати так
А якщо його трактувати як вираз
Розписати приклад)
Тому завжди необхідно використовувати круглі дужки, щоб вказати послідовність виконання інструкцій в запитах на об’єднання, які містять три або більше інструкцій SELECT.
Правила виконання SQL – запиту на вибірку (остаточний варіант)
Таблиця результатів запиту на вибірку генерується таким чином:
(більш детально, ніж в §3)
На практиці багато запитів зчитують інформацію одразу із кількох таблиць бази даних.
SQL дозволяє отримати такі вибірки за допомогою багато табличних запитів, які з’єднують дані із кількох таблиць.
В цьому параграфі розглянемо такі запити та засоби з’єднання таблиць, які має SQL.
Щоб зрозуміти, як в SQL реалізуються багатотабличні запити, розглянемо простий запит , який з’єднує дані із двох різних таблиць:
55). Вивести список всіх замовлень, тобто номер і вартість замовлення, а також прізвище клієнта, який зробив це замовлення і величину наданого кредиту клієнту.
Чотири елемента даних, які необхідно отримати, зберігаються в двох різних таблицях:
Зауважимо, що між двома цими таблицями існує зв’язок: в кожному рядку стовпчика Клієнт таблиці замовлення міститься ідентифікатор клієнта, який зробив це замовлення і цей ідентифікатор відповідає значенню одного з рядків стовпчика Код_Клієнта таблиці клієнти.
Очевидно, що отримати необхідні результати, в операторі SELECT, необхідно якимось чином врахувати цей зв’язок між таблицями.
Таблиця Clients
|
Kod_Clienta |
Prizv |
….. |
Credit |
…. |
|
……. |
. . . |
|||
Таблиця Zamovlennia
|
No_Zamovl |
…… |
Client |
…… |
Vartist |
… |
|
. . . |
. . . |
Результати запиту
|
No_Zamovl |
Vartist |
Prizv |
Credit |
Розглянемо, як такий запит можна виконати вручну, за допомогою ручки і паперу:
1). Спочатку намалюйте таблицю для результатів запиту, яка містить 4 стовпчики; запишіть імена стовпчиків. Потім – перейдіть до таблиці Zamovlennia і почніть із першого рядка;
2). Знайдіть в рядку для першого замовлення його номер (101) і вартість (68 грн.) і перепишіть ці значення в перший рядок таблиці результатів;
3). В рядку для першого замовлення знайдіть ідентифікатор клієнта, який зробив це замовлення (01). Перейдіть до таблиці Clients і в стовпчику Kod_Clienta знайдіть рядок з ідентифікатором клієнта 01;
4). В даному рядку таблиці Clients знайдіть прізвище клієнта (Дід) і величину його кредиту (100 грн.), та перепишіть ці значення в перший рядок таблиці результатів;
5). Це – тільки, перший рядок таблиці результатів! Верніться до таблиці Zamovlennia і повторюйте процес, починаючи з пункту 2, до тих пір, поки не переберете всі замовлення.
Насправді, СУБД може виконувати цей запит іншим способом, але як би його не виконувати, завжди будуть справедливі дві речі:
Процес формування пар рядків шляхом порівняння вмісту відповідних стовпчиків – називається з’єднанням таблиць.
Таблиця, яка буде результатом процесу з’єднання, і яка містить дані із двох таблиць – називається з’єднанням цих таблиць.
З’єднання на основі точної рівності між значеннями двох стовпчиків називаються з’єднання за рівністю (можуть бути з’єднання і на основі інших видів порівняння стовпчиків – їх розглянемо пізніше).
З’єднання – це основа багатотабличних запитів в SQL.
В реляційній БД вся інформація зберігається у вигляді явних значень у стовпчиках, тому всі можливі відношення між таблицями можна сформувати, співставляючи вміст відповідних стовпчиків.
Таким чином, з’єднання – це потужний (і єдиний!) спосіб виявлення відношень, що існують між даними.
Оператор SELECT для багатотабличного запиту повинен містити умову відбору, яка визначає зв’язок між стовпчиками:
55). Вивести номер і вартість замовлення, а також прізвище клієнта, який зробив це замовлення і величину наданого цьому клієнтові кредиту.
55). SELECT No_Zamovl, Vartist,
Prizv, Credit
FROM Zamovlennia,
Clients
WHERE Client = Kod_Clienta
І. Секція FROM містить дві таблиці.
ІІ. В умові відбору Client = Kod_Clienta порівнюються стовпчики із двох таблиць. Такі стовпчики називаються зв’язаними.
Зауважимо, що в операторі SELECT ніде не сказано як саме повинен виконуватися запит. Там немає вказівок типу „почніть з замовлень” або „почніть з клієнтів”. Замість цього в запиті сказано що ми повинні отримати в результаті, а спосіб отримання в результатів – залишається за СУБД.
Така умова відбору зменшує кількість пар рядків таблиці результатів: „Включити в таблицю результатів запиту тільки ті пари рядків, для яких ідентифікатор клієнта (Client) в таблиці Zamovlennia дорівнює ідентифікатору клієнта (Kod_Clienta) в таблиці Clients”.
12.2.1. Запити з використанням відношення „головна – підлегла” таблиці (предок – нащадок)
Із всіх багатотабличних запитів на практиці найбільш поширені запити до двох таблиць, які зв’язані відношенням „головна – підлегла”. Запит про замовлення і клієнтів – саме такий.
У кожного замовлення (нащадок) є відповідний клієнт (предок), і кожний клієнт (предок) може мати багато замовлень (нащадків). Таблиці, із яких беруться рядки для формування пари рядків результатів запиту, зв’язані відношенням „головна (Clients) – підлегла (Zamovlennia)”.
В реляційній базі даних первинні (головні) і зовнішні ключі створюють відношення предок /нащадок .
|
Clients |
|||
|
Kod_Clienta |
.. |
.. |
.. |
|
Zamovlennia |
||||
|
.. |
.. |
Client |
.. |
.. |
Таблиця, яка містить зовнішній ключ – це нащадок (підлегла таблиця), таблиця, у якій відповідний стовпчик є первинним ключем, - це предок (головна таблиця). Щоб в запиті використати відношення предок/нащадок, потрібно задати умову відбору, у якій первинний ключ порівнюється із зовнішнім ключем:
55). SELECT No_Zamovl, Vartist
Prizv, Credit
FROM Zamovlennia,
Clients
WHERE Client = Kod_Clienta
зовнішній ключ первинний ключ
в таблиці таблиці Clients
Zamovlennia
(для зв’язку із
таблицею Clients)
В SQL зовсім не вимагається, щоб зв’язані стовпчики обов’язково включалися в результати багатотабличного запиту. На практиці вони, як правило, і не включаються – як не було в нашому прикладі.
Це пов’язано з тим, що первинний і зовнішній ключі найчастіше являють собою ідентифікатори, які людині важко запам’ятати, тоді як відповідні назви (міста, організації, прізвища, посади тощо) запам’ятати набагато легше.
Тому цілком зрозуміло, що в секції WHERE для з’єднання двох таблиць використовуються ідентифікатори, а в секції SELECT для створення результуючих стовпчиків – більш зручні для сприйняття назви.
12.2.2. Умова для відбору рядків
В багатотабличному запиті можна комбінувати умову відбору, в якій визначаються зв’язані стовпчики, з іншими умовами, щоб ще більше звузити результати запиту.
56). Нехай потрібно повторити попередній запит, але включити в нього лише замовлення тих клієнтів, чий кредит перевищує 85 грн.
56). SELECT No_Zamovl, Vartist
Prizv, Credit
FROM Zamovlennia, Clients
WHERE Client = Kod_Clienta
AND Credit > 85
Перша умова (Client = Kod_Clienta) відбирає із таблиці Zamovlennia і Clients ті пари рядків, які зв’язані відношенням предок/нащадок; друга умова приводить до подальшого відбору лише тих пар рядків, де кредит перевищує 85 грн.
12.2.3. Кілька зв’язаних стовпчиків
Розглянемо приклад:
|
Tovary |
||||
|
Kod_Tovaru |
Kod_Vyrobnyka |
… |
Cina |
… |
|
Zamovlennia |
||||||
|
N_Z |
… |
Tovar |
Vyrobnyk |
… |
Kilkist |
… |
Стовпчики Tovar і Vyrobnyk в таблиці Zamovlennia утворюють зовнішній ключ для зв’язку із таблицею Tovary і зв’язані із її стовпчиками Kod_Tovaru і Kod_Vyrobnyka відповідно.
Щоб сполучити таблиці на основі такого відношення предок/нащадок, необхідно задати обидві пари зв’язаних стовпчиків.
57). Вивести номери замовлень на товари, а також відповідних товарів.
57). SELECT No_Zamovl, Nazva
FROM Zamovlennia, Tovary
WHERE Tovar = Kod_Tovaru
AND Vyrobnyk = Kod_Vyrobnyka
Умова відбору в цьому запиті визначає, що зв’язаними парами рядків таблиць Zamovlennia і Tovary будуть ті, в яких пари зв’язаних стовпчиків містять одні і ті самі значення.
З’єднання за допомогою кількох стовпчиків, як правило, використовуються в запитах із складеними зовнішніми ключами, - як в наведеному прикладі.
12.2.4. Запити на вибірку до трьох і більше таблиць
SQL дозволяє з’єднувати дані із трьох і більше таблиць, використовуючи ту саму методику, що і для з’єднання даних із двох таблиць.
Ось приклад з’єднання даних із трьох таблиць:
58). Вивести номер замовлення, прізвище клієнта, який його зробив, назву і ціну замовленого товару, для замовлень вартістю понад 100 грн.
58). SELECT No_Zamovl, Prizv,
Nazva, Cina
FROM Zamovlennia,
Clients,
Tovary
WHERE Client = Kod_Clienta
AND Tovar = Kod_Tovaru
AND Vyrobnyk = Kod_Vyrobnyka
AND Cina >100
Як видно із нарисованої схеми, в цьому запиті використовуються два зовнішні ключі таблиці Zamovlennia: в ній стовпчик Client – це зовнішній ключ для таблиці Clients, він зв’язує кожне замовлення з клієнтом, який його зробив.
Стовпчики (Tovar, Vyrobnyk) – це складений зовнішній ключ для таблиці Tovary, вони зв’язують замовлення із замовленим товаром.
12.2.5. Інші з’єднання таблиць за рівністю
Переважна більшість багатотабличних запитів основана на відношеннях предок/нащадок, але в SQL це зовсім не обов’язково; тобто, не вимагається, щоб зв’язані стовпчики являли собою пару: „зовнішній ключ – первинний ключ” у зв’язку „підлегла – головна таблиці”.
Будь-які два стовпчика із двох таблиць можуть бути зв’язані, якщо вони мають сумісні типи даних.
59). Вивести всі замовлення, отримані в той день, коли на роботу був прийнятий будь-який новий працівник.
59). SELECT Zamovlennia . * , Prizv
FROM Zamovlennia, Pracivnyky
WHERE Data_Otrym = Data_Pryjomu
Data_Otrym = Data_Pryjomu
Ці стовпчики не входять ні у зовнішній ключ, ні у первинний ключ; та й взагалі, відношення між рядками у цих парах – в достатній мірі дивне: спільне у цих замовленнях і працівниках лише те, що вони з’явилися у фірмі один і той самий день.
Незважаючи на це, СУБД з готовністю сполучає таблиці так, як це визначено в запиті.
Зв’язані стовпчики, подібні до тих, що наведені в нашому прикладі, створюють між таблицями відношення „багато – до – багатьох” (N : M) :може поступити багато замовлень в день прийому на роботу якогось працівника; також – в день отримання якогось замовлення на роботу може бути прийнято декілька працівників.
Відношення „багато – до – багатьох” відрізняється від відношення „один – до – багатьох”, яке створюється коли в якості зв’язаних стовпчиків використовується первинний і зовнішній ключі.
Більш типова ситуація, коли в таблиці Zamovlennia реєструється працівник, який прийняв це замовлення:
Підведемо підсумки:
Зауважимо, що відмінності цих трьох випадків не впливають на форму запису оператора SELECT, який визначає з’єднання. З’єднання всіх трьох видів записуються одним і тим самим способом: в секції WHERE записується порівняння зв’язаних стовпчиків.
(тета) - з’єднання відношення А по атрибуту Х з відношенням В по атрибуту Y – це результат обчислення виразу.
(A B)
WHERE X Y
де A B – декартовий добуток відношень А і В.
X Y – результат порівняння значень атрибутів Х та Y, який може приймати значення TRUE, FALSE, NULL.
- одна із операцій відношення: =, <>, >, >=, <, <=
Іншими словами, - з’єднання таблиці А по стовпчику Х з таблицею В по стовпчику Y, - це результат вибірки з декартового добутку A B тих типів рядків, для яких умова
X Y
має значення TRUE.
З’єднання за рівністю – це один із частинних випадків - сполучень. Хоча серед всіх з’єднань найбільш поширеними є з’єднання за рівністю, але інколи використовуються з’єднання таблиць за допомогою інших операцій порівняння.
60). Вивести дані про працівників (та відповідні філії), які перевищили план продаж по своїй філії.
- фірма має в різних містах свої філії, в кожній встановлено свій план продаж на одного працівника. Потрібно преміювати кращих працівників
60). SELECT Prizv,
Misto,
(Prodav - Plan_Prod) AS Perev
FROM Pracivnyky, Filii
WHERE /* Filia = Kod_Filii
AND */ Prodav > Plan_Prod
В цьому прикладі використовується > - з’єднання таблиць Pracivnyky та Filii – відбираються лише ті рядки, для яких умова
Prodav > Plan_Prod
набуває значення TRUE.
Додаткова умова
Filia = Kod_Filii
потрібна для того, щоб вибрати працівників, які працюють в даній філії – тобто, в результуючій таблиці в кожній парі рядків рядок з таблиці Pracivnyky буде представляти працівника, який працює у філії, представленій рядком з таблиці Filii.
З’єднання за нерівністю поширені відносно мало, але вони корисні в системах підтримки прийняття рішень та інших системах, які досліджують більш складні взаємозв’язки в базі даних.
Запити, які ми розглядали до цих пір, не використовували тих спеціальних синтаксичних форм, якими багатотабличні запити відрізняються від однотабличних:
12.4.1. Повні імена стовпчиків
61). Вивести дані про клієнтів, які знаходяться в тих самих містах, де фірма має свої філії
61). Неправильно:
SELECT Prizv, Misto
FROM Clients, Filii
WHERE Misto = Misto
61). Правильно:
SELECT Prizv, Clients. Misto
FROM Clients, Filii
WHERE Clients. Misto = Filii. Misto
Повне ім’я стовпчика має синтаксис:
Таблиця, вказана в повному імені стовпчика, повинна бути однією з таблиць, вказаних в секції FROM. В операторі SELECT скрізь замість простих імен стовпчиків можна використовувати повні імена власник.
12.4.2. Вибірка всіх стовпчиків
В багатотабличних запитах SELECT * означає вибірку всіх стовпчиків із усіх таблиць, вказаних в секції FROM:
62). Вивести всю інформацію про працівників та філії, в яких вони працюють.
62). SELECT *
FROM Pracivnyky, Filii
WHERE Filia = Kod_Filii
Багато діалектів SQL, реалізованих в сучасних СУБД, трактують * як універсальне ім’я стовпчика, яке перетворюється у список всіх стовпчиків. В наступному запиті Filii.* означає – всі стовпчики таблиці Filii.
63). Вивести всю інформацію філії разом з прізвищами працівників, які працюють в даній філії.
63). SELECT Filii.*, Prizv
FROM Filii, Pracivnyky
WHERE Filia = Kod_Filii
12.4.3. Самоз’єднання
Деякі багатотабличні запити використовують відношення, що існують всередині однієї з таблиць:
Нехай потрібно:
64). Вивести список імен працівників та їх керівників.
Кожному працівнику відповідає один рядок в таблиці Pracivnyky, а стовпчик Kerivnyk містить ідентифікатор (код) того працівника, який є керівником даного працівника.
Стовпчик Kerivnyk повинен бути зовнішнім ключем для тої таблиці, яка містить дані про керівників. Цей стовпчик і є ним – це зовнішній ключ для самої таблиці Pracivnyky.
Якщо попробувати створити цей запит як будь-який інший запит до двох таблиць з відношенням „первинний ключ – зовнішній ключ”, то він виглядав би таким чином:
64).а). Неправильно:
SELECT Prizv, Prizv
FROM Pracivnyky, Pracivnyky
WHERE Kerivnyk = Kod_Pracivn
- цей оператор SELECT – неправильний із-за подвійного звертання до таблиці Pracivnyky в секції FROM. Можна попробувати вилучити друге звертання до таблиці Pracivnyky:
64).б). Неправильно:
SELECT Prizv, Prizv
FROM Pracivnyky
WHERE Kerivnyk = Kod_Pracivn
Це - синтаксично правильний запит, але він не зробить того, що нам потрібно.
Це – однотабличний запит, тому СУБД почергово переглядає всі рядки таблиці Pracivnyky, щоб знайти ті, які задовольняють умові
Kerivnyk = Kod_Pracivn
Цій умові задовольняють рядки, в яких два стовпчика мають однакові значення, тобто працівник є своїм керівником. Таких рядків немає, тому запит не дасть ніякого результату.
Для того, щоб зрозуміти, як в SQL вирішується ця проблема, уявимо собі що є дві ідентичні копії таблиці Pracivnyky: одна копія називається Prac і містить дані про працівників; інша – називається Keriv і містить дані про керівників.
Стовпчик Kerivnyk таблиці Prac – це зовнішній ключ для таблиці Keriv і наступний запит буде працювати
64).в). Дві окремі таблиці – неправильно, бо їх немає.
SELECT Prac. Prizv, Keriv.Prizv
FROM Prac, Keriv
WHERE Prac. Kerivnyk = Keriv. Kod_Pracivn
Для з’єднання таблиці самої з собою в SQL використовується саме такий підхід: створення „уявної копії”. Замість того, щоб насправді зробити копію таблиці, СУБД просто дозволяє звернутися до неї, використовуючи інше ім’я, яке називають псевдонім таблиці.
Ось той самий запит з використанням псевдонімів таблиць:
64).г). Правильно:
SELECT Prac. Prizv, Keriv.Prizv
FROM Pracivnyky Prac,
Pracivnyky Keriv
WHERE Prac. Kerivnyk = Keriv. Kod_Pracivnyka
В секції FROM для кожної уявленої копії таблиці Pracivnyky призначається псевдонім, який записується одразу після справжнього імені таблиці:
Якщо в секції FROM міститься псевдонім таблиці, то в повному імені стовпчика треба використовувати цей псевдонім, а не справжнє ім’я таблиці.
12.4.4. Псевдоніми таблиць
Псевдоніми таблиць необхідні в запитах на самоз’єднання. Проте, псевдоніми можна використовувати у будь-яких запитах. Наприклад, коли ім’я таблиці дуже довге і громіздко його вживати в повних іменах стовпчиків; або – інший приклад – необхідно звернутися до стовпчика таблиці іншого користувача, тоді повне ім’я такого стовпчика:
Секція FROM виконує дві важливі функції:
Будь-який стовпчик, вказаний в операторі SELECT, повинен належати до однієї з таблиць, вказаних в секції FROM (є виняток для зовнішніх звертань у підлеглих запитах, які будемо розглядати пізніше).
Якщо в секції FROM вказується псевдонім таблиці, то він стає її міткою; в іншому випадку – міткою стає ім’я таблиці в тому вигляді, в якому воно вказане в секції FROM. Єдина вимога до міток таблиць в секції FROM – всі вони повинні відрізнятися одна від одної.
Чим більша кількість таблиць в запиті, тим більший об’єм роботи треба виконати для опрацювання запиту.
В SQL немає ніяких обмежень на кількість таблиць, які сполучаються в одному багатотабличному запиті, але СУБД обмежують цю кількість таблиць (як правило, max = 8 табл.).
На практиці – великі затрати на опрацювання багатотабличних запитів накладають ще сильніші обмеження на кількість таблиць.
Всі запити на вибірку можна поділити на дві категорії:
1)-ша категорія – оперативні запити:
В таких системах час відповіді – це вирішальна величина – користувач вводить один або два елементи даних і йому потрібно отримати відповідь від бази даних протягом однієї чи двох секунд.
Ось типові запити для систем оперативного опрацювання транзакцій:
2)-га категорія – запити, які:
В таких системах результати запиту часто потрібні для прийняття важливих рішень, тому вважаються цілком нормальними запити, які виконуються протягом кількох хвилин або кількох годин.
Ось типові запити, пов’язані із прийняттям рішень:
До якої би категорії не відносився запит – єдине правило: чим менше таблиць (в декартовому добутку) – тим швидше буде виконуватися запит.
Звідси – рекомендація: слід уникати зайвих таблиць в багатотабличних запитах. Методика побудови запитів на вибірку:
Для розуміння того, яку функцію виконує та чи інша секція оператора SELECT (особливо у випадках, коли в сполученні приймають участь багато таблиць чи використовуються складні умови відбору), потрібно насамперед дати більш точне визначення для поняття „з’єднання”.
12.6.1. Декартовий добуток таблиць
З’єднання – це частинний випадок більш загальної комбінації даних із двох таблиць, яка називається декартовий добуток таблиць.
Декартовий добуток двох таблиць – це таблиця, яка складається із всіх можливих пар рядків обидвох таблиць.
Стовпчиками таблиці результатів декартового добутку будуть всі стовпчики першої таблиці, за якими йдуть всі стовпчики другої таблиці.
Якщо створити запит до двох таблиць без секції WHERE , то результатом якраз і буде декартовий добуток двох таблиць. Наприклад, результатом запиту.
65). Вивести всі можливі комбінації клієнтів і працівників.
65). SELECT *
FROM Clients, Pracivnyky
- буде добуток таблиць Clients і Pracivnyky, який містить всі можливі комбінації клієнт/працівник.
Таблиця результатів буде містити 12 рядків (4 клієнта + 3 працівника = 12 комбінацій)
Зауваження
Для з’єднання вказаних таблиць використовується такий самий оператор SELECT, але із секцією WHERE, в якій вказується умова порівняння зв’язаних стовпчиків.
66). Вивести список всі дані клієнтів і працівників, які знаходяться в одному місті.
66). SELECT Clients. Prizv,
Pracivnyky. Prizv, *
Clients. Misto,
FROM Clients, Pracivnyky
WHERE Clients. Misto = Pracivnyky. Misto.
Результатом запиту буде:
12.6.2. Вибірка (вилучення тих рядків, які не задовольняють умові відбору)
Приклади 65 і 66 показують важливий зв’язок між з’єднанням і декартовим добутком:
З’єднання двох таблиць – це декартовий добуток цих таблиць, з якого вилучені деякі рядки. Вилучають саме ті рядки, які не задовольняють умові порівняння зв’язаних стовпчиків (умові відбору) для даного з’єднання.
Звичайно, в жодній СУБД з’єднання таблиць не виконується наведеним способом – бо створення тимчасової таблиці декартового добутку – потребує дуже великих затрат.
Цей спосіб – лише пояснює, що ми отримуємо в результаті з’єднання, а не описує як саме в СУБД виконується з’єднання.
Операція з’єднання в SQL поєднує інформацію із двох таблиць, формуючи пари зв’язаних рядків із цих двох таблиць. Таблицю результатів з’єднання утворюють пари тих рядків із різних таблиць, у яких в зв’язаних стовпчиках міститься однакові значення. Якщо ж рядок однієї з таблиць не має відповідної пари у іншій таблиці, то з’єднання може привести до несподіваних наслідків.
Це ілюструють наведені дані приклади.
Працівник Жучка ще не отримав призначення в жодну філію, і його рядок має значення NULL в стовпчику Filia.
67). Вивести список працівників і філій, в яких вони працюють.
67). SELECT Prizv, Filia
FROM Pracivnyky
68).Вивести список працівників і міст, в яких вони працюють.
68). SELECT Prizv, Misto
FROM Pracivnyky, Filii
WHERE Filia = Kod_Filii
На перший погляд, обидва запити 67) і 68) – повинні давати однакову кількість рядків, але в результаті першого запиту отримали 6 рядків, а в результаті другого – лише 5. Чому?
Значення NULL в стовпчику Filia для працівника Жучка не співпадає з жодним ідентифікатором філії в таблиці Filii, тому рядок для Жучки в таблиці Pracivnyky залишається без пари. В результаті вона „випадає” із з’єднання.
Таким чином, стандартне SQL - з’єднання може привести до втрати інформації, коли таблиці, які з’єднуються містять незв’язані рядки.
Можливо, що завдання 68) вимагає тих результатів, які дає наступний запит.
69). Вивести повний список працівників і міст, в яких вони працюють.
69). SELECT Prizv, Misto
FROM Pracivnyky, Filii
WHERE Filia * = Kod_Filii
Зауваження:
Операція * = допускається в СУБД SQL Server. В інших СУБД її може і не бути.
Наведені результати запиту отримаються за допомогою принципово іншої операції з’єднання, яка називається зовнішнє з’єднання таблиць (в прикладі 69) воно позначається *=). Ті з’єднання, які розглядалися до цих пір, називаються внутрішнє з’єднання таблиць.
В стандарті SQL1 визначене лише внутрішнє з’єднання там де ще не було. Але оскільки зовнішні з’єднання – зрозуміла і корисна частина реляційної моделі бази даних і реалізоване в багатьох промислових СУБД (в т. ч. SQL Server, Oracle, Sybase, Informix тощо), то зовнішні з’єднання увійшли в стандарт SQL2. Згідно стандарту SQL2, запит в прикладі 69 треба записати так:
70). Вивести повний список працівників і міст, в яких вони працюють:
форма запису:
70). SELECT Prizv, Misto
FROM Pracivnyky, LEFT JOIN Filii
ON Filia = Kod_Filii
12.7.1. Повне зовнішнє з’єднання
Розглянемо таблиці:
Таблиця Girls
|
Prizv |
Misto |
|
Баба Внучка Жучка Кішка Мишка |
Борислав NULL Дрогобич Дрогобич Трускавець |
Таблиця Boys
|
Prizv |
Misto |
|
Дід Іванов Петров Сідоров Колобок |
Борислав Борислав NULL Дрогобич Стебник |
Таблиця Girls містить список 5 дівчат і міста, в яких вони живуть; Таблиця Boys містить аналогічні дані про 5 хлопців.
Інформації про міста, в якому живе Внучка; та місто, в якому живе Петров – немає.
Призначення міток , , для рядків таблиць – буде пояснено пізніше.
Щоб знайти пари дівчат і хлопців, що живуть в одному і тому самому місті, можна використати наступний запит, який формує внутрішнє з’єднання двох таблиць:
71). Вивести список дівчат і хлопців, які живуть в одних і тих самих містах.
форма запису:
71). SELECT *
FROM Girls, Boys
WHERE Girls. Misto = Boys. Misto
Таблиця результатів (внутрішнього; повного зовнішнього, лівого зовнішнього, правого зовнішнього) з’єднання.
|
Girls. Prizv |
Girls. Misto |
Boys. Prizv |
Boys. Misto |
|
Баба |
Борислав |
Дід |
Борислав |
|
Баба |
Борислав |
Іванов |
Борислав |
|
Жучка |
Дрогобич |
Сідоров |
Дрогобич |
|
Кішка |
Дрогобич |
Сідоров |
Дрогобич |
|
Внучка |
NULL |
NULL |
NULL |
|
Мишка |
Трускавець |
NULL |
NULL |
|
NULL |
NULL |
Петров |
NULL |
|
NULL |
NULL |
Колобок |
Стебник |
|
Назва з’єднання |
Множина рядків в таблиці результатів |
Позначення в стандарті SQL2 |
|
Внутрішнє з’єднання: |
[INNER] JOIN |
|
|
Ліве (зовнішнє) з’єднання: |
LEFT [OUTER] JOIN |
|
|
Праве (зовнішнє) з’єднання: |
RIGHT [OUTER] JOIN |
|
|
Повне зовнішнє з’єднання: |
FULL [OUTER] JOIN |
|
|
Розширений запит на з’єднання: |
UNION JOIN |
|
|
Перехрестя з’єднання: |
декартовий добуток Girls Boys - 4 стовпчики 25 рядків (внутрішнє з’єднання без умови відбору) |
CROSS JOIN |
Внутрішнє з’єднання дасть чотири рядки в таблиці результатів запиту – їм відповідають мітки в таблицях Girls та Boys та мітки в таблиці результатів.
Зверніть увагу, що двоє дівчат (Внучка і Мишка) і двоє хлопців (Петров і Колобок) не представлені в таблиці результатів внутрішнього з’єднання – бо ці рядки не мають пари в іншій таблиці і тому відчутні в таблиці результатів.
Два незв’язних рядки (Мишка і Колобок) мають конкретні значення в стовпчиках Misto, але вони не співпадають з жодним містом в іншій таблиці.
Два інших незв’язних рядка (Внучка і Петров) мають значення NULL в стовпчиках Misto, а за правилами SQL значення NULL не дорівнюють ніякому іншому значенню (навіть іншому значенню NULL).
Нехай потрібно вивести список пар дівчина/хлопець, які живуть в одних і тих самих містах і включити в нього всіх дівчат і хлопців, які не мають відповідної пари.
Цей результат дає повне зовнішнє з’єднання таблиць Girls і Boys.
|
Схема побудови зовнішнього з’єднання |
|
Створити внутрішнє з’єднання двох таблиць |
|
звичайним способом. |
|
Кожний рядок першої таблиці, який не має |
|
зв’язку з жодним рядком другої таблиці, |
|
добавити в результати запиту, присвоюючи |
|
всім стовпчикам другої таблиці значення NULL. |
|
Кожний рядок другої таблиці, який не має |
|
зв’язку з жодним рядком першої таблиці, |
|
добавити в результати запиту, присвоюючи всім |
|
стовпчикам першої таблиці значення NULL. |
|
Результуюча таблиця і буде зовнішнім з’єднанням двох |
|
таблиць. |
Пунктам і наведеної Схеми побудови зовнішнього з’єднання відповідають мітки для рядків таблиць Girls, Boys та Таблиці результатів з’єднання.
Ось оператор SELECT, який приводить до зовнішнього з’єднання:
72). Вивести список дівчат і хлопців, які живуть в одних і тих самих містах, включаючи тих дівчат і хлопців, хто не має пари.
72). SELECT *
FROM Girls, Boys
WHERE Girls.Misto *=* Boys.Misto
Зауваження – цей запит використовує операцію *=*, яка визначає зовнішнє з’єднання в MS SQL Server. Цієї операції немає в інших СУБД.
72). SELECT *
FROM Girls FULL JOIN Boys
ON Girls.Misto = Boys.Misto
Зовнішнє з’єднання двох таблиць містить 8 рядків.
4 рядки ідентичні рядкам внутрішнього з’єднання цих таблиць – їм відповідають мітки в таблицях Girls та Boys та мітки в таблиці результатів, а також дія Схеми побудови зовнішнього з’єднання. 2 інших рядки, для Внучки і Мишки,- це незв’язані рядки таблиці Girls. Ці рядки були розширені значеннями NULL, тобто співставленні з уявним рядком таблиці Boys, який містить лише значення NULL, і добавлені в результати запиту. Цим рядкам відповідають мітки в таблиці Girls та мітки в таблиці результатів, а також дія схеми побудови зовнішнього з’єднання.
2 останніх рядка, для Петрова і Колобка - це незв’язані рядки таблиці Boys. Ці рядки також були розширені значеннями NULL (співставленні з уявним рядком таблиці Girls, який містить лише значення NULL) і добавлені в результати запиту. Цим рядкам відповідають мітки в таблиці Boys та мітки в таблиці результатів, а також дія Схеми побудова зовнішнього з’єднання.
Як видно із цього прикладу, зовнішнє з’єднання – це з’єднання, яке „зберігає інформацію”. Кожний рядок таблиці Girls представлений в таблиці результатів запиту (деякі – навіть більш ніж один раз). Аналогічно , кожний рядок таблиці Boys представлений в таблиці результатів (деякі, знову ж таки, – більш ніж один раз).
Таке зовнішнє з’єднання називається повним зовнішнім з’єднанням. Воно - симетричне по відношенню до обидвох таблиць: Girls і Boys.
12.7.2. Ліве і праве зовнішнє з’єднання
Є ще два типи зовнішніх сполучень, які не симетричні відносно двох таблиць.
Ліве зовнішнє з’єднання отримується, якщо виконати пункти і
із числа вказаних в Схемі побудови зовнішнього з’єднання, а пункт
- пропустити. Таким чином, ліве зовнішнє з’єднання включає всі незв’язані рядки першої (лівої) таблиці, доповнюючи їх значеннями NULL, але не включає незв’язані рядки другої (правої) таблиці.
Ось ліве зовнішнє з’єднання таблиць Girls і Boys:
73). Вивести список дівчат і хлопців, які живуть в одних і тих самих містах; а також дівчат, які не мають пари.
73). SQL Server:
SELECT *
FROM Girls, Boys
WHERE Girls.Misto *= Boys.Misto
73). стандарт SQL 2:
SELECT *
FROM Girls LEFT JOIN Boys
ON Girls.Misto = Boys.Misto
Таблиця результатів цього запиту містить 6 рядків: всі пари дівчина/хлопець із одних і тих самих міст (їм відповідають мітки в таблиці результатів) та дівчата, які не мають пари (їм відповідають мітки в таблиці результатів.
Праве зовнішнє з’єднання отримується, якщо виконати пункти і
із тих, які вказані в Схемі побудови зовнішнього з’єднання, а пункт
– пропустити.
Таким чином, праве зовнішнє з’єднання включає всі незв’язані рядки другої (правої) таблиці, доповнюючи їх значеннями NULL, але не включає незв’язані рядки першої (лівої) таблиці.
Ось праве зовнішнє з’єднання таблиць Girls і Boys:
74). Вивести список дівчат і хлопців, які живуть в одних і тих самих містах; а також хлопців, які не мають пари.
74). SQL Server:
SELECT *
FROM Girls, Boys
WHERE Girls.Misto =* Boys.Misto
74). стандарт SQL2:
SELECT *
FROM Girls RIGHT JOIN Boys
ON Girls.Misto = Boys.Misto
Цей запит також дає таблицю результатів із 6 рядків: всі пари дівчина/хлопець із одних і тих самих міст (їм відповідають мітки в таблиці
результатів) та дівчата, які не мають пари (їм відповідають мітки ). На цей
раз в таблицю не ввійшли дівчата, що не мають пари.
Таблиця, всі рядки якої включаються в з’єднання, називається головною, а таблиця, відсутні елементи якої заповнюються значеннями NULL, називається допоміжною.
На практиці ліве і праве з’єднання більш корисні та частіше використовуються, ніж повне зовнішнє з’єднання, особливо якщо таблиці зв’язані за допомогою зовнішнього і первинного ключа.
12.7.3. Системи запису зовнішнього з’єднання
Оскільки поняття зовнішнього з’єднання не ввійшло в стандарт SQL-1, то виробники тих СУБД, в яких це поняття було реалізоване, використовували самі різні системи запису в своїх діалектах SQL.
Наприклад, та система, яку ми розглядали (*=*) – використовується в SQL Server.
В цій системі зовнішнє з’єднання називається символом *, який добавляється до знаку операції відношення в умові порівняння зв’язаних стовпчиків секції WHERE, яка визначає умову з’єднання.
Для позначення повного зовнішнього з’єднання * ставиться зліва і справа від операції:
WHERE
Для позначення лівого зовнішнього з’єднання * ставиться лише зліва:
WHERE
а правого зовнішнього з’єднання – справа:
WHERE
( , - це зв’язані стовпчики із двох таблиць і
відповідно)
Так само позначаються і інші види зовнішніх - сполучень і символ * добавляється до знаку відповідної операції порівняння; наприклад
WHERE
В СУБД Oracle також є зовнішні з’єднання, але вони позначаються по-іншому: зовнішнє з’єднання в секції WHERE позначається знаком + в ( ), тобто „(+)” – ці символи записуються справа від імені стовпчика тої таблиці, в яку добавляються рядки із значеннями NULL (тобто, допоміжної таблиці). Ліве зовнішнє з’єднання позначається так:
WHERE
а праве зовнішнє з’єднання – так:
WHERE
Повне зовнішнє з’єднання в Oracle не підтримується.
Зверніть увагу: знак (+) знаходиться на протилежній стороні по відношенню до тої, на якій знаходиться *.
Такі системи позначень мають недоліки:
= спочатку аналізується секція FROM і формується декартовий добуток таблиць, вказаних в ній,
= потім – із таблиці декартового добутку вилучаються рядки, які не дозволяють умові відбору секції WHERE.
Але декартовий добуток взагалі не містить рядків із значеннями NULL, які входять у зовнішнє з’єднання!
потрібно вносити корективи в схему, яка пояснює що саме буде отримано в результаті запиту:
= спочатку – проаналізувати секцію WHERE – чи необхідно виконувати зовнішнє з’єднання
. . . . .
Ці недоліки вдалося подолати в стандарті SQL2.
В стандарті SQL 2 з’єднання визначаються в секції FROM – з досконалим синтаксисом, який дозволяє точно вказати, як саме таблиці повинні сполучатися в одному запиті.
Механізм підтримки сполучень стандарту SQL 2 має такі переваги:
Цих переваг вдалося досягти за рахунок ускладнення раніше найпростішої секції оператора SELECT – секції FROM. Це привело до значного розширення можливостей запитів в SQL 2 в цілому: стало можливим виконувати операції над результатами запиту як над множинами (об’єднання, перетин, різниця таблиць) і застосовувати вирази, які використовують рядки, таблиці і підлеглі запити.
Синтаксична діаграма
секції FROM (спрощений варіант)
в стандарті SQL 2.
Вираз природнього з’єднання:
Вираз з’єднання:
Вираз перехресного з’єднання:
Вираз розширеного запиту на з’єднання:
І це – лише сильно спрощений варіант секції FROM в стандарті SQL2!
Вивчити всі випадки, вказані на цій синтаксичній діаграмі, найлегше – розглядаючи по черзі кожний тип з’єднання.
12.8.1. Внутрішні з’єднання в стандарті SQL2
Внутрішнє з’єднання таблиць Girls і Boys за стандартом SQL1 можна записати так:
SELECT *
FROM Girls, Boys
WHERE Girls.Misto = Boys.Misto
В стандарті SQL2 це – також допустима конструкція (від такого синтаксису не можна було відмовлятися, бо він використовується в мільйонах працюючих програм). Але цей оператор можна записати ще і таким чином:
SELECT *
FROM Girls JOIN Boys
ON Girls.Misto = Boys.Misto
SELECT *
FROM Girls [INNER] JOIN Boys
ON Girls.Misto = Boys.Misto
По замовчуванню з’єднання двох таблиць в стандарті SQL2 вважається внутрішнім – тому слово INNER можна не вказувати.
- тепер дві таблиці сполучаються явно за допомогою операції JOIN, а умова відбору, яка визначає з’єднання записується в секції ON всередині секції FROM. В умові відбору можна вказати будь-які критерії порівняння рядків двох таблиць, що сполучаються.
Припустимо, що таблиці Girls та Boys мають по три стовпчика: Prizv, Misto, Vik.
75). Вивести список дівчат та хлопців, які живуть в одних і тих самих містах і одного і того самого віку.
75). а. SQL2
SELECT *
FROM Girls [INNER] JOIN Boys
ON (Girls.Misto=Boys.Misto
AND Girls.Vik = Boys.Vik)
SELECT *
FROM Girls JOIN Boys
ON (Girls.Misto=Boys.Misto
AND Girls.Vik = Boys.Vik)
По замовчуванню з’єднання двох таблиць в стандарті SQL2 вважається внутрішнім – тому слово INNER можна не вказувати.
- в таких простих двох табличних з’єднаннях вміст секції WHERE просто переноситься в секцію ON, - тобто, нічого нового в мову SQL не добавляється.
Переваги такого синтаксису проявляються при сполученні трьох та більше таблиць – за допомогою секції ON можна керувати процесом опрацювання таких багатотабличних сполучень.
Стандарт SQL2 допускає ще один варіант запиту на просте внутрішнє з’єднання таблиць Girls та Boys.
Оскільки зв’язані стовпчики цих таблиць мають однакові імена і порівнюються на рівність (самий вживаний часто випадок), то запит 75) можна записати так:
75). б. SQL2
SELECT *
FROM Girls [INNER] JOIN Boys USING (Misto, Vik)
SELECT *
FROM Girls JOIN Boys USING (Misto, Vik)
По замовчуванню з’єднання двох таблиць в стандарті SQL2 вважається внутрішнім – тому слово INNER можна не вказувати.
Це повністю <=> секції ON, в якій кожна пара зв’язаних стовпчиків задається явно.
Якщо зв’язані стовпчики мають різні імена в таблицях, що сполучаються, то необхідно використовувати секцію ON (або секцію WHERE).
Секцію ON треба використовувати також тоді, коли з’єднання виконується не по рівності зв’язаних стовпчиків, наприклад:
76). Вивести список дівчат та хлопців, які живуть в одних і тих самих містах і хлопці – старші дівчат.
76). SQL2
SELECT *
FROM Girls [INNER] JOIN Boys
ON (Girls.Misto=Boys.Misto
AND Girls.Vik < Boys.Vik)
SELECT *
FROM Girls JOIN Boys
ON (Girls.Misto=Boys.Misto
AND Girls.Vik < Boys.Vik)
По замовчуванню з’єднання двох таблиць в стандарті SQL2 вважається внутрішнім – тому слово INNER можна не вказувати.
Нарешті, є ще один, останній варіант запиту 75).
З’єднання двох таблиць, в якому зв’язані стовпчики мають однакові імена називається природнім з’єднанням – бо звичайно це справді самий „природній” спосіб з’єднання. Запит 75) можна за допомогою природнього з’єднання записати так:
75). Вивести список дівчат та хлопців, які живуть в одних і тих самих містах і одного і того самого віку.
75). в. SQL2
SELECT *
FROM Girls NATURAL [INNER] JOIN Boys
SELECT *
FROM Girls NATURAL JOIN Boys
По замовчуванню з’єднання двох таблиць в стандарті SQL2 вважається внутрішнім – тому слово INNER можна не вказувати.
- якщо задано ключове слово NATURAL, то секції ON та USING можна не вказувати – бо в природньому сполученні точно визначена умова відбору – порівнюються на рівність всі стовпчики з однаковими імена в обидвох таблицях.
12.8.2. Зовнішні з’єднання в стандарті SQL2
Повне зовнішнє з’єднання таблиць Girls та Boys (без стовпчиків Vik) створюється запитом:
SELECT *
FROM Girls FULL OUTER JOIN Boys
ON Girls.Misto = Boys.Misto
Ключове слово OUTER, так само, як і ключове слово INNER – в стандарті SQL2 – не обов’язкові. Тому цей запит можна записати так:
SELECT *
FROM Girls FULL JOIN Boys
ON Girls.Misto = Boys.Misto
- по слову FULL СУБД відомо, що потрібне зовнішнє з’єднання. Таблиця результатів містить набори рядків , і .
В стандарті SQL2 для зовнішніх сполучень допускаються ті самі варіації, що і для внутрішніх.
Цей запит можна записати так:
SELECT *
FROM Girls NATURAL FULL [OUTER] JOIN Boys
SELECT *
FROM Girls NATURAL FULL JOIN Boys
Ключове слово OUTER, так само, як і ключове слово INNER – в стандарті SQL2 – не обов’язкові.
Або так:
SELECT *
FROM Girls FULL [OUTER] JOIN Boys USING (Misto)
SELECT *
FROM Girls FULL JOIN Boys USING (Misto)
Ключове слово OUTER, так само, як і ключове слово INNER – в стандарті SQL2 – не обов’язкові.
Для лівого зовнішнього з’єднання замість слова FULL використовується слово LEFT:
SELECT *
FROM Girls LEFT [OUTER] JOIN Boys
ON Girls.Misto = Boys.Misto
SELECT *
FROM Girls LEFT JOIN Boys
ON Girls.Misto = Boys.Misto
Ключове слово OUTER, так само, як і ключове слово INNER – в стандарті SQL2 – не обов’язкові.
SELECT *
FROM Girls NATURAL LEFT [OUTER] JOIN Boys
SELECT *
FROM Girls NATURAL LEFT JOIN Boys
Ключове слово OUTER, так само, як і ключове слово INNER – в стандарті SQL2 – не обов’язкові.
SELECT *
FROM Girls LEFT [OUTER] JOIN Boys USING (Misto)
SELECT *
FROM Girls LEFT JOIN Boys USING (Misto)
Ключове слово OUTER, так само, як і ключове слово INNER – в стандарті SQL2 – не обов’язкові.
- таблиця результатів містить набори рядків і .
Для правого зовнішнього з’єднання замість слів FULL чи LEFT використовується слово RIGNT:
SELECT *
FROM Girls RIGNT [OUTER] JOIN Boys
ON Girls.Misto = Boys.Misto
SELECT *
FROM Girls RIGNT JOIN Boys
ON Girls.Misto = Boys.Misto
Ключове слово OUTER, так само, як і ключове слово INNER – в стандарті SQL2 – не обов’язкові.
SELECT *
FROM Girls NATURAL RIGNT [OUTER] JOIN Boys
SELECT *
FROM Girls NATURAL RIGNT JOIN Boys
Ключове слово OUTER, так само, як і ключове слово INNER – в стандарті SQL2 – не обов’язкові.
SELECT *
FROM Girls RIGNT [OUTER] JOIN Boys USING (Misto)
SELECT *
FROM Girls RIGNT JOIN Boys USING (Misto)
Ключове слово OUTER, так само, як і ключове слово INNER – в стандарті SQL2 – не обов’язкові.
- таблиця результатів містить набори рядків та .
12.8.3. Перехресні з’єднання і розширені запити на з’єднання в SQL2
Перехресне з’єднання створює декартовий добуток двох таблиць:
SELECT *
FROM Girls CROSS JOIN Boys
- таблиця результатів буде містити всі можливі пари рядків із двох таблиць (кожний із N рядків однієї таблиці по черзі співставляється з кожним із K рядків іншої таблиці – результат містить N x K рядків).
В декартовому добутку немає ніяких зв’язаних стовпчиків чи умов відбору, тому в перехресних з’єднаннях секції ON та USING не допускаються.
Перехресні з’єднання не добавляють нічого нового в мову SQL – ті самі результати можна отримати за допомогою внутрішнього з’єднання, якщо не задавати в ньому умови відбору. Тому наведений запит можна записати ще і таким чином:
SELECT *
FROM Girls, Boys
На практиці перехресні з’єднання рідко використовуються – як правило, лише в складі інших запитів, які повинні видати якісь підсумкові дані.
Розширений запит на з’єднання
SELECT *
FROM Girls UNOIN JOIN Boys
Таблиця результатів розширеного запиту на з’єднання
|
Girls. Prizv |
Girls. Misto |
Boys. Prizv |
Boys. Misto |
|
Баба |
Борислав |
NULL |
NULL |
|
Внучка |
NULL |
NULL |
NULL |
|
Жучка |
Дрогобич |
NULL |
NULL |
|
Кішка |
Дрогобич |
NULL |
NULL |
|
Мишка |
Трускавець |
NULL |
NULL |
|
NULL |
NULL |
Дід |
Борислав |
|
NULL |
NULL |
Іванов |
Борислав |
|
NULL |
NULL |
Петров |
NULL |
|
NULL |
NULL |
Сідоров |
Дрогобич |
|
NULL |
NULL |
Колобок |
Стебник |
На завершення порівняємо набори результатів, які отримуються при виконанні сполучень різних видів (див. cхему далі).
При сполученні двох таблиць Т1, яка містить N рядків, і Т2, яка містить K рядків, виконується наступне:
CROSS JOIN
Набори результатів для різних видів з’єднань в SQL2
[INNER] JOIN
LEFT [OUTER] JOIN
RIGHT [OUTER] JOIN
FULL [OUTER] JOIN
UNION JOIN
12.8.4. Багатотабличні з’єднання в стандарті SQL2.
Однією з найбільших переваг розширеного синтаксису секції FROM є те, що забезпечується чітка специфікація для сполучень трьох і більше таблиць.
При побудові таких складних сполучень будь-який вираз з’єднання із тих, які були вказані на синтаксичній діаграмі секції FROM і описані в попередніх §§ - може бути взятий в круглі дужки, і використаний для створення інших виразів з’єднання, так, ніби цей вираз – звичайна таблиця.
Тобто, так само, як SQL1 за допомогою круглих дужок дозволяє комбінувати різні арифметичні операції + - * / , стандарт SQL2 дає можливість створювати складні вирази для сполучень.
Для прикладу багато табличного з’єднання, припустимо, що до таблиць Girls і Boys добавлена нова таблиця Parents (батьки) яка має три стовпчики:
Child – відповідає стовпчику Prizv в таблицях Girls і Boys;
Type – приймає значення „T”(тато) або „M” (мама);
Name – ім’я тата чи мами.
Деякий рядок таблиці Girls чи таблиці Boys може мати два зв’язаних рядки в таблиці Parents, один з яких визначає маму, а інший – тата, або може мати тільки один зв’язаний рядок, або може взагалі не мати зв’язаних рядків, якщо відсутні дані про батьків цієї дитини.
В таблицях Girls, Boys та Parents в сукупності міститься достатньо широкий набір даних – щоб вивчати багатотабличні з’єднання.
77). Вивести список всіх дівчат та їх мам, а також хлопців, які живуть в тому самому місті, що і дівчата.
SELECT Girls. Prizv, Name, Boys. Prizv
FROM (Girls INNER JOIN Parents ON Parents. Child = Girls. Prizv)
INNER JOIN Boys ON Girls. Misto = Boys. Misto
WHERE Type = ‘M’
Оскільки обидва з’єднання - внутрішні, то дівчата, які не мають зв’язаних рядків в таблицях Parents і Boys, в таблицю результатів запиту не попадуть.
Якщо модифікувати першу частину запиту в ліве зовнішнє з’єднання, то можна буде включити в таблицю результатів рядки, що відповідають дівчатам, для яких відсутня інформація про їх мам:
78). Вивести список всіх дівчат та їх мам (а також дівчат, для яких відсутня інформація про їх мам), та хлопців, які проживають в тому самому місті, що і дівчата:
SELECT Girls. Prizv, Name, Boys. Prizv
FROM (Girls LEFT JOIN Parents ON Parents. Child = Girls. Prizv)
INNER JOIN Boys ON Girls. Misto = Boys. Misto
WHERE (Type = ‘M’)
OR (Type IS NULL)
- в результатах цього запиту все ще відсутні дівчата, в одному місті з якими не живе ніхто з хлопців. Якщо їх потрібно включити в запит, необхідно і друге з’єднання зробити зовнішнім:
79). Вивести список всіх дівчат та їх мам (а також дівчат, для яких відсутня інформація про їх мам), та хлопців, які проживають в тому самому місті, що і дівчата, а також дівчат, які не мають пари:
SELECT Girls. Prizv, Name, Boys. Prizv
FROM (Girls LEFT JOIN Parents ON Parents. Child = Girls. Prizv)
LEFT JOIN Boys ON Girls. Misto = Boys. Misto
WHERE (Type = ‘M’)
OR (Type IS NULL)
Слід звернути увагу на одну проблему, яку створює ліве зовнішнє з’єднання таблиць Girls Parents: якщо для якоїсь дівчини відсутня інформація про її маму, то не тільки стовпчик Name, а і стовпчик Type буде мати значення NULL. Тому порівняння
WHERE Type = ‘M’
для таких рядків дасть значення NULL і вони не будуть включені в результати запиту. Щоб вирішити цю проблему, в секцію WHERE добавлене ще одне порівняння на співпадання стовпчика Type із значенням NULL.
Ще один приклад:
80). Вивести пари дівчина/хлопець із одного і того самого міста; включити в таблицю результатів ім’я тата хлопця та ім’я мами дівчини (якщо вони відомі).
Такий запит потребує 4-ох табличного з’єднання (таблиці Boys, Girls і дві копії таблиці Parents: одна для з’єднання з таблицею Boys для того, щоб отримати імена батьків, а інша – для з’єднання з таблицею Girls, щоб отримати імена мам.
Потрібно буде включити в результати запиту всі пари дівчина / хлопець із одних і тих самих міст, навіть ті, в яких або дівчина або хлопець не мають зв’язних рядків з таблицею Parents. В цьому запиті прийдеться використовувати два зовнішніх з’єднання – між таблицями Boys і Parents та між таблицями Girls і Parents.
Згідно стандарту SQL2 цей запит буде виглядати таким чином:
80). SELECT Girls. Prizv, Mothers. Name, Boys. Prizv, Fathers. Name
FROM (Girls LEFT JOIN Parents AS Mothers
ON ((Mothers. Child = Girls. Prizv)
AND (Mothers. Type = ‘M’)))
INNER JOIN (Boys LEFT JOIN Parents AS Fathers
ON ((Fathers. Child = Boys. Prizv)
AND (Fathers. Type = ‘T’)))
USING (Misto)
В цьому запиті проблема перевірки стовпчика Type вирішена по-іншому: перевірка переміщена із секції WHERE в секцію ON обидвох зовнішніх сполучень. В цьому випадку стовпчик Type буде перевірятися на етапі побудови кожного зовнішнього з’єднання, коли рядки, розширені значеннями NULL, ще не добавлені в таблицю результатів – тому перевірка
Type IS NULL – зайва.
Оскільки таблиця Parents зустрічається в секції FROM двічі в різних ролях, потрібно призначити їй два різних псевдоніми (Mothers та Fathers), щоб в секції SELECT можна було вказати правильні стовпчики.
Як видно із прикладу, - запит за стандартами SQL2 точно і одночасно вказує, що повинна виконати СУБД.
Немає ніякої неясності щодо порядку з’єднання таблиць чи щодо того, які з’єднання є зовнішніми, а які – внутрішні.
Всі інші секції оператора SELECT (приклад, WHERE чи ORDER BY) можуть так само використовуватися і з розширеним синтаксисом секції FROM. Зв’язок між секціями залишається тим самим, що і раніше. Спочатку виконуються дії, вказані в секції FROM, в т.ч. всі з’єднання чи запити на з’єднання. Умови з’єднання, вказані в секціях USING чи ON, трактуються як частина того конкретного з’єднання, для якого вони визначаються.
Коли виконання дій в секції FROM закінчується, до таблиці результатів застосовуються умови відбору, вказані в секції WHERE, і т.д.
Таким чином, в секціях ON чи USING вказуються умови відбору, які застосовуються до окремих сполучень; в секції WHERE вказується умова відбору, яка застосовується до результуючої таблиці всіх цих сполучень =>
Коли з’єднання одне (сполучаються лише дві таблиці) – немає різниці, де вказувати умову відбору в секції WHERE чи в секції ON.
Коли ж в запиті – більш ніж одне з’єднання (сполучаються більш ніж дів таблиці) – то ефективніше буде вказувати умову в секції ON – тоді вона буде застосовуватися ще до добавлення в таблицю результатів рядків, розширених значеннями NULL.
В SQL є поняття підлеглого запиту, механізм підлеглих запитів дозволяє використовувати результати одного запиту як складову частину іншого.
Хоча підлеглі запити не так широко відомі, як з’єднання є причини, за якими підлеглі запити відіграють таку важливу роль.
Спочатку розглянемо підлеглі запити і їх використання в стандарті SQL1, потім – розширені можливості підлеглих запитів, які появилися в стандарті SQL2.
Підлеглим називається запит, який міститься в секції WHERE*) або HAVING*) іншого оператора SQL
*) зауваження: в стандарті SQL2 допускаються підлеглі запитів в секції FROM: зараз ведуться роботи над створенням стандарту SQL3 – в ньому будуть допустимі підлеглі запити в секції SELECT.
Підлеглі запити дуже корисні – вони дозволяють опрацьовувати запити, виражені через результати інших запитів.
Ось приклад такого запиту:
81). Вивести список філій, для яких плановий об’єм продаж відрізняється від суми планових об’ємів продаж всіх працівників.
- це потрібно, наприклад, для аналізу системи планування фірми (щоб виявити помилки планування).
|
Filii |
||||
|
Kod_ Filii |
Misto |
. . . |
Plan |
. . . |
|
Pracivnyky |
||||||
|
Kod_Prac |
Prizv |
. . . |
Filia |
. . . |
Plan |
. . . |
- в цьому завданні потрібно отримати список міст із таблиці Filii, для яких значення стовпчика Plan задовольняє деякій умові.
Можна припустити, що SQL – оператор SELECT, який відповідає такому запиту, повинен виглядати приблизно так:
SELECT Misto
FROM Filii
WHERE Plan <>
- в цьому запиті величина дорівнює сумі планових об’ємів продаж всіх працівників даної філії.
Як цю суму можна обчислити в такому запиті?
Ми вже знаємо (див. 7. Агрегатні функції), що суму планових об’ємів продаж працівників деякої філії (наприклад, філії з кодом 21) можна отримати таким чином:
SELECT SUM (Plan)
FROM Pracivnyky
WHERE Filia = 21
Як результати цього запиту вставити у попередній замість ?
- очевидно, потрібно спочатку написати перший запит, а потім замінити другим:
81). SELECT Misto
FROM Filii
WHERE Plan <> (SELECT SUM (Plan)
FROM Pracivnyky
WHERE Filia = Kod_Filii)
- це і є правильний запит.
Підлеглий (внутрішній, вкладений) запит обчислює для кожної філії суму планових об’ємів продаж всіх працівників, які працюють в даній філії.
Головний (зовнішній, охоплюючий) запит порівнює план продаж філії з отриманою сумою і, в залежності від результату порівняння, або добавляє цю філію в таблицю результатів запиту або ні.
В сукупності головний і підлеглий запити виражають необхідний запит і вибирають із бази даних потрібну інформацію.
В стандарті SQL1 підлеглі запити завжди використовуються тільки в секції WHERE або HAVING.
В секції WHERE підлеглі запити відбирають окремі рядки, а в секції HAVING – групи рядків.
13.1.1. Що таке підлеглий запит?
Підлеглий запит завжди записується в круглих дужках, - він зберігає структуру SQL - оператора SELECT, який містить секцію FROM і необов’язкові секції WHERE, GROUP BY та HAVING.
Структура цих секцій в підлеглому запиті – та сама, що і в SQL - операторі SELECT; в підлеглому запиті ці секції виконують свої звичайні функції.
Синтаксична діаграма підлеглого запиту:
Між підлеглим запитом і SQL – оператором SELECT є ряд відмінностей:
– Це означає, що в секції SELECT підлеглого запиту завжди вказується лише один результуючий стовпчик.
*) Зауваження: з цього правила є виняток: див. Синтаксичну діаграму, та п.13.2.3. перевірка EXISTS.
– Результати підлеглого запиту використовуються тільки всередині головного запиту і для користувача залишаються „невидимими”, тому немає змісту їх сортувати.
13.1.2. Підлеглі запити в секції WHERE
Найчастіше підлеглі запити вказуються в секції WHERE.
- Тоді підлеглий запит приймає участь в процесі відбору рядків:
82). Вивести список працівників, чий плановий об’єкт продаж становить менше, ніж 10% від планового об’єму продаж всієї фірми.
82). SELECT Prizv
FROM Pracivnyky
WHERE Plan <
(0.1 * (SELECT SUM (Plan)
FROM Filii))
- в цьому випадку підлеглий запит обчислює суму планових об’ємів продаж всіх філій, яка потім помножується на 0,1 (10%). Отримане значення використовується в умові відбору при скануванні таблиці Pracivnyky для пошуку необхідних рядків.
В даному прикладі підлеглий запит для кожного рядка таблиці Pracivnyky повертає одне і те саме значення.
- тому можна було би окремо підрахувати план продаж фірми (наприклад 27500), і спростити умову:
SELECT Prizv
FROM Pracivnyky
WHERE Plan < 27500
Сучасні СУБД саме так і оптимізують запити – якщо підлеглий запит ніяк не зв’язаний із таблицями, вказаними в головному запиті, - тоді підлеглий запит обчислюється лише один раз, його результат запам’ятовується, і це значення використовується в умові відбору рядків головного запиту.
Звичайно, бувають і складніші запити, наприклад: №81:
81). Вивести список філій, для яких плановий об’єм продаж відрізняється від суми планових об’ємів продаж працівників цих філій.
81). SELECT Misto
FROM Filii
WHERE Plan <> (SELECT SUM (Plan)
FROM Pracivnyky
WHERE Filia = Kod_Filii)
- в цьому (більш типовому) випадку результат підлеглого запиту не можна обчислити один раз, бо підлеглий запит повертає різні результати для кожної конкретної філії.
Головний запит вибирає далі із таблиці Filii, а його секція WHERE вибирає ті філії, які будуть включені в таблицю результатів запиту.
Умова, задана в секції WHERE головного запиту, по-черзі застосовується до всіх рядків таблиці Filii і порівнюється значення біжучого рядка в стовпчику Plan із значенням, яке виводиться підлеглим запитом.
Для кожного рядка головного запиту виконується свій підлеглий запит, який виводить суму планових об’ємів продаж для працівників „біжучої” філії. Результатом підлеглого запиту буде одне число, і секція WHERE порівнює його із значенням стовпчика Plan таблиці Filii, відбираючи або відкидаючи біжучу філію на основі результатів порівняння.
Виконання підлеглого запиту повторюється для кожного рядка, який перевіряється секцією WHERE головного запиту.
Умова в підлеглому запиті
Filia = Kod_Filii
Зв’язує таблицю Pracivnyky з таблицею Filii – встановлює відповідність між біжучим рядком підлеглого запиту та біжучим рядком головного => такі запити називаються корельованими (див. п. 13.5)
кореляція Ξ відповідність, зв’язок.
13.1.3. Зовнішні звертання
Дуже часто в підлеглому запиті потрібно звернутися до значення стовпчика в біжучому рядку головного запиту. Розглянемо ще раз:
81). SELECT Misto
FROM Filii
WHERE Plan <> (SELECT SUM (Plan)
FROM Pracivnyky
WHERE Filia = Kod_Filii)
- в цьому операторі SELECT підлеглий запит відіграє наступну роль:
він обчислює суму планових об’ємів продаж для працівників, які працюють в одній конкретній філії, а саме в тій, яка в даний момент перевіряється секцією WHERE головного запиту.
Підлеглий запит виконує перегляд таблиці Pracivnyky. Але стовпчик Kod_Filii в секції WHERE підлеглого запиту – це стовпчик не таблиці Pracivnyky, а таблиці Filii, яка входить в головний запит.
Під час послідовної перевірки рядків таблиці Filii значення стовпчика Kod_Filii в біжучому рядку цієї таблиці використовується для виконання підлеглого запиту.
Стовпчик Kod_Filii в підлеглому запиті – це зовнішнє звертання.
Зовнішнє звертання – це ім’я стовпчика, який не входить в жодну із таблиць, вказаних в секції FROM підлеглого запиту; який належить таблиці, вказані в секції FROM головного запиту.
Значення в стовпчику зовнішнього звертання береться із того рядка, який перевіряється в даний момент головним запитом.
Згідно стандарту SQL1, підлеглий запит є частиною умови відбору в секції WHERE або HAVING.
В §§ 2, 10 були розглянуті умови відбору, які можуть використовуватися в цих секціях.
Крім того, в SQL використовуються ще такі умови відбору з підлеглими запитами:
Значення якогось виразу порівнюється з одним єдиним значенням, яке виводить підлеглий запит. Ця перевірка нагадує просте порівняння
Значення якогось виразу перевіряється на рівність одному із множини значень, що повертаються підлеглим запитом. Ця перевірка нагадує звичайну перевірку на належність множині.
Перевіряється наявність рядків в таблиці результатів підлеглого запиту.
Значення якогось виразу порівнюється з кожним із множини значень, які повертаються підлеглим запитом.
13.2.1. Порівняння з результатом підлеглого запиту
Синтаксична діаграма:
- це – модифікована форма простого порівняння: значення виразу порівнюється із значенням, яке повертається підлеглим запитом і якщо умова справджується, то перевірка дає результат TRUE.
Цю перевірку можна використовувати лише тоді, коли підлеглий запит повертає гарантовано одне і лише одне значення;
81). SELECT Misto
FROM Filii
WHERE Plan <> (SELECT SUM (Plan)
FROM Pracivnyky
WHERE Filia = Kod_Filii)
- в цьому прикладі підлеглий запит гарантовано повертає одне значення – результат агрегатної функції SUM( ). Навіть якщо в підлеглому запиті отримається порожня множина рядків, значення функції SUM( ) буде = 0.
Ще приклад:
83). Вивести список працівників, у яких плановий об’єм продаж перевищує плановий об’єм продаж філії, яка розміщена в Стебнику.
83). SELECT Prizv
FROM Pracivnyky
WHERE Plan > (SELECT Plan
FROM Filii
WHERE Misto = ‘Стебник’)
- в цьому прикладі підлеглий запит також повертає одне єдине значення.
Але, якщо в прикладі 81 це гарантувалося використанням функції SUM( ) в якості результуючого стовпчика підлеглого запиту, то в прикладі 83 єдність забезпечується логікою задачі – в одному місті знаходиться лише одна філія, тому умова
Misto = ‘Стебник’
відбирає лише один рядок з таблиці Filii
В перевірці порівняння з результатом підлеглого запиту можна використовувати ті самі операції відношення (=, <>, >, <, >=, <=), що і в звичайному порівнянні.
При цьому результатом підлеглого запиту, який приймає участь в порівнянні, повинно бути єдине одиничне значення, тобто таблиця, яка складається рівно з одного рядка і одного стовпчика.
Якщо результатом підлеглого запиту є кілька рядків чи кілька стовпчиків, то порівняння не має змісту і СУБД виводить повідомлення про помилку. Якщо в результаті виконання підлеглого запиту не буде отримано жодного рядка або буде отримано значення NULL, то операція порівняння поверне значення NULL:
де - операція відношення =, <>, >, <, >=, <=
Згідно стандарту SQL, підлеглий запит в порівнянні може бути записаний лише після знаку операції відношення:
- допускається, а
- забороняється.
Приходиться переформульовувати запит так, щоб „перевернути” умову „зліва направо”:
84).а) Вивести список філій, в яких сума фактичних об’ємів продаж працівників менша планового об’єму продаж цієї філії:
84).а). SELECT Misto
FROM Filii
WHERE
(SELECT SUM (Prodano)
FROM Pracivnyky
WHERE Filia = Kod_Filii)
< Plan
-згідно стандарту SQL1 порівняння
- недопустиме, тому умову потрібно „перевернути”:
84). б). Вивести список філій, для яких плановий об’єм продаж перевищує суму фактичних об’ємів продаж працівників цієї філії.
84). б). SELECT Misto
FROM Filii
WHERE Plan >
(SELECT SUM (Prodano)
FROM Pracivnyky
WHERE Filia = Kod_Filii)
- такий запит вже допускається стандартом SQL1.
Стандарт SQL2 знімає це обмеження – вже не потрібно „перевертати” умову.
Більше того, стандарт SQL2 дозволяє виконувати порівняння не тільки з одиничним значенням (таблицею, що складається з одного стовпчика і одного рядка), а із сукупністю значень (таблицею, яка складається з одного рядка та багатьох стовпчиків). Але в сучасних СУБД такі порівняння, як правило, ще не реалізовані – тому рекомендується дотримуватися тих обмежень, які накладає стандарт SQL1.
13.2.2. Перевірка на належність множині результатів підлеглого запиту
- це модифікована проста перевірка на належність в множині. Одне значення виразу порівнюється із стовпчиком даних, які повертаються підлеглим запитом. Якщо це значення дорівнює одному із елементів стовпчика, то перевірка дає результат TRUE. Ця перевірка використовується, коли необхідно порівняти значення із біжучого рядка з множиною значень, відібраних підлеглим запитом.
85). Вивести список працівників тих філій, в яких фактичний об‘єм продаж перевищує плановий.
85). SELECT Prizv
FROM Pracivnyky
WHERE Filia IN
(SELECT Kod_Filii
FROM Filii
WHERE Prodano > Plan)
- підлеглий запит повертає набір кодів філій, в яких фактичний об‘єм продаж перевищує плановий. Потім головний запит перевіряє кожний рядок таблиці Pracivnyky, щоб визначити, чи працює даний працівник в одній із відібраних філій.
Підлеглий запит повертає в якості результату один стовпчик даних, а секція WHERE головного запиту перевіряє, чи дорівнює значення із біжучого рядка таблиці головного запиту одному із значень в отриманому стовпчику.
Тобто, перевірка IN з підлеглим запитом виконується аналогічно простій перевірці IN, за винятком того, що множина значень визначається підлеглим запитом, а не вказується явно в круглих дужках.
Якщо підлеглий запит повертає лише значення NULL, або не повертає жодного рядка то така перевірка IN дасть результат NULL.
13.2.3. Перевірка на існування (предикат EXISTS)
- ця перевірка дозволяє вияснити, чи міститься в таблиці результатів підлеглого запиту хоча би один рядок.
Аналогічної простої перевірки не існує. Перевірка на існування допускається лише з підлеглим запитом.
Приклад:
86). Вивести список товарів, на які отримано замовлення вартістю 2500 або більше. Переформулюємо це таким чином:
86). Вивести список товарів, для яких в таблиці Zamovlennia існує по крайній мірі одне замовлення, яке задовольняє умовам:
а) – це – замовлення на даний товар;
б) – його вартість не менше 2500.
86). а). SELECT Nazva
FROM Tovary
WHERE EXISTS
(SELECT No_Zamovlennia
FROM Zamovlennia
WHERE Tovar = Kod_Tovaru
AND Vyrobnyk = Kod_Vyrobnyka
AND Vartist > = 2500)
|
Tovary |
||||
|
Kod_Tovaru |
Kod_Vyrobnyka |
. . . |
Nazva |
. . . |
|
Zamovlennia |
|||||
|
No_Zamovlennia |
Tovar |
Vyrobnyk |
. . . |
Vartist |
. . . |
- головний запит послідовно переглядає всі рядки таблиці Tovary, і для кожного виконується підлеглий запит.
Результатом підлеглого запиту є стовпчик даних, який містить номери всіх замовлень на „біжучий товар” вартістю не менше 2500. Якщо такі замовлення є (тобто, стовпчик не порожній), то перевірка EXISTS повертає значення TRUE.
Якщо підлеглий запит не дає жодного рядка замовлень, то перевірка EXISTS поверне значення FALSE.
Перевірка EXISTS ні за яких обставин не може повернути значення NULL.
Можна поміняти логіку перевірки, і використовувати форму NOT EXISTS. Тоді, якщо підлеглий запит не створює жодного рядка результату, то перевірка повертає TRUE; в іншому випадку – FALSE.
Предикат EXISTS на справді зовсім не використовує результати підлеглого запиту. Перевіряється тільки наявність рядків в таблиці результатів підлеглого запиту.
Тому в SQL є виняток з правила, згідно якого „підлеглий запит повинен повертати тільки один стовпчик даних”, і в підлеглому запиті перевірки EXISTS дозволяється використовувати форму SELECT *.
Попередній приклад можна переписати так:
86). Вивести список товарів, на які отримано замовлення вартістю 2500 або більше.
86).б). SELECT Nazva
FROM Tovary
WHERE EXISTS (SELECT *
FROM Zamovlennia
WHERE Tovar = Kod_Tovaru
AND Vyrobnyk = Kod_Vyrobnyka
AND Vartist >= 2500)
На практиці в підлеглому запиті перевірки EXISTS завжди використовується форма SELECT *.
В підлеглому запиті перевірки EXISTS завжди є зовнішнє звертання, яке зв‘язує підлеглий запит з рядком, що перевіряється в даний момент головного запитом.
Це – не синтаксичне правило, а обмеження, викликане здоровим глуздом – без такого зовнішнього звертання підлеглий запит перевірки EXISTS не буде мати ніякого відношення до головного запиту.
В даному прикладі такий зв‘язок здійснюється умовою
WHERE Tovar = Kod_Tovaru
AND Vyrobnyk = Kod_Vyrobnyka
яка зв‘язує стовпчики Tovar і Vyrobnyk із стовпчиками Kod_Tovaru і Kod_Vyrobnyka таблиці Tovaru.
13.2.3.1. Зв‘язок між перевіркою на існування (EXISTS) та перевіркою на належність множині (IN)
Розглянемо розв‘язки рівняння:
87). Вивести список філій, в яких є хоча би один працівник, чий план продаж перевищує 50% від плану продаж філії.
87).а). SELECT F. Misto
FROM Filii F
WHERE EXISTS
(SELECT *
FROM Pracivnyky P
WHERE P. Filia = F. Kod_Filii
AND P. Plan > 0,5 * F. Plan)
87).б). SELECT F. Misto
FROM Filii F
WHERE F. Kod_Filii IN
(SELECT P. Filia
FROM Pracivnyky P
WHERE P. Plan > 0,5 * F. Plan)
Запит способу а) виконується таким чином: для кожної філії виконується підлеглий запит, який відбирає з таблиці Pracivnyky рядки, для яких в стовпчику Filia міститься те саме значення, що і в стовпчику Kod_Filii біжучого рядка таблиці Filii (який перевіряється головним запитом) і для яких значення в стовпчику Plan перевищує половину значення стовпчика Plan біжучого рядка таблиці Filii.
Якщо таблиця результатів підлеглого запиту містить хоча би один рядок, то перевірка WHERE EXISTS (. . .) дасть TRUE; - і така філія ввійде в таблицю результатів головного запиту.
Якщо таблиця результатів підлеглого запиту – порожня (не містить жодного рядка, то перевірка WHERE EXISTS (. . .) дасть False - така філія не ввійде в таблицю результатів головного
Запит в способі б) виконується так: для кожної філії виконується підлеглий запит, який відбирає з таблиці Pracivnyky коди філій тих працівників, для яких значення в стовпчику Plan перевищує половину значення стовпчика Plan біжучого рядка таблиці Filii (який перевіряється головним запитом).
Якщо значення коду філії з біжучого рядка таблиці Filii входить у набір кодів філій, що повертається підлеглим запитом, то перевірка
WHERE . . . IN (. . .)
дасть TRUE - і така філія буде включена в таблицю результатів головного запиту.
Якщо ж значення коду філії з біжучого рядка таблиці Filii не входить у набір кодів філій, що повертається підлеглим запитом; або підлеглий запит взагалі не поверне жодного коду філії (тобто, створить порожню таблицю); або ж підлеглий запит створить таблицю, яка буде містити лише значення NULL – то перевірка
WHERE . . . IN (. . .)
дасть False (в перших двох випадках) або NULL (в останньому випадку) – і така філія не буде включена в таблицю результатів головного запиту.
В загальному випадку, запити
88).а). SELECT . . .
FROM Т1
WHERE EXISTS
(SELECT *
FROM Т2
WHERE Т2. = T1. )
та
88).б). SELECT . . .
FROM Т1
WHERE Т1. IN
(SELECT Т2.
FROM Т2 )
(де Т1 і Т2 – таблиці, Т1. , Т2. - однотипні стовпчики цих таблиць)
- відбирають із таблиці Т1 той самий набір рядків.
У випадку 88).а) підлеглий запит містить умову кореляції з головним запитом
WHERE Т2. = Т1. ,
яка відбирає рядки з таблиці Т2, для яких значення Т2. співпадає із значенням Т1.
Якщо після такого відбору залишиться не порожня множина рядків таблиці Т2, то відповідний рядок таблиці Т1 буде включений у результати запиту.
Умова кореляції (correlation – зв’язок, відповідність)
WHERE Т2. = Т1.
приводить до того, що підлеглий запит повинен виконуватися щоразу для кожного рядка таблиці Т1.
У випадку 88). б) підлеглий запит повертає стовпчик Т2. таблиці Т2.
Якщо цей стовпчик буде містити не порожню множину рядків, і якщо біжуче значення Т1. буде входити у множину значень стовпчика Т2. , який повертається підлеглим запитом, то такий рядок таблиці Т1 буде включений у результати запиту. Якщо стовпчик Т2. , утворений в результаті виконання підлеглого запиту – порожній (тобто, не містить жодного рядка), або біжуче значення Т1. не входить у множину значень стовпчика Т2. , то такий рядок таблиці Т1 не увійде в результати запиту.
Таким чином умова кореляції, тобто відбору рядків в підлеглому запиті для способу 88).а). замінюється перевіркою на входження у множину значень результатів підлеглого запиту для способу 88).б).
Оскільки підлеглий запит в способі 88).б). тепер не містить умови кореляції, то оптимізація обчислень, яку здійснює СУБД, приведе до того, що підлеглий запит буде виконаний лише один раз – для всього головного запиту.
Таким чином, способи 88).а). та 88).б). – еквівалентні (навіть, спосіб 88).б). – більш ефективний. В прикладі 87) способи а) і б) повністю еквівалентні, бо містять умову відбору
P.Plan > 0,5 * F.Plan, яка зв’язує таблиці Pracivnyky P та Filii F.
На завершення розглянемо запити
89).а). SELECT . . .
FROM Т1
WHERE NOT EXISTS
(SELECT *
FROM Т2
WHERE T2. = T1. )
та
89).б). SELECT . . .
FROM Т1
WHERE T1. NOT IN
(SELECT Т2.
FROM Т2)
В способі 89).а). в результати запиту будуть включені лише ті рядки таблиці Т1, для яких не існує результатів підлеглого запиту (тобто, для яких підлеглий запит повертає порожню таблицю).
В способі 89).б). в результати запиту увійдуть ті рядки таблиці Т1, для яких значення Т1. не входить у множину результатів підлеглого запиту.
Якщо підлеглий запит 1) повертає не порожню таблицю, або 2) результат не містить значення NULL, або 3) значення Т1. не є значенням NULL – то способи 89).а). та 89).б). – еквівалентні.
У всіх інших випадках – тобто, коли результат Т2. ,підлеглого запита складається із значення NULL, або значення Т1. є значенням NULL – то такий рядок не увійде в результати запиту 89) б), але він буде включений в результати запиту 89).а)!
- тому, в загальному випадку, запити 89).а). та 89).б). – не еквівалентні.
(Вони приводять до однакових результатів лише за умови, що ні стовпчик Т1. , ні стовпчик Т2.
гарантовано не містить значення NULL і таблиця Т2 – не порожня).
13.2.3.2. Зв’язок між перевіркою „для всіх” та перевіркою на не існування (NOT EXISTS)
|
Clienty |
||
|
Kod_Clienta |
Prizv |
. . . |
|
Zamovlennia |
||||||
|
No_ Zamovlennia |
. . . |
Client |
. . . |
Tovar |
Vyrobnyk |
. . . |
|
Tovary |
||
|
Kod_Tovaru |
Kod_Vyrobnyka |
. . . |
90). Вивести список клієнтів, які замовили весь набір наявних на складі товарів (тобто всі види товарів, які є на складі).
Це завдання можна переформулювати наступним чином.
90). Вивести список клієнтів, таких що для всіх товарів існує замовлення даного клієнта на цей товар.
Позначимо:
- множина всіх клієнтів;
- множина всіх товарів;
- множина всіх замовлень;
с - деякий клієнт,
t - деякий товар,
z ct - замовлення клієнта с на товар t
Фактично, завдання 90) полягає в побудові множини
Відомо, що предикат „для всіх ... існує ...” еквівалентний предикату „не існує ... не існує ...”
- тому завдання 90) можна записати таким чином:
90). а). Вивести список клієнтів, таких що не існує товару, для якого не існує замовлення даного клієнта на цей товар.
Використовуючи наведену систему позначень – потрібно побудувати множину
SQL не має перевірки „для всіх”. Для реалізації такої перевірки потрібно переформульовувати завдання за допомогою перевірки „не існує” – так, як це зроблено в прикладі 90). Тоді такий запит легко записується засобами SQL:
90).а). SELECT C. Prizv
FROM Clienty C
WHERE NOT EXISTS
(SELECT *
FROM Тovary T
WHERE NOT EXISTS
(SELECT *
FROM Zamovlennia Z
WHERE Z.Client = C.Kod_Clienta
AND Z.Тovar = T. Kod_Tovaru
AND Z.Vyrobnyk=T. Kod_Vyrobnyka))
В загальному випадку, перевірка . . . для всіх . . ., для яких існує . . . еквівалентна перевірці . . . не існує . . ., для якого не існує . . ., тобто, якщо припустити, що в стандарті SQL реалізована перевірка „для всіх” за допомогою гіпотетичного предикату FORALL, то тоді запит
91).а). (неможливий в стандарті SQL та SQL2, бо використовує ключове слово FORALL – якого насправді немає)
(SELECT . . .
FROM T1
WHERE FORALL
(SELECT *
FROM T2
WHERE EXISTS
(SELECT *
FROM T3
WHERE Т3. = Т1.
AND Т3. = Т2. ))
(де Т1, Т2, Т3 – таблиці;
Т1. , Т3. - однотипні стовпчики таблиць Т1 і Т3;
Т2. , Т3. - однотипні стовпчики таблиць Т2 і Т3) можна записати у формі:
91).б). (SELECT . . .
FROM T1
WHERE NOT EXISTS
(SELECT *
FROM T2
WHERE NOT EXISTS
(SELECT *
FROM T3
WHERE Т3. = Т1.
AND Т3. = Т2. ))
13.2.4. Багатократне порівняння (предикати ANY та ALL)
В перевірці IN визначається, чи дорівнює якесь значення одному із значень, що містяться в стовпчику результатів підлеглого запиту.
В SQL є два типи багатократного порівняння - ANY та ALL, які розширюють цю перевірку на інші операції порівняння (наприклад, > чи <):
13.2.4.1. Предикат ANY (перевірка . . . ANY . . .)
Значення виразу почергово порівнюється з кожним елементом, що міститься в стовпчику результатів підлеглого запиту. Якщо будь-яке (хоча б одне) із цих порівнянь дає результат TRUE, то перевірка ANY повертає значення TRUE.
92). Вивести перелік працівників, які прийняли замовлення на суму більшу, ніж 10% від їх плану.
92). SELECT Prizv
FROM Pracivnyky
WHERE 0.1 * Plan < ANY
(SELECT Vartist
FROM Zamovlennia
WHERE Pracivnyk = Kod_Pracivnyka)
|
Pracivnyky |
|||
|
Kod_Pracivnyka |
. . . |
Plan |
. . . |
|
Zamovlennia |
|||||
|
Kod_Zamovlennia |
. . . |
Pracivnyk |
. . . |
Vartist |
. . . |
Головний запит по черзі перевіряє всі рядки таблиці Pracivnyky. Підлеглий запит знаходить всі замовлення, прийняті „біжучим” працівником, і повертає стовпчик, який містить вартості цих замовлень.
Секція WHERE головного запиту обчислює 10% від плану біжучого працівника і порівнює це число із вартістю кожного замовлення, відібраного підлеглим запитом. Якщо є хоча би одне замовлення, вартість якого більша 10% від плану працівника, то перевірка < ANY повертає значення, і прізвище такого працівника заноситься в таблицю результатів головного запиту.
Якщо ж таких замовлень немає, то прізвище працівника в таблицю результатів не попадає.
Слід пам’ятати, що перевірка ANY включає в себе не одне порівняння, а кілька. Якщо прочитати умову порівняння дещо по-іншому, то це допоможе зрозуміти зміст перевірки (- інакше – можливі помилки!).
Наприклад, перевірку
WHERE X < ANY (SELECT Y . . .)
слід читати не як X < Y
„де X менше, чим будь-який вибраний Y. . .”,
а так: Y : X<Y
„де для існує деякого Y: X менше, чим Y. . .”
Тоді запит із прикладу 92 можна переформулювати таким чином:
92). Вивести список працівників, у яких для деякого існує замовлення, прийняте ними, таке що 10% від планового об’єму продаж цього працівника менше, ніж вартість цього замовлення.
Якщо підлеглий запит в перевірці ANY не створює жодного рядка, або якщо стовпчик його результатів містить значення NULL виконується по-різному.
Стандарт SQL задає правила, які визначаються результати перевірки ANY:
На практиці перевірка ANY інколи може приводити до помилок, які важко виявити. Особливо коли застосовується операція „не дорівнює” <>. Ось приклад:
93). Вивести прізвища і вік всіх працівників, які не керують філіями. Здавалося би, що відповідний запит можна виразити таким чином:
93).а). Неправильно!
SELECT Prizv, Vik
FROM Pracivnyky
WHERE Kod_Pracivnyka <> ANY
(SELECT Kerivnyk
FROM Filii)
- підлеглий запит
SELECT Kerivnyk
FROM Filii
в результаті дає коди працівників, які є керівниками філій, тому здається, що запит 93).а). має зміст:
„знайти всіх працівників, які не є керівниками філій”.
Але це не так! На справді запит 93).а). означає:
„знайти всіх працівників, які для деякої філії не є керівниками цієї філії”
Оскільки для будь-якого працівника можна знайти деяку філію, якою не керує цей працівник, то в таблицю результатів запиту ввійдуть всі працівники - тобто, запит 93).а). не дає відповіді.
Правильним буде запит:
93).б). Правильно:
SELECT Prizv, Vik
FROM Pracivnyky
WHERE NOT Kod_Pracivnyka = ANY
(SELECT Kerivnyk
FROM Filii))
- цей запит можна переформулювати так:
„знайти всіх працівників, для яких не виконується умова: що для деякої філії даний працівник керує цією філією”.
Таким, чином умова
WHERE X < > ANY (SELECT Y . . .)
- не рівносильна умові.
WHERE NOT (X = ANY (SELECT Y . . .))
Запит з перевіркою ANY завжди можна перетворити в запит з перевіркою EXISTS за допомогою перенесення операції порівняння в середину умови відбору підлеглого запиту.
Рекомендується саме так і робити, оскільки при цьому не буде помилок. Подібних до вище вказаних.
Ось альтернативна форма запиту 93).в). перевіркою EXISTS:
93).в). Правильно.
SELECT Prizv, Vik
FROM Pracivnyky
WHERE NOT EXISTS
(SELECT *
FROM Filii
WHERE Kod_Pracivnyka = Kerivnyk)
Тобто, для будь-якої операції порівняння перевірка.
WHERE NOT (X ANY (SELECT Y . . .))
рівносильна перевірці
WHERE NOT EXISTS
(SELECT *
. . .
WHERE X Y)
13.2.4.2. Предикат ALL (перевірка . . . ALL . . .)
Значення виразу почергово порівнюється з кожним елементом стовпчика результатів підлеглого запиту.
Якщо всі порівняння дають результат TRUE, то перевірка ALL повертає значення TRUE.
94). Вивести список тих філій з їхніми планами продаж, для яких фактичний об’єм продаж кожного працівника перевищує 50% від плану філії.
94). SELECT Misto, Plan
FROM Filii
WHERE 0.5 * Plan < ALL
(SELECT Prodano
FROM Pracivnyky
WHERE Filia = Kod_Filii)
|
Filii |
||||
|
Kod_Filii |
. . . |
Misto |
Plan |
. . . |
|
Pracivnyky |
|||||
|
Kod_Pracivnyka |
. . . |
Filia |
. . . |
Prodano |
. . . |
Головний запит по черзі перевіряє кожний рядок таблиці Filii. Підлеглий запит знаходить всіх працівників „біжучої” філії і повертає стовпчик з фактичними об’ємами продаж для кожного працівника. Секція WHERE головного запиту обчислює 50% від плану продаж філії і порівнює це значення зі всіма об’ємами фактичних продаж, отриманих в результаті виконання підлеглого запиту. Якщо всі об’єми фактичних продаж працівників перевищують значення 50% від плану філії, то перевірка < ALL повертає значення TRUE і дана філія включається в таблицю результатів запиту. Якщо – ні, то така філія не попадає в таблицю результатів.
Перевірка ALL, як і перевірка ANY, включає в себе не одне порівняння, а кілька. Якщо прочитати умову порівняння дещо по-іншому, то це допоможе зрозуміти зміст перевірки і дозволить уникнути помилок.
Наприклад, перевірку
WHERE X < ALL (SELECT Y . . .)
слід читати не як
„де Х менше, чим всі вибрані Y . . .”
а так:
„де для всіх Y: Х менше, чим Y . . .”
Тоді запит із прикладу 94 можна переформулювати таким чином:
94). Вивести список філій, в яких для всіх працівників: 50% плану філії менше, чим фактичний об’єм продаж кожного працівника цієї філії.
Якщо підлеглий запит в перевірці ALL не повертає жодного рядка, або якщо стовпчик його результатів містить значення NULL, то в різних СУБД перевірка ALL виконується по-різному. Стандарт SQL задає правила, які визначають результати перевірки ALL:
Ті самі помилки, що виникають коли перевірка ANY містить операцію порівняння „не дорівнює” <>, можуть бути і в перевірці ALL.
Щоб уникнути таких помилок, слід перевірку ALL перетворити в еквівалентну перевірку на існування (EXISTS) за допомогою перенесення операції порівняння в підлеглий запит, - подібно до того, як це робилося для перевірки ANY:
Завдання із прикладу
93). Вивести прізвища і вік всіх працівників, які не керують філіями, можна переформулювати таким чином:
93’). Вивести прізвище і вік працівників – таких, що для всіх філій: код керівника даної філії відрізняється від коду цього працівника і розв’язати за допомогою перевірки ALL:
93).г). Правильно.
SELECT Prizv, Vik
FROM Pracivnyky
WHERE Kod_Pracivnyka <> ALL
(SELECT Kerivnyk
FROM Filii )
Альтернативні способи:
93).б). Правильно.
SELECT Prizv, Vik
FROM Pracivnyky
WHERE NOT (Kod_Pracivnyka = ANY
(SELECT Kerivnyk
FROM Filii ))
та
93).в). Правильно.
SELECT Prizv, Vik
FROM Pracivnyky
WHERE NOT EXISTS
(SELECT *
FROM Filii
WHERE Kod_Pracivnyka = Kerivnyk)
Чи правильною буде відповідь?
93).д). (Неправильно!)
SELECT Prizv, Vik
FROM Pracivnyky
WHERE EXISTS
(SELECT *
FROM Filii
WHERE Kod_Pracivnyka <> Kerivnyk)
Можна питання сформулювати таким чином:
Чи можна операцію NOT в перевірці NOT EXISTS перенести всередину підлеглого запиту?
Запит 93).в). виконує наступні дії: для кожного працівника вибираються філії, якими керує цей працівник. Якщо таких філій – немає, то дані про цього працівника заносяться у таблицю результатів запиту.
На відміну від цього, запит 93).д). виконує зовсім інші дії:
Для кожного працівника вибираються філії, якими не керує цей працівник. Якщо такі філії – є, то дані про цього працівника заносяться у таблицю результатів запиту. Оскільки завжди є філія, якою не керує даний працівник – то в результати запиту попадають дані про всіх працівників.
Тому запит 93).в). і 93).д). – не еквівалентні:
WHERE NOT EXISTS
(SELECT . . .
WHERE X Y)
- зовсім не те саме, що
(SELECT . . .
WHERE NOT (X Y))
Як вже зазначалося, перевірку = ANY можна представити перевіркою IN:
WHERE X = ANY (SELECT Y. . .)
- рівносильно перевірці
WHERE X IN (SELECT Y. . .)
Багато запитів, записаних з використанням підлеглих запитів, можна також запитати у вигляді багатотабличних запитів.
Розглянемо приклад:
95). Вивести прізвища і вік працівників, які працюють у філіях західного регіону.
|
Filii |
|||
|
Kod-Filii |
. . . |
Region |
. . . |
|
Pracivnyky |
|||
|
Kod_Pracivnyka |
. . . |
Filia |
. . . |
95).а). SELECT Prizv, Vik
FROM Pracivnyky
WHERE Filia IN
(SELECT Kod_Filii
FROM Filii
WHERE Region = ‘Західний’)
- ця форма запиту дуже близька до його словесної формульовки.
Підлеглий запит вибирає список філій західного регіону, а головний запит виводить дані про працівників, які працюють в цих філіях.
Ось альтернативна форма цього запиту, яка використовує з’єднання двох таблиць:
95).б). SELECT Prizv, Vik
FROM Pracivnyky, Filii
WHERE Filia = Kod_Filii
AND Region = ‘Західний’
- тут сполучаюся таблиці Pracivnyky і Filii для того, щоб знайти регіон, в якому працює кожний працівник, а потім вилучаються ті працівники, які не працюють у західному регіоні.
Кожний із запитів 95).а), 95).б) виводить дані про відповідних працівників; жодний з них не є ні „помилковим” ні „ єдиним правильним”.
Для більшості людей варіант 95).а). – з використанням підлеглого запиту – буде більш природнім, бо в словесній формульовці не вимагається ніякої інформації про філії, і необхідність з’єднання таблиць Pracivnyky та Filii може здаватися дещо дивною.
Звичайно, якщо в запиті вимагається вибирати дані із двох таблиць, то варіант з підлеглим запитом вже не підходить і потрібно використовувати з’єднання цих таблиць:
96) Вивести прізвища і вік працівників, які працюють в західному регіоні; а також назви міст, в яких вони працюють.
96). SELECT Prizv, Vik, Misto
FROM Pracivnyky, Filii
WHERE Filia = Kod_Filii
AND Region = ‘Західний’
З іншої сторони, є дуже багато запитів, що використовують підлеглі запити, - які не можна записати у формі з’єднання:
97). Вивести прізвища і вік працівників, для яких плановий об’єм продаж більший середнього.
97). SELECT Prizv, Vik
FROM Pracivnyk
WHERE Plan >
(SELECT AVG ( Plan)
FROM Pracivnyky)
- в цьому випадку підлеглий запит – це підсумковий запит, а зовнішній – ні, тому із цих двох запитів не можна створити з’єднання.
Всі запити, які розглядалися до цих пір, були „дворівневими”. Кількість рівнів може бути більшою, ось приклад „Трирівневого” запиту:
98). Вивести список клієнтів, закріплених за працівниками, які працюють у філіях західного регіону.
98). SELECT Firma
FROM Сlienty
WHERE Prodavec IN
(SELECT Kod_Pracivnyka
FROM Pracivnyky
WHERE Filia IN
(SELECT Kod_Filii
FROM Filii
WHERE Region = ’Західний’))
|
Сlienty |
|||||
|
Kod_Сlienta |
. . . |
Firma |
. . . |
Prodavec |
. . . |
|
Pracivnyky |
|||
|
Kod_Pracivnyka |
. . . |
Filia |
. . . |
|
Filia |
|||
|
Kod_Filii |
. . . |
Region |
. . . |
В цьому прикладі самий внутрішній підлеглий запит
SELECT Kod_Filii
FROM Filii
WHERE Region = ‘Західний’
- створює стовпчик, який містить коди філій західного регіону.
Наступний підлеглий запит
SELECT Kod_Pracivnykа
FROM Pracivnyky
WHERE Filia IN ( )
- створює стовпчик, що містить коди працівників, які працюють в одній із вибраних філій.
І нарешті, зовнішній запит
SELECT Firma
FROM Сlienty
WHERE Prodavec IN ( )
виводить назви фірм, закріплених за вибраними працівниками.
По такій схемі можна створювати запити з більшою кількістю рівнів вкладеності.
Стандарт SQL не визначає максимально допустимої кількості рівнів вкладеності, але на практиці із зростанням їх кількості дуже сильно збільшується час виконання запиту.
Коли запит має більше двох рівнів вкладеності, то він стає важким для розуміння.
На практиці більшість СУБД обмежують максимально допустиму кількість рівнів вкладеності запитів відносно невеликим числом.
Не завжди підлеглий запит обчислюється заново для кожного рядка результатів декартового добутку таблиць головного запиту: дуже часто підлеглий запит повертає одні і ті самі результати для всіх рядків чи для групи рядків, наприклад:
99). Вивести список філій, для яких фактичний об’єм продаж менший середнього для всіх філій планового об’єму продаж.
99).а). SELECT Misto
FROM Filii
WHERE Prodano <
(SELECT AVG (Plan)
FROM Filii )
- було би недоцільно виконувати такий підлеглий запит для кожної філії, бо середній план продаж не змінюється, він абсолютно не залежить від філії, яка в даний момент перевіряється. Тому підлеглий запит можна виконати лише один раз і, отримавши середній план (наприклад, 55000 грн.), перетворити головний запит до вигляду:
99).б). SELECT Misto
FROM Filii
WHERE Prodano < 55000
В промислових СУБД це спрощення використовується кожного разу, коли є така можливість, для того, щоб зменшити об’єм роботи, необхідної для виконання запиту.
Проте коли підлеглий запит містить зовнішнє звертання, то таке спрощення застосовувати, не можна; як в прикладі:
100). Вивести список філій, в яких планові об’єми продаж перевищують сумарні плани працівників, що працюють в цих філіях.
100). SELECT Misto
FROM Filii
WHERE Plan >
(SELECT SUM (Plan)
FROM Pracivnyky
WHERE Filia = Kod_Filii)
В різних рядках таблиці Filia, яка перевіряється секцією WHERE головного запиту, стовпчик Kod_Filii (а він є зовнішнім звертанням в підлеглому запиті) набуває різних значень. Тому підлеглий запит повинен виконуватися заново для кожного рядка таблиці Filia.
Підлеглий запит, який містить зовнішнє звертання, називається корельованими підлеглим запитом, оскільки його результати корелюються з кожним рядком таблиці головного запиту, з тих самих причин зовнішнє звертання називається корелюючим звертанням.
Підлеглий запит може містити зовнішнє звертання до таблиці із секції FROM – будь-якого запиту, який містить даний підлеглий запит, незалежно від рівня вкладеності. Наприклад, ім’я стовпчика в підлеглому запиті четвертого рівня вкладеності може відноситися до однієї із таблиць, вказаних в секції FROM головного запиту, або до таблиці в будь-якому підлеглому запиті, який містить цей підлеглий запит четвертого рівня, - таке звертання завжди означає значення стовпчика в „біжучому” рядку таблиці, яка перевіряється.
Це все приводить до збільшення ймовірності неоднозначних звертань до стовпчиків: якщо в підлеглому запиті вказано неповне (не уточнене) ім’я стовпчика, то СУБД повинна визначити, чи це ім’я стосується таблиці із секції FROM цього самого підлеглого запиту, чи таблиці із секції FROM якогось із запитів, що містять даний підлеглий запит.
В SQL діє правило, згідно якого звертання до стовпчика в підлеглому запиті відноситься до найближчої можливої секції FROM.
Розглянемо приклад, в якому одна і та сама таблиця використовується і в головному, і в підлеглому запиті:
101). Вивести писок керівників, віком старших понад 40 років, в яких є підлеглі працівники, що перевиконують план.
101). SELECT Prizv
FROM Pracivnyky
WHERE Vik > 40
AND Kod_Pracivnykа IN
(SELECT Kerivnyk
FROM Pracivnyky
WHERE Prodano > Plan)
- стовпчики Kerivnyk, Prodano, Plan в підлеглому запиті – це звертання до таблиці Pracivnyky в секції FROM самого підлеглого запиту; СУБД не трактує їх як зовнішні звертання – тому цей підлеглий запит не є корельованими. Оптимізація приведе до того, що спочатку один раз буде виконаний підлеглий запит для того, щоб знайти працівників, які перевиконують план і утворити стовпчик, що містить коди їх керівників.
Потім СУБД виконає головний запит і вибере прізвище старших за віком керівників із отриманого списку.
Якщо в подібному випадку треба зробити зовнішнє звертання, то для цього необхідно використовувати псевдоніми таблиць:
102). Вивести список керівників, старших 40 років, в яких є працівники, що перевиконують план і працюють із своїм керівником в різних філіях.
102). SELECT K. Prizv
FROM Pracivnyky K
WHERE K. Vik > 40
AND K. Kod_Pracivnykа IN
(SELECT P. Kerivnyk
FROM Pracivnyky P
WHERE P. Prodano > P. Plan
AND P. Filia < > K. Filia)
Коли підлеглий запит міститься в секції HAVING, він приймає участь у відборі груп рядків:
103). Вивести список працівників, в яких середня вартість замовлень на товари, виготовлені виробником з кодом 1001, більша, ніж загальна середня вартість замовлень.
103). SELECT Prizv, AVG (Vartist)
FROM Pracivnyky, Zamovlennia
WHERE Kod_Pracivnykа = Prodavec
AND Vyrobnyk = 1001
GROUP BY Prizv
HAVING AVG (Vartist) >
(SELECT AVG (Vartist)
FROM Zamovlennia)
Підлеглий запит обчислює середню вартість всіх замовлень. Це – звичайний підлеглий запит, який не містить зовнішніх звертань, тому це середня вартість обчислюється один раз, а потім багатократно використовується в секції HAVING. Головний запит переглядає всі рядки таблиці, утвореної в результаті з’єднання таблиць Pracivnyky і Zamovlennia на основі співпадання значень в стовпчиках Kod_Pracivnykа (із таблиці Pracivnyky) та Prodavec (із таблиці Zamovlennia) при цьому відбираються рядки, що стосуються замовлень на товари виробника з кодом 1001.
Результуюча таблиця групується за прізвищами працівників, потім секція HAVING порівнює середню вартість замовлень по кожній групі із середньою вартістю по всім замовленням, обчисленою на самому початку.
Якщо середня вартість по групі більша, ніж загальна середня вартість, то така група рядків зберігається, якщо – ні, то ця група вилучається.
Нарешті, секція SELECT створює для кожної групи підсумковий рядок, який містить прізвище працівника і середню вартість прийнятих ним замовлень на товари виробника 1001.
В секції HAVING можна також використовувати корельовані підлеглі запити; при цьому, оскільки підлеглий запит виконується лише один раз для кожної групи рядків, то всі зовнішні звертання в корельованому підлеглому запиті повинні мати одне значення для кожної групи рядків.
На практиці це означає, що зовнішнє звертання повинно бути або звертанням до стовпчика групування зовнішнього запиту, або міститися в аргументі статистичної функції. В останньому випадку значення такої статистичної функції обчислюється при виконанні підлеглого запиту.
104). Вивести список працівників, в яких середня вартість замовлень на товари, виробником з кодом 1001, більша, ніж середня вартість всіх замовлень даного працівника.
104). SELECT Prizv, AVG (Vartist)
FROM Pracivnyky, Zamovlennia
WHERE Kod_Pracivnykа = Prodavec
AND Vyrobnyk = 1001
GROUP BY Prizv, Kod_Pracivnykа пояснити чому?
HAVING AVG (Vartist) >
(SELECT AVG (Vartist)
FROM Zamovlennia
WHERE Prodavec = Kod_Pracivnykа)
- підлеглий запит повинен обчислити середню вартість працівника, група рядків якого перевіряється в даний момент секцією HAVING.
Підлеглий запит відбирає замовлення, прийняті цим працівником, за допомогою зовнішнього звертання Kod_Pracivnykа. Це зовнішнє звертання – правильне, бо стовпчик Kod_Pracivnykа має однакові значення у всіх рядках групи, створеної головним запитом (тому цей стовпчик включено у перелік стовпчиків групування).
Підлеглий запит – це „запит всередині іншого запиту”. Такі запити записуються в одній із умов в секції WHERE або HAVING.
Коли підлеглий запит міститься в секції WHERE, то його результати використовуються для відбору окремих рядків, дані із яких заносяться в таблицю результатів запиту.
Коли ж підлеглий запит міститься в секції HAVING, його результати використовуються для відбору груп рядків. На основі кожної відібраної групи рядків формується один підсумковий рядок таблиці результатів запиту.
Підлеглі запити можуть мати кілька рівнів вкладеності.
При порівнянні з результатом підлеглого запиту значення, що перевіряється, порівнюється з єдиним значенням, яке повертається підлеглим запитом.
При перевірці на входження у множину результатів підлеглого запиту (IN) значення виразу перевіряється на рівність одному із множини значень, які повертаються підлеглим запитом.
Перевірка на існування (EXISTS) дозволяє вияснити, чи повертає підлеглий запит хоч якісь значення.
При багатократному порівняння (перевірки AVY та ALL) значення виразу порівнюється з усіма значеннями, відібраними підлеглим запитом; щоб визначити – чи виконується умова порівняння хоча би для одного (AVY) чи для всіх (ALL) значень.
Оптимізація, яку автоматично виконують промислові СУБД, приводить до того, що некорельовані підлеглі занити виконуються один раз на початку виконання головного запиту.
- такі запити – самі ефективні (порівняно із корельованими запитами та запитами на з’єднання, які дозволяють розв’язати ту саму задачу).
Ефективність корельованих запитів та еквівалентних запитів на з’єднання – приблизно однакова.
?????
SQL дозволяє не тільки вибирати інформацію, що міститься в базі даних, а і змінювати її.
В наступних розділах розглянемо: SQL – оператори, за допомогою яких можна добавляти і вилучати дані, а також оновлювати дані, які вже містяться в базі.
Потім розглянемо, як зберігати цілісність даних при їх зміні.
Останній розділ присвячений засобам опрацювання транзакцій, які дозволяють кільком користувачам одночасно змінювати вміст однієї бази даних, а також розглядати сукупність дій щодо внесення змін у БД як єдине ціле.
Порівняно з оператором SELECT, за допомогою якого виконуються запити на вибірку, SQL – оператори, які вносять зміни в базу даних, є більш простими. Але при зміні вмісту БД від СУБД вимагається, щоб вона:
1) зберігала цілісність даних;
2) дозволяла вводити в БД лише допустимі значення;
3) забезпечувала несуперечливість БД навіть у випадку системної помилки; 4) крім того, СУБД повинна забезпечувати можливість одночасної зміни БД кількома користувачами – таким чином, щоб вони не заважали один одному.
Добавлення нового рядка в БД відбувається тоді, коли в предметній області з’являється новий об’єкт, який представляється цим рядком.
Наприклад:
В усіх прикладах новий рядок добавляється для того, щоб БД залишалася точною моделлю реального світу. Найменшою кількістю інформації, яку можна добавити в БД, є один рядок.
1.1.1. Однорядковий оператор INSERT
- однорядковий оператор INSERT добавляє в таблицю один новий рядок.
В секції INTO вказується цільова таблиця – таблиця, в яку добавляється новий рядок, а в секції VALUES – містяться значення даних для нового рядка, а в секції VALUES – містяться значення даних для нового рядка. Список стовпчиків визначає, яке значення в який стовпчик заноситься.
Нехай на роботу прийняли нового працівника з такими даними:
Прізвище Іванов
Вік 25
Код 111
Посада Продавець
Філія Борислав (код філії 11)
Дата прийому 5 лютого 2003 р.
План продаж Ще не встановлений
Об’єм продаж $ 0. грн.
на біжучу дату
Приклад
1). Добавити інформацію про нового працівника Іванова.
1). а). INSERT INTO Pracivnyky
(Prizv, Vik, Kod_Prac, Posada, Filia, Prodav, Data_Pryjomu)
VALUES
(‘Іванов’, 25, 111, ‘Продавець’, 11, 0, ‘5-FEB-2003’)
Схема виконання оператора INSERT:
1). створюється новий порожній рядок, структура якого повторює структуру стовпчиків таблиці.
2). цей рядок заповнюється значеннями із секції VALUES.
3). заповнений даними рядок добавляється в таблицю.
Оскільки рядки в таблиці – не впорядковані, то не вказується, де саме потрібно вставляти рядок; „вгорі”, „внизу”, чи „між двома рядками таблиці”.
Якщо в таблиці багато стовпчиків, то оператор INSERT може виявитися досить довгим, але його структура все-рівно залишиться дуже простою.
Список стовпчиків в операторі INSERT потрібний для того, щоб встановити відповідність між значеннями, які містяться в секції VALUES та стовпчиками, для яких ці значення призначені.
Списки значень і стовпчиків повинні містити однакову кількість елементів, а тип даних кожного значення повинен бути сумісним з типом відповідного стовпчика.
1.1.1.1. Добавлення значень NULL
При добавлені в таблицю нового рядка всім стовпчикам, імена яких відсутні в списку стовпчиків оператора INSERT, автоматично присвоюються значення NULL.
В операторі INSERT з прикладу 1) був пропущений стовпчик Plan, і тому новий рядок в стовпчику Plan містить значення NULL.
Значення NULL можна присвоїти і явним чином, включивши відповідні стовпчики у список стовпчиків, а в списку значень – вказавши для них ключове слово NULL.
Наступний оператор виконує ту саму дію, що і у випадку 1).а):
1).б). INSERT INTO Pracivnyky
(Prizv, Vik, Kod_Prac, Posada, Filia, Data_Pryjomu Plan, Prodav)
VALUES
(‘Іванов’, 25, 111, ‘Продавець’, 11, ‘5-FEB-2003’, NULL, 0)
1.1.1.2. Добавлення всіх стовпчиків
Можна не включати список стовпчиків в оператор INSERT.
Якщо список стовпчиків не вказується, то він генерується автоматично, і в ньому зліва направо перелічуються всі стовпчики таблиці (при виконанні оператора SELECT * генерується такий самий перелік стовпчиків).
Нехай структура таблиці Pracivnyky така
|
Pracivnyky |
|||||||
|
Kod_Prac |
Prizv |
Vik |
Filia |
Posada |
Data_Pryjomu |
Plan |
Prodav |
Тоді попередній оператор INSERT можна записати у вигляді:
1).в). INSERT INTO Pracivnyky
VALUES (111, ‘Іванов’, 25, 11, ‘Продавець’, ‘5-FEB-2003’, NULL, 0)
- якщо список стовпчиків не вказується, то в списку значень необхідно явно вказувати значення NULL. Крім того, послідовність значень повинна в точності відповідати порядку стовпчиків в таблиці.
В інтерактивному режимі (коли кожний SQL – оператор вводиться з клавіатури) зручно не включати в оператор INSERT список стовпчиків – це зменшує довжину оператора.
Коли ж SQL – оператор INSERT викликається із програми, то список стовпчиків повинен бути вказаний завжди: 1) таку програму легше читати і розуміти; 2) програма не буде залежати від зміни фізичного порядку слідування стовпчиків таблиці.
1.1.2. Багаторядковий оператор INSERT
- багаторядковий оператор INSERT добавляє в цільову таблицю кілька рядків (як правило, більше одного).
В цьому виді оператора INSERT значення нових рядків явно не вказуються.
Джерелом нових рядків служить запит на вибірку, який міститься в операторі INSERT.
2). Нехай нам потрібно скопіювати номери, дати і вартості всіх замовлень, зроблених до 1 січня 2000 року із таблиці Zamovlennia в таблицю Stari Zamovl.
2). INSERT INTO Stari Zamovl
(Kod_Zamovl, Data_Zamovl, Vartist)
SELECT Kod_Zamovl, Data_Zamovl, Vartist
FROM Zamovlennia
WHERE Data_Zamovl < ‘01-JAN-2000’
- спочатку виконується запит до таблиці Zamovlennia, а потім таблиця результатів цього запиту добавляється в таблицю Stari Zamovlennia – по рядково.
Запит на вибірку, який міститься в багаторядковому операторі INSERT, згідно стандарту SQL1, повинен задовольняти вимогам:
1). Запит не повинен містити секцію ORDER BY.
Не має змісту сортувати таблицю результатів запиту, бо вона добавляється в цільову таблицю, яка (як і всі інші) – не впорядкована.
2). Таблиця результатів запиту повинна містити кількість стовпчиків, яка дорівнює кількості стовпчиків в списку секції INTO оператора INSERT (або співпадати з кількістю стовпчиків цільової таблиці, якщо список стовпчиків в секції INTO не вказується).
Типи даних відповідних стовпчиків таблиці результатів запиту і цільової таблиці повинні бути сумісними.
3). Запит не може бути запитом на об’єднання (UNION) кількох операторів SELECT.
4). Ім’я цільової таблиці секції INTO оператора INSERT не може бути вказане в секції FROM запиту на вибірку, або будь-якого в нього.
Тим самим забороняється добавлення таблиці самої до себе.
Перші два обмеження очевидні, а останні були добавлені для того, щоб уникнути зайвої складності оператора INSERT.
В стандарті SQL2 ці обмеження були ослаблені – в запиті дозволяються операції UNION та JOIN; дозволяється також „самодобавлення”.
Вилучати рядок із реляційної БД доводиться тоді, коли об’єкт, що представляється цим рядком, зникає із предметної області (перестає існувати у реальному світі).
Наприклад:
Якщо при цьому звільняють всіх працівників такої філії – потрібно вилучити відповідні рядки із таблиці Pracivnyky; якщо працівників не звільняють, а переводять у інші філії – то відповідні значення в стовпчику Filia таблиці Pracivnyky потрібно поновити.
У всіх цих випадках рядок вилучається для того, щоб БД залишиться точною моделлю реального світу.
Найменшою одиницею інформації, яку можна вилучити із реляційної БД, є один рядок.
1.2.1. Оператор DELETE
- вилучає вибрані рядки із однієї (!) вказаної таблиці.
В секції FROM вказується таблиця, що містить рядки, які необхідно вилучити.
В секції WHERE вказується критерій відбору тих рядків, які необхідно вилучити.
Нехай працівник Іванов звільняється.
3). Вилучити інформацію про Іванова із БД
3). DELETE FROM Pracivnyky,
WHERE Prizv = ‘Іванов’
Секція WHERE оператора DELETE повністю аналогічна секції WHERE оператора SELECT.
Оператор DELETE такого типу здійснює пошук тих рядків, які необхідно вилучити із таблиці, - тому цей оператор називають пошуковий.
Є ще одна форма оператора DELETE – позиційна, яка завжди вилучає лише один рядок (цей оператор використовується лише в програмному SQL).
Розглянемо ще приклад:
4). Вилучити всі замовлення, зроблені до 1 січня 2000 р.
4). DELETE FROM Zamovlennia
WHERE Data_Zamovl < ‘01-JAN-2000’
Зауваження:
Перед виконанням оператора DELETE (в інтерактивному режимі) спочатку рекомендується виконати оператор SELECT * з тими самими секціями FROM та WHERE – щоб переконатися, що будуть вилучені саме ті рядки, які потрібно вилучати; а тоді вже – замінити SELECT * на DELETE.
Якщо оператор DELETE викликається із програми – то варто його протестувати подібним чином: скопіювати текст оператора DELETE в якусь із утиліт, призначених для виконання SQL – запитів (наприклад, SQL – Explorer), замінити DELETE на SELECT * і виконати отриманий запит на вибірку – щоб переконатися, що оператор DELETE буде вилучати саме ті рядки, які ми хочемо вилучити.
1.2.2. Вилучення всіх рядків
Хоча секція WHERE в операторі DELETE – необов’язкова, вона присутня майже завжди. Якщо ця секція відсутня, то вилучаються всі рядки цільової таблиці:
5). Вилучити дані про всі замовлення (очистити таблицю Zamovlennia).
5). DELETE FROM Zamovlennia
- при цьому таблиця не вилучається із БД, вона все ще існує і до неї можна добавляти рядки оператором INSERT (щоб вилучити саму таблицю, потрібно використовувати оператор DROP TABLE).
1.2.3. Оператор DELETE з підлеглим запитом
Інколи буває потрібно провести відбір рядків в операторі DELETE, опираючись на дані із кілька таблиць.
Наприклад: потрібно вилучити всі замовлення, які прийняв працівник Іванов, причому код цього працівника ми забули.
Для того, щоб вибрати ці замовлення, можна побудувати запит до двох таблиць:
6). Вивести всі замовлення, які прийняв працівник Іванов:
6).а). SELECT Z .*
FROM Zamovlennia Z,
Pracivnyky P
WHERE Z . Prodavec = P. Kod_Pracivn
AND P.Prizv = ‘Іванов’
Цей оператор не можна перетворити у оператор DELETE заміною SELECT Z.* на DELETE, бо оператор DELETE не може звертатися до двох таблиць.
Для того, щоб виконати це завдання, потрібно використовувати умову відбору з підлеглим запитом:
6).б). SELECT *
FROM Zamovlennia
WHERE Prodavec = (SELECT Kod_Pracivn
FROM Pracivnyky
WHERE Prizv = ‘Іванов’)
Тепер – можна замінювати SELECT * на DELETE:
7). Вилучити всі замовлення, які прийняв працівник Іванов.
7). DELETE FROM Zamovlennia
WHERE Prodavec = (SELECT Kod_Pracivn
FROM Pracivnyky
WHERE Prizv = ‘Іванов’)
Підлеглий запит в операторі DELETE можуть мати кілька рівнів вкладеності. Вони також можуть містити зовнішні звертання до цільової таблиці оператора DELETE – тоді секція FROM оператора DELETE відіграє ту саму роль, що і секція FROM оператора SELECT.
Ось приклад запиту на вилучення, в якому потрібно використовувати підлеглий запит, що містить зовнішнє звертання до цільової таблиці:
8). Вилучити дані про клієнтів, які не робили замовлень від 1 січня 2000 р.
8). DELETE FROM Сlienty
WHERE NOT EXISTS
(SELECT *
FROM Zamovlennia
WHERE Client = Kod_Clienta
AND Data_Zamovl > ‘01-JAN-00’)
Цей оператор DELETE виконується таким чином: кожний рядок таблиці Clienty по черзі перевіряється на відповідність умові відбору.
Для кожного клієнта підлеглий запит вибирає всі замовлення, зроблені цим клієнтом після 1 січня 2000р. Звертання до стовпчика Kod_Clientа в підлеглому запиті – це зовнішнє звертання до ідентифікатора клієнта того рядка таблиці Clienty, який перевіряється оператором DELETE в даний момент.
Підлеглий запит в цьому прикладі –це корельованих підлеглий запит.
В підлеглих запитах оператора DELETE зовнішні звертання зустрічаються дуже часто, бо це єдиний спосіб „з’єднання” таблиць підлеглого запиту і цільової таблиці оператора DELETE.
В SQL1 єдине обмеження на застосування підлеглих запитів в операторі DELETE полягає в тому, що цільову таблицю не можна вказувати в секції FROM підлеглого запиту незалежно від рівня вкладеності. Це запобігає звертанню із підлеглих запитів на цільову таблицю (частина рядків якої вже може бути вилучена), за винятком зовнішніх звертань до рядка, який в даний момент перевіряється оператором DELETE.
В стандарті SQL2 це обмеження знімається: вважається, що звертання до цільової таблиці в підлеглому запиті – це звертання до повної цільової таблиці, із якої ще не вилучено жодного рядка.
Оновлювати інформацію, що міститься в БД, потрібно тоді, коли відповідні зміни відбуваються у „зовнішньому світі”, наприклад:
- значення даних оновлюються для того, щоб БД залишалася точною моделлю реального світу.
Найменша одиниця інформації, яку можна оновити в реляційній базі даних, - це значення одного стовпчика в одному рядку.
1.3.1. Оператор UPDATE
- цей оператор оновлює значення одного чи кількох стовпчиків у вибраних рядках однієї таблиці.
В операторі вказується цільова таблиця, яку потрібно модифікувати, при цьому користувач повинен мати дозвіл на оновлення таблиці і кожного конкретного стовпчика.
Секція WHERE відбирає рядки таблиці, які необхідно оновити.
В секції SET вказується, які стовпчики потрібно оновити, і для них задаються нові значення.
Наступний оператор UPDATE змінює величину кредиту для клієнта і закріплює його за новим працівником:
9). Збільшити кредит для компанії „Рога і копита” до 6000 грн. і закріпити її за Івановим (ідентифікатор працівника 109)
9). UPDATE Clienty
SET Credit = 6000,
Prodavec = 109
WHERE Firma = ‘Рога і копита’
- цей оператор оновлює один рядок таблиці клієнта.
Умови відбору секції WHERE оператора UPDATE повністю аналогічні умова відбору в операторах SELECT і DELETE.
Оператор UPDATE може одночасно оновити кілька рядка, які відповідають умові відбору:
10). Перевести всіх працівників із стебницької філії (код філії 12) в трускавецьку (код 11) і збільшити їхні особисті плани продаж на 10%.
10). UPDATE Pracivnyky
SET Filia = 11,
Plan = 1.1 * Plan
WHERE Filia = 12
- секція WHERE відбирає набір рядків таблиці Pracivnyky, в яких оновлюються стовпчики Filia та Plan.
Оператор UPDATE виконується так: всі рядки таблиці Pracivnyky по черзі перевіряються на відповідність умові відбору. Рядки, для яких умова відбору виконується (має значення TRUE), - оновлюються, а ті рядки, для яких умова відбору не виконується (результат має значення FALSE або NULL) – не оновлюються.
Такий оператор UPDATE приводить до пошуку тих рядків, які потрібно оновити – тому він називається пошуковий.
Секція SET в операторі UPDATE – це список операцій присвоєння, відокремленнях комами.
В кожній операції визначається цільовий стовпчик, який необхідно оновити, і вказується нове значення для цього стовпчика. Кожний цільовий стовпчик повинен входити у список лише один раз: не повинно бути двох операцій присвоєння для одного і того самого цільового стовпчика.
Вираз в операції присвоєння може бути будь-яким допустимим SQL- виразом, результат якого має тип даних що відповідає цільовому стовпчику. Необхідно, щоб значення виразу обчислювалося на основі значень лише того рядка, який в даний момент оновлюється в цільовій таблиці. Вираз не може включати в себе жодної статистичної функції та жодного підлеглого запиту.
Якщо вираз в операції присвоєння містить звертання до одного із стовпчиків цільової таблиці, то для обчислення виразу використовується значення цього стовпчика, яке було перед оновленням: так, в прикладі 10). присвоєння
Plan = 1.1 * Plan
Те ж саме справедливо і для звертань до стовпчиків в секції WHERE:
11). Збільшити план продаж на 10% для тих працівників, в яких план продаж менший 10000 грн.
11). UPDATE Pracivnyky
SET Plan = 1.1 * Plan
WHERE Plan < 10000
В умові відбору Plan < 10000 секції WHERE та в виразі 1.1 * Plan із секції SET значення стовпчика Plan – те, яке було перед оновленням.
12). Збільшити план продаж на 10% для тих працівників, які недовиконали план, і накласти на них штраф в розмірі 5% від раніше встановленого плану.
12).а). UPDATE Pracivnyky
SET Plan = 1.1 * Plan,
Shtraf = 0.05 * Plan
WHERE Prodano < Plan
12).б). UPDATE Pracivnyky
SET Shtraf = 0.05 * Plan,
Plan = 1.1 * Plan
WHERE Prodano < Plan
- запити 12).а) та 12).б) – еквівалентні (приводять до однакових результатів), тому що і в умові відбору Prodano < Plan і в виразах
1.1 * Plan
та
0.05 * Plan
із операцій присвоєння секції SET значення стовпчика Plan береться те, яке було до оновлення, а отже порядок запису присвоєнь в секції SET – не має ніякого значення, він може бути довільним.
1.3.2. Оновлення всіх рядків
Секція WHERE в операторі UPDATE – необов’язкова. Якщо вона відсутня, то оновлюються всі рядки цільової таблиці.
13). Збільшити плани продаж всіх працівників на 10%.
13). UPDATE Pracivnyky
SET Plan = 1.1 * Plan.
В операторі DELETE секція WHERE також – необов’язкова. Але, як правило, оператор DELETE без секції WHERE майже ніколи не використовується (бо приводить до очистки таблиці – вилучаються всі рядки) – тому оператор DELETE без секції WHERE, можливо, помилковий – просто забули записати секцію WHERE.
На відміну від цього, оператор UPDATE без секції WHERE виконує корисну функцію, яка часто використовується; а саме і оновлює всю таблицю.
1.3.3. Оператор UPDATE з підлеглим запитом
В операторі UPDATE, так як і в операторі DELETE, підлеглі запити відіграють важливу роль, бо вони дають можливість відбирати рядки для оновлення, враховуючи інформацію із інших таблиць:
14). Збільшити на 5000 грн. кредит для тих клієнтів, які зробили якесь замовлення вартістю понад 25000 грн.
14). UPDATE Clienty
SET Credit = Credit + 5000
WHERE Kod_Clienta IN
(SELECT DISTINCT Client
FROM Zamovlennia
WHERE Vartist > 25000)
15). Закріпити за Івановим (код працівника = 105) тих клієнтів, які обслуговувалися працівниками, чий об’єм продаж менший, ніж 80% від їх особистого плану.
15). UPDATE Clienty
SET Prodavec = 105
WHERE Prodavec IN
(SELECT Kod_Pracivn
FROM Pracivnyky
WHERE Prodano < 0.8 * Plan)
16). Всіх працівників, які обслуговують понад трьох клієнтів, підпорядкувати безпосередньо Петрову (код працівника = 106).
16). UPDATE Pracivnyky
SET Kerivnyk = 106
WHERE 3 <
(SELECT COUNT (*)
FROM Clienty
WHERE Prodavec = Kod_Pracivn)
- згідно стандарту SQL1, підлеглий запит потрібно записувати після знаку операції відношення.
Для оператора UPDATE те саме обмеження, яке накладає стандарт SQL1, що і для оператора DELETE: ім’я цільової таблиці не може бути присутнє в секції FROM підлеглого запиту будь-якого рівня вкладеності. Це запобігає звертанню із підлеглих запитів до цільової таблиці (частина рядків якої вже може бути модифікована). Таким чином, всі звертання в підлеглих запитах до цільової таблиці – це зовнішні звертання до того рядка цільової таблиці, який перевіряється в даний момент секцію WHERE оператора UPDATE.
В стандарті SQL2 це обмеження також знімається і визначається, що звертання до цільової таблиці в підлеглому запиті – це звертання до цільової таблиці в її початковому стані, коли ще не були зроблені жодні оновлення.
Рядки, які будуть оновлюватися, визначаються умовою відбору. Стовпчики, які будуть оновлюватися, та вирази, які задають нові значення, вказуються явно в секції SET.
Paradox, Fox Pro, Access
Товари
назва товару
од. вимір.
ціна од. вимір.
відпуск товару
номер відпуску
дата відпуску
назва товару
назва покупця
Покупці
назва покупця
місто
адреса
SELECT FROM
Н.Д.
Таблиця БД
таблиця
,
SELECT
поле
FROM
таблиця
DISTINCT
АLL
SELECT
,
поле
FROM
таблиця
*
DISTINCT
SELECT
АLL
,
*
поле
FROM
таблиця
поле
умова
WHERE
поле
умова
константа
операція
поле
,
DISTINCT
SELECT
АLL
,
*
поле
FROM
таблиця
,
умова
WHERE
таблиця
.
поле
таблиця
. *
ім’я таблиці
псевдонім таблиці
ім’я поля
.
FROM
,
ім’я таблиці
псевдонім таблиці
ORDER BY
,
поле
ASC
DESC
,
ORDER BY
поле
зростання
спадання
*
,
поле
вираз
SELECT
ALL
DISTINCT
AS
нове ім’я
поле
вираз
вираз
вираз
вираз
вираз
вираз
DISTINCT
агрегатна
функція
вираз
поле
SUM ( )
AVG ( )
MIN ( )
MAX ( )
NULL
GROUP BY
поле
,
SELECT
ALL
DISTINCT
AS
,
поле
вираз
нове ім’я
*
FROM
,
таблиця
псевдонім
WHERE
умова
GROUP BY
поле
,
ORDER BY
поле
,
ORDER BY
ASC
DESC
поле
,
група 1
група N
HAVING
умова
агрегатна
функція
операція
відношення
значення
NOT
WHERE
умова
відбору
AND
OR
=
<>
<
>=
>
<=
! =
! <
! >
вираз 1
вираз 2
операції відношення
+
-
+
-
*
/
значення
вираз
стовпчик
функція
арифметичні
операції
)
(
операція відношення
вираз
стовпчик
вираз
операція відношення
стовпчик
NOT
BETWEEN
вираз
нижнє
значення
AND
верхнє
значення
NULL BETWEEN A AND В NULL
X BETWEEN NULL AND NULL NULL
X BETWEEN NULL AND В
False, X > B
NULL, X B
X BETWEEN A AND NULL
False, X < A
NULL, X A
константа
IN
вираз
NOT
,
,
NOT
ім’я об’єкта - поля
. Clear;
біжучий рядок
стовпчик
IS
NOT
NULL
константа
стовпчик
NOT
STARTING
WITH
NOT
стовпчик
CONTAINING
константа
стовпчик
NOT
LIKE
шаблон
символ пропуску
ESCAPE
будь-який 1 символ
„_ A % $ B
C ”
Table
Результат оператора SELECT
др. частина
довжина
др. частина
довжина
довжина
др. частина
довжина
др. частина
др. частина
довжина
др. частина
довжина
довжина
довжина
SQL 2
max. довжина
max. довжина
точність
точність
YEAR
дата
ціле число
MONTH
дата
ціле число
- місяць
- рік
дата
стрічка, яка визначає формат
стрічка
TO_CHAR
,
стрічка
BIT_LENGTH
(
)
значення
AS
тип
(
)
CAST
стрічка
(
)
CHAR_LENGTH
USING
функція
стрічка
(
)
CONVERT
точність
)
CURRENT_TIME
(
CURRENT_DATE
точність
CURRENT_TIME STAMP
(
)
частина
FROM
значення
EXTRACT
(
)
стрічка
LOWER
)
(
дата
MONTH
)
(
стрічка
OCTET_LENGTH
(
)
стрічку
підстрічка
)
підстрічка
IN
стрічка
POSITION
(
)
FOR
довжина
FROM
позиція
стрічка
SUBSTRING
(
стрічки
позиції
довжину
дата
стрічка, яка визначає формат
TO_CHAR
(
BOTH
символ
FROM
стрічка
)
TRIM
(
функція
USING
стрічка
TRANSLATE
)
(
LEADING
символ
FROM
)
TRIM
(
стрічка
символ
TRAILING
FROM
)
TRIM
(
стрічка
)
UPPER
(
стрічка
дата
YEAR
)
(
стрічка
стрічка
стрічка
Вивести всю інформацію про клієнтів , які живуть у Львові.
NULL
CAST
значення
AS
тип даних
(
)
тип даних
типу даних
значення
||
літерний вираз 1
літерний вираз 2
Cina>200
результати запиту
UNION
Таблиця замовлення
Vartist>3000
результати запиту
UNION
UNION
результуюча таблиця
UNION
UNION
А
UNION
В
С
С
В
А
В
С
А
UNION
UNION
UNION
UNION
UNION
)
)
)
(
(
(
А
UNION ALL
В
С
В
А
В
С
А
UNION ALL
UNION ALL
UNION ALL
N
UNION ALL
UNION ALL
)
)
)
(
(
(
А
UNION ALL
С
В
UNION
А
UNION ALL
С
В
UNION
(
)
А
UNION ALL
С
В
UNION
)
(
інструкція SELECT
(без ORDER BY)
UNION
ALL
ORDER BY
№ стовпчика
інструкція SELECT
(без ORDER BY)
,
Дід
100.грн.
Баба
75.грн.
01
02
101
01
68.грн.
203
02
76 грн.
101
68
Дід
100
3
4
4
1
2
2
головний ключ – унікальний ідентифікатор
ключ з’єднання
зовнішній ключ
– для зв’язку із іншою таблицею
1 : N
Nazva
1 : N
складений ключ
з’єднання
складений зовнішній ключ
складений первинний ключ
No_Zamovlennia
ім’я таблиці
.
ім’я стовпчика
SQL Server
SQL-2
2
2
1
1
1
1
1
1
3
3
3
2
1
1
1
1
1
1
1
1
2
2
1
Х
Х
2
1
3
3
Х
3
Х
1
1
2
+
1
3
+
1
2
3
+
+
X
Види сполучень
та їх результати
1
1
1
1
2
3
1
3
2
,
1
3
2
,
,
SQL Server
SQL-2
2
2
Х
1
2
1
1
1
2
1
Х
3
3
3
3
1
3
2
2
3
1
2
1
1
2
Х
3
1
1
3
Х
СТ1
*=*
СТ2
СТ1
*=
СТ2
СТ1
=*
СТ2
Т2
Т1
СТ2
СТ2
СТ2
СТ1
* < =
СТ2
СТ1
=
(+)
СТ2
СТ1
(+) =
FROM
таблиця
,
вираз природнього з’єднання
вираз з’єднання
вираз перехресного з’єднання
вираз розширеного
запиту на з’єднання
LEFT
таб. 1
NATURAL
RIGHT
FULL
INNER
OUTER
...
JOIN
таб. 2
...
JOIN
таб.2
...
таб. 2
JOIN
INNER
FULL
LEFT
RIGHT
OUTER
ON
умова
USING
стовпч
,
таб. 2
(
)
...
CROSS JOIN
таб. 2
таб. 2
UNOIN JOIN
таб. 2
таб. 2
3
2
1
1
2
3
1
праве зовнішнє з’єдн
внутр. з’єдн.
повне зовнішнє з’єдн
рядки Т1, які
не задов. умові відбору розш.
NULL
рядки Т1, які
задов. умові
відбору розш.
знач. NULL
рядки Т2, які
не задов. умові відбору розш. NULL
рядки Т2, які
задов. умові
відбору розш.
знач. NULL
Таблиця Т1, розширена
значеннями NULL (N рядків)
Всі пари рядків таблиць Т1 і Т2,
розширена (N x K рядків)
Таблиця Т2, розширена
значеннями NULL (K рядків)
пари рядків із Т1і Т2,
які не задовольняють умові відбору
пари рядків із Т1 і Т2, які задовольняють умові відбору
перехресне з’єдн
розширений запит на з’єдн
ліве зовнішнє з’єдн
X
V
X
V
X
V
- не задов. умові відбору.
- задов. умові відбору.
1 : N (=)
?
?
?
?
SELECT
ALL
DISTINCT
результуючий стовпчик
,
*
FROM
джерела даних
WHERE
умова відбору
GROUP BY
стовпчик групування
,
HAVING
умова відбору
вираз
підлеглий запит
=
<>
>=
>
<=
<
вираз
?
0
NULL
порожня множина
вираз
?
NULL
NULL
?
підлеглий запит
?
вираз
вираз
?
підлеглий запит
<
підлеглий запит
вираз
підлеглий запит
вираз
NOT
IN
підлеглий запит
NULL IN
NULL
NOT
EXISTS
підлеглий запит
1 : N
1 : N
ст
ст
ст
ст
ст
ст
ст
ст
ст
ст
ст
ст
ст
ст
ст
ст
ст
ст
ст
ст
ст
ст
ст
ст
ст
ст
ст
ст
ст
ст
ст
ст
1 : N (=)
1 : М
М : 1
ст 1
ст 1
ст 2
ст 2
ст 1
ст 1
ст 2
ст 2
ст 1
ст 2
ст 2
вираз
=
>
<
>=
<=
<>
ANY
підлеглий запит
ALL
1 : N (=)
?
?
?
?
1 : N (=)
?
?
1 : N (=)
1 : N (=)
1 : N (=)
підлеглий запит
підлеглий запит
INSERT INTO
ім’я таблиці
,
VALUES
констант
NULL
,
ім’я стовпчика
INSERT INTO
ім’я таблиці
ім’я стовпчика
,
запит на вибірку
DELETE FROM
таблиця
WHERE
умова відбору
UPDATE
таблиця
SET
стовпчик
=
вираз
WHERE
,
умова відбору
значення, яке було перед оновленням.
Query