Конструкция оператора SELECT

В SQL, оператор SELECT состоит из блоков, определяющих детали выражения.

В MySQL единственным обязательным блоком является первый: SELECT.

Блоки оператора SELECT:

• SELECT – указывает, что должно быть выбрано. Отчасти соответствует операциям проекции и переименования реляционной алгебры
• FROM – указывает, откуда должны быть выбраны данные. Соответствует аргументу операции реляционной алгебры.
• WHERE – указывает условие, которому выбранные данные должны удовлетворять. Соответствует операции выборки реляционной алгебры
• GROUP BY – используется для группировки результатов по одинаковым значениям столбцов. Часто используется со статистическими запросами.
• HAVING – аналогичен WHERE, но используется со статистическими запросами (WHERE с ними не работает)
• ORDER BY – сортировка выдачи.

Блок SELECT

Как проекция:

SELECT col1, col2, ...

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

Как переименование:

SELECT col1 as name1, col2 as name2, ...

Выбирает перечисленные столбцы и переименовывает их.

Это базовые формы использования блока SELECT. Однако, его возможности не ограничиваются только этим. Кроме прочего, можно подставлять значения или функции (включая операторы). Например,

SELECT 'Hello World!' as Hello;

Вернет такой результат:

А если, допустим, есть табличка operands

Тогда запрос

SELECT a, b, a+b as c FROM operands

вернет

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

Блок FROM

Блок FROM может быть использован для уточнения аргумента блока SELECT. В простейшем варианте, это название таблицы (отношения).

Кроме того, в соответствии с правилами реляционной алгебры, аргументом FROM может являться результат другого запроса (подзапрос). Для использование подзапроса, необходимо назначить ему псевдоним:

SELECT a+b FROM (SELECT 1 as a, 2 as b) as tbl1;

Блок FROM так же используется для реализации соединений и декартовых произведений. Для этого используется бинарный оператор JOIN.

Пусть дана таблица bin:

Тогда запрос

SELECT * FROM bin b1 JOIN bin b2 JOIN bin b3;

вернет декартово произведение bin×bin×bin:

Собственно, JOIN – это оператор декартова произведения. Существуют несколько его вариантов. По умолчанию используется вариант INNER JOIN. Так же есть OUTER FULL JOIN, OUTER LEFT JOIN, OUTER RIGHT JOIN и NATURAL-вариатны – о них чуть позже.

Для получения θ-соединения, используется уточнение к JOIN, называемое ON, и для всех применений работающее как WHERE, но в применении только к операндам JOIN:

SELECT * FROM bin b1 JOIN bin b2 ON b1.a=b2.a;

Важно, что оптимизатор БД ожидает в операторе ON только условия θ-соединения, а в операторе WHERE только условия выборки по результату.

Принципиально,

SELECT * FROM bin b1 JOIN bin b2 ON b1.a=b2.a; SELECT * FROM bin b1 JOIN bin b2 WHERE b1.a=b2.a;

дают одинаковые результаты. Однако, второй вариант менее эффективен, поскольку оптимизатор сначала строит полное декартово произведение, и только потом применяет выборку, в то время как в первом варианте, полное декартово произведение не строится вообще.

Блок WHERE

По сути, применяет реляционную операцию выборки.

Например,

SELECT * FROM bin WHERE a>0;

вернет

Важно, что любые операции переименования применяются после выборки, то есть выражение

SELECT a as b FROM bin WHERE b>0;

является неверным: блок WHERE не “знает” о переименовании, а “знает” только об аргументе FROM.

Как следствие, блок WHERE не может быть использован без блока FROM:

SELECT 1 WHERE TRUE;

не сработает.

Впрочем, если очень хочется, можно использовать “ненастоящую” таблицу dual.

SELECT 1 FROM dual WHERE TRUE;

сработает.

Таблица dual является “пустой таблицей”. При попытке выбрать что-либо непосредственно из нее, возвращается ошибка. Однако, она может быть указана в случаях, когда синтаксис SQL требует указания таблицы.

Аргументом WHERE может быть любое выражение, которое может быть преобразовано в BOOL.

Особого рассмотрения требует значение NULL. Во-первых, любая операция с NULL возвращает NULL. Во-вторых, при преобразовании NULL в BOOL всегда получается FALSE. Таким образом, NULL=NULL это FALSE, NULL!=NULL это FALSE, NOT NULL=NULL это FALSE. Как же найти значения NULL? Существует два специальных оператора:

