3 Расширенные возможности языка dml

В этом разделе описаны функции, операторы, предикаты и приведены способы упорядочивания выборок.

3.1 Функции

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

В соответствие со стандартом ANSI SQL поддерживает агрегатные функции. Их применяют для определения:

суммарного значения — SUM();

среднего арифметического всех определенных значений (отличных от NULL) — AVG();

минимального значения — MIN();

максимального значения — MAX();

числа значений, которые удовлетворяют некоторому критерию — COUNT();

числа значений, отличных от NULL — COUNT(*).

Следует отметить, что функции применяют к столбцам, а не самой таблице.

Например, вычислить количество типов объектов аренды

SELECT COUNT(*)

FROM Rent

Определить число объектов, стоимость аренды которых больше 60 у.e. можно так:

SELECT COUNT(Rn)

FROM Rent

WHERE Rn > 60

SQL также поддерживает строковые функции:

• UPPER() — преобразование букв в строчные;l

• LOWER() — преобразование букв в прописные;l

• TRIM() — удаления цепочек символов предшествующих или последующих за строкой;

• SUBSTRING() — выделения подстроки из строки.

Например, удалить предшествующую цепочку, которая состоит из символов '2'

SELECT TRIM(LEADING '2' from Ow)

FROM owner

Например, вывести типы квартир

SELECT SUBSTRING(Typ FROM 1 FOR 4)

FROM Rent

WHERE SUBSTRING(Typ FROM 6) = 'квартира'

Данный запрос, используя функцию SUBSTRING(Typ FROM 1 FOR 4) позволяет отобразить первых 4 символа поля Typ, при условии, что все его символы в текущей записи (с 6-го до последнего) — 'квартира'.

SQL позволяет извлекать дату и время, используя функцию работы с датами:

EXTRACT (<данное> FROM <имяПоля>)

Например, необходимо извлечь значение года, которое хранится в поле LDate:

SELECT EXTRACT(YEAR FROM LDate)

FROM Lease

Можно также извлекать месяц (MONTH), день (DAY), час (HOUR), минуты (MINUTE) и секунды (SECOND).

Значения дат можно сравнивать друг с другом, сравнивать с относительными датами, вычитать одну дату из другой. Например, получить номера договоров аренды заключенных за 2 последних года

SELECT NLease

FROM Lease

WHERE '1999.12.31' — LDate < 365 * 2 + 1

Отметим, что допускаются следующие форматы представления даты: '1999-OCT-27', '02/27/10' и '01.10.27'. Например, получить номера договоров аренды заключенных после 1 января 2003 года:p

SELECT NLease, LDate

FROM Lease

WHERE LDate > '2001-JUN-01'

3.2 Операторы

Язык SQL кроме функций поддерживает арифметические операторы

Оператор	Описание
+	Сложение
-	Вычитание
∗	Умножение
/	Деление

Например, с помощью умножения можно определить, какой будет арендная плата, если ее увеличить на 20%

SELECT 1.2*Rn AS UpRenta

FROM Rent

Квалификатор AS присваивает название столбцу результата.

Порядок вычисления выражений подчиняется общепринятым правилам: сначала выполняется умножение и деление, а затем — сложение и вычитание. Операции одного уровня выполняются слева направо. Разрешено применять скобки для изменения порядка вычислений.

Язык SQL поддерживает операторы сравнения.

Оператор	Описание
=	Равно
<>	Не равно
>	Больше
<	Меньше
>=	Больше или равно
<=	Меньше или равно
IS NULL	Не определено
LIKE	Подобно

Элементами сравнения могут быть:

• значение поля;

• строка;

• арифметическое выражение;

• агрегатная функция;

• встроенная функция;

• значение, возвращаемое подзапросом.

Примечание

Элементы сравнения должны быть сопоставимых типов.

Продемонстрируем применение оператора "не равно" на примере получения списка недвижимости, в который не входят дома:

SELECT AdR

FROM Realty

WHERE Typ <> "дом"

Поясним оператор IS NULL.

Предположим, что типы объектов аренды уже известны и введены в таблицу, а арендная плата за некоторые из них еще ни разу не назначалась. Такую ситуацию в таблице регистрируют как NULL — пустое, не определенное значение.

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

SELECT Typ

FROM Rent

WHERE Rn IS NULL

Поясним оператор LIKE.

Его используют только с символьными данными. Он проверяет, соответствует ли текущее значение поля строке с указанной маской. В качестве маски используются все разрешенные символы (с учетом строчных и прописных букв), а также специальные символы: % (знак процента), который замещает любое количество символов (в том числе и 0), а также _ (символ подчеркивания), замещающий только один символ.

Например, получить список адресов, где арендуют квартиры

SELECT AdR, Typ

FROM Realty

WHERE Typ LIKE "%кварт%"

Если требуется найти строку, которая содержит специальные символы (%, _), то:

с помощью ключевого слова ESCAPE определите так называемый escape-символ;

поместите его перед специальным символом, чтобы указать, что искомый символ следует а escape-символом.

Escape-символом может быть любой, никогда не появляющийся в упоминаемом столбце как информационный символ. Часто для этих целей используются символы @ и ~.

Например, получить список адресов, которые содержат _ (знак подчеркивания)

SELECT AdR, Typ

FROM Realty

WHERE Typ LIKE "%@_%" ESCAPE "@"

К логическим операторам относят AND, OR и NOT.

Ниже описаны условия, когда значения предикатов — "истина", при условии, что они содержат следующие операторы:

Оператор	Условие истинности значения предиката
AND	Связываемые этим оператором условия истинны
OR	Хотя бы одно из связываемых этим оператором условий истинно
NOT	Условие, перед которым стоит этот оператор ложно

В одном предикате логические операторы выполняются в следующем порядке: сначала выполняется оператор NOT, затем AND и только после этого оператор OR. Для изменения порядка выполнения операторов используют скобки.

Например, необходимо получить список квартир, арендная плата за которые не превышает 100 у.е.:

SELECT Typ, Rn

FROM Rent

WHERE (Typ LIKE "%кварт%") AND (Rn <= 100)

Если вам необходимо выбрать записи, значения полей которых принадлежат некоторому диапазону, то используйте предикатBETWEEN. Например, необходимо просмотреть список квартир, арендная плата которых находится в диапазоне от 50 до 100 у.е.:

SELECT Typ

FROM Rent

WHERE Rn BETWEEN 50 AND 100

Если вам необходимо получить список владельцев, который начинается с фамилии "Иванов", а остальные следуют вплоть до "Клименко", то этот запрос будет выглядеть так:

SELECT Ow

FROM Owner

WHERE Ow BETWEEN "Иванов" AND "Клименко"

Предикат BETWEEN с отрицанием NOT позволяет получить выборку записей, значения полей которых находятся вне границ. Например, получить список договоров аренды, которые были заключены до 1.08.01 или после 1.03.02:

SELECT NLease

FROM Lease

WHERE LDate NOT BETWEEN "01.08.01" AND "02.03.01"

Для работы со списками используют предикат IN. Он позволяет проверять совпадения значения выражения, с каким либо элементом списка. Например, для просмотра адресов, где проживают арендаторы "Петров" и "Сидоров" воспользуйтесь командой:

SELECT AdT

FROM Tenant

WHERE Tn IN ("Петров", "Сидоров"))

Для просмотра адресов недвижимости, где проживают остальные арендаторы, можно использовать предикат IN с частицей NOT:

SELECT AdT

FROM Tenant

WHERE Tn NOT IN ("Петров", "Сидоров"))