|
БАЗИ ДАНИХ. МОВИ ЗАПИТІВ, УПРАВЛІННЯ ТРАНЗАКЦІЯМИ, РОЗПОДІЛЕНА ОБРОБКА ДАНИХ |
|||||||||||||||||||||||||||||||||||||
|
1.4 Формування запитів мовою SQL
1.4.1 Обчислення в запитах
Вибірку і відображенні даних однієї або декількох таблиць бази даних виконують за допомогою оператора SELECT, який є найбільш затребуваним оператором мови SQL. Загальний формат оператора SELECT має наступний вигляд:
SELECT [DISTINCT | ALL] <список даних> FROM <список таблиць> [WHERE <умова вибірки>] [GROUP BY <ім’я стовпця>[, <ім’я стовпця>]…] [HAVING <умова пошуку>] [ORDER BY <специфікація>[, <специфікація>]…];
Обробка елементів оператора SELECT виконується в наступній послідовності. 1. FROM. Визначаються імена використовуваної таблиці або декількох таблиць. 2. WHERE. Виконується фільтрація рядків об'єкта відповідно до заданими умовами. 3. GROUP BY. Утворюються групи рядків, що мають одне і те ж значення в зазначеному стовпці. 4. HAVING. Фільтруються групи рядків об'єкта відповідно до зазначеної умови. 5. SELECT. Встановлюється, які стовпці повинні бути присутніми у вихідних даних. 6. ORDER BY. Визначається впорядкованість результатів виконання оператора. Порядок конструкцій в операторі SELECT не може бути змінений. Тільки дві конструкції оператора – SELECT і FROM – є обов'язковими, решта конструкцій можуть бути опущені. Для того, всю інформацію про всіх співробітників компанії, необхідно перерахувати повний список стовпців таблиці в операторі SELECT
SELECT staffNo, fName, IName, position, sex, DOB, salary, branchNo FROM Staff;
або замість імен стовпців вказати символ зірочки (*)
SELECT * FROM Staff;
Обидва варіанти запиту є повністю еквівалентними один одному. По замовчуванню в результуючу таблицю включаються усі рядки, навіть повторювані, що відповідає ключовому слову ALL. Для видалення з результуючої таблиці повторюваних рядків використовується ключове слово DISTINCT:
SELECT DISTINC рropertyNo FROM Viewing;
Список даних може містити імена стовпців, що приймають участь в запиті, а також арифметичні вирази над стовпцями
У загальному випадку для створення обчислюваного поля в списку оператора SELECT слід вказати деякий вираз мови SQL. У цих виразах можуть застосовуватися операції додавання, віднімання, множення і ділення. При побудові складних виразів можуть використовуватися круглі дужки. Для отримання значення обчислюваного поля може використовуватися значення з декількох стовпців таблиці, однак тип даних стовпців, які входять в арифметичний вираз, обов'язково повинен бути цифровим. Зазвичай стовпцям результуючої таблиці присвоюються імена відповідних їм стовпців вихідних таблиць бази даних. Однак у випадку з обчислюваними полями це правило не застосовується, оскільки в стандарті SQL не визначені правила іменування похідних стовпців. В одних діалектах мови SQL імена таким стовпцями привласнюють відповідно до порядку їх розташування в таблиця (наприклад, col4), в інших діалектах у подібного стовпець ім'я може бути зовсім відсутнім або замість нього може використовуватися вираз, що записаний в списку SELECT.
Приклад 1.20. Обчислювані поля Створити звіт про річну заробітну плату співробітників з вказанням табельного номера, імені, прізвища та суми заробітної плати.
SELECT staffNo, fName, IName, salary*12 FROM Staff;
Результат виконання запиту може мати такий вигляд:
Стандарт ISO дозволяє явно задавати інші імена стовпців результуючої таблиці, для чого застосовується конструкція AS. При використанні цієї конструкції наведений вище оператор SELECT може бути переписаний таким чином чином:
SELECT staffNo, fName, IName, salary*12 AS yearSalary FROM Staff;
1.4.2 Вибірка рядків конструкцією WHERE
У наведених вище прикладах в результаті виконання операторів SELECT вибиралися всі рядки зазначеної таблиці. Для того, щоб обмежити набір рядків, які мають бути поміщені в результуючу таблицю запиту, використовується конструкція WHERE. Вона складається з ключового слова WHERE, за яким слідує перелік умов пошуку, що визначають ті рядки, які повинні бути обрані при виконанні запиту. Розрізняють п'ять основних типів умов пошуку (або предикатів, якщо користуватися термінологією ISO): - Порівняння. Порівнюються результати обчислення одного виразу з результатами обчислення іншого виразу. - Діапазон. Перевіряється, чи потрапляє результат обчислення виразу в заданий діапазон значень. - Належність до множини. Перевіряється, чи належить результат обчислення виразу до заданої множини значень. - Відповідність шаблону. Перевіряється, чи відповідає деяке рядкове значення заданому шаблону. - Значення NULL. Перевіряється, чи містить даний стовпець NULL (Невизначене значення).
В мові SQL можна використовувати прості оператори порівняння такі як більше ( > ), менше ( < ), дорівнює ( = ), не дорівнює ( < > або != ), більше або дорівнює ( >= ), менше або дорівнює ( <= ).
Приклад 1.21. Умова пошуку шляхом порівняння Скласти список співробітників з розміром заробітної плати більше 7000 грн. в місяць.
SELECT staffNo, fName, IName, position, salary FROM Staff WHERE salary > 10000;
Більш складні умови пошуку можуть бути побудовані з використанням логічних операторів AND, OR і NOT, а також за допомогою круглих дужок. Так, якщо необхідно скласти список співробітників, які отримують заробітну плату 3200 грн. або 6400 грн., то запит матиме вигляд:
SELECT staffNo, fName, IName, position, salary FROM Staff WHERE salary = 3200 OR salary = 6400;
Приклад 1.22. Використання діапазону (оператор BETWEEN/ NOT BETWEEN) Скласти список співробітників, чия заробітна плата становить від 3200 грн. до 10000 грн.
SELECT staffNo, fName, IName, position, salary FROM Staff WHERE salary BETWEEN 3200 AND 10000;
Конструкція NOT BETWEEN має протилежне значення і використовується у випадках, коли потрібно відібрати ті значення, що лежать за межами заданого діапазону.
Приклад 1.23. Перевірка належності до множини (оператор IN/NOT IN). Скласти список всіх керівників та їх замісників.
SELECT staffNo, fName, IName, position FROM Staff WHERE position IN (‘Директор’, ‘Замісник директора’);
Для відбору будь-яких значень, крім тих, що вказані у списку, використовується конструкція NOT IN. Оператори BETWEEN та IN несуттєво підвищують виразність мови SQL, тому на практиці часто їх заміняють логічними операторами. Запит з прикладу 1.22 можна записати так:
SELECT staffNo, fName, IName, position, salary FROM Staff WHERE salary >=3200 AND salary <=10000;
Запит з прикладу 1.23 може мати такий вигляд:
SELECT staffNo, fName, IName, position FROM Staff WHERE position= ‘Директор’ OR position= ‘Замісник директора’;
Використання оператора IN є більш доцільним у випадках, коли набір допустимих значень є достатньо великим.
Приклад 1.24. Умова пошуку з вказанням шаблону (LIKE/NOT LIKE). Вивести список всіх власників квартир, що здаються в оренду у місті Вінниця.
При виконанні цього запиту необхідно організувати пошук рядків, де назва міста ‘Вінниця’ може знаходитись у будь-якому місці значення стовпця address таблиці PrivatOwner. В мові SQL існує два спеціальні символи шаблону, які використовуються для перевірки символьних значень: - %. Символ відсотка замінює будь-яку послідовність з нуля і більше символів. - _ . Символ підкреслення замінює будь-який окремий символ. - address LIKE ‘В%’. Такий шаблон означає, що перший символ значення повинен обов’язково бути символом ‘В’, а решта символів значення не мають і не перевіряються. - address LIKE ‘В___’. Такий шаблон означає, що значення повинно мати довжину в 4 символи, причому першим символом значення повинен обов’язково бути символом ‘В’. - address LIKE ‘%а’. Такий шаблон визначає будь-яку послідовність символів довжиною не менше одного символу, причому останнім символом обов’язково має бути символ ‘а’. - address LIKE ‘%Вінниця%’. Такий шаблон означає, що шукане значення має будь-яку послідовність символів, яка включає підрядок ‘Вінниця’. - address NOT LIKE ‘В%’. Такий шаблон означає, що необхідно знайти будь-які рядки, які не починаються з символу ‘В’. Якщо шуканий рядок повинен включати службовий символ ‘%’ або ‘ _’, то за допомогою конструкції ESCAPE визначається деякий «маскуючий» символ, який вказує, що наступний за ним символ більше не має спеціального значення. Наприклад, для перевірки значення на відповідність рядку ‘45%’ можна скористатись такою умовою:
LIKE ‘45#%’ ESCAPE ‘#’
За допомогою механізму пошуку по шаблону запит виведення списку всіх власників квартир, що здаються в оренду у місті Вінниця, матиме такий вигляд:
SELECT ownerNo, fName, IName, address, telNo FROM PrivateOwner WHERE address LIKE ' %Вінниця%';
Приклад 1.25. Використання значення NULL в умовах пошуку (IS NULL/IS NOT NULL). Скласти список всіх відвідувань об’єкта з номером ‘КР4’, по яким не було залишено коментарів.
SELECT clientNo, viewDate FROM Viewing WHERE propertyNo = 'КР4' AND comment IS NULL;
Для перевірки наявності у стовпці значень, відмінних від NULL, може використовуватись конструкція IS NOT NULL.
1.4.3 Сортування результатів (конструкція ORDER BY)
В деяких СКБД може бути передбачено застосування за замовчуванням певного способу впорядкування рядків результуючої таблиці, наприклад за первинним ключем. Однак їх можна впорядкувати належним чином, для чого в операторі SELECT поміщають конструкцію ORDER BY. Конструкція ORDER BY включає список розділених комами ідентифікаторів стовпців, за якими потрібно впорядкувати результуючу таблицю запиту. Ідентифікатором стовпця може бути або його ім'я, або номер, який позначає елемент списку SELECT відповідно до його позиції в цьому списку. Крайній лівий елемент списку має номер 1, наступний – 2 і т.д. Номера стовпців можуть використовуватися в тих випадках, коли стовпі, за якими слід упорядкувати результат, є обчислюваними, а конструкція AS із зазначенням імені цього стовпця в операторі SELECT відсутня. Конструкція ORDER BY дозволяє упорядкувати вибрані записи в порядку зростання (ASC) чи зменшення (DESC) значень будь-якого стовпця або комбінації стовпців, незалежно від того, чи присутні ці стовпці в таблиці результатів чи ні.
Приклад 1.26. Сортування за значенням одного стовпця. Скласти звіт про середні бали студентів груп ПІ-13, розмістивши рядки за спаданням значення середнього балу.
SELECT name, group, averageMark FROM Student WHERE group LIKE ‘_ПІ-13%’ ORDER BY averageMark DESC;
Конструкція ORDER BY для прикладу 1.26 може бути записана і у вигляді ORDER BY 3 DESC, де 3 означає третій стовпець у списку вибірки оператора SELECT, тобто стовпець averageMark.
Приклад 1.27. Сортування за значеннями декількох стовпців. Скласти список квартир, що здаються в оренду, відсортувавши рядки за зростанням кількості кімнат, причому спочатку потрібно відображати найдорожчі квартири.
SELECT flatNo, rooms, rent FROM FlatForRent ORDER BY rooms, rent DESC;
Ключове слова ASC після стовпця rooms може бути пропущене, оскільки воно передбачається за замовчуванням. Конструкція ORDER BY завжди повинна бути останнім елементом в операторі SELECT. У стандарті ISO зазначено, що значення NULL в стовпці або виразах, для сортування яких застосовується конструкція ORDER BY, повинні розглядатися або як менші, або як більші за величиною, ніж всі непусті значення. Вибір того чи іншого варіанту залежить від розробників СКБД.
1.4.4 Використання вбудованих функцій
Стандарт ISO містить визначення таких п'яти вбудованих функції: - COUNT – повертає кількість значень у вказаному стовпці; - SUM – повертає суму значень в зазначеному стовпці; - AVG – повертає середнє значення в зазначеному стовпці; - MIN – повертає мінімальне значення в зазначеному стовпці; - МАХ – повертає максимальне значення у вказаному стовпці. Всі ці функції оперують зі значеннями в єдиному стовпці таблиці і повертають єдине значення. Функції COUNT, MIN і МАХ застосовуються як до числових, так і до нечислових полів, тоді як функції SUM і AVG – лише до числових полів. Варіант COUNT (*) є особливим випадком використання функції COUNT – його призначення полягає в підрахунку всіх рядків в таблиці, незалежно від того, містяться там порожні, повторювані або будь-які інші значення. У випадку використання всіх інших функцій спочатку виключаються всі порожні значення. Якщо до застосування вбудованої функції необхідно виключити повторювані значення, слід перед ім'ям стовпця у визначенні функції помістити ключове слово DISTINCT. Стандарт ISO допускає використання ключового слова ALL з метою явної вказівки того, що виключення повторюваних значень проводити не потрібно, хоча це ключове слово мається на увазі за замовчуванням. Вбудовані функції можуть використовуватися тільки в списку вибірки оператора SELECT і в конструкції HAVING (див. п.1.4.5). Якщо в операторі SELECT не використовується конструкція GROUP BY, то у його списку вибірки не може бути присутнім жодний стовпець, який не є параметром вбудованої функції. Тобто, запит, що наведений нижче є некоректним:
SELECT flatNo, AVG(rent) FROM FlatForRent;
Помилка полягає в тому, що в запиті відсутня конструкція GROUP BY, а звернення до стовпця flatNo відбувається без вбудованої функції.
Приклади 1.28. Використання функції COUNT. Визначити, у скількох квартир, що здаються в оренду, орендна плата перевищує 4000 грн.
SELECT COUNT(*) AS count FROM FlatForRent WHERE rent > 4000;
Визначити, скільки різних квартир було переглянуто в січні 2017 року.
SELECT COUNT(DISTINCT flatNo) AS count FROM Viewing WHERE ViewDate BETWEEN ‘1-January-17’ AND ’31-January-17’;
Приклад 1.29. Використання функції SUM. Визначити загальну кількість директорів компанії та загальну суму їх місячної заробітної плати.
SELECT COUNT(staffNo) AS count, SUM(salary) AS sum FROM Staff WHERE position = 'Директор';
Приклад 1.30. Використання функцій MIN, MAX і AVG. Обчислити значення максимальної, мінімальної та середньої заробітної плати співробітників компанії.
SELECT MAX(salary) AS max, MIN(salary) AS min, AVG(salary) AS avg FROM Staff;
В даному прикладі необхідно опрацювати відомості про всіх співробітників компанії, тому використовувати конструкцію WHERE не потрібно.
1.4.5 Групування результатів (конструкція GROUP BY/HAVING)
Запит, в якому присутня конструкція GROUP BY, називається групуючим запит, оскільки в ньому групуються дані, отримані в результаті виконання операції SELECT, після чого для кожної окремої групи створюється єдиний підсумковий рядок. При використанні в операторі SELECT конструкції GROUP BY кожен елемент списку в списку вибірки SELECT повинен мати єдине значення для всієї групи. Всі імена стовпців, наведені в списку вибірки SELECT, повинні бути присутніми і в конструкції GROUP BY, за винятком випадків, коли ім'я стовпця використовується тільки у вбудованій функції. В конструкції ж GROUP BY можуть бути присутніми імена стовпців, які відсутні в списку вибірки SELECT. Якщо спільно з конструкцією GROUP BY використовується конструкція WHERE, то вона обробляється в першу чергу, а групуванню піддаються тільки ті рядки, які задовольняють умові пошуку. Стандарт ISO визначає, що при проведенні групування все порожні значення розглядаються як рівні. Якщо два рядки таблиці в групуючому стовпці містять значення NULL та ідентичні значення у всіх інших непустих групуючих стовпцях, вони поміщаються в одну і ту ж групу.
Приклад 1.31. Використання конструкції GROUP BY. Визначити чисельність персоналу у кожному відділі, а також їх загальну заробітну плату.
SELECT branchNo, COUNT(staffNo) AS count, SUM(salary) AS sum FROM Staff GROUP BY branchNo ORDER BY branchNo;
Результат виконання такого запиту може мати вигляд:
При обробці цього запиту виконуються наступні дії. 1. Рядки таблиці Staff розподіляються в групи відповідно до значення в стовпці branchNo компанії. У межах кожної з груп виявляються дані про весь персонал одного з відділень компанії. 2. Для кожної з груп обчислюються загальна кількість рядків, рівне чисельності працівників відділення, а також сума значень в стовпці заробітної плати, яка і є шуканою сумою заробітної плати всіх працівників відділення. Потім генерується єдиний підсумковий рядок для всієї групи вихідних рядків. 3. Отримані рядки результуючої таблиці сортуються в порядку зростання номер відділення, зазначеного в стовпці branchNo. Конструкція HAVING призначена для використання спільно з конструкцією GROUP BY для завдання обмежень, що зазначені з метою відбору тих груп, які будуть поміщені в результуючу таблицю запиту. Стандарт ISO вимагає, щоб імена стовпців, що застосовуються в конструкції HAVING, обов'язково були присутні в списку елементів GROUP BY або використовувались у вбудованій функції. На практиці умови пошуку в конструкції HAVING завжди включають, щонайменше, одну вбудовану функцію. Інакше ці умови пошуку повинні бути поміщені в конструкції WHERE і застосовані для відбору окремих рядків.
Приклад 1.32. Використання конструкції HAVING. Для кожного відділення компанії з чисельністю працівників більше 9 визначити чисельність персоналу, а також їх загальну заробітну плату.
SELECT branchNo, COUNT(staffNo) AS count, SUM(salary) AS sum FROM Staff GROUP BY branchNo HAVING COUNT(staffNo) > 9 ORDER BY branchNo;
Результат виконання такого запиту може мати вигляд:
Конструкція HAVING не є обов’язковою частиною мови SQL – будь-який запит, написаний з використанням конструкції HAVING, може бути поданий в іншому вигляді, без її застосування.
1.4.6 Вкладені запити (підзапити)
Стандарт SQL допускає розміщення в операторі SELECT вкладених запитів. Зовнішній оператор SELECT використовує результат виконання внутрішнього оператора для визначення змісту остаточного результату всієї операції. Внутрішні запити можуть знаходитися в конструкціях WHERE і HAVING, а також у списку вибірки зовнішнього оператора SELECT. В цьому випадку вони отримують назву підзапитів, або вкладених запитів. Також, внутрішні оператори SELECT можуть використовуватися в операторах INSERT, UPDATE і DELETE. Існує три типи підзапитів: 1. Скалярний підзапит повертає значення, що отримується з перетину одного стовпця з одним рядком, тобто єдине значення. Варіанти використання скалярних підзапитів наведені в прикладах 1.28. 2. Рядковий підзапит повертає значення декількох стовпців таблиці, але у вигляді єдиного рядка. Варіант рядкового підзапиту наведено в прикладах 1.29 і 1.30. 3. Табличний підзапит повертає значення одного або декількох стовпців таблиці, що розміщені в більш ніж одному рядку.
Приклад 1.33. Використання скалярного підзапиту в конструкції WHERE. Скласти список співробітників, які працюють у відділенні компанії, яке розташоване по вулиці ‘Хмельницьке шосе, 90’.
SELECT staffNo, fName, IName, position FROM Staff WHERE branchNo = (SELECT branchNo FROM Branch WHERE street = ‘Хмельницьке шосе, 90’);
Внутрішній оператор SELECT призначений для визначення номера відділення компанії, розташованого по вулиці ‘Хмельницьке шосе, 90’. Після отримання номера необхідного відділення виконується зовнішній підзапит, призначений для вибірки відомостей про працівників цього відділення. В результаті зовнішній оператор SELECT набуває вигляду:
SELECT staffNo, fName, IName, position FROM Staff WHERE branchNo = ‘Vin03’;
Підзапит можна вказувати безпосередньо після операторів порівняння (тобто операторів =, <,>, <=,> =, <>) в конструкції WHERE або HAVING. Текст під запиту повинен бути розміщений в круглих дужках.
Приклад 1.34. Використання підзапитів з вбудованими функціями. Скласти список всіх співробітників, які мають заробітну плату вище середньої, зазначивши, на скільки їх заробітна плата перевищує середню.
SELECT staffNo, fName, IName, position, salary - (SELECT AVG(salary) FROM Staff) AS salDiff FROM Staff WHERE salary > (SELECT AVG(salary) FROM Staff);
Необхідно відзначити, що не можна безпосередньо включити в запит вираз WHERE salary > AVG (salary), оскільки застосовувати вбудовані функції в конструкції WHERE заборонено. Для досягнення бажаного результату слід створити підзапит, що обчислює середнє значення заробітної плати, а потім використовувати його в зовнішньому операторі SELECT, призначеному для вибірки відомостей про тих працівниках компанії, чия зарплата перевищує це середнє значення.
До підзапитів застосовуються такі правила і обмеження. 1. У підзапитах не повинна використовуватися конструкція ORDER BY, хоча вона може бути присутньою в зовнішньому операторі SELECT. 2. За замовчуванням імена стовпців в підзапиті відносяться до таблиці, ім'я якої зазначено в конструкції FROM підзапиту. Однак дозволяється посилатися і на стовпці таблиці, зазначеної в конструкції FROM зовнішнього запиту, для чого використовуються уточнені імена стовпців (як описано нижче). 3. Якщо підзапит є одним з двох операндів, що беруть участь в опера- ції порівняння, то підзапит повинен вказуватися в правій частині цієї опе-рації. З підзапитами, які повертають один стовпець чисел, можуть використовуватися ключові слова ANY і ALL. Якщо підзапиту передуватиме ключове слово ALL, умова порівняння вважається виконаною тільки в тому випадку, якщо вона виконується для всіх значень в результуючому стовпці підзапиту. Якщо підзапиту передує ключове слово ANY, то умова порівняння буде вважатися виконаною, якщо вона задовольняється хоча б для будь-якого (одного або декількох) значень в результуючому стовпці підзапиту. Якщо в результаті виконання підзапиту буде отримано порожнє значення, то для ключового слова ALL умова порівняння буде вважатися виконаною, а для ключового слова ANY – невиконаною. Відповідно до стандарту ISO додатково можна використовувати ключове слово SOME, що є синонімом ключового слова ANY.
Приклад 1.35. Використання ключового слова ANY/ SOME. Знайти всіх співробітників, чия заробітна плата перевищує заробітну плату хоча б одного співробітника відділення компанії з номером ‘Vin03’.
SELECT staffNo, fName, IName, position, salary FROM Staff WHERE salary > SOME (SELECT salary FROM Staff WHERE branchNo = 'Vin03');
Цей запит міг бути записаний з використанням підзапиту, що визначає мінімальну зарплату персоналу відділення під номером 'Vin03', після чого зовнішній підзапит зможе вибрати відомості про весь персонал компанії, чия зарплата перевищує це значення (див. приклад 1.34).
Приклад 1.36. Використання ключового слова ALL. Знайти всіх співробітників, чия заробітна плата перевищує заробітну плату будь-якого співробітника відділення компанії з номером ‘Vin03’.
SELECT staffNo, fName, INarae, position, salary FROM Staff WHERE salary > ALL(SELECT salary FROM Staff WHERE branchNo = 'Vin03');
В даному випадку можна було б використовувати підзапит, який визначає максимальне значення зарплати персоналу відділення під номером 'Vin03', після чого за допомогою зовнішнього запиту вибрати відомості про всіх працівників компанії, зарплата яких перевищує це значення.
|
|||||||||||||||||||||||||||||||||||||
|
Пєтух А.М., Романюк О.В., Романюк О.Н. ВНТУ 2016 |