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’ов и вложенных запросах.

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

Базовый синтаксис SQL запроса

Одна из основных функций SQL — это получение выборок данных из СУБД.
Для этого в SQL используется оператор SELECT. Давайте рассмотрим несколько простых запросов с его участием.

Для начала важно понимать, что через оператор SELECT можно выводить данные не только из таблиц базы данных,
но и произвольные строки, числа, даты и т.д. Например, так можно вывести произвольную строку:

SELECT "Hello world"

Для вывода всех полей из определённой таблицы используется символ *.  Давайте взглянем на схему базы данных и

выведем данные одной из таблиц.
SELECT * FROM FamilyMembers





member_id
status
member_name
birthday




1
father
Headley Quincey
1960-05-13T00:00:00.000Z


2
mother
Flavia Quincey
1963-02-16T00:00:00.000Z


3
son
Andie Quincey
1983-06-05T00:00:00.000Z


4
daughter
Lela Quincey
1985-06-07T00:00:00.000Z


5
daughter
Annie Quincey
1988-04-10T00:00:00.000Z


6
father
Ernest Forrest
1961-09-11T00:00:00.000Z


7
mother
Constance Forrest
1968-09-06T00:00:00.000Z



Если необходимо вывести информацию только по определённым столбцам таблицы, а не всю сразу, то

это можно сделать перечисляя названия столбцов через запятую:
SELECT member_id, member_name FROM FamilyMembers





member_id
member_name




1
Headley Quincey


2
Flavia Quincey


3
Andie Quincey


4
Lela Quincey


5
Annie Quincey


6
Ernest Forrest


7
Constance Forrest



В случае, если мы хотим вывести какие-то столбцы таблицы, но чтобы в итоговой выборке они были названы иначе,

мы можем использовать псевдонимы (их также называют алиасами). 
Их синтаксис достаточно простой, мы должны использовать оператор AS. Как в примере ниже:
SELECT member_id, member_name AS Name FROM FamilyMembers





member_id
Name




1
Headley Quincey


2
Flavia Quincey


3
Andie Quincey


4
Lela Quincey


5
Annie Quincey


6
Ernest Forrest


7
Constance Forrest



Или же можно обойтись и без него, просто написав желаемое наименование поля через пробел.
SELECT member_id, member_name Name FROM FamilyMembers


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

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

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

«Самостоятельные упражнения», то рекомендуем  вернуться к нему .
 SQL: Оператор SELECT 

 В этом учебнике по SQL объясняется, как использовать оператор  SQL  SELECT с синтаксисом, примерами и практическими упражнениями. 

 Описание 
 Оператор SQL SELECT используется для извлечения записей из одной или нескольких таблиц в вашей базе данных SQL. Полученные записи известны как набор результатов. 
 Подписаться 
 Синтаксис 
 Синтаксис оператора SELECT в SQL: 
 Выражения SELECT
