Писать sql запросы как: Урок 1. Первые SQL запросы

Чистый SQL код — Статьи : Персональный сайт Михаила Флёнова

05 Июля 2021
Базы данных

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

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

Если запрос состоит из одного select из одной таблицы, то его можно записать как попало. Следующий запрос особо оформлять и не нужно, он легко читается:

select * from person

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

Для начала нужно сказать, что выбирать все колонки с помощью звездочки можно ради тестов или когда вам действительно нужны все колонки, что бывает очень редко. В реальности рекомендуется или лучше все же приучать себя выбирать только колонки, которые вам действительно нужны и при этом указывать их в порядке, который вам необходим. В этом случае вы будете защищены от возможной смены структуры базы данных. Я конечно не рекомендую менять структуру, но сталкивался с таким уже много раз. Правда бывают и. случаи, когда наоборот, вы добавили колонку в запрос, а ее реально удалили и тут уже звездочка наоборот может спасти.

Перечисление колонок помогает в системах, где больше одного сервера приложений и нужен максимальный аптайм. Чтобы этого достигнуть желательно делать обновление базы данных не ломающим приложение, то есть вы должны иметь возможность добавить новые колонки и таблицы, но при этом старый код должен продолжать работать, а для этого нужно соблюдать два основных правила: не удалять колонки и не изменять на несовместимый тип.

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

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

select personid, teamid, firstname, lastname, positionid from person

Вот тут уже все не так все гладко читается. Если команды SQL написать большими буквами, что очень часто делают, то текст уже становиться чуть лучше:

SELECT personid, teamid, firstname, lastname, positionid FROM person

Это еще не все, можно разделить на строки, каждую секцию написать в своей строке:

SELECT personid, teamid, firstname, lastname, positionid 
FROM person

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

select personid, teamid, firstname, lastname, positionid 
from person

Да, это читаемо, но все же на мой вкус все писать большими буквами лучше.

Если код хранить в тексте программы, а текст программы находиться в GIT, то могут возникнуть проблемы со слиянием кода. Если два программиста изменят запрос и каждый из них добавит по одной колонке, то точно возникнет конфликт. Если вы используете ORM, то разрулить конфликт будет относительно просто, а если обращение идет через низкоуровневые функции и индексы колонок, то гореть вам в аду.

Если писать колонки в отдельной строке то можно снизить вероятность конфликтов:

SELECT personid, 
              teamid, 
              firstname, 
              lastname, 
              positionid 
FROM person

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

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

SELECT personid, 
              teamid, 
 --              firstname, 
              lastname, 
              positionid 
FROM person

Теперь я отключил колонку firstname и это сделать было очень просто. В оформленном таким образом запросе проще сортировать колонки, если забыть про последнюю.

SELECT personid, 
              firstname, 
              lastname, 
              teamid, 
              positionid 
FROM person

Недостаток – перемещать удобно любую колонку, кроме последней. С конфликтами тоже была проблема с последней колонкой, причем чтобы добавить новую колонку программисты будут не только добавлять еще одну строку, но еще и трогать последнюю строку с колонками, туда придется добавить запятую после positioned. Эта запятая может стать проблемой, потому что она как раз усложняет слияние.

Запятую лучше писать вначале:

SELECT personid
            , firstname
            , lastname 
            , teamid
            , positionid 
FROM person

Теперь можно закомментировать любую колонку, кроме первой, а это первичный ключ и его редко отключают в запросе. И добавлять новую строку проще – просто добавляем в конец и добавляем одну единственную строку, а значит конфликты разрешать проще.

Да, для огромных запросов, которые возвращают более 20 колонок, такой запрос начинает занимать очень много места в файле и приходиться очень много скролить в коде. Скролить тоже плохо. И вот мы встаем перед выбором – что лучше? Зависит от ситуации и личных предпочтений, я же все же рекомендовал бы писать имена колонки каждую в своей строке.

Усложняем жизнь, добавляем объединение таблиц:

SELECT personid
            , firstname
            , lastname 
            , teamid
            , p.positionid 
            , psn.Name
FROM person p
   JOIN position psn on p.positionid = psn.positionid

Некоторые явно предпочитают писать INNER JOIN, чтобы указать, что перед нами именно INNER объединение. Я за счет лени не пишу лишние пять букв, а сокращаю все до JOIN, потому что привык, что именно это поведение по умолчанию. Тут у меня проблем с чтением нет.

Чуть другое дело, если что-то касается – где писать дополнительные фильтры. Например, следующие два запроса будут абсолютно идентичны:

SELECT personid
     , firstname
     , lastname 
     , teamid
     , p.positionid 
     , psn.Name
FROM person p
   JOIN position psn on p.positionid = psn.positionid and psn.Name = 'Coach'

И

 SELECT personid
     , firstname
     , lastname 
     , teamid
     , p.positionid 
     , psn.Name
FROM person p
   JOIN position psn on p.positionid = psn.positionid 
WHERE psn.Name = 'Coach'

И даже этот запрос

SELECT personid
    , firstname
    , lastname 
    , teamid
    , p.positionid 
    , psn.Name
FROM person p
   JOIN position psn on 1=1
WHERE p.positionid = psn.positionid 
  AND psn.Name = 'Coach'

Вот реально все равно, где вы напишите проверку на Name = ‘Coach’, потому что в случае с INNER JOIN она не повлияет на результат и с точки зрения производительности я не видел разницы, потому что оптимизаторы обычно не смотрят, где вы пишите фильтры. Но проверка Name = ‘Coach’ логически не относится к объединению двух таблиц и поэтому не должна быть в JOIN, она должна быть в секции WHERE, потому что просто логически принадлежит этой секции.

