В SQL, как и в другом любом высокоуровневом языке имеются операторы ветвления. И это очень помогает при запросах к базе данных или внесения информации. Благодаря операторам ветвления многие процессы упрощаются и автоматизируются.
Итак, приступим. Сегодня мы будем изучать два оператора CASE и IF… ELSE.
Зайдем в консоль и создадим нашу базу данных, к примеру, зоопарка, а в ней таблицу с животными(id, вид животного, количество, человек отвечающий за вид) и заполним ее:
CREATE DATABASE zoo;
USE zoo;
CREATE TABLE animals (id INT PRIMARY KEY AUTO_INCREMENT, animal VARCHAR(30), number INT, human VARCHAR(30));
DESCRIBE animals;
+--------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| animal | varchar(30) | YES | | NULL | |
| number | int(11) | YES | | NULL | |
| human | varchar(30) | YES | | NULL | |
+--------+-------------+------+-----+---------+----------------+
INSERT INTO animals (animal,number,human) VALUES ('Выдра',5,'Анна');
INSERT INTO animals (animal,number,human) VALUES ('Слон',1,'Сергей');
INSERT INTO animals (animal,number,human) VALUES ('Попугай',20,'Юрий');
INSERT INTO animals (animal,number,human) VALUES ('Черепах',13,'Ирина');
SELECT * FROM animals;
+----+---------+--------+--------+
| id | animal | number | human |
+----+---------+--------+--------+
| 1 | Лось | 10 | Андрей |
| 2 | Выдра | 5 | Анна |
| 3 | Слон | 1 | Сергей |
| 4 | Попугай | 20 | Юрий |
| 5 | Черепах | 13 | Ирина |
+----+---------+--------+--------+
Готово наша база данных создана и заполнена.
Оператор ветвления CASE:
Например, нам нужно обновить таблицу. Теперь за лосей отвечает Степан, за выдр — Федор, за Слоном — Игнат, за Попугаями — Ольга, За Черепахами — Татьяна.
UPDATE animals SET human = CASE WHEN animal = 'Лось' THEN 'Степан' WHEN animal = 'Выдра' THEN 'Федор' WHEN animal = 'Слон' THEN 'Игнат' WHEN animal = 'Попугай' THEN 'Ольга' WHEN animal = 'Черепах' THEN 'Татьяна' END; select * from animals; +----+---------+--------+---------+ | id | animal | number | human | +----+---------+--------+---------+ | 1 | Лось | 10 | Степан | | 2 | Выдра | 5 | Федор | | 3 | Слон | 1 | Игнат | | 4 | Попугай | 20 | Ольга | | 5 | Черепах | 13 | Татьяна | +----+---------+--------+---------+
Это работает так :
UPDATE таблицу SET Изменяемый столбец = CASE
WHEN Животное такое-то THEN человек такой-то
Конец;
Оператор ветвления IF … ELSE:
Оператор IF работает так в функции Если(Колонка = ‘значение’,’Новое значение’,иначе так). Сделаем выборку из наше таблицы:
SELECT id, IF(animal = 'Лось', 'Лоси', 'Животное') AS Вид, number AS Популяция, human AS Наблюдатель FROM animals; +----+---------+-----------+-------------+ | id | Вид | Популяция | Наблюдатель | +----+---------+-----------+-------------+ | 1 | Лоси | 10 | Степан | | 2 | Выдра | 5 | Федор | | 3 | Слон | 1 | Игнат | | 4 | Попугай | 20 | Ольга | | 5 | Черепах | 13 | Татьяна | +----+---------+-----------+-------------+
А теперь сделаем ошибку и увидим, что нам даст ELSE:
SELECT id, IF(animal = 'Белка', 'Белки', 'Животное') AS Вид, number AS Популяция, human AS Наблюдатель FROM animals; +----+----------+-----------+-------------+ | id | Вид | Популяция | Наблюдатель | +----+----------+-----------+-------------+ | 1 | Животное | 10 | Степан | | 2 | Животное | 5 | Федор | | 3 | Животное | 1 | Игнат | | 4 | Животное | 20 | Ольга | | 5 | Животное | 13 | Татьяна | +----+----------+-----------+-------------+
поломался компьютер? Не беда, обращайтесь в Компьютерный Сервис Днепропетровск и вам всегда помогут высококвалифицированные специалисты.
Ошибка небольшая:
SELECT id, IF(animal = ‘Лось’, ‘Лоси’, ‘Животное’) AS Вид, number AS Популяция, human AS Наблюдатель FROM animals;
приведет к немного другим результатам при выводе в терминал
(сами данные в таблице НЕ меняются, конечно же).
результат вывожу латинскими, так как пока не поборол проблему с кириллицей 🙂
+—-+————+———————+————————+
| id | Вид | Популяция | Наблюдатель |
+—-+————+———————+————————+
| 1 | LOSI | 10 | Andrey |
| 2 | ZHIVOTNOE | 5 | Anna |
| 3 | ZHIVOTNOE | 1 | Sergey |
| 4 | ZHIVOTNOE | 20 | Juriy |
| 5 | ZHIVOTNOE | 13 | Irina |
+—-+————+———————+————————+
но никак не
+—-+———+————+————-+
| id | Вид | Популяция | Наблюдатель |
+—-+———+————+————-+
| 1 | Лоси | 10 | Степан |
| 2 | Выдра | 5 | Федор |
| 3 | Слон | 1 | Игнат |
| 4 | Попугай | 20 | Ольга |
| 5 | Черепах | 13 | Татьяна |
+—-+———+————+————-+
В записях id с 2,3,4,5 должно вывести ‘Животное’.
Второй пример, предполагающий ошибку, синтаксически правильный.
И даже логически верный (смотря что и как хотел программист).
SELECT id, IF(animal = ‘Белка’, ‘Белки’, ‘Животное’) AS Вид, number AS Популяция, human AS Наблюдатель FROM animals;
В данном случае, так как нет в поле animal такого значения как ‘Белка’, все значения этого поля естественно выводятся как ‘Животное’.
Может будет кому интересно:
Что интересно с конструкцией CASE в данном примере в лекции:
— если явно не задать изменения всех значений в animas,
то остальные в human получают значение NULL:
Например зададим для изменения (как бы) только две записи в таблице
UPDATE animals SET human = CASE
WHEN animal = ‘Лось’ THEN ‘Степан’
WHEN animal = ‘Выдра’ THEN ‘Федор’
END;
Имеем не совсем то, что хотелось:
SELECT * FROM animals;
+—-+———+———+———+
| id | animal | number | human |
+—-+———+———+———+
| 1 | Лось | 10 | Степан |
| 2 | Выдра | 5 | Федор |
| 3 | Слон | 1 | NULL |
| 4 | Попугай | 20 | NULL |
| 5 | Черепах | 13 | NULL |
+—-+———+———+———+
Захотелось найти решение корректно изменить только нужные записи,
чтобы не изменялись остальные (или правильней- чтоб корректно изменялись только указанные)
Решил сделать по аналогии с конструкцией Case в других языках, применив ESLE:
mysql> update animals set human = case
-> when id = 3 then ‘Игнат’
-> when id = 4 then ‘Ольга’
-> when id = 5 then ‘Татьяна’
-> ELSE human
-> end;
и всё прекрасно получилось- изменились только те записи,
что я и хотел (в данном случае решил возвратить в human равным NULL корректные значения):
SELECT * FROM animals;
+—-+———+———+———+
| id | animal | number | human |
+—-+———+———+———+
| 1 | Лось | 10 | Степан |
| 2 | Выдра | 5 | Федор |
| 3 | Слон | 1 | Игнат |
| 4 | Попугай | 20 | Ольга |
| 5 | Черепах | 13 | Татьяна |
+—-+———+———+———+
Эксперимент в UPDATE с конструкцией IF() дал логичный результат:
mysql> UPDATE animals SET number = (IF (id = 3, 300, 5));
SELECT * FROM animals;
+—-+———+———+———+
| id | animal | number | human |
+—-+———+———+———+
| 1 | Лось | 5 | Степан |
| 2 | Выдра | 5 | Федор |
| 3 | Слон | 300 | Игнат |
| 4 | Попугай | 5 | Ольга |
| 5 | Черепах | 5 | Татьяна |
+—-+———+———+———+
Просто круто!
Теперь можно просто комбинировать различные условия и менять всё, что душа пожелает, и так, как нужно, используя формулы и по условию.
Попробовал такие конструкции сочинить:
mysql> update animals set number = if (id in (2,3), 400, 7);
mysql> update animals set number = case when id >3 then number*2 end;
mysql> update animals set number = case when id in (1,2,3) then id*2-1 else number-id end;
И даже так:
mysql> update animals set number = if (id > 1 and id < 4, 200, 8);