Какие опции могут быть использованы в операторах выборки данных

SQL SELECT

Команда SELECT (SQL запрос) производит выборку данных из таблиц по запросу. Язык SQL допускает три типа синтаксических конструкций, начинающихся с ключевого слова SELECT:

1. оператор выборки (select statement)
2. спецификация курсора (cursor specification)
3. подзапрос (subquery).

Синтаксис команды SELECT в MySQL

Основные ключевые слова и параметры команды SELECT в MySQL

• DISTINCT — возвращает только одно значение для каждого набора одинаковых выбранных значений столбца
• ALL — возвращает все выбранные строки, включая все повторяющиеся значения столбцов (принимается по умолчанию)
• * — выбирает все столбцы из всех таблиц или представлений, перечисленных после оператора FROM
• schema — идентификатор полномочий, обычно совпадающий с именем некоторого пользователя
• table.* view.* — выбирает все столбцы из указанной таблицы, представления
• Expr — извлекает из таблицы (представления) некоторое определяемое выражение
• table view — имя таблицы(представления), из которой происходит выборка данных.
• subquery — подзапрос, который сервер обрабатывает тем же самым способом как представление.
• WHERE — ограничивает множество строк выборкой тех записей, для которых условие является истинным; если это предложение опускается, сервер возвращает все строки из таблиц.
• GROUP BY — группирует выбранные строки по группам строк с одинаковым значением указанных полей и возвращает одиночную строку итоговой информации для каждой группы.
• HAVING — ограничивает выбираемые группы строк такими группами, для которых определяемое условие является истинным; если это предложение опускается, сервер возвращает строки всех групп.
• UNION UNION ALL INTERSECT MINUS — объединяет строки, возвращенные двумя утверждениями SELECT с использованием операции пересечения множеств; для ссылки на столбец вводится псевдоним для его обозначения; предложение FOR UPDATE не может использоваться с этими операторами
• ORDER BY — упорядочивает строки, возвращенные запросом.
• Expr— значение выражения определяет правило упорядочивания строк.
• ASC DESC — определяет порядок вывода данных (по возрастанию или по убыванию); значением по умолчанию является ASC.
• FOR UPDATE — блокирует выбранные строки.
• OF — блокирует выбираемые строки для специфической таблицы в объединении.
• NOWAIT — возвращает управление пользователю, если команда SELECT пытается блокировать строку, которая уже блокирована другим пользователем; если это предложение опускается, сервер ждет, пока строка не станет доступной и только тогда возвращает результаты команды SELECT.

Синтаксис команды SELECT в Oracle

Основные ключевые слова и параметры команды SELECT в Oracle

• DISTINCT — возвращает только одно значение для каждого набора одинаковых выбранных значений столбца.
• ALL — возвращает все выбранные строки в Oracle, включая все повторяющиеся значения столбцов (принимается по умолчанию).
• * — выбирает все столбцы из всех таблиц или представлений, перечисленных после раздела FROM.
• schema — идентификатор полномочий, обычно совпадающий с именем некоторого пользователя.
• table.* view.* — выбирает все столбцы из указанной таблицы Oracle, представления.
• Expr — извлекает из таблицы (представления) некоторое определяемое выражение.
• table view — имя таблицы(представления), из которой происходит выборка данных.
• c_alias – алиасное имя (псевдоним) извлекаемого столбца, выражения.
• t_alias – алиасное имя (псевдоним) таблицы Oracle.
• subquery — подзапрос, который сервер обрабатывает тем же самым способом как представление.
• WHERE — ограничивает множество строк выборкой тех записей, для которых условие является истинным; если это предложение опускается, сервер возвращает все строки из таблиц Oracle.
• GROUP BY — группирует выбранные строки по группам строк с одинаковым значением указанных полей и возвращает одиночную строку итоговой информации для каждой группы.
• HAVING — ограничивает выбираемые группы строк такими группами, для которых определяемое условие является истинным; если это предложение опускается, сервер возвращает строки всех групп.
• UNION [ALL] INTERSECT MINUS — объединяет строки, возвращенные двумя утверждениями SELECT с использованием операции пересечения множеств; для ссылки на столбец вводится псевдоним для его обозначения. Предложение FOR UPDATE не может использоваться с этими операторами.
• ORDER BY — упорядочивает строки, возвращенные запросом: в Expr — указывается значение выражения, которое определяет правило упорядочивания строк по возрастанию ASC или убыванию DESC. Значением по умолчанию является ASC.
• PARTITION — в отличие от ORDER BY позволяет частично упорядочивать набор данных.
• FOR UPDATE — блокирует выбранные строки.
• NOWAIT — возвращает управление пользователю, если команда SELECT пытается блокировать строку, которая уже блокирована другим пользователем; если это предложение опускается, сервер ждет, пока строка не станет доступной и только тогда возвращает результаты команды SELECT.

