stringtranslate.com

Выбрать (SQL)

Оператор SQL SELECT возвращает результирующий набор строк из одной или нескольких таблиц . [1] [2]

Оператор SELECT извлекает ноль или более строк из одной или нескольких таблиц базы данных или представлений базы данных . В большинстве приложений SELECTявляется наиболее часто используемой командой языка манипулирования данными (DML). Поскольку SQL является декларативным языком программирования , SELECTзапросы указывают набор результатов, но не указывают, как его вычислить. База данных преобразует запрос в « план запроса », который может различаться в зависимости от выполнения, версии базы данных и программного обеспечения базы данных. Эта функциональность называется « оптимизатором запросов », поскольку она отвечает за поиск наилучшего возможного плана выполнения для запроса в рамках применимых ограничений.

Оператор SELECT имеет много необязательных предложений:

Обзор

SELECTявляется наиболее распространенной операцией в SQL, называемой «запрос». SELECTизвлекает данные из одной или нескольких таблиц или выражений. Стандартные SELECTоператоры не оказывают постоянного воздействия на базу данных. Некоторые нестандартные реализации SELECTмогут оказывать постоянное воздействие, например SELECT INTOсинтаксис, предусмотренный в некоторых базах данных. [4]

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

Запрос включает список столбцов для включения в конечный результат, обычно сразу после SELECTключевого слова. Звездочка (" *") может использоваться для указания того, что запрос должен вернуть все столбцы всех запрошенных таблиц. SELECTявляется наиболее сложным оператором в SQL, с необязательными ключевыми словами и предложениями, которые включают:

Следующий пример запроса SELECTвозвращает список дорогих книг. Запрос извлекает все строки из таблицы Book , в которых столбец price содержит значение больше 100.00. Результат сортируется в порядке возрастания по title . Звездочка (*) в списке выбора указывает, что все столбцы таблицы Book должны быть включены в набор результатов.

ВЫБРАТЬ * ИЗ КНИГИ , ГДЕ цена > 100 . 00 УПОРЯДОЧИТЬ ПО названию ;

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

 ВЫБРАТЬ Книгу.title КАК Название , количество ( * ) КАК Авторы ИЗ Книги ПРИСОЕДИНИТЬ Автор_книги В Книгу.isbn = Автор_книги.isbn ГРУППИРОВАТЬ ПО Книгу.title ;​​​​​​​​

Пример вывода может выглядеть следующим образом:

Название Авторы---------------------- -------Примеры и руководство SQL 4Радость SQL 1Введение в SQL 2Подводные камни SQL 1

При условии, что isbn является единственным общим именем столбца в двух таблицах и что столбец с именем title существует только в таблице Book , можно переписать приведенный выше запрос в следующем виде:

ВЫБРАТЬ название , количество ( * ) КАК Авторы ИЗ Книги ЕСТЕСТВЕННОЕ СОЕДИНЕНИЕ Автор_книги ГРУППИРОВАТЬ ПО название ;

Однако многие поставщики [ квантификации ] либо не поддерживают этот подход, либо требуют соблюдения определенных соглашений об именовании столбцов для эффективной работы естественных объединений.

SQL включает операторы и функции для вычисления значений на основе сохраненных значений. SQL позволяет использовать выражения в списке выбора для проецирования данных, как в следующем примере, который возвращает список книг стоимостью более 100,00 с дополнительным столбцом sales_tax , содержащим показатель налога с продаж, рассчитанный в размере 6% от цены .

ВЫБРАТЬ isbn , название , цена , цена * 0. 06 КАК налог_с_продаж ИЗ Книги , ГДЕ цена > 100. 00 УПОРЯДОЧИТЬ ПО название ;​

Подзапросы

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

ВЫБРАТЬ isbn , название , цену ИЗ Книги, ГДЕ цена < ( ВЫБРАТЬ СРЕДНЯЯ ( цена ) ИЗ Книги ) УПОРЯДОЧИТЬ ПО название ;

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

С 1999 года стандарт SQL допускает предложения WITH, т. е. именованные подзапросы, часто называемые общими табличными выражениями (названными и разработанными в честь реализации IBM DB2 версии 2; Oracle называет их факторизацией подзапросов ). CTE также могут быть рекурсивными , ссылаясь на себя; полученный механизм позволяет выполнять обходы деревьев или графов (при представлении в виде отношений) и, в более общем плане, вычисления с фиксированной точкой .

Производная таблица