Логическое расположение важно, и оно в будущем вам будет говорить, где искать определенный код. Например, если вы видите, что связь между двумя таблицами не работает, значит нужно идти, и проверять JOIN и что написано после ON. Если результат не совсем верный и отображаются только тренера, то скорей всего нужно смотреть на фильтры, а значит это секция WHERE. В реальной жизни бывает всякое, но по умолчанию именно логика поиска проблемы должна быть именно такой.

Посмотрим на секцию WHERE в последнем запросе:

WHERE p.positionid = psn.positionid 
  AND psn.Name = 'Coach'

Обратите внимание, что каждый фильтр находиться в отдельной строке. Каждый AND должен быть отдельно. В одну строку можно помещать две проверки, только если они объединены с помощью OR, это удобно, чтобы не забыть поставить скобки:

WHERE p. positionid = psn.positionid 
  AND (psn.Name = 'Coach' OR psn.Name like 'P%')

Если две проверки в одной строке, это всегда должно быть сигналом, что они должны работать как одно целое и там есть скобки.

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

В одном из видео Строим таблицу чемпионата на SQL — Проще некуда и в этой текстовой версии Практика — ищем победителей я написал запрос, в котором мы искали победителей чемпионата по какому-то командному спорту. Запрос получился вот такой:

select team, sum(points)
from (
    select
    case 
        when hometeamgoals > guestteamgoals then hometeamid
        when hometeamgoals < guestteamgoals then guestteamid
    end team,
    3 as points
    from game
    where hometeamgoals != guestteamgoals 

    union all

    select hometeamid, 1 as points
    from game
    where hometeamgoals = guestteamgoals 

    union all

    select guestteamid, 1 as points
    from game
    where hometeamgoals = guestteamgoals 
) p
group by team
order by 2 desc

Если вы не видели того видео или видели его очень давно, то понять и разобрать подобный запрос будет достаточно сложно. В секции FROM находиться три запроса и каждый из них выполняет что-то, а чтобы понять, что они делают, приходиться читать каждый из них и вдумываться.

Можно было бы использовать комментарии, но я противник таких вещей. Если нужен комментарий, то код скорей всего плохой и читать его поддерживать будет сложно. Комментарии – это что-то, что должно быть в коде только в самом крайнем случае.

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

select team, sum(points)
from (
    select * from winners3points
    union all
    select * from hometies1point
    union all
    select * from guestties1point
) p
group by team
order by 2 desc

Вот такой запрос читать на много проще – здесь объединяются три выборки winners3points, hometies1point и guestties1point. По имени уже понятно, что это делает, а как. . . Вот тут есть два варианта, можно взять код, который был на этом месте и вынести в представления. Но я создаю представления только если код будет использоваться в нескольких местах. Если у вас нагруженная система с требованием обновления онлайн, то представления могут привести к проблемам и с ними нужно быть осторожнее.

Если какой-то код только разово, то можно использовать CTE (в MySQL поддерживается с 8-й версии):

with 
   winners3points as (
      select
         case 
            when hometeamgoals > guestteamgoals then hometeamid
            when hometeamgoals < guestteamgoals then guestteamid
         end team,
      3 as points
      from game
      where hometeamgoals != guestteamgoals 
   ),
  hometies1point as (
    select hometeamid, 1 as points
    from game
    where hometeamgoals = guestteamgoals 
  ),
  guestties1point as (
    select guestteamid, 1 as points
    from game
    where hometeamgoals = guestteamgoals 
  )

select team, sum(points)
from (
    select * from winners3points
    union all
    select * from hometies1point
    union all
    select * from guestties1point
) p
group by team
order by 2 desc

CTE практически то же самое, что и псевдонимы для SQL запросов. Глядя на имена псевдонимов, мы без проблем можем понять, что делает запрос без дополнительных комментариев, а если мы хотим понять, как работает один из псевдонимов, мы можем взглянуть на него.

CTE может помочь даже с точки зрения производительности. Из личного опыта – разбиение запросов на небольшие составляющие позволяет базе данных проще понять, что от него требуется.

Внимание!!! Если ты копируешь эту статью себе на сайт, то оставляй ссылку непосредственно на эту страницу. Спасибо за понимание

Почему стоит начать изучать SQL

Как оператор Описание
ГДЕ EmployeeName LIKE ‘e%’ Находит значения, начинающиеся с «e»
ГДЕ Имя Сотрудника КАК ‘%e’ Находит значения, оканчивающиеся на «е»
ГДЕ EmployeeName LIKE ‘%en%’ Находит значения, которые имеют «en» в любой позиции
ГДЕ EmployeeName LIKE ‘_e%’ Находит значения, которые содержат «е» только во второй позиции
ГДЕ EmployeeName LIKE ‘e__%’ Находит значения, начинающиеся с «e» и имеющие длину не менее 3 символов
ГДЕ EmployeeName LIKE ‘e%0’ Находит значения, начинающиеся с «е» и заканчивающиеся на «0»

e_id e_name е_зарплата e_age e_gender e_dept
1 Джон 30000 25 мужской операции
2 юлия 540000 27 женский операции
3 Анна 125000 27 женский аналитика
4 боб 50000 24 мужской поддержка
5 макс. 540000 29 мужской операции