ОП.08 - 08 - Оператор HAVING. Добавление данных, оператор INSERT. Обновление данных, оператор UPDATE. Удаление данных, оператор DELETE

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

Оператор HAVING

Оператор HAVING используется в SQL для фильтрации результатов запроса, которые были сгруппированы с помощью оператора GROUP BY. Он позволяет указать условие, которое должно выполняться для группы, чтобы она была включена в результат.

Например, давайте посмотрим на пример использования оператора HAVING на учебной БД для студентов:

SELECT job_title, AVG(salary) as avg_salary
FROM employees
GROUP BY job_title
HAVING avg_salary > 35000;

В этом запросе мы группируем сотрудников по должностям и вычисляем среднюю зарплату для каждой группы. Затем мы используем оператор HAVING, чтобы выбрать только те группы, у которых средняя зарплата выше 35 000 рублей.

Результатом будет таблица, содержащая только должности со средней зарплатой выше 35 000 рублей:

Или другой пример — допустим, у нас есть таблица "заказы" (orders), которая содержит информацию о заказах в интернет-магазине. Каждый заказ имеет уникальный идентификатор (id), а также указывается, какой товар был куплен (product_name) и в каком количестве (quantity).

Мы хотим вывести список товаров, которые были заказаны более 25 раз:

SELECT product_name, SUM(quantity) as total_quantity
FROM orders
GROUP BY product_name
HAVING total_quantity > 25;

В этом запросе мы сначала группируем заказы по названию товара и вычисляем общее количество единиц каждого товара, проданных в рамках всех заказов (с помощью агрегирующей функции SUM). Затем мы используем оператор HAVING, чтобы выбрать только те товары, которые были заказаны более 25 раз (т.е. у которых общее количество единиц больше 25).

Результатом будет таблица, содержащая только те товары, которые были заказаны более 25 раз:

Таким образом, оператор HAVING позволяет фильтровать результаты запроса на основе агрегированных данных (например, средних значений, сумм и т.д.), что делает его очень полезным инструментом при анализе больших объемов данных.

Добавление данных, оператор INSERT

Для добавления новых записей в таблицу предназначен оператор INSERT.
Рассмотрим его общую структуру.

INSERT INTO имя_таблицы [(поле_таблицы, ...)]
-- значения прописанные вручную
VALUES (значение_поля_таблицы, ...)
-- или значения полученные запросом SELECT
SELECT поле_таблицы, ... FROM имя_таблицы ...

Значения можно вставлять перечислением с помощью слова VALUES, перечислив их в круглых скобках через запятую или c помощью оператора SELECT.

Допустим, на работу приняли нового сотрудника Иванова Ивана (род. 1.06.2003) на должность «программист-стажер» с окладом 18500 руб. в месяц, но корпоративный email ему пока не создавали. Запрос для выполнения данной задачи будет выглядеть следующим образом:

INSERT INTO employees (first_name, last_name, birthday, salary, job_title, gender)
VALUES ('Иван', 'Иванов', '2003-06-01', '18500', 'программист-стажер', 'М');

Или допустим, в таблицу продаж добавили сразу несколько продаж — продали 4 пары носков, 10 единиц футболок и 5 единиц шорт. Запрос для выполнения данной задачи будет выглядеть следующим образом:

INSERT INTO orders (product_name, quantity)
VALUES
  ('носки', '4'),
  ('футболка', '10'),
  ('шорты', '5');

[!TIP]
Следует помнить, что первичный ключ таблицы является уникальным значением и добавление уже существующего значения приведёт к ошибке, но в MySQL для решения подобной задачи в автоматическом режиме принято использовать уникальное свойство AUTO_INCREMENT

Добавление записей через INSERT INTO ... SELECT ... мы рассмотрим в рамках данного курса позднее, когда научимся работать с несколькими таблицами.

Обновление данных, оператор UPDATE

Для редактирования существующих записей в таблицах существует SQL оператор UPDATE.
Рассмотрим его общую структуру.

UPDATE имя_таблицы
SET поле_таблицы1 = значение_поля_таблицы1,
    поле_таблицыN = значение_поля_таблицыN
[WHERE условие_выборки]

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

UPDATE employees
SET last_name = 'Синдеева' WHERE id = 5;

[!INFO]
В различных СУБД существуют некоторые ограничения на изменения полей, в большинстве из них связка UPDATE ... SET не будет работать без WHERE. В MySQL по-умолчанию включен т.н. «безопасный режим» или safe_mode, который не позволит выполнить запрос обновления без WHERE, при этом будет требоваться чтобы одним из условий WHERE было ключевое поле. Тем не менее, подобные ограничения отключаемы, но они защищают от случайно обновления лишних данных при неверно написанном запросе.

