On-Line Библиотека www.XServer.ru - учебники, книги, статьи, документация, нормативная литература.
       Главная         В избранное         Контакты        Карта сайта   
    Навигация XServer.ru




Загрузка...




 

Понимание SQL.

ИСПОЛЬЗОВАНИЕ РЕЛЯЦИОННЫХ И БУЛЕВЫХ ОПЕРАТОРОВ ДЛЯ СОЗДАНИЯ БОЛЕЕ ИЗОЩРЕННЫХ ПРЕДИКАТОВ

В ГЛАВЕ 3, ВЫ УЗНАЛИ ЧТО ПРЕДИКАТЫ МОГУТ оценивать равенство оператора как верного или неверного. Они могут также оценивать другие виды связей кроме равенств. Эта глава будет исследовать другие реляционные операторы используемые в SQL. Вы также узнаете как использовать операторы Буля, чтобы изменять и объединять значения предиката. С помощью операторов Буля (или проще говор логических операторов), одиночный предикат может содержать любое число условий. Это позволяет вам создавать очень сложные предикаты. Использование круглых скобок в структуре этих сложных предикатов будет также объясняться.

РЕЛЯЦИОННЫЕ ОПЕРАТОРЫ

Реляционный оператор - математический символ который указывает на определенный тип сравнения между двум значениями. Вы уже видели как используются равенства, такие как 2 + 3 = 5 или city = "London". Но также имеются другие реляционные операторы. Предположим что вы хотите видеть всех Продавцов с их комиссионными выше определенного значения. Вы можете использовать тип сравнения "больше чем" - (>). Реляционные операторы которыми располагает SQL :

           =    Равный к 
 
           >    Больше чем 
 
           <    Меньше чем 
 
           >=  Больше чем или равно 
 
           <=  Меньше чем или равно 
 
           < >  Не равно 

Эти операторы имеют стандартные значения для числовых значений. Для значения символа, их определение зависит от формата преобразования, ASCII или EBCDIC, который вы используете. SQL сравнивает символьные значения в терминах основных номеров как определено в формате преобразования. Даже значение символа, такого как "1", который представляет номер, не обязательно равняется номеру который он представляет. Вы можете использовать реляционные операторы чтобы установить алфавитный порядок - например, "a" < "n" где средство a первое в алфавитном порядке - но все это ограничивается с помощью параметра преобразования формата.

И в ASCII и в EBCDIC, символы - по значению: меньше чем все другие символы которым они предшествуют в алфавитном порядке и имеют один вариант( верхний или нижний ). В ASCII, все символы верхнего регистра - меньше чем все символы нижнего регистра, поэтому "Z" < "a", а все номера - меньше чем все символы, поэтому "1" < "Z". То же относится и к EBCDIC. Чтобы сохранить обсуждение более простым, мы допустим что вы будете использовать текстовый формат ASCII. Проконсультируйтесь с вашей документацией системы если вы неуверенны какой формат вы используете или как он работает. Значения сравниваемые здесь называются - скалярными значениями. Скалярные значения производятся скалярными выражениями; 1 + 2 - это скалярное выражение которое производит скалярное значение 3. Скалярное значение может быть символом или числом, хотя очевидно что только номера используются с арифметическими операторами, такими как +(плюс) или *(звезда). Предикаты обычно сравнивают значения скалярных величин, используя или реляционные операторы или специальные операторы SQL чтобы увидеть верно ли это сравнение. Некоторые операторы SQL описаны в Главе 5 . Предположим что вы хотите увидеть всех заказчиков с оценкой(rating) выше 200. Так как 200 - это скалярное значение, как и значение в столбце оценки, для их сравнения вы можете использовать реляционный оператор.

    SELECT * 
       FROM Customers 
       WHERE rating > 200; 
 
Вывод для этого запроса показывается в Рисунке 4.1. 
Конечно, если бы мы захотели увидеть еще и заказчиков с оценкой равной 200, мы стали бы использовать предикат
             rating > = 200 

