Справка по SQL - W Cat

Шаблон

Описание

'abc%'

Любые строки, которые начинаются с букв "abc".

'abc_'

Строки длиной строго 4 символа, причем первыми символами строки должны быть "abc".

'%z'

Любая последовательность символов, которая обязательно заканчивается символом "z".

'%Rostov%'

Любая последовательность символов, содержащая слово "Rostov" в любом месте строки.

Пример. Найти все корабли, имена классов которых заканчиваются на букву 'о':

SELECT *

FROM Ships

WHERE class LIKE '%o';

Результатом выполнения запроса будет следующая таблица:

name

class

launched

Haruna

Kongo

1916

Hiei

Kongo

1914

Kirishima

Kongo

1915

Kongo

Kongo

1913

Musashi

Yamato

1942

Yamato

Yamato

1941

Пример. Найти все корабли, имена классов которых заканчиваются на букву 'о', но не на 'go':

SELECT *

FROM Ships

WHERE class NOT LIKE '%go' AND class LIKE '%o';

name

class

launched

Musashi

Yamato

1942

Yamato

Yamato

1941

Если искомая строка содержит трафаретный символ, то следует задать управляющий символ в предложении ESCAPE. Этот управляющий символ должен использоваться в шаблоне перед трафаретным символом, сообщая о том, что трафаретный символ следует трактовать как обычный символ. Например, если в некотором поле следует отыскать все значения, содержащие символ "_", то шаблон '%_%' приведет к тому, что будут возвращены все записи из таблицы. В данном случае шаблон следует записать следующим образом:

'%#_%' ESCAPE '#'

Для проверки значения на соответствие строке "25%" можно воспользоваться таким предикатом:

LIKE '25|%' ESCAPE '|'

Истинностное значение предиката LIKE присваивается в соответствии со следующими правилами:

* Если либо проверяемое значение, либо образец, либо управляющий символ равен NULL, истинностное значение равно UNKNOWN.

* В противном случае, если проверяемое значение и образец имеют нулевую длину, истинностное значение равно TRUE.

* В противном случае, если проверяемое значение соответствует шаблону, то предикат LIKE равен TRUE.

* Если не соблюдается ни одно из перечисленных выше условий, предикат LIKE равен FALSE.

Проверка отсутствия/наличия значения. Предикат IS NULL

Предикат

IS [NOT] NULL

позволяет проверить отсутствие (наличие) значения в полях таблицы. Использование в этих случаях обычных предикатов сравнения может привести к неверным результатам, т.к. сравнение со значением NULL дает результат UNKNOWN (неизвестно).

Так, если требуется найти записи в таблице Pc, для которых в поле price отсутствует значение (поиск ошибок ввода), можно воспользоваться следующим оператором:

SELECT *

FROM Pc

WHERE price IS NULL;

Получение итоговых значений

Как узнать количество моделей ПК, выпускаемых тем или иным поставщиком? Как определить среднее значение цены на компьютеры, имеющие одинаковые технические характеристики? На эти и многие другие вопросы, связанные с некоторой статистической информацией, можно получить ответы при помощи итоговых (агрегатных) функций. Стандартом предусмотрены следующие агрегатные функции:

Функция

Описание

COUNT(*)

Возвращает количество строк источника записей.

COUNT()

Возвращает количество значений в указанном столбце.

SUM()

Возвращает сумму значений в указанном столбце.

AVG()

Возвращает среднее значение в указанном столбце.

MIN()

Возвращает минимальное значение в указанном столбце.

MAX()

Возвращает максимальное значение в указанном столбце.

Все эти функции возвращают единственное значение. При этом функции COUNT, MIN и MAX применимы к любым типам данных, в то время как SUM и AVG используются только для числовых полей. Разница между функцией COUNT(*) и COUNT() состоит в том, что вторая при подсчете не учитывает NULL-значения.

Пример. Найти минимальную и максимальную цену на персональные компьютеры:

SELECT MIN(price) AS Min_price, MAX(price) AS Max_price

FROM PC;

Результатом будет единственная строка, содержащая агрегатные значения:

Min_price

Max_price

350.0

980.0

Пример. Найти имеющееся в наличии количество компьютеров, выпущенных производителем А:

SELECT COUNT(*) AS Qty

FROM PC

WHERE model IN

(SELECT model

FROM Product

WHERE maker = 'A');

В результате получим:

Qty

7

Пример. Если же нас интересует количество различных моделей, выпускаемых производителем А, то запрос можно сформулировать следующим образом (пользуясь тем фактом, что в таблице Product каждая модель записывается один раз):

SELECT COUNT(model) AS Qty_model

FROM Product

WHERE maker = 'A';

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

Qty_model

7

Пример. Найти количество имеющихся различных моделей, выпускаемых производителем А. Запрос похож на предыдущий, в котором требовалось определить общее число моделей, выпускаемых производителем А. Здесь же требуется найти число различных моделей в таблице PC (т.е. имеющихся в продаже).

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

SELECT COUNT(DISTINCT model) AS Qty

FROM PC

WHERE model IN

(SELECT model

FROM Product

WHERE maker = 'A');

даст следующий результат:

Qty

2

Если же нам требуется получить количество моделей ПК, производимых каждым производителем, то потребуется использовать предложение GROUP BY, синтаксически следующего после предложения WHERE.

Группировка записей. Предложение GROUP BY

Предложение GROUP BY используется для определения групп выходных строк, к которым могут применяться агрегатные функции (COUNT, MIN, MAX, AVG и SUM). Если это предложение отсутствует, и используются агрегатные функции, то все столбцы с именами, упомянутыми в SELECT, должны быть включены в агрегатные функции, и эти функции будут применяться ко всему набору строк, которые удовлетворяют предикату запроса. В противном случае все столбцы списка SELECT, не вошедшие в агрегатные функции, должны быть указаны в предложении GROUP BY. В результате чего все выходные строки запроса разбиваются на группы, характеризуемые одинаковыми комбинациями значений в этих столбцах. После этого к каждой группе будут применены агрегатные функции. Следует иметь в виду, что для GROUP BY все значения NULL трактуются как равные, т.е. при группировке по полю, содержащему NULL-значения, все такие строки попадут в одну группу.

Если при наличии предложения GROUP BY, в предложении SELECT отсутствуют агрегатные функции, то запрос просто вернет по одной строке из каждой группы. Эту возможность, наряду с ключевым словом DISTINCT, можно использовать для исключения дубликатов строк в результирующем наборе.

Рассмотрим простой пример:

SELECT model, COUNT(model) AS Qty_model, AVG(price) AS Avg_price

FROM PC

GROUP BY model;

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

model