Лекція 11. Пошук та вибірка даних в мові SQL

1. Створення обчислювальних стовпців.

1.1. Конкатенація стовпців.

SELECT Name + ' ' + CAST(ID AS NCHAR) AS Name_ID

FROM Model

Функція CAST() виконує явне приведення типів

CAST(<вираз> AS <тип> [(<довжина>)])

Оператор + виконує як математичне сумування, так і конкатенацію  стрічок.  У  деяких  СКБД  використовується  для  конкатенації оператор  ||. MySQL  не  підтримує  ні  +,  ні  ||;  для  цього  у  ньому передбачена функція CONCAT().

1.2. Математичні обчислення.

SELECT Name, ROUND(SQRT(Price), 0) AS Formula

FROM Model

Для обчислень використовуються математичні  операції  +,  –,  *,  /,  круглі  дужки () для вказання  порядку  обчислення  та  математичні функції.

2. Отримання статистичних даних.

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

COUNT (*)	повертає кількість наявних стрічок для вибраних даних;
COUNT (ім’я стовпця)	повертає кількість стрічок, що мають значення  у  вказаному  стовпці,  при  цьому NULL-значення не враховуються;
SUM (ім’я стовпця)		повертає суму значень у вказаному стовпці;
AVG (ім’я стовпця)		повертає  середнє  значення  для  вказаного стовпця;
MIN (ім’я стовпця)		повертає мінім. значення у вказаному стовпці;
MAX (ім’я стовпця)		повертає макс. значення у вказаному стовпці.

Функції COUNT(), MIN(), MAX() працюють із довільними типами даних, а SUM() та AVG () лише із числовими стовпцями.

SELECT COUNT(*) count1, COUNT(ID) count2, SUM(Price) suma,

AVG(Price) middle, MIN(Price) min, MAX(Price) max

FROM Model

SELECT COUNT(*) count FROM Model

WHERE Year = 2005

3. Отримання групової статистики.

3.1. Створення груп.

SELECT Name, COUNT(*) Gcount, SUM(Price) Suma,

FROM Model

GROUP BY Name

Вираз GROUP BY вказує СКБД відсортувати та згрупувати дані за вказаним стовпцем. При цьому статистичні функції виконують підрахунок для кожної групи окремо, а не по всьому набору даних. У переліку стовпців оператора SELECT можуть вказуватися лише статистичні функції та стовпці, що є присутні у переліку виразу GROUP BY.

3.2. Фільтрація груп.

SELECT Name FROM Model

GROUP BY Name

HAVING MAX(Price) > 100 AND Name > 'B'

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

3.3. Послідовність  відбору  даних  при  отриманні  групової статистики.

SELECT Name, COUNT(*) Num FROM Model

WHERE Year >= 2014

GROUP BY Name

HAVING MAX(Price) < 70

ORDER BY Num DESC

a) Для вибраної таблиці (Model) виконуємо фільтрації її стрічок на основі умови конструкції WHERE.

б)  Для  профільтрованої  таблиці  сотруємо  та  групуємо  дані навколо вказаного стовпця у конструкції GROUP BY.

в) Виконуємо фільтрацію утворених груп на основі виконання умови у конструкції HAVING.

г)  Остаточні  дані  сортуємо  за  стовпцями,  що  вказані  у конструкції ORDER BY, та виводимо назовні.

4. Об'єднання таблиць.

4.1. Декартовий добуток двох таблиць.

SELECT * FROM Dog, Mouse

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

4.2. Внутрішній добуток таблиць.

SELECT A, D.B, M.B, C FROM Dog D, Mouse M

WHERE D.B = M.B

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

<ім’я таблиці>.<ім’я стовпця>

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

4.3. Об’єднання таблиці самої із собою.

SELECT C1.Model Model1, C2.Model Model2, C1.Price

FROM Car C1, Car C2

WHERE C1.Price = C2.Price AND C1.Model > C2.Model

Тут для оригінала та його дубліката виконується внутрішній добуток за полем Price. Умова C1.Model > C2.Model забезпечує відсутність повтору одинакових пар моделей. Для об’єднання таблиці самої зі собою необхідно її двічі включити у конструкцію FROM та надати їй при цьому різні псевдоніми, щоб можна було ці копії розрізнити.

5. Конструкція JOIN.

