23. Підзапити

В цій частині ми обговоримо використовування закінчених операторів SELECT, вбудованих в тіло іншого оператора SELECT. Зовнішній (другий) оператор SELECT використовує результат виконання внутрішнього (першого) оператора для визначення змісту остаточного результату всієї операції. Внутрішні запити можуть бути вставлені в пропозиції WHERE і HAVING зовнішнього оператора SELECT — в цьому випадку вони одержують назву підзапитів, або вкладених запитів. Крім того, внутрішні оператори SELECT можуть використовуватися в операторах INSERT, UPDATE і DELETE. Існує три типи підзапитів.

• Скалярний підзапит повертає значення, вибиране з перетину одного стовпця з одним рядком, — тобто єдине значення. У принципі, скалярний підзапит може використовуватися скрізь, де вимагається вказати єдине значення.

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

• Табличний підзапит повертає значення одного або більше стовпців таблиці, розміщені в більш ніж одному рядку. Табличний підзапит може використовуватися скрізь, де допускається указувати таблицю — наприклад, як операнд предиката IN.

Вибрати працівників, в яких зарплата перевищує середню і вказати на скільки

SELECT tn, priz, zarpl, zarpl–(SELECT AVG(zarpl) FROM spivrob) AS riznucja

FROM spivrob

WHERE zarpl > (SELECT AVG(zarpl)

FROM spivrob);

25. Особливості і синтаксис речень модифікації. Речення delete. Видалення одиничного запису. Видалення множини записів. Видалення з вкладеним підзапитом.

Модифікація даних може виконуватися за допомогою пропозицій DELETE (видалити), INSERT (вставити) і UPDATE (оновити). Подібно пропозицією SELECT вони можуть оперувати як базовими таблицями, так і уявленнями. Проте з ряду причин не всі подання є оновлюваними. Поки зафіксуємо цей факт, відклавши опис уявлень і особливостей їх оновлення до глави 5, але будемо пам'ятати, що термін "уявлення" відноситься тільки до оновлюваних уявленням.

Пропозиція DELETE має формат

DELETE

FROM базова таблиця | уявлення

[WHERE фраза];

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

Пропозиція INSERT має один з наступних форматів:

INSERT

INTO {базова таблиця | уявлення} [(стовпець [, стовпець] ...)]

VALUES ({константа | змінна} [{константа | змінна}] ...);

або

INSERT

INTO {базова таблиця | уявлення} [(стовпець [, стовпець] ...)]

     підзапит;

У першому форматі в таблицю вставляється рядок зі значеннями полів, зазначеними в переліку фрази VALUES (значення), причому i-е значення відповідає i-му стовпцю в списку стовпців (стовпці, не зазначені в списку, заповнюються NULL-значеннями). Якщо в списку VALUES фрази вказані всі стовпці модифікується таблиці і порядок їх перерахування відповідає порядку стовпців в описі таблиці, то список стовпців в фразі INTO можна опустити. Однак не радимо цього робити, тому що при зміні опису таблиці (перестановка стовпців або зміна їх числа) доведеться переписувати і INSERT пропозицію.

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

Пропозиція UPDATE також має два формати. Перший з них:

UPDATE (базова таблиця | уявлення}

SET стовпець = значення [, стовпець = значення] ...

[WHERE фраза]

де значення - це

стовпець | вираз | константа | змінна

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

При відсутності WHERE фрази оновлюються значення зазначених стовпців у всіх рядках модифікується таблиці. WHERE фраза дозволяє скоротити число оновлюваних рядків, вказуючи умови їх відбору.

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

UPDATE {базова таблиця | уявлення}

SET стовпець = значення [, стовпець = значення] ...

FROM {базова таблиця | уявлення} [псевдонім],

        {Базова таблиця | уявлення} [псевдонім]

      [, {Базова таблиця | уявлення} [псевдонім]] ...

[WHERE фраза]

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

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

1. Видалення одиничного запису

Видалити постачальника з ПС = 7.

DELETE

FROM Постачальники

WHERE ПС = 7;

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

2. Видалення множини записів

Видалити всі поставки.

DELETE

FROM Поставки;

Поставки - все ще відома таблиця, але в ній тепер немає рядків. Для знищення таблиці треба виконати операцію DROP TABLE Поставки.

Видалити всі м'ясні страви.

DELETE FROM Блюда

WHERE Основа = 'М'ясо';

3. Видалення з вкладеним підзапитом

Видалити всі поставки для постачальника з Паневежиса.

DELETE

FROM Поставки

WHERE ПС IN

      (SELECT ПС

       FROM Постачальники

       WHERE Місто = 'Паневежис');