БУЛЕВЫ ОПЕРАТОРЫ

Основные Булевы операторы также распознаются в SQL. Выражения Буля - являются или верными или неверными, подобно предикатам. Булевы операторы связывают одно или более верных/неверных значений и производят единственное верное/или/неверное значение. Стандартными операторами Буля распознаваемыми в SQL являются:

              AND,  OR, и NOT. 
 
                    ===============  SQL Execution Log ============ 
              |                                               | 
              | SELECT *                                      | 
              | FROM  Customers                               | 
              | WHERE rating > 200;                           | 
              | ============================================= | 
              |   snum     cname    city     rating    snum   | 
              |  -----   --------  --------  -----   ------   | 
              |   2004     Crass    Berlin     300     1002   | 
              |   2008     Cirneros San Jose   300     1007   | 
                ============================================= 
 
Рисунок 4.1:  Использование больше чем (>) 

Существуют другие, более сложные, операторы Буля ( типа " исключенный или " ), но они могут быть сформированы из этих трех простых операторов - AND, OR, NOT. Как вы можете понять, Булева верн / неверна логика - основана на цифровой компьютерной операции; и фактически, весь SQL( или любой другой язык ) может быть сведен до уровня Булевой логики.

Операторы Буля и как они работают: 
 
*  AND берет два Буля ( в форме A AND B) как аргументы и 
   оценивает их по отношению к истине, верны ли они оба. 
 
*  OR берет два Буля ( в форме A OR B) как аргументы и 
   оценивает на правильность, верен ли один из них. 
 
*  NOT берет одиночный Булев ( в форме NOT A) как аргументы 
   и заменяет его значение с неверного на верное или верное на 
   неверное. 
Связывая предикаты с операторами Буля, вы можете значительно увеличить их возможности. Предположим вы хотите видеть всех заказчиков в San Jose которые имеют оценку(рейтинг) выше 200:
         SELECT  * 
            FROM Customers 
            WHERE city = " San Jose' 
            AND rating > 200; 
 
Вывод для этого запроса показан на Рисунке 4.2. Имеется только один заказчик который удовлетворяет этому условию.

Если вы же используете OR вы получите всех заказчиков которые находились в San Jose или(OR) которые имели оценку выше 200.

 
               ===============  SQL Execution Log ============ 
              |                                               | 
              | SELECT *                                      | 
              | FROM  Customers                               | 
              | WHERE city = 'San Jose'                       | 
              | AND rating > 200;                             | 
              | ============================================= | 
              |   сnum     cname    city     rating    snum   | 
              |  ------   --------  --------  ----    -----   | 
              |   2008     Cirneros San Jose   300     1007   | 
                ============================================= 
 
 
Рисунок 4.2: SELECT использующий AND 
 
 
           SELECT * 
              FROM Customers 
              WHERE city = " San Jose' 
              OR rating > 200; 
 
Вывод для этого запроса показывается в Рисунке 4.3. 
 
NOT может использоваться для инвертирования значений Буля. Имеется 
пример запроса с NOT: 
 
                    SELECT * 
                       FROM Customers 
                       WHERE city = " San Jose' 
                       OR NOT rating > 200; 
 
Вывод этого запроса  показывается в Рисунке 4.4. 
 
 
               ===============  SQL Execution Log ============ 
              |                                               | 
              | SELECT *                                      | 
              | FROM  Customers                               | 
              | WHERE city = 'San Jose'                       | 
              | OR rating > 200;                              | 
              | ============================================= | 
              |   сnum     cname    city     rating    snum   | 
              |  -----    -------  --------  -----   ------   | 
              |   2003     Liu      San Jose   200     1002   | 
              |   2004     Grass    Berlin     300     1002   | 
              |   2008     Cirneros San Jose   300     1007   | 
                ============================================= 
 
 
