ПИСиБДРВ практика 3.1

1. Группировка. Подзапросы. Соединения

МИРЭА - Российский технологический университет
mirea.ru
Группировка.
Подзапросы. Соединения

2. Оператор LIMIT

МИРЭА - Российский технологический университет
mirea.ru
Оператор LIMIT
Оператор LIMIT используется для
ограничения количества строк,
возвращаемых запросом. Это полезно в
ситуациях, когда требуется получить
только определённое количество
записей.
Если необходимо, чтобы выборка
начиналась не с начала, а с
определенным пропуском строк, то
нужно добавить оператор OFFSET.
2

3. Оператор LIMIT

МИРЭА - Российский технологический университет
mirea.ru
Оператор LIMIT
3

4. Оператор AS

МИРЭА - Российский технологический университет
mirea.ru
Оператор AS
В тех случаях, когда запрос становится достаточно
сложным и включает в себя длинные имена столбцов и
таблиц, а также при использовании агрегатных функций
или выражений, результатом которых являются новые
столбцы, можно назначить псевдонимы (алиасы) для
столбцов и таблиц. Это позволяет упростить запрос,
сделав его более читаемым и удобным для
интерпретации. Для присвоения псевдонимов
используется ключевое слово AS. Назначение
псевдонимов особенно полезно в ситуациях, когда
исходные имена столбцов и таблиц длинные или
неинформативные, а также в тех случаях, когда нужно
обозначить результат вычисления, который создаёт
новый столбец.
4

5. Оператор AS

МИРЭА - Российский технологический университет
mirea.ru
Оператор AS
5

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

МИРЭА - Российский технологический университет
mirea.ru
Агрегатные функции
Агрегатные функции вычисляют некоторые скалярные значения в
наборе строк. В MySQL есть следующие агрегатные функции:
AVG: вычисляет среднее значение;
SUM: вычисляет сумму значений;
MIN: вычисляет наименьшее значение;
MAX: вычисляет наибольшее значение;
COUNT: вычисляет количество строк в запросе.
Все агрегатные функции принимают в качестве параметра
выражение, которое представляет критерий для определения
значений. Выражения в функциях AVG и SUM должно представлять
числовое значение.
Выражение в функциях MIN, MAX и COUNT может представлять
числовое или строковое значение или дату.
Все агрегатные функции за исключением COUNT(*) игнорируют
значения NULL.
6

7.

МИРЭА - Российский технологический университет
mirea.ru
Агрегатные функции
7

8. Группировка

МИРЭА - Российский технологический университет
mirea.ru
Группировка
Группировка данных в SQL — операция, которая
позволяет агрегировать данные по определённым
признакам. Оператор GROUP BY используется для
группировки строк в наборе данных по одному или
нескольким столбцам, после чего к каждой группе
могут быть применены агрегатные функции. В
результате выполнения группирующего запроса для
каждой отдельной группы создается единственная
группирующая строка. При группировке строк со
значениями NULL они считаются как одна группа.
Например, все строки, где значение столбца равно
NULL, будут сгруппированы вместе. Синтаксис в СУБД
MySQL и PostgreSQL идентичен.
8

9. Группировка

МИРЭА - Российский технологический университет
mirea.ru
Группировка
Рассмотрим таблицу Author. Необходимо подсчитать сколько
авторов из каждой страны присутствует в сервисе книг. Другими
словами, необходимо сгруппировать авторов по стране и
подсчитать количество строк в каждой группе. В этом поможет
внешний ключ стран country_id, ссылающийся на таблицу Country и
агрегатная функция COUNT.
9

10. Группировка

МИРЭА - Российский технологический университет
mirea.ru
Группировка
Получившийся запрос выглядит следующим образом: выбирается
идентификатор страны и функция подсчета строк, сгруппированные
по идентификатору страны. Таким образом, можно увидеть, что из
страны под номером 2 (Великобритания) в сервисе имеются 4
книги.
10

11. Группировка

МИРЭА - Российский технологический университет
mirea.ru
Группировка
SQL также позволяет группировать данные по нескольким столбцам
одновременно. В этом случае группы будут создаваться на основе уникальных
комбинаций значений в указанных столбцах. Важно отметить — имена столбцов,
указанные в списке группирующего запроса SELECT, должны присутствовать и во
фразе GROUP BY. Единственное исключение делается для полей, обрабатываемых
агрегатной функцией.
Обратимся к таблице Book. Требуется сгруппировать книги по издательствам и
году публикации и подсчитать количество книг в каждой комбинации издательства
и года.
11

