ОП.08 - 07 - Оператор LIKE. Сортировка ORDER BY. Группировка GROUP BY и агрегатные функции

Примеры данной темы используют учебную БД:

Оператор LIKE

Оператор LIKE используется при условных запросах, когда мы хотим узнать соответствует ли строка определённому шаблону.

Например, у нас есть таблица employees, в которой у каждого сотрудника есть поле email. Допустим, мы хотим найти всех пользователей, чьи почтовые ящики находятся на домене компании company. Т.е. нужно отобрать только те записи, что отвечают условию:

• после символа @ следует company
• после company следует символ «.» и далее любая последовательность символов (т.к. есть и другие доменные зоны кроме ru)

Для таких нетривиальных поисков по строковым полям и нужен оператор LIKE.

Пример синтаксиса:

... WHERE поле_таблицы [NOT] LIKE шаблон_строки;

Шаблон может включать следующие специальные символы:

Так наш запрос может выглядеть следующим образом:

SELECT first_name, last_name, email FROM employees
WHERE email LIKE '%@company.%';

[!TIP]
В MySQL по-умолчанию шаблоны не чувствительны к регистру

ESCAPE-символ

ESCAPE-символ используется для экранирования специальных символов (%, _, /). В случае если вам нужно найти строки, содержащие их, вы можете использовать ESCAPE-символ.

Например, вы хотите получить информацию по бонусам к окладу в процентах:

SELECT first_name, last_name, bonus FROM employees
WHERE bonus LIKE '%!%%' ESCAPE '!';

Если бы мы не экранировали трафаретный символ, то в выборку попало бы всё.

Сортировка ORDER BY

При выполнении запроса SELECT, строки по умолчанию возвращаются в неопределенном порядке. Фактический порядок строк в этом случае зависит от того порядка в котором данные попадали в базу. Для упорядочивания записей используется конструкция ORDER BY.

Общая структура запроса с оператором ORDER BY:

SELECT поля_таблиц FROM наименование_таблицы
WHERE ...
ORDER BY столбец_1 [ASC | DESC][, столбец_n [ASC | DESC]];

Где ASC и DESC - направление сортировки:

• ASC - сортировка по возрастанию (по умолчанию)
• DESC - сортировка по убыванию

Например, выведем всех сотрудников, отсортировав их по зарплате от наибольшей к наименьшей:

SELECT first_name, last_name, job_title, salary FROM employees
ORDER BY salary DESC;

Сортировка по нескольким столбцам

Для сортировки результатов по двум или более столбцам их следует указывать через запятую.

...ORDER BY столбец_1 [ASC | DESC], столбец_2 [ASC | DESC];

Данные будут сортироваться по первому столбцу, но в случае если попадаются несколько записей с совпадающими значениями в первом столбце, то они сортируются по второму столбцу. Количество столбцов, по которым можно отсортировать не ограничено.

При этом стоит учесть что правило сортировки применяется только к тому столбцу, за которым оно следует.

ORDER BY столбец_1, столбец_2 DESC

не то же самое, что

ORDER BY столбец_1 DESC, столбец_2 DESC

Например, выведем всех сотрудников, отсортировав по алфавиту их сначала по фамилии а потом по имени:

SELECT last_name, first_name FROM employees
ORDER BY last_name ASC, first_name ASC;

Группировка GROUP BY и агрегатные функции

Что если мы хотим получить информацию не о каждой записи отдельно, а о группах, которые они образуют? Такими группами могут выступать любые данные, одинаковые для нескольких записей. Эти группы включают разные записи в таблице и, соответственно, обладают разными характеристиками, которые нам могут быть весьма полезны.

Общая структура запроса с GROUP BY:

SELECT [поля_группировки|агрегатные_функции]
FROM имя_таблицы
GROUP BY поля_группировки;

Для того, чтобы записи у нас образовали группы GROUP BY поле, по которому будет происходить группировка, например по полу:

SELECT gender
FROM employees
GROUP BY gender;

На самом деле, такой же результат мы могли получить выполнив и простой запрос SELECT по полю gender с модификатором DISTINCT. Но при попытке добавить к запросу с группировкой какие-либо поля, скорее всего возникнет ошибка вида:

Error Code: 1055. Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'db.employees.gender' which is not functionally dependent on columns in GROUP BY clause

Дело в том что при использовании оператора GROUP BY мы перешли от работы с отдельными записями на работу с образовавшимися группами. В связи с этим мы не можем просто вывести любое поле из записи (например, job_title), как мы это могли делать раньше. Так как в каждой группе может быть несколько записей и в каждой из них в этом поле может быть разное значение.

Поэтому, для того чтобы работать с группами, необходимо использовать особые функции.

Агрегатные функции

Агрегатная функция – это функция, которая выполняет вычисление на наборе значений и возвращает одиночное значение.

В разных СУБД существует довольно много разных агрегатных функций, но мы рассмотрим основные:

Например, запрос с использованием агрегатной функции AVG для вычисления средней зарплаты для каждой из профессий может выглядеть так:

SELECT job_title, AVG(salary)
FROM employees
GROUP BY job_title;

Таким же образом можно найти максимальную зарплату по каждому полу

SELECT gender, MAX(salary)
FROM employees
GROUP BY gender;

Или же посчитать количество мужчин и женщин:

SELECT gender, COUNT(gender)
FROM employees
GROUP BY gender;

[!TIP]
Агрегатные функции применяются для значений, не равных NULL. Исключением является функция COUNT(*).

Кроме того, агрегатные функции могут использоваться и без группировки, например если мы хотим посчитать общее количество сотрудников компании:

SELECT COUNT(*)
FROM employees;

или сумму всех зарплат за месяц (Фонд Оплаты Труда, ФОТ):

SELECT SUM(salary)
FROM employees;

Задания для самопроверки

Задание 1

Напишите запрос, который выберет все строки из таблицы employees, где фамилия пользователя начинается на букву "А".

SELECT * FROM employees WHERE last_name LIKE 'А%';

Задание 2

Напишите запрос, который выберет все строки из таблицы employees, отсортированные по дате рождения в порядке убывания.

SELECT * FROM employees ORDER BY birthday DESC;

Задание 3

Напишите запрос, который выберет среднюю зарплату сотрудника из таблицы employees.

SELECT AVG(salary) FROM employees;

Задание 4

Напишите запрос, который выберет все строки из таблицы employees, где должность содержит слово "программист".

SELECT * FROM employees WHERE job_title LIKE '%программист%';

Задание 5

Напишите запрос, который выберет количество сотрудников каждого пола из таблицы employees.

SELECT gender, COUNT(*) FROM employees GROUP BY gender;

Задание 6

Напишите запрос, который выберет максимальную зарплату сотрудника из таблицы employees.

SELECT MAX(salary) FROM employees;

Задание 7

Напишите запрос, который выведет имя и фамилию сотрудника из таблицы employees с адресом электронной почты "v.ivanova@company.ru".

SELECT first_name, last_name FROM employees WHERE email = 'v.ivanova@company.ru';

Задание 8

Напишите запрос, который выберет суммарную заплату всех сотрудников должности "программист" (не считая ведущего программиста!) из таблицы employees.

SELECT SUM(salary) FROM employees WHERE job_title = 'программист';

Задание 9

Напишите запрос, который выберет все должности сотрудников, состоящие более чем из одного слова (должность через дефис также считается таковой) из таблицы employees.

SELECT job_title FROM employees WHERE job_title LIKE '% %' OR job_title LIKE '%-%';