22. Групування результатів (фраза Group), Обмеження на виконання групування (фраза HAVING)
Приведені в попередній лекції приклади зведених даних подібні підсумковим рядкам, звичайно розміщуваним в кінці звітів. В підсумках всі детальні дані звіту стискаються в одну узагальнюючу рядок. Проте дуже часто в звітах вимагається формувати і проміжні підсумки. Для цієї мети в операторі SELECT може указуватися фраза GROUP. Запит, в якому присутня фраза GROUP, називається групуючим запитом, оскільки в ньому групуються дані, одержані в результаті виконання операції SELECT, після чого для кожної окремої групи створюється єдиний сумарний рядок. Стовпці, перераховані у фразі GROUP, називаються групованими стовпцями. Стандарт ISO вимагає, щоб пропозиція SELECT і фраза GROUP були тісно зв'язані між собою. При використовуванні в операторі SELECT фрази GROUP кожний елемент списку в пропозиції SELECT повинен мати єдине значення для всієї групи. Більш того, пропозиція SELECT може включати тільки наступні типи елементів:
• імена стовпців;
• узагальнюючі функції;
• константи;
• вирази, що включають комбінації перерахованих вище елементів.
Всі імена стовпців, приведені в списку пропозиції SELECT, повинні бути присутні і у фразі GROUP — за винятком випадків, коли ім'я стовпця використовується в узагальнюючій функції. Зворотне правило не є справедливим — у фразі GROUP можуть бути присутні імена стовпців, відсутні в списку пропозиції SELECT. Якщо спільно з фразою GROUP використовується пропозиція WHERE, то воно обробляється першим, а групуванню піддаються тільки ті рядки, які задовольняють умові пошуку.
Стандартом ISO визначено, що при проведенні групування всі відсутні значення розглядаються як рівні. Якщо два рядки таблиці в одному і тому ж групованому стовпці містять значення NULL і ідентичні значення у всій решті непорожніх групованих стовпців, вони поміщаються в одну і ту ж групу.
Підрахувати, мінімальну, максимальну та середню зарплату по кожній професії
SELECT prof, MIN(zarpl) AS minimum, MAX(zarpl) AS maximum, AVG(zarpl) AS serednja
FROM spivrob
GROUP BY prof
ORDER BY prof;
2.6. Обмеження на виконання групування (фраза HAVING)
Фраза Having призначена для використовування спільно з фразою GROUP для завдання обмежень, вказуваних з метою відбору тих груп, які будуть поміщені в результуючу таблицю запиту. Хоча фраза Having і пропозиція WHERE мають схожий синтаксис, їх призначення різне. Пропозиція WHERE призначена для фільтрації окремих рядків, що використовуються для групування або що поміщаються в результуючу таблицю запиту, тоді як фраза HAVING використовується для фільтрації груп, що поміщаються в результуючу таблицю запиту. Стандарт ISO вимагає, щоб імена стовпців, що використовуються у фразі HAVING, обов'язково були присутні в списку фрази GROUP або застосовувалися в узагальнюючих функціях. На практиці умови пошуку у фразі HAVING завжди включають, щонайменше, одну узагальнюючу функцію, інакше ці умови пошуку повинні бути поміщені в пропозицію WHERE і застосовуватися для відбору окремих рядків. (Не забувайте, що узагальнюючі функції не можуть використовуватися в пропозиції WHERE.)
Підрахувати, мінімальну, максимальну та середню зарплату по кожній професії із середньою зарплатою співробітника більше 4500.
SELECT prof, MIN(zarpl) AS minimum, MAX(zarpl) AS maximum, AVG(zarpl) AS serednja
FROM spivrob
GROUP BY prof
HAVING AVG(zarp) > 4500
ORDER BY prof;
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 Місто = 'Паневежис');
26 .Речення INSERT.
1. Вставка єдиною записи в таблицю
Додати в таблицю Страви блюдо:
Шашлик (БЛ - 34, Блюдо - Шашлик, В - Г, Основа - М'ясо, Вихід - 150)
при невідомої поки трудомісткості приготування цієї страви.
INSERT
INTO Страви (БЛ, Блюдо, В, Основа, Вихід)
VALUES (34, 'Шашлик', 'Г', 'М'ясо', 150);
Створюється новий запис для страви з номером 34, з невизначеним значенням в стовпці Праця.
Порядок полів у INSERT не обов'язково повинен збігатися з порядком полів, в якому вони визначалися при створенні таблиці. Цілком припустима і така версія попереднього речення:
INSERT
INTO Страви (Основа, В, Страва, БЛ, Вихід)
VALUES ('М'ясо', 'Г', 'Шашлик', 34, 150);
При відомої трудомісткості приготування шашлику (наприклад, 5 коп) відомості про нього можна ввести за допомогою укороченого пропозиції:
INSERT
INTO Страви
VALUES (34, 'Шашлик', 'Г', 'М'ясо', 150, 5);
в якому повинен дотримуватися строгий порядок перерахування вводятьсязначень, оскільки, не маючи переліку завантажуються стовп-цов, СУБД може використовувати лише перелік, який визначений при створенні модифікується таблиці.
У попередніх прикладах проводилася модифікація стрижневий сутності, тобто таблиці з первинним ключем БЛ (см.п.2.4 в літературі [2]). Майже всі СУБД мають механізми для запобігання вводити не унікального первинного ключа, наприклад, введення "Шашлика" під номером, меншим 34. А як бути з асоціаціями або іншими таблицями, що містять зовнішні ключі?
Нехай, наприклад, знадобилося додати в рецепт страви Салат літній (БЛ = 1) небагато (15 г) лука (ПР = 10), і ми скористалися пропозицією
INSERT
INTO Склад (БЛ, ПР, Вага)
VALUES (1, 10, 15);
Подібно операції DELETE операція INSERT може порушити несуперечливість бази даних. Якщо не вжити спеціальних заходів, то СУБД не перевіряє, чи є в таблиці Страви блюдо з первинним ключем БЛ = 1 і в таблиці Продукти - продукт з первинним ключем ПР = 10. Відсутність будь-якого з цих значень породить протиріччя: в базі з'явиться посилання на неіснуючу запис. Проблеми, що виникають при використанні зовнішніх ключів, детально розглянуті в літературі, а тут отме-тім, що всі "пристойні" СУБД мають механізми для запобігання утворення-рощення введення записів зі значеннями зовнішніх ключів, отсутст-чих серед значень відповідних первинних ключів.
2. Вставка безлічі записів
Створити тимчасову таблицю К_меню, що містить калорійність і вартість усіх страв, які можна приготувати з наявних продуктів. (Ця таблиця буде використовуватися шеф-кухарем для складання меню на наступний день.)
Для створення опису тимчасової таблиці можна, наприклад, скористатися пропозицією CREATE TABLE (см.п.5.2)
CREATE TABLE К_меню
(Вид CHAR (10),
Блюдо CHAR (60),
Калор_блюда INTEGER,
Стоім_блюда REAL);
а для її завантаження даними - пропозиція INSERT з вкладеним підзапитах:
INSERT
INTO К_меню
SELECT Вид, Страва,
INT (SUM (((Білки + Углєв) * 4.1 + Жири * 9.3) * Вага / 1000)),
(SUM (Вартість / К_во * Вага / 1000) + MIN (Праця / 100))
FROM Блюда, Від_блюд, Склад, Продукти, Наявність
WHERE Блюда.БЛ = Состав.БЛ
AND Состав.ПР = Продукти.ПР
AND Состав.ПР = Налічіе.ПР
AND Блюда.В = Від_блюд.В
AND БЛ NOT IN
(SELECT БЛ
FROM Склад
WHERE ПР IN
(SELECT ПР
FROM Наявність
WHERE К_во = 0))
GROUP BY Вид, Блюдо
ORDER BY Вид, 3;
У цьому запиті пропозицію SELECT виконується так само, як звичайно (див. Опис запиту в п.3.6), але результат не виводиться на екран, а копіюється в таблицю К_меню. Тепер з цією копією можна працювати як зі звичайною базової таблицею (Страви, Про-дукти, ...), тобто вибирати з неї Данн на екран або принтер, оновлювати в ній дані і т.п. Ніяка з цих операцій не буде робити впливу на вихідні дані (наприклад, зміна в ній назви страви Салат літній на Салат весняний не приведе до подібного зміни в таблиці Страви, де збережеться стара назва). Так як це може привести до протиріч, то подібні тимчасові таблиці знищують після їх використання. Тому програма, яка обслуговує шеф-кухаря, повинна виконувати пропозицію DROP TABLE К_меню після того, як буде закінчено складання меню.
27. Речення UPDATE. Оновлення одного запису. Оновлення множини записів. Оновлення з підзапитом.
1. Оновлення єдиною записи
Змінити назву страви з кодом БЛ = 5 на Форшмак, збільшити його вихід на 30 г і встановити NULL-значення в стовпець Праця.
UPDATE Страви
SET Блюдо = 'Форшмак', Вихід = (Вихід + 30), Праця = NULL
WHERE БЛ = 5;
2. Оновлення безлічі записів
Потроїти ціну всіх продуктів таблиці поставки (крім ціни кави - ПР = 17).
UPDATE Поставки
SET Ціна = Ціна * 3
WHERE ПР <> 17;
3. Оновлення з підзапитом
Встановити рівною нулю ціну і К_во продуктів для постачальників з Паневежиса та Резекне.
UPDATE Поставки
SET Ціна = 0, К_во = 0
WHERE ПС IN
(SELECT ПС
FROM Постачальники
WHERE Місто IN ('Паневежис', 'Резекне'));
28.Етап фізичного проектування. Основні структури зберігання та методи доступу до даних. Основні поняття. Невпорядковані послідовні файли.
Фізичне проектування бази даних. Процес підготовки опису реалізації бази даних зовнішньої пам'яті; опис повинен включати основні відношення, файлову організаціюб, індекси, що забезпечують ефективний доступ до даних, а також всі відповідні обмеження цілісності й засоби захисту.
У цій главі описуються наступні етапи методології фізичного проектування баз даних.
1.Перенос глобальної логічної моделі даних у середовище цільової СКБД.
2.Проектування основних відношень.
3.Розробка способів одержання похідних даних.
4.Реалізація обмежень предметної області.
5.Проектування фізичного представлення бази даних.
6.Аналіз транзакцій.
7.Вибір файлової структури.
8.Визначення індексів.
9.Визначення вимог до дискової пам'яті.
10.Проектування користувальницьких представлень.
11.Розробка механізмів захисту.
12.Обґрунтування необхідності введення контрольованої надлишковості.
13.Поточний контроль і настроювання операційної системи.
Методологія фізичного проектування баз даних реляційного типу
Етап 4. Перенос глобальної логічної моделі даних у середовище цільової СКБД
Ціль Створення базової функціональної схеми реляційної бази даних на основі глобальної логічної моделі даних, що може бути реалізована в цільовий СКБД.
Найпершим завданням на етапі фізичного проектування баз даних є перетворення відношень, створених на основі глобальної логічної моделі даних, у таку форму, що може бути реалізована в середовищі цільової СКБД. Цей процес вимагає наявності глибоких знань про функціональні можливості, що надаються цільовою СКБД. Зокрема, розробник повинен знати наступне:
- способи створення основних відношень;
- чи підтримує система визначення первинних, зовнішніх й альтернативних ключів;
- чи підтримує система визначення обов'язкових даних (тобто чи допускає система вказувати у визначенні атрибута, що для нього заборонене використання значення NULL);
- чи підтримує система визначення доменів;
- чи підтримує система реляційні обмеження цілісності;
- чи підтримує система визначення обмежень предметної області.
На цьому етапі процедури розробки баз даних виконуються наступні дії.
1.Проектування основних відношень.
2.Розробка способів одержання похідних даних.
3.Реалізація обмежень предметної області.
Етап 4.1 Проектування основних відношень
1 2 3 4 5 6 7 8 9