Описание команды SELECT

Основой всех синтаксических конструкций, начинающихся с ключевого слова SELECT, является синтаксическая конструкция “табличное выражение”. Семантика табличного выражения состоит в том, что на основе последовательного применения разделов FROM, WHERE, GROUP BY и HAVING из заданных в разделе FROM таблиц строится некоторая новая результирующая таблица, порядок следования строк которой не определен и среди строк которой могут находиться дубликаты (т.е. в общем случае таблица-результат табличного выражения является мультимножеством строк).

Наиболее общей является конструкция “спецификация курсора”.

Курсор — это понятие языка SQL, позволяющее с помощью набора специальных операторов получить построчный доступ к результату запроса к БД. К табличным выражениям, участвующим в спецификации курсора, не предъявляются какие- либо ограничения. При определении спецификации курсора используются три дополнительных конструкции: спецификация запроса, выражение запросов и раздел ORDER BY.

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

Выражение запросов — это выражение, строящееся по указанным синтаксическим правилам на основе спецификаций запросов. Единственной операцией, которую разрешается использовать в выражениях запросов, является операция UNION (объединение таблиц) с возможной разновидностью UNION ALL.

Оператор выборки — это отдельный оператор языка SQL, позволяющий получить результат запроса в прикладной программе без привлечения курсора. Поэтому оператор выборки имеет синтаксис, отличающийся от синтаксиса спецификации курсора, и при его выполнении возникают ограничения на результат табличного выражения. Фактически, и то, и другое диктуется спецификой оператора выборки как одиночного оператора SQL: при его выполнении результат должен быть помещен в переменные прикладной программы. Поэтому в операторе появляется раздел INTO, содержащий список переменных прикладной программы, и возникает то ограничение, что результирующая таблица должна содержать не более одной строки. В диалекте SQL СУБД Oracle поддерживается расширенный вариант оператора выборки, результатом которого не обязательно является таблица из одной строки. Такое расширение не поддерживается ни в SQL/89, ни в SQL/92.

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

Кстати, данную статью Вы можете найти в интернете по запросам:

Команда SELECT, Синтаксис команды SELECT, Описание команды SELECT.

SQL-Ex blog

Использование предложения TOP в операторе SELECT

Добавил Sergey Moiseenko on Суббота, 22 июля. 2023

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

Синтаксис предложения TOP

Синтаксис предложения TOP простой:

TOP () [PERCENT] 
[ WITH TIES ]

expression — выражение, которое определяет число возвращаемых строк. Выражение оценивается как float, если используется опция PERCENT, в противном случае — bigint.

PERCENT — когда указано, возвращается процентное отношение строк, а не их конкретное число.

WITH TIES – это необязательный параметр, но при использовании будут возвращаться две или более строк, которые имеют то же самое значение, что и последнее значение в упорядоченном ограниченном наборе. Предложение ORDER BY должно быть включено, если используется опция WITH TIES. Использование этой опции может означать, что возвращаться может больше строк, чем дает вычисленное выражение expression.

Здесь мы будем обсуждать использование предложения TOP только в операторе SELECT. Однако предложение TOP также может использоваться в операторах UPDATE, INSERT, DELETE и MERGE, и в этих случаях оно будет контролировать число модифицируемых или удаляемых строк. Обратитесь к документации Microsoft за получением полной информации о синтаксисе и использовании предложения TOP.

