Использование специальных операторов в условиях
В ДОПОЛНЕНИЕ К РЕЛЯЦИОННЫМ И БУЛЕВСКИМ операторам, обсуждаемым в Главе 4, SQL использует специальные операторыIN, BETWEEN, LIKE,и IS NULL. В этой главе вы узнаете, как их использовать, и как реляционные операторы позволяют создавать более сложные и мощные предикаты. Обсуждение оператора IS NULL будет включать отсутствие данных и значение NULL, которое указывает на то, что данные отсутствуют. Вы также узнаете о разновидностях использования оператора NOT, применяющегося с этими операторами.
Оператор IN
Оператор IN определяет набор значений, в который данное значение может или не может быть включено. В соответствии с нашей учебной базой данных, на которой вы обучаетесь по настоящее время, если вы хотите найти всех продавцов, которые размещены в Barcelona или в London, вы должны использовать следующий запрос (вывод показывается в Рисунке 5.1):
SELECT *
FROM Salespeople
WHERE city = 'Barcelona' OR city = 'London';
================ SQL Execution Log ============
| SELECT * |
| FROM Salespeople |
| WHERE city = 'Barcelona' |
| OR city = 'London'; |
| ==============================================|
| snum sname city comm |
| ------ ---------- ----------- ------- |
| 1001 Peel London 0.12 |
| 1004 Motika London 0.11 |
| 1007 Rifkin Barcelona 0.15 |
===============================================
Рисунок 5.1. Нахождение продавцов в Барселоне и Лондоне
Имеется и более простой способ получить ту же информацию:
SELECT *
FROM Salespeople
WHERE city IN ('Barcelona', 'London');
Вывод для этого запроса показывается в Рисунке 5.2.
================ SQL Execution Log ============
| SELECT * |
| FROM Salespeople |
| WHERE city IN ('Barcelona', 'London'; |
| ==============================================|
| snum sname city comm |
| ------ ---------- ----------- ------- |
| 1001 Peel London 0.12 |
| 1004 Motika London 0.11 |
| 1007 Rifkin Barcelona 0.15 |
===============================================
Рисунок 5.2. SELECT использует IN
Как вы можете видеть, IN определяет набор значений с помощью имен членов набора, заключенных в круглые скобки и отделенных запятыми. Он затем проверяет различные значения указанного поля, пытаясь найти совпадение со значениями из набора. Если это случается, то предикат верен. Когда набор содержит значения номеров, а не символов, одиночные кавычки опускаются. Давайте найдем всех заказчиков относящихся к продавцам имеющих значения snum = 1001, 1007, и 1004. Вывод для следующего запроса показан на Рисунке 5.3:
SELECT *
FROM Customers
WHERE cnum IN (1001, 1007, 1004);
=============== SQL Execution Log ============
| SELECT * |
| FROM Customers |
| WHERE snum IN (1001, 1007, 1004); |
| ============================================= |
| snum cname city rating snum |
| ------ -------- ------ ---- ------ |
| 2001 Hoffman London 100 1001 |
| 2006 Clemens London 100 1001 |
| 2008 Cisneros San Jose 300 1007 |
| 2007 Pereira Rome 100 1004 |
===============================================
Рисунок 5.3. SELECT использует IN с номерами
Оператор BETWEEN
Оператор BETWEEN похож на оператор IN. В отличие от определения по номерам из набора, как это делает IN, BETWEEN определяет диапазон, значения которого должны уменьшаться, что делает предикат верным. Вы должны ввести ключевое слово BETWEEN с начальным значением, ключевое AND и конечное значение. В отличие от IN, BETWEEN чувствителен к порядку, и первое значение в предложении должно быть первым в алфавитном или числовом порядке. Обратите внимание, что, в отличие от английского языка, SQL не говорит "значениенаходится между (BETWEEN)значением и значением”, а просто "значениеBETWEENзначениеANDзначение”. Это применимо и к оператору LIKE (см. ниже). Следующий пример будет извлекать из таблицы Продавцов всех продавцов с комиссионными между .10 и .12 (вывод показывается в Рисунке 5.4):
SELECT *
FROM Salespeople
WHERE comm BETWEEN .10 AND .12;
Для включенного оператора BETWEEN, значение совпадающее с любым из двух значений границы (в этом случае, .10 и .12) заставляет предикат быть верным.
=============== SQL Execution Log ============
| SELECT * |
| FROM Salespeople |
| WHERE comm BETWEEN .10 AND .12; |
| ==============================================|
| snum sname city comm |
| ------ ---------- ----------- ------- |
| 1001 Peel London 0.12 |
| 1004 Motika London 0.11 |
| 1003 Axelrod New York 0.10 |
===============================================
Рисунок 5.4: SELECT использует BETWEEN
SQL не имеет поддержки исключения границ диапазона в выражении BETWEEN. Вы должны или определить ваши граничные значения так, чтобы включающая интерпретация была приемлема, или сделать что-нибудь типа этого:
SELECT *
FROM Salespeople
WHERE (comm BETWEEN .10, AND .12) AND NOT comm IN (.10, .12);
Вывод для этого запроса показывается в Рисунке 5.5.
=============== SQL Execution Log ============
| SELECT * |
| FROM Salespeople |
| WHERE (comm BETWEEN .10 AND .12 |
| AND NOT comm IN (.10, .12); |
| ==============================================|
| snum sname city comm |
| ------ ---------- ----------- ------- |
| 1004 Motika London 0.11 |
===============================================
Рисунок 5.5. Сделать BETWEEN — невключающим
По общему признанию, это немного неуклюже, но зато показывает, как эти новые операторы могут комбинироваться с операторами Буля, чтобы производить более сложные предикаты. В основном, вы используете IN и BETWEEN так же, как вы использовали реляционные операторы, чтобы сравнивать значения, которые берутся либо из набора (для IN) либо из диапазона (для BETWEEN).
Также, подобно реляционным операторам, BETWEEN может работать с символьными полями. Это означает, что вы можете использовать BETWEEN, чтобы выбирать ряд значений из упорядоченных по алфавиту значений.
Этот запрос выбирает всех заказчиков, чьи имена попали в диапазон от 'A' до 'G':
SELECT *
FROM Customers
WHERE cname BETWEEN 'A' AND 'G';
Вывод для этого запроса показывается в Рисунке 5.6.
=============== SQL Execution Log ============
| SELECT * |
| FROM Customers |
| WHERE cname BETWEEN 'A' AND 'G'; |
| ============================================= |
| cnum cname city rating snum |
| ------ -------- ------ ---- ------ |
| 2006 Clemens London 100 1001 |
| 2008 Cisneros San Jose 300 1007 |
===============================================
Рисунок 5.6. Использование BETWEEN в алфавитном порядке.
Обратите внимание, что Grass и Giovanni отсутствуют. Это происходит из-за того, что BETWEEN сравнивает строки неравной длины. Строка 'G' более короткая, чем строка 'Giovanni', поэтому BETWEEN выводит 'G' с пробелами. Пробелы предшествуют символам в алфавитном порядке (в большинстве реализаций), поэтому 'Giovanni' не выбирается. То же самое происходит с Grass. Важно помнить это, когда вы используете BETWEEN для извлечения значений из алфавитных полей. Обычно вы указываете диапазон с помощью символа начала диапазона и символа конца (вместо которого можно просто поставить ‘z’).
Оператор LIKE
LIKE применим только к полям типа CHAR или VARCHAR, с которыми он используется, чтобы находить подстроки. То есть, он ищет поле символа, чтобы видеть, совпадает ли с условием часть его строки. В качестве условия он использует групповые символы (wildcards) — специальные символы которые могут соответствовать чему-нибудь. Имеются два типа групповых символов используемых с LIKE:
символ подчеркивания (_) замещает любой одиночный символ. Например, 'b_t' будет соответствовать словам 'bat' или 'bit', но не будет соответствовать 'brat'.
знак процента (%) замещает последовательность любого числа символов (включая символы нуля). Например '%p%t' будет соответствовать словам 'put', 'posit',или'opt',но не'spite'.
Давайте найдем всех заказчиков, чьи имена начинаются с G (вывод показывается в Рисунке 5.7):
SELECT
FROM Customers
WHERE cname LIKE 'G%';
=============== SQL Execution Log ============
| SELECT * |
| FROM Customers |
| WHERE cname LIKE 'G%'; |
| ============================================= |
| cnum cname city rating snum |
| ------ -------- ------ ---- ------ |
| 2002 Giovanni Rome 200 1003 |
| 2004 Grass Berlin 300 1002 |
===============================================
Рисунок 5.7. Оператор SELECT использует LIKE со знаком '%'.
LIKE может быть удобен, если вы ищете имя или другое значение, и если вы не помните, как они точно пишутся. Предположим, что вы не уверены, как записано по буквам имя одного из ваших продавцов PealилиPeel. Вы можете просто использовать ту часть, которую вы знаете, и групповые символы, чтобы находить все возможные пары (вывод этого запроса показывается в Рисунке 5.8):
SELECT *
FROM Salespeople
WHERE sname LIKE 'P _ _ l %';
Групповые символы подчеркивания, каждый из которых представляет один символ, добавят только два символа к уже существующим 'P' и 'l', поэтому имя наподобие Prettel не может быть показано. Групповой символ '%' — в конце строки необходим в большинстве реализаций, если длина поля sname больше чем число символов в имени Peel, потому что некоторые другие значения sname — длиннее, чем четыре символа. В таком случае, значение поля sname, фактически сохраняемое как имя Peel, сопровождается рядом пробелов. Следовательно, символ 'l' не будет рассматриваться концом строки. Групповой символ '%' — просто соответствует этим пробелам. Это необязательно, если поля sname имеет тип VARCHAR.
=============== SQL Execution Log ============
| SELECT * |
| FROM Salespeople |
| WHERE sname LIKE ' P _ _ l% '; |
| ==============================================|
| snum sname city comm |
| ------ ---------- ----------- ------- |
| 1001 Peel London 0.12 |
===============================================
Рисунок 5.8. Оператор SELECT использует LIKE с символом подчеркивания (_).
А что же Вы будете делать, если вам нужно искать знак процента или знак подчеркивания в строке? В предикате LIKE вы можете определить любой одиночный символ как символ ESC. Символ ESC используется сразу перед процентом или подчеркиванием в предикате, и означает, что процент или подчеркивание будет интерпретироваться как символ, а не как групповой символ. Например, мы могли бы найти наш sname столбец, где присутствует подчеркивание, следующим образом:
SELECT *
FROM Salespeople
WHERE sname LIKE '%/_%'ESCAPE'/';
С этими данными не будет никакого вывода, потому что мы не включили никакого подчеркивания в имя нашего продавца. Предложение ESCAPE определяет '/' как символ ESC. Символ ESC используемый в LIKE строке, сопровождается знаком процента, знаком подчеркивания, или знаком ESCAPE, который будет искаться в столбце, а не обрабатываться как групповой символ. Символ ESC должен быть одиночным символом и применяться только к одиночному символу сразу после него.
В примере выше и символ процента в начале, и символ процента в конце обрабатываются как групповые символы; только подчеркивание предоставлено само себе.
Как упомянуто выше, символ ESC может также использоваться самостоятельно. Другими словами, если вы будете искать столбец с вашим символом ESC, вы просто вводите его дважды. Во-первых, это будет означать, что символ ESC "берет следующий символ буквально как символ", и, во-вторых, что символ ESC самостоятелен.
Имеется предыдущий пример, который пересмотрен, чтобы искать местонахождение строки '_/' в sname столбце:
SELECT *
FROM Salespeople
WHERE sname LIKE '%/_//%' ESCAPE'/';
Снова не будет никакого вывода с такими данными.
Строка сравнивается с содержанием любой последовательности символов (%), сопровождаемых символом подчеркивания (/_), символом ESC (//), и любой последовательностью символов в конце строки (%).