Отбор записей
Выражения в выборках
Ко всем числовым полям типа int, smallint, tinyint, float, real, monay и smallmonay можно применять арифметические операторы. При этом необходимо учитывать, что:
· они могут выполнять вычисления с числовыми столбцами или числовыми константами;
· остаток после деления целых чисел не может вычисляться для столбцов с типом данных float, real, monay и smallmonay.
Приведем примеры. Пусть в таблице ТИП_НОМЕРА хранится цена проживания в сутки без налога на добавленную стоимость (НДС). Определим цену с учетом НДС.
USE Гостиница
DECLARE @@nds monay
SET @@nds = 0.18
SELECT [Число мест]=Число_мест, Цена, НДС=Цена*@@nds,
[Цена с НДС]=Цена*(@@nds+1)
FROM Тип_номера
Результат выборки:
Число мест Цена НДС Цена с НДС
---------- --------------------- ----------------------- ------------------------
1 1200.0000 216.00000 1416.00000
2 600.0000 108.00000 708.00000
3 400.0000 72.00000 472.00000
(3 row(s) affected)
Определим количество номеров, имеющихся в гостинице. Очевидно, что это количество будет равно числу записей таблицы НОМЕР.
SELECT COUNT(*) AS [Число номеров в гостинице]
FROM Номер
Результат выборки:
Число номеров в гостинице
-------------------------
(1 row(s) affected)
Определим максимальную стоимость проживания в сутки.
SELECT [Максимальная стоимость] = MAX(Цена) * (@@nds+1)
FROM Тип_номера
Результат выборки:
Максимальная стоимость
------------------------
1416.00000
(1 row(s) affected)
Основным способом отбора записей является предложение WHERE. При использовании технологии «клиент-сервер» количество передаваемых по сети данных очень сильно влияет на производительность информационной системы. Идеальный способ обработки данных – это использование хранимых процедур, когда пользовательское приложение обменивается с сервером только параметрами и результатами. Но это не всегда возможно, а часто и не нужно, потому что просмотр пользователем информации тоже имеет значение. Предложение WHERE позволяет не только сократить объем передаваемой по сети информации, но и существенно уменьшить время выполнения команды SELECT.
Таблица 4.9 - Операторы, используемые в предложении WHERE
Тип операторов | Операторы |
Сравнение | =, >, <, >=, <=, <>, !=, !<, !> |
Интервал | BETWEEN, NOT BETWEEN |
Список | IN, NOT IN |
Сравнение строк | LIKE, NOT LIKE |
Проверка значения | IS NULL, IS NOT NULL |
Логические | AND, OR |
Отрицание | NOT |
При построении условий поиска необходимо помнить, что оптимизатор запросов не может работать с отрицанием. Другими словами, при наличии отрицания в предложении WHERE отбор записей выполняется путем последовательного сканирования таблицы. При использовании операторов сравнения необходимо придерживаться следующих правил:
· Выражения могут содержать константы, имена столбцов, функции, вложенные запросы и арифметические операторы.
· Лучше использовать одинарные кавычки при работе со строками символов (двойные кавычки допустимы), поскольку при этом обеспечивается совместимость со стандартом ANSI.
Операторы сравнения можно использовать для работы с числами, датами и строками.
В качестве примера рассмотрим запрос, в котором определим клиентов из Москвы.
SELECT Фамилия [Клиенты из Москвы]
FROM Клиент
WHERE Город = 'Москва'
Результат запроса:
Клиенты из Москвы
--------------------
Сидоров
Артемьев
(2 row(s) affected)
В данном примере предполагалось, что названия всех городов в таблице начинаются с прописной буква, все остальные буквы – строчные, отсутствуют ведущие пробелы, в названиях населенных отсутствуют обозначения их типов (например, г., пос.). В реальных базах данных эти условия могут не выполняться. Для того, чтобы выбрать из таблицы необходимые данных при произвольном вводе их пользователем, необходимо приведение символов к одному регистру и использование ассоциативного поиска данных. Ассоциативный отбор необходим и в том случае, когда пользователь не знает точного названия объекта. При ассоциативном отборе используется оператор LIKE, синтаксис которого имеет вид:
WHERE <Имя столбца> [NOT] LIKE <Шаблон> [ESCAPE <Символ>]
Шаблон должен быть заключен в кавычки и может включать знаки подстановки. ANSI SQL обеспечивает два знака подстановки: процент (%) и подчеркивание ( _ ). Процент заменяет собой строку из любого количества символов, а знак подчеркивания - только один символ. Опция ESCAPE используется в том редком случае, когда поисковое значение включает в себя один из знаков подстановки и его необходимо рассматривать буквально. В некоторых диалектах языка SQL, в том числе и Transact-SQL, поддерживаются еще два вида подстановки:
· квадратные скобки [] - показывают, что символ должен лежать в указанном списке;
· [^] - показывают на то, что символ не должен принадлежать диапазону.
Тогда запрос для предыдущего примера будет выглядеть следующим образом:
SELECT Фамилия [Клиенты из Москвы]
FROM Клиент
WHERE UPPER(Город) LIKE '%МОСКВА%'
Напишем запрос, с помощью которого выбираются названия городов, начинающиеся с букв «В» и «И».
SELECT Город FROM Клиент WHERE Город LIKE '[ВИ]%'
Результат выборки:
Город
--------------------
Иваново
Владимир
(2 row(s) affected)
При решении многих задач возникает необходимость использования нескольких условий отбора, которые объединяются логическими операторами. Например, выборка всех клиентов из Владимира и Москвы может быть записана следующим образом:
SELECT Фамилия [(Клиенты из Москвы и Владимира)], Город
FROM Клиент
WHERE Город = 'Москва' OR Город = 'Владимир'
Результат выборки:
(Клиенты из Москвы и Владимира) Город
------------------------------- ------------------
Владимиров Владимир
Сидоров Москва
Артемьев Москва
(3 row(s) affected)
Тот же результат можно получить с использованием в предложении WHERE списка:
WHERE Город IN ('Владимир', 'Москва')
Оператор IN более удобен, чем эквивалентный ему набор операторов OR, особенно когда число элементов списка велико.
Часто приходится проводить отбор данных, значения которых лежат в определенном диапазоне. Для этой цели используется оператор BETWEEN, хотя его всегда можно заменить двумя операторами «>=» и «<=». В качестве примера определим число клиентов, прибывших в гостиницу с 27 февраля 2001 года по 1 марта 2001 г.
SELECT [Число клиентов]=COUNT(*)
FROM ПРОЖИВАЕТ
WHERE Дата_прибытия BETWEEN '02/27/01' AND '03/01/01'
Результат:
Число клиентов
--------------
(1 row(s) affected)
Другой вариант записи предложения WHERE для данной задачи:
WHERE Дата_прибытия >= '02/27/01' AND Дата_прибытия <= '03/01/01'
Оператор BETWEEN можно использовать не только с числовыми полями и полями типа Datetime, но и с полями символьного типа.
Например, необходимо получить список клиентов, начинающийся Ивановым и оканчивающийся Сидоровым.
SELECT Фамилия
FROM Клиент
WHERE Фамилия BETWEEN 'Иванов' AND 'Сидоров'
Результат выборки:
Фамилия
--------------------
Иванов
Попович
Сазонов
Котов
Сидоров
(5 row(s) affected)
По умолчанию выводятся все записи, которые удовлетворяют условию, указанному в предложении WHERE. Но в ряде случаев интерес представляют только уникальные записи. Например, необходимо получить список всех городов из таблицы КЛИЕНТ, полагая при этом, что из одного и того же города в гостинице могли останавливаться несколько клиентов. Для этого можно использовать ключевое слово DISTINCT.
SELECT DISTINCT Город FROM КЛИЕНТ
Результат выборки:
Город
--------------------
Алексеевка
Владимир
Иваново
Казань
Ковров
Москва
Петушки
Ставрово
Тверь
(9 row(s) affected)
Хотя со значением NULL не возможны операции сравнения, тем не менее DISTINCT различает, что записи дублируются по NULL, и выводит только одну запись. При включении ключевого слова DISTINCT в инструкция SELECT автоматически упорядочивает записи. Недостатком использования DISTINCT является большое время выполнения запроса, поскольку при этом проверяются все поля очередной добавленной записи на предмет обнаружения в них изменений в них по сравнению с предыдущей записью. Поэтому предпочтительнее использовать в запросе вместо DISTINCT предложение GROUP BY, которое будет рассмотрено ниже.