Данные для примеров

Тестовые данные для всех примеров этой статьи будут базироваться на таблице dbo.Inventory, которая создается в базе данных tempdb. Эта таблица создается и заполняется данными с помощью кода в листинге 1.

-- Создаем тестовые данные 
USE tempdb; 
GO 
CREATE TABLE dbo.Inventory( 
ID int IDENTITY(1,1) NOT NULL, 
ProductName varchar(50) NULL, 
Quantity int NULL, 
Price money NULL, 
PriceChangeDate datetime NULL 
) 
GO 
INSERT INTO dbo.Inventory VALUES 
('Hammer',10,12.99,SYSDATETIME()), 
('8 mm socket',5,3.99,SYSDATETIME()), 
('7 mm socket',5,3.99,SYSDATETIME()), 
('9 mm socket',5,3.99,SYSDATETIME()), 
('6 mm socket',5,3.59,SYSDATETIME()), 
('Flat head #3',15,3.99,SYSDATETIME()), 
('Flat head #1', 15,2.99,SYSDATETIME()), 
('Flat head #2',15,3.59,SYSDATETIME()); 
GO

Листинг 1. Создание и заполнение данными таблицы dbo.Inventory

Таблица dbo.Inventory содержит различные типы инструментов. В следующем разделе эта таблица будет использоваться для демонстрации работы предложения TOP в операторе SELECT. Если вы хотите выполнять примеры из этой статьи, то можете использовать код в листинге 1 для создания таблицы dbo.Inventory в базе данных tempdb на одном из ваших тестовых экземпляров SQL Server.

Простое предложение TOP

Наиболее простой и обычный способ использования предложения TOP заключается в выборке конкретного числа строк. Код в листинге 2 показывает использование простого предложения TOP для получения 5 строк из таблицы dbo.Inventory.

USE tempdb; 
GO 
SELECT TOP (5) * 
FROM dbo.Inventory; 
GO

Листинг 2. Возврат 5 несортированных строк с помощью предложения TOP

При выполнении кода в листинге 2 получен результат, представленный в Отчете 1. (Без использования предложения ORDER BY возможен вывод других 5 строк, но для такого небольшого набора данных, который мы создали, это маловероятно. Вариации в выводе обычно заметны, когда обрабатывается больше строк.)

Отчет 1. Результаты выполнения листинга 1

В отчете 1 показаны 5 строк, полученных из таблицы dbo.Inventory при выполнении листинга 2. Эти строки несортированы, поскольку предложение ORDER BY не было задано в операторе SELECT.

Для возвращения первых 5 строк из таблицы dbo.Inventory на основе сортировки по столбцу ProductName можно выполнить код в листинге 3.

USE tempdb; 
GO 
SELECT TOP (5) * 
FROM dbo.Inventory 
ORDER BY ProductName; 
GO

Листинг 3. Получение 5 строк из отсортированного набора

При выполнении кода в листинге 3 будет получен следующий результат:

Отчет 2. Результаты выполнения листинга 3

Если предложение ORDER BY включено в запрос, SQL Server сначала сортирует результирующий набор, прежде чем определить возвращаемые строки. Если посмотреть вывод в Отчете 3, можно увидеть, что SQL Server вернул 5 строк, и каждая строка отсортирована на основе столбца ProductName.

До сих пор в примерах число возвращаемых строк было основано на жестко закодированном значении. В данном случае этим числом было «5». Число возвращаемых строк не обязательно жестко кодировать, его можно получить из переменной.

Использование переменной в предложении TOP

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

USE tempdb; 
GO 
CREATE PROCEDURE dbo.Get_TopX (@TopX int) 
AS 
SELECT TOP (@TopX) * 
FROM dbo.Inventory; 
GO 
EXEC dbo.Get_TopX @TopX = 2;

Листинг 4. Хранимая процедура для возвращения числа строк на основе переменной

