Geek Notes
заметки/статьи/переводы на темы программирования, алгоритмов и etc.
Выборка данных SQL. Операторы SELECT, in, BETWEEN, LIKE, GROUP by и HAVING
Mar 15 th , 2015 9:23 pm
В данном конспекте рассмотрим как производится выборка данных из базы с помощью оператора SELECT. Использование DISTINCT. Операторы сравнения и логические операторы применяемые в предложении WHERE. А также операторы IN, BETWEEN и LIKE. Агрегатные функции типа COUNT(), AVG() и т.д. И рассмотрим применение операторов GROUP BY и HAVING.
И так простейшая выборка данных осуществляется оператором SELECT:
Так как предложение WHERE фильтрует строки до того как они попадут в группы, т.е. до применения GROUP BY, то его использовать не возможно, если нам надо задать условие для всей группы. Для этого есть оператор HAVING, который похож на WHERE только задает фильтр сразу к группе.
Допустим, есть следующая таблица:
При выполнении следующего запроса, сначала все строки группируются по одинаковым сочетаиням значений в полях Snum и Odate, а затем, к каждой группе применяет опертор MAX():
1 2 3 4
SELECT Snum, Odate, MAX( Amt ) FROM Orders GROUP BY Snum, Odate HAVING MAX( Amt ) > 3000.00 
Posted by fizikovnet Mar 15 th , 2015 9:23 pm sql
Агрегатные функции (Transact-SQL)
Агрегатная функция выполняет вычисление на наборе значений и возвращает одиночное значение. Агрегатные функции, за исключением COUNT(*) , не учитывают значения NULL. Агрегатные функции часто используются в выражении GROUP BY инструкции SELECT.
Все агрегатные функции являются детерминированными. Другими словами, агрегатные функции возвращают одну и ту же величину при каждом их вызове на одном и том же наборе входных значений. Дополнительные сведения о детерминированности функций см. в статье Детерминированные и недетерминированные функции. Предложение OVER может следовать за всеми агрегатными функциями, кроме STRING_AGG, GROUPING или GROUPING_ID.
Агрегатные функции можно использовать в качестве выражений только в следующих случаях.
- Список выбора инструкции SELECT (вложенный или внешний запрос).
- Предложение HAVING.
Transact-SQL предоставляет следующие агрегатные функции:
См. также
Обратная связь
Были ли сведения на этой странице полезными?
Обратная связь
Coming soon: Throughout 2024 we will be phasing out GitHub Issues as the feedback mechanism for content and replacing it with a new feedback system. For more information see: https://aka.ms/ContentUserFeedback.
Отправить и просмотреть отзыв по
Какие агрегатные функции можно использовать в операторе select
В статье об группировках мы обсудили, что при использовании оператора GROUP BY мы можем использовать агрегатные функции. Давайте поговорим о них поглубже ��.
Агрегатная функция – это функция, которая выполняет вычисление на наборе значений и возвращает одиночное значение.
MySQLSELECT [литералы, агрегатные_функции, поля_группировки] FROM имя_таблицы GROUP BY поля_группировки;Например, запрос с использованием агрегатной функции AVG может выглядеть так:
MySQLSELECT home_type, AVG(price) as avg_price FROM Rooms GROUP BY home_type
home_type avg_price Private room 89.4286 Entire home/apt 148.6667 Shared room 40 Оператор SELECT
Наиболее используемым, но и самым сложным оператором является оператор выборки SELECT. Он позволяет производить выборку данных из таблиц и преобразовывать к нужному виду полученные результаты.
20 дек. 2020 · 9 минуты на чтение
Результатом выполнения оператора SELECT является таблица. К этой таблице может быть снова применен оператор SELECT и т.д., то есть такие операторы могут быть вложены друг в друга. Вложенные операторы SELECT называют подзапросами.
Синтаксис оператора SELECT использует следующие основные предложения:
SELECT FROM [WHERE ] [GROUP BY ] [HAVING ] [ORDER BY ]Кратко пояснить смысл предложений оператора SELECT можно следующим образом:
- SELECT - выбрать данные из указанных столбцов и (если необходимо) выполнить перед выводом их преобразование в соответствии с указанными выражениями и (или) функциями
- FROM - из перечисленных таблиц, в которых расположены эти столбцы
- WHERE - где строки из указанных таблиц должны удовлетворять указанному перечню условий отбора строк
- GROUP BY - группируя по указанному перечню столбцов с тем, чтобы получить для каждой группы единственное значение
- HAVING - имея в результате лишь те группы, которые удовлетворяют указанному перечню условий отбора групп
- ORDER BY - сортируя по указанному перечню столбцов
Как видно из синтаксиса рассматриваемого оператора, обязательными являются только два первых предложения: SELECT и FROM .
Рассмотрим каждое предложение оператора SELECT .
Спонсор поста
База данных для примеров
Дальше будет много примеров и логично постоянно использовать одну и ту же БД. Так что на основании базы данных ниже будут продемонстрированы все примеры, не только в этой статье, но и в других.
Постановка задачи: пусть требуется разработать БД для предметной области «Поставка деталей»!
Требуется хранить следующую информацию:
- О поставщиках (P) pnum, pname
- О деталях (D) pnum, dname, dprice
- О поставках (PD) volume
Значения таблицы P
pnum pname 1 Иванов 2 Петров 3 Сидоров 4 Кузнецов Значения таблицы D
pnum dname dprice 1 Болт 10 2 Гайка 20 3 Винт 30 Значения таблицы PD
pnum dnum volume 1 1 100 1 2 100 1 3 300 2 1 150 1 2 250 3 1 1000 Предложение SELECT
После служебного слова SELECT перечисляются имена столбцов, значения которых будут входить в результат выполнения запроса.
Столбцы в результирующей таблице размещаются в том порядке, в котором они были указаны в предложении SELECT . Имена столбцов указываются через запятую.
Если имя столбца содержит пробелы или разделители, то его необходимо заключить в квадратные скобки.
При обработке данных из разных таблиц может возникнуть ситуация, когда столбцы разных таблиц имеют одинаковые имена. В этом случае имя столбца необходимо записывать как составное, указывая перед ним имя соответствующей таблицы: .
Предложение FROM
В предложении FROM перечисляются имена таблиц, которые содержат столбцы, указанные после слова SELECT .
Пример 1.
Вывести список наименований деталей из таблицы D (“Детали”).SELECT dname FROM DПример 2.
Получить всю информацию из таблицы D (“Детали”).Получить результат можно двумя способами:
Явным указанием всех столбцов таблицы.
SELECT dnum, dname, dprice FROM DSELECT * FROM DВ результате и первого и второго запроса получаем новую таблицу, представляющую собой полную копию таблицы D (“Детали”).
Можно осуществить выбор отдельных столбцов и их перестановку.
Пример 3.
Получить информацию о наименовании и номере поставщика.SELECT pname, pnum FROM PПример 4.
Определить номера поставщиков, которые поставляют детали в настоящее время (то есть номера тех поставщиков, которые присутствуют в таблице PD (“Поставки”)).SELECT pnum FROM PD
pnum 1 1 1 2 2 3
Дополнительно о SELECT
Теперь, когда мы научились делать простые запросы с SELECT и FROM , можно ненадолго снова вернуться к SELECT .
Агрегатные функции
В операторе SELECT можно использовать агрегатные функции, которые дают единственное значение для целой группы строк в таблице.
Агрегатная функция записывается в следующем виде: ()
Пользователю доступны следующие агрегатные функции:
- SUM ‑ вычисляет сумму множества значений указанного столбца;
- COUNT ‑ вычисляет количество значений указанного столбца;
- MIN / MAX ‑ определяет минимальное/максимальное значение в указанном столбце;
- AVG ‑ вычисляет среднее арифметическое значение множества значений столбца;
- FIRST / LAST ‑ определяет первое/последнее значение в указанном столбце.
Пример 5.
Определить общий объем поставляемых деталей.SELECT SUM(volume) FROM PD
Expr1000 2000 Вычисляемые столбцы
Столбцы результирующей таблицы, которых не существовало в исходных таблицах, называются вычисляемыми. Таким столбцам СУБД присваивает системные имена, что не всегда является удобным.
При вычислении результатов любой агрегатной функции СУБД сначала исключает все NULL -значения, после чего требуемая операция применяется к оставшимся значениям.
Для функции COUNT возможен особый вариант использования - COUNT(*) . Его назначение состоит в подсчете всех строк в результирующей таблице, включая NULL -значения.
Следует запомнить, что агрегатные функции нельзя вкладывать друг в друга. Такая конструкция работать не будет: MAX(SUM(VOLUME))
Переименование столбца
Язык SQL позволяет задавать новые имена столбцам результирующей таблицы, для чего используется операция AS . Переименование также используют для изменения сложных имен столбцов таблицы.
Например, присвоить новое имя вычисляемому столбцу в предыдущем примере позволит выполнение следующего запроса.
SELECT SUM(volume) AS SUM FROM PDПример 6.
Определить количество поставщиков, которые поставляют детали в настоящее время.SELECT COUNT(pnum) AS COUNT FROM PDНесмотря на то, что реальное число поставщиков деталей в таблице PD равно 3, СУБД возвращает число 6. Такой результат объясняется тем, что СУБД подсчитывает все строки в таблице PD, не обращая внимание на то, что в строках есть одинаковые значения.
Операция DISTINCT
Если до применения агрегатной функции необходимо исключить дублирующиеся значения, следует перед именем столбца указать ключевое слово DISTINCT .
SELECT COUNT(DISTINCT pnum) AS COUNT FROM PDDISTINCT можно задать только один раз для одного предложения SELECT.
Противоположностью DISTINCT является операция ALL . Она имеет противоположное действие «показать все строки таблицы» и предполагается по умолчанию.
Операция TOP
Итоговый набор записей, получаемых после выполнения запроса можно ограничить первыми N строками или первыми N процентами от общего количества строк результата.
Для этого используется операция TOP , которая записывается в предложении SELECT следующим образом: SELECT TOP N [PERCENT]
Пример 7.
Определить номера первых двух деталей таблицы D.SELECT TOP 2 dnum FROM DСтандарт SQL требует, чтобы при сортировке NULL -значения трактовались либо как превосходящие, либо как уступающие по сравнению со всеми остальными значениями. Так как конкретный вариант стандартом не оговаривается, то в зависимости от используемой СУБД при сортировке NULL -значения следуют до или после остальных значений. В MS SQL Server NULL -значения считаются уступающими по сравнению с остальными значениями.
Рандомный блок
Предложение WHERE
После служебного слова WHERE указываются условия выбора строк, помещаемых в результирующую таблицу. Существуют различные типы условий выбора:
- Сравнение значений атрибутов со скалярными выражениями, другими атрибутами или результатами вычисления выражений.
- Проверка значения на принадлежность множеству.
- Проверка значения на принадлежность диапазону.
- Проверка строкового значения на соответствие шаблону.
- Проверка на наличие null -значения.
Сравнение
В языке SQL используются традиционные операции сравнения = , <> , < , , >= .
В качестве условия в предложении WHERE можно использовать сложные логические выражения, использующие атрибуты таблиц, константы, скобки, операции AND , OR , отрицание NOT .
Пример 8.
Определить номера деталей, поставляемых поставщиком с номером 2.SELECT dnum FROM PD WHERE pnum = 2Пример 9.
Получить информацию о поставщиках Иванов и Петров.SELECT * FROM P WHERE pname='Иванов' OR pname='Петров'Строковые значения атрибутов заключаются в апострофы.
Проверка на принадлежность множеству
Операция IN проверяет, принадлежит ли значение атрибута заданному множеству.
Пример 10.
Получить информацию о поставщиках ‘Иванов’ и ‘Петров’.SELECT * FROM P WHERE pname IN ('Иванов','Петров')Пример 11.
Получить информацию о деталях с номерами 1 и 2.SELECT * FROM D WHERE dnum IN (1, 2)Проверка на принадлежность диапазону
Операция BETWEEN определяет минимальную и максимальную границу диапазона, в которое должно попадать значение атрибута. Обе границы считаются принадлежащими диапазону.
Пример 12.
Определить номера деталей, с ценой от 10 до 20 рублей.SELECT dnum FROM D WHERE dprice BETWEEN 10 AND 20Пример 13.
Вывести наименования поставщиков, начинающихся с букв от ‘К’ по ‘П’.SELECT pname FROM P WHERE pname BETWEEN 'К' AND 'Р'Сравнение символов
Буква Р в условии запроса объясняется тем, что строки сравниваются посимвольно. Для каждого символа при этом определяется код. Для нашего случая справедливо условие: П < Петров < Р
Проверка строкового значения на соответствие шаблону
Операция LIKE используется для поиска подстрок. Значения столбца, указываемого перед служебным словом LIKE сравниваются с задаваемым после него шаблоном. Форматы шаблонов различаются в конкретных СУБД.
Для СУБД MS SQL Server:
- Символ % заменяет любое количество любых символов.
- Символ _ заменяет один любой символ.
- [] ‑ вместо символа строки может быть подставлен один любой символ из множества возможных, указанных в ограничителях.
- [^] ‑ вместо символа строки может быть подставлен любой из символов кроме символов из множества, указанного в ограничителях.
Множество символов в квадратных скобках можно указывать через запятую, либо в виде диапазона.
Пример 14.
Вывести фамилии поставщиков, начинающихся с буквы И .SELECT pname FROM P WHERE pname LIKE 'И%'Пример 15.
Вывести фамилии поставщиков, начинающихся с букв от К по П .SELECT pname FROM P WHERE dname LIKE '[К-П]%'Проверка на наличие null -значения
Операции IS NULL и IS NOT NULL используются для сравнения значения атрибута со значением NULL .
Пример 16.
Определить наименования деталей, для которых не указана цена.SELECT dname FROM D WHERE dprice IS NULLПример 17.
Определить номера поставщиков, для которых указано наименование.SELECT pnum FROM P WHERE pname IS NOT NULLПредложение GROUP BY
Использование GROUP BY позволяет разбивать таблицу на логические группы и применять агрегатные функции к каждой из этих групп. В результате получим единственное значение для каждой группы.
Обычно предложение GROUP BY применяют, если формулировка задачи содержит фразу «для каждого…», «каждому..» и т.п.
Пример 18.
Определить суммарный объем деталей, поставляемых каждым поставщиком.SELECT pnum, SUM(VOLUME) AS SUM FROM PD GROUP BY pnum
pnum sum 1 600 2 400 3 1000 Выполнение запроса можно описать следующим образом: СУБД разбивает таблицу PD на три группы, в каждую из групп помещаются строки с одинаковым значением номера поставщика. Затем к каждой из полученных групп применяется агрегатная функция SUM , что дает единственное итоговое значение для каждой группы.
Рассмотрим два похожих примера. В примере 19 определяется минимальный объем поставки каждого поставщика. В примере 20 определяется объем минимальной поставки среди всех поставщиков.
Пример 19:
SELECT pnum, MIN(VOLUME) AS MIN FROM PD GROUP BY pnumПример 20:
SELECT MIN(VOLUME) AS MIN FROM PРезультаты запросов представлены в следующей таблице:
pnum min max 1 100 100 2 150 3 1000 Следует обратить внимание, что в первом примере мы можем вывести номера поставщиков, соответствующие объемам поставок, а во втором примере – не можем.
Все имена столбцов, перечисленные после ключевого слова SELECT должны присутствовать и в предложении GROUP BY , за исключением случая, когда имя столбца является аргументом агрегатной функции.
Однако в предложении GROUP BY могут быть указаны имена столбцов, не перечисленные в списке вывода после ключевого слова SELECT .
Если предложение GROUP BY расположено после предложения WHERE , то группы создаются из строк, выбранных после применения WHERE .
Пример 21.
Для каждой из деталей с номерами 1 и 2 определить количество поставщиков, которые их поставляют, а также суммарный объем поставок деталей.SELECT dnum, COUNT(pnum) AS COUNT, SUM(volume) AS SUM FROM PD WHERE dnum=1 OR dnum=2 GROUP BY dnum
dnum COUNT SUM 1 3 1250 2 2 450 Чтобы организовать вложенные группировки, после GROUP BY следует указать несколько группирующих столбцов через запятую. В этом случае реальный подсчет данных будет происходить по той группе, которая указана последней.
Предложение HAVING
Предложение HAVING определяет критерий, согласно которому, определенные группы, сформированные с помощью предложения GROUP BY , исключаются из результирующей таблицы.
Выполнение предложения HAVING сходно с выполнением предложения WHERE . Но предложение WHERE исключает строки до того, как выполняется группировка, а предложение HAVING - после. Поэтому предложение HAVING может содержать агрегатные функции, а предложение WHERE - не может.
Пример 22.
Определить номера поставщиков, поставляющих в сумме более 500 деталей.SELECT pnum, SUM(volume) AS SUM FROM PD GROUP BY pnum HAVING SUM(volume) > 500
pnum SUM 1 600 3 1000 Пример 23.
Определить номера поставщиков, которые поставляют только одну деталь.SELECT pnum, COUNT(dnum) AS COUNT FROM PD GROUP BY pnum HAVING COUNT(dnum) = 1Предложение ORDER BY
При выполнении запроса СУБД возвращает строки в случайном порядке. Предложение ORDER BY позволяет упорядочить выходные данные запроса в соответствии со значениями одного или нескольких выбранных столбцов.
Можно задать возрастающий - ASC (от слова Ascend) или убывающий - DESC (от слова Descend) порядок сортировки. По умолчанию принят возрастающий порядок сортировки.
Пример 24.
Отсортировать таблицу PD в порядке возрастания номеров поставщиков, а строки с одинаковыми значениями pnum отсортировать в порядке убывания объема поставок.SELECT pnum, volume, dnum FROM PD ORDER BY pnum ASC, volume DESC
pnum volume dnum 1 300 3 1 200 2 1 100 1 2 250 2 2 150 1 3 1000 1 Операцию TOP удобно применять после сортировки результирующего набора с помощью предложения ORDER BY .
Пример 25.
Определить номера первых двух деталей с наименьшей стоимостью.SELECT TOP 2 dnum FROM D ORDER BY dprice ASCСледует отметить, что если в таблице D будут две детали без указания цены, то именно их и отобразит предыдущий запрос. Поэтому при наличии NULL -значений их необходимо исключать с помощью предложения WHERE .
SELECT TOP 2 dnum FROM D WHERE dprice IS NOT NULL ORDER BY dprice ASCЗаключение
В статье было рассмотрен оператор выборки SELECT . Знание оператора SELECT является ключевым при написании любых SQL-запросов. Он позволяет производить выборку данных из таблиц и преобразовывать результаты в соответствии с нужными выражениями и функциями.
Результатом выполнения оператора SELECT является таблица, которую можно вложить в другой оператор SELECT в качестве подзапроса.
Синтаксис оператора SELECT содержит несколько предложений, из которых обязательными являются только SELECT и FROM . Остальные предложения, такие как WHERE , GROUP BY , HAVING и ORDER BY , могут использоваться по желанию для уточнения выборки данных.
