Использование операторов сравнения 3 страница
AND
- когда должны удовлетворяться оба разделяемых с помощью AND условия;
OR
- когда должно удовлетворяться одно из разделяемых с помощью OR условий;
AND NOT
- когда должно удовлетворяться первое условие и не должно второе;
OR NOT
- когда или должно удовлетворяться первое условие или не должно удовлетворяться второе,
причем существует приоритет AND над OR (сначала выполняются все операции AND и только после этого операции OR). Для получения желаемого результата WHERE условия должны быть введены в правильном порядке, который можно организовать введением скобок.
При обработке условия числа сравниваются алгебраически - отрицательные числа считаются меньшими, чем положительные, независимо от их абсолютной величины. Строки символов сравниваются в соответствии с их представлением в коде, используемом в конкретной СУБД, например, в коде ASCII. Если сравниваются две строки символов, имеющих разные длины, более короткая строка дополняется справа пробелами для того, чтобы они имели одинаковую длину перед осуществлением сравнения.
Наконец, синтаксис фразы GROUP BY имеет вид
GROUP BY [таблица.]столбец [,[таблица.]столбец] ... [HAVING фраза]
GROUP BY инициирует перекомпоновку формируемой таблицы по группам, каждая из которых имеет одинаковое значение в столб-цах, включенных в перечень GROUP BY. Далее к этим группам применяются агрегирующие функции, указанные во фразе SELECT, что приводит к замене всех значений группы на единственное значение (сумма, количество и т.п.).
С помощью фразы HAVING (синтаксис которой почти не отличается от синтаксиса фразы WHERE)
HAVING [NOT] HAVING_условие [[AND|OR][NOT] HAVING_условие]...
можно исключить из результата группы, не удовлетворяющие заданным условиям:
значение { = | <> | < | <= | > | >= } { значение | ( подзапрос )
| SQL_функция }
{значение_1 | SQL_функция_1} [NOT] BETWEEN
{значение_2 | SQL_функция_2} AND {значение_3 | SQL_функция_3}
{значение | SQL_функция} [NOT] IN { ( константа [,константа]... )
| ( подзапрос ) }
{значение | SQL_функция} IS [NOT] NULL
[таблица.]столбец [NOT] LIKE 'строка_символов' [ESCAPE 'символ']
EXISTS ( подзапрос )
2.2. Выборка без использования фразы WHERE
Простая выборка
Запрос выдать название, статус и адрес поставщиков
SELECT Название, Статус, Адрес
FROM Поставщики;
дает результат, приведенный на рис. 2.1,а.
При необходимости получения полной информации о поставщиках, можно было бы дать запрос
SELECT ПС, Название, Статус, Город, Адрес, Телефон
FROM Поставщики;
или использовать его более короткую нотацию:
SELECT *
FROM Поставщики;
Здесь "звездочка" (*) служит кратким обозначением всех имен полей в таблице, указанной во фразе FROM. При этом порядок вывода полей соответствует порядку, в котором эти поля определялись при создании таблицы.
Еще один пример. Выдать основу всех блюд:
SELECT Основа
FROM Блюда;
дает результат, показанный на рис. 2.1,б.
| а) | б) | в) | |||||||||||||||||||||||||||||||||||||||||||||||
|
|
|
Рис. 2.1. Примеры простой выборки
Исключение дубликатов
В предыдущем примере был выдан правильный, но не совсем удачный перечень основных продуктов: из него не были исключены дубликаты. Для исключения дубликатов и одновременного упорядочения перечня необходимо дополнить запрос ключевым словом DISTINCT (различный, различные), как показано в следующем примере:
SELECT DISTINCT Основа
FROM Блюда;
Результат приведен на рис. 2.1,в.
2.2.3. Выборка вычисляемых значений
Из синтаксиса фразы SELECT (п.2.1) видно, что в ней может содержаться не только перечень столбцов таблицы или символ *, но и выражения.
Например, если нужно получить значение калорийности всех продуктов, то можно учесть, что при окислении 1 г углеводов или белков в организме освобождается в среднем 4.1 ккал, а при окислении 1 г жиров - 9.3 ккал, и выдать запрос:
SELECT Продукт, ((Белки+Углев)*4.1+Жиры*9.3)
FROM Продукты;
результат которого приведен на рис. 2.2,а.
| а) | б) | в) | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
|
|
Рис. 2.2. Примеры запросов с вычисляемыми полями
Фраза SELECT может включать не только выражения, но и отдельные числовые или текстовые константы. Следует отметить, что текстовые константы должны заключаться в апострофы ('). На рис. 2.2,б приведен результат запроса:
SELECT Продукт, 'Калорий =', ((Белки+Углев)*4.1+Жиры *9.3)
FROM Продукты;
А что произойдет, если какой-либо член выражения не определен, т.е. имеет значение NULL и каким образом появилось такое значение?
Если при загрузке строк таблицы в какой-либо из вводимых строк отсутствует значение для какого-либо столбца, то СУБД введет в такое поле NULL-значение. NULL-значение "придумано" для того, чтобы представить единым образом "неизвестные значения" для любых типов данных. Действительно, так как при вводе данных в столбец или их изменении СУБД запрещает ввод значений не соответствующих описанию данных этого столбца, то, например, нельзя использовать пробел для отсутствующего значения числа. Нельзя для этих целей использовать и ноль: нет месяца или дня недели равного нулю, да и для чисел ноль не может рассматриваться как неизвестное значение в одном месте и как известное - в другом. При выводе же NULL-значения на экран или печатающее устройство его код воспроизводится каким-либо специально заданным символом или набором символов: например, пробелом (если его нельзя перепутать с текстовым значением пробела) или сочетанием -0-.
С помощью специальной команды можно установить в СУБД один из режимов представления NULL-значений при выполнении числовых расчетов: запрет или разрешение замены NULL-значения нулем. В первом случае любое арифметическое выражение, содержащее неопределенный операнд, будет также иметь неопределенное значение. Во втором случае результат вычислений будет иметь численное значение (если это значение попадает в диапазон представления соответствующего типа данных).
Например, при выполнении запроса
SELECT ПР, Цена, К_во, (Цена * К_во)
FROM Поставки;
и разных "настройках" СУБД могут быть получены разные результаты:
|
|
2.3. Выборка c использованием фразы WHERE
Использование операторов сравнения
В синтаксисе фразы WHERE (п.2.1) показано, что для отбора нужных строк таблицы можно использовать операторы сравнения = (равно), <> (не равно), < (меньше), <= (меньше или равно), > (больше), >= (больше или равно), которые могут предваряться оператором NOT, создавая, например, отношения "не меньше" и "не больше".
Так, для получения перечня продуктов, практически не содержащих углеводов, можно сформировать запрос
SELECT Продукт, Белки, Жиры, Углев, K, Ca, Na, B2, PP, C
FROM Продукты
WHERE Углев = 0;
и получить:
| Продукт | Белки | Жиры | Углев | K | Ca | Na | B2 | PP | C |
| Говядина | 189. | 124. | 0. | 1.5 | 28. | ||||
| Судак | 190. | 80. | 0. | 270 0 | 1.1 | 10. |
Возможность использования нескольких условий, соединенных логическими операторами AND, OR, AND NOT и OR NOT, позволяет осуществить более детальный отбор строк. Так, для получения перечня продуктов, практически не содержащих углеводов и натрия, можно сформировать запрос:
SELECT Продукт, Белки, Жиры, Углев, K, Ca, Na, B2, PP, C
FROM Продукты
WHERE Углев = 0 AND Na = 0;
Результат запроса имеет вид
| Продукт | Белки | Жиры | Углев | K | Ca | Na | B2 | PP | C |
| Судак | 190. | 80. | 0. | 1.1 | 10. |
Добавим к этому запросу еще одно условие
SELECT Продукт, Белки, Жиры, Углев, K, Ca, Na, B2, PP, C
FROM Продукты
WHERE Углев = 0 AND Na = 0 AND Продукт <> 'Судак';
и получим на экране сообщение "No rows exist or satisfy the specified clause" или аналогичное (в зависимости от вкусов разработчиков разных СУБД), информирующее об отсутствии строк, удовлетворяющих заданному(ым) условию(ям).
2.3.2. Использование BETWEEN
С помощью BETWEEN ... AND ... (находится в интервале от ... до ...) можно отобрать строки, в которых значение какого-либо столбца находятся в заданном диапазоне.
Например, выдать перечень продуктов, в которых значение содержания белка находится в диапазоне от 10 до 50:
| Результат: | ||
| SELECT Продукт, Белки FROM Продукты WHERE Белки BETWEEN 10 AND 50; | ||
| Продукт | Белки | |
| Майонез | 31. | |
| Сметана | 26. | |
| Молоко | 28. | |
| Морковь | 13. | |
| Лук | 17. |
Можно задать и NOT BETWEEN (не принадлежит диапазону между), например:
| Результат: | |||
| SELECT Продукт, Белки, Жиры FROM Продукты WHERE Белки NOT BETWEEN 10 AND 50 AND Жиры > 100; | |||
| Продукт | Белки | Жиры | |
| Говядина | 189. | 124. | |
| Масло | 60. | 825. | |
| Яйца | 127. | 115. |
BETWEEN особенно удобен при работе с данными, задаваемыми интервалами, начало и конец которых расположен в разных столбцах.
Для примера воспользуемся таблицей "минимальных окладов" (табл. 2.1), величина которых непосредственно связана со студенческой стипендией. В этой таблице для текущего значения минимального оклада установлена запредельная дата окончания 9 сентября 9999 года.
Таблица 2.1
Минимальные оклады
| Миноклад | Начало | Конец |
| 01-01-1993 | 31-03-1993 | |
| 01-04-1993 | 30-06-1993 | |
| 01-07-1993 | 30-11-1993 | |
| 01-12-1993 | 30-06-1994 | |
| 01-07-1994 | 09-09-9999 |
Если, например, потребовалось узнать, какие изменения минимальных окладов производились в 1993/94 учебном году, то можно выдать запрос
SELECT Начало, Миноклад
FROM Миноклады
WHERE Начало BETWEEN '1-9-1993' AND '31-8-1994'
и получить результат:
| Начало | Миноклад |
| 01-12-1993 | |
| 01-07-1994 |
Отметим, что при формировании запросов значения дат следует заключать в апострофы, чтобы СУБД не путала их с выражениями и не пыталась вычитать из 31 значение 8, а затем 1994.
Для выявления всех значений минимальных окладов, которые существовали в 1993/94 учебном году, можно сформировать запрос
SELECT *
FROM Миноклады
WHERE Начало BETWEEN '1-9-1993' AND '31-8-1994'
OR Конец BETWEEN '1-9-1993' AND '31-8-1994'
| Миноклад | Начало | Конец |
| 01/07/1993 | 30/11/1993 | |
| 01/12/1993 | 30/06/1994 | |
| 01/07/1994 | 09/09/9999 |
Наконец, для получения минимального оклада на 15-5-1994:
| Результат: | ||
| SELECT Миноклад FROM Миноклады WHERE '15-05-1994' BETWEEN Начало AND Конец | ||
| Миноклад | ||
2.3.3. Использование IN
Выдать сведения о блюдах на основе яиц, крупы и овощей
SELECT *
FROM Блюда
WHERE Основа IN (Яйца Крупа Овощи);
Результат:
| БЛ | Блюдо | В | Основа | Выход | Труд |
| Салат летний | З | Овощи | 200. | ||
| Салат витаминный | З | Овощи | 200. | ||
| Драчена | Г | Яйца | 180. | ||
| Морковь с рисом | Г | Овощи | 260. | ||
| Омлет с луком | Г | Яйца | 200. | ||
| Каша рисовая | Г | Крупа | 210. | ||
| Пудинг рисовый | Г | Крупа | 160. | ||
| Помидоры с луком | Г | Овощи | 260. |
Рассмотренная форма IN является в действительности просто краткой записью последовательности отдельных сравнений, соединенных операторами OR. Предыдущее предложение эквивалентно такому:
SELECT *
FROM Блюда
WHERE Основа=Яйца OR Основа=Крупа OR Основа=Овощи;
Можно задать и NOT IN (не принадлежит), а также возможность использования IN (NOT IN) с подзапросом (см. главу 3).
2.3.4. Использование LIKE
Выдать перечень салатов
| Результат: | ||
| SELECT Блюдо FROM Блюда WHERE Блюдо LIKE 'Салат%'; | ||
| Блюдо | ||
| Салат летний | ||
| Салат мясной | ||
| Салат витаминный | ||
| Салат рыбный |
Обычная форма "имя_столбца LIKE текстовая_константа" для столбца текстового типа позволяет отыскать все значения указанного столбца, соответствующие образцу, заданному "текстовой_константой". Символы этой константы интерпретируются следующим образом:
- символ _ (подчеркивание) – заменяет любой одиночный символ,
- символ % (процент) – заменяет любую последовательность из N символов (где N может быть нулем),
- все другие символы означают просто сами себя.
Следовательно, в приведенном примере SELECT будет осуществлять выборку записей из таблицы Блюда, для которых значение в столбце Блюдо начинается сочетанием 'Салат' и содержит любую последовательность из нуля или более символов, следующих за сочетанием 'Салат'. Если бы среди блюд были "Луковый салат", "Фруктовый салат" и т.п., то они не были бы найдены. Для их отыскания надо изменить фразу WHERE:
WHERE Блюдо LIKE '%салат%'
или при отсутствии различий между малыми и большими буквами (такую настройку допускают некоторые СУБД):
WHERE Блюдо LIKE '%Салат%'
Это позволит отыскать все салаты.
2.3.5. Вовлечение неопределенного значения (NULL-значения)
Как было рассказано в п.2.2.3, если при загрузке данных не введено значение в какое-либо поле таблицы, то СУБД поместит в него NULL-значение. Аналогичное значение можно ввести в поле таблицы, выполняя операцию изменения данных. Так, при отсутствии сведений о наличии у поставщиков судака и моркови в столбцы Цена и К_во соответствующих строк таблицы Поставки вводится NULL и там будет храниться код NULL-значения, а не 0, 0. или пробел. (Отметим, что в распечатке таблицы Поставки рис.1.1 в этих местах расположен пробел, установленный в СУБД для представления NULL-значения при выводе на печать).
В этом случае для выявления названий продуктов, отсутствующих в кладовой, шеф-повар может дать запрос
| Результат: | ПР | |
| SELECT DISTINCT ПР FROM Наличие WHERE К_во IS NULL; | 2 9 |
Естественно, что для выявления продуктов, существующих в кладовой, следует дать запрос
SELECT DISTINCT ПР
FROM Наличие
WHERE К_во IS NOT NULL;
Использование условий
столбец IS NULL и столбец IS NOT NULL
вместо, например,
столбец = NULL и столбец <> NULL
связано с тем, что ничто - и даже само NULL-значение - не считается равным другому NULL-значению. (Несмотря на это, два неопределенных значения рассматриваются, однако, как дубликаты друг друга при исключении дубликатов, и предложение SELECT DISTINCT даст в результате не более одного NULL-значения.)
2.4. Выборка с упорядочением
Синтаксис фразы упорядочения был дан в п. 2.1. Простейший вариант этой фразы - упорядочение строк результата по значению одного из столбцов с указанием порядка сортировки или без такого указания. (По умолчанию строки будут сортироваться в порядке возрастания значений в указанном столбце.)
Например, выдать перечень продуктов и содержание в них основных веществ в порядке убывания содержания белка
| Результат: | ||||||||||||||||||||||||||||||||||
|
| |||||||||||||||||||||||||||||||||
При включении в список ORDER BY нескольких столбцов СУБД сортирует строки результата по значениям первого столбца списка пока не появится несколько строк с одинаковыми значениями данных в этом столбце. Такие строки сортируются по значениям следующего столбца из списка ORDER BY и т.д.
Например, выдать содержимое таблицы Блюда, отсортировав ее строки по видам блюд и основе:
| Результат: | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
| |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
Кроме того, в список ORDER BY можно включать не только имя столбца, а его порядковую позицию в перечне SELECT. Благодаря этому возможно упорядочение результатов на основе вычисляемых столбцов, не имеющих имен.
Например, запрос
SELECT Продукт, ((Белки+Углев)*4.1+Жиры*9.3)
FROM Продукты
ORDER BY 2;
позволит получить список продуктов, показанный на рис.2.2,в – переупорядоченный по возрастанию значений калорийности список рис.2.2,а.
2.5. Агрегирование данных
SQL-функции
В SQL существует ряд специальных стандартных функций (SQL-функций). Кроме специального случая COUNT(*) каждая из этих функций оперирует совокупностью значений столбца некоторой таблицы и создает единственное значение, определяемое так:
COUNT
- число значений в столбце,
SUM
- сумма значений в столбце,
AVG
- среднее значение в столбце,
MAX
- самое большое значение в столбце,
MIN
- самое малое значение в столбце.
Для функций SUM и AVG рассматриваемый столбец должен содержать числовые значения.
Следует отметить, что здесь столбец - это столбец виртуальной таблицы, в которой могут содержаться данные не только из столбца базовой таблицы, но и данные, полученные путем функционального преобразования и (или) связывания символами арифметических операций значений из одного или нескольких столбцов. При этом выражение, определяющее столбец такой таблицы, может быть сколь угодно сложным, но не должно содержать SQL-функций (вложенность SQL-функций не допускается). Однако из SQL-функций можно составлять любые выражения.
Аргументу всех функций, кроме COUNT(*), может предшествовать ключевое слово DISTINCT (различный), указывающее, что избыточные дублирующие значения должны быть исключены перед тем, как будет применяться функция. Специальная же функция COUNT(*) служит для подсчета всех без исключения строк в таблице (включая дубликаты).
2.5.2. Функции без использования фразы GROUP BY
Если не используется фраза GROUP BY, то в перечень элементов_SELECT можно включать лишь SQL-функции или выражения, содержащие такие функции. Другими словами, нельзя иметь в списке столбцы, не являющихся аргументами SQL-функций.
Например, выдать данные о массе лука (ПР=10), проданного поставщиками, и указать количество этих поставщиков:
| Результат: | ||
| SELECT SUM(К_во),COUNT(К_во) FROM Поставки WHERE ПР = 10; | ||
| SUM(К_во) | COUNT(К_во) | |
Если бы для вывода в результат еще и номера продукта был сформирован запрос
SELECT ПР,SUM(К_во),COUNT(К_во)
FROM Поставки
WHERE ПР = 10;
то было бы получено сообщение об ошибке. Это связано с тем, что SQL-функция создает единственное значение из множества значений столбца-аргумента, а для "свободного" столбца должно быть выдано все множество его значений. Без специального указания (оно задается фразой GROUP BY) SQL не будет выяснять, одинаковы значения этого множества (как в данном примере, где ПР=10) или различны (как было бы при отсутствии WHERE фразы). Поэтому подобный запрос отвергается системой.