Предикаты для вложенных запросов

В выражении WHERE, не считая перечисленных предикатов, смогут кроме этого употребляться выражения с оператором SELECT. Любое выражение, начинающееся с оператора SELECT, есть запросом к базе данных. В случае если в выражении видится еще хотя бы один оператор SELECT, то он задает запрос, положенный в первоначальный. Положенные запросы кроме этого именуют подзапросами.

Положенный запрос есть простым запросом, таким же, как и рассмотренные ранее. Он возвращает таблицу (комплект записей), которая, так или иначе, употребляется для создания ответа на главный запрос. Так, к примеру, подзапрос употребляется, в то время, когда для выборки данных в одной таблице нужно выполнить проверки по второй таблице. Для данной цели подходят перечисленные потом особые предикаты.

ALL, SOME, ANY

Предикаты ALL (все), SOME (некий), ANY (любой) в конечном итоге являются кванторами , узнаваемые в математической логике как существования и кванторы всеобщности. ALL — квантор всеобщности, a SOME и ANY, являющиеся синонимами в SQL, — кванторы существования. Увидим, что в переводе на русский слово ANY следовало бы осознавать как квантор всеобщности (любой свидетельствует все), но в английском имеется разные варианты значений этого слова. Использование главного слова ALL направляться осознавать как для всех либо для каждого. Главные слова SOME и ANY направляться осознавать как хотя бы какой-нибудь один. Как бы то ни было, в языке SQL главные слова SOME и ANY имеют однообразный суть, отличающийся от ALL.

Примечание. Выражения с главными словами ALL, SOME (ANY) соответствуют логическим выражениям с кванторами и, как таковые, смогут именоваться предикатами.

EXISTS

Обработка данных довольно часто имеет несколько этапов. Так, сперва производится некая выборка данных, а после этого выполняются какие-то манипуляции с ней. Но, делая запрос на выборку, мы далеко не всегда можем быть уверенными, что ответ содержит хотя бы одну непустую строчок. В случае если ответ на запрос безлюден, то бессмысленно создавать предстоящую обработку данных. Так, полезно знать, содержит ли ответ на запрос какие-либо эти. Для этого рекомендован предикат EXISTS (существует). Он делается подлинным лишь тогда, кода результатная таблица, полученная в ответ на запрос, содержит хотя бы одну запись.

UNIQUE

Предикат UNIQUE (неповторимый) имеет такой же суть, как и EXISTS, но наряду с этим для его истинности требуется, дабы все записи в результатной таблице не только существовали, но и были неповторимы (т. е. не повторялись).

DISTINCT

Предикат DISTINCT (отличающийся, особенный) практически такой же, как UNIQUE. Отличие этих предикатов обнаруживается применителъно к значениям NULL. Так, в случае если в результатной таблице все записи неповторимы (предикат UNIQUE подлинен), то и предикат DISTINCT также подлинен (т. е. в случае если все записи неповторимы, то они и отличающиеся). Иначе, в случае если в результатной таблице имеются, хотя бы две неизвестные записи, то предикат DISTINCT фальшив, не смотря на то, что предикат UNIQUE подлинен.

OVERLAPS

Предикат OVERLAPS (перекрывает) употребляется для определения, перекрываются ли два промежутка времени. Промежуток времени возможно задать двумя методами: в виде начального и конечного моментов либо в виде длительности и начального момента. Примеры задания промежутка времени:

  • (TIME ’12:25:30′, TIME ’14:30:00′) —промежуток, заданный начальным и конечным моментами;
  • (TIME ’12:45:00′, INTERVAL ‘2’ HOUR)—промежуток, заданный длительностью и начальным моментом в часах.

Выражение с предикатом OVERLAPS возможно записать, к примеру, так:

(TIME ’12:25:30′, TIME ’14:30:00′) OVERLAPS (TIME 42:45:00′, INTERVAL ‘2’ HOUR)

Потому, что временные промежутки в данном примере пересекаются, то предикат OVERLAPS возвращает значение true.

MATCH

Предикат MATCH используется для проверки сохранения ссылочной целостности при модификации данных, т. е. при добавлении, удалении и изменении записей.

SIMILAR

Предикат SIMILAR (подобный) используется для проверки частичного соответствия символьных строчков. Эту же задачу возможно ре шить и посредством предиката LIKE, но во многих случаях SIMILAR более действен.

Предположим, что в некоей таблице имеется столбец ОС, содержащий заглавия операционных совокупностей. Необходимо выбрать записи, соответствующие Windows NT, Windows XP и Windows 98. Тогда в выражении запроса возможно применять таковой оператор WHERE:

WHERE ОС SIMILAR TO ‘(Windows (NT|XP|98))’;