В листинге была создана хранимая процедура dbo.Get_TopX. Эта процедура требует передачи параметра с именем @TopX при выполнении. Эта переменная используется для определения числа строк, которое будет возвращать предложение TOP при выполнении хранимой процедуры. При выполнении оператора EXEC в листинге 4 будут получены результаты, показанные в Отчете 4.

Отчет 4. Результаты, полученные при выполнении хранимой процедуры dbo.Get_TopX в листинге 4

Различное число строк может возвращаться просто передачей различных значений для входного параметра хранимой процедуры dbo.Get_TopX. Я оставлю вам протестировать это.

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

Кроме того, вы можете использовать выражение в предложении TOP. Замените в предыдущей процедуре на TOP (@TopX -1), и тогда при передаче значения параметра 2 на выходе будет одна строка.

Возвращение процентного отношения строк при использовании предложения TOP

Пока все примеры с предложением TOP возвращали заданное число строк из набора записей на основе числа, указанного в предложении TOP. Это предложением поддерживает также возвращение процентного отношения строк из результирующего набора, а не заданного числа строк. Как это делается показано в листинге 5 на примере оператора SELECT.

USE tempdb; 
GO 
SELECT TOP (50) PERCENT * 
FROM dbo.Inventory 
WHERE ProductName like '% mm %'; 
GO

Листинг 5. Возвращение процентного отношения числа строк при использовании предложения TOP

Результаты выполнения кода листинга 5 показаны в Отчете 4.

Результаты выполнения кода листинга 5

Оператор SELECT из листинга 5 вернул только 2 строки, что точно составляет 50 процентов от числа инструментов для метрических головок, находящихся в таблице dbo.Inventory.

При использовании опции PERCENT в предложении TOP вычисленное число возвращаемых строк может иметь дробное значение. При вычислении дробного числа SQL Server округляет его к следующему целому значению, чтобы получить число возвращаемых строк. Для демонстрации рассмотрим код в листинге 6.

USE tempdb; 
GO 
SELECT TOP (51) PERCENT * 
FROM dbo.Inventory 
WHERE ProductName like '%socket%'; 
GO

Листинг 6. Возвращается 51 процент строк

При выполнении кода в листинге 6 получаются следующие результаты.

Отчет 5. Результаты при выполнении листинга 6

Когда SQL Server обрабатывает код в листинге 5, он обнаруживает, что 4 строки имеют название “socket” в ProductName. 51 процент от 4 строк дает дробное значение 2.04. Поскольку SQL Server не может вернуть дробное число строк, значение округляется к следующему целому значению — вот почему было получено 3 строк при выполнении листинга 6.

Использование опции WITH TIES

Предложение TOP имеет также опцию WITH TIES. Эта опция полезна для возвращения всех строк, которые имеют то же самое значение, что и последняя строка. Под последней строкой я имею в виду номер строки, который равен числу в предложении TOP. При использовании опции WITH TIES оператор должен также включать предложение ORDER BY, в противном случае возникнет следующая ошибка:

Msg 1062, Level 15, State 1, Line 78
The TOP N WITH TIES clause is not allowed without a corresponding ORDER BY clause.

Чтобы увидеть, как вернуть связанные записи, рассмотрите два оператора SELECT в листинге 7.

USE tempdb; 
GO 
SELECT TOP (2) WITH TIES * 
FROM dbo.Inventory 
WHERE Price < 3.60
ORDER BY Price; 
GO 
SELECT TOP (2) * 
FROM dbo.Inventory 
WHERE Price < 3.60
ORDER BY Price; 
GO

Листинг 7. Использование опции WITH TIES

При выполнении кода в листинге 7 первый оператор SELECT возвращает строки, показанные в отчете 6, а второй оператор SELECT — в отчете 7.

Отчет 6. Возвращаемые строки, когда используется опция WITH TIES

Отчет 7. Возвращаемые строки, когда опция WITH TIES не используется

Оба оператора SELECT в листинге 7 используют одинаковое предложение TOP (2). Единственное отличие состоит в том, что в первом операторе SELECT используется опция WITH TIES, а во втором — нет. По результатам в отчетах 6 и 7 можно увидеть, что опция WITH TIES приводит к выводу 3 строк, а без нее возвращается только 2 строки. Опция WITH TIES возвращает третью строку в листинге 6 потому, что эта строка имеет тот же самое значение в столбце Price, что и вторая строка (или последняя строка на основе числа в предложении TOP).