Производная таблица — это подзапрос в предложении FROM. По сути, производная таблица — это подзапрос, из которого можно выбирать или к которому можно присоединяться. Функционал производной таблицы позволяет пользователю ссылаться на подзапрос как на таблицу. Производная таблица также называется встроенным представлением или списком select in from .

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

ВЫБРАТЬ b.isbn , b.title , b.price , sales.items_sold , sales.company_nm ИЗ Книги b ПРИСОЕДИНИТЬСЯ ( ВЫБРАТЬ СУММУ ( Товары_проданы ) Товары_проданы , Company_Nm , ISBN ИЗ Книги СГРУППИРОВАТЬ ПО Company_Nm , ISBN ) продажи В продажи.isbn = b.isbn​​​​​​​​​​​​​​

Примеры

Для таблицы T запрос приведет к отображению всех элементов всех строк таблицы.SELECT * FROM T

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

При использовании той же таблицы запрос приведет к отображению всех элементов всех строк, где значение столбца C1 равно '1' — в терминах реляционной алгебры будет выполнен выбор из-за предложения WHERE. Это также известно как горизонтальное разбиение, ограничивающее вывод строк запросом в соответствии с указанными условиями.SELECT * FROM T WHERE C1 = 1

При наличии более одной таблицы результирующий набор будет содержать все комбинации строк. Так, если две таблицы — T1 и T2, то результатом будет каждая комбинация строк T1 с каждой строкой T2. Например, если T1 имеет 3 строки, а T2 имеет 5 строк, то результатом будет 15 строк.SELECT * FROM T1, T2

Хотя это и не является стандартом, большинство СУБД позволяют использовать предложение select без таблицы, делая вид, что используется воображаемая таблица с одной строкой. Это в основном используется для выполнения вычислений, где таблица не нужна.

Предложение SELECT определяет список свойств (столбцов) по имени или с помощью подстановочного знака («*»), означающего «все свойства».

Ограничение строк результата

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

В ISO SQL:2003 наборы результатов могут быть ограничены с помощью

Данный пункт был введен в стандарт ISO SQL:2008FETCH FIRST .

Согласно документации PostgreSQL v.9, оконная функция SQL «выполняет вычисления по набору строк таблицы, которые каким-либо образом связаны с текущей строкой», аналогично агрегатным функциям. [7] Название напоминает оконные функции обработки сигналов . Вызов оконной функции всегда содержит предложение OVER .

Оконная функция ROW_NUMBER()

ROW_NUMBER() OVERможет использоваться для простой таблицы по возвращаемым строкам, например, для возврата не более десяти строк:

SELECT * FROM ( SELECT ROW_NUMBER () OVER ( ORDER BY sort_key ASC ) AS row_number , columns FROM tablename ) AS foo WHERE row_number <= 10

ROW_NUMBER может быть недетерминированным : если sort_key не является уникальным, каждый раз при выполнении запроса можно получить разные номера строк, назначенные любым строкам, где sort_key является одинаковым. Если sort_key является уникальным, каждая строка всегда будет получать уникальный номер строки.

Оконная функция RANK()

Оконная RANK() OVERфункция действует как ROW_NUMBER, но может возвращать больше или меньше n строк в случае совпадения условий, например, чтобы вернуть 10 самых молодых людей:

ВЫБРАТЬ * ИЗ ( ВЫБРАТЬ РАНГ () ПО ( УПОРЯДОЧИТЬ ПО ВОЗРАСТАМ ) КАК рейтинг , идентификатор_персоны , имя_персоны , возраст ОТ персона ) КАК foo ГДЕ рейтинг < = 10

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

Предложение FETCH FIRST

Начиная с ISO SQL:2008 пределы результатов можно указать, как в следующем примере, с помощью FETCH FIRSTпредложения.

ВЫБРАТЬ * ИЗ T ВЫБРАТЬ ТОЛЬКО ПЕРВЫЕ 10 СТРОК

В настоящее время этот пункт поддерживается CA DATACOM/DB 11, IBM DB2, SAP SQL Anywhere, PostgreSQL, EffiProz, H2, HSQLDB версии 2.0, Oracle 12c и Mimer SQL .

Microsoft SQL Server 2008 и выше поддерживает FETCH FIRST, но он считается частью ORDER BYпредложения. Для этого использования требуются предложения ORDER BY, OFFSET, и .FETCH FIRST

ВЫБРАТЬ * ИЗ T УПОРЯДОЧИТЬ ПО acolumn УБЫТОЧНОЕ СМЕЩЕНИЕ 0 СТРОК ВЫБРАТЬ ТОЛЬКО ПЕРВЫЕ 10 СТРОК