Оператор GROUP BY

Оператор GROUP BY (собирать по) помогает для группировки записей по значениям одного либо нескольких столбцов. В случае если в SQL-выражении употребляется оператор WHERE, задающий фильтр записей, то оператор GROUP BY находится и выполняется по окончании него. Для определения, какие конкретно записи должны войти в группы, помогает оператор HAVING, применяемый совместно с GROUP BY. В случае если оператор HAVING не используется, то группировке подлежат все записи, отфильтрованные оператором WHERE. В случае если WHERE не употребляется, то группируются все записи исходной таблицы.

Допустим, что на базе таблицы о клиентах требуется сгруппировать информацию о суммах заказов клиентов по регионам. Для этого возможно воспользоваться следующим SQL-выражением:

SELECT Регион, Сумма_заказа FROM Клиенты GROUP BY Регион;

На рис. 4 продемонстрирована результатная таблица на фоне исходной таблицы Клиенты. Обратите внимание, что записи с однообразными заглавиями регионов расположены рядом между собой (в одной группе).

Рис. 4. Итог запроса сумм заказов с группировкой по регионам

Рис. 5. Итог запроса итоговых сумм заказов по регионам

Чтобы получить таблицу, в которой суммы заказов подытожены по регионам, потребуется применять итоговую функцию SUM () и группировку по регионам:

SELECT Регион, SUM(Сумма_заказа) FROM Клиенты

GROUP BY Регион;

Тут в выражении SELECT указаны простой столбец таблицы итоговая функция и Клиенты SUM(), вычисляющая сумму значений столбца Сумма_заказа. Потому, что группировка задана по столбцу Регион, то функция SUM (Сумма_заказа) вычисляет Суммы значений столбца Сумма_заказа для каждого значения столбца Регион. На рис. 5 продемонстрирована результатная таблица на фоне исходной таблицы Клиенты. Обратите внимание, что в данной таблице заглавия регионов не повторяются.

Оператор GROUP BY собирает записи в группы и упорядочивает (сортирует) группы по алфавиту (правильнее, по ASCII-кодам знаков). Это событие направляться иметь в виду перед тем, как решить об применении оператора сортировки ORDER BY.

Оператор HAVING

Оператор HAVING (имеющие, при условии) в большинстве случаев используется совместно с оператором группировки GROUP BY и задает фильтр записей в группах. Правила его формирования такие же, что и для оператора WHERE.

Предположим, что из таблицы Клиенты требуется выбрать информацию о суммах и регионах заказов, сгруппированные по регионам и такие, в которых сумма заказа превышает 500. В противном случае говоря, требуется сгруппировать эти с ограничением записей, входящих в группы. Запрос, делающий это задание, имеет форму:

SELECT Регион, Сумма_заказа FROM Клиенты

GROUP BY Регион, Сумма_заказа

HAVING Сумма_заказа 500;

Рис. 6. Итог запроса с группировкой по регионам
и ограничением по суммам заказов

В случае если в какое количество-выражении оператора GROUP BY нет, то оператор HAVING используется ко всем записям, возвращаемым оператором WHERE. В случае если же отсутствует и WHERE, то HAVING действует на все записи таблицы.

Оператор ORDER BY

Оператор ORDER BY (сортировать по) используется для упорядочивания (сортировки) записей. Если он употребляется в запросе, то в самом финише запроса. Данный оператор сортирует строки всей таблицы либо отдельных ее групп (при применения оператора GROUP BY). В случае если в выражении запроса оператора GROUP BY нет, то оператор ORDER BY разглядывает все записи таблицы как одну группу.

За главным словом ORDER BY указывается столбец, по значениям которого направляться произвести сортировку. По окончании имени столбца возможно указать главное слово, задающее порядок (режим) сортировки:

  • ASC — по возрастанию (ascending). Это значение принято по умолчанию, исходя из этого в случае если нужна сортировка, к примеру, в алфавитном порядке, то намерено показывать порядок не нужно;
  • DESC — по убыванию (descending).

В случае если в выражении ORDER BY указаны пара столбцов сортировки, то сперва записи упорядочиваются по значениям первого столбца, после этого для каждого значения первого столбца записи упорядочиваются по значениям второго столбца и т. д. Столбцы в перечне разделяются, как в большинстве случаев, запятыми. Так, создается иерархическая совокупность сортировки записей результатной таблицы.

В следующем примере эти исходной таблицы клиенты сортируются по регионам и по именам клиентов (рис. 7). Наряду с этим сортировка по именам клиентов производится по убыванию, т. е. в порядке, противоположном алфавитному.

SELECT * FROM Клиенты

ORDER BY Регион, Имя DESC;