Логическая обработка предложений TOP и ORDER BY

Иногда вы можете получить в чем-то неожиданные результаты, обусловленные логическим порядком обработки предложений TOP и ORDER BY. Эти неожиданные результаты могут иметь место, когда предложение TOP используется в сочетании с операторами UNION, UNION ALL, EXCEPT и INTERSECT. Для демонстрации выполним код в листинге 8. Этот код использует операцию UNION.

USE tempdb; 
GO 
SELECT TOP(1) * FROM dbo.Inventory 
WHERE ProductName like 'Flat head%' 
UNION 
SELECT TOP(1) * FROM dbo.Inventory 
WHERE ProductName like '%socket%' 
ORDER BY Price ASC; 
GO

Листинг 8. Запрос с UNION

Намерением кода в листинге 8 было найти наименее дороге продукты “Flat head” и “socket”. При выполнении кода в листинге 8 получены результаты, показанные в отчете 8.

Отчет 8. Результаты выполнения кода в листинге 8

Код в листинге 8 не выводит наименее дорогие продукты “Flat head” или “socket”. Причина заключается в том, что, поскольку предложение ORDER BY обрабатывается после выполнения двух операторов SELECT и операции UNION.

Поскольку оба оператора SELECT возвращают несортированный набор, предложение TOP (1) просто возвращает первую строку этого неупорядоченного набора. В данном случае это не был наименее дорогой элемент. Чтобы получить результаты, необходимо обработать предложение ORDER BY для каждого оператора SELECT до выполнения предложения TOP в каждом операторе. Для правильного нахождения самых дешевых инструментов “Flat head” и “socket” может использоваться код в листинге 9.

USE tempdb; 
GO 
SELECT * FROM 
(SELECT TOP(1) * FROM dbo.Inventory 
WHERE ProductName like 'Flat head%' 
ORDER BY Price ASC) AS Flat_Head 
UNION 
SELECT * FROM 
(SELECT TOP(1) * FROM dbo.Inventory 
WHERE ProductName like '%socket%' 
ORDER BY Price ASC) AS MM; 
GO

Листинг 9. Правильное нахождение наименее дорогих продуктов

При выполнении кода в листинге 9 будут получены следующие результаты.

Отчет 9. Результаты выполнения кода в листинге 9

Добавляя предложение ORDER BY в оператор SELECT каждого подзапроса, наряду с предложением TOP, мы сначала выполняем сортировку до выборки записи TOP (1) из каждого оператора. Имея отсортированные данные, для каждого типа продуктов выбрается корректный самый дешевый пункт.

Использование предложения TOP в операторе SELECT

Предложение TOP может использоваться для ограничения числа возвращаемых строк из оператора SELECT. Число возвращаемых строк может быть точным числом или процентным отношением. Опция WITH TIES может использоваться для возврата большего числа строк, чем указано в предложении TOP, когда последняя возвращаемая строка имеет то же значение, что и несколько последующих строк. Если требуется выбрать строки на основе некоторого порядка, то должен быть включено предложение ORDER BY. Примеры в данной статье относятся только к использованию предложения TOP в операторе SELECT. Предложение TOP может также применяться к операторам INSERT, UPDATE, DELETE или MERGE. За более подробной информацией обратитесь к документации Microsoft.

Ссылки по теме

1. Советы по производительности оператора SQL TOP
2. SQL ORDER BY: 5 правил сортировки на профессиональном уровне
3. Любопытный случай сортировки с Top N
4. Получение максимального значения на группу в SQL Server: Row Number против Cross Apply

Комментарии

В следующем программном коде показаны ключевые секции команды SELECT — SQL:

Копировать Код

SELECT Select_List FROM Table_List. [WITH (BUFFERING = lExpr)] [WHERE Conditions] [GROUP BY Column_List] [HAVING Conditions] [UNION Clause] [ORDER BY Column_List] [INTO Clause | TO Clause ] [Additional_Display_Options]

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

