5. ИСПОЛЬЗОВАНИЕ СПЕЦИАЛЬНЫХ
ОПЕРАТОРОВ В УСЛОВИЯХ.
В ДОПОЛНЕНИИ К РЕЛЯЦИОННЫМ И БУЛЕВСКИМ операторам обсуждаемым в Гла-
ве 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';
Имеется и более простой способ получить ту же информацию:
SELECT *
FROM Salespeople
WHERE city IN ( 'Barcelona', 'London' );
Вывод для этого запроса показывается в Рисунке 5.2.
Как вы можете видеть, IN определяет набор значений с помощью имен
членов набора заключеных в круглые скобки и отделенных запятыми. Он
затем проверяет различные значения указанного поля пытаясь найти сов-
падение со значениями из набора. Если это случается, то предикат ве-
рен. Когда наборсодержит значения номеров а не символов, одиночные ка-
вычки опускаются. Давайте найдем всех заказчиков относящихся к продав-
цам имеющих значения snum = 1001, 1007, и 1004. Вывод для следующего
запроса показан на Рисунке 5.3:
SELECT *
FROM Customers
WHERE cnum IN ( 1001, 1007, 1004 );
=============== 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 Нахождение продавцов в Барселоне и Лондоне
=============== 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
=============== 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 значение значение|.
Это применимо и к оператору 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.
По общему признанию, это немного неуклюже, но зато показывает как
эти новые операторы могут комбинироваться с операторами Буля чтобы
производить более сложные предикаты. В основном, вы используете IN и
BETWEEN также как вы использовали реляционные операторы чтобы сравни-
вать значения, которые берутся либо из набора ( для IN ) либо из диа-
пазона ( для BETWEEN ).
Также, подобно реляционным операторам, BETWEEN может работать с сим-
вольными полями в терминах эквивалентов ASCII. Это означает что вы мо-
жете использовать BETWEEN чтобы выбирать ряд значений из упорядоченных
по алфавиту значений.
=============== 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 - невключенным
Этот запрос выбирает всех заказчиков чьи имена попали в определенный
алфавитный диапазон:
SELECT *
FROM Customers
WHERE cname BETWEEN 'A' AND 'G';
Вывод для этого запроса показывается в Рисунке 5.6.
Обратите Внимание что Grass и Giovanni отсутствуют, даже при вклю-
ченном BETWEEN. Это происходит из-за того что BETWEEN сравнивает стро-
ки неравной длины. Строка 'G' более короткая чем строка Giovanni, поэ-
тому BETWEEN выводит 'G' с пробелами. Пробелы предшествуют символам в
алфавитном порядке ( в большинстве реализаций ), поэтому Giovanni не
выбирается. То же самое происходит с Grass. Важно помнить это когда вы
используете BETWEEN для извлечения значений из алфавитных диапазонов.
Обычно вы указываете диапазон с помощью символа начала диапазона и
символа конца( вместо которого можно просто поставить z ).
=============== 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 в алфавитных порядках
============ ОПЕРАТОР LIKE =============
LIKE применим только к полям типа CHAR или VARCHAR, с которыми он
используется чтобы находить подстроки. Т.е. он ищет поле символа чтобы
видеть, совпадает ли с условием часть его строки. В качестве условия
он использует групповые символы(wildkards) - специальные символы кото-
рые могут соответствовать чему-нибудь.
Имеются два типа групповых символов используемых с 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 1% '; |
| ==============================================|
| 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 строке,
сопровождается знаком процента, знаком подчеркивания, или знаком ESCA-
PE, который будет искаться в столбце, а не обрабатываться как группо-
вой символ. Символ ESC должен быть одиночным символом и применяться
только к одиночному символу сразу после него.
В примере выше, символ процента начала и символ процента окончания
обрабатываются как групповые символы; только подчеркивание предостав-
лено само себе.
Как упомянуто выше, символ ESC может также использоваться самостоя-
тельно. Другими словами, если вы будете искать столбец с вашим симво-
лом ESC, вы просто вводите его дважды. Во-первых это будет означать
что символ ESC "берет следующий символ буквально как символ", и
во-вторых что символ ESC самостоятелен.
Имеется предыдущий пример который пересмотрен чтобы искать
местонахождение строки '_/' в sname столбце:
SELECT *
FROM Salespeople
WHERE sname LIKE ' % /_ / / %'ESCAPE'/';
Снова не будет никакого вывода с такими данными. Строка сравнивается
с содержанием любой последовательности символов (%), сопровождаемых
символом подчеркивания ( /_ ), символом ESC ( // ), и любой последова-
тельностью символов в конце строки ( % ).
РАБОТА С НУЛЕВЫМИ( NULL ) ЗНАЧЕНИЯМИ
Часто, будут иметься записи в таблице которые не имеют никаких зна-
чений для каждого поля, например потому что информация не завершена,
или потому что это поле просто не заполнялось. SQL учитывает такой ва-
риант, позволяя вам вводить значение NULL(ПУСТОЙ) в поле, вместо зна-
чения. Когда значение поля равно NULL, это означает, что программа ба-
зы данных специально промаркировала это поле как не имеющее никакого
значения для этой строки (или записи). Это отличается от просто назна-
чения полю, значения нуля или пробела, которые база данных будет обра-
батывать также как и любое другое значение. Точно также, как NULL не
является техническим значением, оно не имеет и типа данных. Оно может
помещаться в любой тип поля. Тем ни менее, NULL в SQL часто упоминает-
ся как нуль.
Предположим, что вы получили нового заказчика который еще не был
назначен продавцу. Чем ждать продавца к которому его нужно назначить,
вы можете ввести заказчика в базу данных теперь же, так что он не по-
теряется при перестановке.
Вы можете ввести строку для заказчика со значением NULL в поле snum
и заполнить это поле значением позже, когда продавец будет назначен.
========== NULL ОПЕРАТОР =========
Так как NULL указывает на отсутствие значения, вы не можете знать
каков будет результат любого сравнения с использованием NULL. Когда
NULL сравнивается с любым значением, даже с другим таким же NULL, ре-
зультат будет ни верным ни неверным, он - неизвестен. Неизвестный Бу-
лев, вообще ведет себя также как неверная строка, которая произведя
неизвестное значение в предикате не будет выбрана запросом - имейте
ввиду что в то время как NOT(неверное) - равняется верно, NOT (неиз-
вестное) - равняется неизвестно.
Следовательно, выражение типа 'city = NULL' или 'city IN (NULL)' бу-
дет неизвестно, независимо от значения city.
Часто вы должны делать различия между неверно и неизвестно - между
строками содержащими значения столбцов которые не соответствуют усло-
вию предиката и которые содержат NULL в столбцах. По этой причине, SQL
предоставляет специальный оператор IS, который используется с ключевым
словом NULL, для размещения значения NULL.
Найдем все записи в нашей таблице Заказчиков с NULL значениями в ci-
ty столбце:
SELECT *
FROM Customers
WHERE city IS NULL;
Здесь не будет никакого вывода, потому что мы не имеем никаких зна-
чений NULL в наших типовых таблицах. Значения NULL - очень важны, и мы
вернемся к ним позже.
ИСПОЛЬЗОВАНИЕ NOT СО СПЕЦИАЛЬНЫМИ ОПЕРАТОРАМИ
Специальные операторы которые мы изучали в этой главе могут немед-
ленно предшествовать Булеву NOT.
Он противоположен реляционным операторам, которые должны иметь опе-
ратор NOT - вводимым выражением. Например, если мы хотим устранить
NULL из нашего вывода, мы будем использовать NOT чтобы изменить на
противоположное значение предиката:
SELECT *
FROM Customers
WHERE city NOT NULL;
При отсутствии значений NULL( как в нашем случае ), будет выведена
вся таблица Заказчиков. Аналогично можно ввести следующее
SELECT *
FROM Customers
WHERE NOT city IS NULL;
- что также приемлемо.
Мы можем также использовать NOT с IN:
SELECT *
FROM Salespeople
WHERE city NOT IN ( 'London', 'San Jose' );
А это - другой способ подобного же выражения
SELECT *
FROM Salespeople
WHERE NOT city IN ( 'London', ' San Jose' );
Вывод для этого запроса показывается в Рисунке 5.9.
Таким же способом Вы можете использовать NOT BETWEEN и NOT LIKE.
=============== SQL Execution Log ============
| |
| SELECT * |
| FROM Salespeople |
| WHERE sity NOT IN ('London', 'San Jose'; |
| ==============================================|
| snum sname city comm |
| ------ ---------- ----------- ------- |
| 1003 Rifkin Barcelona 0.15 |
| 1007 Axelrod New York 0.10 |
| |
===============================================
Рисунок 5. 9: Использование NOT с IN
=============== РЕЗЮМЕ ================
Теперь вы можете создавать предикаты в терминах связей специально
определенных SQL. Вы можете искать значения в определенном диапазоне
(BETWEEN) или в числовом наборе (IN), или вы можете искать символьные
значения которые соответствуют тексту внутри параметров (LIKE).
Вы также изучили некоторые вещи относительно того как SQL поступает
при отсутствии данных - что реальность мировой базы данных - используя
NULL вместо конкретных значений. Вы можете извлекать или исключать
значения NULL из вашего вывода используя оператор IS NULL. Теперь,
когда вы имеете в вашем распоряжении весь набор стандартных математи-
ческих и специальных операторов, вы можете переходить к специальным
функциям SQL которые работают на всех группах значений, а не просто на
одиночном значении, что важно.
Это уже тема Главы 6.
************** РАБОТА С SQL **************
1. Напишите два запроса которые могли бы вывести все порядки на 3 или
4 Октября 1990
2. Напишите запрос который выберет всех заказчиков обслуживаемых про-
давцами Peel или Motika. ( Подсказка: из наших типовых таблиц, поле
snum связывает вторую таблицу с первой )
3. Напишите запрос, который может вывести всех заказчиков чьи имена
начинаются с буквы попадающей в диапазон от A до G.
4. Напишите запрос который выберет всех пользователей чьи имена начи-
наются с буквы C.
5. Напишите запрос который выберет все порядки имеющие нулевые значе-
ния или NULL в поле amt(сумма).
( См. Приложение A для ответов. )
|