ИЗ столов
[ГДЕ условия]
[ORDER BY выражение [ ASC | ДЕСК ]]; 
 Параметры или аргументы 

 выражения 
 Столбцы или вычисления, которые вы хотите получить.  Используйте *, если вы хотите выбрать все столбцы. 
 таблицы 
 Таблицы, из которых вы хотите извлечь записи. В предложении FROM должна быть хотя бы одна таблица. 
 ГДЕ условия 
 Дополнительно. Условия, которые должны быть соблюдены для выбора записей. Если условия не указаны, то будут выбраны все записи. 
 ЗАКАЗАТЬ по выражению 
 Дополнительно. Выражение, используемое для сортировки записей в результирующем наборе. Если указано более одного выражения, значения должны быть разделены запятыми. 
 АСЦ 
 Дополнительно. ASC сортирует результирующий набор в порядке возрастания по  выражению  . Это поведение по умолчанию, если нет модификатора provider. 
 DESC 
 Дополнительно. DESC сортирует результирующий набор в порядке убывания по  выражению  . 

 DDL/DML для примеров 
 Если вы хотите следовать этому руководству, получите DDL для создания таблиц и DML для заполнения данных.  Тогда попробуйте примеры в своей собственной базе данных! 
 Получить DDL/DML 
 Пример — выбор всех полей из таблицы 
 Давайте рассмотрим пример, который показывает, как использовать оператор SQL SELECT для выбора всех полей из таблицы. 
 В этом примере у нас есть таблица с именем  клиентов  со следующими данными: 



 идентификатор_клиента 
 фамилия 
 имя_имя 
 любимый_веб-сайт 




 4000 
 Джексон 
 Джо 
 techonthenet.com 


 5000 
 Смит 
 Джейн 
 digminecraft.com 


 6000 
 Фергюсон 
 Саманта 
 bigactivities.com 


 7000 
 Рейнольдс 
 Аллен 
 checkyoumath.com 


 8000 
 Андерсон 
 Пейдж 
 НУЛЕВОЙ 


 9000 
 Джонсон 
 Дерек 
 techonthenet. com 



 Теперь давайте продемонстрируем, как работает оператор SELECT, выбрав все столбцы из  клиентов  стол. Введите следующую инструкцию SELECT: 
 Попробуйте 
 SELECT *
ОТ клиентов
ГДЕ Favorite_website = 'techonthenet.com'
ЗАКАЗАТЬ ПО фамилии ASC; 
 Будет выбрано 2 записи. Вот результаты, которые вы должны увидеть: 



 customer_id 
 фамилия 
 имя_имя 
 любимый_веб-сайт 




 4000 
 Джексон 
 Джо 
 techonthenet.com 


 9000 
 Джонсон 
 Дерек 
 techonthenet.com 



 В этом примере мы использовали * для обозначения того, что мы хотим просмотреть все поля из таблицы  клиентов , где  любимый_веб-сайт  — это «techonthenet. com». Набор результатов отсортирован по  last_name  в порядке возрастания. 
 Пример — выбор отдельных полей из таблицы 
 Вы также можете использовать оператор SQL SELECT для выбора отдельных полей из таблицы, а не всех полей из таблицы. 
 В этом примере у нас есть таблица с именем  поставщиков  со следующими данными: 



 supplier_id 
 имя_поставщика 
 город 
 состояние 




 100 
 Майкрософт 
 Редмонд 
 Вашингтон 


 200 
 Гугл 
 Маунтин-Вью 
 Калифорния 


 300 
 Оракул 
 Редвуд Сити 
 Калифорния 


 400 
 Кимберли-Кларк 
 Ирвинг 
 Техас 


 500 
 Тайсон Фудс 
 Спрингдейл 
 Арканзас 


 600 
 СК Джонсон 
 Расин 
 Висконсин 


 700 
 Доул Фуд Компани 
 Вестлейк Виллидж 
 Калифорния 


 800 
 Цветочная еда 
 Томасвилл 
 Грузия 


 900 
 Электронное искусство 
 Редвуд Сити 
 Калифорния 



 Теперь давайте продемонстрируем, как использовать оператор SELECT для выбора отдельных столбцов из таблицы.  Введите следующую инструкцию SELECT: 
 Попробуйте 
 SELECT supplier_name, city
