Короткі теоретичні відомості

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

SELECT d.Назва, d.Фонд, (d.Фонд / f.Фонд) * 100

FROM  ФАКУЛЬТЕТ f, КАФЕДРА d

WHERE f.F = d.F AND F.Назва = "інформатики"

Використання агрегатних (вбудованих) функцій. Агрегатними називаються функції, які обчислюють різні статистичні характеристики. Вони наведені в табл. 4.1.

Таблиця 4.1. Агрегатні функції

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

Приклад запиту: Знайти кількість рядків таблиці Дисципліна

SELECT  COUNT(*)

FROM : Дисципліна

Приклад запиту: Знайти кількість рядків таблиці Дисципліна, код дисципліна яких менший 5

SELECT COUNT(*)

FROM ^: Дисципліна

WHERE Код_дисципліни<5

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

AVG(SUM(…)) не можна

AVG(а+в) можна

SUM( фонд)/COUNT(*) можна

Запит. Визначити кількість студентів груп ІТ-220 і ІТ-210

SELECT Sum(Група.ЧислоСтудентів) AS skmrscnm

FROM Група

where НазваГрупи="ТО-220" OR НазваГрупи="ТО-21"

Запит. Визначити середнє арифметичне кількості студентів груп ІТ-220 і ІТ-210

SELECT AVG (Група.ЧислоСтудентів) AS skmrscnm

FROM Група

where НазваГрупи="ТО-220" OR НазваГрупи="ТО-21"

Використання GROUP BY.Наступним з розглянутих нами виразів є GROUP BY. Він дозволяє розподілити рядки, що вибираються, по групах і є корисним при його використанні в комбінації з функціями, застосовуваними до груп рядків. Це є функція count().

Розглянемо наступний запит:

SELECT COUNT(*), JOB

FROM EMPLOYEE

GROUP BY JOB;

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

+-------------+--------------------------------------+

| count(*) | job                                |

+-------------+--------------------------------------+

|    1 | Адміністратор БД          |

|    2 | Програміст                          |

|    1 | Системний адміністратор |

+------------+---------------------------------------+

3 rows in set (0.04 sec)

За замовчуванням сортування проводиться по зростанню. Щоб в останньому запиті результати були представлені в спадаючому порядку, запит використовується службове слово DESC.

SELECT COUNT(*), JOB

FROM EMPLOYEE

GROUP BY JOB DESC;

Результат:

+------------+-------------------------------------+     

 |count (*) | job                                     |

+-------------+------------------------------------+

 |1   | Системний адміністратор |

 |2   | Програміст                       |

 |1   | Адміністратор БД            |

+------------+-------------------------------------+     

3 rows in set (0.04 sec)

Назви посад приводяться в порядку, зворотному алфавітному.

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

SELECT COUNT(*)

FROM EMPLOYEE;

Цей запит поверне число рядків таблиці employee. Зазвичай, такі функції застосовуються до груп. Наприклад:

SELECT JOB, COUNT(JOB)

FROM EMPLOYEE

GROUP BY JOB;

Цей запит повідомить, скільки службовців містить кожна із груп.

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

Вибір груп за допомогою HAVING

Наступним виразом в операторі SELECT є вираз HAVING. GROUP BY з виразом HAVING подібний до SELECT з виразом WHERE. Наприклад:

SELECT COUNT(*), JOB

FROM EMPLOYEE

GROUP BY JOB

HAVING COUNT(*) = L;

Такий запит вибере всі наявні в компанії посади, на яких працює по одному службовцю. Результат виконання запиту буде наступним:

+--------------+------------------------------------+

|   count(*) | job                                  |

+--------------+------------------------------------+

|    1  | Адміністратор БД        |

|    1  | Системний адміністратор |

+--------------+------------------------------------+

2 rows in set (0.05 sec)

Сортування результатів пошуку за допомогою ORDER BY.Наступним виразом в операторі SELECT є вираз ORDER BY. Він дає можливість відсортувати рядки результату по одному або декількох стовпцях. Сортувати при цьому можна як по зростанню (позначається ASC), так і по спаданню (позначається DESC). Наприклад:

SELECT *

FROM EMPLOYEE

ORDER BY JOB ASC, NAME DESC;

У результаті будуть обрані усі рядки з таблиці employee і відсортовані за значенням job за абеткою. Якщо при цьому два або більше службовців будуть мати однакові посади, то відповідні рядки будуть відсортовані за іменем у зворотному порядку.

Дані, що виймаються до цього, завжди виводилися в тому порядку, у якому вони були збережені в таблиці. У дійсності SQL дозволяє сортувати витягнуті дані за допомогою виразу ORDER BY. Ця пропозиція вимагає ім'я стовпця, на основі якого будуть сортуватися дані. Давайте подивимося, як можна вивести імена співробітників з упорядкованими за алфавітом прізвищ співробітників (у зростаючому порядку).

SELECT l_name, f_name

from employee_data

ORDER BY l_name;

А от так співробітників можна відсортувати за віком.

SELECT f_name, l_name, age

from employee_data

ORDER BY age;

Вираз ORDER BY може сортувати в зростаючому порядку (ASCENDING або ASC) або в спадному порядку (DESCENDING або DESC) залежно від зазначеного аргументу.

Щоб вивести список співробітників в спадному порядку, можна використовувати такий оператор.

SELECT f_name

from employee_data

ORDER by f_name DESC;

Приклади завдань

1. Вивести список співробітників у порядку, обумовленому зарплатою, що вони одержують.

2. Виведіть список співробітників в спадному порядку їхнього стажу роботи в компанії.

3. Що робить наступний оператор?

SELECT emp_id, l_name, title, age

from employee_data

ORDER BY title DESC, age ASC;

4. Вивести список співробітників (прізвище та ім'я), які обіймають посаду "програміст" або "розроблювач Web" і відсортувати їхні прізвища за алфавітом.

Можливі вирішення завдань

mysql> SELECT f_name, l_name, salary

-> from employee_data

-> ORDER BY salary;

mysql> SELECT f_name, l_name, yos

-> from employee_data

-> ORDER by yos;

Оператор виводить список співробітників з ідентифікаційним номером, посадою, і віком, відсортований за посадою в спадному порядку й за віком у зростаючому порядку.

mysql> SELECT emp_id, l_name, title, age

-> from employee_data ORDER BY

-> title DESC, age ASC;

Примітка: Спочатку сортуються посади в спадному порядку. Потім для кожної посади сортується вік співробітників у зростаючому порядку.

mysql> SELECT l_name, f_name from employee_data

-> where title IN ('програміст',

-> 'розроблювач Web') ORDER BY l_name;

Питання вхідного контролю

1 Які ви знаєте засоби групування результатів?

2 Які ви знаєте засоби сортування результатів?

3 Які ви знаєте функції групування (агрегуючі функції)?

4 В чому полягає необхідність сортування результатів?

5 Яке призначення має вираз HAVING у SELECT запиті?

6 Які функції називаються агрегатними? Перелічіть їх?

7 Яке призначення функції AVG?

8 В якій фразі формуємо стовпець, що обчислюється?

9 Як знаходиться кількість записів в таблиці?

Постановка завдання

Створити базу даних Успішність з чотирьох таблиць: Група, Студенти, Дисципліни, Оцінки

Створити таблицю Група з вказаними властивостями полів.

Створити таблицю Студенти з вказаними властивостями полів.

Створити таблицю Дисципліна з вказаними властивостями полів.

Створити таблицю Оцінка з вказаними властивостями полів.

Заповнити по 4-5 записів кожної таблиці, переглянути заповнені таблиці