Главная | Обратная связь | Поможем написать вашу работу!

Использование специальных операторов в условиях.

В ДОПОЛНЕНИИ К РЕЛЯЦИОННЫМ И БУЛЕВСКИМ операторам обсуждаемым в Главе 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(сумма).

⇐ Предыдущая 1 234 5 6 7 Следующая ⇒