Рисунок 4.:3: SELECT использующий OR 
 
               ===============  SQL Execution Log ============ 
              |                                               | 
              | SELECT *                                      | 
              | FROM  Customers                               | 
              | WHERE city = 'San Jose'                       | 
              | OR NOT rating > 200;                          | 
              | ============================================= | 
              |   cnum     cname    city     rating    snum   | 
              |  ------   --------  ------   -----    -----   | 
              |   2001     Hoffman  London     100     1001   | 
              |   2002     Giovanni Rome       200     1003   | 
              |   2003     Liu      San Jose   200     1002   | 
              |   2006     Clemens  London     100     1001   | 
              |   2008     Cirneros San Jose   300     1007   | 
              |   2007     Pereira  Rome       100     1004   | 
                ============================================= 
 
Рисунок 4.4: SELECT использующий NOT 

Все записи за исключением Grass были выбраны. Grass не был в San Jose, и его оценка была больше чем 200, так что он потерпел неудачу при обеих проверках. В каждой из других строк встретился один или другой или оба критериев. Обратите внимание что оператор NOT должен предшествовать Булеву оператору, чье значение должно измениться, и не должен помещаться перед реляционным оператором. Например неправильным вводом оценки предиката будет:

                 rating NOT > 200 
 
Он выдаст другую отметку. А как SQL оценит следующее? 
 
                   SELECT * 
                      FROM Customers 
                      WHERE NOT city = " San Jose' 
                      OR rating > 200; 

NOT применяется здесь только к выражению city = 'SanJose', или к выражению rating > 200 тоже ? Как и написано, правильный ответ будет прежним. SQL может применять NOT с выражением Буля только сразу после него. Вы можете получить другой результат при команде:

                   SELECT * 
                      FROM Customers 
                      WHERE NOT( city = " San Jose' 
                      OR rating > 200 ); 
Здесь SQL понимает круглые скобки как означающие, что все внутри них будет оцениваться первым и обрабатываться как единое выражение с помощью всего что снаружи них ( это является стандартной интерпретацией в математике ). Другими словами, SQL берет каждую строку и определяет, соответствует ли истине равенство city = " San Jose' или равенство rating > 200. Если любое условие верно, выражение Буля внутри круглых скобок верно. Однако, если выражение Буля внутри круглых скобок верно, предикат как единое целое неверен, потому что NOT преобразует верно в неверно и наоборот.

Вывод для этого запроса - показывается в Рисунке 4.5.

Имеется намеренно сложный пример. Посмотрим сможете ли вы проследить его логику (вывод показан в Рисунке 4.6 ):

        SELECT * 
           FROM Orders 
             WHERE NOT ((odate = 10/03/1990 AND snum >1002) 
                OR amt > 2000.00); 
 
               ===============  SQL Execution Log ============ 
              |                                               | 
              | SELECT *                                      | 
              | FROM  Customers                               | 
              | WHERE NOT  (city = 'San Jose'                 | 
              | OR rating > 200);                             | 
              | ============================================= | 
              |   cnum     cname    city     rating    snum   | 
              |  -----   --------  -------   -----   ------   | 
              |   2001     Hoffman  London     100     1001   | 
              |   2002     Giovanni Rome       200     1003   | 
              |   2006     Clemens  London     100     1001   | 
              |   2007     Pereira  Rome       100     1004   | 
                ============================================= 
 
Рисунок 4.5: SELECT использующий NOT и вводное предложение 
 
 
               ===============  SQL Execution Log ============== 
              |                                                 | 
              | SELECT *                                        | 
              | FROM  Orders                                    | 
              | WHERE NOT ((odate = 10/03/1990 AND snum > 1002) | 
              | OR amt > 2000.00);                              | 
              | =============================================== | 
              |   onum       amt      odate      cnum     snum  | 
              |  ------   --------  ----------  -----    -----  | 
              |   3003      767.19  10/03/1990   2001     1001  | 
              |   3009     1713.23  10/04/1990   2002     1003  | 
              |   3007       75.75  10/04/1990   2004     1002  | 
              |   3010     1309.95  10/06/1990   2004     1002  | 
               ================================================= 
 