12. Группировка

МИРЭА - Российский технологический университет
mirea.ru
Группировка
Для выполнения поставленной задачи столбцы для
группировки указываются после оператора GROUP BY через
запятую.
12

13. Группировка

МИРЭА - Российский технологический университет
mirea.ru
Группировка
Предикат HAVING применяется после выполнения
операции группировки данных и предназначен для
дополнительной фильтрации уже сформированных
групп строк. Его основная функция заключается в
том, чтобы позволить пользователю задавать
условия отбора на основе агрегированных данных,
полученных в результате группировки. В этом
отношении поведение предиката HAVING схоже с
оператором WHERE, однако существует ключевое
различие: если WHERE применяется для фильтрации
отдельных строк до этапа группировки, то HAVING
действует на уже сгруппированные данные,
оперируя агрегатными значениями.
13

14. Группировка

МИРЭА - Российский технологический университет
mirea.ru
Группировка
Добавим одно условие в предыдущий пример. Теперь требуется
выводить сгруппированные комбинации книг и издательств,
количество строк которых больше 1. С помощью предиката HAVING
были исключены все группы, где количество строк в группе меньше
или равно 1.
14

15. Группировка

МИРЭА - Российский технологический университет
mirea.ru
Группировка
Результаты, полученные с использованием GROUP BY, не обязательно будут
отсортированы. Для упорядочивания результатов используется оператор ORDER
BY.
Запрос в примере группирует книги по идентификатору, вычисляет среднее
количество страниц и сортирует книги по убыванию среднего количества страниц.
15

16. Соединение таблиц

МИРЭА - Российский технологический университет
mirea.ru
Соединение таблиц
Соединение таблиц (JOIN) позволяет объединять строки из
двух или более таблиц на основе связанных столбцов. В
зависимости от типа соединения, результат может включать
только те строки, которые имеют совпадающие значения в
обоих таблицах, или также строки, которые не имеют
соответствий.
Простейшим способом соединения двух и более таблиц
является использование оператора WHERE с определением
столбцов, применяемых для соединения.
Когда для внутреннего соединения используется WHERE,
строки из двух таблиц объединяются только в том случае,
если значения в определённых столбцах обеих таблиц
совпадают. Этот метод был одним из первых способов
выполнения соединений до появления синтаксиса JOIN.
16

17. Соединение таблиц

МИРЭА - Российский технологический университет
mirea.ru
Соединение таблиц
Рассмотрим таблицы User и Subscribe. Таблица User имеет внешний ключ
subscribe_id, который ссылается на идентификатор сущности Subscribe.
Именно через эти атрибуты будет происходить соединение таблиц.
17

18. Соединение таблиц

МИРЭА - Российский технологический университет
mirea.ru
Соединение таблиц
В представленном запросе строки из таблиц User и Subscribe будут
объединены только в том случае, если значения в столбце country_id
совпадают со значениями в столбце id. В результате будут возвращены
имена и фамилии пользователей и названия типов подписок, которые
оформлены у пользователей.
18

19. Соединение таблиц

МИРЭА - Российский технологический университет
mirea.ru
Соединение таблиц
Представленный тип соединения называется внутренним
соединением (или INNER JOIN). Оно выполняется на основе
строгого соответствия значений, хранящихся в двух столбцах
— обычно это столбцы, представляющие внешний и
первичный ключи, то есть внешний ключ=первичный ключ.
Внутреннее соединение объединяет строки из двух таблиц
только в том случае, если значения в этих столбцах полностью
совпадают.
Ключевой особенностью внутреннего соединения является
то, что в результирующее отношение включаются
исключительно те строки из обеих таблиц, которые имеют
соответствующие друг другу строки — так называемых
«напарников». Если для строки из одной таблицы не найдено
соответствие в другой таблице, эта строка не будет включена
в результат запроса.
19

20. Соединение таблиц

МИРЭА - Российский технологический университет
mirea.ru
Соединение таблиц
INNER JOIN — это наиболее распространённый тип соединения, который
возвращает только те строки, для которых есть совпадающие значения в
обеих таблицах.
Обратимся к таблицам Category и Genre.
20

21. Соединение таблиц

МИРЭА - Российский технологический университет
mirea.ru
Соединение таблиц
Необходимо вывести все жанры и названия категорий, которые им
соответствуют. Данные сущности связаны через внешний ключ
category_id. Произведем внутреннее соединение, используя INNER JOIN.
21