• x IS NULL
• x IS NOT NULL

Первый срабатывает, если значение x – это NULL, второй – если значение x – не NULL.

Следует заметить, что никакое значение не равно NULL (включая сам NULL).

Блок GROUP BY

Этот блок позволяет группировать выдачу по значению некого выражения. Группировка означает немного не то что можно было бы подумать. Например,

select * from bin b1 join bin b2 join bin b3 group by b3.a;

вернет

Это не совсем то, что можно было бы ожидать.

Смысл группировки в применении статистических или агрегатных функций – эти функции будут применены к каждой из групп.

В частности, функция COUNT возвращает количество ненулевых (не NULL) элементов множества.

select COUNT(*), b3.a from bin b1 join bin b2 join bin b3 group by b3.a;

Агрегатные функции включают:

• AVG(x) – Среднее значение
• BIT_AND(x) – Побитовая конъюнкция
• BIT_OR(x) – Побитовая дизъюнкция
• BIT_XOR(x) – Побитовая строгая дизъюнкция
• COUNT(DISTINCT x) – Возвращает количество различных значений
• COUNT(x) – количество значений (возможно одинаковых)
• GROUP_CONCAT(x) – Объединяет строки группы (конкатенация)
• MAX(x) – Максимальное значение в группе
• MIN(x) – Минимальное значение в группе
• STDDEV_POP(x) – Стандартное отклонение популяции
• STDDEV_SAMP(x) – Стандартное отклонение выборки
• SUM(x) – Суммирование
• VAR_POP(x) – Дисперсия популяции
• VAR_SAMP(x) – Дисперсия выборки

В качестве небольшой справки,

\[V_p(X) = \frac{\sum_{i=1}^n (X_i-X_ \mathrm{avg})^2}{n}\]

\[V_s(X) = \frac{\sum_{i=1}^n (X_i-X_ \mathrm{avg})^2}{n-1}\]

\[S_p(X) = \sqrt{V_p(X)}\]

\[S_s(X) = \sqrt{V_s(X)}\]

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

Существует особая форма COUNT(*), которая получает общее количество записей в выборке. COUNT(x) учитывает только записи, у которых x не NULL. Вообще, значение NULL агрегатными функциями игнорируется.

Пример:

select COUNT(DISTINCT b1.a), b3.a from bin b1 join bin b2 join bin b3 group by b3.a;

select COUNT(DISTINCT b1.a, b2.a), b3.a from bin b1 join bin b2 join bin b3 group by b3.a;

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

Следует заметить, что группировка автоматически приводит к сортировке, что может быть нежелательно ввиду того, что сортировка может быть не быстрой. Чтобы этого избежать, можно добавить ORDER BY NULL в конец выражения.

К GROUP BY можно добавить модифиактор WITH ROLLUP, который так же добавит в конец запись, применяющую соответствующие статистические функции ко всем результирующим записям (то есть, по сути, добавит строку “Итого”). Если в параметрах GROUP BY указано несколько выражений, то WITH ROLLUP посчитает итог для каждого из выражений по очереди.

Например:

    SELECT
        reserves.idClient AS idClient,
        clients.name AS name,
        YEAR(reserves.startTime) AS year,
        SUM(RESERVEPRICE(clients.id, tariffs.idCourt,reserves.duration)) AS income
    FROM
        reserves
        JOIN clients ON clients.id = reserves.idClient
        JOIN tariffs ON clients.isMember = tariffs.isMember
            AND reserves.idCourt = tariffs.idCourt
        JOIN prices ON tariffs.idPrice = prices.id
    GROUP BY reserves.idClient, YEAR(reserves.startTime) WITH ROLLUP;

Надо заметить, что в “итоговых” строках, колонки, по которым строится итог, устанавливаются в NULL. Это, однако не относится к колонкам, по которым нет ни статистики, ни агрегации – там оказывается последнее выражение.

Для использования ROLLUP данные должны быть отсортированы.

Блок HAVING

Поскольку блок SELECT применяется после блока WHERE, подобная конструкция не сработает:

SELECT COUNT(*), b3.a FROM bin b1 JOIN bin b2 JOIN bin b3 GROUP BY b3.a WHERE COUNT(*) < 1;

Для выборки по статистическим функциям, используется блок HAVING.

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