Рисунок 4.6:  Полный (комплексный) запрос 
 
Несмотря на то что Булевы операторы индивидуально просты, они не так просты когда комбинируются в комплексное выражение.

Способ оценки комплекса Булева состоит в том, чтобы оценивать Булевы выражения наиболее глубоко вложенные в круглых скобках, объединять их в единичное Булево значение, и затем объединять его с верхними значениями.

Имеется подробное объяснение того как пример выше был вычислен. Наиболее глубоко вложенные выражения Буля в предикате - это odate = 10/03/1990 и snum > 1002 являются объединенными с помощью AND, формируя одно выражение Буля которое будет оценено как верное для всех строк в которых встретились оба эти условия. Это составное Булево выражение (которое мы будем называть Булево номер 1, или B1 для краткости) объединяется с выражением (amt) > 2000.00 (B2) с помощью OR, формируя третье выражение (B3), которое является верным для данной строки, если или B1 или B2 - верны для этой строки.

B3 полностью содержится в круглых скобках которым предшествует NOT, формируя последнее выражение Буля(B4), которое является условием предиката. Таким образом B4, предикат запроса, - будет верен всякий раз, когда B3 неправилен. B3 - неправилен всегда, когда B1 и B2 - оба неверны. B1 неправилен для строки если дата порядка строки не 10/03/1990, или если значение snum не большее чем 1002. B2 неправилен для всех строк, значения суммы приобретений которых не превышает 2000.00. Люба строка со значением выше 2000.00 сделает B2 - верным; в результате B3 будет верен, а B4 нет. Следовательно, все эти строки будут удалены из вывода. Из оставшихся, строки которые на 3 Октября имеют snum > 1002 ( такие как строки для onum 3001 на 3 Октября со snum = 1007 ), делают B1 верным, с помощью верного B3 и неверного предиката запроса. Они будут также удалены из вывода. Вывод показан для строк которые оставлены.

РЕЗЮМЕ

В этой главе, вы значительно расширили ваше знакомство с предикатами. Теперь вы можете находить значения которые связаны с данным значением любым способом - определяемым различными реляционными операторами. Вы можете также использовать операторы Буля AND и OR чтобы много условий, каждое из которых автономно в предикатах, объединять в единый предикат. Оператор Буля NOT, как вы уже видели, может изменять значение условия или группы условий на противоположное.

Булевы и Реляционные операторы могут эффективно управляться с помощью круглых скобок, которые определяют порядок, в котором операции будут выполнены. Эти операции применимы к любому уровню сложности и вы поняли как сложные условия могут создаваться из этих простых частей.

Теперь, когда мы показали как используются стандартные математические операторы, мы можем перейти к операторам которые являются исключительными в SQL. Это мы сделаем в Главе 5.

РАБОТА С SQL

 
 
1. Напишите запрос который может дать вам все порядки со значениями 
   суммы выше чем $1,000. 
 
2. Напишите запрос который может выдать вам пол sname и city для всех 
   продавцов в Лондоне с комиссионными выше  .10 . 
 
3. Напишите запрос к таблице Заказчиков чей вывод может включить всех 
   заказчиков с оценкой  =< 100, если они не находятся в Риме. 
 
4. Что может быть выведено в результате следующего запроса ? 
 
              SELECT * 
                 FROM Orders 
                 WHERE (amt < 1000 OR 
                     NOT (odate = 10/03/1990 
                         AND cnum > 2003 )); 
 
 
5. Что может быть выведено в результате следующего запроса ? 
 
              SELECT * 
                 FROM Orders 
                 WHERE NOT ((odate = 10/03/1990 OR snum > 1006) 
                    AND amt > = 1500 ); 
 
 
6. Как можно проще переписать такой запрос ? 
 
              SELECT snum, sname, city, comm 
                 FROM Salespeople 
                 WHERE ( comm > + .12 OR 
                    comm < .14 ); 
 
 
(См. Приложение A для ответов.)