MySQL 5.0. Библиотека программиста

Гольцман Виктор

x <Оператор сравнения> ALL <Вложенный запрос>

может быть одно из следующих значений:

• 1 (TRUE) – если условие х <Оператор сравнения> у выполнено для всех у, выданных подзапросом, а также в случае, если подзапрос возвращает пустой результат;

• 0 (FALSE) – если среди выданных подзапросом значений есть такое значение у, для которого выражение х <Оператор сравнения> у ложно (FALSE);

• NULL – в остальных случаях.

Например, запрос

SELECT * FROM Customers WHERE 5000 < ALL (SELECT amount FROM Orders WHERE Customers.id = customer_id);

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

SELECT * FROM Customers

WHERE 5000 < ALL

(SELECT amount FROM Orders

WHERE Customers.id = customer_id)

AND EXISTS

(SELECT amount FROM Orders

WHERE Customers.id = customer_id);

Как

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

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

Логические операторы

Логические операторы позволяют построить сложное условие отбора на основе операторов сравнения. Операнды логических операторов рассматриваются как логические значения: TRUE, FALSE и NULL. При этом число 0 и нулевая дата и/или время («0000-00-00 00:00:00») считаются ложными значениями (FALSE), а отличные от нуля числа и даты – истинными значениями (TRUE) (более подробно об этом рассказывалось в пункте «Оператор х IS у, где у – TRUE, FALSE, UNKNOWN или NULL»). Начнем с изучения оператора AND.

Оператор x AND y

Оператор AND («и») возвращает следующие значения:

• 1 (TRUE) – если оба операнда – истинные значения;

• 0 (FALSE) – если один или оба операнда – ложные значения;

• NULL – в остальных случаях.

Иными словами, если вы соединили два условия отбора с помощью оператора AND, то составное условие выполняется только тогда, когда выполняются одновременно оба составляющих условия.

Например, запрос

SELECT * FROM Customers WHERE name LIKE \'ООО%\' AND rating>1000;

не выводит ни одной строки. В таблице Customers (Клиенты) есть имена, начинающиеся с «ООО», и рейтинги, превышающие 1000, но ни одна из строк не удовлетворяет обоим этим условиям одновременно.

Пара символов && является синонимом оператора AND. Следующий оператор, который мы рассмотрим, – это оператор OR.

Оператор x OR y

Оператор OR («или») возвращает следующие значения:

• 1 (TRUE) – если один или оба операнда – истинные значения;

• 0 (FALSE) – если оба операнда – ложные значения;

• NULL – в остальных случаях.

Иными словами, если вы соединили два условия отбора с помощью оператора OR, то составное условие выполняется, если выполняется хотя бы одно из составляющих условий.

Например, запрос

SELECT * FROM Customers WHERE name LIKE \'ООО%\' OR rating>1000;

выводит строки таблицы Customers (Клиенты), в которых имя начинается с «ООО», а также строки, в которых рейтинг больше 1000 (см. табл. 3.6). Пара символов | |является синонимом оператора OR. Следующий оператор, который мы рассмотрим, – это оператор XOR. Оператор x XOR y

Оператор XOR («исключающее или») возвращает следующие значения:

• 1 (TRUE) – если один из операндов – истинное значение, а другой – ложное;

• 0 (FALSE) – если оба операнда либо истинные значения, либо ложные;

• NULL – если хотя бы один из операндов равен NULL.

Иными словами, если вы соединили два условия отбора с помощью оператора XOR, то составное условие выполняется, если выполняется ровно одно из составляющих условий.

Например, запрос

SELECT * FROM Customers WHERE name LIKE \'ООО%\' XOR rating>500;

выводит строки таблицы Customers, в которых имя начинается с «ООО», а также те строки, в которых рейтинг больше 500, за исключением тех строк, в которых эти условия выполняются одновременно (см. табл. 3.15). Наконец, рассмотрим последний логический оператор – NOT. Оператор NOT x

Оператор NOT («не», то есть «отрицание») возвращает следующие значения:

• 1 (TRUE) – если операнд – ложное значение;

• 0 (FALSE) – если операнд – истинное значение;

• NULL – если операнд равен NULL.

Иными словами, условие отбора, созданное с помощью оператора NOT, выполняется, если исходное условие не выполнено и не равно NULL.

Например, запрос