ОТ поставщиков
ГДЕ supplier_id > 500
ЗАКАЗАТЬ ПО provider_name ASC, город DESC; 
 Необходимо выбрать 4 записи. Вот результаты, которые вы должны увидеть: 



 supplier_name 
 город 




 Доул Фуд Компани 
 Вестлейк Виллидж 


 Электронное искусство 
 Редвуд Сити 


 Цветочные продукты 
 Томасвилл 


 СК Джонсон 
 Расин 



 В этом примере будут возвращены только поля supplier_  name  и  city  из таблицы  Suppliers , где  supplier_id  значение больше 500. Результаты сортируются по supplier_  имени  в порядке возрастания, а затем  городу  в порядке убывания. 
 Пример — выбор отдельных полей из нескольких таблиц 
 Вы также можете использовать оператор SQL SELECT для извлечения полей из нескольких таблиц.  
 В этом примере у нас есть таблица с именем  orders  со следующими данными: 



 order_id 
 идентификатор_клиента 
 дата_заказа 




 1 
 7000 
 18.04.2016 


 2 
 5000 
 18.04.2016 


 3 
 8000 
 19.04.2016 


 4 
 4000 
 20.04.2016 


 5 
 НУЛЕВОЙ 
 01.05.2016 



 И таблица под названием  customers  со следующими данными: 



 customer_id 
 фамилия 
 имя_имя 
 любимый_веб-сайт 




 4000 
 Джексон 
 Джо 
 techonthenet. com 


 5000 
 Смит 
 Джейн 
 digminecraft.com 


 6000 
 Фергюсон 
 Саманта 
 bigactivities.com 


 7000 
 Рейнольдс 
 Аллен 
 checkyoumath.com 


 8000 
 Андерсон 
 Пейдж 
 НУЛЕВОЙ 


 9000 
 Джонсон 
 Дерек 
 techonthenet.com 



 Теперь давайте выберем столбцы из таблиц  заказов  и  клиентов . Введите следующую инструкцию SELECT: 
 Попробуйте 
 SELECT orders.order_id, customers.last_name
ОТ заказов
ВНУТРЕННЕЕ ПРИСОЕДИНЯЙТЕСЬ к клиентам
ON заказы.customer_id = customers.customer_id
ГДЕ orders.order_id <> 1
ЗАКАЗАТЬ ПОorders.order_id; 
 Будет выбрано 3 записи.  Вот результаты, которые вы должны увидеть: 



 идентификатор_заказа 
 фамилия 




 2 
 Смит 


 3 
 Андерсон 


 4 
 Джексон 



 Этот пример SELECT объединяет две таблицы, чтобы дать нам результирующий набор, который отображает  order_id  из таблицы  заказов  и  last_name  из  клиентов  табл. Каждый раз, когда мы используем столбец в операторе SELECT, мы добавляем к столбцу префикс имени таблицы (например,  orders  .  order_id  ) на случай неясности в отношении того, к какой таблице принадлежит столбец. 
 Если вы хотите выбрать все поля из таблицы  заказов , а затем поле  фамилия  из таблицы клиентов, введите следующую инструкцию SELECT: 
 Попробуйте 
 SELECT заказы. *, customers.last_name
ОТ заказов
ВНУТРЕННЕЕ ПРИСОЕДИНЯЙТЕСЬ к клиентам
ON заказы.customer_id = customers.customer_id
ГДЕ orders.order_id <> 1
ЗАКАЗАТЬ ПОorders.order_id; 
 Будет выбрано 3 записи. Вот результаты, которые вы должны увидеть: 



 order_id 
 идентификатор_клиента 
 дата_заказа 
 фамилия 




 2 
 5000 
 18.04.2016 
 Смит 


 3 
 8000 
 19.04.2016 
 Андерсон 


 4 
 4000 
 20.04.2016 
 Джексон 



 В этом примере мы используем  заказы.*  означает, что мы хотим выбрать все поля из таблицы  заказов , а затем мы выбираем поле  last_name  из таблицы  клиентов . 
 Практические упражнения 
 Если вы хотите проверить свои навыки работы с оператором SQL SELECT, выполните некоторые из наших практических упражнений.  
 Эти упражнения позволят вам проверить свои навыки работы с оператором SELECT. Вам будут предложены вопросы, которые вам необходимо решить. После каждого упражнения мы предлагаем решение, чтобы вы могли проверить свой ответ. Попробуйте! 
 Перейти к практическим упражнениям 
 SQL — запросы SELECT 
 Запросы SELECT извлекают данные строк/столбцов из одной или нескольких существующих таблиц. 
 Синтаксис: 
 ВЫБЕРИТЕ столбец1, столбец2,... столбецN
