Моргунов 6 глава

Запросы

Предикат сравнения BETWEEN

SELECT * FROM aircrafts WHERE range BETWEEN 3000 AND 6000;

ORDER BY - упорядочивание ASC по возрастанию, DESC по убыванию

если не указывать вид упорядочивания, то сортирует по возраставнию

SELECT DISTINCT timezone FROM airports ORDER BY 1;

в этом запросе DISTINCT позволяет нам ивзлечь все уникальные таймзоны,

ORDER BY 1 - позволяет не писать имя столбца , а его номер в запросе

SELECT airport_name, city, longitude
  FROM airports
  ORDER BY longitude DESC
  LIMIT 3;

LIMIT позволяет ограничить количество строк в выборке в данном случае мы попросим отобразить только первые 3

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

OFFSET используется пропуска строк,сколько строк от начала нужно пропустить.

Пример:

SELECT airport_name, city, longitude
  FROM airports
  ORDER BY longitude DESC
  LIMIT 3
OFFSET 3; 

SELECT model, range,
  CASE WHEN range < 2000 THEN 'Ближнемагистральный'
       WHEN range < 5000 THEN 'Среднемагистральный'
       ELSE 'Дальнемагистральный'
  END AS type
  FROM aircrafts
  ORDER BY model;

Пример использования условных выражения

Эти условия условные выражения мы можем очень гибко использования для формирования столбцов

Соединения

(INNER) JOIN соединение, которое включает пересечение таблиц

LEFT OUTER JOIN включает все записи из левой таблицы и пересечений из правой

RIGHT OUTER JOIN включает все записи из правой таблицы и пересечение из левой

FULL OUTER JOIN включает все записи из левой и правой таблицы

CROSS JOIN - декартово произведение(каждая запись с каждой)

OUTER и INNER можно опустить

Можно соединять таблицу саму с собой

Также можно реализовать декартово произведение через FROM

SELECT *
  FROM airports a1, airports a2

В самом простом случае, как работает JOIN:

сначала мы берем декартово произведение записей таблицы и фильтруем им на основе условий ,которые мы задаем,из-за этого JOIN довольно тяжелая операция .Но планировщик СУБД может это оптимизировать

VALUES

это выражение позволяет создать константную таблицу без сохрания ее на диске

пример

SELECT * FROM (VALUES (1, 'one'), (2, 'two'), (3, 'three')) AS t (num,letter);

UNION

объедение множества строк , исключает дубликаты, чтобы оставить дубликаты можно использовать UNION ALL

INTERSECT - пересечение множества строк , аналогично есть INTERSECT ALL

EXCEPT - разность между строки, аналогично есть EXCEPT ALL

важно отметить ограничение для применения эти ключевых слов:

Запросы должны возвращать одинаковое число столбцов, типы данных у столбцов также должны совпадать. 

Группировки

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

Оконные функции

Интересная тема, но в этой книге не достаточно раскрыта

вообщем - то идея в том,что

мы можем определить раздел на основе какого-то столбца как в группировке

к примеру все уроки с предметом математика это раздел или partition

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

Дальше мы можем проводить разные расчет определяя раздел и это фрейм

Пример из жизни:

Когда 1 сущность пагинация это 5 сущностей в таблице и нам нужно пагинироватсья по таблице по набору из 5 сущностей и они должны быть не отделимиы от нас и причем мы должны поддерживать порядок в каждой партиции из 5 сущностей

Существует много разных видов оконных функций

Подзапросы

скалярный подзапрос - когда запрос возвращает одну строку и один столбец

некореллированный - подзапросы , который не зависят от запросов

кореллированный - наоборот,когда запрос зависит от другого запроса

CTE

общее табличное выражение

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

но они хранятся в оперативе насколько понимаю и иногда проще написать подзапрос