• FROM, секция
• SELECT — SQL, команда — WITH, секция
• SELECT — SQL, команда — WHERE, секция
• GROUP BY, секция
• HAVING, секция
• UNION, секция
• ORDER BY, секция
• INTO / TO, секция
• Дополнительные Опции отображения результатов Запроса

Примеры

Пример 1

В представленном примере выполняется выборка уникальных значений с использованием опции DISTINCT ; соединяются две таблицы с помощью параметров секции WHERE . В запросе выбираются поля: Company, Order_Date, и Shipped_On, имеющие уникальные совокупные значения в таблицах-источниках: Customer и Orders. В операторе SELECT определены внутренние (локальные ) алиасы таблиц — для однозначного указания связующего поля Cust_ID, которое присутствует в обоих таблицах.

Копировать Код

CLOSE ALLCLOSE DATABASESOPEN DATABASE (HOME(2) + 'Data\TestData')SELECT DISTINCT TAlias1.company, TAlias2.order_date, ; TAlias2.shipped_on ; FROM customer TAlias1, orders TAlias2 ; WHERE TAlias1.cust_id = TAlias2.cust_id

Пример 2

В следующем примере выполняется выборка всех записей из исходной таблицы; для определения нового имени выбираемого поля используется опция AS Column_Name . В результирующий набор выбирается поле City, из таблицы Customer, значение которого преобразуется в верхний регистр, с помощью функции UPPER( ); выбираемому столбцу присваивается имя: CityList.

Копировать Код

CLOSE ALLCLOSE DATABASESOPEN DATABASE (HOME(2) + 'Data\TestData')SELECT UPPER(city) AS CityList FROM customer

3.4 Команда выборки данных

Одной из главных функций SQL считается выполнение выборки. Поэтому рассмотрим этот процесс подробно.

Выборка – это обращение к БД с целью извлечь данные в виде, удобном для пользователя. Для выборки применяются запросы к БД. Иногда в SQL выделяют даже раздел, который называют языком запросов к данным DQL (Data Query Language). Фактически этот раздел языка ANSI SQL представлен только одной командой – SELECT. Но эта команда достаточно обширна. Она является ядром языка SQL. и используется для реализации операций проекции, ограничения, расширения.

Для пользователя РБД оператор SELECT является, пожалуй, одним из наиболее главных и полезных операторов языка SQL. Этот оператор позволяет производить:

• выбор данных (отбор записей и полей);
• вычисления и сравнения;
• упорядочение записей при выводе содержимого таблиц;
• группирование данных и применение к этим группам специальных групповых операций.

• инструкция языка SQL – это предложение (команда, оператор);
• отдельные составные части инструкции (список полей, спецификация) – это опции предложения;
• любая спецификация – это фраза, отвечающая требованиям синтаксиса предложения.

• основная часть команды SELECT …FROM обязательна;
• опция предикат необязательна
• дополнительные части WHERE,GROUPBY,ORDERBYнеобязательны, они следуют за FROM;
• опция HAVING не может применяться без GROUP BY.

• при наличии слова DISTINCT эти записи не учитываются;
• функция COUNT возвратит значение нуль;
• функция COUNT (*) обработает все записи так же, как обычные значения;
• другие функции обычно возвращают Null-значение.

• числа сравниваются алгебраически; отрицательные числа считаются меньшими, чем положительные, независимо от их абсолютной величины;
• строки символов сравниваются в соответствии с их представлением в коде, используемом в конкретной СУБД, например, в коде ASCII;
• если сравниваются две строки символов, имеющих разные длины, то перед выполнением операции сравнения их длина уравнивается до большей за счет добавления пробелов справа в короткой строке.

• символ _ (подчеркивание) заменяет любой одиночный символ;
• символ % (процент) заменяет любую последовательность из N символов, где N может быть нулем;
• все другие символы означают сами себя.

• SELECT ↔ проекция и расширение;
• FROM ↔ произведение;
• WHERE ↔ ограничение.