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 для ответов. )

6. ОБОБЩЕНИЕ ДАННЫХ С ПОМОЩЬЮ АГРЕГАТНЫХ ФУНКЦИЙ (COUNT, SUM, AVG, MAX, MIN)

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

ЧТО ТАКОЕ АГРЕГАТНЫЕ ФУНКЦИИ ?

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

* COUNT производит номера строк или не-NULL значения полей которые выбрал запрос.

* SUM производит арифметическую сумму всех выбранных значений данного поля.

* AVG производит усреднение всех выбранных значений данного поля.

* MAX производит наибольшее из всех выбранных значений данного поля.

* MIN производит наименьшее из всех выбранных значений данного поля.

КАК ИСПОЛЬЗОВАТЬ АГРЕГАТНЫЕ ФУНКЦИИ ?

Агрегатные функции используются подобно именам полей в предложении SELECT запроса, но с одним исключением, они берут имена поля как аргументы. Только числовые поля могут использоваться с SUM и AVG. С COUNT, MAX, и MIN, могут использоваться и числовые или символьные поля. Когда они используются с символьными полями, MAX и MIN будут транслировать их в эквивалент ASCII, который должен сообщать, что MIN будет означать первое, а MAX последнее значение в алфавитном порядке (выдача алфавитного упорядочения обсуждается более подробно в Главе 4).

Чтобы найти SUM всех наших покупок в таблицы Порядков, мы можем ввести следующий запрос, с его выводом в Рисунке 6.1:

SELECT SUM (amt)

FROM Orders;

=============== SQL Execution Log ============

| |

| SELECT SUM (amt) |

| FROM Orders; |

| ==============================================|

| |

| ------- |

| 26658.4 |

| |

| |

===============================================

Рисунок 6.1: Выбор суммы

Это конечно, отличается от выбора поля при котором возвращается одиночное значение, независимо от того сколько строк находится в таблице. Из-за этого, агрегатные функции и поля не могут выбираться одновременно, пока предложение GROUP BY (описанное далее) не будет использовано.

Нахождение усредненной суммы - это похожая операция (вывод следующего запроса показывается в Рисунке 6.2 ):

SELECT AVG (amt)

FROM Orders;

=============== SQL Execution Log ============

| |

| SELECT AVG (amt) |

| FROM Orders; |

| ==============================================|

| |

| ------- |

| 2665.84 |

| |

===============================================

Рисунок 6.2: Выбор среднего

СПЕЦИАЛЬНЫЕ АТРИБУТЫ COUNT

Функция COUNT несколько отличается от всех. Она считает число значений в данном столбце, или число строк в таблице. Когда она считает значения столбца, она используется с DISTINCT чтобы производить счет чисел различных значений в данном поле. Мы могли бы использовать ее, например, чтобы сосчитать номера продавцов в настоящее время описанных в таблице Порядков (вывод показывается в Рисунке 6.3 ):

SELECT COUNT (DISTINCT snum)

FROM Orders;

ИСПОЛЬЗОВАНИЕ DISTINCT

Обратите внимание в вышеупомянутом примере, что DISTINCT, сопровождаемый именем поля с которым он применяется, помещен в круглые скобки, но не сразу после SELECT, как раньше. Этого использования DISTINCT с COUNT применяемого к индивидуальным столбцам, требует стандарт ANSI, но большое количество программ не предъявляют к ним такого требования.

=============== SQL Execution Log ============

| |

| SELECT COUNT (DISTINCT snum) |

| FROM Orders; |

| ==============================================|

| |

| ------- |

| 5 |

| |

| |

===============================================

Рисунок 6.3: Подсчет значений поля

Вы можете выбирать многочисленные счета(COUNT) из полей с помощью DISTINCT в одиночном запросе который, как мы видели в Главе 3, не выполнялся когда вы выбирали строки с помощью DISTINCT. DISTINCT может использоваться таким образом, с любой функцией агрегата, но наиболее часто он используется с COUNT. С MAX и MIN, это просто не будет иметь никакого эффекта, а SUM и AVG, вы обычно применяете для включения повторяемых значений, так как они законно эффективнее общих и средних значений всех столбцов.