Ця конструкція виконує явну операцію з’єднання для двох та більше таблиць. Синтаксис конструкції JOIN має такий вигляд:

FROM <таблиця 1> {[INNER] | {LEFT | RIGHT | FULL} [OUTER] |

CROSS} JOIN <таблиця 2> [ON <умова з’єднання>]

[INNER] JOIN – внутрішнє з’єднання;

LEFT [OUTER] JOIN – ліве зовнішнє з’єднання;

RIGHT [OUTER] JOIN – праве зовнішнє з’єднання;

FULL [OUTER] JOIN – повне зовнішнє з’єднання;

CROSS JOIN – перехресне з’єднання;

Службові слова INNER та OUTER можуть бути опущені, оскільки зовнішнє з’єднання однозначно визначається його типом LEFT (ліве), RIGHT (праве) чи FULL (повне), а просто JOIN означає внутрішнє з’єднання.

5.1. Внутрішнє з’єднання [INNER] JOIN.

SELECT * FROM Dog D JOIN Mouse M ON D.B = M.B

Ключове слово INNER може бути опущене. Виконання з’єднання згідно конструкції [INNER] JOIN є повністю еквівалентне виконанню внітрішнього добутку таблиць, тобто

SELECT * FROM Dog D, Mouse M

WHERE D.B = M.B

5.2. Ліве зовнішнє з’єднання LEFT [OUTER] JOIN.

SELECT *

FROM Dog D LEFT JOIN Mouse M ON D.B = M.B

Ключове слово OUTER може бути опущене. При лівому зовнішньому з’єднанні у підсумкову таблицю мають бути включені усі стрічки з лівої (розташованої перед оператором JOIN) таблиці, а з правої таблиці лише ті стрічки, що мають відповідність у лівій. При відсутності для стрічок лівої таблиці відповідних стрічок з правої, у правій частині з’єднання проставляються null-значення.

5.3. Праве зовнішнє з’єднання RIGHT [OUTER] JOIN.

SELECT *

FROM Dog D RIGHT JOIN Mouse M ON D.B = M.B

Праве зовнішнє з’єднання є схоже до лівого. Тобто усі стрічки з правої (розташованої після оператора JOIN) таблиці мають бути включеними у з’єднання, а з лівої лише ті, що мають відповідність у правій таблиці. Вільні місця зліва заповнюються null-значеннями.

5.4. Повне зовнішнє з’єднання FULL [OUTER] JOIN.

SELECT *

FROM Dog D FULL JOIN Mouse M ON D.B = M.B

Повні зовнішні з’єднання дають результат, як при одночасному поєднанню лівого та правого з’єднань, тобто, у підсумкову таблицю включаються усі стрічки з обох таблиць. Стрічки, що мають відповідність одна одній, виводяться паралельно, решта вільного місця заповнюються null-значеннями.

5.5. Перехресне з’єднання CROSS JOIN.

SELECT *

FROM Dog CROSS JOIN Mouse

У даній конструкції відсутній оператор ON з підлеглою йому умовою. Це означає, що дві таблиці з’єднуються між собою за правилом: кожна стрічка з кожною стрічною, а це повністю відповідає декартовому добутку таблиць.

        SELECT * FROM Dog, Mouse

        Результати в обох випадках однакові.

6. Операція UNION.

Використовується при об’єднанні результатів двох запитів в один набір даних.

SELECT … FROM …

UNION [ ALL ]

SELECT … FROM …

ORDER BY <перелік стовпців>

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

SELECT A, B FROM Dog

UNION

SELECT C, B FROM Mouse

Зауваження:

1. Кількість вихідних стовпців для обох таблиць має бути одинаковою, при цьому тип 1-го стовпця першої таблиці повинен співпадати з типом 1-го стовпця другої таблиці; тип 2-го стовпця першої таблиці повинен співпадати з типом 2-го стовпця другої таблиці, і т.д.

2. Назви стовпців підсумкової таблиці співпадають з відповідними назвами першого запиту SELECT.

3. Конструкція ORDER BY може бути лише одна та розміщується у кінці об’єднання. Для сортування вказуються імена стовпців з першого запиту або їхні порядкові номери.

SELECT A, B FROM Dog

UNION

SELECT C, B FROM Mouse

ORDER BY 2