Вычисляемые значения

В запросах на обновление данных можно менять значения, опираясь на предыдущие значение.

Например если мы хотим поднять оклад ведущему программисту на 10%, это можно сделать следующим запросом:

UPDATE employees SET salary = salary * 1.1 WHERE id = 7;

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

Удаление данных, оператор DELETE

Время от времени возникает задача удаления записей из таблицы. Для этого в SQL предусмотрены операторы DELETE и TRUNCATE, из которых наиболее универсальным и безопасным является первый вариант.

Рассмотрим его общую структуру.

DELETE FROM имя_таблицы
[WHERE условие_отбора_записей];

Если условие отбора записей WHERE отсутствует, то будут удалены все записи указанной таблицы.

[!INFO]
С включенным safe_mode СУБД MySQL такую операцию выполнить не позволит, сообщив о том что операция небезопасна, и выкинет соответствующую ошибку.

Эту же операцию (удаления всех записей) можно сделать также с помощью оператора TRUNCATE. Он выполнит удаление таблицы и пересоздаст её заново - этот вариант работает гораздо быстрее, чем удаление всех записей одна за другой (как в случае с DELETE) особенно для больших таблиц.

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

TRUNCATE TABLE имя_таблицы;

[!INFO]
Оптимизатор запросов СУБД MySQL автоматически использует оператор TRUNCATE, если оператор DELETE не содержит условия WHERE или конструкции LIMIT при отключенном safe_mode.

Для удаления отдельных записей используется запрос DELETE с указанием первичных ключей соответствующих записей. Например, если мы хотим удалить двоих сотрудников с первичными ключами 1 и 4, запрос будет выглядеть следующим образом:

DELETE FROM employees WHERE id IN (1, 4);

А если мы хотим очистить все заказы, то используем следующий запрос:

TRUNCATE TABLE orders;

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

Задание 1

Получите данные о товарах, которые куплены менее 30 раз.

SELECT product_name, SUM(quantity) as total_quantity
FROM orders
GROUP BY product_name
HAVING total_quantity < 30;

Задание 2

Получите данные о должностях, у которых средняя зарплата сотрудников превышает 35000 рублей.

SELECT job_title, AVG(salary) as avg_salary
FROM employees
GROUP BY job_title
HAVING avg_salary > 35000;

Задание 3

Определите должности, где средняя зарплата мужчин превышает 40000 рублей.

SELECT job_title, AVG(salary) as avg_salary
FROM employees
WHERE gender = 'М'
GROUP BY job_title
HAVING avg_salary > 40000;

Задание 4

Вывести фамилию, должность и зарплату сотрудников, у которых она выше 35000.

SELECT last_name, job_title, salary
FROM employees
WHERE salary > 35000;

Задание 5

Добавить в таблицу orders товары, поступившие на склад: поло - 10 шт, шарф - 15 шт, ботинки - 30 шт.

INSERT INTO orders (product_name, quantity)
VALUES
  ('поло', '10'),
  ('шарф', '15'),
  ('ботинки', '30');

Задание 6

Добавить 2 сотрудников в employees:

1. Петр Иванов, 22.01.2000 г. рождения, на должность старший программист, с зарплатой 70000.
2. Матвей Щукин, 27.06.1995 г. рождения, на должность программист-стажер, с зарплатой 35000.

INSERT INTO employees (first_name, last_name, birthday, salary, job_title, gender)
VALUES
  ('Петр', 'Иванов', '2000-01-22', '70000', 'старший программист', 'М'),
  ('Матвей', 'Щукин', '1995-06-27', '35000', 'программист-стажер', 'М');

Задание 7

Обновить информацию о зарплате сотрудника с id = 3, установив зарплату 45000.

UPDATE employees SET salary = 45000 WHERE id = 3;

Задание 8

Обновить информацию о должности сотрудника с id = 3 на "главный программист".

UPDATE employees
SET job_title = 'главный программист'
WHERE id = 3;

Задание 9

Поднять зарплату на 7% всем сотрудникам, у которых в должности указано слово "программист" (включая должности "программист", "старший программист", "программист-стажер" и т.д.), при этом зарплаты сотрудников с другими должностями не должны измениться.

UPDATE employees
SET salary = salary * 1.07
WHERE
  job_title LIKE '%программист%'
    AND
  id > 0
;

Задание 10

Удалить всех сотрудников с фамилией 'Антонова'.

DELETE FROM employees
WHERE
  last_name = 'Антонова'
    AND
  id > 0
;

Задание 11

Удалить сотрудника с id = 9.

DELETE FROM employees
WHERE id = 9;

Задание 12

Удалить информацию обо всех заказах начиная с id = 10 включительно.

DELETE FROM orders
WHERE id >= 10;