22. Соединение таблиц

МИРЭА - Российский технологический университет
mirea.ru
Соединение таблиц
При написании SQL-запросов порядок соединения таблиц
играет ключевую роль, что обуславливает введение понятий
«левая» и «правая» таблицы. Эти термины используются для
обозначения таблиц, участвующих в операции соединения, и
их расположение в запросе имеет строгое значение. В
соответствии с правилами синтаксиса SQL, при использовании
оператора JOIN имя «левой» таблицы должно следовать
непосредственно после ключевого слова FROM, в то время
как имя «правой» таблицы указывается после ключевого
слова JOIN. Такой порядок обеспечивает правильную
интерпретацию запроса и корректное выполнение операции
соединения.
22

23. Соединение таблиц

МИРЭА - Российский технологический университет
mirea.ru
Соединение таблиц
LEFT JOIN — возвращает все строки из левой таблицы, даже если нет
совпадений в правой таблице. Если соответствующих строк в правой
таблице нет, соответствующие столбцы будут заполнены NULL
значениями.
Вернемся к сущности User, в нее были добавлены две новые записи. В
сервисе зарегистрировались два новых пользователя, но не оформили
подписку.
23

24. Соединение таблиц

МИРЭА - Российский технологический университет
mirea.ru
Соединение таблиц
Произведем левое соединение с сущностью Subscribe. Запрос возвращает
имена и фамилии всех пользователей, включая тех, кто не оформил
подписку в сервисе. Для таких пользователей значения из столбца
Subscribe.title будут NULL.
24

25. Соединение таблиц

МИРЭА - Российский технологический университет
mirea.ru
Соединение таблиц
RIGHT JOIN — работает аналогично LEFT JOIN, но
возвращает все строки из правой таблицы и только
совпадающие строки из левой таблицы. Если в левой
таблице нет соответствующих строк, возвращаются
NULL значения для столбцов из левой таблицы.
25

26. Соединение таблиц

МИРЭА - Российский технологический университет
mirea.ru
Соединение таблиц
CROSS JOIN — возвращает декартово произведение
двух таблиц, создавая комбинацию каждой строки
из первой таблицы с каждой строкой из второй
таблицы. Этот тип соединения обычно используется
редко и чаще всего для специальных задач, где
требуется объединение всех возможных пар строк.
26

27. Соединение таблиц

МИРЭА - Российский технологический университет
mirea.ru
Соединение таблиц
Представленный запрос возвращает все возможные комбинации
пользователей и подписок, независимо от того, какой тип подписки у
пользователя. В результирующей таблице получилось 198 строк, поэтому
рисунок представленном в укороченном виде.
27

28. Соединение таблиц

МИРЭА - Российский технологический университет
mirea.ru
Соединение таблиц
NATURAL JOIN — это тип соединения, который автоматически объединяет таблицы по всем
столбцам с одинаковыми именами. Этот вид соединения не требует явного указания
столбцов для сравнения, так как СУБД сама находит и использует совпадающие столбцы для
выполнения соединения. Соединение выполняется по всем столбцам с одинаковыми
именами в обеих таблицах. Если совпадающие столбцы не найдены, NATURAL JOIN вернёт
декартово произведение таблиц, что может привести к неожиданным результатам.
Для демонстрации работы естественного соединения немного модернизируем таблицы User
и Subscribe. Сделаем одинаковыми имена ключевых полей subscribe_id.
28

29. Соединение таблиц

МИРЭА - Российский технологический университет
mirea.ru
Соединение таблиц
Реализуем запрос с естественным соединением таблиц. В данном
примере NATURAL JOIN автоматически соединяет таблицы по столбцу
subscribe_id, так как это единственный столбец с одинаковым именем в
обеих таблицах.
29

30. Слияние таблиц

МИРЭА - Российский технологический университет
mirea.ru
Слияние таблиц
Оператор UNION позволяет объединять результаты
нескольких запросов в один набор данных. Этот оператор
используется для выполнения операций слияния, когда
необходимо объединить данные из разных запросов,
сохраняя при этом уникальные строки. UNION играет важную
роль в ситуациях, когда данные хранятся в разных таблицах
или, когда результаты различных запросов нужно объединить
в единый набор для дальнейшего анализа. Все запросы,
объединяемые с помощью UNION, должны возвращать
одинаковое количество столбцов. Типы данных столбцов в
каждом запросе должны быть совместимы. Порядок столбцов
в каждом запросе должен совпадать.
30

