Что является результатами выполнения операторов языка sql

Содержание

SQL-операторы: руководство с примерами запросов

Перевод первой части статьи «SQL Operators Tutorial – Bitwise, Comparison, Arithmetic, and Logical Operator Query Examples».

Весь интернет и все приложения это по сути просто данные.

Каждый email, твит, селфи, банковская транзакция — просто данные, хранящиеся где-то в базе данных.

Чтобы от этих данных был какой-то прок, мы должны иметь возможность получать их. Но только лишь получения данных недостаточно: данные также должны быть полезными и подходящими для нашей ситуации.

На уровне базы данных мы запрашиваем из нее какую-либо информацию путем написания SQL-запроса. Этот SQL-запрос конкретизирует, какие именно данные и в каком формате мы хотим получить.

В этой статье мы поговорим обо всех самых распространенных способах фильтрации SQL-запросов. В первой части мы рассмотрим следующие темы:

Настройка базы данных

Чтобы иметь возможность фильтровать данные, нужно для начала этими данными обзавестись.

В наших примерах мы будем использовать PostgreSQL, но запросы и концепции, которые мы будем показывать, легко применимы в любой другой современной СУБД (например, в MySQL, SQL Server и т. п.).

Для работы с нашей базой данных PostgreSQL мы будем пользоваться интерактивной cli-программой psql. Если у вас установлен другой клиент, вы прекрасно можете работать с ним!

Теперь давайте воспользуемся интерактивной консолью (запустив команду psql ) и подключимся к только что созданной базе данных при помощи команды \c :

Создание пользователей

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

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

Давайте в рамках нашей psql-сессии создадим таблицу users :

В выводе мы видим CREATE TABLE : это означает, что создание таблицы прошло успешно.

Примечание: в примерах я подчистил вывод, чтобы их было легче читать. Поэтому не волнуйтесь, что показанный здесь output не в точности совпадает с тем, что вы видите в своем терминале.

Давайте посмотрим содержимое нашей таблицы с пользователями:

Мы не вставили в таблицу никаких данных, поэтому видим просто пустую структуру.

Если вы не знакомы с SQL-запросами, этот запрос — SELECT * FROM users — один из самых простых, которые вы можете написать.

Ключевое слово SELECT указывает, какие именно столбцы вы хотите вернуть ( * означает «все столбцы»), а ключевое слово FROM указывает, о какой таблице идет речь (в нашем случае — users ).

Таким образом, SELECT * FROM users на самом деле означает «верни все строки и все столбцы из таблицы users».

Вставка (добавление) пользователей

Пустая таблица — не слишком интересная вещь, так что давайте добавим в нее кое-какие данные, чтобы попрактиковаться в составлении запросов:

Запустив запрос SELECT * FROM users еще раз, мы увидим внесенные данные:

Фильтрация данных при помощи WHERE

Скажем, нам нужно найти пользователей, чье имя — John:

В наших данных есть две строки, отвечающие этому условию.

Чтобы найти строку Джона Якобса («John Jacobs»), мы можем запросить данные по его ID:

Условию id = 1 отвечает только одна строка, ее-то мы и получим.

Логические операторы (AND / OR / NOT)

Давайте попробуем найти нужную запись для человека по имени Джон Смит («John Smith»), не указывая конкретный ID. Воспользуемся оператором AND в обороте WHERE, чтобы искать и по имени, и по фамилии:

Поиск людей с именем John или с фамилией Doe:

В результате мы видим строки с обоими Джонами и одной Джейн Доу.

Условия AND и OR можно комбинировать. Допустим, мы хотим найти кого-то по имени John Smith или кого-то с фамилией Doe (т. е., все равно, с каким именем):

Если мы хотим инвертировать это условие и найти пользователей, чье имя не John Smith и фамилия не Doe, можно добавить оператор NOT :

Примечание: у всех свой личный стиль форматирования запросов, так что делайте так, как удобно вам!

Допустим, нам нужно найти пользователей, которым уже исполнилось 18 лет (т. е., 18 и старше):

Как насчет пользователей до 35 (включительно), которым при этом больше 25?

Кроме всего прочего, мы можем совершать математические действия с нашими данными.

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

Во второй части статьи мы рассмотрим следующие темы:

Источник

Язык SQL стал фактически стандартным языком доступа к базам данных. Все СУБД, претендующие на название «реляционные», реализуют тот или иной диалект SQL. Многие нереляционные системы также имеют в настоящее время средства доступа к реляционным данным. Целью стандартизации является переносимость приложений между различными СУБД.

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

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

Операторы SQL

Основу языка SQL составляют операторы, условно разбитые не несколько групп по выполняемым функциям.

