Добро пожаловать!
Здесь вы можете найти ответ на интересующий вас вопрос в отрасли сайтостроения, познакомится ближе с web технологиями и web стандартами.

SQL

Глава 4. Использование реляционных и булевых операций для создания более сложных предикатов

В Главе 3 вы узнали, что предикаты могут оценивать равенство в операции как true (верное) или false (неверное). Они могут также оценивать другие виды связей, помимо равенств.

Эта глава будет исследовать другие реляционные операции, используемые в SQL. Вы также узнаете, как использовать булевы операции, чтобы изменять и объединять значения предиката. С помощью булевых операций (или, проще говоря, логических операций) одиночный предикат может содержать любое число условий. Это позволяет создавать очень сложные предикаты. Использование круглых скобок в структуре этих сложных предикатов будет также разъясняться.

Реляционные операции

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

=
Равно
>
Больше
<
Меньше
>=
Больше или равно
<=
Меньше или равно
<>
Не равно

Эти операции имеют стандартное значение для чисел. Для символов их определение зависит от формата преобразования, ASCII или EBCDIC, который вы используете.

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

И в ASCII, и в EBCDIC символы сортируются по значению: символ имеет значение меньше, чем все другие символы, которым он предшествует в алфавитном порядке и которые имеют с ним один вариант регистра (верхний или нижний). В ASCII все символы верхнего регистра меньше, чем все символы нижнего регистра, поэтому "Z" < "a", а все числа — меньше чем все символы, поэтому "1" < "A". То же относится и к EBCDIC.

Чтобы сохранить обсуждение более простым, мы допустим, что вы будете использовать текстовый формат ASCII. Проконсультируйтесь в документации вашей системы, если вы не уверены, какой формат вы используете или как он работает.

Значения, сравниваемые здесь, называются скалярными значениями. Скалярные значения производятся скалярными выражениями; 1 + 2 это скалярное выражение, которое производит скалярное значение 3. Скалярное значение может быть символом или числом, хотя очевидно, что только числа используются с арифметическими операциями, такими как + (сложение) или * (умножение).

Предикаты обычно сравнивают значения скалярных величин, используя реляционные операции или специальные операции SQL, чтобы увидеть, верно ли это сравнение. Некоторые операции/операторы SQL описаны в Главе 5.

Предположим, что вы хотите увидеть всех заказчиков с оценкой (rating) выше 200. Так как 200 — скалярное значение, как и значение в столбце оценки, для их сравнения вы можете использовать реляционную операцию. 

    
SELECT *
  FROM Customers
  WHERE rating > 200;

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

               
   ===============  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 Использование операции "больше" (>)

Конечно, если бы мы захотели увидеть ещё и заказчиков с оценкой, равной 200, мы использовали бы предикат 

             
rating >= 200

Булевы операции

Основные булевы операции также распознаются в SQL. Выражения Буля являются или верными (true), или неверными (false), подобно предикатам. Булевы операции связывают одно или более верных/неверных значений и производят единственное верное или неверное значение.

Стандартными булевыми операциями, распознаваемыми в SQL, являются AND, OR и NOT.

Существуют другие, более сложные булевы операции (типа "исключающее ИЛИ"), но они могут быть сформированы из этих трёх простых операций — 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. Имеется только один заказчик, который удовлетворяет этому условию. 

               
   ===============  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

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

           
SELECT *
  FROM Customers
  WHERE city = 'San Jose'
    OR rating > 200;

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

               
   ===============  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

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 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 преобразует верно в неверно и наоборот. 

   ===============  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 и вводное предложение

Вывод для этого запроса показан на Рисунке 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  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.