ИСПОЛЬЗОВАНИЕ COUNT СО СТРОКАМИ, А НЕ ЗНАЧЕНИЯМИ

Чтобы подсчитать общее число строк в таблице, используйте функцию COUNT со звездочкой вместо имени поля, как например в следующем примере, вывод из которого показан на Рисунке 6.4:

SELECT COUNT (*)

FROM Customers

COUNT со звездочкой включает и NULL и дубликаты, по этой причине DISTINCT не может быть использован. DISTINCT может производить более высокие номера чем COUNT особого поля, который удаляет все

=============== SQL Execution Log ============

| |

| SELECT COUNT (*) |

| FROM Customers; |

| ==============================================|

| |

| ------- |

| 7 |

| |

| |

===============================================

Рисунок 6. 4: Подсчет строк вместо значений

Строки, имеющие избыточные или NULL данные в этом поле.

DISTINCT не применим с COUNT (*), потому, что он не имеет никакого действия в хорошо разработанной и поддерживаемой базе данных.

В такой базе данных, не должно быть ни таких строк, которые бы являлись полностью пустыми, ни дубликатов (первые не содержат никаких данных, а последние полностью избыточны). Если, с другой стороны, все таки имеются полностью пустые или избыточные строки, вы вероятно не захотите чтобы COUNT скрыл от вас эту информацию.

ВКЛЮЧЕНИЕ ДУБЛИКАТОВ В АГРЕГАТНЫЕ ФУНКЦИИ

Агрегатные функции могут также (в большинстве реализаций) использовать аргумент ALL, который помещается перед именем поля, подобно DISTINCT, но означает противоположное: - включать дубликаты.

ANSI технически не позволяет этого для COUNT, но многие реализации ослабляют это ограничение. Различия между ALL и * когда они используются с COUNT -

* ALL использует имя_поля как аргумент.

* ALL не может подсчитать значения NULL.

Пока * является единственным аргументом который включает NULL значения, и он используется только с COUNT; функции отличные от COUNT игнорируют значения NULL в любом случае. Следующая команда подсчитает(COUNT) число не-NULL значений в поле rating

в таблице Заказчиков (включая повторения):

SELECT COUNT (ALL rating )

FROM Customers;

АГРЕГАТЫ, ПОСТРОЕННЫЕ НА СКАЛЯРНОМ ВЫРАЖЕНИИ

До этого, вы использовали агрегатные функции с одиночными полями как аргументами. Вы можете также использовать агрегатные функции с аргументами которые состоят из скалярных выражений включающих одно или более полей. (Если вы это делаете, DISTINCT не разрешается.)

Предположим, что таблица Порядков имеет еще один столбец который хранит предыдущий неуплаченный баланс (поле blnc) для каждого заказчика. Вы должны найти этот текущий баланс, добавлением суммы приобретений к предыдущему балансу.

Вы можете найти наибольший неуплаченный баланс следующим образом:

SELECT MAX (blnc + (amt))

FROM Orders;

Для каждой строки таблицы, этот запрос будет складывать blnc и amt для этого заказчика и выбирать самое большое значение которое он найдет. Конечно, пока заказчики могут иметь многочисленные порядки, их неуплаченный баланс оценивается отдельно для каждого порядка. Возможно, порядок с более поздней датой будет иметь самый большой неуплаченный баланс. Иначе, старый баланс должен быть выбран как в запросе выше.

Фактически, имеются большое количество ситуаций в SQL, где вы можете использовать скалярные выражения с полями или вместо полей, как вы увидите это в Главе 7 ФОРМИРОВАНИЕ ВЫВОДОВ ЗАПРОСОВ.

ПРЕДЛОЖЕНИЕ GROUP BY

Предложение GROUP BY позволяет вам определять подмножество значений в особом поле в терминах другого поля, и применять функцию агрегата к подмножеству. Это дает вам возможность объединять поля и агрегатные функции в едином предложении SELECT. Например, предположим что вы хотите найти наибольшую сумму приобретений, полученную каждым продавцом. Вы можете сделать раздельный запрос для каждого из них, выбрав MAX (amt) из таблицы Порядков для каждого значения поля snum. GROUP BY, однако, позволит Вам поместить их все в одну команду:

SELECT snum, MAX (amt)

FROM Orders

GROUP BY snum;