Можно выделить следующие группы операторов (перечислены не все операторы SQL):

Операторы защиты и управления данными

Кроме того, есть группы операторов установки параметров сеанса, получения информации о базе данных, операторы статического SQL, операторы динамического SQL.

Наиболее важными для пользователя являются операторы манипулирования данными (DML).

Примеры использования операторов манипулирования данными

Пример 1. Вставка одной строки в таблицу:

Пример 2. Вставка в таблицу нескольких строк, выбранных из другой таблицы (в таблицу TMP_TABLE вставляются данные о поставщиках из таблицы P, имеющие номера, большие 2):

Пример 3. Обновление нескольких строк в таблице:

Пример 4. Удаление нескольких строк в таблице:

Пример 5. Удаление всех строк в таблице:

Примеры использования оператора SELECT

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

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

Отбор данных из одной таблицы

Пример 6. Выбрать все данные из таблицы поставщиков (ключевые слова SELECT… FROM…):

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

Пример 7. Выбрать все строки из таблицы поставщиков, удовлетворяющих некоторому условию (ключевое слово WHERE…):

Замечание. В качестве условия в разделе WHERE можно использовать сложные логические выражения, использующие поля таблиц, константы, сравнения (>, | = | <>> Конструктор значений строки

Пример 33. Сравнение поля таблицы и скалярного значения:

Пример 34. Сравнение двух сконструированных строк:

Этот пример эквивалентен условному выражению

Предикат between ::=
Конструктор значений строки [NOT] BETWEEN
Конструктор значений строки AND Конструктор значений строки

Пример 35. PD.VOLUME BETWEEN 10 AND 100

Предикат in ::=
Конструктор значений строки [NOT] IN
<(Select-выражение) | (Выражение для вычисления значения. )>

Предикат like ::=
Выражение для вычисления значения строки-поиска [NOT] LIKE
Выражение для вычисления значения строки-шаблона [ESCAPE Символ]

Предикат null ::=
Конструктор значений строки IS [NOT] NULL

Замечание. Предикат NULL применяется специально для проверки, не равно ли проверяемое выражение null-значению.

Замечание. Кванторы ANY и SOME являются синонимами и полностью взаимозаменяемы.

Замечание. Если указан один из кванторов ANY и SOME, то предикат количественного сравнения возвращает TRUE, если сравниваемое значение совпадает хотя бы с одним значением, возвращаемом в подзапросе (select-выражении).

Замечание. Если указан квантор ALL, то предикат количественного сравнения возвращает TRUE, если сравниваемое значение совпадает с каждым значением, возвращаемом в подзапросе (select-выражении).

Предикат exist ::=
EXIST (Select-выражение)

Замечание. Предикат EXIST возвращает значение TRUE, если результат подзапроса (select-выражения) не пуст.

Предикат unique ::=
UNIQUE (Select-выражение)

Замечание. Предикат UNIQUE возвращает TRUE, если в результате подзапроса (select-выражения) нет совпадающих строк.

Предикат match ::=
Конструктор значений строки MATCH [UNIQUE]
[PARTIAL | FULL] (Select-выражение)

Замечание. Предикат MATCH проверяет, будет ли значение, определенное в конструкторе строки совпадать со значением любой строки, полученной в результате подзапроса.

Предикат overlaps ::=
Конструктор значений строки OVERLAPS Конструктор значений строки

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

Порядок выполнения оператора SELECT

Для того чтобы понять, как получается результат выполнения оператора SELECT, рассмотрим концептуальную схему его выполнения. Эта схема является именно концептуальной, т.к. гарантируется, что результат будет таким, как если бы он выполнялся шаг за шагом в соответствии с этой схемой. На самом деле, реально результат получается более изощренными алгоритмами, которыми «владеет» конкретная СУБД.

Стадия 1. Выполнение одиночного оператора SELECT

Если в операторе присутствуют ключевые слова UNION, EXCEPT и INTERSECT, то запрос разбивается на несколько независимых запросов, каждый из которых выполняется отдельно:

Шаг 1 (FROM). Вычисляется прямое декартовое произведение всех таблиц, указанных в обязательном разделе FROM. В результате шага 1 получаем таблицу A.

Шаг 2 (WHERE). Если в операторе SELECT присутствует раздел WHERE, то сканируется таблица A, полученная при выполнении шага 1. При этом для каждой строки из таблицы A вычисляется условное выражение, приведенное в разделе WHERE. Только те строки, для которых условное выражение возвращает значение TRUE, включаются в результат. Если раздел WHERE опущен, то сразу переходим к шагу 3. Если в условном выражении участвуют вложенные подзапросы, то они вычисляются в соответствии с данной концептуальной схемой. В результате шага 2 получаем таблицу B.

Шаг 3 (GROUP BY). Если в операторе SELECT присутствует раздел GROUP BY, то строки таблицы B, полученной на втором шаге, группируются в соответствии со списком группировки, приведенным в разделе GROUP BY. Если раздел GROUP BY опущен, то сразу переходим к шагу 4. В результате шага 3 получаем таблицу С.

Шаг 4 (HAVING). Если в операторе SELECT присутствует раздел HAVING, то группы, не удовлетворяющие условному выражению, приведенному в разделе HAVING, исключаются. Если раздел HAVING опущен, то сразу переходим к шагу 5. В результате шага 4 получаем таблицу D.

Шаг 5 (SELECT). Каждая группа, полученная на шаге 4, генерирует одну строку результата следующим образом. Вычисляются все скалярные выражения, указанные в разделе SELECT. По правилам использования раздела GROUP BY, такие скалярные выражения должны быть одинаковыми для всех строк внутри каждой группы. Для каждой группы вычисляются значения агрегатных функций, приведенных в разделе SELECT. Если раздел GROUP BY отсутствовал, но в разделе SELECT есть агрегатные функции, то считается, что имеется всего одна группа. Если нет ни раздела GROUP BY, ни агрегатных функций, то считается, что имеется столько групп, сколько строк отобрано к данному моменту. В результате шага 5 получаем таблицу E, содержащую столько колонок, сколько элементов приведено в разделе SELECT и столько строк, сколько отобрано групп.

Стадия 2. Выполнение операций UNION, EXCEPT, INTERSECT

Если в операторе SELECT присутствовали ключевые слова UNION, EXCEPT и INTERSECT, то таблицы, полученные в результате выполнения 1-й стадии, объединяются, вычитаются или пересекаются.

Стадия 3. Упорядочение результата

Если в операторе SELECT присутствует раздел ORDER BY, то строки полученной на предыдущих шагах таблицы упорядочиваются в соответствии со списком упорядочения, приведенном в разделе ORDER BY.

Как на самом деле выполняется оператор SELECT

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

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

Схематично работу оптимизатора можно представить в виде последовательности нескольких шагов:

Шаг 1 (Синтаксический анализ). Поступивший запрос подвергается синтаксическому анализу. На этом шаге определяется, правильно ли вообще (с точки зрения синтаксиса SQL) сформулирован запрос. В ходе синтаксического анализа вырабатывается некоторое внутренне представление запроса, используемое на последующих шагах.

Шаг 2 (Преобразование в каноническую форму). Запрос во внутреннем представлении подвергается преобразованию в некоторую каноническую форму. При преобразовании к канонической форме используются как синтаксические, так и семантические преобразования. Синтаксические преобразования (например, приведения логических выражений к конъюнктивной или дизъюнктивной нормальной форме, замена выражений «x AND NOT x» на «FALSE», и т.п.) позволяют получить новое внутренне представление запроса, синтаксически эквивалентное исходному, но стандартное в некотором смысле. Семантические преобразования используют дополнительные знания, которыми владеет система, например, ограничения целостности. В результате семантических преобразований получается запрос, синтаксически не эквивалентный исходному, но дающий тот же самый результат.

Шаг 3 (Генерация планов выполнения запроса и выбор оптимального плана). На этом шаге оптимизатор генерирует множество возможных планов выполнения запроса. Каждый план строится как комбинация низкоуровневых процедур доступа к данным из таблиц, методам соединения таблиц. Из всех сгенерированных планов выбирается план, обладающий минимальной стоимостью. При этом анализируются данные о наличии индексов у таблиц, статистических данных о распределении значений в таблицах, и т.п. Стоимость плана это, как правило, сумма стоимостей выполнения отдельных низкоуровневых процедур, которые используются для его выполнения. В стоимость выполнения отдельной процедуры могут входить оценки количества обращений к дискам, степень загруженности процессора и другие параметры.

Шаг 4. (Выполнение плана запроса). На этом шаге план, выбранный на предыдущем шаге, передается на реальное выполнение.

Во многом качество конкретной СУБД определяется качеством ее оптимизатора. Хороший оптимизатор может повысить скорость выполнения запроса на несколько порядков. Качество оптимизатора определяется тем, какие методы преобразований он может использовать, какой статистической и иной информацией о таблицах он располагает, какие методы для оценки стоимости выполнения плана он знает.

Реализация реляционной алгебры средствами оператора SELECT (Реляционная полнота SQL)

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

Оператор декартового произведения

Реляционная алгебра:

Оператор проекции

Реляционная алгебра:

Источник