HAVING нельзя использовать вместо WHERE, поскольку оператор HAVING применяется к группам, в то время как WHERE – к записям.

Блок ORDER BY

Этот блок позволяет сортировать результаты выдачи по каким-то выражениям в возрастающем (ascending) и убывающем (descending) порядке.

Общий синтаксис ORDER BY expr1, expr2 ([ASC]|DESC)

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

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

По умолчанию, сортировка производится в возрастающем порядке. Это можно явно указать, добавив спецификатор ASC. В то же время, возможно указать спецификатор DESC для сортировки в убывающем порядке.

Например:

select * from bin b1 join bin b2 join bin b3 ORDER BY b1.a, b2.a, b3.a;

Построение условий

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

Имеется стандартный набор операторов сравнения:

• = – оператор равенства
• != – оператор неравенства
• < – оператор “меньше”
• > – оператор “больше”
• <= – оператор “меньше или равно”
• >= – оператор “больше или равно”

и булевых операторов:

• NOT – инверсия
• AND – конъюнкция
• OR – дизъюнкция
• XOR – строгая дизъюнкция

Кроме того, есть специальные операторы сравнения:

• <=> – сравнение с учетом NULL: NULL <=> NULL ≡ TRUE x <=> NULL ≡ FALSE, x <=> y ≡ x = y
• x BETWEEN low AND high – x находится в диапазоне [low, high]
• x IN set – x является элементом множества set. set может быть задан как набор значений в скобках через запятую, либо как результат подзапроса.
• x NOT IN set – аналог NOT (x IN set)
• x LIKE mask – проверяет соответствие строки x некой маске mask.
• x REGEXP regex – проверяет соответствие строки x регулярному выражению regex.

Примеры:

дана табличка dates (date DATE)

select * from dates where date between '2010-05-08' and '2016-12-31';

select * from dates where date in ('2015-09-23', '2016-12-31');

select * from dates where date in (select * from dates);

Сравнение с помощью подзапросов

Существует возможность выборки по результатам подзапросов. Для этого в SQL есть несколько специальных операторов. Оператор IN был рассмотрен в предыдущем разделе.

Существуют операторы, применяемые непосредственно к подзапросу:

• ANY (или SOME)
• ALL
• EXISTS

Синтаксис ANY/ALL:

SELECT ... WHERE expr op {ANY|ALL} (SELECT ...)

где op – это оператор сравнения, {ANY|ALL} – оператор ANY или ALL.

Оператор ANY означает, что expr удовлетворяет op хотя бы с одной записью из подзапроса. Оператор ALL означает, что expr удовлетворяет op со всеми записями из подзапроса.

Оператор IN по сути эквивалентен = ANY, а NOT IN – != ALL.

Cинтаксис EXISTS:

SELECT ... WHERE EXISTS (SELECT ...)

Если подзапрос возвращает хотя бы одну запись, то EXISTS возвращает TRUE. Иначе – FALSE.

Сравнение строк по маске/регулярному выражению

Сравнение строк по маске осуществляется оператором LIKE. В маске используются следующие обозначения:

• % – любое количество любых символов
• _ – ровно один любой символ

Таким образом, например, маска ___-__-__ выберет все строки, состоящие из 9 символов, в которых 4-й и 7-й символы – дефисы. F% выберет все строки, начинающиеся на F, а, скажем, %воз% – все строки, содержащие подстроку воз.

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

• ^[A-Z] – строка начинается на буквы с A по Z
• 20[0-9]5 – строка содержит какую-либо подстроку из 2005, 2015, 2025, … 2095.
• a.*z – строка содержит подстроку, начинающуюся на a и заканчивающуюся z
• a.z – строка содержит подстроку, начинающуюся на a и заканчивающуюся z из трех символов

Соответствие символам маски следующее: | Маска | Регулярное выражение | |:——|:———————| | % | .* | | _ | . |

Следует заметить, что для точного соответствия, регулярное выражение должно иметь маркеры начала (^) и конца ($) строки, поскольку маска всегда проверяется на совпадение со всей строкой, в то время как регулярное выражение – на совпадение с любой подстрокой.

В общем и целом, регулярные выражения – очень мощный инструмент, однако и очень сложный. В 80-х и начале 90-х годов была популярна шутка, которая в целом отражает суть регулярных выражений:

