К операторам и функциям общего назначения относятся:
- Функция IFNULL;
- Функция NULLIF;
- Функция IF;
- Оператор CASE.
Функция IFNULL
Любые арифметические действия со значением NULL всегда возвращают значение NULL. Это не всегда удобно, поэтому часто возникает следующая задача:
- Проверить содержит ли поле значение NULL;
- Если содержит, то заменить его на более удобное значение.
Для решения таких задач используется функция IFNULL.
IFNULL(выражение1,выражение2);
Если выражение1 равно NULL, то функция возвращает выражение2. Если выражение1 НЕ равно NULL, то функция возвращает выражение1.
В таблице abiturient хранятся оценки по двум экзаменам, сданным абитуриентами.
Необходимо подсчитать рейтинг абитуриентов, который формируется как сумма баллов по двум экзаменам.
Если попробовать решить задачу запросом
SELECT Familia,Imya, exam1+exam2 AS reit FROM abiturient;
то результат получится следующим:
У абитуриентов, которые сдали оба экзамена рейтинг рассчитывается верно, но у тех, кто сдал только один экзамен, рейтинг равен NULL. Поэтому запрос следует изменить, так, чтобы вместо NULL было значение 0, которое можно использовать в арифметических операциях:
SELECT Familia,Imya,IFNULL(exam1,0)+IFNULL(exam2,0) AS reit FROM abiturient;
Теперь результат получается более корректным:
Функция NULLIF
NULLIF(выражение1, выражение2);
Если выражение1=выражение2, то функция вернет NULL, в противном случае функция вернет выражение1.
В таблице tovar хранится оптовая цена – price1 и розничная цена – price 2. Необходимо найти товары, для которых оптовая и розничная цена равны, а напротив остальных товаров вывести оптовую цену:
SELECT tovar_name, NULLIF(price1,price2) FROM tovar;
Функция IF
IF(логическое_выражение, выражение1, выражение2)
Функция проверяет истинность логического выражения. Если оно истинно, то функция вернет выражение1, если ложно, то выражение2.
Если оптовая цена (price1) товара больше 300руб., то необходимо сделать скидку 10%.
SELECT tovar_name, IF(price1
Функции IF можно вкладывать друг в друга для реализации более сложных условий. В этом случае вложенная функция IF ставится либо на место выражения1 либо на место выражения2.
Назначим товарам с оптовой ценой больше 300 руб. и меньше 500 руб. скидку 10%, а товарам с ценой больше 500 руб. – скидку 20%.
SELECT Tovar_name, IF(price1
При большом количестве условий многократное вложение IF нецелесообразно, так как такая запись будет плохо поддаваться прочтению. Для этого лучше воспользоваться оператором CASE.
Оператор CASE
Оператор CASE используется в двух формах.
-
Первая форма оператора CASE
CASE выражение1 WHEN
[значение1] THEN результат1
[WHEN [значение2] THEN результат2 ...]
[ELSE результатN]
END
Пример 5В таблице levels хранится список квалификационных уровней вуза:
Необходимо для каждого уровня создать сокращенную запись. Для уровня «бакалавр» - «б», для уровня «магистр» - «м», для остальных случаев - «б/нс». Эта задача решается следующим запросом:
SELECT level, CASE level
WHEN бакалавр"WHEN "б"
WHEN "магистр" WHEN "м"
ELSE "б/нс" end
FROM levels;
Результат:
-
Вторая форма оператора CASE
CASE
WHEN [условие1] THEN результат1
[WHEN [условие2] THEN результат2 ...]
[ELSE результатN] END
Если первая форма оператора позволяет проверить равно ли некоторое выражение ряду значений, то вторая больше подходит для проверки сложных условий с различными операторами сравнения.
Пример 6Задача из примера5 может быть решена также следующим запросом:
SELECT level, CASE
WHEN level="бакалавр"THEN "б"
WHEN level= "магистр"THEN "м"
ELSE level="б/нс" END
FROM levels;
Пример 7Задачу из примера4 можно решить также следующим запросом:
SELECT tovar_name,CASE
WHEN price1
WHEN price1 > 300 AND price1
ELSE 0.8*price1
END
FROM tovar;