Нестандартный синтаксис

Некоторые СУБД предлагают нестандартный синтаксис вместо или в дополнение к стандартному синтаксису SQL. Ниже перечислены варианты простого предельного запроса для различных СУБД:

Разбивка строк на страницы

Разбиение на страницы по строкам [9] — это подход, используемый для ограничения и отображения только части всех данных запроса в базе данных. Вместо того, чтобы показывать сотни или тысячи строк одновременно, сервер запрашивает только одну страницу (ограниченный набор строк, например, только 10 строк), и пользователь начинает навигацию, запрашивая следующую страницу, затем следующую и т. д. Это очень полезно, особенно в веб-системах, где нет выделенного соединения между клиентом и сервером, поэтому клиенту не нужно ждать, чтобы прочитать и отобразить все строки сервера.

Данные в подходе пагинации

Самый простой метод (но очень неэффективный)

  1. Выбрать все строки из базы данных
  2. Читать все строки, но отправлять на отображение только тогда, когда row_number прочитанных строк находится в диапазоне от {begin_base_0 + 1}до{begin_base_0 + rows}
Выбрать * из { table } упорядочить по { unique_key }

Другой простой метод (немного более эффективный, чем чтение всех строк)

  1. Выберите все строки таблицы от начала до последней строки для отображения ( {begin_base_0 + rows})
  2. Читать {begin_base_0 + rows}строки, но отправлять на отображение только тогда, когда row_number прочитанных строк больше, чем{begin_base_0}


Метод с позиционированием

  1. Выберите {rows}для отображения только строки, начиная со следующей строки ( {begin_base_0 + 1})
  2. Прочитать и отправить для отображения всех строк, считанных из базы данных


Метод с фильтром (он более сложный, но необходим для очень больших наборов данных)

  1. Выберите только {rows}строки с фильтром:
    1. Первая страница: выберите только первые {rows}строки в зависимости от типа базы данных.
    2. Следующая страница: выбрать только первые {rows}строки, в зависимости от типа базы данных, где {unique_key}больше {last_val}(значение {unique_key}последней строки на текущей странице)
    3. Предыдущая страница: отсортировать данные в обратном порядке, выбрать только первые {rows}строки, где {unique_key}меньше {first_val}(значения {unique_key}первой строки на текущей странице), и отсортировать результат в правильном порядке
  2. Прочитать и отправить для отображения всех строк, считанных из базы данных

Иерархический запрос

Некоторые базы данных предоставляют специализированный синтаксис для иерархических данных .

Оконная функция в SQL:2003 — это агрегатная функция, применяемая к разделу результирующего набора.

Например,

 сумма ( население ) ПО ( РАЗДЕЛЕНИЕ ПО городу )

вычисляет сумму численности населения всех строк, имеющих то же значение города , что и текущая строка.

Разделы указываются с помощью предложения OVER , которое изменяет агрегат. Синтаксис:

< OVER_CLAUSE >  :: = НАД ( [ РАЗДЕЛЕНИЕ ПО < выраж > , ... ] [ УПОРЯДОЧИТЬ ПО < выражение > ] )

Предложение OVER может разбить и упорядочить результирующий набор. Упорядочивание используется для функций относительно порядка, таких как row_number.

Оценка запроса ANSI

Обработка оператора SELECT в соответствии с ANSI SQL будет выглядеть следующим образом: [10]

  1. выберите g . * из пользователей u внутренние группы присоединения g для g . Userid = u . Userid где u . LastName = 'Smith' и u . FirstName = 'John'
  2. оценивается предложение FROM, создается перекрестное соединение или декартово произведение для первых двух таблиц в предложении FROM, в результате чего получается виртуальная таблица Vtable1
  3. предложение ON оценивается для vtable1; только записи, которые соответствуют условию соединения g.Userid = u.Userid, вставляются в Vtable2
  4. Если указано внешнее соединение, записи, которые были удалены из vTable2, добавляются в VTable 3, например, если приведенный выше запрос был следующим:
    выберите u . * из пользователей u left присоединиться к группам g на g . Userid = u . Userid где u . LastName = 'Smith' и u . FirstName = 'John'
    все пользователи, не входящие ни в одну группу, будут добавлены обратно в Vtable3
  5. оценивается предложение WHERE, в этом случае в vTable4 будет добавлена ​​только информация о группе для пользователя John Smith
  6. GROUP BY оценивается; если приведенный выше запрос был:
    выберите g.GroupName , count ( g . * ) as NumberOfMembers из пользователей u , внутренние группы g , присоединяются к g.Userid = u.Userid group по GroupName​​​​
    vTable5 будет состоять из элементов, возвращенных из vTable4, упорядоченных по группировке, в данном случае GroupName
  7. Предложение HAVING оценивается для групп, для которых предложение HAVING истинно, и вставляется в vTable6. Например:
    выберите g.GroupName , count ( g . * ) как NumberOfMembers из пользователей u , внутренние группы присоединения g к g.Userid = u.Userid группа по GroupName , имеющая count ( g . * ) > 5​​​​
  8. список SELECT оценивается и возвращается как Vtable 7
  9. оценивается предложение DISTINCT; дублирующиеся строки удаляются и возвращаются как Vtable 8
  10. предложение ORDER BY оценивается, упорядочивая строки и возвращая VCursor9. Это курсор, а не таблица, поскольку ANSI определяет курсор как упорядоченный набор строк (не реляционный).

