Перейти к оглавлению
                     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 для ответов. )
 
 
 
Перейти к оглавлению
Hosted by uCoz