SQL запросы быстро. Часть 1

Введение


Язык SQL очень прочно влился в жизнь бизнес-аналитиков и требования к кандидатам благодаря простоте, удобству и распространенности. Из собственного опыта могу сказать, что наиболее часто SQL используется для формирования выгрузок, витрин (с последующим построением отчетов на основе этих витрин) и администрирования баз данных. И поскольку повседневная работа аналитика неизбежно связана с выгрузками данных и витринами, навык написания SQL запросов может стать фактором, из-за которого кандидат или получит преимущество, или будет отсеян. Печальная новость в том, что не каждый может рассчитывать получить его на студенческой скамье. Хорошая новость в том, что в изучении SQL нет ничего сложного, это быстро, а синтаксис запросов прост и понятен. Особенно это касается тех, кому уже доводилось сталкиваться с более сложными языками.

Обучение SQL запросам я разделил на три части. Эта часть посвящена базовому синтаксису, который используется в 80-90% случаев. Следующие две части будут посвящены подзапросам, Join'ам и специальным операторам. Цель гайдов: быстро и на практике отработать синтаксис SQL, чтобы добавить его к арсеналу навыков.

Практика


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

Кликнуть здесь

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

Структура sql-запросов


Общая структура запроса выглядит следующим образом:

SELECT ('столбцы или * для выбора всех столбцов; обязательно')
FROM ('таблица; обязательно')
WHERE ('условие/фильтрация, например, city = 'Moscow'; необязательно')
GROUP BY ('столбец, по которому хотим сгруппировать данные; необязательно')
HAVING ('условие/фильтрация на уровне сгруппированных данных; необязательно')
ORDER BY ('столбец, по которому хотим отсортировать вывод; необязательно')

Разберем структуру. Для удобства текущий изучаемый элемент в запроса выделяется CAPS'ом.

SELECT, FROM


SELECT, FROM — обязательные элементы запроса, которые определяют выбранные столбцы, их порядок и источник данных.

Выбрать все (обозначается как *) из таблицы Customers:

SELECT * FROM Customers

Выбрать столбцы CustomerID, CustomerName из таблицы Customers:

SELECT CustomerID, CustomerName FROM Customers


WHERE


WHERE — необязательный элемент запроса, который используется, когда нужно отфильтровать данные по нужному условию. Очень часто внутри элемента where используются IN / NOT IN для фильтрации столбца по нескольким значениям, AND / OR для фильтрации таблицы по нескольким столбцам.

Фильтрация по одному условию и одному значению:

select * from Customers
WHERE City = 'London'

Фильтрация по одному условию и нескольким значениям с применением IN (включение) или NOT IN (исключение):

select * from Customers
where City IN ('London', 'Berlin')

select * from Customers
where City NOT IN ('Madrid', 'Berlin','Bern')

Фильтрация по нескольким условиям с применением AND (выполняются все условия) или OR (выполняется хотя бы одно условие) и нескольким значениям:

select * from Customers
where Country = 'Germany' AND City not in ('Berlin', 'Aachen') AND CustomerID > 15

select * from Customers
where City in ('London', 'Berlin') OR CustomerID > 4

GROUP BY


GROUP BY — необязательный элемент запроса, с помощью которого можно задать агрегацию по нужному столбцу (например, если нужно узнать какое количество клиентов живет в каждом из городов).

При использовании GROUP BY обязательно:

  1. перечень столбцов, по которым делается разрез, был одинаковым внутри SELECT и внутри GROUP BY,
  2. агрегатные функции (SUM, AVG, COUNT, MAX, MIN) должны быть также указаны внутри SELECT с указанием столбца, к которому такая функция применяется.

Группировка количества клиентов по городу:

select City, count(CustomerID) from Customers
GROUP BY City

Группировка количества клиентов по стране и городу:

select Country, City, count(CustomerID) from Customers
GROUP BY Country, City

Группировка продаж по ID товара с разными агрегатными функциями: количество заказов с данным товаром и количество проданных штук товара:


select ProductID, COUNT(OrderID), SUM(Quantity) from OrderDetails
GROUP BY ProductID

Группировка продаж с фильтрацией исходной таблицы. В данном случае на выходе будет таблица с количеством клиентов по городам Германии:


select City, count(CustomerID) from Customers
WHERE Country = 'Germany'
GROUP BY City

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

select City, count(CustomerID) AS Number_of_clients from Customers
group by City

HAVING


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

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


select City, count(CustomerID) from Customers
group by City
HAVING count(CustomerID) >= 5 


В случае с переименованным столбцом внутри HAVING можно указать как и саму агрегирующую конструкцию count(CustomerID), так и новое название столбца number_of_clients:


select City, count(CustomerID) as number_of_clients from Customers
group by City
HAVING number_of_clients >= 5

Пример запроса, содержащего WHERE и HAVING. В данном запросе сначала фильтруется исходная таблица по пользователям, рассчитывается количество клиентов по городам и остаются только те города, где количество клиентов не менее 5:


select City, count(CustomerID) as number_of_clients from Customers
WHERE CustomerName not in ('Around the Horn','Drachenblut Delikatessend')
group by City
HAVING number_of_clients >= 5

ORDER BY


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

Простой пример сортировки по одному столбцу. В данном запросе осуществляется сортировка по городу, который указал клиент:


select * from Customers
ORDER BY City

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


select * from Customers
ORDER BY Country, City

По умолчанию сортировка происходит по возрастанию для чисел и в алфавитном порядке для текстовых значений. Если нужна обратная сортировка, то в конструкции ORDER BY после названия столбца надо добавить DESC:


select * from Customers
order by CustomerID DESC

Обратная сортировка по одному столбцу и сортировка по умолчанию по второму:

select * from Customers
order by Country DESC, City

JOIN


JOIN — необязательный элемент, используется для объединения таблиц по ключу, который присутствует в обеих таблицах. Перед ключом ставится оператор ON.

Запрос, в котором соединяем таблицы Order и Customer по ключу CustomerID, при этом перед названиям столбца ключа добавляется название таблицы через точку:

select * from Orders
JOIN Customers ON Orders.CustomerID = Customers.CustomerID

Нередко может возникать ситуация, когда надо промэппить одну таблицу значениями из другой. В зависимости от задачи, могут использоваться разные типы присоединений. INNER JOIN — пересечение, RIGHT/LEFT JOIN для мэппинга одной таблицы знаениями из другой,


select * from Orders
join Customers on Orders.CustomerID = Customers.CustomerID
where Customers.CustomerID >10

Внутри всего запроса JOIN встраивается после элемента from до элемента where, пример запроса:

Другие типы JOIN'ов можно увидеть на замечательной картинке ниже:


В следующей части подробнее поговорим о типах JOIN'ов и вложенных запросах.

При возникновении вопросов/пожеланий, всегда прошу обращаться!

