Группировка записей и функция COUNT()
Пусть необходимо узнать, какие сообщения и в каких темах имеются. Для этого можно воспользоваться стандартным запросом с использованием оператора SELECT:
Для того чтобы узнать сколько сообщений на форуме имеется, можно воспользоваться встроенной функцией COUNT(). Эта функция подсчитывает число строк. Причем, если в качестве аргумента этой функции выступает *, то подсчитываются все строки таблицы. А если в качестве аргумента указывается имя столбца, то подсчитываются только те строки, которые имеют значение в указанном столбце.
В данном примере оба аргумента дадут одинаковый результат, т.к. все столбцы таблицы имеют тип NOT NULL.
Напишем запрос, используя в качестве аргумента столбец id_topic:
SELECT COUNT(id_topic) FROM posts;
Таким образом, в темах имеется 4 сообщения.
Пусть теперь необходимо узнать, сколько сообщений имеется в каждой теме. Для этого понадобится сгруппировать сообщения по темам и вычислить для каждой группы количество сообщений. Для группировки в SQL используется оператор GROUP BY.
Запрос теперь будет выглядеть так:
SELECT id_topic, COUNT(id_topic) FROM posts GROUP BY id_topic;
Оператор GROUP BY указывает СУБД сгруппировать данные по столбцу id_topic (т.е. каждая тема – отдельная группа) и для каждой группы подсчитать количество строк:
Таким образом, в теме с id=1 – 3 сообщения, а с id=4 – одно.
Если бы в поле id_topic были возможны отсутствия значений, то такие строки были бы объединены в отдельную группу со значением NULL.
Предположим, что нас интересуют только те группы, в которых больше двух сообщений. В обычном запросе данное условие указывается с помощью оператора WHERE, но этот оператор умеет работать только со строками, а для групп те же функции выполняет оператор HAVING:
SELECT id_topic, COUNT(id_topic) FROM posts
GROUP BY id_topic
HAVING COUNT(id_topic) > 2;
В результате имеем:
Условия, которые можно задавать оператором WHERE, можно задавать и оператором HAVING, только надо запомнить, что WHERE фильтрует строки, а HAVING – группы.
Редактирование, обновление и удаление данных
Предположим, что форуму нужны модераторы. Для этого в таблицу users надо добавить столбец с ролью пользователя. Для добавления столбцов в таблицу используется оператор ALTER TABLE – ADD COLUMN. Его синтаксис следующий:
ALTER TABLE имя_таблицы ADD COLUMN имя_столбца тип;
Добавим столбец role в таблицу users:
ALTER TABLE users ADD COLUMN role varchar(20);
Столбец появится в конце таблицы:
Для того чтобы указать местоположение столбца используются ключевые слова:
FIRST – новый столбец будет первым;
AFTER – указывает после какого столбца поместить новый.
Добавим еще два столбца: kol – количество оставленных сообщений и rating – рейтинг пользователя. Оба столбца вставим после поля password:
ALTER TABLE users ADD COLUMN kol int(10) AFTER password,
ADD COLUMN rating varchar(20) AFTER kol;
Теперь надо назначить роль модератора какому-нибудь пользователю, пусть это будет sergey с id=1. Для обновления уже существующих данных служит оператор UPDATE. Его синтаксис следующий:
UPDATE имя_таблицы SET имя_столбца=значение_столбца
WHERE условие;
Для данного примера:
UPDATE users SET role='модератор' WHERE id_user=1;
Изменять данные можно и сразу в нескольких строках и во всей таблице.
Например, рейтинг дается в зависимости от количества оставленных пользователем сообщений.
Сначала внесем в таблицу значения столбца kol:
Зададим рейтинг «Профи» тем, у кого количество сообщений больше 30: UPDATE users SET rating='Профи' WHERE kol > 30;
Данные изменились в двух строках, согласно заданному условию. Если в запросе опустить условие, то данные будут обновлены во всех строках таблицы.
Теперь предположим, что необходимо переименовать столбец «Рейтинг» в «Репутация» – reputation. Для изменения имени существующего столбца используется оператор CHANGE. Его синтаксис следующий:
ALTER TABLE имя_таблицы
CHANGE старое_имя_столбца новое_имя_столбца тип;
Для данного примера:
ALTER TABLE users CHANGE rating reputation varchar(20);
Обратите внимание, что тип столбца надо указывать даже, если он не меняется.
Если понадобится изменить только тип столбца, то используем оператор MODIFY. Его синтаксис следующий:
ALTER TABLE имя_таблицы
MODIFY имя_столбца новый_тип;
Оператор DELETE позволяет удалять строки из таблицы. Его синтаксис следующий:
DELETE FROM имя_таблицы WHERE условие;
Удалим из таблицы сообщений те записи, которые оставлял пользователь valera (id=2):
DELETE FROM posts WHERE id_author='2';
Если опустить условие, то из таблицы будут удалены все данные. Следует помнить, что данные СУБД даст удалить только в том случае, если они не являются внешними ключами для данных из других таблиц (поддержка целостности БД).
Например, если мы захотим удалить из таблицы users пользователя, который оставлял сообщения, то нам это не удастся. Сначала надо удалить его сообщения, а уж потом и его самого.
