Содержание

Оператор SELECT

Backend Java Developer
Backend Java Developer

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

Результатом выполнения оператора 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
pnumpname
1Иванов
2Петров
3Сидоров
4Кузнецов
Значения таблицы D
pnumdnamedprice
1Болт10
2Гайка20
3Винт30
Значение таблицы PD
pnumdnumvolume
11100
12100
13300
21150
12250
311000

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

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

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

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

В предложении FROM перечисляются имена таблиц, которые содержат столбцы, указанные после слова SELECT.

Пример 1.
Вывести список наименований деталей из таблицы D (“Детали”).
SELECT dname
FROM D

Пример 2.
Получить всю информацию из таблицы D (“Детали”).

Получить результат можно двумя способами:

  • Явным указанием всех столбцов таблицы.
    SELECT dnum, dname, dprice
    FROM D
    
  • Полный список столбцов таблицы заменяет символ *.
    SELECT *
    FROM D
    

В результате и первого и второго запроса получаем новую таблицу, представляющую собой полную копию таблицы D (“Детали”).

Можно осуществить выбор отдельных столбцов и их перестановку.

Пример 3.
Получить информацию о наименовании и номере поставщика.
SELECT pname, pnum
FROM P

Пример 4.
Определить номера поставщиков, которые поставляют детали в настоящее время (то есть номера тех поставщиков, которые присутствуют в таблице PD (“Поставки”)).

SELECT pnum
FROM PD
pnum
1
1
1
2
2
3

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

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

Агрегатная функция записывается в следующем виде: <имя функции>(<имя столбца>)

Пользователю доступны следующие агрегатные функции:

  • SUM ‑ вычисляет сумму множества значений указанного столбца;
  • COUNT ‑ вычисляет количество значений указанного столбца;
  • MIN / MAX ‑ определяет минимальное/максимальное значение в указанном столбце;
  • AVG ‑ вычисляет среднее арифметическое значение множества значений столбца;
  • FIRST / LAST ‑ определяет первое/последнее значение в указанном столбце.

Пример 15.
Определить общий объем поставляемых деталей.

SELECT SUM(volume)
FROM PD
Expr1000
2000
Вычисляемые столбцы
Столбцы результирующей таблицы, которых не существовало в исходных таблицах, называются вычисляемыми. Таким столбцам СУБД присваивает системные имена, что не всегда является удобным.

При вычислении результатов любой агрегатной функции СУБД сначала исключает все NULL-значения, после чего требуемая операция применяется к оставшимся значениям.

Для функции COUNT возможен особый вариант использования - COUNT(*). Его назначение состоит в подсчете всех строк в результирующей таблице, включая NULL-значения.

Следует запомнить, что агрегатные функции нельзя вкладывать друг в друга. Такая конструкция работать не будет: MAX(SUM(VOLUME))

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

Например, присвоить новое имя вычисляемому столбцу в предыдущем примере позволит выполнение следующего запроса.

SELECT SUM(volume) AS SUM
FROM PD
Sum
2000

Пример 16.
Определить количество поставщиков, которые поставляют детали в настоящее время.

SELECT COUNT(pnum)AS COUNT
FROM PD
Count
6

Несмотря на то, что реальное число поставщиков деталей в таблице PD равно 3, СУБД возвращает число 6. Такой результат объясняется тем, что СУБД подсчитывает все строки в таблице PD, не обращая внимание на то, что в строках есть одинаковые значения.

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

SELECT COUNT(DISTINCT pnum) AS COUNT
FROM PD
Count
3

DISTINCT можно задать только один раз для одного предложения SELECT.

Противоположностью DISTINCT является операция ALL. Она имеет противоположное действие «показать все строки таблицы» и предполагается по умолчанию.

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

Для этого используется операция TOP, которая записывается в предложении SELECT следующим образом: SELECT TOP N [PERCENT] <список столбцов>

Пример 23.
Определить номера первых двух деталей таблицы D.
SELECT TOP 2 dnum
FROM D

Стандарт SQL требует, чтобы при сортировке NULL-значения трактовались либо как превосходящие, либо как уступающие по сравнению со всеми остальными значениями. Так как конкретный вариант стандартом не оговаривается, то в зависимости от используемой СУБД при сортировке NULL-значения следуют до или после остальных значений. В MS SQL Server NULL-значения считаются уступающими по сравнению с остальными значениями.

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

Типы условий выбора:
  • Сравнение значений атрибутов со скалярными выражениями, другими атрибутами или результатами вычисления выражений.
  • Проверка значения на принадлежность множеству.
  • Проверка значения на принадлежность диапазону.
  • Проверка строкового значения на соответствие шаблону.
  • Проверка на наличие null-значения.

В языке SQL используются традиционные операции сравнения =,<>,<,<=,>,>=.

В качестве условия в предложении WHERE можно использовать сложные логические выражения, использующие атрибуты таблиц, константы, скобки, операции AND, OR, отрицание NOT.

Пример 5.
Определить номера деталей, поставляемых поставщиком с номером 2.

SELECT dnum
FROM PD
WHERE pnum = 2

Пример 6.
Получить информацию о поставщиках Иванов и Петров.

SELECT *
FROM P
WHERE pname='Иванов' OR pname='Петров'
Строковые значения атрибутов заключаются в апострофы.

Операция IN проверяет, принадлежит ли значение атрибута заданному множеству.

Пример 7.
Получить информацию о поставщиках ‘Иванов’ и ‘Петров’.

SELECT *
FROM P
WHERE pname IN ('Иванов','Петров')

Пример 8.
Получить информацию о деталях с номерами 1 и 2.

