Выражение CASE – условный оператор языка SQL
Данный оператор позволяет осуществить проверку условий и возвратить в зависимости от выполнения того или иного условия тот или иной результат.
Оператор CASE имеет 2 формы:
| Первая форма: | Вторая форма: |
| CASE WHEN условие_1 THEN возвращаемое_значение_1 … WHEN условие_N THEN возвращаемое_значение_N [ELSE возвращаемое_значение] END | CASE проверяемое_значение WHEN сравниваемое_значение_1 THEN возвращаемое_значение_1 … WHEN сравниваемое_значение_N THEN возвращаемое_значение_N [ELSE возвращаемое_значение] END |
В качестве значений здесь могут выступать и выражения.
примере первую форму CASE:
SELECT
ID,Name,Salary,
CASE
WHEN Salary>=3000 THEN 'ЗП >= 3000'
WHEN Salary>=2000 THEN '2000 <= ЗП < 3000'
ELSE 'ЗП < 2000'
END SalaryTypeWithELSE,
CASE
WHEN Salary>=3000 THEN 'ЗП >= 3000'
WHEN Salary>=2000 THEN '2000 <= ЗП < 3000'
END SalaryTypeWithoutELSE
FROM Employees
WHEN-условия проверяются последовательно, сверху-вниз. При достижении первого удовлетворяющего условия дальнейшая проверка прерывается и возвращается значение, указанное после слова THEN, относящегося к данному блоку WHEN.
Если ни одно из WHEN-условий не выполняется, то возвращается значение, указанное после слова ELSE (что в данном случае означает «ИНАЧЕ ВЕРНИ …»).
Если ELSE-блок не указан и не выполняется ни одно WHEN-условие, то возвращается NULL.
И в первой, и во второй форме ELSE-блок идет в самом конце конструкции CASE, т.е. после всех WHEN-условий.
Разберем на примере вторую форму CASE:
Допустим, на новый год решили премировать всех сотрудников и попросили вычислить сумму бонусов по следующей схеме:
Сотрудникам ИТ-отдела выдать по 15% от ЗП;
Сотрудникам Бухгалтерии по 10% от ЗП;
Всем остальным по 5% от ЗП.
Используем для данной задачи запрос с выражением CASE:
SELECT
ID,Name,Salary,DepartmentID,
-- для наглядности выведем процент в виде строки
CASE DepartmentID -- проверяемое значение
WHEN 2 THEN '10%' -- 10% от ЗП выдать Бухгалтерам
WHEN 3 THEN '15%' -- 15% от ЗП выдать ИТ-шникам
ELSE '5%' -- всем остальным по 5%
END NewYearBonusPercent,
-- построим выражение с использованием CASE, чтобы увидеть сумму бонуса
Salary/100*
CASE DepartmentID
WHEN 2 THEN 10 -- 10% от ЗП выдать Бухгалтерам
WHEN 3 THEN 15 -- 15% от ЗП выдать ИТ-шникам
ELSE 5 -- всем остальным по 5%
END BonusAmount
FROM Employees
Здесь делается последовательная проверка значения DepartmentID с WHEN-значениями. При достижении первого равенства DepartmentID с WHEN-значением, проверка прерывается и возвращается значение, указанное после слова THEN, относящегося к данному блоку WHEN.
Соответственно, значение блока ELSE возвращается в случае, если DepartmentID не совпал ни с одним WHEN-значением.
Если блок ELSE отсутствует, то в случае несовпадения DepartmentID ни с одним WHEN-значением будет возвращено NULL.
Вторую форму CASE несложно представить при помощи первой формы:
SELECT
ID,Name,Salary,DepartmentID,
CASE
WHEN DepartmentID=2 THEN '10%' -- 10% от ЗП выдать Бухгалтерам
WHEN DepartmentID=3 THEN '15%' -- 15% от ЗП выдать ИТ-шникам
ELSE '5%' -- всем остальным по 5%
END NewYearBonusPercent,
-- построим выражение с использованием CASE, чтобы увидеть сумму бонуса
Salary/100*
CASE
WHEN DepartmentID=2 THEN 10 -- 10% от ЗП выдать Бухгалтерам
WHEN DepartmentID=3 THEN 15 -- 15% от ЗП выдать ИТ-шникам
ELSE 5 -- всем остальным по 5%
END BonusAmount
FROM Employees
Так что, вторая форма – это всего лишь упрощенная запись для тех случаев, когда нам нужно сделать сравнение на равенство, одного и того же проверяемого значения с каждым WHEN-значением/выражением.
Представления
Основными структурными единицами в реляционных базах данных являются таблицы. Однако язык SQL представляет ещё один способ организации данных – представления.
Представление - это виртуальная таблица, которая сама по себе не существует, но для пользователя выглядит таким образом, как будто она существует. Представление не поддерживаются его собственными физическими хранимыми данными. Вместо этого в каталоге таблиц хранится определение, оговаривающее, из каких столбцов и строк других таблиц оно должно быть сформировано при реализации SQL-предложения на получение данных из представления или на модификацию таких данных.
Представления – это запрос на выборку, которому присваивается уникальное имя и который можно сохранять или удалять из базы данных. Содержимое представлений выбирается из таблиц или существующих представлений.
Когда СУБД MySQL встречает в запросе ссылку на представление, она отыскивает его определение, сохранённое в базе данных. Затем происходит преобразование пользовательского запроса с участием представления в эквивалентный запрос с исходными таблицами. После этого выполняется запрос. Таким образом клиент может работать с представлениями так, как будто это независимые таблицы.
Клиент, обращаясь к представлению, будет видеть только столбцы результирующей таблицы, при этом не имеет значения сколько столбцов в исходной таблице и является ли запрос, лежащий в основе представления одно- или многотабличным. Кроме этого, клиенту можно запретить обращаться к исходным таблицам, но снабдить привилегиями обращения к представлениям.
Преимущества использования представлений:
· Безопасность – каждому пользователю можно разрешить доступ к небольшому числу представлений, содержащих только ту информацию, которую ему позволено знать;
· Простота запросов – с помощью представления можно извлечь данные из нескольких таблиц и представить их как одну таблицу, заменяя запрос ко многим таблицам в однотабличный запрос к представлению;
· Простота структуры – представления позволяют создать для каждого пользователя собственную структуру базы данных, отображая только те данные, которые ему нужны;
· Защита от изменений – в связи с оптимизацией скорости, таблицы и их структура могут быть изменены или переименовываться. Представления позволяют создавать виртуальные таблицы со старыми именами и структурой, помогая избегать модификации внешней прикладной программой;
· Обновление данных – возможность производить обновления данных в представлениях и будут обновляться данные в исходных таблицах. Однако это возможно только с простыми представлениями, сложные представления доступны только для выборки.
Синтаксис предложения CREATE VIEW имеет вид
CREATE VIEW имя_представления
[(столбец[,столбец]...)]
AS подзапрос
[WITH CHECK OPTION];
где подзапрос, следующий за AS и являющийся определением данного представления, не исполняется, а просто сохраняется в каталоге;
необязательная фраза "WITH CHECK OPTION" (с проверкой) указывает, что для операций INSERT и UPDATE над этим представлением должна осуществляться проверка, обеспечивающая удовлетворение WHERE фразы подзапроса;
список имён столбцов должен быть обязательно определён лишь в тех случаях, когда:
а) хотя бы один из столбцов подзапроса не имеет имени (создаётся с помощью выражения, SQL-функции или константы);
б) два или более столбцов подзапроса имеют одно и то же имя;
если же список отсутствует, то представление наследует имена столбцов из подзапроса.
Например, создадим представление Мясные_блюда