Комментарии 35

    +3
    Следующие две части будут посвящены подзапросам, Join'ам и специальным операторам.

    1. У вас секция с объединениями в первую часть скопировалась.
    2. Ну и если у вас в тегах указано SQLite, то неплохо бы сказать, что он не все виды объединений, определённых в стандарте, поддерживает, см. «SQL Features That SQLite Does Not Implement», а также что в этом случае делать.
      0
      1. Да, действительно, выбрал тактику и стоило ее придерживаться) Здесь решил в итоге добавить короткую секцию, чтобы совсем новичкам было, с чем играться, пока ожидаю модерации и пишу вторую часть статьи
      2. Это правда, в следующей части постараюсь осветить это)
      –4

      Не совсем в тему, но моего хорошего знакомого, который давно и успешно рулит всякими oracle bd, тоже зовут Александр Соколов. А я, хоть и не Александр, но тоже sql-программист и тоже Соколов… Скажите, а как вас затянуло на эту тернистую дорожку из бизнес-анализа?

        0
        Александр Соколов очень распространенная комбинация имя-фамилия, полных тезок за жизнь встречал дважды, если не считать просто Александров Соколовых)

        Я пользуюсь sql исключительно для выгрузок и витрин. Самой простой пример из последнего: формирования витрины данных жалоб с сайта для формирования дашборда по динамике NPS, количеству жалоб и пр.
        Дорожка действительно тернистая, особенно когда вынужден использовать одновременно PL/SQL, MySQL
          0
          Если бы вы оба были Мухаммеды Соколовы, то это было бы странно.
          Соколов — очень распространённая фамилия, а Александр — имя. Меня вот тоже Александром зовут. Но я Орлов.
          +6

          Все просто только пока дело не доходит до запросов с переподвыподвертом
          А уж когда нужно чтобы это ещё и работало быстро…
          Это я к чему собственно
          Учебников по элементарному sql в интернете навалом. Ещё один ничего не изменит.
          Лучше напишите о интересных и сложных случаях применения, может кому-нибудь пригодится.

            0
            Спасибо за замечание!
            гайдов много, но я не встречал таких, которые а) короткие, б) содержат много примеров, в) сразу предлагают практику
            Интересные примеры обязательно включу)
            0

            Имхо стоит добавить про последовательность выполнения запроса, что важно для понимания работы фильтров, а то тот же having очень часто удивляет людей, которые пытаются его использовать вместо where например.

              0
              Про having правда)) В начале статьи я кратко описал общую структуру запроса, подумаю, как можно акцентировать внимание читателя на последовательности запроса
              0
              Самое интересное видимо будет, когда до лягушки дойдёте.
              Пока начальная подготовка.
                +2
                JOINs это НЕ пересечения множеств. Диаграммы Венна только все запутывают.
                Сколько строк вернет следующий запрос?
                with
                  a as (
                	select 1 x from dual
                	union all
                	select 1 x from dual
                        )
                , b as (
                	select 1 x from dual
                	union all
                	select 1 x from dual
                        )
                select a.*, b.*
                  from a inner join b on a.x = b.x
                

                Can we stop with the SQL JOINs venn diagrams insanity?
                  0
                  Зачем эта статья? Чем она отличается от любой справки по SQL?
                    +1
                    30-ти летней давности, заметим. Потому что на таком уровне изложения язык не отличается от того, каким он был в SQL/DS, к п
                    римеру
                      0
                      Моя ЦА: новички, студенты
                      Таргет: быстро, просто, с достаточным количеством примеров и практикой

                      Те справки, которые я встречал, не отвечали одновременно всем критериям, поэтому родилась эта статья)
                        +1
                        Есть прекрасный учебник на SQL EX
                        www.sql-tutorial.ru/ru/book_simple_select_statement/page1.html
                        Быстро, просто, с примерами и практикой.

                        Немного старый, но отлично объясняющий основы учебник на SQL.ru
                        www.sql.ru/docs/sql/u_sql/ch3.shtml
                        Быстро, просто, с примерами и практикой.

                        Есть неплохой учебник на SQL Academy
                        sql-academy.ru/guide/syntax-sql-select
                        Быстро, просто, с примерами и практикой.

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

                        А теперь вы анонсировали ещё и «продолжение».

                        Почему, мистер Андерсон, почему? Во имя чего? Что вы делаете? Зачем продолжаете копипастить?
                          0

                          за мистера Андерсона не скажу, а за себя скажу)
                          вышеперечисленными учебниками я действительно пользовался, но они не отвечали моему критерию по затраченному времени и не всегда нравилась последовательность материала.
                          Я не просто так поставил тег «студентам» и, как уже писал выше, один из таргетов: скорость
                          но в любом случае спасибо! тем, кто захочет углубиться самостоятельно и имеет хороший запас времени, ресурсы будут действительно очень полезны)

                          0
                          Да ладно… А что-то типа Кодда не пробовали? Я не очень верю, что за более чем 30 лет не написан базовый учебник хорошего уровня. Не базовый — верю, хотя бы по причине различий в диалектах, но по той же причине в качестве основы сойдет учебник, описывающий любую СУБД — потому что отличия все равно потом придется изучать по справочнику.
                        0

                        А где удалить, обновить, вставить?

                          0

                          В следующих, трех, статьях

                            0
                            Да, это это действительно опишу дальше
                            Для новичка и пользователя с доступом «чтение» написанное в статье — самое важное
                            Создание/удаление/добавление — чуть повыше уровнем, но в любом случае, это опишу
                              0
                              sokolov_alexr
                              Спасибо за пост!

                              У меня вопрос-предложение – будет ли в аналогичной подаче пост о работе с самой базой данных? На примере используемой вами системы (или системы, которая наиболее распространена для подобной работы): как подключиться к базе, как создать таблицу(-ы) / добавить или удалить столбцы и записи, связать таблицы между собой (один-ко-многим и т.д.). Такой средний (часто встречающийся) набор стандартных операций над базой помимо синтаксиса SQL. Крайне желательно, конечно, чтобы был интерактив – как в данном посте.

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

                                Спасибо за отзыв)
                            0
                            Есть ли ide для SQL кода с завершением? Чтобы писать начало команды а программа дополняла? Что бы форматировала код? И какие еще инструменты для ускорения и упрощения работы есть?
                            • НЛО прилетело и опубликовало эту надпись здесь
                                0
                                dbForge хороший и бесплатный
                                0
                                Самое то для новичков.
                                  0
                                  Спасибо! Помочь новичкам и было моей основной целью)
                                  0
                                  Мне, как начинающему, этот материал оказался полезен.
                                    0
                                    Рад, что понравилось!
                                    0

                                    Эээ… Это попытка пересказать "Понимание SQL" М.Грабера???

                                      0
                                      Это пересказ собственных знаний в сжатом (и немного урезанном) виде для новичков)
                                      0
                                      Про типы запросов уже сказано. Из «ещё» — не нравится описание JOIN, во-первых, потому, что он не там, где надо (а должен он быть подразделом FROM), во-вторых, потому, что, мягко говоря, привирает — то, что названо ключом, запросто может ссылаться на третьи таблицы и/или скалярные данные. HAVING (в тегах указано MySQL) порой используется и без агрегирования. Про CTE (совсем до) и оконные функции (совсем после) даже не говорю.

                                      Но главным недостатком считаю следующее. Да, для того, кто вообще никогда и ни в чём не программил, сойдёт — вот только такой товарищ ничего не поймёт, текст предполагает наличие минимальных навыков программистского мышления. А для того, кто уже программировал, упущена очень важная необходимость «сломать мышление». В программировании надо думать итерациями (выбираем по одному то, что подходит), а в SQL надо наоборот, думать множествами (сваливаем всё в кучу, а потом выбрасываем то, что не подходит). Если этого не сделать — мозг начинает порождать сонмы сто лет ненужных коррелированных подзапросов, в которых чёрт ногу сломит.
                                        0
                                        Спасибо за развернутый фидбек!
                                        СТЕ и оконные функции здесь намеренно не рассматривал, чтобы не запутать вливающегося новичка, об этом имхо лучше говорить, когда есть база в голове.
                                        Насчет «ничего не поймет» не соглашусь, перед написанием статьи я давал этот материал кусочно в личных сообщениях и вживую людям, которые была далеки от программирования в принципе и получал положительную обратную связь, что, собственно, и подтолкнуло к тому, чтобы написать статью.
                                        Насчет HAVING хорошее замечание, вы правы, внесу правку, спасибо!
                                        –1
                                        Как начинающий скажу — статья очень и очень понятна. После чтения www.sql-tutorial.ru/ru/book_simple_select_statement/page1.html — вообще небо и земля. Спасибо! Буду ждать остальные части.
                                          0
                                          Спасибо большое! Бальзам на душу от таких отзывов))

                                        Только полноправные пользователи могут оставлять комментарии. Войдите, пожалуйста.

                                        Самое читаемое