ОТ имя_таблицы
 
 Для демонстрационных целей во всех приведенных здесь примерах будет использоваться следующая таблица  Employee . 




 Эмпид 
 Имя 
 Фамилия 
 Электронная почта 
 Телефон № 
 Зарплата 




 1 
 ‘Джон’ 
 ‘Король’ 
 ‘[электронная почта защищена]’ 
 ‘650. 127.1834′ 
 33000 


 2 
 ‘Джеймс’ 
 «Бонд» 



             

 3 
 ‘Нина’ 
 ‘Кочхар’ 
 ‘[электронная почта защищена]’ 
 ‘123.456.4568’ 
 17000 


 4 
 ‘Лекс’ 
 ‘Де Хаан’ 
 ‘[электронная почта защищена]’ 
 ‘123.456.4569’ 
 15000 



 Выбрать все столбцы 
 Оператор  *  представляет все столбцы таблицы. Таким образом, вам не нужно указывать имя каждого столбца в запросе SELECT, чтобы получить данные из всех столбцов.
     
 ВЫБЕРИТЕ * ОТ Сотрудника;
 
 Приведенный выше запрос возвращает данные всех строк и столбцов из таблицы  Employee , как показано ниже.  




 Эмпид 
 Имя 
 Фамилия 
 Электронная почта 
 Телефон № 
 Зарплата 




 1 
 ‘Джон’ 
 ‘Король’ 
 ‘[электронная почта защищена]’ 
 ‘650.127.1834’ 
 33000 


 2 
 ‘Джеймс’ 
 «Бонд» 



             

 3 
 ‘Нина’ 
 ‘Кочхар’ 
 ‘[электронная почта защищена]’ 
 ‘123.456.4568’ 
 17000 


 4 
 ‘Лекс’ 
 ‘Де Хаан’ 
 ‘[электронная почта защищена]’ 
 ‘123. 456.4569′ 
 15000 



 Выберите данные определенных столбцов 
 Укажите имена столбцов в операторе SELECT, чтобы получить данные только из этих столбцов, как показано ниже.
  
 ВЫБЕРИТЕ EmpId, Имя, Фамилию ОТ Сотрудника;
 




 Эмпид 
 Имя 
 Фамилия 




 1 
 ‘Джон’ 
 ‘Король’ 


 2 
 ‘Джеймс’ 
 «Бонд» 


 3 
 ‘Нина’ 
 ‘Кочхар’ 


 4 
 ‘Лекс’ 
 ‘Де Хаан’ 



 Псевдоним для столбцов и таблиц 
 Вы можете указать псевдоним для одного или нескольких столбцов в запросе SELECT.  Псевдоним — это временное имя таблицы или столбца в запросе.
      
 Преимущество псевдонима: 


        Псевдоним делает столбец более читаемым в результирующем наборе. 

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

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

 Следующий запрос указывает  «Идентификатор сотрудника»  для  EmpId  и  Имя  в качестве псевдонима для  Имя  в запросе SELECT.

    Укажите псевдоним в одинарных или двойных кавычках, если вы хотите, чтобы в нем был пробел или другая строка.
     
 ВЫБЕРИТЕ EmpId "Идентификатор сотрудника", Имя КАК Имя ОТ Сотрудника;
 




 Идентификатор сотрудника 
 Имя 




 1 
 ‘Джон’ 


 2 
 ‘Джеймс’ 


 3 
 ‘Нина’ 


 4 
 ‘Лекс’ 



 Операторы в операторе SELECT 
 В операторе select можно указать операторы для выполнения некоторых действий со значением столбца.  Например, оператор  +  в MS SQL Server и  || Оператор  в базах данных PostgreSQL, MySQL и Oracle объединяет строковые значения или добавляет числовые значения.

    Следующее объединяет два  varchar  столбца в результате. 
 ВЫБЕРИТЕ EmpId, Имя + ' ' + Фамилия КАК "Полное имя"
ОТ Сотрудника;
 




 Эмпид 
 Полное имя 




 1 
 ‘Джон Кинг’ 


 2 
 ‘Джеймс Бонд’ 


 3 
 ‘Нина Кочхар’ 


 4 
 ‘Лекс Де Хаан’ 



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