Рис. 7. Итог сортировки по регионам в алфавитном порядке
и по именам клиентов в обратном порядке

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

Логические выражения в операторах WHERE и HAVING смогут быть сложными, т. е. складываться из двух и более несложных выражений, соединенных между собой логическими операторами (альянсами) AND и/либо OR. Оператор AND делает роль логического альянса И, а оператор OR — альянса Либо. Так, в случае если х и у — два логических выражения, то составное выражение х AND у принимает значение true (ИСТИНА) лишь тогда, в то время, когда х и у в один момент подлинны; в другом случае выражение х and у принимает значение false (Неправда). Выражение х OR у действительно, в случае если хотя бы одно из выражений, х либо у, действительно; в случае если х и у в один момент фальшивы, то составное выражение х OR у ложно.

Логический оператор NOT используется к одному выражению (вероятно и к сложному), расположенному справа от него. Данный оператор меняет значение выражения на противоположное. Так, в случае если выражение х имеет значение true, то выражение NOT х имеет значение false, и, напротив, в случае если х ложно, то NOT х действительно.

Предположим, из таблицы Клиенты (см. рис. 1) требуется выдать записи о клиентах из Северо запада и-Москвы. Соответствующий запрос имеет форму:

SELECT Регион, Имя, Сумма_заказа FROM Клиенты

WHERE Регион=’Москва’ OR Регион=’Северо-запад’;

Обратите внимание, что тут употребляется логический оператор OR (Либо), а не AND (И), потому, что нам необходимы клиенты, живущие либо в Москве, либо на Северо-Западе. Если бы вместо оператора OR мы применили AND, то взяли бы пустую таблицу, т. к. в исходной таблице нет ни одной записи, в которой одинаковый столбец имел бы разные значения.

Внимание. Будьте внимательны при формулировке запроса на естественном языке и при его переводе на SQL.

Следующее SQL-выражение эквивалентно рассмотренному ранее. Оно основано на применении оператора IN:

SELECT Регион, Имя, Сумма_заказа FROM Клиенты

WHERE Регион IN (‘Москва’, ‘Северо-запад’);

В случае если требуется получить информацию обо всех клиентах, каковые не живут ни в Москве, ни на Северо-западе, то возможно применять такое SQL-выражение:

SELECT Регион, Имя, Сумма_заказа FROM Клиенты

WHERE NOT (Регион=’Москва’ OR Регион=’Северо-Запад’);

Это выражение эквивалентно следующим двум:

SELECT Регион, Имя, Сумма_заказа FROM Клиенты

WHERE Регион ‘Москва’ AND Регион ‘Северо-Запад’;

и:

SELECT Регион, Имя, Сумма_заказа FROM Клиенты

WHERE Регион NOT IN (‘Москва’, ‘Северо-Запад’);

Задачи

Выберите в качестве исходной таблицу Клиенты, продемонстрированную на рис. 1. Вы имеете возможность не копировать ее содержимое в точности, а создать похожую таблицу самостоятельно. Принципиально важно, дабы в таблице имелись символьные (текстовые) и числовые столбцы. Прекрасно, в случае если кое-какие столбцы имели бы однообразные значения, к примеру, столбец Регион. В предлагаемых потом задачах требуется организовать SQL-выражения, снабжающие некую выборку записей.

При формировании условий поиска (выборки) записей старайтесь отвлечься от конкретных данных в имеющейся таблице, потому, что пользователь может додавать новые записи и модифицировать уже имеющиеся, делая это так, как ему хочется и как разрешают ограничения для данной таблицы. К примеру, столбец Адрес имеет символьный тип. Это указывает, что он содержит произвольные символьные эти, структура которых поддерживается пользователем на семантическом уровне. В полях данного столбца может находиться либо нет почтовый индекс, перед заглавием улицы возможно указано ул. либо улица, либо возможно ничего не указано, номер телефона может иметь разную значность, содержать либо не содержать код региона, дефисы и т. п. При формулировании запроса в аналогичных обстановках направляться учесть как возможно больше семантических нюансов.

Задача 1

Выберите записи, сгруппированные по регионам и исключающие Северо-Западный регион. Попытайтесь сделать это по крайней мере двумя методами (с применением WHERE и HAVING).

Задача 2

Выберите записи о клиентах, живущих в городах, наименование которых оканчивается на бург, а сумма заказа превышает 2000.

Задача 3

Выберите записи, в которых номера телефонов пятизначные. Наряду с этим направляться предусмотреть, что номера телефонов смогут содержать, а смогут и не содержать дефисы, а сам номер может содержать либо нет код региона, осуждённый либо не заключенный в круглые скобки.

Урок 32. SQL. Предикаты ANY, ALL


Также читать:

Понравилась статья? Поделиться с друзьями: