Материалы представленные на сайте исключительно с целью ознакомления пользователям Интернета и не преследуют коммерческих целей или нарушение авторских прав.
© 2018 - 2024
В обычных языках программирования имеются операторы условного перехода, которые позволяют управлять вычислительным процессом. В языке SQL таким оператором является CASE. Этот оператор возвращает значение и, следовательно, может использоваться в выражениях. Он имеет две основные формы.
Оператор CASE со значениями
Оператор CASE со значениями имеет следующий синтаксис:
CASE проверяемое_значение
WHEN значение1 THEN результат1
WHEN значение2 THEN результат2
...
WHEN значениеN THEN результатN
ELSE результатX
END
В случае, когда проверяемое_значение равно значение1, оператор CASE возвращает значение результат1, указанное после ключевого слова THEN. В противном случае проверяемое_значение сравнивается с значение2, и если они равны, то возвращается значение результат2. В противном случае проверяемое_значение сравнивается со следующим значением, указанным после ключевого слова WHEN и т.д. Если проверяемое_значение не равно ни одному из таких значений, то возвращается значение результатХ, указанное после ключевого слова ELSE.
Ключевое слово ELSE не является обязательным. Если оно отсутствует и ни одно из значений, подлежащих сравнению, не равно проверяемому значению, то оператор CASE возвращает NULL.
Например, вместо номера издания в виде числа выведем номер издания в виде текстовой строки:
SELECT isbn,
CASE edition
WHEN 1 THEN 'first'
WHEN 2 THEN 'second'
WHEN 3 THEN 'third'
ELSE 'large then 3'
END AS edition_num
FROM editions;
Оператор CASE с условием поиска
Вторая форма оператора CASE предполагает его использование при поиске в таблице тех записей, которые удовлетворяют определенному условию:
CASE
WHEN условие1 THEN результат1
WHEN условие2 THEN результат2
...
WHEN условиеN THEN результатN
ELSE результатX
END
Оператор CASE проверяет, истинно ли условие1 для первой записи в таблице. Если да, то CASE возвращает значение результат1. В противном случае для данной записи проверяется условие2. Если оно истинно, то возвращается значение результат2 и т.д. Если ни одно из условий не выполняется, то возвращается значение результатХ, указанное после ключевого слова ELSE.
Ключевое слово ELSE не является обязательным. Если оно отсутствует и ни одно из условий не выполняется, оператор CASE возвращает NULL. После того как оператор, содержащий CASE, выполнится для первой записи, происходит переход к следующей записи. Так продолжается до тех пор, пока не будет обработан весь набор записей.
Например, вместо номера издания в виде числа выведем номер издания в виде текстовой строки:
SELECT isbn,
CASE
WHEN edition = 1 THEN 'first'
WHEN edition = 2 THEN 'second'
WHEN edition = 3 THEN 'third'
ELSE 'large then 3'
END AS edition_num
FROM editions;
Функция COALESCE
В ряде случаев, особенно в запросах на обновление данных (оператор UPDATE), удобно использовать вместо громоздкого оператора CASE более компактную функцию COALESCE.
Функция COALESCE(значение1, значение2, ..., значениеN) принимает список значений, которые могут быть как определенными, так и неопределенными (NULL). Функция возвращает первое определенное значение из списка. Если все значения окажутся неопределенными, функция возвращает NULL.
Например, заменим все NULL-значения поля location на значение 'не определено':
SELECT id, subject,
coalesce(location, 'не определено')
FROM subjects;
Лабораторная работа 2
Подключиться к базе данных booktown и выполнить следующие запросы:
1. Все сведения о книгах.
2. Все сведения об изданиях книг.
3. Код и название всех книг.
4. ISBN, номер издания, дата издания и тип обложки для всех изданий.
5. Неповторяющиеся коды издательств, выпускавших книги.
6. Неповторяющиеся коды тем, по которым написаны книги.
7. Для каждого кода ISBN получить прибыль магазина в случае продажи всех имеющихся в наличии экземпляров.
8. Названия всех книг, упорядоченные по алфавиту.
9. Фамилии и имена всех покупателей, отсортированные по алфавиту (если есть однофамильцы, то они должны быть отсортированы по именам).
10. Список всех ISBN с указанием розничной цены, упорядоченный от самых дорогих к самым дешевым.
11. Код ISBN и затраты магазина на закупку соответствующих имеющихся в наличии книг. Упорядочить по стоимости закупки (всех имеющихся экземпляров).
12. Код ISBN, номер издания и дата издания для всех книг в таком порядке, чтобы сначала шли книги, издававшиеся наибольшее количество раз. Книги с одним и тем же количеством изданий упорядочить по дате издания.
13. Код ISBN и имеющееся количество для десяти книг, в наибольшем количестве имеющихся на складе.
14. Все данные о последних пяти покупках.
15. Все данные о первых семи покупках, не считая трех самых первых (т.е. следующие семь после первых трех).
16. Самая дорогая цена (розничная) книги.
17. Самая малая разница между ценой закупки и ценой продажи.
18. Код ISBN и название типа обложки («твердая» / «мягкая») для всех изданий. Упорядочить по названию типа обложки.
19. Код ISBN, цена закупки, цена продажи, величина наценки, процент наценки и класс всех книг. Если цена продажи более, чем на 10 % превышает цену закупки, то класс – «VIP», иначе – «эконом». Упорядочить по убыванию процента наценки.
УТОЧНЕНИЕ ЗАПРОСА
Секция WHERE
Основное выражение для выборки данных имеет вид:
SELECT списокСтолбцов
FROM списокТаблиц;
Такой запрос возвращает таблицу, полученную из указанной в операторе FROM путем выделения в ней только тех столбцов, которые определены в операторе SELECT.
Для выделения требуемых записей (строк) исходной таблицы используется выражение, следующее за ключевым словом WHERE. Условия поиска в операторе WHERE являются логическими выражениями, т.е. принимающими одно из двух возможных значений – true (ИСТИНА) или false (ЛОЖЬ).
Например, получим список всех книг о компьютерных технологиях. У этих книг поле subject_id равно 4. Соответственно в секцию WHERE включается оператор =, который проверяет это условие:
SELECT *
FROM books
WHERE subject_id = 4;
Выражение subject_id = 4 является истинным (имеет значение true), если в текущей записи таблицы значение столбца subject_id равно 4. В противном случае это выражение ложно (имеет значение false). Одно и то же логическое выражение может быть истинным для одних записей и ложным для других. В SQL логические выражения могут принимать еще и неопределенное значение. Это происходить тогда, когда в выражении некоторые элементы имеют значение NULL. Таким образом, SQL имеет дело не с классической двухзначной, а с трехзначной логикой.
Секция WHERE может содержать несколько условий, объединенных логическими операторами (например, AND или OR) и возвращающими одно логическое значение.
Например, получим все записи для книг о компьютерах, которые, кроме того, что они о компьютерах, написаны Марком Лутцем. Запрос уточняется объединением двух условий при помощи логического оператора AND:
SELECT title
FROM books
WHERE subject_id = 4 AND author_id = 7805;
Первое условие проверяет, что книга посвящена компьютерным технологиям (поле subject_id равно 4), а второе – что автором книги является Марк Лутц (поле author_id равно 7805). Объединение условий уменьшает объем итогового набора.
Получим все книги, посвященные компьютерным технологиям или искусству; в этом случае два условия объединяются логическим оператором OR.
SELECT title
FROM books
WHERE subject_id = 4 OR subject_id = 0;
Прежнее первое условие (книги по компьютерной тематике) объединяется со вторым условием: книги по искусству (поле subject_id равно 0). В результате объем итогового набора увеличивается, каждая его запись удовлетворяет хотя бы одному из этих условий.
Количество условий, объединяемых в секции WHERE, не ограничено, хотя при наличии двух и более условий обычно задается порядок выполнения сравнений при помощи круглых скобок.
Предикаты
При составлении логических выражений используются специальные ключевые слова и символы операций сравнения, которые называют предикатами. Наиболее часто используются предикаты сравнения, такие как (=), (<), (>), (<>), (<=) и (>=). Однако имеются и другие. Далее приведен список всех предикатов:
· предикаты сравнения (=), (<), (>), (<>), (<=) и (>=);
· between;
· in, not in;
· like, not like;
· is null;
· all, some, any;
· exists;
· unique;
· distinct;
· overlaps;
· match;
· similar.
Between
Предикат between позволяет задать выражение проверки вхождения какого-либо значения в диапазон, определяемый граничными значениями. Например,
WHERE cost between 100 and 150
Здесь ключевое слово AND представляет собой логический союз И. Граничные значения (в примере это 100 и 150) входят в диапазон. Причем первое граничное значение должно быть не больше второго.
Эквивалентным приведенному является выражение с предикатами сравнения:
WHERE cost >= 100 and cost <= 150
Кроме данных числового типа, в выражениях с between можно использовать данные следующих типов: символьные, битовые, даты-времени. Например, чтобы выбрать записи, в которых фамилии авторов находятся в диапазоне от A до G, можно использовать такое выражение:
SELECT first_name, last_name
FROM authors
WHERE last_name between 'A' and 'G';
IN и NOT IN
Предикаты IN и NOT IN применяются для проверки вхождения какого-либо значения в заданный список значений. Например, для выборки записей о книгах некоторой тематики можно использовать такое выражение:
SELECT title, author_id
FROM books
WHERE subject_id IN (7805, 0);
Если требуется получить данные о всех книгах не о компьютерах и искусстве, то можно использовать предикат NOT IN:
SELECT title, author_id
FROM books
WHERE subject_id NOT IN (7805, 0);
LIKE и NOT LIKE
Предикаты LIKE и NOT LIKE применяются для проверки частичного соответствия символьных строк. Например, столбец Телефон в некоторой таблице содержит полные номера телефонов, и требуется выбрать лишь те записи, в которых номера телефонов начинаются с 8112 или содержат такое сочетание цифр.
Критерий частичного соответствия задается с помощью двух символов-масок: знака процента (%) и подчеркивания (_). Знак процента означает любой набор символов, в том числе и пустой, а символ подчеркивания – любой одиночный символ.
Например, чтобы выбрать записи об изданиях, у которых isbn начинается с 044, можно использовать такое выражение:
SELECT isbn, edition, publication
FROM edition
WHERE isbn LIKE '044%';
Если требуется выбрать записи о книгах про Python, то для этого подойдет следующее выражение:
SELECT title, author_id
FROM books
WHERE title LIKE '%Python%';
Если необходимо исключить все записи о книгах про Python, то можно воспользоваться следующим выражением:
SELECT title, author_id
FROM books
WHERE title NOT LIKE '%Python%';
SIMILAR
Предикат SIMILAR применяется для проверки частичного соответствия символьных строк. Эту же задачу можно решить и с помощью предиката LIKE, однако в ряде случаев SIMILAR более эффективен.
Пусть в некоторой таблице имеется столбец OC, содержащий названия операционных систем. Нужно выбрать записи, соответствующие Windows XP, Windows Vista и Windows 7. Тогда в выражении запроса можно использовать такой оператор WHERE:
WHERE ОС SIMILAR TO 'Windows (XP|Vista|7)';
IS NULL
Предикат IS NULL применяется для выявления записей, в которых тот или иной столбец не имеет значения. Например, для получения записей о жанрах, для которых не указано местоположение, можно использовать следующее выражение:
SELECT id, subject
FROM subjects
WHERE location IS NULL;
Для получения записей, в которых столбец location содержит некоторые определенные значения (т.е. отличные от NULL) можно использовать аналогичное выражение, но с логическим оператором NOT:
SELECT id, subject
FROM subjects
WHERE location IS NOT NULL;
Не следует использовать предикаты сравнения с NULL, такие как location = NULL.
OVERLAPS
Предикат OVERLAPS используется для определения, перекрываются ли два интервала времени. Выражение с предикатом OVERLAPS можно записать, например, так:
(TIME '12:25:30', TIME '14:30:00') OVERLAPS
(TIME '12:45:00', INTERVAL '2' HOUR)
Поскольку временные интервалы в данном примере пересекаются, то предикат OVERLAPS возвращает значение true.
Предикаты для вложенных запросов ALL, SOME, ANY, EXISTS, UNIQUE, DISTINCT будут рассмотрены в разделе "Сложные запросы".
Предикат MATCH применяется для проверки сохранения ссылочной целостности при модификации данных, т.е. при добавлении, изменении и удалении записей.
Лабораторная работа 3
Выполнить следующие запросы к базе данных booktown:
1. Вся информация о переиздании (т.е. не первое издание, а второе и выше) книг, так чтобы сначала списка шли книги, выдержавшие наибольшее количество переизданий.
2. Вся информация об издании книг с типом h в порядке их публикации.
3. Информация об изданиях книг до XXI века.
4. Информация об имеющихся книгах на складе (stock). Отсортировать по убыванию количества, отсутствующие книги не выводить.
5. Код ISBN и отпускная цена для книг с отпускной ценой в диапазоне от 20 до 30 в порядке возрастания цены.
6. Список покупателей, которых зовут Jean.
7. Код ISBN, имеющееся количество и отпускная цена для книг, имеющихся на складе в количестве, не меньшем 50, или с отпускной ценой, меньшей 20.
8. Код ISBN, тип обложки и дата издания для книг в мягкой (бумажной) обложке, изданных в период с 1990 по 2010 гг. Упорядочить по дате издания.
9. Код ISBN, номер издания и дата издания для книг, изданных впервые или в XXI веке.
10. Названия книг, написанных автором с кодом 1809 по теме с кодом 15.
11. Код ISBN и дата покупки для покупок в период с 10-го по 15 августа 2001 года. Отсортировать по дате покупки.
12. Названия книг и коды авторов для книг, написанных авторами с кодами 1809, 7805, 1212, 15990 и 25041. Упорядочить по коду автора.
13. Названия книг и коды авторов для книг, написанных авторами, чьи номера не 1809, 7805, 1212, 15990 и 25041. Упорядочить по коду автора.
14. Название темы и отдел для тем, расположенных в отделах Main St, Black Raven Dr, Productivity Ave и Creativity St. Отсортировать по названию темы.
15. Фамилия и имя авторов, имена которых начинаются на Marg.
16. Названия книг, в которых присутствует предлог in.
17. Названия книг, в которых присутствует The или the.
18. Названия книг, в которых нет ни The, ни the.
19. Фамилии авторов, чьи имена не известны.
20. Код темы, название темы и отдел для тем с кодом, большим 10, и известным отделом.
МНОГОТАБЛИЧНЫЕ ЗАПРОСЫ
Дата: 2019-02-19, просмотров: 341.