Операції та агрегатні функції.
При створенні запитів на мові SQL використовуються реляційні та булеві оператори, спеціальні оператори в умовах, а також агрегатні функції.
Реляційні операції – це математичні символи, які вказують на певний тип порівняння двох значень. SQL використовує наступні операції:
| = | рівне; |
| > | більше ніж; |
| < | менше ніж; |
| >= | більше або рівне; |
| <= | менше або рівне; |
| <> | не дорівнює. |
Ці операції мають стандартні значення для числових значень. Для символьних значень їх визначення залежить від формату перетворення ASCII або EBCDIC.
Основні булеві операції (NOT, AND, OR) також розпізнаються. Булеві вирази набувають значення істина або хибність.
Також SQL використовує спеціальні операції:
IN – визначає набір значень, в який дане значення може бути включене або ні (A IN (1, 2, 3, 4, 5); B IN (‘X’, ‘Y’, ‘Z’));
BETWEEN – визначає діапазон значень, куди дане значення може входити (A BETWEEN .1 AND .5; B BETWEEN ‘X’ AND ‘Z’));
LIKE – використовується тільки для даних символьних типів, де у значеннях шукає входження рядка; при використанні символ (_) заміщує будь-який символ, а (%) заміщує послідовність будь-яких символів (Text LIKE S_ – набуде істини, якщо значення поля Text починається з літери S, а другий символ є будь-яким одиночним; Text LIKE S% – набуде істини, якщо значення поля Text починається з літери S);
IS NULL – вказує на невизначене значення.
Агрегатні функції видають одиночне значення для всієї групи таблиць:
|
|
|
COUNT – видає кількість вибраних значень;
SUM – видає арифметичну суму вибраних значень;
AVG – видає середнє значення вибраних значень;
MAX – видає найбільше значення серед вибраних значень;
MIN – видає найменше значення серед вибраних значень.
Вибірка даних
Оператор SELECT
Оператор SELECT – дозволяє проводити вибірки даних з таблиць і перетворювати до потрібного вигляду отримані результати.. Синтаксис:
SELECT [ALL | DISTINCT | DISTINCTROW | TOP]{* | <ім’я таблиці>.* | [<ім’я таблиці>.]<назва поля>
[AS <псевдонім>] [, …]}
FROM <ім’я таблиці> [, …] [IN <ЗБД>]
[WHERE …]
[GROUP BY …]
[HAVING …]
[ORDER BY …];
ALL | DISTINCT | DISTINCTROW | TOP – предикати, які використовуються для обмеження числа записів, що повертаються. По замовчуванню використовується ALL (повертає усі значення), якщо предикати відсутні. DISTINCT – виключає записи, що містять значення, які повторюються у вибраних полях. DISTINCTROW – ігнорує дані, які ґрунтуються на записах, що цілком повторюються, а не окремих полів. Впливає на результат тоді, коли до запиту включені не всі поля з таблиці, що аналізуються. TOP n [PERCENT] – повертає певне число записів, що знаходяться на початку або в кінці діапазону описаного за допомогою ORDER BY. «*» – вибрані всі поля заданої таблиці або таблиць. <ім’я таблиці> – назва таблиці, з якої потрібно вибрати записи. <назва поля> – назва поля, з якого вибирають дані; якщо ж задано декілька полів, то дані вибираються у визначеному порядку. AS – задає назву заголовка стовпця у запиті. <псевдонім> – назва, що буде заголовком стовпця замість початкового. IN – оператор входження. <ЗБД> – ім’я БД, що містить таблиці, які вказані за допомогою FROM, якщо вони не знаходяться у поточній БД. Даний оператор SELECT не змінює дані у БД, де відбувається вибір. Кожен запит завершується символом крапка з комою.
|
|
|
Приклади. Розглянемо таблицю Students (табл. 1), яка складається з п’яти полів і п’яти записів: Num містить порядкові номери студентів; Surname - прізвища студентів; Name - імена студентів; Age - вік студентів; Speciality – спеціальність, на якій судент навчається.
Таблиця 1
| Num | Surname | Name | Age | Speciality |
| 1 | Крупик | Віктор | 19 | Інформатика |
| 2 | Прапій | Степан | 19 | Інформатика |
| 3 | Працків | Оксана | 18 | Інформатика |
| 4 | Селань | Віктор | 20 | Математика |
| 5 | Ятрів | Олег | 17 | Інформатика |
Дана таблиця буде використовуватись при розгляді решти прикладів.
Приклад 1.
SELECT * FROM Students;
Дана команда виводить усі значення (табл. 2.2) таблиці Students (табл. 2.1).
|
|
|
Таблиця 2
| Num | Surname | Name | Age | Speciality |
| 1 | Крупик | Віктор | 19 | Інформатика |
| 2 | Прапій | Степан | 19 | Інформатика |
| 3 | Працків | Оксана | 18 | Інформатика |
| 4 | Селань | Віктор | 20 | Математика |
| 5 | Ятрів | Олег | 17 | Інформатика |
Приклад 2.
SELECT Num, Surname, Name, Age, Speciality
FROM Students;
Дана команда також виводить усі значення (табл. 3) таблиці Students (табл.1).
Таблиця 3
| Num | Surname | Name | Age | Speciality |
| 1 | Крупик | Віктор | 19 | Інформатика |
| 2 | Прапій | Степан | 19 | Інформатика |
| 3 | Працків | Оксана | 18 | Інформатика |
| 4 | Селань | Віктор | 20 | Математика |
| 5 | Ятрів | Олег | 17 | Інформатика |
Приклад 3.
SELECT Age AS s_age
FROM Students;
Дана команда виведе тільки значення поля Age (вік студентів), з назвою заголовка стовпця s_age (табл. 4.).
Для отримання переліку даних без повторень, використовують оператор DISTINCT.
SELECT DISTINCT Age
FROM Students;
Результатом (табл. 5) будуть унікальні дані. Розм. у 2-а стовпці
Таблиця 4
| S_age |
| 19 |
| 19 |
| 18 |
| 20 |
| 17 |
Таблиця 5
| Age |
| 17 |
| 18 |
| 19 |
| 20 |
| Речення FROM |
Речення FROM міститься у операторі SELECT і є обов’язковим. Вказує на таблицю чи таблиці з якої вибирають дані. Порядок таблиць у виразі не важливий.
|
|
|
FROM <ім’я таблиці> [, …] [IN <ЗБД>];
Тут <ім’я таблиці> – назва таблиці, з якої потрібно вибрати записи; IN – оператор входження; <ЗБД> – ім’я БД, що містить таблиці, які вказані за допомогою FROM, якщо вони не знаходяться у поточній БД.
| Речення WHERE |
Речення WHERE є необов’язковим. Дане речення визначає умову вибірки рядків із таблиць у реченні FROM, що необхідно включити. Якщо речення WHERE не включити у запит, то результатом будуть усі рядки таблиці. Якщо в запиті використовуються декілька таблиць і речення WHERE не включити, то результатом буде скалярний добуток таблиць.
[WHERE <умова>]
де <умова> – умова або умови вибору значень із поля.
Приклад 1. Вибрати прізвища студентів, яким є дев’ятнадцять років. Для цього використаємо речення WHERE.
SELECT Surname, Age
FROM Students
WHERE Age=19;
Результат – таб. 6
Таблиця 6
| Surname | Age |
| Крупик | 19 |
| Прапій | 19 |
Приклад 2. Вибрати студентів, що поступили на інформатику, яким менше 19 років.
SELECT Surname, Age, Speciality
FROM Students
WHERE Speciality='Інформатика' AND Age<19;
Результат - таб. 7
Таблиця 7
| Surname | Age | Speciality |
| Працків | 18 | Інформатика |
| Ятрів | 17 | Інформатика |
| Речення GROUP BY |
Речення GROUP BY є необов’язковим. Використовується при формуванні підсумкових запитів. GROUP BY і об’єднує записи з однаковими значеннями у вказаному списку полів в один запис. Якщо оператор SELECT містить статистичну функцію, то для кожного поля буде розраховано підсумок. Дозволяє застосовувати агрегатні функції до кожної групи, яка визначається загальним значенням поля або полів, вказаних в цьому реченні.
[GROUP BY <назва поля>[,…]]
<назва поля> – поле, по якому відбувається групування.
Приклад. Підрахувати кількість студентів на кожній із спеціальностей і вивести назву спеціальності і кількість студентів на ній. Для зручності скористаємось реченням GROUP BY.
SELECT speciality, COUNT(speciality)
FROM Students
GROUP BY speciality;
Результат - табл. 8
Таблиця 8
| Speciality | COUNT(speciality) |
| Інформатика | 4 |
| Математика | 1 |
| Речення HAVING |
Речення HAVING є необов’язковим. Після того, як записи будуть згруповані за допомогою GROUP BY, речення HAVING відбере ті значення з отриманих записів, що задовольнять умовам вибірки, які вказані у реченні HAVING
[HAVING <умова>]
<умова> – умова або умови відбору значень із отриманих записів.
Приклад. Видати назви спеціальностей на які поступило більше трьох людей та точну їх кількість. Для цього після використання GROUP BY застосуємо речення HAVING.
SELECT Speciality, COUNT(Speciality)
FROM Students
GROUP BY Speciality
HAVING COUNT(Speciality)>3;
Результат - табл. 9
Таблиця 9
| Speciality | COUNT(Speciality) |
| Інформатика | 4 |
| Речення ORDER BY |
Речення ORDER BY є необов’язковим. Дане речення сортує записи, що отримані після виконання запиту за зростанням або спаданням указаних полів. Його необхідно вказувати для сортування результату запиту. За замовчуванням задано порядок сортування за зростанням
[ORDER BY {<назва поля>[ASC|DESC]} [,…]]
<назва поля> – поле, за яким відбувається сортування. ASC (DESC) – зареєстровані слово для розташування елементів у зростаючому (спадному) порядку.
Приклад. Виведемо на екран всю таблицю, яка відсортована у спаданні за віком студентів (табл. 2.10), а потім у зростанні за їх прізвищами.
SELECT *
FROM Students
ORDER BY Age DESC, Surname ASC;
Таблиця 10
| Num | Surname | Name | Age | Speciality |
| 4 | Селань | Віктор | 20 | Математика |
| 1 | Крупик | Віктор | 19 | Інформатика |
| 2 | Прапій | Степан | 19 | Інформатика |
| 3 | Працків | Оксана | 18 | Інформатика |
| 5 | Ятрів | Олег | 17 | Інформатика |
Операція JOIN
Для завдання типу об’єднання у реченні FROM виконують операцію JOIN. Якщо в результат вибірки необхідно включити всі рядки з обох таблиць, що задовольняють умові вибірки, використовується операція INNER JOIN.
FROM <таблиця1> INNER JOIN <таблиця2>
ON {<таблиця1>.<поле1>} <операція> {<таблиця2>.<поле2>}
де <таблиця1>, <таблиця2> – імена таблиць, записи яких підлягають об’єднанню. <поле1>, <поле2> – імена полів, що об’єднуються; якщо поля не є числовими, то повинні мати однаковий тип даних і вміщувати дані одного виду; поля можуть мати різні імена. <оператор> – будь-який оператор порівняння.
Операції JOIN можуть бути вкладеними.
SELECT <поля>
FROM <таблиця1> INNER JOIN
(<таблиця2> INNER JOIN [(] <таблиця3>
[INNER JOIN [(] <таблицяX> [INNER JOIN…)]
ON <таблиця3>.<поле3> <операція> <таблицяХ>.<полеХ>]
ON <таблиця2>.<поле2> <операція> <таблиця3>.<поле3>)
ON <таблиця1>.<поле1> <операція> <таблиця2>.<поле2>;
Для створення зовнішнього об’єднання використовують операції LEFT JOIN, RIGHT JOIN.
FROM <таблиця1> [ LEFT | RIGHT ] JOIN <таблиця2>
ON <таблиця1>.<поле1> <операція> <таблиця2>.<поле2>
де <таблиця1>, <таблиця2> – імена таблиць, що підлягають об’єднанню. <поле1>, <поле2> – імена полів, що об’єднуються. <оператор> – будь-який оператор порівняння.
Операція LEFT JOIN використовується для створення лівого зовнішнього об’єднання, за якого всі записи з першої (лівої) таблиці включаються в динамічний набір, навіть якщо в другій (правій) таблиці немає відповідних до них записів. Якщо у другій таблиці, з якою виконується з’єднання, не має відповідних рядків, то замість значень її полів додається значення Null.
Операція RIGHT JOIN використовується для створення правого зовнішнього об’єднання, за якого всі записи з другої (правої) таблиці включаються в динамічний набір, навіть якщо в першій (лівій) таблиці немає відповідних до них записів. Якщо у першій таблиці, з якою виконується з’єднання, не має відповідних рядків, то замість значень її полів додається значення Null.
Операція LEFT JOIN або RIGHT JOIN можуть бути вкладені в операцію INNER JOIN, але не навпаки.
Вкладені підзапити
SQL дозволяє використовувати одні запти всередені інших запитів, тобто вкладати один запит у інший.
Прикад. Необхідно дізнатись прізвища студентів, вік яких дорівнює мінімальному. Для цього використаємо наступний запит.
SELECT Surname
FROM Students
WHERE Age=(SELECT MIN(Age) FROM Students);
Вкладений підзапит працює наступним чином:
· вибирається рядок з таблиці, назва якого вказана у зовнішньому запиті;
· виконується підзапит і отримане значення застосовується для аналізу цього рядка в умові речення WHERE зовнішнього запиту;
· за результатами оцінки цієї умови виконується рішення про включення або не включення рядка у склад вихідних даних
· процедура виконується для наступного рядка таблиці зовнішнього запиту.
Широко застосовується оператор IN.
Операція UNION
Дана операція створює запит на об’єднання, що поєднує результати кількох незалежних запитів або таблиць. Синтаксис:
[TABLE] <запит1> UNION [ALL] [TABLE]
<запит2> [UNION [ALL] [TABLE] <запитX>[.][..]];
<запит1>, <запит2>, <запитХ> – інструкція SELECT, ім’я збереженого запиту або ім’я збереженої таблиці, перед яким стоїть зарезервоване слово TABLE.
За замовчуванням записи, що повторюються не повертаються при використанні операції UNION. Всі запити, включені в операцію UNION, повинні відбирати однакове число полів.
Приклад. Об’єднаємо два запити, один з яких ...... (табл. 11):
SELECT * ;
Таблиця 11
| Surname | Name |
Оператор SELECT…INTO
Даний оператор створює запит на створення нової таблиці. У новій таблиці можна визначити ключ. При створенні нової таблиці поля у ній успадковують типи даних і розміри базових полів, інші властивості не передаються.
SELECT <назва поля> [, …]
INTO <нова таблиця> [IN <ЗБД>]
FROM <ім’я таблиці> [, …] [IN <ЗБД>];
<назва поля> – поле, що копіюється у нову таблицю. <нова таблиця> – ім’я таблиці, що створюється. <ЗБД> – зовнішня БД; шлях до зовнішнього джерела. Запит на створення нової таблиці можна використати для створення резервних копій, як основу тощо.
Приклад. У подальшому може виникнути потреба опрацювання інформації про студентів, що вчаться по спеціальності інформатика. У цьому випадку доцільно створити нову таблицю, що буде містити інформацію тільки про цих студентів.
SELECT Num, Surname, Same, Age INTO Informatics
FROM Students
WHERE Speciality='Інформатика';
Після виконання даного запиту ми отримаємо нову таблицю Informatics (табл. 12), яка буде містити дані тільки про студентів зі спеціальністю інформатика.
Таблиця 12
| Num | Surname | Name | Age |
| 1 | Крупик | Віктор | 19 |
| 2 | Прапій | Степан | 19 |
| 3 | Працків | Оксана | 18 |
| 5 | Ятрів | Олег | 17 |
Маніпулювання даними
Оператор INSERT INTO
Даний оператор додає запис або записи у таблицю. Він створює запит, що дописує записи у таблицю.
INSERT INTO <призначення> [(<назва поля>)[, …]]
VALUES (<значення> [, …]);
<призначення> – ім’я таблиці у яку додають записи. <назва поля> – ім’я поля для дозапису даних, якщо відповідає аргументу призначення. <значення> – значення, що дозаписується у вказане поле. Розділяються комами, текстові значення беруться у лапки.
Якщо оператор INSERT INTO присутній у запиті, то він повинен знаходитись перед оператором SELECT.
Якщо вихідна таблиця містить ключ, то необхідно впевнитись, що у ключове поле або поля додаються унікальні не порожні значення, інакше значення не будуть дозаписані.
Щоб дописати поля в таблицю з полем лічильника та заново пронумерувати додані записи, включати в запит поле лічильника не треба.
Приклад. З’явися новий студент. Необхідно занести у БД його дані. Для цього використаємо оператор INSERT INTO.
INSERT INTO Students ( num, Surname, Name, Age, Speciality )
VALUES (6, 'Куцик', 'Олександр', 16, 'Математика');
Даний запит додасть у кінець таблиці новий запис з інформацією про студента, що прибув (табл. 13).
Таблиця 13
| Num | Surname | Name | Age | Speciality |
| 1 | Крупик | Віктор | 19 | Інформатика |
| 2 | Прапій | Степан | 19 | Інформатика |
| 3 | Працків | Оксана | 18 | Інформатика |
| 4 | Селань | Віктор | 20 | Математика |
| 5 | Ятрів | Олег | 17 | Інформатика |
| 6 | Куцик | Олександр | 16 | Математика |
Оператор UPDATE
Даний оператор створює запит на оновлення, що змінює значення полів вказаної таблиці на основі умови відбору.
UPDATE <таблиця> SET <поле1> = <нове значення>
[<поле2> = <нове значення> [, …]]
WHERE <умова відбору>;
<таблиця> – ім’я таблиці, у якій змінюють дані. <нове значення> – вираз, що визначає значення, що замінює значення у полях <полеХ>, котрі оновлюються. <умова відбору> – вираз, що відбирає записи, які необхідно змінити.
Одночасно можна змінювати значення кількох полів. Оператор UPDATE не приводить до створення результуючого набору записів.
При використанні цього оператора необхідно регулярно створювати резервну копію даних. При ненавмисному оновленні записів їх можна буде відновити.
Приклад. Спеціальність «Інформатика» вирішено було перейменувати на «Прикладна математика», використавши для цього скорочену назву «ПМ». Для того, щоб вручну не змінювати усі записи, доцільно створити запит на оновлення даних.
UPDATE Students SET Speciality = 'ПМ'
WHERE Speciality='Інформатика';
Даний запит зробить заміну значень у вже існуючій таблиці (табл. 14).
Таблиця 14
| Num | Surname | Name | Age | Speciality |
| 1 | Крупик | Віктор | 19 | ПМ |
| 2 | Прапій | Степан | 19 | ПМ |
| 3 | Працків | Оксана | 18 | ПМ |
| 4 | Селань | Віктор | 20 | Математика |
| 5 | Ятрів | Олег | 17 | ПМ |
| 6 | Куцик | Олександр | 16 | Математика |
Оператор DELETE
Даний оператор використовується для створення запитів на вилучення записів. За його допомогою можна вилучати записи з однієї або кількох таблиць, що перелічені у реченні FROM, які задовольняють речення WHERE.
DELETE [<ім’я таблиці>.*]
FROM <ім’я таблиці>
WHERE <умова відбору>;
<ім’я таблиці> – ім’я таблиці з якої вилучають записи. <умова відбору> – вираз, який визначає записи, що необхідно вилучити.
Дана операція зручна для видалення значної кількості записів. При знищенні даних, структура таблиць не втрачається та інші властивості.
Запит на вилучення вилучає записи повністю. Щоб вилучити дані у конкретному полі, необхідно створити запит на оновлення, що змінює значення на NULL.
Відновити видалені записи можна тільки за допомогою резервної копії.
Приклад. В армію почався набір. Усі студенти, які старше сімнадцяти років, вирішили стати військовими і назавжди забрати свої документи. Інформація у БД про них стає зайвою, оскільки всі студенти і так занесені до архіву. Було вирішено видалити їх дані із робочих таблиць. Для цього був створений запит на видалення даних.
DELETE *
FROM Students
WHERE Students.Age>17;
Даний запит після виконання видалить усі записи про студентів, які старші за сімнадцять (табл. 15).
Таблиця 15
| Num | Surname | Name | Age | Speciality |
| 5 | Ятрів | Олег | 17 | ПМ |
| 6 | Куцик | Олександр | 16 | Математика |
Дата добавления: 2022-01-22; просмотров: 24; Мы поможем в написании вашей работы! |
Мы поможем в написании ваших работ!