4. Якщо вказано необов’язковий параметр ALL, тоді виводяться усі стрічки разом з дублікатами, а за замовчуванням, без параметра ALL, дублікати фільтруються.

7. Отримання даних за допомогою підзапитів.

7.1. Підзапити у конструкції FROM.

SELECT A, B, BB, CC

FROM Dog, (SELECT B AS BB, C AS CC FROM Mouse) MM

WHERE B = BB

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

7.2. Підзапити у переліку стовпців SELECT.

SELECT A, B

    (SELECT COUNT(*) FROM Mouse

    WHERE B = Dog.B) AS Count,

    (SELECT MAX(C) FROM Mouse

    GROUP BY B

    HAVING B = Dog.B) AS Max

FROM Dog

У цьому випадку підзапити мають повертати лише одне скалярне значення. І тому часто їх використовують для отримання статистичних даних з підлеглої таблиці.

7.3. Підзапити у конструкції WHERE.

а) Логічна операція EXISTS.

SELECT A, B

FROM Dog

WHERE EXISTS (SELECT * FROM Mouse WHERE B = Dog.B)

Якщо підзапит повертає одну чи більше стрічок з даними, тоді операція EXISTS дає логічне значення true, а якщо не повертає жодної – тоді значення false.

а) Логічна операція IN.

SELECT A, B

FROM Dog

WHERE B IN (SELECT B FROM Mouse)

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

б) Ключові слова SOME, ANY, ALL.

<вираз> {= | > | < | >= | <= | <> | != | !> | !<} SOME | ANY | ALL (<підзапит>)

стандартна операція порівняння

Тобто, логічний вираз складається з операції порівняння та одного з ключових слів, наприклад:

> ANY; <> ALL; = SOME

1) Ключові слова SOME та ANY є синонімами, і тому немає значення, який з них використовувати. Словосполучення з певної операції порівняння та слова SOME чи ANY поводить себе таким чином: порівнюється значення зліва з кожним окремим значенням зі списку значень, що повертається підзапитом. Якщо хоча б одне таке порівняння справджується, тоді загальний результат буде true, якщо жодне не справджується – false. Якщо підзапит не поверне жодного значення, тобто буде пустий список, тоді загальний результат – false.

SELECT A, B

FROM Dog

WHERE B > ANY (SELECT B FROM Mouse)

2) Словосполучення з певної операції порівняння та слова ALL поводить себе таким чином: порівнюється значення зліва з кожним окремим значенням зі списку значень, що повертається підзапитом. Якщо справджуються усі порівняння – тоді результат true, а якщо хоча б одне не виконується – false. Якщо підзапит поверне пустий список, тоді загальний результат – true.

SELECT A, B

FROM Dog

WHERE B <> ALL (SELECT B FROM Mouse)

Зауваження: оператор =ALL є еквівалентний оператору IN, а оператор <>ALL еквівалентний оператору NOT IN. Відмінність між IN та ANY, SOME, ALL така, що останні працюють лише зі списками значень, отриманих з підзапитів.

8. Оператор CASE.

Перевіряє перелік умов та повертає одне з можливих скалярних значень.

Оператор CASE має два формати:

а) Простий формат.

CASE <вхідне значення>

WHEN <порівнюване значення 1> THEN <вихідне значення 1>

…

WHEN <порівнюване значення N> THEN <вихідне значення N>

[ELSE <вихідне значення>]

END

      б) Пошуковий формат.

CASE

WHEN <умова 1> THEN <вихідне значення 1>

…

WHEN <умова N> THEN <вихідне значення N>

[ELSE <вихідне значення>]

END

Пояснення:

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

2. Повертається вихідне значення для найпершого виразу WHEN, умова якого справджується.

3. Якщо жодна умова не виконується, тоді повертається вихідне значення з конструкції ELSE або NULL, якщо значення для ELSE не вказано.

SELECT Surname,

                CASE Sex

                        WHEN 'M' THEN 'хлопець'

                        WHEN 'W' THEN 'дівчина'

                        ELSE 'не вказано'

                END AS Sex

FROM Student

SELECT Surname,

CASE

        WHEN Result < 50 THEN 'незадовільно'

        WHEN Result < 71 THEN 'задовільно'

        WHEN Result < 88 THEN 'добре'

        ELSE 'відмінно'

        END AS Result

FROM Student