SQL запросы быстро. Часть 1 / Хабр
Введение
Язык SQL очень прочно влился в жизнь бизнес-аналитиков и требования к кандидатам благодаря простоте, удобству и распространенности. Из собственного опыта могу сказать, что наиболее часто SQL используется для формирования выгрузок, витрин (с последующим построением отчетов на основе этих витрин) и администрирования баз данных. И поскольку повседневная работа аналитика неизбежно связана с выгрузками данных и витринами, навык написания SQL запросов может стать фактором, из-за которого кандидат или получит преимущество, или будет отсеян. Печальная новость в том, что не каждый может рассчитывать получить его на студенческой скамье. Хорошая новость в том, что в изучении SQL нет ничего сложного, это быстро, а синтаксис запросов прост и понятен. Особенно это касается тех, кому уже доводилось сталкиваться с более сложными языками.
Обучение SQL запросам я разделил на три части. Эта часть посвящена базовому синтаксису, который используется в 80-90% случаев.
Практика
Введение в синтаксис будет рассмотрено на примере открытой базы данных, предназначенной специально для практики 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
Фильтрация по одному условию и одному значению:
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 > 4GROUP BY
GROUP BY — необязательный элемент запроса, с помощью которого можно задать агрегацию по нужному столбцу (например, если нужно узнать какое количество клиентов живет в каждом из городов).
При использовании GROUP BY обязательно:
- перечень столбцов, по которым делается разрез, был одинаковым внутри SELECT и внутри GROUP BY,
- агрегатные функции (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.
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 >= 5ORDER 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’ов и вложенных запросах.
Конструкция WHERE в SQL
Программирование на Python для начинающих
Бесплатный курс по Python даст Вам всю необходимую базу для начала программирования на языке Python. В курсе Вас ждёт множество уроков (больше 4-х часов видео), исходников и упражнений.
Чтобы получить Видеокурс,
заполните форму
| E-mail: | |
| Имя: | |
Другие курсы
Как создать профессиональный Интернет-магазин
После семинара:
— Вы будете знать, как создать Интернет-магазин.
— Вы получите бесплатный подарок с подробным описанием каждого шага.
— Вы сможете уже приступить к созданию Интернет-магазина.
Записаться
Другие курсы
Когда, совершив ошибку, не исправил её, это и называется совершить ошибку.
Конфуций
При выборке записей из таблицы практически всегда требуется задавать определённые условия, по которым мы определяем, какие записи нам нужны, а какие — нет. И именно эти условия можно задавать с помощью конструкции WHERE в SQL. Я уже знакомил Вас с ней, но в этой статье решил познакомить с WHERE немного поближе.
Начну с простого примера выборки с использованием конструции WHERE в SQL:
SELECT * FROM table WHERE count=5Вернутся записи, в которых поле «count» имеет значение 5. Теперь усложним запрос:
SELECT * FROM table WHERE count=5 AND id < 100Таким образом, вернутся записи, у которых поле «count» имеет значение 5 И поле «id» имеет значение меньше 100.
Разумеется, Вы можете использовать и другие логические операции. Их полный список:
- ! (отрицание)
- AND (И)
- OR (ИЛИ)
- XOR (ИСКЛЮЧАЮЩЕЕ ИЛИ, иногда ещё называют МОНТАЖНОЕ ИЛИ, но такое название встречается в основном в микропроцессорной литературе)
Пример с использованием нескольких логических операторов:
SELECT * FROM table WHERE !(id <= 120 AND (count=10 OR date > "10/11/1980"))Вот такой, на первый взгляд, сложный SQL-запрос. Постарайтесь в нём разобраться самостоятельно.
Также конструкция WHERE в SQL может содержать LIKE. LIKE позволяет определить, совпадает ли указанная строка с определённым шаблоном. Чтобы стало немного понятнее, приведу пример:
SELECT * FROM table WHERE text LIKE "%some text%"Данный SQL-запрос вернёт result_set, содержащий записи, в которых поле «text» имеет такой текст: «some text«.
az]some_«. Данный шаблон означает, что вначале идёт любой символ, но только НЕ «a» и НЕ «z«. Далее должна идти строка «some«, а после только один одиночный символ.
Знание и умение использования LIKE очень важно, поверьте моему опыту. Самый простой пример использования LIKE — это поиск по сайту. Ведь контент находится в базе данных, и необходимо вытащить только те записи, в которых содержится строка, заданная в строке поиска. И тут приходит на помощь LIKE. Именно так реализован поиск мною на этом сайте.
Вот, пожалуй, всё, что необходимо знать для успешной выборки записей с использованием конструкции WHERE в SQL.
Полный курс по PHP и MySQL: http://srs.myrusakov.ru/php
- Создано 22.01.2011 16:41:50
- Михаил Русаков
Предыдущая статьяСледующая статья
Копирование материалов разрешается только с указанием автора (Михаил Русаков) и индексируемой прямой ссылкой на сайт (http://myrusakov.
ru)!
Добавляйтесь ко мне в друзья ВКонтакте: http://vk.com/myrusakov.
Если Вы хотите дать оценку мне и моей работе, то напишите её в моей группе: http://vk.com/rusakovmy.
Если Вы не хотите пропустить новые материалы на сайте,
то Вы можете подписаться на обновления: Подписаться на обновления
Если у Вас остались какие-либо вопросы, либо у Вас есть желание высказаться по поводу этой статьи, то Вы можете оставить свой комментарий внизу страницы.
Порекомендуйте эту статью друзьям:
Если Вам понравился сайт, то разместите ссылку на него (у себя на сайте, на форуме, в контакте):
- Кнопка:
<a href=»https://myrusakov.ru» target=»_blank»><img src=»https://myrusakov.ru/images/button.gif» alt=»Как создать свой сайт» /></a>Она выглядит вот так:
- Текстовая ссылка:
<a href=»https://myrusakov.ru» target=»_blank»>Как создать свой сайт</a>Она выглядит вот так: Как создать свой сайт
- BB-код ссылки для форумов (например, можете поставить её в подписи):
[URL=»https://myrusakov.
ru»]Как создать свой сайт[/URL]
ru»]Как создать свой сайт[/URL]Использование предложений HAVING и WHERE в одном запросе — визуальные инструменты для работы с базами данных
Редактировать
Твиттер LinkedIn Фейсбук Электронная почта
- Статья
Применяется к: SQL Server Azure SQL База данных Azure SQL Управляемый экземпляр Azure Synapse Analytics Analytics Platform System (PDW)
В некоторых случаях может потребоваться исключить отдельные строки из групп (используя предложение WHERE) перед применением условия для групп в целом (используя предложение HAVING).
Предложение HAVING похоже на предложение WHERE, но применяется только к группам в целом (то есть к строкам в результирующем наборе, представляющим группы), тогда как предложение WHERE применяется к отдельным строкам.
Запрос может содержать как предложение WHERE, так и предложение HAVING. В этом случае:
Предложение WHERE сначала применяется к отдельным строкам в таблицах или табличным объектам на панели диаграммы. Группируются только те строки, которые соответствуют условиям в предложении WHERE.
Предложение HAVING затем применяется к строкам в результирующем наборе. В результатах запроса отображаются только те группы, которые соответствуют условиям HAVING. Вы можете применять предложение HAVING только к столбцам, которые также появляются в предложении GROUP BY или в агрегатной функции.
Например, представьте, что вы объединяете таблицы наименований и издателей , чтобы создать запрос, показывающий среднюю цену книги для набора издателей. Вы хотите увидеть среднюю цену только для определенного набора издателей — возможно, только для издателей в штате Калифорния. И даже в этом случае вы хотите видеть среднюю цену, только если она превышает 10 долларов США.
Вы можете установить первое условие, включив предложение WHERE, которое отбрасывает всех издателей, которые не находятся в Калифорнии, до расчета средних цен. Для второго условия требуется предложение HAVING, поскольку условие основано на результатах группировки и суммирования данных. Результирующий оператор SQL может выглядеть следующим образом:
SELECT titles.pub_id, AVG(titles.price) ОТ издателей INNER JOIN ON titles.pub_id = publishers.pub_id ГДЕ publishers.state = 'CA' СГРУППИРОВАТЬ ПО titles.pub_id ПРИ СРЕДНЕМ (цене) > 10;
В визуальных инструментах для работы с базами данных в SQL Server Management Studio можно создать предложения HAVING и WHERE на панели критериев . По умолчанию, если вы указываете условие поиска для столбца, оно становится частью предложения HAVING. Однако вы можете изменить условие на предложение WHERE.
Вы можете создать предложение WHERE и предложение HAVING с использованием одного и того же столбца.
Для этого необходимо дважды добавить столбец на панель Criteria , затем указать один экземпляр как часть предложения HAVING, а другой экземпляр — как часть предложения WHERE.
Чтобы указать условие WHERE в агрегированном запросе
Укажите группы для вашего запроса. Дополнительные сведения см. в разделе Групповые строки в результатах запроса.
Если его еще нет на панели Критерии , добавьте столбец, на котором вы хотите основывать условие WHERE.
Очистите столбец Output , если столбец данных не является частью предложения GROUP BY или включен в агрегатную функцию.
В столбце Фильтр укажите условие ГДЕ. Конструктор запросов и представлений добавляет условие в предложение HAVING оператора SQL.
Примечание
Запрос, показанный в примере для этой процедуры, объединяет две таблицы:
titlesиpublishers.На этом этапе запроса оператор SQL содержит предложение HAVING:
ВЫБЕРИТЕ titles.pub_id, AVG(titles.price) ОТ издателей INNER JOIN ON titles.pub_id = publishers.pub_id СГРУППИРОВАТЬ ПО titles.pub_id НАЛИЧИЕ publishers.state = 'CA'
В столбце Group By выберите Where из списка параметров группировки и сводки. Конструктор запросов и представлений удаляет условие из предложения HAVING в операторе SQL и добавляет его в предложение WHERE.
Вместо этого оператор SQL включает предложение WHERE:
ВЫБЕРИТЕ titles.pub_id, AVG(titles.price) ОТ издателей INNER JOIN ON titles.pub_id = publishers.pub_id ГДЕ publishers.state = 'CA' СГРУППИРОВАТЬ ПО titles.pub_id
См. также
- SELECT — HAVING
Следующие шаги
- Сортировка и группировка результатов запроса
- Суммировать результаты запроса
Обратная связь
Просмотреть все отзывы о странице
Разница между предложениями WHERE и HAVING в SQL
Цель этой статьи — подробно объяснить предложения WHERE, GROUP By и HAVING.
Кроме того, мы увидим разницу между WHERE и HAVING на примерах.
Программирование TSQL — это язык, используемый для запроса данных из базы данных SQL-сервера. TSQL является производным от ANSI SQL. В операторе TSQL используются различные предложения для написания полного запроса.
Что такое предложение WHERE?
Предложение WHERE используется для фильтрации данных в таблице на основе заданного условия. Ниже приведен синтаксис простого оператора TSQL с предложением WHERE.
В приведенном выше операторе TSQL для извлечения данных из таблицы используются разные предложения. Предложение WHERE появляется после выполнения оператора select в таблице. После предложения мы должны поставить условие, по которому нам нужно фильтровать данные. Это условие может быть текстовым или числовым условием.
Далее мы увидим разные варианты использования для разных условий фильтрации на примере. Вы можете использовать этот сценарий для практики с помощью SQL Server Management Studio (SSMS).
Мы создадим временную таблицу, используя приведенный ниже синтаксис, чтобы хранить образцы данных для различных примеров.
Примечание. Временные таблицы автоматически удаляются после окончания сеанса.
1 2 3 4 5 6 7 8 9 |
СОЗДАТЬ ТАБЛИЦУ #temp_employee ( id INT, empname VARCHAR(100), age INT,empaddress VARCHAR(200) ) 31 |
Эта временная таблица имеет столбцы ID, Name и Address с числовыми и символьными типами данных. Теперь нам нужно заполнить таблицу примерными данными. Используйте приведенный ниже скрипт для заполнения данных в таблице.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
ВСТАВИТЬ В #temp_employee ЗНАЧЕНИЯ (1, ‘XYZ’, 0003 ‘new road ‘)
INSERT INTO #temp_employee VALUES (2, ‘ABC’, 28, ‘old road’)
INSERT INTO #temp_employee VALUES ( 3, ‘aab’, 35, NULL) Вставка в #TEMP_EMPULEEE Значения (4, ‘CBC’, 25, ‘сектор 24’)
|
Поскольку теперь данные заполняются в таблице, давайте посмотрим на различные условия фильтрации в этой таблице.
Числовые фильтры с логическим оператором
Сценарий 1. Из таблицы нам нужно узнать, все ли идентификаторы сотрудников с идентификатором больше единицы
SELECT * FROM #temp_employee WHERE id > 1
|
В приведенном выше запросе предложение WHERE используется для фильтрации данных на основе столбца ID. Вывод вернет все значения, где идентификатор больше 1, как показано на снимке экрана ниже.
Применение предложения WHERE к текстовым значениям: Для применения предложения WHERE к текстовым значениям мы обычно используем оператор like.
В приведенном выше сценарии, если мы хотим найти сотрудников с буквой A в имени, мы можем написать приведенный ниже SQL-запрос, используя условие оператора «Мне нравится» для предложения WHERE.
SELECT * FROM #temp_employee WHERE empname LIKE ‘%A%’
|
% знак в приведенном выше запросе позволит использовать любую букву до и после A.
Ниже приведен результат запроса.
В качестве выходных данных будут сгенерированы сотрудники с буквой А в именах.
Что такое предложение HAVING?
Предложение HAVING используется в SQL для фильтрации сгруппированных данных. Чтобы понять HAVING, нам нужно понять предложение Group by.
Группа по статье
Он используется для группировки данных на основе одного или нескольких столбцов.
Например, в приведенном выше сценарии у нас есть столбец возраста, который может соответствовать сценарию группировки. В примере данных у нас может быть несколько сотрудников одного возраста. В одну группу могут входить сотрудники одного возраста. У нас есть два сотрудника в возрасте 25 лет и по одному сотруднику в возрасте 35 и 28 лет каждому. В этих данных у нас есть три группы.
Группировка по используется для агрегатных функций, таких как сумма, среднее, максимальное, минимальное и количество. Мы можем написать приведенный ниже запрос, чтобы получить количество людей в каждой возрастной группе.
1 2 3 4 5 6 |
SELECT age, Count(*) AS no_of_employees FROM #temp_employee ГРУППИРОВАТЬ ПО возрасту
|
Вывод группы по запросу возвращает 3 строки, как показано на снимке экрана ниже.
В выводе мы видим, что для возрастной группы 25 лет у нас есть два сотрудника; для других у нас есть только один сотрудник. Мы можем выбрать столбец для группировки и агрегировать его в соответствии с нашими требованиями.
Оператор GROUP BY также может использоваться с предложением WHERE. Мы можем сначала отфильтровать данные, а затем сгруппировать отфильтрованные данные. Давайте рассмотрим этот сценарий на примере.
Мы отфильтруем сотрудников с буквой b в имени, а затем сгруппируем их по возрасту.
1 2 3 4 5 6 7 | Выбрать возраст, COUNT (*) в качестве NO_OF_EMPOLYEES от #TEMP_EMPLOYEE , где EMPNAME, как и ‘%B%’ Группа по возрасту |
В приведенном выше запросе он сначала фильтрует записи таблицы с помощью предложения WHERE, а затем группирует их по возрасту.
Результат запроса показан на снимке экрана ниже.
Этот запрос возвращает те же 3 возрастные группы, но для возрастной группы 25 одна запись отфильтровывается и показывает счет как единицу. Одна запись, которая не содержит «B» в имени empname, фильтруется из-за предложения WHERE, а затем выполняется операция группировки, чтобы у нее была одна запись для 25 вместо двух.
Теперь мы знаем, как работает группировка, нам нужно применить фильтр к данным GROUP. Для фильтрации данных мы используем предложение HAVING. Синтаксис предложения HAVING следующий.
ВЫБРАТЬ select_list
ОТ имя_таблицы
СГРУППИРОВАТЬ ПО group_list
ИМЕЮЩИЕ условия;
В этом сценарии мы выберем возрастную группу, в которой работает более одного сотрудника. Ниже приведен запрос с использованием предложения HAVING.
1 2 3 4 5 6 7 |
ВЫБЕРИТЕ возраст, Count(*) AS no_of_employees FROM #temp_employee ГРУППИРОВАТЬ ПО ВОЗРАСТУ HAVING Count(*) 9010 3 1 3 |
В этом запросе HAVING отфильтровывает группы, которые меньше или равны 1.
На выходе мы получим только группы HAVING более одного сотрудника.
Поскольку мы отфильтровали строки, менее двух других возрастных групп 28,35 отфильтровываются, и мы получаем только одну возрастную группу, которая составляет 25 лет с двумя сотрудниками.
WHERE и HAVING могут использоваться в одном SQL-запросе, поскольку оба имеют разные функциональные возможности. Мы можем отфильтровать все агрегаты с помощью предложения HAVING.
SQL Engine следует порядку выполнения при выполнении запроса. Ниже приведен порядок выполнения SQL-запроса. Чтобы понять порядок WHERE и HAVING, нам также необходимо понять порядок выполнения запроса.
FROM: выполнение SQL-запроса начинается с предложения FROM. |
ГДЕ: его условие фильтрации и следующие шаги в порядке выполнения запроса. Отфильтровывает строки, которые не требуются. |
GROUP BY: После применения для фильтрации операции GROUP BY сгруппируйте оставшиеся данные. |
HAVING: после применения группы к оставшемуся набору данных предложение HAVING фильтрует, если какая-либо фильтрация требуется для данных GROUP, |
SELECT: после всех фильтров и группировки обработка возвращается к оператору select, где оцениваются операторы UNIQUE, DISTINCT и TOP, если они используются в SQL-запросе. |
ORDER BY: В конце концов, если используется упорядочение по, он сортирует оставшийся набор данных. Он применяется на основе различных условий и является самым внешним предложением, выполняемым механизмом SQL. |
Внутри он создает таблицу групп.С точки зрения производительности запроса предложение WHERE оказывает огромное влияние, поскольку оно отфильтровывает общие данные. ИМЕЮЩИЕ группы всех данных. Это дорогостоящая операция, поскольку процесс GROUP суммирует все данные и создает новую таблицу.
Теперь мы увидим разницу между предложениями HAVING и WHERE в различных точках.
ГДЕ | ИМЕЮЩИЙ |
Он используется для фильтрации данных непосредственно в таблице. | Он используется для фильтрации данных групп, созданных в таблице. |
Применяется как операция строки. | Применяется как операция столбца. |
Предложение WHERE извлекает только данные фильтра на основе условия. | Предложение HAVING извлекает все данные перед применением условия фильтрации. |
Вы не можете использовать агрегатные функции в условии WHERE. | Он используется для агрегатной функции, по которой группа выполнила. |
WHERE работает с инструкциями SELECT, UPDATE, DELETE. | HAVING работает только с операторами select. |
WHERE не требует предложения GROUP BY. Оставить комментарий
|