Поддержка оконных функций поставщиками СУБД

Реализация функций оконных функций поставщиками реляционных баз данных и SQL-движков сильно различается. Большинство баз данных поддерживают по крайней мере некоторые разновидности оконных функций. Однако, если присмотреться, становится ясно, что большинство поставщиков реализуют только подмножество стандарта. Давайте возьмем в качестве примера мощное предложение RANGE. Только Oracle, DB2, Spark/Hive и Google Big Query полностью реализуют эту функцию. Совсем недавно поставщики добавили новые расширения к стандарту, например, функции агрегации массивов. Они особенно полезны в контексте запуска SQL в отношении распределенной файловой системы (Hadoop, Spark, Google BigQuery), где у нас более слабые гарантии совместной локализации данных, чем в распределенной реляционной базе данных (MPP). Вместо равномерного распределения данных по всем узлам, SQL-движки, выполняющие запросы в отношении распределенной файловой системы, могут достигать гарантий совместной локализации данных путем вложения данных и, таким образом, избегая потенциально дорогостоящих объединений, включающих интенсивное перемешивание по сети. Определяемые пользователем агрегатные функции, которые можно использовать в оконных функциях, являются еще одной чрезвычайно мощной функцией.

Генерация данных в T-SQL

Метод генерации данных на основе объединения всех

выберите 1 а , 1 б объединение всех выберите 1 , 2 объединение всех выберите 1 , 3 объединение всех выберите 2 , 1 объединение всех выберите 5 , 1

SQL Server 2008 поддерживает функцию «конструктора строк», указанную в стандарте SQL:1999.

выберите * из ( значений ( 1 , 1 ), ( 1 , 2 ), ( 1 , 3 ), ( 2 , 1 ), ( 5 , 1 )) как x ( a , b )

Ссылки

  1. ↑ Microsoft (23 мая 2023 г.). «Соглашения о синтаксисе Transact-SQL».
  2. ^ MySQL. «Синтаксис SQL SELECT».
  3. ^ Пропуск предложения FROM не является стандартным, но допускается большинством основных СУБД.
  4. ^ "Transact-SQL Reference". Справочник по языку SQL Server. Электронная литература по SQL Server 2005. Microsoft. 2007-09-15 . Получено 2007-06-17 .
  5. ^ SAS 9.4 SQL Procedure User's Guide. SAS Institute (опубликовано в 2013 г.). 10 июля 2013 г. стр. 248. ISBN 9781612905686. Получено 21.10.2015 . Хотя аргумент UNIQUE идентичен DISTINCT, он не является стандартом ANSI.
  6. ^ Леон, Алексис ; Леон, Мэтьюз (1999). "Устранение дубликатов - SELECT с использованием DISTINCT". SQL: Полный справочник. Нью-Дели: Tata McGraw-Hill Education (опубликовано в 2008 г.). стр. 143. ISBN 9780074637081. Получено 21.10.2015 . [...] ключевое слово DISTINCT [...] исключает дубликаты из набора результатов.
  7. ^ Документация PostgreSQL 9.1.24 - Глава 3. Расширенные возможности
  8. ^ OpenLink Software. "9.19.10. Вариант TOP SELECT". docs.openlinksw.com . Получено 1 октября 2019 г. .
  9. ^ Инж. Оскар Бонилья, MBA
  10. ^ Внутри Microsoft SQL Server 2005: запросы T-SQL Ицика Бен-Гана, Любора Коллара и Деяна Сарки

Источники

Внешние ссылки