31. Слияние таблиц

МИРЭА - Российский технологический университет
mirea.ru
Слияние таблиц
Рассмотрим следующий пример. Сейчас в сервисе имеется таблица User.
Необходимо отдельно хранить информацию о пользователях, которые
зарегистрированы в сервисе больше двух лет. Для этого создана новая
таблица Old_user, имеющая одинаковые названия столбцов с таблицей
User.
31

32. Слияние таблиц

МИРЭА - Российский технологический университет
mirea.ru
Слияние таблиц
Итоговый запрос объединяет имена и фамилии пользователей и пользователей,
зарегистрированных более двух лет, в один список. В результате будут включены
только уникальные записи, то есть если имя и фамилия совпадают в обеих
таблицах, такая запись будет присутствовать в результатах только один раз.
32

33. Вложенные запросы (подзапросы)

МИРЭА - Российский технологический университет
mirea.ru
Вложенные запросы (подзапросы)
Подзапросы представляют собой запросы, встроенные в
другие запросы и выполняющиеся перед основным запросом,
что позволяет использовать результаты одного запроса в
другом. Подзапросы могут использоваться в различных частях
SQL-запроса: в операторе SELECT, в условиях WHERE, в списках
значений для операторов INSERT, и даже в выражениях после
FROM.
Подзапросы позволяют:
• выполнять сложные фильтрации данных на основе
результатов других запросов;
• использовать результаты вычислений в одном запросе в
другом запросе;
• проверять существование записей или значение,
соответствующих определённым условиям.
33

34. Вложенные запросы (подзапросы)

МИРЭА - Российский технологический университет
mirea.ru
Вложенные запросы (подзапросы)
Подзапросы можно классифицировать по нескольким
критериям:
• вложенные подзапросы — подзапросы, которые находятся в
операторе WHERE основного запроса;
• коррелированные подзапросы — подзапросы, которые
ссылаются на столбцы из основного запроса, они
выполняются для каждой строки основного запроса;
• независимые подзапросы — подзапросы, которые не
зависят от данных из основного запроса, они выполняются
один раз и возвращают фиксированный результат,
используемый в основном запросе.
34

35. Вложенные запросы (подзапросы)

МИРЭА - Российский технологический университет
mirea.ru
Вложенные запросы (подзапросы)
На рисунке представлен пример простого запроса, который
выбирает имена и фамилии авторов, которые родились в
стране Франция. Подзапрос возвращает id автора, который
затем используется в основном запросе для фильтрации
авторов.
35

36. Вложенные запросы (подзапросы)

МИРЭА - Российский технологический университет
mirea.ru
Вложенные запросы (подзапросы)
Оператор IN в SQL используется для проверки того,
принадлежит ли значение определённому
множеству значений. Когда он используется с
подзапросами, IN позволяет сравнивать значения из
основной таблицы с набором значений,
возвращённым подзапросом.
Предикат IN проверяет, совпадает ли значение
столбца с любым значением из списка,
возвращённого подзапросом. Если значение
найдено в этом списке, условие возвращает TRUE, и
строка включается в результат основного запроса.
Если совпадений нет, строка исключается.
36

37. Вложенные запросы (подзапросы)

МИРЭА - Российский технологический университет
mirea.ru
Вложенные запросы (подзапросы)
Первый запрос выбирает имена и фамилии авторов, которые родились в странах,
названия которых начинаются с «Р». Подзапрос возвращает список
идентификаторов авторов, и основной запрос проверяет, соответствует ли id
автора одному из этих идентификаторов.
Второй запрос возвращает имена и фамилии авторов, которые родились 25 июня
1903 года или 24 мая 1905 года.
37

38. Вложенные запросы (подзапросы)

МИРЭА - Российский технологический университет
mirea.ru
Вложенные запросы (подзапросы)
Предикат EXISTS используется для проверки существования
записей, соответствующих определённым условиям. В
отличие от IN, который проверяет наличие конкретного
значения в списке, EXISTS проверяет факт существования хотя
бы одной строки, соответствующей условиям подзапроса.
Оператор EXISTS возвращает TRUE, если подзапрос
возвращает хотя бы одну строку. Если подзапрос не
возвращает ни одной строки, EXISTS возвращает FALSE.
Основное применение EXISTS заключается в том, чтобы
проверить наличие данных, соответствующих определённым
критериям.
Предикат EXISTS чаще всего используется для работы с
несколькими таблицами.
38