SELECT *
FROM D
WHERE dnum IN (1, 2)

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

Пример 9.
Определить номера деталей, с ценой от 10 до 20 рублей.

SELECT dnum
FROM D
WHERE dprice BETWEEN 10 AND 20

Пример 10.
Вывести наименования поставщиков, начинающихся с букв от ‘К’ по ‘П’.

SELECT pname
FROM P
WHERE pname BETWEEN 'К' AND 'Р'
Сравнение символов
Буква ‘Р’ в условии запроса объясняется тем, что строки сравниваются посимвольно. Для каждого символа при этом определяется код. Для нашего случая справедливо условие: ‘П’<‘Петров’<‘Р’

Операция LIKE используется для поиска подстрок. Значения столбца, указываемого перед служебным словом LIKE сравниваются с задаваемым после него шаблоном. Форматы шаблонов различаются в конкретных СУБД.

Для СУБД MS SQL Server:

  • Символ % заменяет любое количество любых символов.
  • Символ _ заменяет один любой символ.
  • [<множество символов>] ‑ вместо символа строки может быть подставлен один любой символ из множества возможных, указанных в ограничителях.
  • [^<множество символов>] ‑ вместо символа строки может быть подставлен любой из символов кроме символов из множества, указанного в ограничителях.

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

Пример 11.
Вывести фамилии поставщиков, начинающихся с буквы ‘И’.

SELECT pname
FROM P
WHERE pname LIKE 'И%'

Пример 12.
Вывести фамилии поставщиков, начинающихся с букв от ‘К’ по ‘П’.

SELECT pname
FROM P
WHERE dname LIKE '[К-П]%'

Операции IS NULL и IS NOT NULL используются для сравнения значения атрибута со значением NULL.

Пример 13.
Определить наименования деталей, для которых не указана цена.

SELECT dname
FROM D
WHERE dprice IS NULL

Пример 14.
Определить номера поставщиков, для которых указано наименование.

SELECT pnum
FROM P
WHERE pname IS NOT NULL

Использование GROUP BY позволяет разбивать таблицу на логические группы и применять агрегатные функции к каждой из этих групп. В результате получим единственное значение для каждой группы.

Обычно предложение GROUP BY применяют, если формулировка задачи содержит фразу «для каждого…», «каждому..» и т.п.

Пример 18.
Определить суммарный объем деталей, поставляемых каждым поставщиком.

SELECT pnum, SUM(VOLUME) AS SUM
FROM PD
GROUP BY pnum
pnumsum
1600
2400
31000

Выполнение запроса можно описать следующим образом: СУБД разбивает таблицу PD на три группы, в каждую из групп помещаются строки с одинаковым значением номера поставщика. Затем к каждой из полученных групп применяется агрегатная функция SUM, что дает единственное итоговое значение для каждой группы.

Рассмотрим два похожих примера.

В примере 1 определяется минимальный объем поставки каждого поставщика. В примере 2 определяется объем минимальной поставки среди всех поставщиков.

SELECT pnum, MIN(VOLUME) AS MIN
FROM PD
GROUP BY pnum
SELECT MIN(VOLUME) AS MIN
FROM P

Результаты запросов представлены в следующей таблице:

pnumminmax
1100100
2150
31000
Следует обратить внимание, что в первом примере мы можем вывести номера поставщиков, соответствующие объемам поставок, а во втором примере – не можем.

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

Однако в предложении GROUP BY могут быть указаны имена столбцов, не перечисленные в списке вывода после ключевого слова SELECT.

Если предложение GROUP BY расположено после предложения WHERE, то группы создаются из строк, выбранных после применения WHERE.

Пример 19.
Для каждой из деталей с номерами 1 и 2 определить количество поставщиков, которые их поставляют, а также суммарный объем поставок деталей.

SELECT dnum, COUNT(pnum) AS COUNT, SUM(volume) AS SUM
FROM PD
WHERE dnum=1 OR dnum=2
GROUP BY dnum

Результат запроса:

dnumCOUNTSUM
131250
22450

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

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

Выполнение предложения HAVING сходно с выполнением предложения WHERE. Но предложение WHERE исключает строки до того, как выполняется группировка, а предложение HAVING - после. Поэтому предложение HAVING может содержать агрегатные функции, а предложение WHERE - не может.

Пример 20.
Определить номера поставщиков, поставляющих в сумме более 500 деталей.

SELECT pnum, SUM(volume) AS SUM
FROM PD
GROUP BY pnum
HAVING SUM(volume)>500
pnumSUM
1600
31000

Пример 21.
Определить номера поставщиков, которые поставляют только одну деталь.

SELECT pnum, COUNT(dnum) AS COUNT
FROM PD
GROUP BY pnum
HAVING COUNT(dnum)=1
pnumSUM
31

При выполнении запроса СУБД возвращает строки в случайном порядке. Предложение ORDER BY позволяет упорядочить выходные данные запроса в соответствии со значениями одного или нескольких выбранных столбцов.

Можно задать возрастающий - ASC (от слова Ascend) или убывающий - DESC (от слова Descend) порядок сортировки. По умолчанию принят возрастающий порядок сортировки.

Пример 22.
Отсортировать таблицу PD в порядке возрастания номеров поставщиков, а строки с одинаковыми значениями pnum отсортировать в порядке убывания объема поставок.

SELECT pnum, volume, dnum
FROM PD
ORDER BY pnum ASC, volume DESC
pnumvolumednum
13003
12002
11001
22502
21501
310001
Операцию TOP удобно применять после сортировки результирующего набора с помощью предложения ORDER BY.
Пример 24.
Определить номера первых двух деталей с наименьшей стоимостью.
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

Комментарии