SELECT * FROM Customers WHERE NOT (name LIKE \'ООО%\' OR rating>1000);

выводит те строки таблицы Customers, для которых условие name LIKE \'ООО% OR rating>10 0 0 не выполнено и которые, следовательно, не были выведены запросом из пункта «Оператор х OR у». Таким образом, запрос возвращает результат, представленный в табл. 3.1.

Завершая изучение операторов и функций проверки условий, обсудим еще несколько функций, используемых для сравнения различных величин. Эти функции отличаются от операторов, рассмотренных в подразделе «Операторы сравнения», тем, что возвращаемое ими значение не обязательно логическое.

Операторы и функции, основанные на сравнении

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

• LEAST( a 1, a 2,…, an )

Данная функция возвращает наименьший из своих аргументов (либо NULL, если один из аргументов равен NULL). Например, выражение LEAST(000 "Кускус",\'Петров\',\'Крылов\') возвращает значение «Крылов». Отметим, что в функции LEAST можно указать только фиксированное количество аргументов.

Например, невозможно получить первое в алфавитном порядке имя клиента с помощью запроса SELECT LEAST(name) FROM Customers; вместо этого необходимо использовать групповую функцию MIN (о ней вы узнаете в разделе «Групповые функции»).

• GREATEST( a 1, a 2,…, an ).

Данная функция возвращает наибольший из своих аргументов (либо NULL, если по крайней мере один из аргументов равен NULL). Например, выражение GREATEST (ООО «Кускус», Петров, Крылов) возвращает значение «Петров». Как и в функции LEAST, в функции GREATEST можно указать только фиксированное количество аргументов. Например, невозможно получить последнее в алфавитном порядке имя клиента с помощью запроса SELECT GREATEST(name) FROM Customers; вместо этого необходимо использовать групповую функцию MAX (см. раздел «Групповые функции»).

• INTERVAL( a , b 1, b 2,…, bn ), где b 1 < b 2 < … < bn.

Функция INTERVAL возвращает порядковый номер наибольшего из чисел b., не превосходящих числа a:

• bi ≤ a < bi +1 – функция возвращает номер i ;

• a < b 1 – функция возвращает значение 0:

• a > bn – функция возвращает значение n ;

• a равно NULL – функция возвращает значение -1.

Все аргументы этой функции являются целыми числами (если вы укажете аргумент с другим типом данных, он будет преобразован в целочисленное значение). Чтобы функция возвращала корректный результат, необходимо, чтобы значения bi были упорядочены, то есть выполнялось условие b1 < b2 <… < bn. Например, выражение INTERVAL(1500, 1000, 2000, 3000) возвращает значение 1.

• GOALESCE ( a 1, a 2,…, an )

Данная функция возвращает первый из аргументов, который отличен от NULL (а если все аргументы равны NULL, то возвращает значение NULL). Например, выражение COALESCE(NULL,1/0,\'TeKCT\') возвращает значение «Текст», поскольку это первый аргумент, отличный от NULL (при делении на 0 результатом является NULL).

• IF(a,b,c).

Данная функция проверяет, является ли истинным логическое значение или выражение a. Если a истинно (то есть является числом, датой или временем, отличным от нулевых), то функция возвращает значение b, а если a ложно или равно NULL, функция возвращает значение c. Например, если требуется удвоить те рейтинги клиентов, которые превышают 1000, это можно сделать с помощью команды

UPDATE Customers SET rating = IF(rating>1000,rating*2,rating);

• IFNULL(a,b). Данная функция возвращает значение a, если это значение отлично от NULL, и значение b, если a равно NULL. Например, если требуется всем клиентам, чей рейтинг не указан (равен NULL), присвоить рейтинг 500, это можно сделать с помощью команды

UPDATE Customers SET rating = IFNULL(rating,500);

• NULLIF(a,b). Данная функция возвращает значение NULL, если a = b, и значение a в противном случае. Например, если требуется выполнить операцию, обратную операции из предыдущего пункта, то есть всем клиентам с рейтингом 500 присвоить неопределенный рейтинг, это можно сделать с помощью команды

UPDATE Customers SET rating = NULLIF(rating,500);

• CASE x WHEN a1 THEN b1.

[WHEN a2 THEN b2]

…

[WHEN an THEN bn]

[ELSE b0]

END

или

CASE WHEN x1 THEN b1

[WHEN x2 THEN b2]

…

[WHEN xn THEN bn]

[ELSE b0]

END

Оператор CASE обеспечивает последовательную проверку списка условий и возвращает значение в зависимости от того, какое из условий выполнено. В первом варианте значение выражения х сравнивается со значениями a1, a2,…, an :

• если х = ai , то оператор возвращает значение bi;

• если значение выражения х не совпало ни с одним из a, то оператор возвращает значение b0 , заданное с помощью параметра ELSE;

• если значение выражения х не совпало ни с одним из ai , а параметр ELSE не задан, то оператор возвращает значение NULL.

Во втором варианте последовательно проверяется истинность логических выражений хi:

• если хi истинно, то оператор возвращает значение bi;

• если ни одно из выражений хi не является истинным, то оператор возвращает значение b0 , заданное с помощью параметра ELSE;

• если ни одно из выражений хi не является истинным, а параметр ELSE не задан, то оператор возвращает значение NULL.

Например, запрос