39. Вложенные запросы (подзапросы)

МИРЭА - Российский технологический университет
mirea.ru
Вложенные запросы (подзапросы)
Рассмотрим таблицы User и Subscribe. Данные сущности связаны внешним ключом
subscribe_id. Представим ситуацию в которой нужно узнать имена и фамилией
пользователей, имеющих подписку «Премиум». Это можно сделать вручную,
сопоставляя идентификатор подписки с внешним ключом в таблице User. Однако,
этот способ займет много времени. Эту задачу можно решить быстро с
использованием подзапроса с предикатом EXISTS.
39

40. Вложенные запросы (подзапросы)

МИРЭА - Российский технологический университет
mirea.ru
Вложенные запросы (подзапросы)
Основной запрос выбирает имена и фамилии пользователей («U» — псевдоним
для таблицы User, «S» — псевдоним для таблицы Subscribe). В подзапросе
проверяется существование у пользователя подписки через subscribe_id и
добавляется условие, что подписка должна иметь название «Премиум». Если
EXISTS возвращает TRUE, то на экране появляются пользователи, соответствующие
заданным критериям.
40

41. Вложенные запросы (подзапросы)

МИРЭА - Российский технологический университет
mirea.ru
Вложенные запросы (подзапросы)
Если в предыдущий запрос добавить NOT, то запрос вернет всех пользователей с
подпиской не премиум.
41

42. Вложенные запросы (подзапросы)

МИРЭА - Российский технологический университет
mirea.ru
Вложенные запросы (подзапросы)
Предикаты ALL, ANY и SOME используются для
сравнения значений из основного запроса с
множеством значений, возвращаемых подзапросом.
Эти предикаты предоставляют более тонкий
контроль над условиями сравнения, позволяя
определять, должны ли условия быть выполнены для
всех значений, хотя бы для одного или нескольких.
Оператор ALL проверяет, соответствует ли условие
всем значениям, возвращённым подзапросом. Если
условие верно для всех значений, ALL возвращает
TRUE. В противном случае результат будет FALSE.
42

43. Вложенные запросы (подзапросы)

МИРЭА - Российский технологический университет
mirea.ru
Вложенные запросы (подзапросы)
Рассмотрим таблицу Book. В таблице есть внешний ключ, ссылающийся на
издательства. Необходимо узнать названия книг, которые были изданы после всех
книг, изданных издательством с идентификатором 2 (publisher_id=2).
Если внимательно посмотреть, то можно увидеть, что самая поздняя книга,
выпущенная вторым издательством, издана в 2021 году. Соответственно,
результирующая таблица должна показывать все книги, изданные после 2021
года.
43

44. Вложенные запросы (подзапросы)

МИРЭА - Российский технологический университет
mirea.ru
Вложенные запросы (подзапросы)
В итоге получаем следующий запрос. Основной запрос
выбирает название и год публикации книг. В подзапросе
проверяется существование годов издания, которые больше,
чем годы издания книг, выпущенных издательством №2.
44

45. Вложенные запросы (подзапросы)

МИРЭА - Российский технологический университет
mirea.ru
Вложенные запросы (подзапросы)
Операторы ANY и SOME функционально эквивалентны и
проверяют, соответствует ли условие хотя бы одному
значению из множества, возвращённого подзапросом. Если
условие верно хотя бы для одного значения, ANY или SOME
возвращают TRUE.
Вернемся к предыдущему примеру и немного изменим
условие. Необходимо узнать названия книг, которые были
изданы после любой из книг (если условия выбора не указаны
явно, то берется первая запись, удовлетворяющая
требованиям), изданных издательством с идентификатором 2
(publisher_id=2).
45

46. Вложенные запросы (подзапросы)

МИРЭА - Российский технологический университет
mirea.ru
Вложенные запросы (подзапросы)
46

47. Практическая работа

МИРЭА - Российский технологический университет
mirea.ru
Практическая работа
Для сдачи первой части 3-й практической работы необходимо
выполнить запросы по своей предметной области для
каждого примера из презентации: все агрегатные функции,
группировка (group by), группировка с фильтрацией (group by
+ having) (агрегатные функции можно объединить с
запросами группировки), inner join, right join, left join, cross
join, natural join, union, подзапросы с предикатами in, all, any,
some, exists, not exists. Запросы должны иметь описание и
целевое назначение. Выполнять запросы можно как в
командной строке, так и в IDE (Workbench, PgAdmin).
47