У Вас есть проблема. Вы решили использовать регулярные выражения для ее решения. Теперь у Вас две проблемы.

Типы соедниений

Как уже говорилось, есть несколько типов соединений

• “Внутреннее”
• Три “внешних”
• “Естественные” варианты

С последним разобраться проще всего. Если Вы пишите NATURAL ... JOIN, то спецификатор ON автоматически определяется следующим образом: все атрибуты, имеющие одинаковые имена, должны иметь одинаковые значения.

В общем, в практике стараются избегать NATURAL-соединений, поскольку они могут приводить к ошибкам за счет “встроенной” неявности.

С “внутренним” соединением тоже должно быть все понятно: это вариант “по умолчанию” и по сути есть выборка по декартову произведению.

Внешних существует три варианта:

• LEFT OUTER JOIN ... ON
• RIGHT OUTER JOIN ... ON
• FULL OUTER JOIN ... ON

Каждое из них включает в результат не только записи, удовлетворяющие условию ON, но и некоторые другие:

• LEFT включает все записи левой таблицы. Для тех, для которых условие ON не выполняется, все атрибуты правой таблицы устанавливаются в NULL
• RIGHT включает все записи правой таблицы. Для тех, для которых условие ON не выполняется, все атрибуты левой таблицы устанавливаются в NULL
• FULL по сути является объединением двух предыдущих.

Следует заметить, что MySQL не поддерживает FULL OUTER JOIN.

Примеры:

есть две таблички

select categories.name, count(posts.id) from categories left join posts on catId=categories.id group by categories.id;

В то время как

select categories.name, count(posts.id) from categories join posts on catId=categories.id group by categories.id;

Ограничение числа записей

MySQL имеет расширение LIMIT, позволяющее ограничить число записей, выбираемых оператором SELECT. Синтаксис такой:

SELECT ... LIMIT max_num OFFSET shift

max_num – максимальное количество выбираемых записей, shift – количество записей, которые надо пропустить.

Это расширение доступно не везде. MS SQL Server имеет аналогичный синтаксис SELECT TOP, а Oracle использует атрибут ROWNUM.

Объединение

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

Для объединения, все объединяемые запросы должны иметь одинаковое количество колонок. Названия колонок будут взяты из первого запроса. Типы будут взяты либо из первого запроса, либо будут автоматически приведены к “общему знаменателю”.

Существует два типа объединения: DISTINCT и ALL. В случае первого, в объединении будут только различные записи, то есть, повторяющиеся записи будут удалены. В случае второго, все записи будут включены в результат.

Синтаксис:

SELECT ...
UNION
SELECT ...

SELECT ...
UNION DISTINCT
SELECT ...

SELECT ...
UNION ALL
SELECT ...

Просто UNION и UNION DISTINCT – синонимы.

Условия в выражениях SQL

Выражения SQL допускают ветвление по условию. Оно несколько отличается от более привычного процедурного ветвления (как, например, в Java или C++) тем, что результатом ветвления является значение. Реализуется оно при помощи выражений CASE и IF.

CASE позволяет проверять несколько условий на истинность, и в зависимости от них возвращает некое выражение. Общий синтаксис:

CASE expr
  WHEN expr_comp_1 THEN expr_res_1
  WHEN ...
  ELSE expr_res_n
END

либо

CASE
  WHEN cond_1 THEN expr_res_1
  WHEN ...
  ELSE expr_res_n
END

Здесь expr – это некое выражение, которое сравнивается с expr_comp_*, и, при совпадении, получается expr_res_*. ELSE срабатывает, если других совпадений не найдено.

Если подходящего условия не найдено, и отсутствует блок ELSE, то результатом будет NULL.

Пример:

SELECT (CASE 1
    WHEN 0 THEN 'YES'
    WHEN 2 THEN 'YES'
    ELSE 'NO'
END) AS value;

вернет

А, скажем,

SELECT (CASE
  WHEN NOW() > '1999-12-31' THEN 'Third'
    ELSE 'Second'
END) AS Millenium;

вернет

Второй условный оператор – это IF. Имеет очень простой синтаксис:

IF(cond, expr_true, expr_false);

Если условие cond истинно, то результатом будет expr_true, иначе – expr_false.

Так же для удобства есть варианты IFNULL и NULLIF:

IFNULL(expr1, expr2)

возвращает expr1, если оно не NULL, иначе – expr2.

