Чтобы определить количество записей в таблице MySQL, нужно воспользоваться специальной функцией COUNT().
Функция COUNT() возвращает количество записей в таблице, соответствующих заданному критерию.
Функция COUNT(expr) всегда считает только те строки, у которых результатом выражения expr является NOT NULL .
Исключением из этого правила является использование функции COUNT() со звездочкой в качестве аргумента - COUNT(*) . В этом случае считаются все строки, вне зависимости от того, NULL они или NOT NULL .
Например, функция COUNT(*) возвращает общее количество записей в таблице:
SELECT COUNT(*) FROM table_name
Пример PHP+MySQL-кода для подсчета и вывода общего количества строк:
$res = mysql_query("SELECT COUNT(*) FROM table_name") $row = mysql_fetch_row($res); $total = $row; // всего записей echo $total; ?>
Этот пример иллюстрирует самый простой вариант использования функции COUNT() . Но с помощью этой функции можно выплнять и другие задачи.
Указав определенный столбец таблицы в виде параметра, функция COUNT(column_name) возвращает количество записей этого столбца, которые не содержат значение NULL . Записи со значениями NULL игнорируются.
SELECT COUNT(column_name) FROM table_name
Использовать функцию mysql_num_rows() нельзя, потому что для того, чтобы узнать общее количество записей, нужно выполнить запрос SELECT * FROM db , то есть получить все записи, а это нежелательно, поэтому предпочтительнее использовать функцию count .
$result = mysql_query("SELECT COUNT (*) as rec FROM db");
Вот еще один пример использования функции COUNT() . Допустим, есть таблица ice_cream с каталогом мороженого, в которой находятся идентификаторы категорий и названия мороженого.
Как узнать количество моделей ПК, выпускаемых тем или иным поставщиком? Как определить среднее значение цены на компьютеры, имеющие одинаковые технические характеристики? На эти и многие другие вопросы, связанные с некоторой статистической информацией, можно получить ответы при помощи итоговых (агрегатных) функций . Стандартом предусмотрены следующие агрегатные функции:
Все эти функции возвращают единственное значение. При этом функции COUNT, MIN и MAX применимы к любым типам данных, в то время как SUM и AVG используются только для числовых полей. Разница между функцией COUNT(*) и COUNT(<имя поля>) состоит в том, что вторая при подсчете не учитывает NULL-значения.
Пример. Найти минимальную и максимальную цену на персональные компьютеры:
Пример. Найти имеющееся в наличии количество компьютеров, выпущенных производителем А:
Пример. Если же нас интересует количество различных моделей, выпускаемых производителем А, то запрос можно сформулировать следующим образом (пользуясь тем фактом, что в таблице Product каждая модель записывается один раз):
Пример. Найти количество имеющихся различных моделей, выпускаемых производителем А. Запрос похож на предыдущий, в котором требовалось определить общее число моделей, выпускаемых производителем А. Здесь же требуется найти число различных моделей в таблице PC (т.е. имеющихся в продаже).
Для того, чтобы при получении статистических показателей использовались только уникальные значения, при аргументе агрегатных функций можно использовать параметр DISTINCT . Другой параметр ALL используется по умолчанию и предполагает подсчет всех возвращаемых значений в столбце. Оператор,
Если же нам требуется получить количество моделей ПК, производимых каждым производителем, то потребуется использовать предложение GROUP BY , синтаксически следующего после предложения WHERE .
| SELECT model, COUNT(model) AS Qty_model, AVG(price) AS Avg_price FROM PC GROUP BY model; |
| model | Qty_model | Avg_price |
| 1121 | 3 | 850.0 |
| 1232 | 4 | 425.0 |
| 1233 | 3 | 843.33333333333337 |
| 1260 | 1 | 350.0 |
Если бы в SELECT присутствовал столбец с датой, то можно было бы вычислять эти показатели для каждой конкретной даты. Для этого нужно добавить дату в качестве группирующего столбца, и тогда агрегатные функции вычислялись бы для каждой комбинации значений (модель−дата).
Существует несколько определенных правил выполнения агрегатных функций :
Итак, если запрос не содержит предложения GROUP BY , то агрегатные функции , включенные в предложение SELECT , исполняются над всеми результирующими строками запроса. Если запрос содержит предложение GROUP BY , каждый набор строк, который имеет одинаковые значения столбца или группы столбцов, заданных в предложении GROUP BY , составляет группу, и агрегатные функции выполняются для каждой группы отдельно.
Если предложение WHERE определяет предикат для фильтрации строк, то предложение HAVING применяется после группировки для определения аналогичного предиката, фильтрующего группы по значениям агрегатных функций . Это предложение необходимо для проверки значений, которые получены с помощью агрегатной функции не из отдельных строк источника записей, определенного в предложении FROM , а из групп таких строк . Поэтому такая проверка не может содержаться в предложении WHERE .
Начиная от версии 4.0 в СУБД MySQL появилась достаточно удобная возможность подсчета количества всех подходящих под запрос записей, когда количество записей ограничивается LIMIT’ом. При работе с поиском в БД, а так же при выборках из таблиц с большим количеством записей такой функционал просто необходим.
Синтаксис. В запросе SELECT перед списком столбцов необходимо указать опцию SQL_CALC_FOUND_ROWS. Вот начало описания синтаксиса конструкции SELECT.
SELECT
select_expr, … …
Таким образом, выполняя запрос SELECT SQL_CALC_FOUND_ROWS СУБД подсчитает полное число строк, подходящих под условие запроса, и сохранить это число в памяти. Естественно, имеет смысл запрос SELECT SQL_CALC_FOUND_ROWS только при использовании ограничения (LIMIT). Сразу после выполнения запроса на выборку для получения количества записей нужно выполнить еще один SELECT-запрос: SELECT FOUND_ROWS ();. В результате MySQL вернет одну строку с одним полем, в котором и будет храниться число строк.
Пример самих запросов:
> SELECT SQL_CALC_FOUND_ROWS * FROM tbl_name WHERE number > 100 LIMIT 10;
> SELECT FOUND_ROWS();
Первый запрос вернет (выведет) 10 строк таблицы tbl_name, для которых выполняется условие number > 100. Второй вызов команды SELECT возвратит количество строк, которые возвратила бы первая команда SELECT, если бы она была написана без выражения LIMIT. Хотя при использовании команды SELECT SQL_CALC_FOUND_ROWS, MySQL должен пересчитать все строки в наборе результатов, этот способ все равно быстрее, чем без LIMIT, так как не требуется посылать результат клиенту.
Пример запросов из PHP:
$result = mysql_query(«SELECT SQL_CALC_FOUND_ROWS * FROM table1 LIMIT 0, 10″, $link);
while ($row = mysql_fetch_assoc($result))
{
var_dump($row);
}$result = mysql_query(«SELECT FOUND_ROWS()», $link);
$num_rows = mysql_result($result, 0);
echo «$num_rows Rows\n»;
В результате выполнения кода при условии, что $link указывает на открытое соединение с СУБД, PHP выведет 10 строк из таблицы table1 , а затем целочисленное значение количества строк, соответствующих запросу (без учета LIMIT).
В запросах с UNION SQL_CALC_FOUND_ROWS может вести себя двояко из-за того, что LIMIT может появляться в нескольких местах. Счет строк может вестись для индивидуальных SELECT-запросов, или же для всего запроса после объединения.
Цель SQL_CALC_FOUND_ROWS для UNION состоит в том, что он должен вернуть количество строк, которые будут возвращены без глобального LIMIT. Условия применения SQL_CALC_FOUND_ROWS с UNION перечислены ниже:
ВЫЧИСЛЕНИЯ
Итоговые функции
В выражениях SQL-запросов нередко требуется выполнить предварительную обработку данных. С этой целью используются специальные функции и выражения.
Довольно часто требуется узнать, сколько записей соответствует тому или иному запросу, какова сумма значений некоторого числового столбца, его максимальное, минимальное и среднее значения. Для этого служат так называемые итоговые (статистические, агрегатные) функции. Итоговые функции обрабатывают наборы записей, заданные, например, выражением WHERE. Если их включить в список столбцов, следующий за оператором SELECT, то результатная таблица будет содержать не только столбцы таблицы базы данных, но и значения, вычисленные с помощью этих функций. Далее приведен список итоговых функций .
SELECT COUNT(*) FROM Клиенты;
SELECT COUNT(Сумма_заказа) FROM Клиенты;
SELECT COUNT(DISTINCT Сумма_заказа) FROM Клиенты;
Попытка выполнить следующий запрос приведет к сообщению об ошибке:
SELECT Регион , COUNT(*) FROM Клиенты ;
SELECT SUM (Сумма_заказа) FROM Клиенты;
Данное SQL-выражение возвращает таблицу, состоящую из одного столбца и одной записи и содержащую сумму всех определенных значений столбца Сумма_заказа из таблицы Клиенты.
Допустим, что в исходной таблице значения столбца Сумма_заказа выражены в рублях, а нам требуется вычислить общую сумму в долларах. Если текущий обменный курс равен, например, 27,8, то получить требуемый результат можно с помощью выражения:
SELECT SUM (Сумма_заказа*27.8) FROM Клиенты;
SELECT AVG (Сумма_заказа) FROM Клиенты;
SELECT AVG (Сумма_заказа*27.8) FROM Клиенты
WHERE Регион <> "Северо_3апад";
SELECT МАХ(Сумма__заказа) FROM Клиенты;
SELECT МАХ(Сумма_заказа*27.8) FROM Клиенты
W HERE Регион <> "Северо_3апад";
SELECT MIN(Сумма_заказа) FROM Клиенты;
SELECT MIN (Сумма__заказа*27 . 8) FROM Клиенты
W HERE Регион <> "Северо_3апад";
На практике нередко требуется получить итоговую таблицу, содержащую суммарные, усредненные, максимальные и минимальные значения числовых столбцов. Для этого следует использовать группировку (GROUP BY) и итоговые функции.
SELECT Регион, SUM (Сумма_заказа) FROM Клиенты
GROUP BY Регион;
Результатная таблица для данного запроса содержит имена регионов и итоговые (общие) суммы заказов всех клиентов из соответствующих регионов (рис. 5).
Теперь рассмотрим запрос на получение всех итоговых данных по регионам:
SELECT Регион, SUM (Сумма_заказа), AVG (Сумма_заказа), МАХ(Сумма_заказа), MIN (Сумма_заказа)
FROM Клиенты
GROUP BY Регион;
Исходная и результатная таблицы показаны на рис. 8. В примере только Северо-Западный регион представлен в исходной таблице более чем одной записью. Поэтому в результатной таблице для него различные итоговые функции дают различные значения.
Рис. 8. Итоговая таблица сумм заказов по регионам
При использовании итоговых функций в списке столбцов в операторе SELECT заголовки соответствующих им столбцов в результатной таблице имеют вид Expr1001, Expr1002 и т.д. (или что-нибудь аналогичное, в зависимости от реализации SQL). Однако заголовки для значений итоговых функций и других столбцов вы можете задавать по своему усмотрению. Для этого достаточно после столбца в операторе SELECT указать выражение вида:
AS заголовок_столбца
Ключевое слово AS (как) означает, что в результатной таблице соответствующий столбец должен иметь заголовок, указанный после AS. Назначаемый заголовок еще называют псевдонимом. В следующем примере (рис. 9) задаются псевдонимы для всех вычисляемых столбцов:
SELECT Регион,
SUM (Сумма_заказа) AS [Общая сумма заказа],
AVG (Сумма_заказа) AS [Средняя сумма заказа],
МАХ(Сумма_заказа) AS Максимум,
MIN (Сумма_заказа) AS Минимум,
FROM Клиенты
GROUP BY Регион;
Рис. 9. Итоговая таблица сумм заказов по регионам с применением псевдонимов столбца
Псевдонимы, состоящие из нескольких слов, разделенных пробелами, заключаются в квадратные скобки.
Итоговые функции можно использовать в выражениях SELECT и HAVING, но их нельзя применять в выражении WHERE. Oneратор HAVING аналогичен оператору WHERE, но в отличие от WHERE он отбирает записи в группах.
Допустим, требуется определить, в каких регионах более одного клиента. С этой целью можно воспользоваться таким запросом:
SELECT Регион , Count(*)
FROM Клиенты
GROUP BY Регион HAVING COUNT(*) > 1;
Функции обработки значений
При работе с данными часто приходится их обрабатывать (преобразовывать к нужному виду): выделить в строке некоторую подстроку, удалить ведущие и заключительные пробелы, округлить число, вычислить квадратный корень, определить текущее время и т. п. В SQL имеются следующие три типа функций:
Строковые функции
Строковые функции принимают в качестве параметра строку и возвращают после ее обработки строку или NULL.
Например:
SUBSTRING ("Дорогая Маша!" FROM 9 FOR 4) возвращает "Маша";
SUBSTRING ("Дорогая Маша! " FROM 9) возвращает "Маша! ";
SUBSTRING("Дорогая Маша! " FROM 15) возвращает NULL.
Использовать эту функцию в SQL-выражении можно, например, так:
SELECT * FROM Клиенты
WHERE SUBSTRING(Регион FROM 1 FOR 5) = "Север";
Например:
TRIM (LEADING " " FROM "город Санкт-Петербург") вращает " город Санкт-Петербург ";
TRIM(TRALING " " FROM "город Санкт-Петербург") возвращает "город Санкт-Петербург";
TRIM (BOTH " " FROM " город Санкт-Петербург ") возвращает "город Санкт-Петербург";
TRIM(BOTH FROM " город Санкт-Петербург ") возвращает "город Санкт-Петербург";
TRIM(BOTH "г" FROM "город Санкт-Петербург") возвращает "ород Санкт-Петербур".
Среди этих функций наиболее часто используемые - SUBSTRING() И TRIM().
Числовые функции
Числовые функции в качестве параметра могут принимать данные не только числового типа, но возвращают всегда число или NULL (неопределенное значение).
Например:
POSITION ("e" IN "Привет всем") возвращает 5;
POSITION ("всeм" IN "Привет всем") возвращает 8;
POSITION (" " Привет всем") возвращает 1;
POSITION("Привет!" IN "Привет всем") возвращает 0.
В таблице Клиенты (см. рис. 1) столбец Адрес содержит, кроме названия города, почтовый индекс, название улицы и другие данные. Возможно, вам потребуется выбрать записи о клиентах, проживающих в определенном городе. Так, если требуется выбрать записи, относящиеся к клиентам, проживающим в Санкт-Петербурге, то можно воспользоваться следующим выражением SQL-запроса:
SELECT * FROM Клиенты
WHERE POSITION (" Санкт - Петербург " IN Адрес ) > 0;
Заметим, что этот простой запрос на выборку данных можно сформулировать иначе:
SELECT * FROM Клиенты
WHERE Адрес LIKE " %Петербург% ";
EXTRACT (MONTH FROM DATE "2005-10-25") возвращает 10.
Например:
CHARACTER_LENGTH("Привет всем") возвращает 11.
ABS (-123) возвращает 123;
ABS (2 - 5) возвращает 3.
MOD (5, з) возвращает 2;
MOD (2, з) возвращает 0.
FLOOR (5.123) возвращает 5.0.
CEIL (5.123) возвращает 6. 0.
WIDTH_BUCKET(3.14, 0, 9, 5) возвращает 2.
Функции даты-времени
В языке SQL имеются три функции, которые возвращают текущие дату и время.
Например: 2005-06-18.
12:39:45.27.
Обратите внимание, что дата и время, возвращаемые этими функциями, имеют не символьный тип. Если требуется представить их в виде символьных строк, то для этого следует использовать функцию преобразования типа CAST ().
Функции даты-времени обычно применяются в запросах на вставку, обновление и удаление данных. Например, при записи сведений о продажах в специально предусмотренный для этого столбец вносятся текущие дата и время. После подведения итогов за месяц или квартал, данные о продажах за отчетный период можно удалить.
Вычисляемые выражения
Вычисляемые выражения строятся из констант (числовых, строковых, логических), функций, имен полей и данных других типов путем соединения их арифметическими, строковыми, логическими и другими операторами. В свою очередь, выражения могут быть объединены посредством операторов в более сложные (составные) выражения. Для управления порядком вычисления выражений используются круглые скобки.
Логические операторы AND, OR и NOT и функции были рассмотрены ранее.
Арифметические операторы:
Строковый оператор только один оператор конкатенации или склейки строк (| |). В некоторых реализациях SQL (например, Microsoft Access) вместо (| |) используется символ (+). Оператор конкатенации приписывает вторую строку к концу первой пример, выражение:
"Саша" | | "любит" | | " Машу"
вернет в качестве результата строку " Сашалюбит Машу".
При составлении выражений необходимо следить, чтобы операнды операторов имели допустимые типы. Например, выражение: 123 + "Саша" недопустимо, поскольку арифметический оператор сложения применяется к строковому операнду.
Вычисляемые выражения могут находиться после оператора SELECT, а также в выражениях условий операторов WHERE и HAVI NG .
Рассмотрим несколько примеров.
Пусть таблица Продажи содержит столбцы Тип_товара, Количество и Цена, а нам требуется знать выручку для каждого типа товара. Для этого достаточно в список столбцов после оператора SELECT включить выражение Количество*Цена:
SELECT Тип_товара, Количество, Цена, Количество*Цена AS
Итого FROM Продажи;
Здесь используется ключевое слово AS (как) для задания псевдонима столбца с вычисляемыми данными.
На рис. 10 показаны исходная таблица Продажи и результатная таблица запроса.
Рис. 10. Результат запроса с вычислением выручки по каждому типу товара
Если требуется узнать общую выручку от продажи всех товаров, то достаточно применить следующий запрос:
SELECT SUM (Количество*Цена) FROM Продажи;
Следующий запрос содержит вычисляемые выражения и в списке столбцов, и в условии оператора WHERE. Он выбирает из таблицы продажи те товары, выручка от продажи которых больше 1000:
SELECT Тип_товара, Количество*Цена AS Итого
FROM Продажи
WHERE Количество*Цена > 1000;
Предположим, что требуется получить таблицу, в которой два столбца:
Товар, содержащий тип товара и цену;
Итого, содержащий выручку.
Поскольку предполагается, что в исходной таблице продажи столбец Тип_товара является символьным (тип CHAR), а столбец Цена числовой, то при объединении (склейке) данных из этих столбцов необходимо выполнить приведение числового типа к символьному с помощью функции CAST (). Запрос, выполняющий это задание, выглядит так (рис. 11):
SELECT Тип_товара | | " (Цена: " | | CAST(Цена AS CHAR(5)) | | ")" AS Товар, Количество*Цена AS Итого
FROM Продажи;
Рис. 11. Результат запроса с объединением разнотипных данных в одном столбце
Примечание. В Microsoft Access аналогичный запрос будет иметь следующий вид:
SELECT Тип_товара + " (Цена: " + C Str (Цена) + ")" AS Товар,
Количество*Цена AS Итого
FROM Продажи;
Условные выражения с оператором CASE
В обычных языках программирования имеются операторы условного перехода, которые позволяют управлять вычислительным процессом в зависимости от того, выполняется или нет некоторое условие. В языке SQL таким оператором является CASE (случай, обстоятельство, экземпляр ). В SQL:2003 этот оператор возвращает значение и, следовательно, может использоваться в выражениях. Он имеет две основные формы, которые мы рассмотрим в данном разделе.
Оператор CASE со значениями
Оператор CASE со значениями имеет следующий синтаксис:
CASE проверяемое_значение
WHEN значение1 THEN результат1
WHEN значение2 THEN резулътат2
. . .
WHEN значением N THEN результат N
ELSE результатХ
В случае, когда проверяемое_значение равно значение1 , оператор CASE возвращает значение результат1 , указанное после ключевого слова THEN (то). В противном случае проверяемое_значение сравнивается с значение2 , и если они равны, то возвращается значение результат2. В противном случае проверяемое значение сравнивается со следующим значением, указанным после ключевого слова WHEN (когда) и т. д. Если проверяемое_значение не равно ни одному из таких значений, то возвращается значение результат X , указанное после ключевого слова ELSE (иначе).
Ключевое слово ELSE не является обязательным. Если оно отсутствует и ни одно из значений, подлежащих сравнению, не равно проверяемому значению, то оператор CASE возвращает NULL.
Допустим, на основе таблицы Клиенты (см. рис. 1) требуется получить таблицу, в которой названия регионов заменены их кодовыми номерами. Если в исходной таблице различных регионов не слишком много, то для решения данной задачи удобно воспользоваться запросом с оператором CASE:
SELECT Имя , Адрес ,
CASE Регион
WHEN " Москва " THEN "77"
WHEN "Тверская область" THEN "69"
. . .
ELSE Регион
AS Код региона
FROM Клиенты;
Оператор CASE с условиями поиска
Вторая форма оператора CASE предполагает его использование при поиске в таблице тех записей, которые удовлетворяют определенному условию:
CASE
WHEN условие1 THEN результат1
WHEN уоловие2 THEN результат2
. . .
WHEN условие N THEN результат N
ELSE результатХ
Оператор CASE проверяет, истинно ли условие1 для первой записи в наборе, определенном оператором WHERE, или во всей таблице, если WHERE отсутствует. Если да, то CASE возвращает значение результат1. В противном случае для данной записи проверяется условие2. Если оно истинно, то возвращается значение результат2 и т. д. Если ни одно из условий не выполняется, то возвращается значение результат X , указанное после ключ го слова ELSE.
Ключевое слово ELSE не является обязательным. Если оно отсутствует и ни одно из условий не выполняется, оператор CASE вращает NULL. После того как оператор, содержащий CASE, выполнится для первой записи, происходит переход к следующей записи. Так продолжается до тех пор, пока не будет обработан весь набор записей.
Предположим, в таблице книги (Название, Цена) столбец имеет значение NULL, если соответствующей книги нет в наличии. Следующий запрос возвращает таблицу, в которой вместо NULL отображается текст "Нет в наличии":
SELECT Название,
CASE
WHEN Цена IS NULL THEN " Нет в наличии "
ELSE CAST(Цена AS CHAR(8))
AS Цена
FROM Книги;
Все значения одного и того же столбца должны иметь одинаковые типы. Поэтому в данном запросе используется функция преобразования типов CAST для приведения числовых значений столбца Цена к символьному типу.
Обратите внимание, что вместо первой формы оператора CASE всегда можно использовать вторую:
CASE
WHEN проверяемое_значение = значение1 THEN результат1
WHEN проверяемое_значение = значение2 THEN результат2
. . .
WHEN проверяемое_значение = значение N THEN peзyльтaтN
ELSE резулътатХ
Функции NULLIF и COALESCE
В ряде случаев, особенно в запросах на обновление данных (оператор UPDATE), удобно использовать вместо громоздкого оператора CASE более компактные функции NULLIF () (NULL, если) и COALESCE() (объединять).
Функция NULLIF (значение1, значение2 ) возвращает NULL, если значение первого параметра соответствует значению второго параметра, в случае несоответствия возвращается значение первого параметра без изменений. То есть если равенство значение1 = значение2 выполняется, то функция возвращает NULL, иначе значение значение1.
Данная функция эквивалентна оператору CASE в следующих двух формах:
WHEN значение2 THEN NULL
ELSE значение1
WHEN значение1 = значение2 THEN NULL
ELSE значение1
Функция COALESCE(значение1, значение2, ... , значение N ) принимает список значений, которые могут быть как определенными, так и неопределенными (NULL). Функция возвращает определенное значение из списка или NULL, если все значения не определены.
Данная функция эквивалентна следующему оператору CASE:
CASE
WHEN значение 1 IS NOT NULL THEN значение 1
WHEN значение 2 IS NOT NULL THEN значение 2
. . .
WHEN значение N IS NOT NULL THEN значение N
ELSE NULL
Предположим, что в таблице Книги (Название, Цена) столбец Цена имеет значение NULL, если соответствующей книги нет в наличии. Следующий запрос возвращает таблицу, в которой вместо NULL отображается текст "Нет в наличии":
SELECT Название, COALESCE (CAST(Цена AS CHAR(8)),
"Нет в наличии") AS Цена
FROM Книги;