NULLIF(expr1, expr2)

возвращает NULL, если expr1=expr2, иначе возвращает expr1.

CASE и IF часто используют для составления так называемых “перекрестных запросов” – запросов, в которых значения используются в качестве колонок. В новых стандартах SQL существует операция PIVOT, которая делает примерно то же самое, однако она пока поддерживается не везде.

Пример с IF:

SELECT
    reserves.idClient AS idClient,
    clients.name AS name,
    YEAR(reserves.startTime) AS year,
    SUM(NULLIF(MONTH(reserves.startTime) = 1, RESERVEPRICE(clients.id, tariffs.idCourt,reserves.duration), NULL)) AS `1`,
    SUM(IF(MONTH(reserves.startTime) = 2, RESERVEPRICE(clients.id, tariffs.idCourt,reserves.duration), NULL)) AS `2`,
    SUM(IF(MONTH(reserves.startTime) = 3, RESERVEPRICE(clients.id, tariffs.idCourt,reserves.duration), NULL)) AS `3`,
    SUM(IF(MONTH(reserves.startTime) = 4, RESERVEPRICE(clients.id, tariffs.idCourt,reserves.duration), NULL)) AS `4`,
    SUM(IF(MONTH(reserves.startTime) = 5, RESERVEPRICE(clients.id, tariffs.idCourt,reserves.duration), NULL)) AS `5`,
    SUM(IF(MONTH(reserves.startTime) = 6, RESERVEPRICE(clients.id, tariffs.idCourt,reserves.duration), NULL)) AS `6`,
    SUM(IF(MONTH(reserves.startTime) = 7, RESERVEPRICE(clients.id, tariffs.idCourt,reserves.duration), NULL)) AS `7`,
    SUM(IF(MONTH(reserves.startTime) = 8, RESERVEPRICE(clients.id, tariffs.idCourt,reserves.duration), NULL)) AS `8`,
    SUM(IF(MONTH(reserves.startTime) = 9, RESERVEPRICE(clients.id, tariffs.idCourt,reserves.duration), NULL)) AS `9`,
    SUM(IF(MONTH(reserves.startTime) = 10, RESERVEPRICE(clients.id, tariffs.idCourt,reserves.duration), NULL)) AS `10`,
    SUM(IF(MONTH(reserves.startTime) = 11, RESERVEPRICE(clients.id, tariffs.idCourt,reserves.duration), NULL)) AS `11`,
    SUM(IF(MONTH(reserves.startTime) = 12, RESERVEPRICE(clients.id, tariffs.idCourt,reserves.duration), NULL)) AS `12`,
FROM
    reserves
    JOIN clients ON clients.id = reserves.idClient
    JOIN tariffs ON clients.isMember = tariffs.isMember
        AND reserves.idCourt = tariffs.idCourt
    JOIN prices ON tariffs.idPrice = prices.id
GROUP BY reserves.idClient , YEAR(reserves.startTime);

Пример с использованием PIVOT

SELECT idClient, name, year, `1`, `2`, `3`, `4`, `5`, `6`, `7`, `8`, `9`, `10`, `11`, `12` FROM
  (SELECT reserves.idClient AS idClient,
          clients.name AS name,
          YEAR(reserves.startTime) AS year,
          MONTH(reserves.startTime) AS month,
          RESERVEPRICE(clients.id, tariffs.idCourt,reserves.duration) as cost
        ) AS p
  PIVOT
  (
    SUM(cost)
    FOR month IN
    ( 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12 )
  ) AS pvt;

INTERSECT и EXCEPT

Стандарт SQL предполагает еще два оператора работы с множествами, помимо UNION/UNION ALL.

INTERSECT строит пересечение двух множеств, а EXCEPT – разность.

Однако, MySQL не поддерживает эти операторы.

Тем не менее, INTERSECT по сути представляет собой NATURAL INNER JOIN, поскольку выбираются только записи с совпадающими атрибутами, а EXCEPT можно записать как выборку по NATURAL LEFT JOIN:

SELECT a.* FROM a NATURAL LEFT JOIN b WHERE b.pk IS NULL

где b.pk – первичный ключ таблицы b (который, ясно, может быть NULL только в результате OUTER JOIN).

Так же это можно реализовать через подзапросы и EXISTS/NOT EXISTS, однако это в большинстве случаев будет менее эффективно.